# SQL筆記 ## SQL簡介 SQL (Structured Query Language)是關聯式資料庫，每一筆資料都會用預先設定的格式儲存，例如MySQL。 相較於關聯式資料庫，NOSQL(Not only SQL)的每筆資料可以有不同的欄位，用key-value來查詢，可以用來處理分散式檔案，例如mongoDB。 ## SQL語法 * **SELECT 查詢** * 查詢特定col ``` SQL SELECT column1, column2, ... FROM table_name; ``` * 查詢所有col ``` SQL SELECT * FROM table_name; ``` * 查詢不重複資料 ``` SQL SELECT DISTINCT column1, column2, ... FROM table_name; ``` * 有條件的查詢 ``` SQL SELECT column1, column2, ... FROM table_name WHERE condition; ``` 下表為數值可用的運算符(表格修改自 LHB阿好伯 SQL語法基礎) | Operator | 功能 | SQL 範例 | | :-------------: | :-----------------: | :--------------: | | =, !=, < <=, >, >= | 標準數值操作 | col_name != 4 | | # | 匹配一個數字 | 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295 | | BETWEEN ... AND ... | 數值在兩個值的範圍（含）內 | col_name BETWEEN 1.5 AND 10.5 | | NOT BETWEEN ... AND ... | 數值不在兩個值的範圍(含）內 | col_name NOT BETWEEN 1 AND 10 | | IN (...) | 存在於列表中存在 | col_name IN (2, 4, 6) | | NOT IN (...) | 不是在列表中的存在 | col_name NOT IN (1, 3, 5) | 下表為字串可用的運算符 | Operator | Condition | Example | |:--------:|:---------:|:--------:| | = | ==區分大小==寫精確的字符串比較（注意單等號） | col_name = "abc" | | != or <> | ==區分大小==寫精確的字符串比較不相等 | col_name != "abcd" | | LIKE | ==不區分==大小寫精確的字符串比較 | col_name LIKE "ABC" | | NOT LIKE | ==不區分==大小寫精確的字符串比較不相等 | col_name NOT LIKE "ABCD" | | % |字符串中的任何地方使用匹配的==零個或多個==字符（僅與LIKE或NOT LIKE）序列 | col_name LIKE "%AT%"\(matches "AT", "ATTIC", "CAT" or even "BATS") | | _ | 字符串中的任何地方使用，以匹配==單個==字符（僅與LIKE或NOT LIKE） | col_name LIKE "AN_"\(matches "AND", but not "AN") | | # | 字符串中的任何地方使用，以匹配==單個==字符（僅與LIKE或NOT LIKE） | col_name LIKE "AN_"\(matches "AND", but not "AN") | | IN (...) | 字串存在於列表中 | col_name IN ("A", "B", "C") | | NOT IN (...) |字串不存在於列表中 | col_name NOT IN ("D", "E", "F" | * 查詢空值 * IS NULL ``` SQL SELECT column_names FROM table_name WHERE column_name IS NULL; ``` * IS NOT NULL ``` SQL SELECT column_names FROM table_name WHERE column_name IS NOT NULL; ``` * **ORDER BY 排序** ``` SQL SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; ``` * **INSERT INTO 插入資料** * 插入特定欄位 ``` SQL INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ``` * 插入所有欄位 ``` SQL INSERT INTO table_name VALUES (value1, value2, value3, ...); ``` * **UPDATE 更新資料** 記得要寫好更新的條件，不然所有的欄位都會被更動! ``` SQL UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ``` * **DELETE 刪除資料** 記得要寫好刪除的條件，不然所有的欄位都會被刪除! * 刪除符合條件的資料 ``` SQL DELETE FROM table_name WHERE condition; ``` * 刪除全部資料 ``` SQL DELETE FROM table_name; ``` * **JOIN 連結資料** * INNER JOIN ```SQL SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; ``` 範例: ```SQL SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; ``` 表Order: ![](https://i.imgur.com/tNHFa80.png) 表Customers: ![](https://i.imgur.com/JWO0m7F.png) result: ![](https://i.imgur.com/uzkKlnx.png) * LEFT JOIN 把表1跟表2連接起來的時候，保留所有表1的資料(就算在表2中沒有找到匹配的行) ```SQL SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; ``` 範例: ```SQL SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; ``` 表Customers: ![](https://i.imgur.com/JWO0m7F.png) 表Order: ![](https://i.imgur.com/tNHFa80.png) result: ![](https://i.imgur.com/a0f7S6Q.png) * RIGHT JOIN 把表1跟表2連接起來的時候，保留所有表2的資料(就算在表1中沒有找到匹配的行) ```SQL SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; ``` * FULL JOIN 保留表1跟表2的所有資料，沒有匹配到的欄位就填NULL ```SQL SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name; ``` 圖表整理： ![](https://i.imgur.com/OvqUuiQ.jpg) * SELF JOIN 不是跟其他table做連結，而是跟自己做連結。 T1,T2都是table1的別名(alias) ```SQL SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; ``` 範例: ```SQL SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.City = B.City; ``` 用SELF JOIN找出表Customers中，來自相同城市的消費者，會先把每一筆資料都更其他筆資料比對，當符合條件時，就把這筆資料列入result中。 例如表中ID1的顧客來自Berlin，跟每一筆資料比對之後，發現只有ID1的顧客來自Berlin，所以跟條件(A.City = B.City)相符的就只有自己，形成表result中的第一筆資料。(為了美觀，圖中沒有畫出ID1跟其他資料比對的藍色箭頭) 而ID2的顧客來自Mexico，跟所有資料比對之後，發現ID2、ID3、ID13也都來自Mexico，符合條件(A.City = B.City)，故依序形成表result中的第2、3、4筆資料。 ![](https://i.imgur.com/YJDtYPI.png) **result:** ![](https://i.imgur.com/5VL6env.png) * **UNION 合併資料** * UNION defalut會去掉重複的值，若想要全部顯示，要用UNION ALL ```SQL SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; ``` 也可以加上條件，例如： ```SQL SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City; ``` * UNION ALL ```SQL SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; ``` * **GROUP BY 資料分組** ```SQL SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ``` 範例(統計來自每個國家的顧客數) ```SQL SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; ``` **result:** ![](https://i.imgur.com/RAelvOO.png) * **HAVING** ```SQL SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); ``` 範例(列出大於5個客人的國家&顧客數) ```SQL SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; ``` **result:** ![](https://i.imgur.com/b14V3iw.png) HAVING 跟 WHERE 都是在進行資料的過濾，但 HAVING 只用aggregate (合計) 情況下 ，也就是有GROUP BY的時候，並且HAVING要過濾的條件，要跟GROUP BY的合計函數有關。 **(1) 沒有GROUP BY的時候，只使用WHERE而不使用HAVING。** 例如: ```SQL SELECT * FROM mymoney WHERE mperson='0'; //(O 正確) ``` ```SQL SELECT * FROM mymoney HAVING mperson='0'; //(X 雖然也正確，但不建議使用) ``` **(2) 有GROUP BY的時候，WHERE在GROUP BY前面，HAVING在GROUP BY後面。** 也就是WHERE條件跟GROUP BY沒有關係，而HAVING是跟GROUP BY有關係的，例如: ```SQL SELECT SUM(mamount) as s FROM mymoney WHERE SUBSTR(mdate,1,6)='201401' GROUP BY mperson HAVING s>200; ``` **(3) 使用HAVING的時候，只用在跟GROUP BY相關函數有關的條件上。** 如上例，HAVING指定SUM(mamount) >200。 如果你使用WHERE SUM(mamount) >200就會出現錯誤。 * **EXIST** ```SQL SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); ``` 範例(EXIST回傳TRUE之後，列出商品價格小於20的suppliers) ```SQL SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20); ``` **result:** ![](https://i.imgur.com/Jhf2pTu.png) 先判斷EXIST回傳 TRUE 或 FALSE，若EXIST = TRUE，就繼續執行外查詢中的SQL；若為FALSE，整個SQL查詢不會回傳結果。 ```SQL SELECT * FROM table_a WHERE EXISTS (SELECT * FROM table_b WHERE table_b.id=table_a.id); ``` 上面的結果跟下面一樣： ```SQL SELECT * FROM table_a WHERE id in (SELECT id FROM table_b); ``` 資料來源: * W3school SQL Tutorial * SQL 語法基礎 LHB阿好伯 * [SQL語法中WHERE與HAVING有何差異?](https://www.mysql.tw/2014/06/sqlwherehaving.html) * [SQL EXIST](https://www.fooish.com/sql/exists.html)