跳至内容

MySQL/语言/查询

Edit links
来自 Wikibooks，开放世界中的开放书籍

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 之前运行。以下是发生的情况

  1. 检索与 WHERE 子句匹配的记录
  2. 这些记录用于根据 GROUP BY 子句中定义的内容计算新记录
  3. 返回与 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);

参考文献

[编辑 | 编辑源代码]
  1. ↑ http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
  2. ↑ https://dev.mysqlserver.cn/doc/refman/5.0/en/information-functions.html
  3. ↑ https://dev.mysqlserver.cn/doc/refman/5.7/en/index-hints.html
  4. ↑ https://dev.mysqlserver.cn/doc/refman/5.7/en/join.html

资源

[编辑 | 编辑源代码]
检索自 "https://wikibooks.cn/w/index.php?title=MySQL/Language/Queries&oldid=3678607"
华夏公益教科书