HackMD - Collaborative Markdown Knowledge Base

資料庫基本操作 定義：資料定義語言(Data Definition Language, DDL) 功能：用來定義資料庫、資料表(含欄位名稱、資料型態及設置完整性限制) 建立資料庫： Create DataBase [IF NOT EXISTS] DataBase_Name 修改資料庫： Alter DataBase DataBase_Name [CHARACTER SET 字元及名稱] [COLLATE Collation 名稱] 刪除資料庫： Drop DataBase [IF EXISTS] DataBase_Name   建立資料表： Create Table Table_Name ( 欄位 {資料型態|定義域} [NULL|NOT NULL] [Default] [定義整合限制], ...... Primary Key(欄位集合), ← 當主鍵，不可為空值 Foreign Key(oreign Key (欄位集合) Reference 基本表 (屬性集合), ← 當外鍵 Unique (欄位集合), ← 當候選鍵，用來定義某一欄位具唯一的索引值，可為空值 [ON DELETE], [ON UPDATE] ) 符號說明： {|} → 大括號內的項目為必要項，可以擇一 [] → 中括號內的項目是非必要項，依實際情況來選擇 NULL|NOT NULL → 可為空值|不可為空值 Default → 設置欄位預設值 ON UPDATE/ON DELETE → 當資料表的資料更新或刪除時，所關聯的資料表的紀錄也會一併被異動   修改資料表： ALTER TABLE Table_Name 新增欄位： ADD 欄位名稱 {資歷型態|定義域} [NULL|NOT NULL] [Default] 修改欄位： Modify 欄位名稱 {資歷型態|定義域} [NULL|NOT NULL] [Default] 刪除欄位： Drop 欄位名稱   建立資料表順序：父關聯表→子關聯表 刪除關聯資料表：子關聯表→父關聯表   定義：資料操作語言(Data Manipulation Language, DML) 功能：對資料表紀錄的新增、修改、刪除及查詢等功能   新增紀錄： INSERT INTO Table_Name <欄位串列> VALUES (<欄位值串列> | <SELECT 指令>) 例： USE test_db; INSERT INTO 學生表 SELECT * FROM 學生表_OLD; 將學生表_OLD所查詢的紀錄新增至學生表 修改紀錄： UPDATE Table_Name SET {<欄位名稱1>=<欄位值1>, ..., <欄位名稱n>=<欄位值n>} [WHERE <條件子句>] 例： USE test_db; UPDATE 學生表 SET 電話='222222' WHERE WHERE 電話 IS NULL AND 學號='S002' 將尚未填入電話號碼的S002學號設定為'222222' 刪除紀錄： DELETE FROM Table_Name [WHERE <條件式>]; 例： USE test_db; DELETE FROM `學生表_OLD` WHERE 姓名='十全'; 將學生表_OLD中的十全學生紀錄刪掉 查詢紀錄： SELECT [DISTINCT] <欄位串列> FROM (資料表名稱 {<別名>}|JOIN 資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位>] [HAVING <群組條件>] [ORDER BY <欄位> [ASC|DESC]] [LIMIT 限制顯示筆數] ASC ← 由小至大 DESC ← 由大至小 例： USE test_db; SELECT * FROM 學生表 WHERE 性別='女'; 找出學生表中，性別為女的學生紀錄 USE test_DB; SELECT 課名 AS 課程名稱, 學分數 FROM 課程表; 將課名欄位設定別名為課程名稱(AS只是暫時性的變更列名，並不會把原本的名稱覆蓋過去)   定義：資料控制語言(Data Control Language, DCL) 功能：控制使用者對資料庫內容的存取權利   取得現有資料庫使用者帳號的權限： GRANT 權限 ON Table_Name TO 使用者 權限可分為四種：INSERT, UPDATE, DELETE, SELECT 例： GRANT SELECT, INSERT ON 學生表 TO USER1; 對USER1提供SELECT, INSERT對客戶資料表的使用者權限功能 GRANT SELECT TO 學生表 TO PUBLIC; 對所有使用者提供SELECT功能權限 取消資料庫使用者已取得的權限： REVOKE 權限 ON 資料庫名稱 FROM 使用者 例： REVOKE INSERT ON 學生表 FROM UER1; 移除USER1對學生表INSERT的權限   比較運算子； = → 等於 <> → 不等於 < → 小於 <= → 小於等於 > → 大於 >= → 大於等於 邏輯比較運算子： AND → 且 OR → 或 NOT → 反 IS NULL → 為空值例：SELECT * FROM 選課表 WHERE 成績 IS NULL; 模糊條件與範圍： LIKE：模糊相似條件 %：代表0個或1個以上的任意字元 _：代表單一個數的任意字元例：WHERE 系所 LIKE '資管%'; IN：集合條件只要符合其中一個條件，將被選取例：WHERE 課號 IN ('C001', 'C002'); BETWEEN.....AND：範圍條件等同於：最小值>= AND <=最大值 WHERE 成績 BETWEEN 60 AND 80; 算數運算子： + ：加 - ：減 * ：乘 / ：除 % ：取餘數 例：WHERE 成績*1.2<70;   聚合函數：統計資料表中數值資料的最大值、最小值、平均值及合計值 COUNT(*) ：計算個數 例：SELECT COUNT(*) AS 全班人數 FROM 學生表在學生表中查詢目前選修課程的全班人數 COUNT(欄位名稱)：計算欄位名稱不具NULL質料的總數 例：SELECT COUNT(成績) AS 有成績總筆數 FROM 選課表; 在選課表中查詢有成績而不為空值的總筆數 AVG ：計算平均值 例：SELECT AVG(成績) AS 資料庫平均成績 FROM 選課表 WHERE 課號='C005'; 在選課表中查詢有選修課號為'C005'的全班平均值 SUM ：計算總和 例：SELECT SUM(成績) AS 資料庫總成績 FROM 選課表 WHERE 課號='C005'; 在選課表中查詢有選修課號為'C005'的全班總成績 MAX ：計算最大值 例：SELECT MAX(成績) AS 資料庫最高分 FROM 選課表 WHERE 課號='C005'; 在選課表中查詢有選修課號為'C005'的全班最高分 MIN ：計算最小值 例：SELECT MIN(成績) AS 資料庫最高分 FROM 選課表 WHERE 課號='C005'; 在選課表中查詢有選修課號為'C005'的全班最低分   排序及排成績： ORDER BY 成績 ASC：遞增排序，由小至大 例：SELECT 學號, 課號, 成績 FROM 選課表 ORDER BY 成績 ASC; ORDER BY 成績 DESC：遞減排序，由大至小 例：SELECT 學號, 課號, 成績 FROM 選課表 ORDER BY 成績 DESC; 複雜排序：指定一個欄位以上來做排序，先以第一個欄位優先，再由第二個欄位進行排序，以此類推 例：SELECT * FROM 選課表 ORDER BY 學號, 成績; 先由學號進行排序，再由成績做排序 限制顯示筆數： LIMIT：資料記錄在排序之後，取前N筆資料 例：SELECT * FROM 選課表 WHERE 課號='C005' ORDER BY 成績 DESC LIMIT 2 在選課表中查詢有選課號'C005'的同學成績前兩名的成績   群組化： GROUP BY：將某些特定欄位的值相同紀錄全部組合起來，以進行群組化，接著可以在這個群組內求出各種統計分析 GROUP BY 欄位1, 欄位2, ..., 欄位N [HAVING 條件式] 1.GROUP BY 可單獨存在，將數個欄位組合起來，以做為每次動作的依據 2.[HAVING 條件式] 將數個欄位中以有條件的組合。不可單獨存在 3.WHERE 與 HAVING之差別 WHERE HAVING 執行順序 GROUP BY 之前 GROUP BY 之後 聚合函數不能使用聚合函數可以使用 SELECT 欄位1, 欄位2, 聚合函數運算 FROM 資料表 WHERE 過濾條件式 GROUP BY 欄位1, 欄位2; 例：SELECT 學號, COUNT(*) AS 選科目數 FROM 選課表 GROUP BY 學號; 在選課表中查詢每一位同學各選幾門科目 例：SELECT 學號, AVG(成績) AS 平均成績 FROM FROM 選課表 GROUP BY 學號 HAVING AVG(成績) >= 70; 在選課表中計算所修之課目的平均成績，大於70者顯示出來