第 15 章　存储过程与函数　·　第五节

常用内置函数

数学、字符串、日期和时间、系统信息、加密、聚合和控制流函数

本节知识点

18数学函数
19字符串函数
20日期和时间函数
21系统信息、加密与其他函数
22聚合函数

第 15 章 · 第五节 · 课件第 41、42 页合并重做

数学函数

四舍五入、截断、取整、取余最常用。

15.3 常用内置函数课件第 41、42 页合并

数学函数

内置函数分为数学、字符串、日期和时间、系统信息、聚合、加密和其他函数七类。数学函数中最常用的是求近似值的几个。

函数	功能	例子
`ROUND(x, n)`	四舍五入，保留 n 位小数	ROUND(5.6782, 2) 为 5.68
`TRUNCATE(x, n)`	截去 n 位以后的小数，不四舍五入	TRUNCATE(12.567, 2) 为 12.56
`CEILING(x)`	不小于 x 的最小整数	CEILING(-5.6) 为 -5
`FLOOR(x)`	不大于 x 的最大整数	FLOOR(-5.2) 为 -6
`ABS(x)`	绝对值	ABS(-3.4) 为 3.4
`MOD(x, y)`	x 除以 y 的余数	MOD(10, 3) 为 1
`SQRT(x)、POWER(x, y)`	平方根，x 的 y 次方	SQRT(16) 为 4，POWER(2, 4) 为 16
`RAND()`	0 到 1 之间的随机数	每次执行都不同
`PI()`	圆周率	3.141593

三角函数、对数函数等用到时再查。

实　验

实验 3-1　数学函数

第 15 章 · 第五节 · 课件第 43 页重做

字符串函数

连接、求长度、截取、查找和替换。

15.3 常用内置函数课件第 43 页重做

字符串函数

函数	功能	例子
`CONCAT(s1, s2, …)`	把几个字符串连起来	CONCAT('北京', '首都') 为北京首都
`CHAR_LENGTH(s)`	字符数	CHAR_LENGTH('北京') 为 2
`LENGTH(s)`	字节数，utf8mb4 中一个汉字 3 个字节	LENGTH('北京') 为 6
`LEFT(s, n)、RIGHT(s, n)`	左边、右边的 n 个字符	LEFT('BEIJING', 2) 为 BE
`SUBSTRING(s, i, n)`	从第 i 个字符开始取 n 个	SUBSTRING('BEIJING', 1, 3) 为 BEI
`LOCATE(s1, s)`	s1 在 s 中第一次出现的位置	LOCATE('ab', 'xyzabc') 为 4
`REPLACE(s, s1, s2)`	把 s 中的 s1 都换成 s2	REPLACE('aaa', 'a', 'b') 为 bbb
`UPPER(s)、LOWER(s)`	转为大写、小写	UPPER('beijing') 为 BEIJING
`TRIM(s)`	去掉两端的空格，LTRIM、RTRIM 只去一端	TRIM(' ab ') 为 ab
`REVERSE(s)`	把字符的顺序反过来	REVERSE('abc') 为 cba

重　点

汉字要用 CHAR_LENGTH 求字数，LENGTH 得到的是字节数。

实　验

实验 3-2　字符串函数

第 15 章 · 第五节 · 课件第 44、45 页合并重做

日期和时间函数

取当前时间、求间隔、按格式显示。

15.3 常用内置函数课件第 44、45 页合并

日期和时间函数

函数	功能	例子
`NOW()、CURDATE()、CURTIME()`	当前的日期和时间、日期、时间
`YEAR(d)、MONTH(d)、DAY(d)`	取出年、月、日	MONTH('2021-2-6') 为 2
`DATEDIFF(d1, d2)`	d1 与 d2 相差的天数	DATEDIFF('2021-1-1', '2020-11-26') 为 36
`DATE_ADD(d, INTERVAL n 单位)`	加上一段时间，DATE_SUB 减去	DATE_ADD('2021-02-06', INTERVAL 10 DAY) 为 2021-02-16
`DAYNAME(d)`	星期几的英文名	DAYNAME('2021-02-06') 为 Saturday
`DATE_FORMAT(d, f)`	按格式 f 显示日期	见右下

常用的格式符

格式符	含义
`%Y、%y`	四位、两位年份
`%m、%M`	两位月份、月份的英文名
`%d`	两位的日
`%H、%i、%s`	时、分、秒

DATE_FORMAT 的例子

SELECT DATE_FORMAT(
  '2021-02-06 11:07:45',
  '%Y年%m月%d日 %H:%i');
-- 2021年02月06日 11:07

实　验

实验 3-3　日期和时间函数

第 15 章 · 第五节 · 课件第 46、47、50、51 页合并重做

系统信息、加密与其他函数

查看当前连接的信息，加密口令，按条件取值。

15.3 常用内置函数课件第 46、47、50、51 页合并

系统信息、加密与其他函数

系统信息函数

函数	返回
`VERSION()`	数据库的版本号
`DATABASE()`	当前数据库名，与 SCHEMA() 相同
`USER()`	当前用户名
`CONNECTION_ID()`	当前连接的 ID
`LAST_INSERT_ID()`	最后一个自动增长的值

加密函数

函数	说明
`MD5(s)`	得到 32 位的密文，不能还原
`SHA1(s)`	得到 40 位的密文，不能还原
`AES_ENCRYPT(s, key)`	用密钥加密，AES_DECRYPT 用同一密钥解密

控制流与类型转换函数

函数	功能	例子
`IF(expr, v1, v2)`	expr 为真返回 v1，否则返回 v2	IF(50 < 100, 5, 10) 为 5
`IFNULL(v1, v2)`	v1 不为 NULL 返回 v1，否则返回 v2	IFNULL(1/0, 4) 为 4
`CAST(v AS 类型)`	把 v 转换为指定的类型	CAST(3.14 AS CHAR(4)) 为 '3.14'
`CONVERT(v, 类型)`	同上，写法不同	CONVERT('3.14', FLOAT) 为 3.14

重　点

第 14 章用户表的登录密码，存放的就是 MD5 等函数加密后的字符串。

实　验

实验 3-4　系统信息与加密函数

实　验

实验 3-6　控制流与类型转换函数

第 15 章 · 第五节 · 课件第 48、49 页合并重做

聚合函数

除 COUNT(*) 外，聚合函数都不计 NULL。

15.3 常用内置函数课件第 48、49 页合并

聚合函数

函数	功能
`COUNT(*)`	统计行数，含有 NULL 的行也算
`COUNT(列)`	统计该列不为 NULL 的值的个数，COUNT(DISTINCT 列) 去掉重复值后再统计
`SUM(列)、AVG(列)`	求和、平均值，只用于数值型的列，不包括 NULL
`MAX(列)、MIN(列)`	最大值、最小值
`STDDEV(列)、VARIANCE(列)`	标准差、方差

例 15-8　每人的选课门数和平均成绩

SELECT sno AS 学号, COUNT(*) AS 门数,
  ROUND(AVG(score), 2) AS 平均分
FROM sc
GROUP BY sno;

说明

聚合函数在第 6 章已经用过
s2 有一门课没有成绩，门数是 4，平均分只按 3 个成绩计算
s6 没有选课，结果中没有 s6

实　验

实验 3-5　聚合函数

上一步　实验实验二　运算符与流程控制下一步　实验实验三　常用内置函数

保山学院·人工智能教研室·曹鼎鼎

常用内置函数

数学函数

实 验

字符串函数

重 点

实 验

日期和时间函数

实 验

系统信息、加密与其他函数

重 点

实 验

实 验

聚合函数

实 验

实　验

重　点

实　验

实　验

重　点

实　验

实　验

实　验