表达式和函数

表达式

操作数

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

常数常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2019-08-16 17:10:43啥的。
列名针对某个具体的表，它的列名可以被当作表达式的一部分，比如对于student_info表来说，number、name都可以作为操作数。
函数调用MySQL中有函数的概念，比方说获取当前时间的函数NOW，而在函数后边加个小括号就算是一个函数调用，比如NOW()。如果你不清楚函数的概念，我们之后会详细唠叨的，现在不知道也可以～
标量子查询或者行子查询这个子查询我们稍后会详细唠叨的～
其他表达式一个表达式也可以作为一个操作数与另一个操作数来形成一个更复杂的表达式，比方说（假设col是一个列名）：
- (col - 5) / 3
- (1 + 1) 2 + col 3

操作符

算术操作符就是加减乘除法那一堆，我们看一下MySQL中都支持哪些：在使用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
<=	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。

逻辑操作符

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

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

表达式的使用

放在查询中

可以在查询student_score表时把score字段的数据都加50

sql

mysql> select number,subject,score+50 from student_score;
+----------+-----------------------------+----------+
| number   | subject                     | score+50 |
+----------+-----------------------------+----------+
| 20180101 | 母猪的产后护理              |      128 |
| 20180101 | 论萨达姆的战争准备          |      138 |
| 20180102 | 母猪的产后护理              |      150 |
| 20180102 | 论萨达姆的战争准备          |      148 |
| 20180103 | 母猪的产后护理              |      109 |
| 20180103 | 论萨达姆的战争准备          |      111 |
| 20180104 | 母猪的产后护理              |      105 |
| 20180104 | 论萨达姆的战争准备          |       96 |
+----------+-----------------------------+----------+
8 rows in set (0.02 sec)

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	将给定的各个字符串拼接成一个新字符串

以 CONCAT 函数组合查询

sql

mysql> select concat("学号", number, "的学生在课程", subject, "的分数是", score) from student_score;
+--------------------------------------------------------------------------------+
| concat("学号", number, "的学生在课程", subject, "的分数是", 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>

日期和时间处理函数

名称	调用示例	示例结果	描述
NOW	NOW()	2019-08-16 17:10:43	返回当前日期和时间
CURDATE	CURDATE()	2019-08-16	返回当前日期
CURTIME	CURTIME()	17:10:43	返回当前时间
DATE	DATE('2019-08-16 17:10:43')	2019-08-16	将给定日期和时间值的日期提取出来
DATE_ADD	DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-18 17:10:43	将给定的日期和时间值添加指定的时间间隔
DATE_SUB	DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-14 17:10:43	将给定的日期和时间值减去指定的时间间隔
DATEDIFF	DATEDIFF('2019-08-16', '2019-08-17');	-1	返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
DATE_FORMAT	DATE_FORMAT(NOW(),'%m-%d-%Y')	08-16-2019	用给定的格式显示日期和时间

在使用这些函数时需要注意一些地方：

在使用DATE_ADD和DATE_SUB这两个函数时需要注意，增加或减去的时间间隔单位可以自己定义，下边是MySQL支持的一些时间单位： | 时间单位 | 描述 | | --- | --- | | MICROSECOND | 毫秒 | | SECOND | 秒 | | MINUTE | 分钟 | | HOUR | 小时 | | DAY | 天 | | WEEK | 星期 | | MONTH | 月 | | QUARTER | 季度 | | YEAR | 年 |

如果我们相让 2019-08-16 17:10:43 这个时间值增加2分钟，可以这么写：

sql

mysql> select date_add("2019-08-16 17:10:43", interval 2 minute);
+----------------------------------------------------+
| date_add("2019-08-16 17:10:43", interval 2 minute) |
+----------------------------------------------------+
| 2019-08-16 17:12:43                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

在使用 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）

我们可以把我们想要的显示格式用对应的格式符描述出来

sql

mysql> select date_format(now(), "%b %d %Y %h");
+-----------------------------------+
| date_format(now(), "%b %d %Y %h") |
+-----------------------------------+
| Jun 01 2021 09                    |
+-----------------------------------+
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	返回一个角度的正切

聚集函数

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

COUNT函数

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

COUNT(*)：对表中行的数目进行计数，不管列的值是不是NULL。
COUNT(列名)：对特定的列进行计数，会忽略掉该列为NULL的行。

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

sql

mysql> select count(*) from student_info;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.09 sec)

mysql>

MAX MIN 函数

取表列中最大值，最小值

sql

mysql> select max(score) from student_score;
+------------+
| max(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

mysql> select min(score) from student_score;
+------------+
| min(score) |
+------------+
|         46 |
+------------+
1 row in set (0.00 sec)

mysql>

SUM 函数

求列的和

sql

mysql> select sum(score) from student_score;
+------------+
| sum(score) |
+------------+
|        585 |
+------------+
1 row in set (0.00 sec)

mysql>

AVG 函数

求平均数函数

sql

mysql> select avg(score) from student_score;
+------------+
| avg(score) |
+------------+
|    73.1250 |
+------------+
1 row in set (0.00 sec)

mysql>

给定搜索条件聚焦函数使用

sql

mysql> select avg(score) from student_score where subject like "%产后护理%";
+------------+
| avg(score) |
+------------+
|    73.0000 |
+------------+
1 row in set (0.00 sec)

mysql>

聚焦函数 DISTINCT 使用

指定的列中有重复数据的话，可以选择使用DISTINCT来过滤掉这些重复数据

sql

mysql> select count(distinct major) from student_info;
+-----------------------+
| count(distinct major) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

mysql>

可以看到总共只有 4 个科目

组合聚焦函数

sql

mysql> select count(*) as 成绩记录总数, max(score) as 最高成绩,min(score) as 最低成绩 from student_score;
+--------------------+--------------+--------------+
| 成绩记录总数       | 最高成绩     | 最低成绩     |
+--------------------+--------------+--------------+
|                  8 |          100 |           46 |
+--------------------+--------------+--------------+
1 row in set (0.00 sec)

mysql>

隐式类型转换

sql

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会自动把这些值的类型转换为字符串类型的：

sql

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

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

sql

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：

sql

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

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

sql

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'，所以最后插入成功，我们来看一下效果：

sql

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

mysql>

类型转换的注意事项

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

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

sql

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

看个例子：

sql

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

mysql>

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

sql

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表中有一条记录如下：

sql

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 列的值相加会发生什么呢？请看：

sql

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

mysql>

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

表达式和函数 ​

表达式 ​

操作数 ​

操作符 ​

表达式的使用 ​

函数 ​

文本处理函数 ​

日期和时间处理函数 ​

数值处理函数 ​

聚集函数 ​

COUNT函数 ​

MAX MIN 函数 ​

SUM 函数 ​

AVG 函数 ​

给定搜索条件聚焦函数使用 ​

聚焦函数 DISTINCT 使用 ​

组合聚焦函数 ​

隐式类型转换 ​

类型转换的注意事项 ​