跳至內容

MySQL/Language/Queries

新增連結
維基教科書，自由的教學讀本

SELECT

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

列出字段（field）

[編輯]

SELECT子句允許任何SQL表達式： 

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

表中所有列： 

 SELECT * FROM `stats`

表的名字

[編輯]
 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

[編輯]
 SELECT * FROM `stats` WHERE `id`=42

 SELECT * FROM `antiques` WHERE buyerid IS NOT NULL

GROUP BY

[編輯]

所有行通過一列或多列分組。在每個分組上用某個聚集函數（aggregate function）計算分組的值。 


 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子句的分組施加過濾。各種子句的運行先後次序：

  1. WHERE子句先過濾。
  2. GROUP BY子句做分組。
  3. HAVING子句對分組做過濾。可以使用聚集函數，不能使用索引，不能被優化。

不正確的使用HAVING的例子： 

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

不正確的使用HAVING的例子： 

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

雖然其結果正確，但更優化的方案是用WHERE子句來做sex='m'過濾。

正確的使用HAVING的例子： 

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

ORDER BY

[編輯]
 SELECT * FROM `stats` ORDER BY `id`

缺省為ASCENDING. 可指示為DESCENDING： 

 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

通常與ORDER BY配合使用。

也可以得到隨機排序的行數： 

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

可以指定從哪行開始返回指定數量的行。首行編號為0： 

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

可以對結果集做分頁： 

 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

檢查查詢語句的有效性，但不需要返回結果： 

 SELECT ... LIMIT 0

優化提示：

  • SQL_CALC_FOUND_ROWS可加速查詢[1][2]
  • LIMIT對於使用ORDER BY, DISTINCT,GROUP BY特別有效，因為不需要考慮所有行。
  • 如果服務器把查詢結果在內部存放在一個臨時表中，LIMIT有助於確定臨時表耗用多少內存。

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`

如果結果集中某個列是主鍵、獨一無二索引，則DISTINCT是無用的。對於使用了GROUP BY子句，DISTINCT是無用的。

IN and 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，ANY

[編輯]
 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)


  • some是any的別名
  • not in 是 「<>all」的別名，用法相同。
  • in 與「=any」是相同的。

SELECT的優化hint關鍵字

[編輯]
 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把結果集保存在臨時表中，這有助於儘可能避免LOCK

SQL_CACHE 迫使MySQL把結果集保存在查詢cache中。只用於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();

索引的hint關鍵字

[編輯]
  • USE INDEX: 使用索引
  • 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

[編輯]

返回兩種表中所有行： 

 SELECT * FROM english 
 UNION ALL
 SELECT * FROM hindi

UNION同義 UNION DISTINCT.
使用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)

Joins

[編輯]

多表之間的操作，可以用JOIN，也可以用子查詢。

首先創建一個數據庫： 

 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
TagInenglish Tag Inhindi
1 One 2 Do
2 Two 3 Teen
3 Three 4 Char

Inner Join

[編輯]
 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
Tag Inenglish Inhindi
2 Two Do
3 Three Teen

MySQL, JOIN同義於INNER JOIN或CROSS JOIN (笛卡爾積)[3].

笛卡爾積： 

 SELECT * FROM english, hindi

也可以寫為： 

 SELECT * FROM english CROSS JOIN hindi
TagInenglishTag Inhindi
1 One 2 Do
2 Two 2 Do
3 Three 2 Do
1 One 3 Teen
2 Two 3 Teen
3 Three 3 Teen
1 One 4 Char
2 Two 4 Char
3 Three 4 Char

Natural Join

[編輯]

Natural Join給出INNER JOIN在兩張表的同名的列上。 

 SELECT hindi.tag, hindi.Inhindi, english.Inenglish
 FROM hindi NATURAL JOIN english

Outer Joins

[編輯]
Tag Inenglish Tag Inhindi
1 One   
2 Two 2 Do
3 Three 3 Teen
    4 Char

Left Join / Left Outer Join

[編輯]
 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-

Right Outer Join

[編輯]
 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

English   tag     Hindi
--no row--   4      Char

Full Outer Join

[編輯]

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)

Multiple joins

[編輯]

多個表的連接： 

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

例如： 

 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)

子查詢

[編輯]

子查詢出現在 WHERE (或HAVING) 子句中。只有一個列可以出現在子查詢的SELECT語句中，即子查詢的結果集只能含一列。子查詢禁用ORDER BY。通常子查詢引用主表的一個列名，即子查詢在主表的一行（當前行）上執行，這稱為外引用（outer reference）。

例如，查詢銷售辦公室的目標值大於其所有銷售代表完成的銷量之和： 

 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)

參考文獻

[編輯]
  1. ↑ http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
  2. ↑ http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
  3. ↑ https://dev.mysql.com/doc/refman/5.7/en/join.html

資源

[編輯]
取自「https://zh.wikibooks.org/w/index.php?title=MySQL/Language/Queries&oldid=133974」