mysql使用--表达式和函数

1.表达式
如：1+1，一般包含操作数，运算符。
_1.操作数
MYSQL中最常用的操作数有以下几种
(1).常数
(2).列名，针对某个具体的表，它的列名可被当作表达式的一部分
(3).函数调用
一个函数用于完成某个特定的功能。比如NOW()获取当前时间。
(4).其他表达式
一个表达式也可作为一个操作数与另一个操作数形成一个更复杂的形式。

_2.运算符
常用的有
(1).算术运算符

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

DIV取商的整数部分，/会保留小数部分。
(2).比较运算符

运算符	示例	解释
=	a=b	等于
<=>	a<=>b	等于（NULL值安全等于）
<>或!=	a<>b	不等于
<	a<b	小于
<=	a<=b	小于或等于
>	a>b	大于
>=	a>=b	大于等于
BETWEEN	a BETWEEN b AND c	a需满足b<=a<=c
NOT BETWEEN	a NOT BETWEEN b AND c	a需不满足b<=a<=c
IN	a IN (b1, b2)	a是b1,b2中的某个
NOT IN	a NOT IN (b1, b2, b3)	a不是b1,b2中的某个
IS NULL	a IS NULL
IS NOT NULL	a IS NOT NULL
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	不匹配

比较表达式结果要么1（TRUE），要么0（FALSE）
(3).逻辑运算符

运算符	示例	描述
NOT（也可写作!）	NOT a	对a取反
AND（也可写作&&）	a AND b
OR（也可写作		）
XOR	a XOR b	a和b有且只有一个为真时，表达式为真

_3.表达式的使用
_3.1.作为计算字段放在SELECT子句中
如：SELECT number, score+100 FROM student_score;
还有像这样的：SELECT 1, 'a' FROM student_score;
查询处理为：
(1).基于FROM结合WHERE得到结果集。
(2).对结果集每一行结合SELECT语句得到最终集中一行结果。

_3.2.作为搜索条件放在WHERE子句
基于FROME结合WHERE得到结果集时，对FROM得到表的每一行采用WHERE表达式，结果为TRUE，此行加入结果集。

_3.3.表达式中的NULL
_3.3.1.NULL作为算术符的操作数时，表达式的结果都为NULL
如：1+NULL结果是NULL，NULL*1结果也是NULL
_3.3.2.除<=>、IS NULL、IS NOT NULL外，NULL作为其余比较运算符的操作数时，表达式的结果都为NULL。
如：1=NULL结果是NULL，2>NULL结果是NULL。
IS NULL，IS NOT NULL用于判断某个值是否为NULL，结果只能是0或1。
<=>的操作数不包含NULL时，等价于=；当<=>的一个操作数为NULL，另一个不为NULL时，结果为0；两个操作数都为NULL时，结果为1；

2.函数
_1.字符串处理函数

名称	调用示例	结果	描述
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
CHAR_LENGTH	CHAR_LENGTH(‘狗哥’)	2	给定字符串的字符数量

_2.日期和时间处理函数

名称	示例	结果	描述
NOW	NOW()	2021-05-11 17:10:43	当前日期和时间
CURDATE	CURDATE()	2021-05-11
CURTIME	CURTIME()	17:10:43
DATE	DATE(‘2021-05-11 17:10:43’)	2021-05-11	将给定日期和时间值的日期提取出来
DATE_ADD	DATE_ADD(‘2021-05-11 17:10:43’, INTERVAL 2 DAY)	2021-05-13 17:10:43
DATE_SUB	DATE_SUB(‘2021-05-11 17:10:43’, INTERVAL 2 DAY)	2021-05-09 17:10:43
DATEDIFF	DATEDIFF(‘2021-05-11’, ‘2021-05-17’)	-6
DATE_FORMAT	DATE_FORMAT(NOW(), ‘%m-%d-%Y’)	05-11-2021
YEAR	YEAR(‘2021-05-11 17:10:43’)	2021
MONTH	MONTH(‘2021-05-11 17:10:43’)	5
DAY	DAY(‘2021-05-11 17:10:43’)	11
HOUR	HOUR(‘2021-05-11 17:10:43’)	17
MINUTE	MINUTE(‘2021-05-11 17:10:43’)	10
SECOND	SECOND(‘2021-05-11 17:10:43’)	43

_2.1.使用DATE_ADD，DATE_SUB时，可自定义增加或减去的时间间隔的单位。

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

如：SELECT DATE_ADD('2021-05-11 17:10:43', INTERVAL 2 MINUTE)

_2.2.使用DATE_FORMAT需注意，可通过一些所谓的格式符来自定义日期和时间的显示格式。

格式符	含义
%b	简写的月份名称（Jan、、、）
%D	带英文后缀的月份中的日期（0th，1st，…）
%d	数字格式的月份中的日期(00，01，…)
%f	微妙(000000~999999)
%H	24小时制的小时（00~23）
%h	12小时制的小时（01~12）
%i	数值格式的分钟（00~59）
%M	月份名（January，…）
%m	数值形式的月份（00~12）
%p	上午或下午（AM代表上午，PM代表下午）
%S	秒(00~59)
%s	秒(00~59)
%W	星期名(Sunday，…)
%w	周内第几天（0=星期日，…）
%Y	4位数字形式的年（例如2019）
%y	2位数字形式的年（例如19）

_2.3.数值处理函数

名称	示例	结果	描述
ABS	ABS(-1)	1
Pi	PI()	3.141593
COS	COS(PI())	-1
SIN	SIN(PI())	1
TAN	TAN(0)	0
POW	POW(2, 2)	4
SQRT	SQRT(9)	3
MOD	MOD(5, 2)	1
RAND	RAND()	0.7537623539136372
CEIL	CEIL(2.3)	3
FLOOR	FLOOR(2.3)	2

_2.4.流程控制表达式和函数
CASE WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2 …] [ELSE 默认结果] END
如：SELECT number, score, CASE WHEN score < 60 THEN '不及格' WHEN score < 90 THEN '及格' ELSE '优秀' END As level FROM student_score;

CASE表达式还有第二种形式：
CASE 待比较表达式 WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2 …] [ELSE 默认结果] END
它的含义是：
(1).待比较表达式值和表达式1值相同时，整个CASE值是结果1
(2).待比较表达式值和表达式2值相同时，整个CASE值是结果2
…
(3).待比较表达式值和所有WHEN后的表达式值都不同，则整个CASE表达式的值就是ELSE之后的默认结果。
如：SELECT name, department, CASE deparement WHEN '计算机学院' THEN '1级学科' WHEN '航天学院' THEN '2级学科' END AS 学院类别 FROM student_info;

除CASE语句外，还可用下面三个函数进行条件判断。
(1).IF(表达式1, 表达式2, 表达式3)
含义为：表达式1为TRUE时，返回值为表达式2；否则为表达式3；
(2).IFNULL(表达式1, 表达式2)
含义为：当表达式1为NULL时，返回值为表达式2；否则为表达式1；
(3).NULLIF(表达式1, 表达式2)
含义为：当表达式1值和表达式2值相同时，返回值为NULL；否则为表达式1；

_2.5.汇总函数
_1.常见函数
(1).MAX(表达式)
(2).MIN(表达式)
(3).SUM(表达式)
(4).AVG(表达式)
(5).COUNT(表达式)
上述函数的原理是，先从表运算得到结果集1，结果集1通过WHERE过滤得到结果集2，对结果集2的每一行执行汇总函数。结果集2中所有行处理完毕后，得到一个最终结果。
_2.汇总函数中DISTINCT的使用
如：SELECT COUNT(DISTINCT major) FROM student_info;
这样，结果集2中相同的表达式即使出现在多行，也只参与一次汇总函数运算。
_3.使用多个汇总函数
如：SELECT COUNT(*) AS 成绩记录总数, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩, AVG(score) AS 平均成绩 FROM student_score;

3.隐式类型转换
_1.将操作数类型转换为运算符需要的类型
如：SELECT 1+2, '1'+2, '1'+'2'结果中每一列都是数值3
一些字符串转换成数值的举例："12a"-->12，"2019-01"-->2019，"abc"-->0
_2.将函数参数转换成该函数期望的类型
指的是实参被转换为形参所需类型。
如：CONCAT('1', '2')，CONCAT('1', 2)，CONCAT(1, 2)结果都是'12'。
_3.在WHERE子句中，单独的字符串类型的表达式会被转换为DOUBLE类型的数值。
_4.存储数据时，把某个值转换为某个列需要的类型
类似与实参转换为形参。
注意的是，向表中插入记录时，发生字符串的表达式转换为DOUBLE时，不可产生截断。
'12a'->12产生截断，因为存在字符a未参与转换。
'12'->12未产生截断，因为所有字符都参与了转换。