2019年03月11日阅读 73

MySQL入门系列：查询简介（三）

上篇回顾

之前两篇介绍了基本的查询语句以及基本的带有搜索条件的查询语句，本篇继续深入介绍各种眼花缭乱的查询规则。

操作数

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

常数

常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2018-03-05 16:12:46啥的。
列名

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

MySQL中有函数的概念，比方说我们前边提到的获取当前时间的NOW就算是一个函数，而在函数后边加个小括号就算是一个函数调用，比如NOW()。
```
如果你不清楚函数的概念，我们之后会详细唠叨的，现在不知道也可以～
复制代码
```
子查询

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

操作符

至于操作符我们也都了解了一些，我们需要掌握的大致是下边这3种：

算术操作符

就是加减乘除法那一堆，我们看一下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。

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

比较操作符

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

操作符	示例	描述
`=`	`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

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

逻辑操作符

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

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

表达式的使用

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

作为查询对象

我们前边都是以列名作为查询对象的(*号代表所有的列名～)。列名只是表达式中超级简单的一种，我们可以将任意一个表达式作为查询对象来处理，比方说我们可以在查询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;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>
复制代码

虽然可以这么写，但是貌似没啥实际意义吧～

作为搜索条件

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

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

函数

对于某些我们会经常遇到的问题，MySQL内置了许多函数来帮我们解决这些问题。比方说UPPER函数是用来把给定的文本中的小写字母转换成大写字母，MONTH函数是用来把某个日期数据中的月份值提取出来等等。

如果我们想使用这些函数，可以在函数名后加一个小括号()就表示函数调用。比方说NOW()就代表调用NOW函数来获取当前时间。下边来介绍一些常用的MySQL内置函数：

文本处理函数

名称	调用示例	示例结果	描述
`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`	返回指定字符串从指定位置截取指定长度的子串
`CONCAT`	`CONCAT('abc', '123', 'xyz')`	`abc123xyz`	将给定的各个字符串参数拼接程一个新字符串

我们调用一下SUBSTRING函数：

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

mysql>
复制代码

我们前边在唠叨表达式的说过，函数调用也算是一种表达式的操作数，它可以和其他操作数和操作符连接起来组成一个表达式来用到查询对象和搜索条件处。我们来举个例子：

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>
复制代码

日期和时间处理函数

下边有些函数会用到当前日期，我编辑文章的日期是2018-02-28，在实际调用这些函数时以你的当前时间为准。

名称	调用示例	示例结果	描述
`NOW`	`NOW()`	`2018-02-28 09:24:10`	返回当前日期和时间
`CURDATE`	`CURDATE()`	`2018-02-28`	返回当前日期
`CURTIME`	`CURTIME()`	`09:24:10`	返回当前时间
`DATE`	`DATE('2018-02-28 09:24:10')`	`2018-02-28`	将给定时间值的日期提取出来
`DATE_ADD`	`DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 DAY)`	`2018-03-02 09:24:10`	给日期添加指定的时间间隔
`DATE_SUB`	`DATE_SUB('2018-02-28 09:24:10', INTERVAL 2 DAY)`	`2018-02-26 09:24:10`	从日期减去指定的时间间隔
`DATEDIFF`	`DATEDIFF('2018-02-27', '2018-02-28');`	`-1`	返回两个日期之间的天数
`DATE_FORMAT`	`DATE_FORMAT(NOW(),'%m-%d-%Y')`	`02-28-2018`	用不同的格式显示日期/时间

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

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

如果我们相让2018-02-28 09:24:10这个时间值增加2分钟，可以这么写：

mysql> SELECT DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 MINUTE);
+----------------------------------------------------+
| DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 MINUTE) |
+----------------------------------------------------+
| 2018-02-28 09:26:10                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>
复制代码

在使用DATE_FORMAT函数时需要注意，日期和时间的显式格式是我们自定义的，下边时MySQL中常用的一些格式：

格式	描述
`%D`	带有英文前缀的月中的天
`%d`	月的天，数值(00-31)
`%f`	微秒
`%H`	小时 (00-23)
`%h`	小时 (01-12)
`%i`	分钟，数值(00-59)
`%M`	月名
`%m`	月，数值(00-12)
`%p`	AM 或 PM
`%S`	秒(00-59)
`%s`	秒(00-59)
`%W`	星期名
`%w`	周的天（0=星期日, 6=星期六）
`%Y`	年，4 位
`%y`	年，2 位

比如我们再换一种格式输出一下当前时间：

mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| Feb 28 2018 10:30 AM                   |
+----------------------------------------+
1 row 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`	返回一个角度的正切

聚集函数

如果将上边介绍的那些函数用作查询对象，那么会为表中的每一条记录调用一次该函数。比方说这样：

mysql> SELECT LEFT(name, 1) FROM student_info;
+---------------+
| LEFT(name, 1) |
+---------------+
| 杜            |
| 杜            |
| 范            |
| 史            |
| 范            |
| 朱            |
+---------------+
6 rows in set (0.00 sec)

mysql>
复制代码

student_info表中的每一条记录name字段都会调用一次LEFT函数，所以结果就是把所有人名字的首个字符给提取出来了。但是有些函数是用来汇总数据的，比方说统计一下表中的行数，某一列数据的最大值是什么，我们把这种函数称之为聚集函数，下边介绍一些MySQL中常用的几种聚集函数：

函数名	描述
`COUNT`	返回某列的行数
`MAX`	返回某列的最大值
`MIN`	返回某列的最小值
`SUM`	返回某列值之和
`AVG`	返回某列的平均值

COUNT函数

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

COUNT(*)：对表中行的数目进行计数，不管列中包含的是不是NULL值。
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>
复制代码

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

MIN函数

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

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

mysql>
复制代码

最小值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>
复制代码

总结

表达式由操作数和操作符构成，单个的操作数也可以被当作是一个表达式，通常将表达式用在查询列表或者搜索条件处。

常用的操作符可以是下边这几种类型：
- 算数操作符
- 比较操作符
- 逻辑操作符
操作数可以是下边这几种类型：
- 常数
- 列名
- 函数调用
- 子查询
MySQL内置了许多函数来帮我们解决一些我们经常遇到的问题，我们常用到的函数有下边这些：
- 文本处理函数
- 日期和时间处理函数
- 数值处理函数
- 聚集函数
其中，聚集函数比较特殊，它是用来统计数据的。

小册

本系列专栏都是MySQL入门知识，想看进阶知识可以到小册中查看：MySQL是怎样运行的链接。小册的内容主要是从小白的角度出发，用比较通俗的语言讲解关于MySQL内核的一些核心概念，比如记录、索引、页面、表空间、查询优化、事务和锁等，总共的字数大约是三四十万字，配有上百幅原创插图。主要是想降低普通程序员学习MySQL内核的难度，让学习曲线更平滑一点～