I love you three thousand

# 查询语句

你可能使用得最频繁的 SQL 语句是 SELECT 语句，其用途是从一个或多个表中检索信息。

增删改查 四大操作中，查 操作与 增删改 操作的总和的比为 7:1 到 9:1 之间。

SELECT 操作属于 DML 和 DQL 两大类语句中的 DQL 。

要使用 SELECT 检索表中的数据，至少必须指定两组信息：

  • 你想得到什么
  • 你想从哪里得到它

# 1. Select 语句

# 检索单独的列

SELECT <列名> FROM <表名>;

SELECT 语句从 指定表 中检索单个 指定列 的数据。

  • 想要的 列名称 紧接 SELECT 关键字之后指定 。

  • FROM 关键字指定要从中检索数据的 表名称 。

查询出来的数据没有进行过滤，也没有进行排序。

注意，必须用分号（;）作为 SQL 语句的结束标记。

SQL 语句不区分大小写，因此 SELECT 等同于 select，也等同于 Select。但是，约定俗成的习惯是：

  • 所有 SQL 关键字使用全大写字母；

  • 列名和表名使用全小写字母

# 检索多列

SELECT <列名1>, <列名2>, ... FROM <表名>;

从表中检索多列，可以使用相同的 SELECT 语句。唯一的区别是：必须在 SELECT 关键字后面指定多个列名称，并且用逗号（,）把每个列隔开。

注意，最后一个列名称后面不需要逗号。

# 检索所有列

SELECT * FROM <表名>;

除了指定单列和多列以外，还可以使用 SELECT 语句请求所有列，而不必单独列出它们。

这是通过使用星号（*）通配符替代实际的列名做到的。

不过，一般来说，在实际工作中最好不要使用 * 通配符，除非是确实需要表中的每一列。因为检索不必要的列通常会降低检索效率。

# 使用完全限定的表名

表的完全限定名：库名.表名

列的完全限定名：表名.列名

# 2. 对检索的数据进行排序

# 对数据进行排序

SELECT * FROM <表名> ORDER BY <列名>;

如果不进行排序，那么数据是以它们加到表中的顺序排列显示。

如果需要显示地对检索出的数据进行排序，可以使用 ORDER BY 子句。

ORDER BY 用于获取一列或多列的名称，并通过它们对数据进行排序。

# 按多个列进行排序

SELECT * FROM <表名> ORDER BY <列名1>, <列名2>, ...;

要按多个列进行排序，可以简单地指定列名，并用逗号隔开它们。

在以第一个列进行排序出现相等情况是，会以第二列为条件对相关行再进行排序，并以此类推。

# 指定排序方向

SELECT * FROM <表名> ORDER BY <列名> DESC;

默认情况下，排序是以自然顺序进行。数字以从小到大，字母以字典顺序

要按降序排序，必须指定关键字 DESC 。

注意，DESC 关键字只对直接位于它之前的列名起作用。如果想要在多个列上以降序排序，就要确保没个列都具有它自己的 DESC 关键字。

升序的关键字是 ASC，不过，在实际中通常不使用它。

# 3. 过滤数据

SELECT * FROM <表名> WHERE <过滤条件>;

只检索想要的特定的数据，这需要指定 搜索条件，也称为 过滤条件。

WHERE 子句的运算符

运算符 描述
= 相等性
!= 不等性
<> 不等性
< 小于
<= 小于或等于
> 大于
>= 大于或等于
BETWEEN...AND... 在两个指定值之间

当在 WHERE 条件中使用日期概念时，可以书写成字符串形式（即使用单引号引住），但是该字符串必须合法特定的格式：'yyyy-MM-dd' 。

表示日期概念的字符串可以进行大小判断。

在创建表时，标设计者可以指定单独的列中是否允许为空，即不包含任何值。

当某一列没有包含任何值时，则称之为 NULL 。

注意，没有值（No Value），并不是 0、空字符串 或者 空白字符串 。

SELECT 语句具有一个特殊的 WHERE 子句判断：IS NULL。可以使用它检索具有 NULL 值得列：

SELECT <列名> FROM <表名> WHERE <列名> IS NULL;

于此同时，还有 IS NOT NULL 判断：检索非空。

# 4. 高级数据过滤

SELECT * FROM <表名> WHERE <过滤条件1> AND <过滤条件2>;
SELECT * FROM <表名> WHERE <过滤条件1> OR <过滤条件2>;

除了用单个条件过滤数据，为了对过滤进行更大程度的控制，MySQL 允许指定多个 WHERE 子句。

可以用两种方式使用这些子句：作为 AND 子句，或者，作为 OR 子句。

AND 运算符相当于在给 WHERE 子句 追加条件 。

OR 运算符指示检索与任意一个条件匹配的行。

和编程有类似的情况，AND 和 OR 的优先级并非同级。AND 的优先级要高于 OR 。

SQL 将在 OR 运算符之前处理 AND 运算符。所以，请多考虑使用圆括号（()）。

select * from emp where deptno = 10 or deptno = 20 and sal > 2000

SELECT * FROM <表名> WHERE <列名> IN ( <值1>, <值2>, ... );

在某些场合下，IN 运算符可以替代 OR 运算符，来指定可能匹配的任何条件的范围。

IN 运算符有以下优点：

  • IN 运算符语法要清晰得多，并且更容易读取。
  • IN 运算符比 OR 运算符执行速度更快。
  • IN 的最大的优点是：IN 运算符可以包含另一条 SELECT 语句，从而构建更加灵活的 WHERE 子句。

WHERE 子句的 NOT 运算符只有一种功能：否定气候出现的任何条件。

最常见的 NOT 出现的场景有：

  • IS NOT NULL
  • NOT IN

# 5. 模糊查询

SELECT * FROM <表名> WHERE <列名> LIKE <通配字符串>;

通配符：用于匹配值得某些部分的特殊字符。

要在 WHERE 子句中使用通配符，必须使用 LIKE 运算符。LIKE 指示 MySQL 使用 通配 的方式，而不是直接相等性匹配来比较后面的条件。

最常见的通配符是 百分号（%）。在搜索字符串中，% 意指『匹配出现任意次数的任意字符串』。

注意，% 可以匹配 0 个字符。

可以在搜索字符串中的任意位置使用通配符，也可以使用多个通配符。

另一个需要注意的地方，% 通配符不会匹配 NULL 。

另一个有用的通配符是 下划线（_）。它只匹配单个字符。

# 6. 聚合函数

有时操作数据库的目的是汇总数据，而并非罗列所有的数据。常见情况有：

  • 确定表中行数（或满足某个特定条件的行数）。
  • 获得表中行组的和。
  • 找出表列中的最大、最小、平均值。

标准 SQL 中提供了5个 聚集函数（也叫聚合函数）来提供这样的功能。

聚集函数：运行在行组上，计算和返回单个值的函数。

聚合函数 说明
AVG() 返回某列的平均值。
COUNT() 返回某列的行数。
MAX() 返回某列的最大值。
MIN() 返回某列的最小值。
SUM() 返回某列的和。

# AVG 函数

AVG ( ) 通过对表中行数计数并计算特定列值之和，求得该列的平均值。

SELECT AVG(prod_price) AS avg_price
FROM products;

结合 SELECT 语句中的 WHERE，AVG ( ) 可以用于返回特定数据的平均值。

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id='DLL01';

AVG( ) 只能用来计算某 一个特定数值列 的平均值，而且列名必须作为函数参数给定。为了获得多个列的平均值，必须使用多个 AVG ( ) 函数。

SELECT AVG(quantity), AVG(item_price)
FROM orderitems;

AVG ( ) 函数会忽略值为 NULL 的行。

# COUNT 函数

COUNT ( ) 函数进行计数，利用 Count ( ) 函数确定表中行的数目或符合特定条件的行数。

Count ( ) 函数有两种形式：

  • 使用 Count ( * ) 会计算所有行，包括值为 NULL 的行。
  • 使用 Count ( 列名 ) 只会计算指定列中有值的行，即忽略值为 NULL 的行。
SELECT COUNT(*) AS num_cust
FROM customers;

SELECT COUNT(cust_email) AS num_cust
FROM customers;

结合 SELECT 语句中的 WHERE，Count ( ) 可以用于计算符合特定条件的行的数量。

# MAX 函数

MAX() 返回指定列中的最大值。Max() 函数必须指定列名，即 Max(列名) 。

MAX() 函数一般用于找出最大的数值和日期值。

SELECT MAX(prod_price) AS max_price FROM products;
SELECT MAX(order_date) AS max_date FROM orders;

MAX() 函数也会忽略值为 NULL 的行。

# MIN 函数

MIN ( ) 的功能正好与MAX()相反，它返回指定列的最小值。它也必须指定列名，即 Min ( 列名 ) 。

SELECT MIN(prod_price) AS max_price FROM products;
SELECT MIN(order_date) AS max_date FROM orders;

MIN ( ) 函数也会忽略值为 NULL 的行。

# SUM 函数

SUM ( ) 用来返回指定列值的和。

SELECT SUM(quantity) AS items_orderd
FROM orderitems;

结合 SELECT 语句中的 WHERE 子句，SUM ( ) 函数可以计算某些数据的总和。

SELECT SUM(quantity) AS items_orderd
FROM orderitems
WHERE order_num=20005;

利用标准的算术运算符，所有聚集函数都可以用来执行多个列上的计算。

SELECT SUM(quantity*item_price) AS items_orderd
FROM orderitems
WHERE order_num=20005;

SUM ( ) 函数也会忽略值为 NULL 的行。

# 7. 分组查询和 Having 子句

# 分组查询

可以这么认为：在没有指定分组前，所有查询出来的数据默认都在同一个“组”里。聚集函数都是这对一个组进行计算，并得出一个结果。

使用 GROUP BY 子句可以将表中的数据分为几个组进行处理。

SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id;

GROUP BY 子句和 SELECT 子句一样，可以通过逗号分隔指定多列。

在使用分组和聚集函数时，SELECT 子句中只能存在以下三种元素：

  • 常数
  • 聚集函数
  • GROUP BY 子句中指定的列名

在 GROUP BY 子句中不能使用列的别名。

只有 SELECT 子句和 HAVING 子句中能够使用聚集函数，特别是 WHERE 子句中无法使用。

# HAVING 子句

使用聚集函数对表中数据进行聚集操作时，为其指定条件的不是 WHERE 子句，而需要使用 HAVING 子句。

  • WHERE 子句，用于指定行所对应的条件。
  • HAVING 子句，用于指定组所对应的条件。

自此，SELECT 语句中各种子句的顺序是：

  1. SELECT 子句
  2. FROM 子句
  3. WHERE 子句
  4. GROUP BY 子句
  5. HAVING 子句
  6. ORDER BY 子句
  7. LIMIT 子句
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ... ;

# 8. 使用子查询

SQL 允许创建 子查询（Subquery）：嵌入在其他查询语句中的查询。

子查询最常见的场景是：由一条 SELECT 语句返回的结果充当另一条 SELECT 语句的 WHERE 子句的条件（通常结合 IN 使用）。

# 9. 表之间的关系

# 一对多/多对一（使用最为广泛）

例如：

  • 员工 - 部门
  • 学生 - 班级
  • 问题 - 答案

要点：

  • 多方 是从表；一方 是主表。
  • 从表使用外键列 引用/关联 主表的主键列。
  • 某些情况下从表中的外键列可以（短暂地）为 NULL 。
  • 如果从表中的外键列中有值，那么该值必须是主表主键列中已有的值。

# 多对多（双向的一对多关系）

例如：

  • 学生 - 课程
  • 电影 - 演员
  • 订单 - 商品

要点：

  • 使用 中间表 维护双方的关系。
  • 中间表有两个列，都是外键，分别引用双方的主键。
  • 中间表的这两列可以组成一个联合主键，当然也可以有 单独的/独立的 主键列。

# 一对一（使用相对较少）

例如：

  • 员工 - 合同
  • 市民 - 身份证

要点：

  • 类似于一对多的情况，概念上需要分出主从表。
  • 从表的外键列 引用/关联 到主表的主键。

# 10. 关联查询 SQL 语句

SQL 最强大的一个特性是能够在数据检索查询内自由地连接表。连接是使用 SELECT 可以执行的最重要的操作之一。

如果把数据存储在多个表中，怎样才能利用独立一条 SELECT 语句检索该数据？连接（Join）。

连接 使用一种特殊的语法，可以把多个表连接起来，并返回单个输出集，并且连接可以自由地关联每个表中正确的行。

# 内连接
SELECT 主表.列1, ..., 从表.列1, ...
  FROM 主表, 从表
  WHERE 主表.主键 = 从表.外键 
  AND 过滤条件 ...

如果两个表中的列名没有同名的，即，列名无歧义，那么 表名. 部分可以省略。

INNER JOIN 写法：

SELECT 主表.列1, ..., 从表.列1, ...
  FROM 主表 INNER JOIN 从表 ON 主表.主键 = 从表.外键
  WHERE 过滤条件;

列名 和 表名 都可以指定别名。以用于缩写。

SELECT 列名 AS 别名, ... FROM 表名 AS 别名 WHERE ...;

其中 AS 关键字可以省略。

SELECT 列名 别名, ... FROM 表名 别名 WHERE ...;

# 11. 外连接

  • 左外连接
  • 右外连接
SELECT * FROM ... 
    LEFT JOIN ... ON ... = ...
    WHERE ...;

SELECT * FROM ... 
    RIGHT JOIN ... ON ... = ...
    WHERE ...;

注意，从表在左，主表在右。

# 12. 自连接

自连接的关键是使用别名！

← 插入、更新和删除数据 →