DQL

准备表

学生基本信息表的结构

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY (id_number)
);

学生成绩表的结构

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

为表填入数据

我们给这两个表插入一些数据：

mysql> INSERT INTO student_info(number, name, sex, id_number, department, major, enrollment_time) VALUES
    ->     (20180101, '杜子腾', '男', '158177199901044792', '计算机学院', '计算机科学与工程', '2018-09-01'),
    ->     (20180102, '杜琦燕', '女', '151008199801178529', '计算机学院', '计算机科学与工程', '2018-09-01'),
    ->     (20180103, '范统', '男', '17156319980116959X', '计算机学院', '软件工程', '2018-09-01'),
    ->     (20180104, '史珍香', '女', '141992199701078600', '计算机学院', '软件工程', '2018-09-01'),
    ->     (20180105, '范剑', '男', '181048199308156368', '航天学院', '飞行器设计', '2018-09-01'),
    ->     (20180106, '朱逸群', '男', '197995199501078445', '航天学院', '电子信息', '2018-09-01');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO student_score (number, subject, score) VALUES
    ->     (20180101, '母猪的产后护理', 78),
    ->     (20180101, '论萨达姆的战争准备', 88),
    ->     (20180102, '母猪的产后护理', 100),
    ->     (20180102, '论萨达姆的战争准备', 98),
    ->     (20180103, '母猪的产后护理', 59),
    ->     (20180103, '论萨达姆的战争准备', 61),
    ->     (20180104, '母猪的产后护理', 55),
    ->     (20180104, '论萨达姆的战争准备', 46);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>

现在这两个表中的数据就如下所示了：

student_info表

number	name	sex	id_number	department	major	enrollment_time
20180101	杜子腾	男	158177199901044792	计算机学院	计算机科学与工程	2018-09-01
20180102	杜琦燕	女	151008199801178529	计算机学院	计算机科学与工程	2018-09-01
20180103	范统	男	17156319980116959X	计算机学院	软件工程	2018-09-01
20180104	史珍香	女	141992199701078600	计算机学院	软件工程	2018-09-01
20180105	范剑	男	181048200008156368	航天学院	飞行器设计	2018-09-01
20180106	朱逸群	男	197995199801078445	航天学院	电子信息	2018-09-01

student_score表

number	subject	score
20180101	母猪的产后护理	78
20180101	论萨达姆的战争准备	88
20180102	母猪的产后护理	100
20180102	论萨达姆的战争准备	98
20180103	母猪的产后护理	59
20180103	论萨达姆的战争准备	61
20180104	母猪的产后护理	55
20180104	论萨达姆的战争准备	46

好了，表的填充工作也已经做完了～ 终于可以开始查询数据了！

顺序

FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMIT

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]

基本查询

查询列

查看某个表中的某一列的数据的通用格式是这样：

SELECT 列名1 [AS] 列的别名 [, 列名2 [AS] 列的别名, ... 列名n [AS] 列的别名] FROM 表名;

别名只是在本次查询的到的结果集中展示，而不会改变真实表中的列名

如果需要把记录中的所有列都查出来，MySQL也提供一个省事儿的办法，我们之前也介绍过，就是直接用星号*来表示要查询的东西，就像这样：

SELECT * FROM 表名;

这个命令我们之前看过了，就不多唠叨了。不过需要注意的是，除非你确实需要表中的每个列，否则一般最好别使用星号*来查询所有列，虽然星号*看起来很方便，不用明确列出所需的列，但是查询不需要的列通常会降低性能。

列别名注意

• 在GROUP BY子句中写了列的别名（MySQL 和 PostgreSQL 可以这样写，对查询做了增强）🔥

  由于SQL的执行顺序为SELECT最后，所以 SELECT 的别名不能用做GROUP BY中。但实际上 MySQL 可以！

• 在 HAVING 中使用了列的别名，可以使用！！！

• 在 ORDER BY 中使用了列的别名，可以使用！！！

查询结果去重

DISTINCT只能放在第一列之前

去除单列的重复结果

如果我们想去除重复结果的话，可以将DISTINCT放在被查询的列前边，就是这样：

SELECT DISTINCT 列名 FROM 表名;

去除多列的重复结果

对于查询多列的情况，两条结果重复的意思是：两条结果的每一个列中的值都相同。如果我们想对多列查询的结果去重的话，可以直接把DISTINCT放在被查询的列的最前边：

SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

注意

NULL 也被视为一类数据。NULL 存在于多行中时，也会被合并为一条 NULL 数据

常数查询

SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, product_id, product_name FROM Product;

数字类型的列做加、减、乘、除运算 🔥

记住，列就类似变量，变量当然可以做运算了！

⚠️ 与NULL运算时，结果都为NULL。甚至NULL / 0 都是NULL。

SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;

FROM子句真的有必要吗？

实际上，通过执行 SELECT 语句来代替计算器的情况基本上是不存在的。Oracle 必须有！

只希望得到一行临时数据的情况，还是可以通过使用没有 FROM 子句的 SELECT 语句来实现某种业务的

SELECT (100 + 200) * 3 AS calculation;

条件查询

算术运算符

运算符：可用于列运算、条件查询

算术运算符
+
-
*
/
()

比较运算符

比较运算符	说明
=、<>、!=、<、>、<=、>=	对数字、日期和字符（字符按照字典顺序）等几乎所有数据类型的列和值进行比较 <>在 SQL 中表示不等于，很多RDBMS的方言（仅测试了MySQL、Oracle）可以使用!=。
[NOT] BETWEEN...AND	在一个范围之内，如：a between 100 and 200 相当于条件 a 在 [100 到 200] 之间

匹配NULL值

我们前边说过，NULL代表没有值，意味着你并不知道该列应该填入什么数据，在判断某一列是否为NULL的时候并不能单纯的使用=操作符，而是需要专业判断值是否是NULL的操作符：

操作符	示例	描述
IS NULL	a IS NULL	a的值是NULL
IS NOT NULL	a IS NOT NULL	a的值不是NULL

再次强调一遍，不能直接使用普通的操作符来与NULL值进行比较，必须使用IS NULL或者IS NOT NULL！

NULL 运算注意 🔥

• 与 NULL 运算时，结果都为 NULL。甚至 NULL / 0 都是 NULL。
• 查询 NULL 时不能使用比较运算符(= 或者 <>、>、<等)，因为结果为空不满足该条件，需使用 IS [ NOT ] NULL。
• 查询 NULL 时不能使用逻辑运算符，因为结果不为真也不为假，是不确定（UNKNOWN）
• 除了 COALESCE 及其衍生函数！！

匹配列表中的元素

有时候搜索条件中指定的匹配值并不是单个值，而是一个列表，只要匹配到列表中的某一项就算匹配成功，这种情况可以使用IN操作符：

操作符	示例	描述
IN	a IN (b1, b2, ...)	a是b1, b2, ... 中的某一个
NOT IN	a NOT IN (b1, b2, ...)	a不是b1, b2, ... 中的任意一个

匹配运算符	说明
[NOT] IN、ALL、ANY	集合表示多个值，使用逗号分隔。ALL为所有，ANY为任意一个即可，可用最值替代
[NOT] EXIST	通常使用关联子查询作为参数（是否有该条记录，经常会使用 SELECT *）

实际上即使不使用 EXIST，基本上也都可以使用 IN(或者 NOT IN)来代替

SELECT product_name, sale_price
FROM Product AS P 1
WHERE EXISTS (SELECT *  -- 关联子查询
                            FROM ShopProduct AS SP 2
                            WHERE SP.shop_id = '000C'
                            AND SP.product_id = P.product_id); -- 将 Product 表和 ShopProduct 表进行了联接

通配符—模糊查询

有时候我们并不能精确的描述我们要查询的哪些结果，比方说我们只是想看看姓'杜'的学生信息，而不能精确的描述出这些姓'杜'的同学的完整姓名，我们称这种查询为模糊查询。MySQL中使用下边这两个操作符来支持模糊查询：

操作符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	a不匹配b

既然我们不能完整描述要查询的信息，那就用某个符号来替代这些模糊的信息，这个符号就被称为通配符。MySQL中支持下边这两个通配符：

• %：代表任意一个字符串。

  比方说我们想查询student_info表中name以'杜'开头的记录，我们可以这样写：

  mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜%';
  +----------+-----------+--------------------+--------------------------+
  | number   | name      | id_number          | major                    |
  +----------+-----------+--------------------+--------------------------+
  | 20180101 | 杜子腾    | 158177199901044792 | 计算机科学与工程         |
  | 20180102 | 杜琦燕    | 151008199801178529 | 计算机科学与工程         |
  +----------+-----------+--------------------+--------------------------+
  2 rows in set (0.00 sec)
  
  mysql>
  

  或者我们只知道学生名字里边包含了一个'香'字，那我们可以这么查：

  mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '%香%';
  +----------+-----------+--------------------+--------------+
  | number   | name      | id_number          | major        |
  +----------+-----------+--------------------+--------------+
  | 20180104 | 史珍香    | 141992199701078600 | 软件工程     |
  +----------+-----------+--------------------+--------------+
  1 row in set (0.00 sec)
  
  mysql>
  

• _：代表任意一个字符。

  有的时候我们知道要查询的字符串中有多少个字符，而使用%时匹配的范围太大，我们就可以用_来做通配符。就像是支付宝的万能福卡，一张万能福卡能且只能代表任意一张福卡(也就是它不能代表多张福卡)。

  比方说我们想查询姓'范'，并且姓名只有2个字符的记录，可以这么写：

  mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范_';
  +----------+--------+--------------------+-----------------+
  | number   | name   | id_number          | major           |
  +----------+--------+--------------------+-----------------+
  | 20180103 | 范统   | 17156319980116959X | 软件工程        |
  | 20180105 | 范剑   | 181048199308156368 | 飞行器设计      |
  +----------+--------+--------------------+-----------------+
  2 rows in set (0.00 sec)
  
  mysql>
  

  不过下边这个查询却什么都没有查到：

  mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜_';
  Empty set (0.00 sec)
  
  mysql>
  

  这是因为一个_只能代表一个字符（%是代表任意一个字符串），并且student_info表中并没有姓'杜'并且姓名长度是2个字符的记录，所以这么写是查不出东西的。

转义通配符

如果待匹配的字符串中本身就包含普通字符'%'或者'_'该咋办，怎么区分它是一个通配符还是一个普通字符呢？

答：如果匹配字符串中需要普通字符'%'或者'_'的话，需要在它们前边加一个反斜杠\来和通配符区分开来，也就是说：

• '\%'代表普通字符'%'
• '\_'代表普通字符'_' 比方说这样：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范\_';
Empty set (0.00 sec)
    
mysql>

由于student_info表中没有叫范_的学生，所以查询结果为空。

逻辑运算符

逻辑运算符	说明
AND或 &&	与，后者并不通用；优先于OR，“()”优先级最高
OR 或 ||	或
NOT或 !	非；WHERE NOT代表后面的表达式的非运算（但是不要滥用，不清晰）

  WHERE age >= 18 AND age <= 80;
  WHERE age BETWEEN 18 AND 80;
  WHERE name='zhangSan' OR name='liSi';
  WHERE name IN ('zhangSan', 'liSi');
  WHERE age IS NULL; -- 不能使用等号
  WHERE age IS NOT NULL;
  WHERE sale_price - purchase_price >= 500;

  SELECT * FROM emp WHERE ename LIKE '张_'; -- 姓张，名字由两个字组成的员工
  SELECT * FROM emp WHERE ename LIKE '___'; -- 姓名由3个字组成的员工

  SELECT * FROM emp WHERE ename LIKE '张%'; -- 查询的是姓张的所有员工
  SELECT * FROM emp WHERE ename LIKE '%阿%';-- 查询姓名中间带有阿字的员工
  SELECT * FROM emp WHERE ename LIKE '%'; -- 条件不存在，如果姓名为NULL的查询不出来

注意 0 在 MySQL 中代表 false，非 0 代表 true（如 1，-1都是 true）

select * from sys_user where -1;

注意

如果我们需要在某个查询中指定很多的搜索条件，比方说我们想从student_score表中找出课程为'论萨达姆的战争准备'，并且成绩大于95分或者小于55分的记录，那我们可能会这么写：

mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '论萨达姆的战争准备';
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
| 20180104 | 论萨达姆的战争准备          |    46 |
+----------+-----------------------------+-------+
3 rows in set (0.00 sec)

mysql>

为什么结果中仍然会有'母猪的产后护理'课程的记录呢？因为：AND操作符的优先级高于OR操作符，也就是说在判断某条记录是否符合条件时会先检测AND操作符两边的搜索条件。所以

score > 95 OR score < 55 AND subject = '论萨达姆的战争准备'

可以被看作下边这两个条件中任一条件成立则整个式子成立：

1. score > 95
2. score < 55 AND subject = '论萨达姆的战争准备'

因为结果集中subject是'母猪的产后护理'的记录中score值为100，符合第1个条件，所以整条记录会被加到结果集中。为了避免这种尴尬，在一个查询中有多个搜索条件时最好使用小括号()来显式的指定各个搜索条件的检测顺序，比如上边的例子可以写成下边这样：

mysql> SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = '论萨达姆的战争准备';
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 论萨达姆的战争准备          |    98 |
| 20180104 | 论萨达姆的战争准备          |    46 |
+----------+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql>

分组查询

何为分组

针对某个列，将该列的值相同的记录分到一个组中。在GROUP BY 子句中指定的列称为聚合键或者分组列。可以有多个分组！

当聚合键中包含 NULL 时，在结果中会以**“不确定”行(空行)**的形式表现出来。

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; -- deptno分组，查询部门编号和每个部门记录
SELECT job, MAX(SAL) FROM emp GROUP BY job; -- 使用job分组，查询每种工作的最高工资

在使用分组的时候必须要意识到，分组的存在仅仅是为了方便我们分别统计各个分组中的信息，所以我们只需要把分组列和聚合函数放到查询列表处就好！当然，如果非分组列出现在查询列表中会出现什么情况呢？比如下边这个查询：

mysql> SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xiaohaizi.student_score.number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

可以看到出现了错误。为啥会错误呢？回想一下我们使用GROUP BY子句的初衷，我们只是想把记录分为若干组，然后再对各个组分别调用聚集函数去做一些统计工作。本例中的查询列表处放置了既非分组列、又非聚集函数的number列，那我们想表达啥意思呢？从各个分组中的记录中取一条记录的number列？该取分组中的哪条记录为好呢？比方说对于'母猪的产后护理'这个分组中的记录来说，该分组中有4条记录，那number列的值应该取20180101，还是20180102，还是20180103，还是20180104呢？这个我们也不知道，也就是说把非分组列放到查询列表中会引起争议，导致结果不确定。基于此，设计MySQL的大叔才会为上述语句报错。

提示

小贴士： 其实假如分组后的每个分组的所有记录的某个非分组列的值都一样，那我把该非分组列加入到查询列表中也没啥问题呀。比方说按照subject列进行分组后，假如在'母猪的产后护理'的分组中各条记录的number列的值都相同，在'论萨达姆的战争准备'的分组中各条记录的number列的值也都相同，那么我们把number列放在查询列表中也没啥问题。可能设计MySQL的大叔觉得这种说法也有点儿道理，他们提出了一个称之为ONLY_FULL_GROUP_BY的SQL模式，当我们关闭这个SQL模式时，就允许把非分组列放到查询列表中。当然，什么是SQL模式，怎么开启和关闭这个称之为ONLY_FULL_GROUP_BY的SQL模式，不是我们初学者要考虑的问题，等以后大家变牛的时候可以再到文档中去查看。

嵌套分组

有时候按照某个列进行分组太笼统，一个分组内可以被继续划分成更小的分组。比方说对于student_info表来说，我们可以先按照department来进行分组，所以可以被划分为2个分组。我们觉得这样按照department分组后，各个分组可以再按照major来继续分组，从而划分成更小的分组。

所以现在有了2个大分组，4个小分组，我们把这种对大的分组下继续分组的的情形叫做嵌套分组。我们只需要在GROUP BY子句中把各个分组列依次写上，用逗号,分隔开就好了。比如这样：

mysql> SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
+-----------------+--------------------------+----------+
| department      | major                    | COUNT(*) |
+-----------------+--------------------------+----------+
| 航天学院        | 电子信息                 |        1 |
| 航天学院        | 飞行器设计               |        1 |
| 计算机学院      | 计算机科学与工程         |        2 |
| 计算机学院      | 软件工程                 |        2 |
+-----------------+--------------------------+----------+
4 rows in set (0.00 sec)

mysql>

可以看到，在嵌套分组中，聚集函数将作用在最后一个分组列上，在这个例子中就是major列

使用分组注意事项

使用分组来统计数据给我们带来了非常大的便利，但是要随时提防有坑的地方：

• 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。

• 如果存在多个分组列，也就是嵌套分组，聚合函数将作用在最后的那个分组列上。

• 如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

• GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。

  上边介绍的GROUP BY后跟随的都是表中的某个列或者某些列，其实一个表达式也可以，比如这样：

  mysql> SELECT concat('专业：', major), COUNT(*) FROM student_info GROUP BY concat('专业：', major);
  +-----------------------------------+----------+
  | concat('专业：', major)           | COUNT(*) |
  +-----------------------------------+----------+
  | 专业：电子信息                    |        1 |
  | 专业：计算机科学与工程            |        2 |
  | 专业：软件工程                    |        2 |
  | 专业：飞行器设计                  |        1 |
  +-----------------------------------+----------+
  4 rows in set (0.00 sec)
  
  mysql>
  

  MySQL会根据这个表达式的值来对记录进行分组，使用表达式进行分组的时候需要特别注意，查询列表中的表达式和GROUP BY子句中的表达式必须完全一样。不过一般情况下我们也不会用表达式进行分组，所以目前基本没啥用～

• WHERE子句和HAVING子句的区别。

  WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

• 在GROUP BY子句中写了列的别名（MySQL 和 PostgreSQL 可以这样写，对查询做了增强）🔥

  由于SQL的执行顺序为SELECT最后，所以 SELECT 的别名不能用做GROUP BY中。但实际上 MySQL 可以！

• GROUP BY子句的结果能排序吗？

  通常 SELECT 语句的执行结果的显示顺序都是随机的，需要排序则需在SELECT中指定

• DISTINCT和GROUP BY都能够删除后续列中的重复数据。在“想要删除选择结果中的重复记录”时使用DISTINCT，在“想要计算汇总结果”时使用GROUP BY。不使用COUNT等聚合函数，而只使用GROUP BY子句的SELECT语句，会让人觉得奇怪

• 在 SELECT 子句中书写了多余的列。使用聚合函数（其实是使用分组函数）时，SELECT 子句中只能存在以下三种元素：

  • 常数：数字 123，或者字符串 '测试'，或日期
  • 聚合函数。非分组列不能单独出现在检索列表(SELECT)中(可以被放到聚集函数中)。
  • GROUP BY子句中指定的列名(也就是聚合键)，其实应该是每组共有的。

HAVING

作用于分组的过滤条件 🔥

有时候某个带有GROUP BY子句的查询中可能会产生非常多的分组，假设student_score表中存储了100门学科的成绩，也就是subject列中有100个不重复的值，那就会产生100个分组，也就意味着这个查询的结果集中会产生100条记录。如果我们不想在结果集中得到这么多记录，只想把那些符合某些条件的分组加入到结果集，从而减少结果集中记录的条数，那就需要把针对分组的条件放到HAVING子句了。比方说老师想要查询平均分大于73分的课程，就可以这么写

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
1 row in set (0.00 sec)

mysql>

其实这里所谓的针对分组的条件一般是指下边这两种：

• 分组列

  也就是说我们可以把用于分组的列放到HAVING子句的条件中，比如这样：

  SELECT subject, AVG(score) FROM student_score GROUP BY subject having subject = '母猪的产后护理';
  

• 作用于分组的聚集函数

  当然，并不是HAVING子句中只能放置在查询列表出现的那些聚集函数，只要是针对这个分组进行统计的聚集函数都可以，比方说老师想查询最高分大于98分的课程的平均分，可以这么写：

  mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98;
  +-----------------------+------------+
  | subject               | AVG(score) |
  +-----------------------+------------+
  | 母猪的产后护理        |    73.0000 |
  +-----------------------+------------+
  1 row in set (0.00 sec)
  
  mysql>
  

  其中的MAX(score)这个聚集函数并没有出现在查询列表中，但仍然可以作为HAVING子句中表达式的一部分。

结果集过滤条件 🔥

当不使用 GROUP 时，WHERE 和 HAVING 的效果大致一样。不同在于：

• WHERE 是过滤磁盘数据

• HAVING 是过滤内存中结果集数据（如列别名）

  如果在 WHERE 中使用别名则报错（没有该列），这时候就可以使用 HAVING 处理

select (price - outer_price) as a from good where a > 100; -- 报错
select (price - outer_price) as a from good having a > 100; -- 在HAVING子句中使用无集合条件可能效率不高。考虑将它们移至WHERE 

WHERE 和 HAVING 区别

• WHERE：分组前过滤数据，指定行对应条件；HAVING：分组后过滤数据，指定组对应条件

  • 聚合键所对应的条件不应该书写在 HAVING 子句当中，而应该书写在 WHERE 子句当中。

  • 通常情况下，将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快。

    • 使用 COUNT 函数等对表中的数据进行聚合操作时，DBMS 内部就会进行排序处理。排序处理是会大大增加机器负担的高负荷的处理 。因此，只有尽可能减少排序的行数，才能提高处理速度。通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。

  • 可以对 WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。

• 见结果集过滤条件

• WHERE后不可以使用聚合函数，HAVING后可以使用聚合函数

  SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;-- 以部门分组...。条件为记录数大于3
  

• HAVING 子句中能够使用的 3 种要素如下所示：

• • 常数：数字 123，或者字符串 '测试'，或日期
  • 聚合函数
  • GROUP BY子句中指定的列名(也就是聚合键)

排序

我们之前查询number列的时候得到的记录并不是有序的，这是为什么呢？MySQL其实默认会按照这些数据底层存储的顺序来给我们返回数据，但是这些数据可能会经过更新或者删除，如果我们不明确指定按照什么顺序来排序返回结果的话，那我们可以认为该结果中记录的顺序是不确定的。换句话说如果我们想让返回结果中的记录按照某种特定的规则排序，那我们必须显式的指定排序规则。

通常，从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知

升序

ascendent

SELECT * FROM student_score ORDER BY score ASC; -- ASC可以省略

降序

descendent

SELECT * FROM student_score ORDER BY score DESC; -- DESC不能省略

多字段作为排序条件

SELECT * FROM student_score ORDER BY subject, score DESC; -- 按 subject 升序排，如果 subject 相同，按 score 降序排

注意

• 排序键中包含 NULL 时，会在开头或末尾进行汇总。
• **在 ORDER BY 子句中可以使用 SELECT 子句中定义的别名。**与SQL执行顺序有关！
• 在 ORDER BY 子句中可以使用 SELECT 子句中未使用的列和聚合函数。
• 在 ORDER BY 子句中不要使用列编号：阅读困难；该功能将会删除
• 对于数字的排序还是很好理解的，但是字符串怎么排序呢？大写的A和小写的a哪个大哪个小？这个问题涉及到字符串使用的编码方式以及字符串排序规则，我们之后会详细的介绍它们，现在你只需要知道排序的语法就好了。

限制查询结果条数—分页

简介

有时候查询结果的条数会很多，都显示出来可能会撑爆屏幕～ 所以MySQL给我们提供了一种限制结果集中的记录条数的方式，就是在查询语句的末尾使用这样的语法：

LIMIT 开始行, 限制条数;

开始行指的是我们想从第几行数据开始查询，限制条数是结果集中最多包含多少条记录。

提示

小贴士： 在生活中通常都是从1开始计数的，而在计算机中都是从0开始计数的，所以我们平时所说的第1条记录在计算机中算是第0条。比如student_info表里的6条记录在计算机中依次表示为：第0条、第1条、第2条、第3条、第4条、第5条。

MySQL、PostgreSQL、MariaDB 和 SQLite方言LIMIT用来限定查询结果的起始索引（从0开始），以及总行数：开始的索引 = (当前页-1) \* 每页记录数

/*1. 一页的记录数：10行;2. 查询第3页*/
select * from emp limit 20, 10;

第一个参数是0可以省略；最后不足总行数的话，有多少显示多少。

如果是 Oracle，你需要基于 ROWNUM 来统计行数

SELECT name, hp_max 
FROM (SELECT name, hp_max 
      FROM heros 
      ORDER BY hp_max) 
WHERE ROWNUM <=5

注意

如果指定的开始行大于结果中的行数，那查询结果就什么都没有：

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 6, 2;
Empty set (0.00 sec)

mysql>

如果查询的结果条数不超过限制条数，那就可以全部显式出来：

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 4, 3;
+----------+-----------+--------------------+-----------------+
| number   | name      | id_number          | major           |
+----------+-----------+--------------------+-----------------+
| 20180105 | 范剑      | 181048199308156368 | 飞行器设计      |
| 20180106 | 朱逸群    | 197995199501078445 | 电子信息        |
+----------+-----------+--------------------+-----------------+
2 rows in set (0.00 sec)

mysql>

从第4条开始的记录有两条，限制条数为3，所以这两条记录都可以被展示在结果集中。

==========

表达式

操作数

MySQL中操作数可以是下边这几种类型：

1. 常数

   常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2019-08-16 17:10:43啥的。

2. 列名

   针对某个具体的表，它的列名可以被当作表达式的一部分，比如对于student_info表来说，number、name都可以作为操作数。

3. 函数调用

   MySQL中有函数的概念，比方说获取当前时间的函数NOW，而在函数后边加个小括号就算是一个函数调用，比如NOW()。

   如果你不清楚函数的概念，我们之后会详细唠叨的，现在不知道也可以～
   

4. 标量子查询或者行子查询

   这个子查询我们稍后会详细唠叨的～

5. 其他表达式

   一个表达式也可以作为一个操作数与另一个操作数来形成一个更复杂的表达式，比方说（假设col是一个列名）：

   • (col - 5) / 3
   • (1 + 1) * 2 + col * 3

提示

小贴士： 当然，可以作为操作数的东西不止这么几种，不过我们这是一个入门书籍，大家在熟练使用MySQL后再到文档中查看更多的操作数类型吧。

操作符

对于小白的我们来说，目前熟悉掌握下边三种操作符就应该够用了：

1. 算术操作符

   就是加减乘除法那一堆，我们看一下MySQL中都支持哪些：

   操作符	示例	描述
   +	a + b	加法
   -	a - b	减法
   *	a * b	乘法
   /	a / b	除法
   DIV	a DIV b	除法，取商的整数部分
   %	a % b	取余
   -	-a	负号

   在使用MySQL中的算术操作符时需要注意，DIV和/都表示除法操作符，但是DIV只会取商的整数部分，/会保留商的小数部分。比如表达式 2 DIV 3的结果是0，而2 / 3的结果是0.6667。

2. 比较操作符

   就是在搜索条件中我们已经看过的比较操作符，我们把常用的都抄下来看一下：

   操作符	示例	描述
   =	a = b	a等于b
   <>或者!=	a <> b	a不等于b
   <	a < b	a小于b
   <=	a <= b	a小于或等于b
   >	a > b	a大于b
   >=	a >= b	a大于或等于b
   BETWEEN	a BETWEEN b AND c	满足 b <= a <= c
   NOT BETWEEN	a NOT BETWEEN b AND c	不满足 b <= a <= c
   IN	a IN (b1, b2, ...)	a是b1, b2, ... 中的某一个
   NOT IN	a NOT IN (b1, b2, ...)	a不是b1, b2, ... 中的任意一个
   IS NULL	a IS NULL	a的值是NULL
   IS NOT NULL	a IS NOT NULL	a的值不是NULL
   LIKE	a LIKE b	a匹配b
   NOT LIKE	a NOT LIKE b	a不匹配b

   由比较操作符连接而成的表达式也称为布尔表达式，表示真或者假，也可以称为TRUE或者FALSE。比如1 > 3就代表FALSE，3 != 2就代表TRUE。

3. 逻辑操作符

   逻辑操作符是用来将多个布尔表达式连接起来，我们需要了解这几个逻辑操作符：

   操作符	示例	描述
   AND	a AND b	只有a和b同时为真，表达式才为真
   OR	a OR b	只要a或b有任意一个为真，表达式就为真
   XOR	a XOR b	a和b有且只有一个为真，表达式为真

表达式的使用

只要把这些操作数和操作符相互组合起来就可以组成一个表达式。表达式主要以下边这两种方式使用：

1. 放在查询列表中

   我们前边都是将列名放在查询列表中的(*号代表所有的列名～)。列名只是表达式中超级简单的一种，我们可以将任意一个表达式作为查询列表的一部分来处理，比方说我们可以在查询student_score表时把score字段的数据都加100，就像这样：

   mysql> SELECT  number, subject, score + 100 FROM student_score;
   +----------+-----------------------------+-------------+
   | number   | subject                     | score + 100 |
   +----------+-----------------------------+-------------+
   | 20180101 | 母猪的产后护理              |         178 |
   | 20180101 | 论萨达姆的战争准备          |         188 |
   | 20180102 | 母猪的产后护理              |         200 |
   | 20180102 | 论萨达姆的战争准备          |         198 |
   | 20180103 | 母猪的产后护理              |         159 |
   | 20180103 | 论萨达姆的战争准备          |         161 |
   | 20180104 | 母猪的产后护理              |         155 |
   | 20180104 | 论萨达姆的战争准备          |         146 |
   +----------+-----------------------------+-------------+
   8 rows in set (0.00 sec)
   
   mysql>
   

   其中的number、subject、score + 100都是表达式，结果集中的列的名称也将默认使用这些表达式的名称，所以如果你觉得原名称不好，我们可以使用别名：

   mysql> SELECT  number, subject, score + 100 AS score FROM student_score;
   +----------+-----------------------------+-------+
   | number   | subject                     | score |
   +----------+-----------------------------+-------+
   | 20180101 | 母猪的产后护理              |   178 |
   | 20180101 | 论萨达姆的战争准备          |   188 |
   | 20180102 | 母猪的产后护理              |   200 |
   | 20180102 | 论萨达姆的战争准备          |   198 |
   | 20180103 | 母猪的产后护理              |   159 |
   | 20180103 | 论萨达姆的战争准备          |   161 |
   | 20180104 | 母猪的产后护理              |   155 |
   | 20180104 | 论萨达姆的战争准备          |   146 |
   +----------+-----------------------------+-------+
   8 rows in set (0.00 sec)
   
   mysql>
   

   这样score + 100列就可以按照别名score来展示了！

   需要注意的是，放在查询列表的表达式也可以不涉及列名，就像这样：

   mysql> SELECT 1 FROM student_info;
   +---+
   | 1 |
   +---+
   | 1 |
   | 1 |
   | 1 |
   | 1 |
   | 1 |
   | 1 |
   +---+
   6 rows in set (0.01 sec)
   
   mysql>
   

   因为student_info中有6条记录，所以结果集中也就展示了6条结果，不过我们的查询列表处只有一个常数1，所以所有的结果的值也都是常数1。这种查询列表中不涉及列名的情况下，我们甚至可以省略掉FROM子句后边的表名，就像这样：

   mysql> SELECT 1;
   +---+
   | 1 |
   +---+
   | 1 |
   +---+
   1 row in set (0.00 sec)
   
   mysql>
   

   可是这么写有什么现实用处么？好像有的，可以做个计算器[偷笑]～

2. 作为搜索条件

   我们在介绍搜索条件的时候介绍的都是带有列名的表达式，搜索条件也可以不带列名，比如这样：

   mysql> SELECT number, name, id_number, major FROM student_info WHERE 2 > 1;
   +----------+-----------+--------------------+--------------------------+
   | number   | name      | id_number          | major                    |
   +----------+-----------+--------------------+--------------------------+
   | 20180101 | 杜子腾    | 158177199901044792 | 计算机科学与工程         |
   | 20180102 | 杜琦燕    | 151008199801178529 | 计算机科学与工程         |
   | 20180103 | 范统      | 17156319980116959X | 软件工程                 |
   | 20180104 | 史珍香    | 141992199701078600 | 软件工程                 |
   | 20180105 | 范剑      | 181048199308156368 | 飞行器设计               |
   | 20180106 | 朱逸群    | 197995199501078445 | 电子信息                 |
   +----------+-----------+--------------------+--------------------------+
   6 rows in set (0.00 sec)
   
   mysql>
   

   由于我们的搜索条件是2 > 1，这个条件对于表中的每一条记录都成立，所以最后的查询结果就是全部的记录。不过这么写有点儿傻哈，没有一毛钱卵用，没一点实际意义～ 所以通常情况下搜索条件中都会包含列名的。

时间处理函数

当前日期

【标准SQL】CURRENT_DATE

-- PostgreSQL 和 MySQL
SELECT CURRENT_DATE; -- 2021-12-12

-- 在 Oracle 中使用该函数时，需要在 FROM 子句中指定临时表(DUAL)。且返回的有日期和时间！
-- CURRENT_DATE 取会话的当前日期和时间；SYSDATE 取服务器（主机）的当前日期和时间。一般相同，除非修改会话的时区。
SELECT CURRENT_DATE FROM dual;


-- SQL Server CAST函数将CURRENT_TIMESTAMP转换为日期类型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
-- 在 DB2 中使用时，需要在CRUUENT和DATE之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1(相当于 Oracle 中的 DUAL)。
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;

【MySQL】CURDATE

SELECT CURDATE(); -- 2021-12-12

当前时间

【标准SQL】CURRENT_TIME

-- PostgreSQL 和 MySQL
SELECT CURRENT_TIME; -- 18:18:18

-- 在 Oracle 中使用该函数时，需要在 FROM 子句中指定临时表(DUAL)。且返回的有日期和时间！
SELECT CURRENT_TIMESTAMP FROM dual; -- Oracle


--SQL Server CAST函数将CURRENT_TIMESTAMP转换为时间类型
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME; 
--DB2 CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1; 

【MySQL】CURTIME

SELECT CURTIME(); -- 18:18:18

当前日期和时间

【标准SQL】CURRENT_TIMESTAMP

-- -- PostgreSQL、MySQL 和 SQL Server
SELECT CURRENT_TIMESTAMP; -- 2021-12-12 18:18:18

-- 在 Oracle 中使用该函数时，需要在 FROM 子句中指定临时表(DUAL)。且返回的有日期和时间！
SELECT CURRENT_TIMESTAMP FROM dual;


-- DB2 CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

【MySQL】NOW

SELECT NOW(); -- 2021-12-12 18:18:18

提取

【标准SQL】EXTRACT——提取日期元素返回数值

-- PostgreSQL 和 MySQL	
SELECT CURRENT_TIMESTAMP, EXTRACT( YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM CURRENT_TIMESTAMP() )

-- Oracle
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year 


-- SQL Server
SELECT CURRENT_TIMESTAMP, DATEPART(YEAR , CURRENT_TIMESTAMP) 
/* DB2，CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP, EXTRACT(YEAR FROM CURRENT TIMESTAMP) AS year

该函数的返回值并不是日期类型而是数值类型。

【标准SQL】DATE：提取日期部分返回数值，下同

DATE 中的参数可以是日期类型、时间类型（就是这么神奇）、日期时间类型、字符串（会隐式类型转换）

SELECT DATE('2020-11-11 12:12:12')

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

增删计算

【MySQL】

SELECT CURRENT_DATE();-- 2021-12-12
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY);-- 2021-12-14
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY);-- 2021-12-10

在使用DATE_ADD和DATE_SUB这两个函数时需要注意，增加或减去的时间间隔单位可以自己定义，下边是MySQL支持的一些时间单位：

时间单位	描述
MICROSECOND	毫秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	星期
MONTH	月
QUARTER	季度
YEAR	年

如果我们相让2021-12-12 18:18:18这个时间值增加2分钟，可以这么写：

SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 2 MINUTE );
SELECT DATE_ADD(NOW(), INTERVAL 2 MINUTE );

比较计算

【MySQL】

-- 返回值是相差的天数，不能定位到小时、分钟和秒
SELECT DATEDIFF('2021-12-12', '2021-12-14');-- -2

-- 返回值是相差的时间
SELECT TIMEDIFF('2021-12-12 12:12:12', '2021-12-14 12:12:12');-- -48:00:00

-- 有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND）等上述单位
SELECT TIMESTAMPDIFF(DAY ,'2021-12-12 12:12:12', '2021-12-14 12:12:12');-- 2 这个值和上面的不一样！比较方向不一样

格式化

【MySQL】

SELECT DATE_FORMAT(NOW(),'%m-%d-%Y');-- 12-12-2021

在使用DATE_FORMAT函数时需要注意，我们可以通过一些所谓的格式符来自定义日期和时间的显示格式，下边是MySQL中常用的一些日期和时间的格式符以及它们对应的含义：

格式符	描述
%b	简写的月份名称（Jan、Feb、...、Dec)
%D	带有英文后缀的月份中的日期（0th、1st、2nd、...、31st)）
%d	数字格式的月份中的日期(00、01、02、...、31)
%f	微秒（000000-999999）
%H	二十四小时制的小时 (00-23)
%h	十二小时制的小时 (01-12)
%i	数值格式的分钟(00-59)
%M	月份名（January、February、...、December）
%m	数值形式的月份(00-12)
%p	上午或下午（AM代表上午、PM代表下午）
%S	秒(00-59)
%s	秒(00-59)
%W	星期名（Sunday、Monday、...、Saturday）
%w	周内第几天 （0=星期日、1=星期一、 6=星期六）
%Y	4位数字形式的年（例如2019）
%y	2位数字形式的年（例如19）

我们可以把我们想要的显示格式用对应的格式符描述出来，就像这样：

mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| Aug 16 2019 05:10 PM                   |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>

'%b %d %Y %h:%i %p'就是一个用格式符描述的显示格式，意味着对应的日期和时间应该以下边描述的方式展示：

• 先输出简写的月份名称（格式符%b），也就是示例中的Aug，然后输出一个空格。
• 再输出用数字格式表示的的月份中的日期（格式符%d），也就是示例中的16，然后输出一个空格。
• 再输出4位数字形式的年（格式符%Y），也就是示例中的2019，然后输出一个空格。
• 再输出十二小时制的小时（格式符%h），也就是示例中的05，然后输出一个冒号:。
• 再输出数值格式的分钟（格式符%i），也就是示例中的10，然后输出一个空格。
• 最后输出上午或者下午（格式符%p），也就是示例中的PM。

文本处理函数

简介

名称	调用示例	示例结果	描述
LEFT	LEFT('abc123', 3)	abc	给定字符串从左边取指定长度的子串
RIGHT	RIGHT('abc123', 3)	123	给定字符串从右边取指定长度的子串
LENGTH	LENGTH('abc')	3	给定字符串的长度
LOWER	LOWER('ABC')	abc	给定字母字符串的小写格式
UPPER	UPPER('abc')	ABC	给定字母字符串的大写格式
LTRIM	LTRIM(' abc')	abc	给定字符串左边空格去除后的格式
RTRIM	RTRIM('abc ')	abc	给定字符串右边空格去除后的格式
SUBSTRING	SUBSTRING('abc123', 2, 3)	bc1	给定字符串从指定位置截取指定长度的子串。index 从1开始
CONCAT	CONCAT('abc', '123', 'xyz')	abc123xyz	将给定的各个字符串拼接成一个新字符串
REPLACE	REPLACE('abcde', 'bc', 'a')	aade	替换字符串
POSITION	POSITION('@' IN 'conanan@qq.com')	8	index 从1开始

• 进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是NULL。这是因为它也是变了形的函数。多个值拼接也可以。

  • MySQL使用CONCAT函数（可多个）
  • PG 使用 || （标准SQL语法）
  • Oracle 使用 || （标准SQL语法）
  • SQL Server使用“+” 拼接

• MySQL 中的 LENGTH 以字节为单位的函数进行计算时，“LENGTH(山田)”的返回结果是 4。MySQL中还存在计算字符串长度的自有函数 CHAR_LENGTH 。该函数也无法在 SQL Server 中使用，可以使用LEN函数来计算。

• SUBSTRING函数的语法是标准 SQL 承认的正式语法，但是现在只有 PostgreSQL 和 MySQL 支持该语法。 该函数也存在和LENGTH函数同样的多字节字符的问题（没有这情况。。）。

  SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数) -- PostgreSQL 、 MySQL 都可以
  SUBSTRING(对象字符串，截取的起始位置，截取的字符数 ) -- SQL Server、MySQL 都可以
  SUBSTR(对象字符串，截取的起始位置，截取的字符数) -- Oracle/DB2 专用语法;MySQL也可以使用，可以有前2个参数或3个参数
  

  INSERT INTO demo.sys_user (user_id, age, user_name) VALUES (5, null, 'x小米加步枪111');
  INSERT INTO demo.sys_user (user_id, age, user_name) VALUES (7, null, 'x小米加步枪222');
  
  update sys_user set user_name = CONCAT('h红米', SUBSTRING(user_name ,7)) where user_name like 'x小米加步枪%';
  

示例

我们以SUBSTRING函数为例试一下：

mysql> SELECT SUBSTRING('abc123', 2, 3);
+---------------------------+
| SUBSTRING('abc123', 2, 3) |
+---------------------------+
| bc1                       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

我们前边在唠叨表达式的说过，函数调用也算是一种表达式的操作数，它可以和其他操作数用操作符连接起来组成一个表达式来作为查询列表的一部分或者放到搜索条件中。我们来以CONCAT函数为例来看一下：

mysql> SELECT CONCAT('学号为', number, '的学生在《', subject, '》课程的成绩是：', score) AS 成绩描述 FROM student_score;
+---------------------------------------------------------------------------------------+
| 成绩描述                                                                              |
+---------------------------------------------------------------------------------------+
| 学号为20180101的学生在《母猪的产后护理》课程的成绩是：78                              |
| 学号为20180101的学生在《论萨达姆的战争准备》课程的成绩是：88                          |
| 学号为20180102的学生在《母猪的产后护理》课程的成绩是：100                             |
| 学号为20180102的学生在《论萨达姆的战争准备》课程的成绩是：98                          |
| 学号为20180103的学生在《母猪的产后护理》课程的成绩是：59                              |
| 学号为20180103的学生在《论萨达姆的战争准备》课程的成绩是：61                          |
| 学号为20180104的学生在《母猪的产后护理》课程的成绩是：55                              |
| 学号为20180104的学生在《论萨达姆的战争准备》课程的成绩是：46                          |
+---------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql>

数值处理函数

下边列举一些数学上常用到的函数，在遇到需要数学计算的业务时会很有用：

名称	调用示例	示例结果	描述
ABS	ABS(-1)	1	取绝对值
Pi	PI()	3.141593	返回圆周率
COS	COS(PI())	-1	返回一个角度的余弦
EXP	EXP(1)	2.718281828459045	返回e的指定次方
MOD	MOD(5,2)	1	返回除法的余数
RAND	RAND()	0.7537623539136372	返回一个随机数
SIN	SIN(PI()/2)	1	返回一个角度的正弦
SQRT	SQRT(9)	3	返回一个数的平方根
TAN	TAN(0)	0	返回一个角度的正切
ROUND	ROUND(5.55,1)	5.6	四舍五入（保留n位小数，不写则不保留小数）

• ABS 函数的参数为 NULL 时，结果也是 NULL。绝大多数函数对于 NULL 都返回 NULL，但是转换函数中的COALESCE函数除外
• 主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，使用“%”来计算余数。

聚合（统计）函数—列的纵向运算

如果将上边介绍的那些函数以函数调用的形式放在查询列表中，那么会为表中符合WHERE条件的每一条记录调用一次该函数。比方说这样：

mysql> SELECT number, LEFT(name, 1) FROM student_info WHERE number < 20180106;
+----------+---------------+
| number   | LEFT(name, 1) |
+----------+---------------+
| 20180101 | 杜            |
| 20180102 | 杜            |
| 20180103 | 范            |
| 20180104 | 史            |
| 20180105 | 范            |
+----------+---------------+
5 rows in set (0.00 sec)

mysql>

student_info表中符合number < 20180106搜索条件的每一条记录的name字段会依次被当作LEFT函数的参数，结果就是把这些人的名字的首个字符给提取出来了。但是有些函数是用来统计数据的，比方说统计一下表中的行数，某一列数据的最大值是什么，我们把这种函数称之为聚集函数，下边介绍MySQL中常用的几种聚集函数：

函数名	描述
COUNT	返回某列的行数
MAX	返回某列的最大值
MIN	返回某列的最小值
SUM	返回某列值之和，可利用 true 返回 1 false 返回 0 的特性
AVG	返回某列的平均值

常见错误注意

• 聚合函数只能用于 SELECT 子句和 HAVING 子句和 ORDER BY子句

• 在 WHERE 子句中使用聚合函数（不能使用）

• 聚合函数的计算，排除了NULL值。（解决：选择不包含NULL的列如主键 或 IFNULL函数）。与四则运算中若存在NULL，结果一定为NULL不同

• 聚合函数的结果为单行单列的值。

• 可以修改聚合函数列别名

• 聚合函数中使用比较运算符表达式时注意表达式为 true 时结果为1，false 时结果为0。此时count 和 sum 选择一定要注意

  select name, avg(score)
  from score group by name having sum(score < 60) >= 2;
  

COUNT函数

COUNT函数使用来统计行数的，它有下边两种使用方式：

1. COUNT(*)：对表中行的数目进行计数，不管列的值是不是NULL。只要有一列不为空则记录上！
2. COUNT(列名)：对特定的列进行计数，会忽略掉该列为NULL的行。

两者的区别是会不会忽略统计列的值为NULL的行！两者的区别是会不会忽略统计列的值为NULL的行！两者的区别是会不会忽略统计列的值为NULL的行！重要的事情说了3遍，希望你能记住。我们来数一下student_info表中有几行记录吧：

mysql> SELECT COUNT(*) FROM student_info;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql>

MAX函数（原则上适用任何数据类型，能排序的，如日期、字符串）

MAX函数是用来查询某列中数据的最大值，以student_score表中的score列为例来看一下：

mysql> SELECT MAX(score) FROM student_score;
+------------+
| MAX(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

mysql>

score列的最大值100就被查找出来了～

MIN函数（原则上适用任何数据类型，能排序的，如日期、字符串）

MIN函数是用来查询某列中数据的最小值，以student_score表中的score列为例来看一下：

mysql> SELECT MIN(score) FROM student_score;
+------------+
| MIN(score) |
+------------+
|         46 |
+------------+
1 row in set (0.00 sec)

mysql>

score列的最小值46就被查找出来了～

SUM函数（适用于数值类型）

SUM函数是用来计算某列数据的和，还是以student_score表中的score列为例来看一下：

mysql> SELECT SUM(score) FROM student_score;
+------------+
| SUM(score) |
+------------+
|        585 |
+------------+
1 row in set (0.01 sec)

mysql>

所有学生的成绩总和585就被查询出来了，比我们用自己算快多了哈～

AVG函数（适用于数值类型）

AVG函数是用来计算某列数据的平均数，还是以student_score表中的score列为例来看一下：

mysql> SELECT AVG(score) FROM student_score;
+------------+
| AVG(score) |
+------------+
|    73.1250 |
+------------+
1 row in set (0.00 sec)

mysql>

可以看到平均分就是73.1250。

给定搜索条件下聚合函数的使用

聚合函数并不是一定要统计一个表中的所有记录，我们也可以指定搜索条件来限定这些聚集函数作用的范围。比方说我们只想统计'母猪的产后护理'这门课程的平均分可以这么写：

mysql> SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理';
+------------+
| AVG(score) |
+------------+
|    73.0000 |
+------------+
1 row in set (0.00 sec)

mysql>

换句话说就是：不在搜索条件中的那些记录是不参与统计的。

聚合函数中DISTINCT的使用

默认情况下，上边介绍的聚合函数将计算指定列的所有非NULL数据，如果我们指定的列中有重复数据的话，可以选择使用DISTINCT来过滤掉这些重复数据。比方说我们想查看一下student_info表中存储了多少个专业的学生信息，就可以这么写：

mysql> SELECT COUNT(DISTINCT major) FROM student_info;
+-----------------------+
| COUNT(DISTINCT major) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.01 sec)

mysql>

可以看到一共有4个专业。

组合聚合函数

这些聚集函数也可以集中在一个查询中使用，比如这样：

mysql> SELECT COUNT(*) AS 成绩记录总数, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩, AVG(score) AS 平均成绩 FROM student_score;
+--------------------+--------------+--------------+--------------+
| 成绩记录总数       | 最高成绩     | 最低成绩     | 平均成绩     |
+--------------------+--------------+--------------+--------------+
|                  8 |          100 |           46 |      73.1250 |
+--------------------+--------------+--------------+--------------+
1 row in set (0.00 sec)

mysql>

类型转换函数

类型转换—CAST

【标准SQL】CAST

-- CAST(转换前的值 AS 想要转换的数据类型);

SELECT CAST('0001' AS INTEGER) AS int_col; -- SQL Server PostgreSQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col; -- MySQL
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL; -- Oracle
SELECT CAST('0001' AS INTEGER) AS int_col FROM SYSIBM.SYSDUMMY1; -- DB2

SELECT CAST('2009-12-14' AS DATE) AS date_col; -- SQL Server PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM DUAL; -- Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM SYSIBM.SYSDUMMY1; -- DB2

将字符串类型转换为整数类型时，前面的 “000”消失了，能够切实感到发生了转换。但是，将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化。类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便 DBMS 内部处理而开发的功能。

【Oracle】

• to_char
• to_date

转换NULL值—COALESCE

【标准SQL】COALESCE

将 NULL 转换为其他值，返回第一个非空数值

SELECT COALESCE(数据1,数据2,数据3...)
/* COALESCE 是 SQL 特有的函数。该函数会返回可变参数中左侧开始第1个不是 NULL 的值。*/
SELECT COALESCE(NULL, NULL, '2009-11-01') ... --SQL Server PostgreSQL MySQL
SELECT COALESCE(NULL, NULL, '2009-11-01') AS col_1 FROM DUAL; -- Oracle
SELECT COALESCE(NULL, NULL, '2009-11-01') AS col_1 FROM SYSIBM.SYSDUMMY1; --  DB2

【MySQL】IFNULL

SELECT IFNULL(null, 0)+1000; -- 如果存在NULL值，那么当成0来运算

【Oracle】NVL

隐式类型转换的场景

只要某个值的类型与上下文要求的类型不符，MySQL就会根据上下文环境中需要的类型对该值进行类型转换，由于这些类型转换都是MySQL自动完成的，所以也可以被称为隐式类型转换。我们列举几种常见的隐式类型转换的场景：

把操作数类型转换为适合操作符计算的相应类型

比方说对于加法操作符+来说，它要求两个操作数都必须是数字才能进行计算，所以如果某个操作数不是数字的话，会将其隐式转换为数字，比方说下边这几个例子：

1 + 2       →   3
'1' + 2     →   3
'1' + '2'   →   3

虽然'1'、'2'都是字符串，但是如果它们作为加法操作符+的操作数的话，都会被强制转换为数字，所以上边几个表达式其实都会被当作1 + 2去处理的，这些表达式被放在查询列表时的效果如下：

mysql> SELECT 1 + 2, '1' + 2, '1' + '2';
+-------+---------+-----------+
| 1 + 2 | '1' + 2 | '1' + '2' |
+-------+---------+-----------+
|     3 |       3 |         3 |
+-------+---------+-----------+
1 row in set (0.00 sec)

mysql>

将函数参数转换为该函数期望的类型

我们拿用于拼接字符串的CONCAT函数举例，这个函数以字符串类型的值作为参数，如果我们在调用这个函数的时候，传入了别的类型的值作为参数，MySQL会自动把这些值的类型转换为字符串类型的：

CONCAT('1', '2')    →   '12'
CONCAT('1', 2)      →   '12'
CONCAT(1, 2)        →   '12'

虽然1、2都是数字，但是如果它们作为CONCAT函数的参数的话，都会被强制转换为字符串，所以上边几个表达式其实都会被当作CONCAT('1', '2)去处理的，这些表达式被放到查询列表时的效果如下：

mysql> SELECT CONCAT('1', '2'), CONCAT('1', 2), CONCAT(1, 2);
+------------------+----------------+--------------+
| CONCAT('1', '2') | CONCAT('1', 2) | CONCAT(1, 2) |
+------------------+----------------+--------------+
| 12               | 12             | 12           |
+------------------+----------------+--------------+
1 row in set (0.00 sec)

mysql>

存储数据时，把某个值转换为某个列需要的类型

我们先新建一个简单的表t：

CREATE TABLE t (
    i1 TINYINT,
    i2 TINYINT,
    s VARCHAR(100)
);

这个表有三个列，列i1和i2是用来存储整数的，列s是用来存储字符串的，如果我们在存储数据的时候填入的不是期望的类型，就像这样：

mysql> INSERT INTO t(i1, i2, s) VALUES('100', '100', 200);
Query OK, 1 row affected (0.01 sec)

mysql>

我们为列i1和i2填入的值是一个字符串值：'100'，列s填入的值是一个整数值：200，虽然说类型都不对，但是由于隐式类型转换的存在，在插入数据的时候字符串'100'会被转型为整数100，整数200会被转型成字符串'200'，所以最后插入成功，我们来看一下效果：

mysql> SELECT * FROM t;
+------+------+------+
| i1   | i2   | s    |
+------+------+------+
|  100 |  100 | 200  |
+------+------+------+
1 row in set (0.00 sec)

mysql>

类型转换的注意事项

MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误

按理说'23sfd'这个字符串无法转换为数字，但是MySQL规定只要字符串的开头部分包含数字，那么就把这个字符串转换为开头的数字，如果开头并没有包含数字，那么将被转换成0，比方说这样：

'23sfd'         →   23
'2019-08-28'    →   2019
'11:30:32'      →   11
'sfd'           →   0

看个例子：

mysql> SELECT '23sfd' + 0, 'sfd' + 0;
+-------------+-----------+
| '23sfd' + 0 | 'sfd' + 0 |
+-------------+-----------+
|          23 |         0 |
+-------------+-----------+
1 row in set, 2 warnings (0.00 sec)

mysql>

不过需要注意的是，这种强制转换不能用于存储数据中（只能查询），比方说这样：

mysql> INSERT INTO t(i1, i2, s) VALUES('sfd', 'sfd', 'aaa');
ERROR 1366 (HY000): Incorrect integer value: 'sfd' for column 'i1' at row 1
mysql>

由于i1和i2列需要整数，而填入的字符串'sfd'并不能顺利的转为整数，所以报错了。

在运算时会自动提升操作数的类型

我们知道不同数据类型能表示的数值范围是不一样的，在小的数据类型经过算数计算后得出的结果可能大于该可以表示的范围。比方说t表中有一条记录如下：

mysql> SELECT * FROM t;
+------+------+------+
| i1   | i2   | s    |
+------+------+------+
|  100 |  100 | 200  |
+------+------+------+
1 row in set (0.00 sec)

mysql>

其中的i1列和i2列的类型都是TINYINT，而TINYINT能表示的最大正整数是127，如果我们把i1列的值和i2列的值相加会发生什么呢？请看：

mysql> SELECT i1 + i2 FROM t;
+---------+
| i1 + i2 |
+---------+
|     200 |
+---------+
1 row in set (0.00 sec)

mysql>

可以看到最后的结果是200，可是它已经超过TINYINT类型的表示范围了。其实在运算的过程中，MySQL自动将整数类型的操作数提升到了BIGINT，这样就不会产生运算结果太大超过TINYINT能表示的数值范围的尴尬情况了。类似的，有浮点数的运算过程会把操作数自动转型为DOUBLE类型。

提示

小贴士： 有隐式类型转换，自然也有显式类型转换。在MySQL中，可以使用CAST函数完成显式地类型转换，就是我们明确指定要将特定的数值转换为某种特定类型，一般是 MySQL 底层使用，初衷不是给开发者使用的。