目录
🎇一、聚合函数
提供 student 表
1️⃣count 函数
count 表示返回行的总数
（1）基本语法
SELECTCOUNT(*|列名)FROM 表名
WHERE 条件;
（2）基本练习
统计一个班级共有多少学生？
SELECTCOUNT(*)FROM student;
统计数学成绩大于 90 的学生有多少个？
SELECTCOUNT(*)FROM student
WHERE math >90;
统计总分大于 250 的人数有多少？
SELECTCOUNT(*)FROM student
WHERE(math + english + chinese)>250
（3）注意细节
**
count(*) 和 count(列) 的区别：
**
**
count(*) 返回满足条件的记录的行数
count(列): 统计满足条件的某列有多少个，但是会排除为 null 的情况
**
2️⃣sum 函数
sum函数返回满足where条件的行的和，一般使用于数值列
（1）基本语法
SELECTSUM(列名)FROM 表名
WHERE 条件;
（2）基本练习
统计一个班级数学总成绩
SELECTSUM(math)FROM student;
统计一个班级语文、英语、数学各科的总成绩
SELECTSUM(math),SUM(english),SUM(chinese)FROM student;
统计一个班级语文、英语、数学的成绩总和
SELECTSUM(math + english + chinese)FROM student;
统计一个班级语文成绩平均分
SELECTSUM(chinese)/COUNT(*)FROM student;
（3）注意细节
**
sum 函数仅对数值起作用，对多行求和需用逗号隔开
**
3️⃣avg 函数
avg函数返回满足where条件的一列的平均值
（1）基本语法
SELECTAVG(列名)FROM 表名
WHERE 条件;
（2）基本练习
求一个班级数学平均分？
SELECTAVG(math)FROM student;
求一个班级总分平均分
SELECTAVG(math + english + chinese)FROM student;
4️⃣max/min 函数
max/min 函数返回满足 where 条件的一列的最大/最小值
（1）基本语法
SELECTMAX(列名)FROM 表名
WHERE 条件;SELECTMIN(列名)FROM 表名
WHERE 条件;
（2）基本练习
求班级最高分和最低分
SELECTMAX(math + english + chinese),MIN(math + english + chinese)FROM student;
求出班级数学最高分和最低分
SELECTMAX(math),MIN(math)FROM student;
🎆二、字符串相关函数
1️⃣字符串常用函数一览表
函数用法说明CHARSET(str)返回字串字符集CONCAT (string2 [,… ])连接字串, 将多个列拼接成一列INSTR (string ,substring )返回 substring 在 string 中出现的位置,没有返回 0UCASE (string2)转换成大写LCASE (string2)转换成小写LEFT (string2 ,length)从 string2 中的左边起取 length 个字符RIGHT (string2 ,length)从 string2 中的右边起取 length 个字符LENGTH (string)string 长度[按照字节]REPLACE (str ,search_str ,replace_str )在 str 中用 replace_str 替换 search_strSTRCMP (string1 ,string2)逐字符比较两字串大小SUBSTRING (str , position [,length ])从 str 的 position 开始【从 1 开始计算】,取 length 个字符LTRIM (string)去除前端空格RTRIM (string)去除后端空格TRIM(string)去除前后两端空格
2️⃣案例演示
以 emp 表为例
--CHARSET(str) 返回字串字符集
SELECTCHARSET(ename)FROM emp;--CONCAT(string2 [,...]) 连接字串, 将多个列拼接成一列
SELECTCONCAT(ename,'工作是', job)FROM emp;--INSTR(string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0-- dual 亚元表, 系统表 可以作为测试表使用
SELECTINSTR('jack','c')FROMDUAL;--UCASE(string2) 转换成大写
SELECTUCASE(ename)FROM emp;--LCASE(string2) 转换成小写
SELECTLCASE(ename)FROM emp;--LEFT(string2 ,length)从 string2 中的左边起取 length 个字符
SELECTLEFT(ename,2)FROM emp;--RIGHT(string2 ,length) 从 string2 中的右边起取 length 个字符
SELECTRIGHT(ename,2)FROM emp;--LENGTH(string)string 长度[按照字节]SELECTLENGTH(ename)FROM emp;SELECTLENGTH('邱崇源')FROM emp;--9个字节
--REPLACE(str ,search_str ,replace_str )在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename,REPLACE(job,'MANAGER','经理')FROM emp;--STRCMP(string1 ,string2) 逐字符比较两字串大小
SELECTSTRCMP('abc','bbc')FROM emp;--SUBSTRING(str , position [,length ])-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECTSUBSTRING(ename,1,2)FROM emp;--LTRIM(string2 )RTRIM(string2 )TRIM(string)-- 去除前端空格或后端空格
SELECTLTRIM(' 邱崇源')FROMDUAL;SELECTRTRIM('邱崇源 ')FROMDUAL;SELECTTRIM(' 邱崇源 ')FROMDUAL;
✨三、数学相关函数
1️⃣数学常用函数一览表
函数用法说明ABS(num)绝对值BIN (decimal_number)十进制转二进制CEILING (number2)向上取整, 得到比 num2 大的最小整数CONV(number2,from_base,to_base)进制转换FLOOR (number2)向下取整,得到比 num2 小的最大整数FORMAT (number,decimal_places)保留小数位数(四舍五入)HEX (DecimalNumber)转十六进制LEAST (number , number2 [,…])求最小值MOD (numerator ,denominator)求余RAND([seed])返回随机数 其范围为 0 ≤ v ≤ 1.0
2️⃣案例演示
-- 演示数学相关函数
--ABS(num) 绝对值
SELECTABS(-10)FROMDUAL;--BIN(decimal_number )十进制转二进制
SELECTBIN(10)FROMDUAL;--CEILING(number2 ) 向上取整, 得到比 num2 大的最小整数
SELECTCEILING(-1.1)FROMDUAL;--CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECTCONV(8,10,2)FROMDUAL;-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECTCONV(16,16,10)FROMDUAL;--FLOOR(number2 ) 向下取整,得到比 num2 小的最大整数
SELECTFLOOR(1.1)FROMDUAL;--FORMAT(number,decimal_places )保留小数位数(四舍五入)SELECTFORMAT(78.125458,2)FROMDUAL;--HEX(DecimalNumber ) 转十六进制
--LEAST(number , number2 [,..]) 求最小值
SELECTLEAST(0,1,-10,4)FROMDUAL;--MOD(numerator ,denominator ) 求余
SELECTMOD(10,3)FROMDUAL;--RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0-- 说明
--1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0--2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECTRAND()FROMDUAL;
🎄四、时间日期相关函数
1️⃣时间日期常用函数一览表
函数用法说明CURRENT_DATE ( )当前日期CURRENT_TIME ( )当前时间CURRENT_TIMESTAMP ( )当前时间戳DATE_ADD(send_time, INTERVAL d_value d_type)在date2中加上一个日期或时间DATE_SUB(send_time, INTERVAL d_value d_type)在date2中减去一个日期或时间DATEDIFF(date1,date2)两个日期差（结果是天）TIMEDIFF(date1,date2)两个时间差（结果是多少小时多少分钟多少秒）NOW()当前时间YEAR/Month/DAY (datetime)取时间的年月日unix_timestamp()返回的是 1970-1-1 到现在的秒数FROM_UNIXTIME()可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
2️⃣案例演示
建立mes表
CREATETABLEmes(
id INT,
content VARCHAR(30),
send_time DATETIME);INSERTINTO mes VALUES(1,'界面打开',CURRENT_TIMESTAMP());INSERTINTO mes VALUES(2,'鸡腿肉',NOW());INSERTINTO mes VALUES(3,'圣诞狗狗',NOW());
-- 日期时间相关函数
--CURRENT_DATE() 当前日期
SELECTCURRENT_DATE()FROMDUAL;--CURRENT_TIME()当前时间
SELECTCURRENT_TIME()FROMDUAL;--CURRENT_TIMESTAMP() 当前时间戳
SELECTCURRENT_TIMESTAMP()FROMDUAL;-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.SELECT id,content,DATE(send_time)FROM mes;-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下.SELECT*FROM mes WHEREDATE_ADD(send_time,INTERVAL10MINUTE)>=NOW();-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECTDATEDIFF('2011-11-11','1990-01-01')FROMDUAL;-- 请用 mysql 的 sql 语句求出你活了多少天?SELECTDATEDIFF(NOW(),'2000-05-24')FROMDUAL;-- 如果你能活 80 岁，求出你还能活多少天.SELECTDATEDIFF('2080-05-24',NOW())FROMDUAL;SELECTTIMEDIFF('10:11:11','06:10:10')FROMDUAL;--YEAR|Month|DAY|(datetime)SELECTYEAR(NOW())FROMDUAL;SELECTMONTH(NOW())FROMDUAL;SELECTDAY(NOW())FROMDUAL;--unix_timestamp(): 返回的是 1970-1-1 到现在的秒数
SELECTUNIX_TIMESTAMP()FROMDUAL;--FROM_UNIXTIME(): 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
--%Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
SELECTFROM_UNIXTIME(1672307500,'%Y-%m-%d %H:%i:%s');
🍱五、加密和系统函数
1️⃣加密和系统函数一览表
函数用法说明USER()查询用户DATABASE()查询当前使用数据库名称MD5(str)为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密PASSWORD(str)加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
2️⃣案例演示
-- 演示加密函数和系统函数
--USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IPSELECTUSER()FROMDUAL;-- 用户@IP 地址
--DATABASE()查询当前使用数据库名称
SELECTDATABASE();--MD5(str) 为字符串算出一个 MD532的字符串，常用(用户密码)加密
-- root 密码是 qcy -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECTMD5('qcy')FROMDUAL;SELECTLENGTH(MD5('qcy'));--32--PASSWORD(str)-- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECTPASSWORD('qcy')FROMDUAL;
🎋六、流程控制函数
1️⃣流程控制函数一览表
函数用法说明IF(expr1,expr2,expr3)如果 expr1 为 True ,则返回 expr2 否则返回 expr3IFNULL(expr1,expr2)如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]如果 expr1 为 TRUE,则返回 expr2,如果 expr3 为 TRUE, 返回 expr4, 否则返回 expr5
2️⃣案例演示
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECTIF(TRUE,'北京','上海')FROMDUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECTIFNULL(NULL,'韩顺平教育')FROMDUAL;
# SELECTCASEWHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr3 为 TRUE, 返回 expr4, 否则返回 expr5
SELECTCASEWHENTRUETHEN'jack'-- jack
WHENFALSETHEN'tom'ELSE'mary'END--1. 查询 emp 表, 如果 comm 是 null, 则显示 0.0-- 说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not nullSELECT ename,IFNULL(comm,0.0)FROM emp;--2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename,(SELECTCASEWHEN job ='CLERK'THEN'职员'WHEN job ='MANAGER'THEN'经理'WHEN job ='SALESMAN'THEN'销售人员'ELSE job END)AS'job'FROM emp;
版权归原作者 邱崇源 所有， 如有侵权，请联系我们删除。