查詢 · MySQL入門基礎

## 數據操作 - 查詢 SELECT 大概是 SQL 語言中最常用的語句，而且怎樣使用它也最為講究；用它來選擇記錄可能相當復雜，可能會涉及許多表中列之間的比較。本節介紹 SELECT 語句關于查詢的最基本功能。語法如下： SELECT column_list // 選擇哪些列 FROM table_list // 從何處選擇行 WHERE primary_constraint // 行必須滿足什么條件 GROUP BY grouping_columns // 怎樣對結果分組 HAVING secondary_constraint // 行必須滿足的第二條件 ORDER BY sorting_columns // 怎樣對結果排序 LIMIT count // 結果限定上述語法中除了 SELECT 關鍵字之外，其他每樣東西都是可選的。有些數據庫需要用 FROM 關鍵字，但是 MySQL 允許對表達式求值而不引用任何表。 **注意：** 所有使用的關鍵詞必須精確地以上面的順序給出。例如，一個 HAVING 子句必須跟在 GROUP BY 子句之后和 ORDER BY 子句之前。 ### 查詢所有數據 SELECT * FROM employee; // 查所有數據 SELECT employee_name,job_title FROM employee; // 查特定列使用星號（*）一些注意事項說明： - 它會返回所有列數據，但是可能部分列數據我們并不使用，MySQL 數據庫服務器和應用程序之間就會產生不必要的磁盤 I/O 和網絡流量。 - 如果明確指定列，結果集更可預測，更易于管理。試想一下，當有其他開發人員修改表結構并添加更多的列，查詢返回的結果集可能與您期望的就不一樣了。 - 可能會暴露敏感信息給未經授權的用戶。 ### 條件查詢 SELECT * FROM employee WHERE office_id = 1000; // 單個條件查詢 SELECT * FROM employee WHERE office_id = 1000 AND gender = '男'; // 多條件且關系查詢 SELECT * FROM employee WHERE office_id = 1000 OR office_id = 1001; // 多條件或關系查詢 SELECT * FROM employee WHERE report_to IS NULL; // NULL 判斷 SELECT * FROM employee WHERE report_to IS NOT NULL; // NOT NULL 判斷 SELECT * FROM employee WHERE office_id IN (1000,1001); // 范圍查詢 SELECT * FROM employee WHERE office_id NOT IN (1000,1001); // 范圍查詢 SELECT * FROM employee WHERE office_id >= 1000 AND office_id <= 1005; // 范圍查詢 SELECT * FROM employee WHERE office_id BETWEEN 1001 AND 1005; // 范圍查詢 SELECT * FROM employee WHERE office_id NOT BETWEEN 1001 AND 1005; // 范圍查詢 ### 模糊查詢 SELECT * FROM employee WHERE employee_name LIKE '%吳'; // %通配符代表任意多個字符 SELECT * FROM employee WHERE employee_name LIKE '%吳%'; SELECT * FROM employee WHERE employee_name LIKE '吳_'; // _通配符代表任意一個字符 ### 查詢排序 SELECT * FROM employee ORDER BY office_id ASC; // 按照升序排列 SELECT * FROM employee ORDER BY office_id DESC // 按照降序排列 SELECT * FROM employee ORDER BY office_id, dept_id; // 按照兩列進行排序，前面的為主要的 #### 分頁查詢 SELECT * FROM employee LIMIT 5; // 獲取前5條數據 SELECT * FROM employee LIMIT 0,5; // 獲取前5條數據 SELECT * FROM employee LIMIT 5,5; // 獲取第5-10條數據 SELECT * FROM employee LIMIT 5 OFFSET 5; // 獲取第5-10條數據 ### 去重查詢 SELECT DISTINCT status FROM `order`; // 查詢有那些訂單狀態 ### 統計函數（聚合函數） SELECT COUNT(*) FROM `order`; // 查詢表中有多少條數據 SELECT MAX(total_money) FROM `order`; // 取支付的最大值 SELECT MIN(total_money) FROM `order`; // 取支付的最小值 SELECT SUM(total_money) FROM `order`; // 取支付的總和 SELECT AVG(total_money) FROM `order`; // 取支付的平均值 **注意：** 如果表名或字段名是 MySQL 的保留字，需要用波浪號包裹起來，否則會引發錯誤。所以對表名以及字段命名請盡量避免 MySQL 保留字。參考鏈接：[MySQL保留字](https://dev.mysql.com/doc/refman/5.7/en/keywords.html) ### 分組查詢 SELECT status FROM `order` GROUP BY status; // 查詢有那些訂單狀態 SELECT status, COUNT(*) AS total FROM `order` GROUP BY status; // 查詢各類狀態的訂單有多少 SELECT status, COUNT(*) AS total FROM `order` GROUP BY status HAVING total > 50; 查詢各類狀態的訂單量, 要求訂單量大于50 **注意：** HAVING 子句篩選條件針對每一個分組的行，而 WHERE 子句的過濾條件是針對每個單獨的行。 ### 連接查詢 **內連接（INNER JOIN）** MySQL 的 INNER JOIN 子句的語法如下： SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ... 當我們使用 INNER JOIN 子句將 T1 表連接 T2 表：在 T1 表的每一行與 T2 表中的每一行進行比較，檢查是否它們都滿足連接條件。當連接條件匹配，它將返回在 T1 和 T2 表合并選擇列的行記錄。如果沒有找到匹配，查詢將返回一個空的結果集。下面的思維圖說明了 INNER JOIN 子句是如何工作的。結果集中的行必須出現在：T1 和 T2 兩個表中。 ![內連接原理圖](http://www.studymysql.com/uploads/images/161007/07141053_99799.png) 從 product 表中讀取產品代碼（product_code）和產品名稱（product_name）從 category 表中讀取分類名稱（name） - product 表中的每個產品都屬于 category 表中的一個分類； - category 表中每個分類在 product 表中有零個或多個產品； - category 和 product 是一對多的關系； ``` SELECT p.product_code, p.product_name, c.name AS category_name FROM product AS p INNER JOIN category AS c ON p.category_id = c.category_id LIMIT 10; ``` **左連接（LEFT JOIN）** SELECT column_list FROM t1 LEFT JOIN t2 ON join_condition1 LEFT JOIN t3 ON join_condition2 ... 當我們使用 LEFT JOIN 子句將 T1 表連接 T2 表：如果左表 T1 中的一行基于連接條件與右表 T2 一行相匹配，此行將被包括在結果集中。如果左表中的行不能匹配右表中的行，則左邊的表中的行也被選擇，并與右表中的一個“偽造”的行合并。這個”偽造”的行用 NULL 值填充。下面的思維圖說明了 LEFT JOIN 子句是如何工作的。兩個圓之間的交集表示那些符合兩個表中的行記錄，左圓的其余部分表示 T1 表沒有與 T2 表相匹配的記錄。左表中的所有行都會被包括在結果集中。 ![左連接原理圖](http://www.studymysql.com/uploads/images/161007/07211054_93390.png) 查找每個客戶自己的所有訂單： - order 表中的每一個訂單必須屬于 customer 表中的某一客戶； - customer 表中每個客戶在 order 表中有零個或多個訂單； - customer 和 order 是一對多的關系; ``` SELECT cs.customer_id, cs.customer_name, o.order_number, o.status FROM `customer` AS cs LEFT JOIN `order` AS o ON cs.customer_id = o.customer_id ORDER BY o.order_number ASC LIMIT 10; ``` **右連接（RIGHT JOIN）** 右連接是相對于左連接，和左連接原理類似。把主表和連接表互換，也即是左連接和右鏈接互換。 ``` SELECT cs.customer_id, cs.customer_name, o.order_number, o.status FROM `order` AS o RIGHT JOIN `customer` AS cs ON o.customer_id = cs.customer_id ORDER BY o.order_number ASC LIMIT 10; ``` **全連接（FULL JOIN）** 只要其中某個表存在匹配，FULL JOIN 關鍵字就會返回行。 ``` SELECT cs.customer_id, cs.customer_name, o.order_number, o.status FROM `order` AS o FULL JOIN `customer` AS cs ON o.customer_id = cs.customer_id ORDER BY o.order_number ASC LIMIT 10; ``` **自連接（SELF JOIN）** 自連接是一個比較 “特殊” 的連接方式，它自己連接自已本身。當我們想在同一個表中行記錄上結合多列，可以考慮使用自連接。要執行自連接操作，必須要使用表的別名，以幫助 MySQL 從同一個表名稱中區分左表和右表。查找雇員以及雇員的直屬領導：在 employee 表中，我們不僅存儲員工的數據，還有組織結構的數據。*report_to* 字段是用來存儲這個雇員的管理者的ID。為了獲得整個組織結構，我們可以在 *employee_id* 和 *report_to* 這兩個字段上連接 employee 表本身。 ``` SELECT e.office_id, e.employee_name AS '雇員姓名', m.employee_name AS '直屬領導' FROM employee AS e LEFT JOIN employee AS m ON m.employee_id = e.report_to ORDER BY e.office_id, m.employee_name; ``` ### 子查詢 MySQL 的子查詢是嵌套在另一個查詢中的查詢，子查詢也稱為內部查詢，那些包含子查詢的查詢被稱為外部查詢。可以在任何地方，在一個表達式中使用子查詢。此外，必須用括號將子查詢包起來。查找單筆支付額最大的支付信息： ``` SELECT * FROM payment WHERE total_money = ( SELECT MAX(total_money) FROM payment ); ``` 查找所有在廣州辦公的雇員信息： ``` SELECT * FROM employee WHERE office_id IN ( SELECT office_id FROM office WHERE city = '廣州' ); ``` 查找所有下過訂單的客戶信息： ``` SELECT * FROM customer WHERE EXISTS ( SELECT * FROM `order` WHERE customer.customer_id = `order`.customer_id ); ``` 查找所有未下過訂單的客戶信息： ``` SELECT * FROM customer WHERE NOT EXISTS ( SELECT * FROM `order` WHERE customer.customer_id = `order`.customer_id ); ``` exists 對外表逐條 loop 查詢，每次查詢都會查看 exists 的條件語句，當 exists 里的條件語句能夠返回記錄行時，返回當前 loop 到的這條記錄，反之這條記錄被丟棄。 ### 合并查詢 MySQL UNION 運算符允許從多個表查詢出來的結果集組合成一個結果集。語法如下： SELECT column1,column2 UNION[DISTINCT|ALL] SELECT column1,column2 UNION[DISTINCT|ALL] 查找編號為 1001 的顧客 2015-2017 每年前 10 條訂單信息： ``` SELECT order_id,customer_id,order_date FROM order_2015 WHERE customer_id = 1001 LIMIT 10 UNION ALL SELECT order_id,customer_id,order_date FROM order_2016 WHERE customer_id = 1001 LIMIT 10 UNION ALL SELECT order_id,customer_id,order_date FROM order_2017 WHERE customer_id = 1001 LIMIT 10 ``` 使用 UNION 的注意點： - SELECT 語句列數量必須相等。 - SELECT 語句的列要有相同的數據類型，或至少是可轉換的數據類型。 UNION DISTINCT 從結果集中清除重復行，UNION ALL 明確地保留重復行，UNION ALL 性能比 UNION DISTINCT 更好。參考鏈接： - [MySQL 子查詢](http://www.studymysql.com/mysql/subquery.html) - [MySQL UNION 用法](http://www.studymysql.com/mysql/union.html) - [MySQL 中 EXISTS 與 IN 的使用](http://www.cnblogs.com/beijingstruggle/p/5885137.html)