MySQL/語言/查詢

SELECT

select 語法如下

 SELECT *
 FROM a_table_name
 WHERE condition
 GROUP BY grouped_field
 HAVING group_name condition
 ORDER BY ordered_field
 LIMIT limit_number, offset

欄位列表

您必須在 SELECT 子句中指定要檢索的資料

 SELECT DATABASE() -- returns the current db's name
 SELECT CURRENT_USER() -- returns your username
 SELECT 1+1 -- returns 2

此處允許使用任何 SQL 表示式。

您還可以檢索表中的所有欄位

 SELECT * FROM `stats`

如果您只選擇必要的欄位，查詢速度會更快。

表的名稱

如果您要從表或檢視中檢索結果，通常在 FROM 子句中指定表的名稱

 SELECT id FROM `stats` -- retrieve a field called id from a table called stats

或者

 SELECT MAX(id) FROM `stats`
 SELECT id*2 FROM `stats`

您還可以使用 `db_name`.`table_name` 語法

 SELECT id FROM `sitedb`.`stats`

但是您也可以在 SELECT 子句中指定表的名稱

 SELECT `stats`.`id` -- retrieve a field called id from a table
 SELECT `sitedb`.`stats`.`id`

WHERE

您可以設定過濾器來決定必須檢索哪些記錄。

例如，您可以只檢索 id 為 42 的記錄

 SELECT * FROM `stats` WHERE `id`=42

或者您可以讀取多條記錄

 SELECT * FROM `antiques` WHERE buyerid IS NOT NULL

GROUP BY

您可以按一個或多個欄位對所有記錄進行分組。對於該欄位具有相同值的記錄將分組到一個計算出的記錄中。您只能選擇分組後的記錄和一些聚合函式的結果，這些結果將在每個組的所有記錄上計算。

例如，以下操作將按 `city` 欄位對 `users` 表中的所有記錄進行分組。對於居住在同一城市的每組使用者，將返回最大年齡、最小年齡和平均年齡

 SELECT city, MAX(age), MIN(age), AVG(age) GROUP BY `city`

在下面的示例中，使用者按城市和性別分組，以便我們瞭解每個城市中男性/女性使用者的最大、最小和平均年齡

 SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city`, `sex`

HAVING

HAVING 子句聲明瞭一個過濾器，用於由 GROUP BY 子句計算的記錄。它與 WHERE 子句不同，WHERE 子句在 GROUP BY 之前執行。以下是發生的情況

檢索與 WHERE 子句匹配的記錄
這些記錄用於根據 GROUP BY 子句中定義的內容計算新記錄
返回與 HAVING 條件匹配的新記錄

這意味著 WHERE 決定哪些記錄用於組成新的計算記錄。

HAVING 決定返回哪些計算記錄，因此它可以在聚合函式的結果上執行。HAVING 沒有最佳化，不能使用索引。

HAVING 的不正確使用

 SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city` HAVING sex='m'

這可能會產生錯誤的結果。MAX(age) 和其他聚合計算使用所有值進行計算，即使記錄的 sex 值為 'f'。這不太可能是預期的結果。

HAVING 的不正確使用

 SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city`, `sex` HAVING sex='m'

這是正確的，並返回預期的結果，但此查詢的執行沒有最佳化。可以使用並且應該使用 WHERE 子句，以便 MySQL 不計算稍後被排除的記錄。

HAVING 的正確使用

 SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city` HAVING MAX(age) > 80

它必須對所有記錄進行分組，因為在執行 GROUP BY 子句之前無法確定每個城市的最高年齡。稍後，它只返回 MAX(age)>80 的記錄。

ORDER BY

您可以為檢索到的記錄設定任意順序。順序可以是字母順序或數字順序。

 SELECT * FROM `stats` ORDER BY `id`

預設情況下，順序為升序。您還可以指定順序必須為降序

 SELECT * FROM `stats` ORDER BY `id` ASC -- default
 SELECT * FROM `stats` ORDER BY `id` DESC -- inverted

NULL 值被視為小於任何其他值。

您還可以指定欄位位置，而不是欄位名稱

 SELECT `name`, `buyerid` FROM `antiques` ORDER BY 1 -- name
 SELECT `name`, `buyerid` FROM `antiques` ORDER BY 2 -- buyerid
 SELECT `name`, `buyerid` FROM `antiques` ORDER BY 1 DESC

允許使用 SQL 表示式

 SELECT `name` FROM `antiques` ORDER BY REVERSE(`name`)

您可以以隨機順序檢索記錄

 SELECT `name` FROM `antiques` ORDER BY RAND()

如果指定了 GROUP BY 子句，則結果將按 GROUP BY 中命名的欄位排序，除非存在 ORDER BY 子句。您甚至可以在 GROUP BY 子句中指定順序是升序還是降序

 SELECT city, sex, MAX(age) GROUP BY `city` ASC, `sex` DESC

如果您有 GROUP BY 但不希望記錄排序，可以使用 ORDER BY NULL

 SELECT city, sex, MAX(age) GROUP BY `city`, `sex` ORDER BY NULL

LIMIT

您可以指定要讀取的行數最大值

 SELECT * FROM `antiques` ORDER BY id LIMIT 10

此語句最多返回 10 行。如果少於 10 行，則返回找到的行數。limit 子句通常與 ORDER BY 一起使用。

您可以獲取給定數量的隨機記錄

 SELECT * FROM `antiques` ORDER BY rand() LIMIT 1 -- one random record
 SELECT * FROM `antiques` ORDER BY rand() LIMIT 3

您可以指定在開始返回找到的記錄之前應跳過多少行。第一條記錄是 0，而不是 1

 SELECT * FROM `antiques` ORDER BY id LIMIT 10
 SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10 -- synonym

您可以使用 LIMIT 子句獲取結果的分頁

 SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10 -- first page
 SELECT * FROM `antiques` ORDER BY id LIMIT 10, 10 -- second page
 SELECT * FROM `antiques` ORDER BY id LIMIT 20, 10 -- third page

此外，以下語法是可以接受的

 SELECT * FROM `antiques` ORDER BY id LIMIT 10 OFFSET 10

您可以使用 LIMIT 子句檢查查詢的語法，而無需等待它返回結果

 SELECT ... LIMIT 0

最佳化技巧

SQL_CALC_FOUND_ROWS 可能會加快查詢速度^[1]^[2]
LIMIT 對於使用 ORDER BY、DISTINCT 和 GROUP BY 的 SELECT 特別有用，因為它們的計算不必涉及所有行。
如果查詢透過伺服器將結果內部複製到臨時表來解決，則 LIMIT 可以幫助 MySQL 計算表所需的記憶體量。

DISTINCT

DISTINCT 關鍵字可用於從結果集中刪除所有重複行

 SELECT DISTINCT * FROM `stats` -- no duplicate rows
 SELECT DISTINCTROW * FROM `stats` -- synonym
 SELECT ALL * FROM `stats` -- duplicate rows returned (default)

您可以使用它來獲取一個欄位中包含的所有值的列表

 SELECT DISTINCT `type` FROM `antiques` ORDER BY `type`

或者您可以使用它來獲取某些值的現有組合

 SELECT DISTINCT `type`, `age` FROM `antiques` ORDER BY `type`

如果您選擇的欄位之一是 PRIMARY KEY 或具有 UNIQUE 索引，則 DISTINCT 無用。此外，在與 GROUP BY 子句結合使用時，使用 DISTINCT 也無用。

IN 和 NOT IN

 SELECT id
 FROM stats
 WHERE position IN ('Manager', 'Staff')

 SELECT ownerid, 'is in both orders & antiques'
 FROM orders, antiques WHERE ownerid = buyerid
 UNION
 SELECT buyerid, 'is in antiques only'
 FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)

EXISTS 和 ALL

(相容：Mysql 4+)

 SELECT ownerfirstname, ownerlastname
 FROM owner 
 WHERE EXISTS (SELECT * FROM antiques WHERE item = 'chair')

 SELECT buyerid, item 
 FROM antiques
 WHERE price = ALL (SELECT price FROM antiques)

最佳化提示

您可以提供一些提示給伺服器，以便更好地最佳化 SELECT 語句。如果您提供多個提示，關鍵字的順序很重要。

 SELECT [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY] [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT | SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    ...

HIGH_PRIORITY

通常，DML 命令（INSERT、DELETE、UPDATE）優先順序高於 SELECT 語句。但是，如果您指定 HIGH_PRIORITY，則 SELECT 語句的優先順序將高於 DML 語句。

STRAIGHT_JOIN 強制 MySQL 按命名順序（從左到右）評估 JOIN 中的表。

SQL_SMALL_RESULT 僅在使用 DISTINCT 或 GROUP BY 時有用。告訴最佳化器查詢將返回少量行。

SQL_BIG_RESULT 僅在使用 DISTINCT 或 GROUP BY 時有用。告訴最佳化器查詢將返回大量行。

SQL_BUFFER_RESULT 強制 MySQL 將結果複製到臨時表中。這有助於儘快解除鎖。

SQL_CACHE 強制 MySQL 將結果複製到查詢快取中。僅當 query_cache_type 的值為 DEMAND 或 2 時才有效。

SQL_NO_CACHE 告訴 MySQL 不要快取結果。如果查詢很少發生或結果經常更改，則此選項很有用。

SQL_CALC_FOUND_ROWS 在使用 LIMIT 子句時很有用。告訴伺服器計算如果沒有 LIMIT 將返回多少行。您可以使用另一個查詢檢索該數字。

 SELECT SQL_CALC_FOUND_ROWS * FROM `stats` LIMIT 10 OFFSET 100;
 SELECT FOUND_ROWS();

索引提示

USE INDEX：指定優先透過瀏覽表索引來查詢某些記錄^[3]。
FORCE INDEX：更嚴格的同義詞。只有當最佳化器別無選擇時，才會不使用索引瀏覽表。
IGNORE INDEX：請求不優先使用索引。

示例

SELECT *
FROM table1 USE INDEX (date)
WHERE date between '20150101' and '20150131'

SELECT *
FROM table1 IGNORE INDEX (date)
WHERE id between 100 and 200

UNION 和 UNION ALL

(相容：Mysql 4+)

以下查詢將返回兩個表中的所有記錄。

 SELECT * FROM english 
 UNION ALL
 SELECT * FROM hindi

UNION與以下相同UNION DISTINCT.
如果您只鍵入 UNION，則表示您正在請求不同的記錄。如果您想要所有記錄，則必須使用UNION ALL.

 SELECT word FROM word_table WHERE id = 1
 UNION
 SELECT word FROM word_table WHERE id = 2

 (SELECT magazine FROM pages)
 UNION DISTINCT
 (SELECT magazine FROM pdflog)
 ORDER BY magazine

 (SELECT ID_ENTRY FROM table WHERE ID_AGE = 1)
 UNION DISTINCT 
 (SELECT ID_ENTRY FROM table WHERE ID_AGE=2)

連線

SQL 最重要的方面是其關係功能。您可以查詢、比較和計算兩個具有完全不同結構的不同表。連線和子查詢是連線表的兩種方法。連線表的這兩種方法都應該給出相同的結果。在大多數 SQL 平臺上，自然連線更快。

在以下示例中，一名學生試圖學習印地語中數字的叫法。

 CREATE TABLE english (Tag int, Inenglish varchar(255));
 CREATE TABLE hindi (Tag int, Inhindi varchar(255));

 INSERT INTO english (Tag, Inenglish) VALUES (1, 'One');
 INSERT INTO english (Tag, Inenglish) VALUES (2, 'Two');
 INSERT INTO english (Tag, Inenglish) VALUES (3, 'Three');

 INSERT INTO hindi (Tag, Inhindi) VALUES (2, 'Do');
 INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');
 INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');

	select * from english		select * from hindi
標籤	英文	標籤	印地語
1	一	2	二
2	三	3	四
3	五	4	六

內連線

 SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
 FROM english, hindi
 WHERE english.Tag = hindi.Tag
 -- equal
 SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
 FROM english INNER JOIN hindi ON english.Tag = hindi.Tag

標籤	英文	印地語
2	三	二
3	五	四

您也可以將相同的查詢寫成

 SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
 FROM english INNER JOIN hindi
 ON english.Tag = hindi.Tag

備註：在 MySQL 中，JOIN 等效於 INNER JOIN 和 CROSS JOIN（笛卡爾積）^[4]。

笛卡爾積是在將一個表中的每一行與另一個表中的每一行連線起來時發生的。

 SELECT * FROM english, hindi

它也稱為交叉連線，可以用這種方式編寫

 SELECT * FROM english CROSS JOIN hindi

標籤	英文	標籤	印地語
1	一	2	二
2	三	2	二
3	五	2	二
1	一	3	四
2	三	3	四
3	五	3	四
1	一	4	六
2	三	4	六
3	五	4	六

自然連線

自然連線給出與所有兩個表公共列上的INNER JOIN相同的結果。

以下使用“USING”方法的語句將顯示與之前的INNER JOIN相同的結果（相容：MySQL 4+；但在 MySQL 5 中已更改）。

 SELECT hindi.tag, hindi.Inhindi, english.Inenglish
 FROM hindi NATURAL JOIN english
 USING (Tag)

外連線

標籤	英文	標籤	印地語
1	一
2	三	2	二
3	五	3	四
		4	六

左連線 / 左外連線

語法如下

 SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2

 SELECT e.Inenglish as English, e.Tag, '--no row--' as Hindi
 FROM english AS e LEFT JOIN hindi AS h
 ON e.Tag=h.Tag 
 WHERE h.Inhindi IS NULL

English  tag   Hindi
One      1     --no row-

右外連線

 SELECT '--no row--' AS English, h.tag, h.Inhindi AS Hindi
 FROM english AS e RIGHT JOIN hindi AS h
 ON e.Tag=h.Tag
 WHERE e.Inenglish IS NULL

英文標籤印地語 --無行-- 4 六

確保兩個表中具有相同的名稱和相同的資料型別。
關鍵字 LEFT 和 RIGHT 不是絕對的，它們僅在給定語句的上下文中起作用：我們可以反轉表的順序並反轉關鍵字，結果將相同。
如果連線型別未指定為內部或外部，則將作為內部連線執行。

全外連線

對於 v5.1，MySQL 不提供 FULL OUTER JOIN。您可以透過以下方式模擬它

     (SELECT a.*, b* 
         FROM tab1 a LEFT JOIN tab2 b
         ON a.id = b.id)
 UNION
     (SELECT a.*, b* 
         FROM tab1 a RIGHT JOIN tab2 b
         ON a.id = b.id)

多連線

可以連線兩個以上的表。

 SELECT ... FROM a JOIN (b JOIN c on b.id=c.id) ON a.id=b.id

這是一個來自Savane的示例

 mysql> SELECT group_type.type_id, group_type.name, COUNT(people_job.job_id) AS count 
        FROM group_type
         JOIN (groups JOIN people_job ON groups.group_id = people_job.group_id) 
         ON group_type.type_id = groups.type
        GROUP BY type_id ORDER BY type_id
 +---------+--------------------------------------+-------+
 | type_id | name                                 | count |
 +---------+--------------------------------------+-------+
 |       1 | Official GNU software                |   148 |
 |       2 | non-GNU software and documentation   |   268 |
 |       3 | www.gnu.org portion                  |     4 |
 |       6 | www.gnu.org translation team         |     5 |
 +---------+--------------------------------------+-------+
 4 rows in set (0.02 sec)

子查詢

(相容：MySQL 4.1 及更高版本)

SQL 子查詢允許您將一個查詢的結果用作另一個查詢的一部分。
子查詢通常是編寫語句的自然方式。
允許您將查詢分解成多個部分並進行組合。
允許一些無法構造的查詢。如果不使用子查詢，則必須分兩步進行。
子查詢始終作為 WHERE（或 HAVING）子句的一部分出現。
子查詢 SELECT 中只能有一個欄位。這意味著子查詢只能將其結果作為單個數據列。
不允許使用 ORDER BY；這沒有意義。
通常引用子查詢中主表列的名稱。
這定義了正在為其執行子查詢的主表的當前行。這稱為外部引用。

例如，如果 RepOffice=Offices 表中的 OfficeNbr，請列出銷售配額超過各個銷售人員配額之和的辦事處。

 SELECT City FROM Offices WHERE Target > ???

??? 是銷售人員配額的總和，即

 SELECT SUM(Quota)
 FROM SalesReps 
 WHERE RepOffice = OfficeNbr

我們將這些組合起來得到

 SELECT City FROM Offices 
 WHERE Target > (SELECT SUM(Quota) FROM SalesReps 
 WHERE RepOffice = OfficeNbr)

顯示所有有訂單或信用額度 > 50,000 美元的客戶。使用 DISTINCT 關鍵字僅列出客戶一次。

 SELECT DISTINCT CustNbr 
 FROM Customers, Orders 
 WHERE CustNbr = Cust AND (CreditLimit>50000 OR Amt>50000);

參考文獻

資源

MySQL 官方文件

[1] ttp://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

[2] ttps://dev.mysql.com.tw/doc/refman/5.0/en/information-functions.html

[3] ttps://dev.mysql.com.tw/doc/refman/5.7/en/index-hints.html

[4] ttps://dev.mysql.com.tw/doc/refman/5.7/en/join.html

[1]

[2]

[3]

[4]