SQL - HackMD

# SQL https://www.fooish.com/sql/syntax.html MS SQL語法（join的使用） --當要刪除資料時，加上一個select 可快速顯示結果 delete Table01 where id='1' select * from Table01 LIKE 可以用來篩選出部分符合條件的字串 SELECT * FROM users WHERE name LIKE '%Ja%' ## [SQL 教學](https://www.fooish.com/sql/) ### 1.Data definition 資料定義 DDL 資料庫的相關操作： 1. CREATE 建立資料庫 2. ALTER 變更資料庫 3. DROP 刪除資料庫&表 ```sql= -- CREATE 建立資料庫 -- CREATE TABLE table_name ( -- column_name1 data_type, -- column_name2 data_type, -- ··· -- ); CREATE TABLE user( UserId init, UserName varchar(50), ... ); ``` ```sql= -- ALTER 變更資料庫 -- 增加欄位 (ADD COLUMN) -- ALTER TABLE table_name ADD column_name datatype; ALTER TABLE user ADD UserNumber VARCHAR(10); -- 更改欄位資料型別 (ALTER COLUMN TYPE) -- ALTER TABLE table_name ALTER COLUMN column_name datatype; ALTER TABLE user ALTER COLUMN UserNumber VARCHAR(20); -- 刪除欄位 (DROP COLUMN) -- ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE user DROP COLUMN UserNumber; ``` ```sql= -- DROP 刪除資料庫&表 -- 刪除資料表 (DROP TABLE) -- DROP TABLE table_name; DROP TABLE user; -- 清空資料，保留結構(TRUNCATE TABLE) -- TRUNCATE TABLE table_name; TRUNCATE TABLE user; -- 刪除資料庫 (DROP DATABASE) -- DROP DATABASE database_name; DROP DATABASE project_data; ``` ### 2. Data manipulation 資料操作 DML 處理資料表裡的資料： 1. INSERT: 新增資料到資料表中 2. UPDATE: 更改資料表中的資料 3. DELETE: 刪除資料表中的資料 ```sql= -- INSERT 方法一 -- INSERT INTO table_name (column1, column2, column3...) -- VALUES (value1, value2, value3...); INSERT INTO user (UserId,UserName) VALUES (1,'Lily'); -- INSERT 方法二！簡寫方式欄位須依序輸入 -- INSERT INTO table_name -- VALUES (value1, value2, value3...); INSERT INTO user VALUES (1,'Lily'); -- 一次新增多筆資料 (INSERT INTO SELECT) -- INSERT INTO table_name -- VALUES (value1_1, value2_2, value3_3,···), -- (value2_1, value2_2, value2_3,···), -- (value3_1, value3_2, value3_3,···), -- ······; INSERT INTO user VALUES (1,'Lily'),(2,'Louis'); -- 利用子查詢，從其它的資料表中取得資料來作一次多筆新增： -- INSERT INTO table_name (column1, column2, column3,...) -- SELECT othercolumn1, othercolumn2, othercolumn3,... -- FROM othertable_name; ``` ```sql= -- UPDATE 更改資料表中的資料 -- UPDATE table_name -- SET column1=value1, column2=value2, column3=value3··· -- WHERE some_column=some_value; UPDATE user SET UserName='Brian' WHERE UserId=2; ``` ```sql= -- DELETE FROM 是用來刪除資料表中的資料。(不加WHERE條件式，"全部的" 資料都會刪除） -- DELETE FROM table_name -- WHERE column_name operator value; DELETE FROM user WHERE UserName='Brian'; -- 一次刪除資料表中所有的資料方法一 -- DELETE FROM table_name; DELETE FROM user; -- 一次刪除資料表中所有的資料方法二 -- DELETE * FROM table_name; DELETE * FROM user; ``` ### 3. Queries 資料查詢 DQL 用來查詢資料表裡的資料: 1. SELECT: 選取資料庫中的資料 ```sql= -- SELECT table_column1, table_column2, table_column3... -- FROM table_name; SELECT UserId, UserName FROM user; -- 一次取得所有資料 -- SELECT * FROM table_name; SELECT * FROM user; ``` ### 4. LIKE 語法 (SQL LIKE Syntax) LIKE 運算子搭配 WHERE 子句可以依一特定模式 (Pattern) 為條件來搜尋資料表中的特定資料。 1. LIKE：條件搜尋資料 2. NOT LIKE：不包含在條件裡的的資料 ```sql= -- % 任何含有零或多個字元的字串。 -- LIKE '%computer%' 可找出書名中含有 'computer' 這個字的所有書名。 -- LIKE：條件搜尋資料 -- SELECT table_column1, table_column2, table_column3... -- FROM table_name WHERE column_name LIKE pattern; SELECT * FROM customers WHERE Address LIKE '台北%'; -- NOT LIKE：不包含在條件裡的的資料 SELECT * FROM customers WHERE Address NOT LIKE '台北%'; ``` 補充 pattern ![](https://i.imgur.com/uMKrppZ.png) ### 5. IN 語法 (SQL IN Syntax) IN 搭配 WHERE 子句可以用來限定必需符合某些欄位值為條件來搜尋資料表中的特定資料。 1. IN：符合某些欄位值為條件來搜尋資料 2. NOT IN：不包含在條件裡的的資料 ```sql= -- IN：符合某些欄位值為條件來搜尋資料 -- SELECT table_column1, table_column2, table_column3... -- FROM table_name -- WHERE column_name -- IN (value1, value2, value3...); SELECT * FROM customers WHERE Name IN ('張一', '李三'); -- NOT IN：不包含在條件裡的的資料 SELECT * FROM customers WHERE Name NOT IN ('張一', '李三'); ``` ### 6. JOIN 連接 (SQL JOIN) SQL JOIN (連接) 是利用不同資料表之間欄位的關連性來結合多資料表之檢索。 [多種JOIN的詳細差別](https://bbs.csdn.net/topics/310179371) 1. INNER JOIN 內部連接 (兩邊有符合資格的才連結) 2. LEFT (OUTER) JOIN 左外部連接 3. RIGHT (OUTER) JOIN 右外部連接 4. FULL (OUTER) JOIN 全部外部連接 5. CROSS JOIN 交叉連接 (不指定任何條件，即將兩個資料表中所有的可能排列組合出來) 6. NATURAL JOIN 自然連接 (兩資料表之間同名的欄位會被自動結合在一起) ```sql= -- INNER JOIN 內部連接方法一 -- SELECT table_column1, table_column2... -- FROM table_name1 -- INNER JOIN table_name2 -- ON table_name1.column_name=table_name2.column_name; SELECT customers.Name, orders.Order_No FROM customers INNER JOIN orders ON customers.C_Id=orders.C_Id; -- INNER JOIN 內部連接方法二 SELECT table_column1, table_column2... FROM table_name1 INNER JOIN table_name2 USING (column_name); ``` ![](https://i.imgur.com/bai3kE2.png) ```sql= -- LEFT JOIN 左外部連接 -- SELECT table_column1, table_column2... -- FROM table_name1 -- LEFT JOIN table_name2 -- ON table_name1.column_name=table_name2.column_name; SELECT customers.Name, orders.Order_No FROM customers LEFT JOIN orders ON customers.C_Id=orders.C_Id; ``` ![](https://i.imgur.com/9sTPrQa.png) ```sql= -- RIGHT JOIN 右外部連接 -- SELECT table_column1, table_column2··· -- FROM table_name1 -- RIGHT JOIN table_name2 -- ON table_name1.column_name=table_name2.column_name; SELECT customers.Name, orders.Order_No FROM customers RIGHT JOIN orders ON customers.C_Id=orders.C_Id; ``` ![](https://i.imgur.com/e5V4jAY.png) >MySQL 資料庫中沒有 FULL JOIN，但是可以用 UNION 來模擬。 ```sql= -- FULL JOIN 全部外部連接 -- SELECT table_column1, table_column2... -- FROM table_name1 -- FULL JOIN table_name2 -- ON table_name1.column_name=table_name2.column_name; SELECT customers.Name, orders.Order_No FROM customers FULL JOIN orders ON customers.C_Id=orders.C_Id; ``` ![](https://i.imgur.com/wqEkRD6.png) ```sql= -- CROSS JOIN 交叉連接(兩個資料表中所有的可能排列組合出來) -- SELECT table_column1, table_column2... -- FROM table_name1 -- CROSS JOIN table_name2; SELECT customers.Name, orders.Order_No FROM customers CROSS JOIN orders; ``` ![](https://i.imgur.com/r33ScBa.png) ```sql= -- NATURAL JOIN 自然連接 -- SELECT table_column1, table_column2... -- FROM table_name1 -- NATURAL JOIN table_name2; SELECT customers.Name, orders.Order_No FROM customers NATURAL JOIN orders; ``` ![](https://i.imgur.com/ci3kxso.png) ### 7. UNION 運算子聯集用來將兩個(以上) SQL 查詢的結果合併起來，而由 UNION 查詢中各別 SQL 語句所產生的欄位需要是相同的資料型別及順序。 UNION 與 JOIN 不同的地方在於，JOIN 是作橫向結合 (合併多個資料表的各欄位)；而 UNION 則是作垂直結合 (合併多個資料表中的紀錄)。 UNION 查詢返回的資料集欄位名稱，通常會依據第一個 SELECT 查詢的欄位名稱。 1. UNION 資料不重複合併 2. UNION ALL 重複合併 ```sql= -- UNION 資料不重複合併 -- SELECT column_name(s) FROM table_name1 -- UNION -- SELECT column_name(s) FROM table_name2; SELECT P_Name FROM products_taiwan UNION SELECT P_Name FROM products_china; -- UNION ALL 重複合併 SELECT P_Name FROM products_taiwan UNION ALL SELECT P_Name FROM products_china; ``` ### 8.INTERSECT 運算子交集 > MySQL 目前還不支援 INTERSECT。兩個 SELECT 查詢所產生的欄位需要是相同的資料型別及順序。 INTERSECT 查詢返回的資料集欄位名稱，通常會依據第一個 SELECT 查詢的欄位名稱。 ```sql= -- SELECT column_name(s) FROM table_name1 -- INTERSECT -- SELECT column_name(s) FROM table_name2; SELECT P_Name FROM products_taiwan INTERSECT SELECT P_Name FROM products_china; ``` ### 9.SELECT DISTINCT 可使用 DISTINCT 關鍵字過濾重複出現的紀錄值。 ```sql= -- SELECT DISTINCT table_column1, table_column2... -- FROM table_name; SELECT DISTINCT City FROM customers; ```