维基教科书

MySQL/Language/Queries

< MySQL

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”