HackMD
  • Beta
    Beta  Get a sneak peek of HackMD’s new design
    Turn on the feature preview and give us feedback.
    Go → Got it
      • Create new note
      • Create a note from template
    • Beta  Get a sneak peek of HackMD’s new design
      Beta  Get a sneak peek of HackMD’s new design
      Turn on the feature preview and give us feedback.
      Go → Got it
      • Sharing Link copied
      • /edit
      • View mode
        • Edit mode
        • View mode
        • Book mode
        • Slide mode
        Edit mode View mode Book mode Slide mode
      • Note Permission
      • Read
        • Only me
        • Signed-in users
        • Everyone
        Only me Signed-in users Everyone
      • Write
        • Only me
        • Signed-in users
        • Everyone
        Only me Signed-in users Everyone
      • More (Comment, Invitee)
      • Publishing
        Please check the box to agree to the Community Guidelines.
        Everyone on the web can find and read all notes of this public team.
        After the note is published, everyone on the web can find and read this note.
        See all published notes on profile page.
      • Commenting Enable
        Disabled Forbidden Owners Signed-in users Everyone
      • Permission
        • Forbidden
        • Owners
        • Signed-in users
        • Everyone
      • Invitee
      • No invitee
      • Options
      • Versions and GitHub Sync
      • Transfer ownership
      • Delete this note
      • Template
      • Save as template
      • Insert from template
      • Export
      • Dropbox
      • Google Drive Export to Google Drive
      • Gist
      • Import
      • Dropbox
      • Google Drive Import from Google Drive
      • Gist
      • Clipboard
      • Download
      • Markdown
      • HTML
      • Raw HTML
    Menu Sharing Create Help
    Create Create new note Create a note from template
    Menu
    Options
    Versions and GitHub Sync Transfer ownership Delete this note
    Export
    Dropbox Google Drive Export to Google Drive Gist
    Import
    Dropbox Google Drive Import from Google Drive Gist Clipboard
    Download
    Markdown HTML Raw HTML
    Back
    Sharing
    Sharing Link copied
    /edit
    View mode
    • Edit mode
    • View mode
    • Book mode
    • Slide mode
    Edit mode View mode Book mode Slide mode
    Note Permission
    Read
    Only me
    • Only me
    • Signed-in users
    • Everyone
    Only me Signed-in users Everyone
    Write
    Only me
    • Only me
    • Signed-in users
    • Everyone
    Only me Signed-in users Everyone
    More (Comment, Invitee)
    Publishing
    Please check the box to agree to the Community Guidelines.
    Everyone on the web can find and read all notes of this public team.
    After the note is published, everyone on the web can find and read this note.
    See all published notes on profile page.
    More (Comment, Invitee)
    Commenting Enable
    Disabled Forbidden Owners Signed-in users Everyone
    Permission
    Owners
    • Forbidden
    • Owners
    • Signed-in users
    • Everyone
    Invitee
    No invitee
       owned this note    owned this note      
    Published Linked with GitHub
    Like BookmarkBookmarked
    Subscribed
    • Any changes
      Be notified of any changes
    • Mention me
      Be notified of mention me
    • Unsubscribe
    Subscribe
    --- tags: 前端 --- # 資料庫筆記 ```sql= SQL操作備忘錄 SELECT * FROM Member WHERE account = {account}; UPDATE Member SET password = {password} WHERE id = {id}; REPLACE INTO Member (id, password) VALUES ({id},{password}); DELETE FROM Member WHERE id = {id}; INSERT INTO Member(...) VALUES (...); CREATE TABLE IF NOT EXISTS Member (...); [INTEGER|REAL|TEXT|BLOB] [NOT NULL|PRIMARY KEY|AUTOINCREMENT|UNIQUE|DEFAULT] [CURRENT_TIMESTAMP] FOREIGN KEY (...) REFERENCES [TABLE] (...) ON DELETE CASCADE ON UPDATE NO ACTION CREATE TRIGGER [TRIGGER] [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] ON [TABLE] WHEN [condition] BEGIN TRANSACTION; END; COMMIT; CREATE UNIQUE INDEX id ON Member (...); ALTER TABLE [TABLE] RENAME [COLUMN?] TO [TABLE|COLUMN]; DROP TABLE|TRIGGER [TABLE|TRIGGER]; ``` ### 資料庫圖形化介面 * [HeidiSQL](https://www.heidisql.com/) * [DBeaver](https://dbeaver.io/?ref=eversql.com) * [MySQL Workbench](https://www.mysql.com/products/workbench/) ### [MySQL](https://dev.mysql.com/downloads/mysql/)指令 |動作|指令| |-|-| |秀出所有的資料庫|show databases;| |建立新的資料庫|CREATE [db];| |刪除資料庫|DROP DATABASE [db];| |進入資料庫|USE [db];| |秀出所有已經建立的資料表|SHOW TABLES;| |查詢某個資料表的資料格式|DESCRIBE [table];| |建立新資料表|CREATE TABLE [table] (sid int);| ### [PostgreSQL](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)指令 |動作|指令| |-|-| |新增資料庫|CREATE DATABASE mytestdb;| |進入資料庫|\c mytestdb| |列出所有使用者|\du| |列出所有DB|\l| |列出所有table|\dt| ### 伺服器端架postgresql(ubuntu) | 動作 | 指令 | | -------- | -------- | |安裝postgresql|sudo apt install postgresql postgresql-contrib| |設定postgres密碼|sudo passwd postgres| |進入postgres命令列|su - postgres| |進入DB操作|psql| |更改使用者密碼|ALTER USER postgres WITH PASSWORD '自訂新密碼';| |離開DB操作|\q| |離開postgres命令列|exit| |設定開機自動執行|update-rc.d postgresql enable| |啟動資料庫伺服|service postgresql start| |開啟防火牆資料庫對外連接阜|ufw allow 5432/tcp| |開啟防火牆|ufw enable| |編輯(版本9.5)|nano /etc/postgresql/9.5/main/pg_hba.conf| |在最後一行加入|host all all 0.0.0.0/0 trust| |編輯(版本9.5)|nano /etc/postgresql/9.5/main/postgresql.conf| |找到註解並修改|listen_address = '\*' | |重啟伺服|service postgresql restart| ### 管理SCHEMA ```sql= PRIMARY KEY 主鍵 NOT NULL 不允許有空值 CHECK 限制特定條件 UNIQUE 不允許重複 CREATE TABLE [mytable] ( sid INT PRIMARY KEY, name VARCHAR(10) NOT NULL, date DATETIME CHECK(date>'2017-01-01'), addr TEXT UNIQUE ); 外來鍵(別人沒有的我不能有) CREATE TABLE [mytable2] ( id INT, sid INT, my_primary_key(id), my_foreign_key(sid) REFERENCE [mytable] (sid) ); ``` |刪除資料表|DROP TABLE [table];| |-|-| |更動資料表(刪除某欄)|ALTER TABLE [table] DROP [old_column];| |更動資料表(新增某欄)|ALTER TABLE [table] ADD [new_column] CHAR(10) AUTO_INCREMENT PRIMARY KEY;| |更動資料表(更改欄位名稱)|ALTER TABLE [table] CHANGE [old_name] [new_name]; |更動某欄資料格式|ALTER TABLE [table] CHANGE [column] CHAR(20);| |重新命名資料表|RENAME [old_table_name] TO [new_table_name];| ### CRUD(create, read, update, delete) |新增資料|INSERT INTO [table] (sid,date,name) VALUE (11,'2017-01-01','[value]');| |-|-| |跳過前2筆後的10筆|SELECT * FROM [table] LIMIT 10 OFFSET 2;| |選取多筆特定的欄位|SELECT [column1], [column2] FROM [table];| |更新資料|UPDATE [table] SET [column]='[value]' WHERE [column] = '[value]';| |刪除資料|DELETE FROM [table] WHERE [column] > [value];| |清空資料表中所有資料|TRUNCATE TABLE [table];| ### 特定函數 |平均|出現次數|最大|最小|總和|字數|去除左右邊的空白|唯一值| |-|-|-|-|-|-|-|-| |AVG|COUNT|MAX|MIN|SUM|LENGTH|TRIM|DISTINCT| |相等|不等於|與|或|任意個任意值|一個任意值| |-|-|-|-|-|-|-| |=|<>|AND|OR|%|_ |NOT|IN|BETWEEN| ```sql (COUNT 不包含空值) SELECT COUNT([column]) FROM [table]; (相等是用=而非==雙等號) SELECT * FROM [table] WHERE [column] = [value]; SELECT * FROM [table] WHERE [column] LIKE '%[text]%'; SELECT * FROM [table] WHERE [column] NOT IN ('[value1]','[value2]','[value3]'); SELECT * FROM [table] WHERE [column] BETWEEN '2017-01-01' AND '2017-12-31'; ``` ### 條件篩選 |ORDER BY 重新排序,ASC升冪由小到大(預設)、DESC降冪由大到小|SELECT * FROM [table] ORDER BY [column] DESC;| |-|-| |群組化運算|SELECT * FROM [table] GROUP BY [column];| |群組化後篩選|SELECT * FROM [table] GROUP BY [column] HAVING AVG([column]) > 666;| |AS 別名,當覺得指令或名稱很長時可使用|SELECT COUNT([abbr].[column]) AS [abbr] FROM [table] As [abbr] GROUP BY [abbr].[column];| |CASE WHEN THEN END 條件下更換資料|SELECT [column] CASE [any_column] WHEN '[specified_value]' THEN [any_column] * 2 END FROM [mytable];| |VIEW虛擬資料表,想要測試SQL但不想要動到資料庫時可使用|CREATE VIEW [view_table] AS SELECT * FROM [table];<br>SELECT * FROM [view_table];| ### 資料表合併 |UNION聯集且不包含重複的值 |SELECT [column] FROM [table1] UNION SELECT [column] FROM [table2];| |-|-| |UNOIN ALL聯集且包含重複的值|SELECT [column] FROM [table1] WHERE EXISTS (SELECT [column] FROM [table2]);| |INTERSECT交集|SELECT [column] FROM [table1] INTERSECT SELECT [column] FROM [table2];| |MINUS減集(前者有後者沒有的)|SELECT [column] FROM [table1] MINUS SELECT [column] FROM [table2];| |LEFT JOIN|SELECT [columns] FROM [table1] LEFT JOIN [table2] ON [columns]| |CROSS JOIN排列組合|| ### 檔案I/O |執行SQL檔|source C:/[directory]/[.sql]| |-|-| |匯入CSV檔(MySQL)|LOAD DATA INFILE '[.csv]' INTO TABLE [table] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;| |匯入CSV檔(PGSQL)|COPY DB.表格(欄位) FROM '文字檔的路徑' WITH CSV HEADER DELIMITER AS ',';| |輸出csv檔|SELECT * FROM [table] INTO OUTFILE '[.csv]' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';| # 資料表合併 #### 居住城市資料表 |索引 |名字 |城市| |---|---|---| |0 |我 |新竹| |1 |你 |台中| |2 |他 |台北| |3 |她 |高雄| #### 學校IP資料表 |IP |學校 |城市| |---|---|---| |113|交大 |新竹| |114|清大 |新竹| |112|台大 |台北| |116|成大 |台南| |120|中興 |台中| #### Inner join(natural join) |索引|名字|城市|IP|學校| |---|---|---|---|---| |0|我|新竹|113|交大| |0|我|新竹|114|清大| |1|你|台中|120|中興| |2|他|台北|112|台大| #### Left join(left outer join) |索引|名字|城市|IP|學校| |---|---|---|---|---| |0|我|新竹|113|交大| |0|我|新竹|114|清大| |1|你|台中|120|中興| |2|他|台北|112|台大| |3|她|高雄| | | #### Right join(right outer join) |索引|名字|城市|IP|學校| |---|---|---|---|---| |0|我|新竹|113|交大| |0|我|新竹|114|清大| |1|你|台中|120|中興| |2|他|台北|112|台大| | | |台南|116|成大| #### Full join(outer join) |索引|名字|城市|IP|學校| |---|---|---|---|---| |0|我|新竹|113|交大| |0|我|新竹|114|清大| |1|你|台中|120|中興| |2|他|台北|112|台大| |3|她|高雄| | | | | |台南|116|成大| #### Cross join(共20欄) |索引|名字|城市|IP|學校| |---|---|---|---|---| |0|我|新竹|113|交大| |0|我|新竹|114|清大| |0|我|台中|120|中興| |0|我|台北|112|台大| |0|我|台南|116|成大| |1|你|新竹|113|交大| |1|你|新竹|114|清大| |1|你|台中|120|中興| |1|你|台北|112|台大| |1|你|台南|116|成大| |…|…|…|…|…|

    Import from clipboard

    Advanced permission required

    Your current role can only read. Ask the system administrator to acquire write and comment permission.

    This team is disabled

    Sorry, this team is disabled. You can't edit this note.

    This note is locked

    Sorry, only owner can edit this note.

    Reach the limit

    Sorry, you've reached the max length this note can be.
    Please reduce the content or divide it to more notes, thank you!

    Import from Gist

    Import from Snippet

    or

    Export to Snippet

    Are you sure?

    Do you really want to delete this note?
    All users will lost their connection.

    Create a note from template

    Create a note from template

    Oops...
    This template is not available.


    Upgrade

    All
    • All
    • Team
    No template found.

    Create custom template


    Upgrade

    Delete template

    Do you really want to delete this template?

    This page need refresh

    You have an incompatible client version.
    Refresh to update.
    New version available!
    See releases notes here
    Refresh to enjoy new features.
    Your user state has changed.
    Refresh to load new user state.

    Sign in

    Forgot password

    or

    By clicking below, you agree to our terms of service.

    Sign in via Facebook Sign in via Twitter Sign in via GitHub Sign in via Dropbox

    New to HackMD? Sign up

    Help

    • English
    • 中文
    • Français
    • Deutsch
    • 日本語
    • Español
    • Català
    • Ελληνικά
    • Português
    • italiano
    • Türkçe
    • Русский
    • Nederlands
    • hrvatski jezik
    • język polski
    • Українська
    • हिन्दी
    • svenska
    • Esperanto
    • dansk

    Documents

    Tutorials

    Book Mode Tutorial

    Slide Mode Tutorial

    YAML Metadata

    Contacts

    Facebook

    Twitter

    Feedback

    Send us email

    Resources

    Releases

    Pricing

    Blog

    Policy

    Terms

    Privacy

    Cheatsheet

    Syntax Example Reference
    # Header Header 基本排版
    - Unordered List
    • Unordered List
    1. Ordered List
    1. Ordered List
    - [ ] Todo List
    • Todo List
    > Blockquote
    Blockquote
    **Bold font** Bold font
    *Italics font* Italics font
    ~~Strikethrough~~ Strikethrough
    19^th^ 19th
    H~2~O H2O
    ++Inserted text++ Inserted text
    ==Marked text== Marked text
    [link text](https:// "title") Link
    ![image alt](https:// "title") Image
    `Code` Code 在筆記中貼入程式碼
    ```javascript
    var i = 0;
    ```
    var i = 0;
    :smile: :smile: Emoji list
    {%youtube youtube_id %} Externals
    $L^aT_eX$ LaTeX
    :::info
    This is a alert area.
    :::

    This is a alert area.

    Versions

    Versions and GitHub Sync

    Sign in to link this note to GitHub Learn more
    This note is not linked with GitHub Learn more
     
    Add badge Pull Push GitHub Link Settings
    Upgrade now

    Version named by    

    More Less
    • Edit
    • Delete

    Note content is identical to the latest version.
    Compare with
      Choose a version
      No search result
      Version not found

    Feedback

    Submission failed, please try again

    Thanks for your support.

    On a scale of 0-10, how likely is it that you would recommend HackMD to your friends, family or business associates?

    Please give us some advice and help us improve HackMD.

     

    Thanks for your feedback

    Remove version name

    Do you want to remove this version name and description?

    Transfer ownership

    Transfer to
      Warning: is a public team. If you transfer note to this team, everyone on the web can find and read this note.

        Link with GitHub

        Please authorize HackMD on GitHub

        Please sign in to GitHub and install the HackMD app on your GitHub repo. Learn more

         Sign in to GitHub

        HackMD links with GitHub through a GitHub App. You can choose which repo to install our App.

        Push the note to GitHub Push to GitHub Pull a file from GitHub

          Authorize again
         

        Choose which file to push to

        Select repo
        Refresh Authorize more repos
        Select branch
        Select file
        Select branch
        Choose version(s) to push
        • Save a new version and push
        • Choose from existing versions
        Available push count

        Upgrade

        Pull from GitHub

         
        File from GitHub
        File from HackMD

        GitHub Link Settings

        File linked

        Linked by
        File path
        Last synced branch
        Available push count

        Upgrade

        Danger Zone

        Unlink
        You will no longer receive notification when GitHub file changes after unlink.

        Syncing

        Push failed

        Push successfully