一些重要函数
1、 合计/统计函数
（1）count --返回查询的结果有多少行
解释 :count(*) 返回满足条件的记录的行数
- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
（2）sum --返回满足where条件的行的和，一般使用在数值列
演示 sum 函数的使用 :
-- 统计一个班级数学总成绩?
SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student; --总分/人数
SELECT SUM(`name`) FROM student;--0 对非数值列求和没有意义
（3）avg --返回满足where条件的一列的平均值，一般使用在数值列
演示 avg 的使用：
-- 求一个班级数学平均分?
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
（4）max/min --返回满足where条件的一列的最大/最小值，一般使用在数值列
-- 求班级最高分和最低分(数值范围在统计中特别有用)
SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre FROM student;
（5）group by --队列进行分组 having --对分组后的结果进行过滤
-- ?如何显示每个部门的平均工资和最高工资
-- 分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECT AVG(sal), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
FROM emp GROUP BY deptno, job;
having 子句用于限制分组显示结果。
-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 老师分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) < 2000;
-- 使用别名
SELECT AVG(sal) AS avg_sal, deptno
FROM emp
GROUP BY deptno
HAVING avg_sal < 2000;
2、 字符串相关函数
(1) CHARSET
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
(2) CONCAT
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, '工作是', job) AS 'introduction' from emp;
(3) INSTR
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0 -- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
(4) UCASE/LCASE
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
(5) LEFT/ RIGHT
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp;
(6) LENGTH
-- LENGTH (string ) string 长度[按照字节]
SELECT LENGTH(ename) FROM emp;
(7) REPLACE
-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT REPLACE(job,'MANAGER','经理') FROM emp;
注意：这里只是查找结果被替换，原表是不会被替换的
(8) STRCMP
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL;
SELECT STRCMP('hsp', 'hisp') FROM DUAL;
SELECT STRCMP('hisp', 'hsp') FROM DUAL;
(9) SUBSTRING
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始[从 1 开始计算],取 length 个字符
-- 从 ename 列的第 1 个位置开始取出 2 个字符
-- 如果没有 length 限制，则从 position 位置开始，截取到最后
SELECT SUBSTRING(ename, 1, 2) FROM emp;
(10) LTRIM / RTRIM / TRIM
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' hspjy') FROM DUAL;
SELECT RTRIM('hspjy ') FROM DUAL;
SELECT TRIM(' hspjy ') FROM DUAL;
3、 数学相关函数
(1) ABS --求绝对值
-- ABS(num) 绝对值
SELECT ABS(-5) FROM DUAL;
(2) BIN --十进制转二进制
-- BIN (decimal_number) 十进制转二进制
SELECT BIN(10) FROM DUAL;
(3) CEILING --向上取整
-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
(4) CONV --进制转换
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 16 是16进制的16, 转成 10 进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
(5) FLOOR --向下取整
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
(6) FORMAT --保留小数位数(四舍五入)
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
(7) HEX --转十六进制
-- HEX (DecimalNumber ) 转十六进制
SELECT HEX(16) FROM DUAL;
(8) LEAST --求最小值
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
(9) MOD --求余
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL;
(10) RAND --返回随机数 其范围为 0 ≤ v ≤ 1.0
-- RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT RAND(3) FROM DUAL;
4、 时间日期相关函数
(1) CURRENT_DATE 当前日期\ CURRENT_TIME 当前时间\ CURRENT_TIMESTAMP 当前时间戳
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
可以带小括号，也可以不带
(2) DATE 日期\ DATE_ADD\ DATE_SUB\ DATEDIFF
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time) FROM mes;
-- 请查询在 12 分钟内发布的新闻, 思路一定要梳理一下.
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 12 MINUTE) >= NOW();
SELECT * FROM mes
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 12 MINUTE)
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1998-11-2') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习]
-- 然后在使用 datediff(x, now());
-- INTERVAL 80 YEAR : YEAR 可以是 年月日，时分秒
SELECT DATEDIFF(DATE_ADD('1998-11-2',INTERVAL 80 year),NOW()) FROM DUAL;
上面函数的细节说明:
- DATE_ADD()中的 interval 后面可以是 year/ minute/ second/ day 等
- DATE_SUB() 中的 interval 后面可以是 year/ minute/ second/ hour/ day 等
- DATEDIFF(date1,date2) 得到的是天数，而且是date1-date2 的天数，因此可以取负数
- 这四个函数的日期类型可以是 date, datetime 或者 timestamp
(3) TIMEDIFF\ NOW()\YEAR|MONTH|DAY
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义:在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换 --
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d %H:%i:%s') FROM DUAL;--加了时分秒
5、 加密和系统函数
(1) USER --查看登录到 mysql 的有哪些用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL;
(2) DATABASE --查询当前使用数据库名称
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
(3) MD5 --为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 zhuang -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('zhuang') FROM DUAL;
SELECT LENGTH(MD5('zhuang')) FROM DUAL; --可以直接计算 MD5 的长度--输出32
(4) PASSWORD --加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('zhuang') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
5、 流程控制函数
(1) IF(expr1,expr2,expr3) / IFNULL(expr1,expr2)
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
-- 老师说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
(2) SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2;如果 expr3 为 true, 返回 expr4;否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END;
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp;
总结
以上是从韩顺平老师教程里截出来的笔记以供自己复习使用。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
