MySQL 查询专题

全量查询语句

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING xxx
ORDER BY column_name1;
LIMIT 5

全量关键字

SELECT 
FROM
WHERE 
GROUP BY
HAVING
ORDER BY

注意：SQL可以只通过列名引用列。也可能会使用完全限定的名字来引用列。

WHERE 过滤数据

MySQL中可根据需要使用很多条件操作符和操作符的组合。为了检查某个范围的值，可使用BETWEEN操作符。

注意：是！=还是<>？！=和<>通常可以互换。但是，并非所有 DBMS 都支持这两种不等于操作符。如果有疑问，请参阅相应的 DBMS 文档。

SELECT语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个WHERE子句就是 ISNULL 子句。

NULL 关键字

NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

NULL 与不匹配
在通过过滤选择出不具有特定值的行时，你可能希望返回具有 NULL 值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有 NULL 的行。

计算次序
WHERE 可包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂和高级的过滤。

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

在WHERE子句中使用圆括号 任何时候使用具有 AND 和 OR 操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。

为什么要使用IN操作符？
其优点具体如下。
❑ 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
❑ 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
❑ IN操作符一般比OR操作符清单执行更快。
❑ IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
❑ IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

NOT操作符
WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

GROUP BY 创建分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

在使用 GROUP BY 子句前，需要知道一些重要的规定。
❑ GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
❑ 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
❑ GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
❑ 大多数SQL实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。
❑ 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
❑ 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
❑ GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

WITH ROLLUP：在 GROUP 分组字段的基础上再进行统计数据。示例：

select vend_id , prod_price ,count(*)  from `Products` group by vend_id, prod_price WITH ROLLUP

+---------+------------+----------+
| vend_id | prod_price | count(*) |
+---------+------------+----------+
| BRS01   | 5.99       | 1        |
| BRS01   | 8.99       | 1        |
| BRS01   | 11.99      | 1        |
| BRS01   | <null>     | 3        |
| DLL01   | 3.49       | 3        |
| DLL01   | 4.99       | 1        |
| DLL01   | <null>     | 4        |
| FNG01   | 9.49       | 2        |
| FNG01   | <null>     | 2        |
| <null>  | <null>     | 9        |
+---------+------------+----------+
10 rows in set

HAVING 过滤分组

HAVING 非常类似于 WHERE。事实上，目前为止所学过的所有类型的 WHERE子句都可以用 HAVING 来替代。唯一的差别是，WHERE 过滤行，而 HAVING 过滤分组。

HAVING 和 WHERE 的差别 这里有另一种理解方法，WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。这是一个重要的区别，WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

说明：和 WHERE HAVING 与 WHERE 非常类似，如果不指定 GROUP BY，则大多数 DBMS 会同等对待它们。不过，你自己要能区分这一点。使用 HAVING 时应该结合GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

ORDER BY 排序数据

若不使用 ORDER BY，检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

ASC 是 ASCENDING 的缩写，是默认行为。而 DESC 是 DESCENDING 的缩写。

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 在指定一条 ORDER BY 子句时，应该保证它是SELECT语句中最后一条子句，否则这将报错
• 不限制是否使用非选择列进行排序
• 除了能用列名指出排序顺序外，ORDER BY 还支持按相对列位置进行排序，下标从 0 开始，当根据不出现在 SELECT 清单中的列进行排序时，不能采用这项技术
• 如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。在升序排序时可以指定它。但实际上，指定 ASC 没有多大用处
• 在对文本性数据进行排序时，A 与a 相同吗？a位于 B 之前，还是Z之后？这些问题不是理论问题，其答案取决于数据库的设置方式。

limit 关键字

行0 检索出来的第一行为行 0 而不是行 1。因此，LIMIT 1, 1 将检索出第二行而不是第一行。

MySQL 5 的 LIMIT语法 LIMIT 3, 4 的含义是从行 3 开始的 4 行，这容易把人搞糊涂。由于这个原因，MySQL 5 支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3 意为从行 3 开始取 4 行，就像LIMIT 3, 4 一样。

子查询

版本要求 MySQL 4.1 引入了对子查询的支持，所以要想使用本章描述的 SQL，必须使用MySQL 4.1 或更高级的版本。

子查询通常用于过滤

select cust_id  from orders where order_num in (
    select distinct order_num  from orderitems where item_price >= 10
)

列必须匹配 在 WHERE 子句中使用子查询（如这里所示），应该保证SELECT语句具有与 WHERE 子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

作为计算字段使用的成为相关子查询

select cust_email from customers where cust_id in 
(select cust_id  from orders where order_num in 
  (select order_num from orderitems where prod_id  = 'BR01' 
  ) 
)

注意：只能是单列作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

tip: 逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与 MySQL 处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

一对一关系 (夫妻关系)
从表的主键即是外键

一对多关系(部门和职员的关系)
从表有一个键作为外键

多对多(学生老师关系)
需要一个中间表, 然后指定两个外键

一般主表的记录数会少. 因为主要是一对多的关系. 主表是一.

合并结果集 union
要求两个表的列数 和 列类型 完全一致

连接查询

内连接

方言版

select xxx列 from 表A, 表b where 条件1=xxx

标准版 逗号改成inner join, where 改成 on

select xxx列 from 表A inner join 表b on 条件1=xxx

自然版

sql 的一对一, 多对多关系

子查询

• 有多个 select 关键字
• 可以出现的位置 select , 表名, where。其中出现在在select位置不推荐
• 出现在表名表示是临时表, 出现在where 条件则是作为一个判断条件的一部分

单行单列 select * from 表1 别名1 where 列1 [=, >, <, <=, >=, !=] (select 列 from 表名2 别名2 where 条件)

多行单列 select * from 表1 别名1 where 列1 [IN, ALL, ANY] (select 列 from 表名2 别名2 where 条件)

单行多列 select * from 表1 别名1 where (列1, 列2) in (select 列1, 列2 from 表2 别名2 where 条件)
很少见, 看上去像对象

多行多列 select * from 表1 别名1 , (select ... ) 别名2 where 条件

所谓的连接是有针对性的找出关联关系

全文搜索

用基于文本的搜索作为正则表达式匹配列值的更进一步的介绍。使用正则表达式，可以编写查找所需行的非常复杂的匹配模式。虽然这些搜索机制非常有用，但存在几个重要的限制。
❑ 性能——通配符和正则表达式匹配通常要求 MySQL 尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
❑ 明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
❑ 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL 创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL 可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。