MySQL 05 : 函数
合计/统计函数-count
– count()和count(列)的区别
– 解释:count(*)返回满足条件的记录的行数
– cout(列):统计满足条件的某列有多个,但是会排除为空的情况
-- 演示mysg1的统计函数的使用
-- 统计一个班级共有多少学生?
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) FROM student;
WHERE math >90;
-- 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) >250;
-- count()和count(列)的区别
-- 解释:count(*)返回满足条件的记录的行数
-- cout(列):统计满足条件的某列有多个,但是会排除为空的情况
CREATE TABLE t16
( `name` VARCHAR(20)) ;
INSERT INTO t16 VALUES('tom'),('jack'),('marry'),(NULL);
SELECT * FROM t16;
SELECT COUNT(*) FROM t16; -- 4
SELECT COUNT(`name`) FROM t16; -- 3
count(列名) 返回·列名·所在列非空的行数
count(*) 返回该表所以的行
合计函数-sum
-- sum
-- 统计一个班级数学总成绩?
SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS total_math,SUM(english) AS total_english,SUM(chinese) AS total_chinese FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(*) FROM student;
-- 注意:sum仅对数值起作用,否则会报错。
-- 注意:对多列求和,号不能少
合计函数-avg
-- 求一个班级数学平均分?
SELECT AVG(math),AVG(english),AVG(math+english+chinese) FROM student;
-- 求一个班级总分平均分
合计函数-Max/min
-- 求班级最高分和最低分(数值范围在统计中特别有用)
SELECT MAX(math+english+chinese),MIN(math+english+chinese) FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS high_math,MIN(math) FROM student;
使用group by子句对列进行分组[先创建测试表]
使用having子句对分组后的结果进行过滤
字符串相关函数
●练习:以首字母小写的方式显示所有员工emp表的姓名·
-- 演示字符甲相关函斑的使用
SELECT * FROM emp;
SELECT CHARSET(ename) FROM emp;
SELECT CONCAT(ename, '工作是',job) FROM emp;
-- dual 亚元表,系统表可以作为测试表使用
SELECT INSTR('hanshunping','ping') FROM DUAL;
SELECT LCASE(ename) FROM emp;
SELECT LEFT(ename,2) FROM emp;
SELECT RIGHT(ename,2) FROM emp;
SELECT LENGTH(ename) FROM emp;
SELECT LENGTH('hsp') FROM emp; -- 3. '韩顺平' 9
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
SELECT STRCMP('hsp','asp') FROM DUAL;
SELECT SUBSTRING(ename,2,2) FROM emp;
SELECT LTRIM(' 喊顺平教育') FROM DUAL;
SELECT RTRIM('喊顺平 ') FROM DUAL;
SELECT TRIM(' 喊顺平 ')FROM DUAL;
-- 练习:以首字母小写的方式显示所有员工emp表的姓名
SELECT * FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename)-2)) FROM emp;
方式一
– 练习:以首字母小写的方式显示所有员工emp表的姓名
SELECT * FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename)-2)) FROM emp;
方式二
– 练习:以首字母小写的方式显示所有员工emp表的姓名
SELECT * FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename)-2)) FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)),RIGHT(ename,LENGTH(ename)-1)) FROM emp;
方式三
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp;
数学相关函数
– RAND([seed])RAND([seed])返回随机数其范围为0≤v≤1.0
– 老韩说明
– 1。如果使用rand()每次返回不同的随机数,在0≤v≤1.0
– 2。如果使用rand(seed) 返回随机数,范围0≤v≤1.0,如果seed不变,该随机数也不变了
SELECT ABS(-10) FROM DUAL;
SELECT BIN(10) FROM DUAL;
SELECT CEILING(-1.1) FROM DUAL;
SELECT FLOOR(-1.1) FROM DUAL;
SELECT FORMAT(78.155456,2) FROM DUAL;
SELECT HEX(100) FROM DUAL;
SELECT MOD(10,3) FROM DUAL;
-- RAND([seed])RAND([seed])返回随机数其范围为0≤v≤1.0
-- 老韩说明
-- 1。如果使用rand()每次返回不同的随机数,在0≤v≤1.0
-- 2。如果使用rand(seed) 返回随机数,范围0≤v≤1.0,如果seed不变,该随机数也不变了
SELECT RAND(6) FROM DUAL;
时间日期相关函数 date。sql
YEAR MINUTE SECOND DAY
也可以自定义好TIMEDIFF中的参数,这样也可以输出具体时间,而不只是天
SELECT TIMEDIFF('12:10:10','10:12:12') FROM DUAL;-- 01:57:58
```
![请添加图片描述](https://img-blog.csdnimg.cn/bbc27841ae824f6cbc536a787aafe20e.png)
![请添加图片描述](https://img-blog.csdnimg.cn/310e4002a2fa454ab5bfed30803e10c4.png)
![请添加图片描述](https://img-blog.csdnimg.cn/ce5c162e3b6e46b48b0fccf8ee061deb.png)
````sql
SELECT CURRENT_DATE FROM DUAL;
SELECT CURRENT_TIME FROM DUAL;
SELECT CURRENT_TIMESTAMP FROM DUAL;
-- 创建测试表
CREATE TABLE mes(
id INT,
content VARCHAR(30),
sendtime DATETIME);
INSERT INTO mes
VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mes
VALUES(2,'上海新闻',CURRENT_TIMESTAMP);
INSERT INTO mes
VALUES(3,'石家庄新闻',NOW());
INSERT INTO mes
VALUES(4,'葫芦岛新闻','2023-1-14 10:10:10');
SELECT * FROM mes;
-- 显示所有留言信息,发布日期只显示日期,不用显示时间。
SELECT id,content,DATE(sendtime) FROM mes;
-- 请查询在10分钟内发布的帖子
SELECT * FROM mes
WHERE DATE_ADD(sendtime, INTERVAL 100 MINUTE) >= NOW();
SELECT * FROM mes
WHERE DATE_SUB(sendtime,INTERVAL -100 MINUTE) >= NOW();
SELECT * FROM mes
WHERE sendtime >= DATE_SUB(NOW(), INTERVAL 100 MINUTE);
-- 请在mysq的sql语句中求出2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF(CURRENT_TIM ESTAMP,'1998-11-14') FROM DUAL;
-- 请用mysql的sql语句求出你活了多少天?[练习]
SELECT CEILING(DATEDIFF(NOW(),'1998-11-14')/365) FROM DUAL;
-- 如果你能活80岁,求出你还能活多少天[练习]
SELECT FLOOR(DATEDIFF(DATE_ADD(NOW(),INTERVAL 80 YEAR),NOW())/365) FROM DUAL;
SELECT TIMEDIFF('12:10:10','10:12:12') FROM DUAL;-- 01:57:58
-- YEAR|Month DAY DATE (datetime
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT YEAR('2013-10-10') FROM DUAL;
-- unix t timestamp(),返回的是1970-1-1 到现在的毫秒数
SELECT UNIX_TIMESTAMP()/(24*3600*365) FROM DUAL;
-- FROM UNIXTIME 可以把一个可以把一个unix timestamp 秒数[时间戳],转成指定格式的日期.
-- %y-%m-%d格式是规定好的,表示年月日
-- 意义:在开发中,可以存放一个整数,然后表示时间,通过ROM UNIXTIME转换
SELECT FROM_UNIXTIME(1618483484,'%y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483484,'%y-%m-%d %H:%i:%s') FROM DUAL;
加密和系统函数
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户,以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32的字符串,常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- 演示用户表,存放密码时,是md5
CREATE TABLE hsp_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL注入问题
WHERE `name`='韩顺平' AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密
SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
流程控制函数
#IF(expr1,expr2,expr3)如果expr1为True,则返回expr2 否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;
SELECT IF(FALSE,'北京','上海') FROM DUAL;
#IFNULL(expl,expr2) 如果expr1不为空ULL,则返回expr1,否则返回expr2
SELECT IFNULL(NULL,'喊顺平') FROM DUAL;
#(SELECT CASE WHEN expr1 THEN e expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支.]
#如果expr1为TRUE,则返回expr2,如果expr2为t,返回expr4,否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack'
WHEN FALSE THEN 'tom'
ELSE 'mary'
END;
SELECT ename, IF(comm IS NULL,0.0,comm)
FROM emp;
SELECT ename,CASE -- 实际开发中用的不是太多
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售'
ELSE job END)
FROM emp;
SELECT ename, IFNULL(comm,0.0)
FROM emp;
多表查询—mysql表查询-加强
where 子句的加强
在mySQL中,日期类表可以直接比较
Order by 子句的加强
SELECT * FROM emp
WHERE hiredate >= '1992-01-01';
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%';
SELECT ename,sal FROM emp
WHERE ename LIKE '__O%' -- _ _ O
SELECT * FROM emp
WHERE mgr IS NULL;
SELECT * FROM salarygarde;
DESC emp;
-- ?如何按照工资的从低到高的顺序【升序】,显示雇员的信息
-- ?按照部门号升序而雇员的工资降序排列,显示雇员信息
SELECT * FROM emp
ORDER BY sal ASC; --DESC
SELECT * FROM emp
-- GROUP BY deptno,sal
ORDER BY deptno DESC,sal ASC;
分页查询加强
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 9,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 10,5;
SELECT * FROM emp
ORDER BY empno
LIMIT 20,5;
SELECT * FROM emp
ORDER BY empno
LIMIT 25,5;
分组增强
-- (1)显示每种岗位的雇员总数、平均工资
SELECT COUNT(*) , AVG(sal), job
FROM emp
GROUP BY job;
-- (2)显示雇员总数,获得补助的雇员数
-- 思路:获得补助的雇员数 就是comm列为非NULL
-- 如果该列的值为null,count是不会统计的
SELECT COUNT(*) ,COUNT(comm)
FROM emp
-- 统计没有拿到补助的员工的总数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
-- sql语句非常的灵活
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3)显示管理者的总人数
SELECT COUNT(*), COUNT(DISTINCT mgr) FROM emp
-- (4) 显示雇员工资的最大差额。
SELECT MAX(sal) - MIN(sal)
FROM emp
-- 小技巧:尝试写-》修改-》尝试[正确的]