MySQL 05 : 函数

合计/统计函数-count

– count()和count(列)的区别

– 解释：count(*)返回满足条件的记录的行数

– cout(列)：统计满足条件的某列有多个,但是会排除为空的情况

-- 演示mysg1的统计函数的使用-- 统计一个班级共有多少学生？ 
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少个？
SELECT COUNT(*) FROM student;WHERE math >90;
-- 统计总分大于250的人数有多少？ 
SELECT COUNT(*) FROM studentWHERE (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; -- 4SELECT 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.  '韩顺平' 9SELECT 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 mesVALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mesVALUES(2,'上海新闻',CURRENT_TIMESTAMP);
INSERT INTO mesVALUES(3,'石家庄新闻',NOW());					
INSERT INTO mesVALUES(4,'葫芦岛新闻','2023-1-14 10:10:10');								SELECT * FROM mes;			
-- 显示所有留言信息，发布日期只显示日期，不用显示时间。 
SELECT id,content,DATE(sendtime) FROM mes;
-- 请查询在10分钟内发布的帖子
SELECT * FROM mesWHERE DATE_ADD(sendtime, INTERVAL 100 MINUTE) >= NOW();
SELECT * FROM mesWHERE DATE_SUB(sendtime,INTERVAL -100 MINUTE) >= NOW();	
SELECT * FROM mesWHERE 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; -- csdnSELECT * 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 否则返回expr3SELECT 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 empWHERE hiredate >= '1992-01-01';
SELECT ename,sal  FROM empWHERE ename LIKE 'S%';
SELECT ename,sal FROM empWHERE ename LIKE '__O%' -- _ _ O
SELECT * FROM empWHERE mgr IS NULL;SELECT * FROM salarygarde;
DESC emp;-- ？如何按照工资的从低到高的顺序【升序】，显示雇员的信息 
-- ？按照部门号升序而雇员的工资降序排列，显示雇员信息SELECT * FROM empORDER BY sal ASC; --DESCSELECT * FROM emp	-- GROUP BY deptno,sal ORDER BY deptno DESC,sal ASC;

分页查询加强

SELECT * FROM empORDER BY empnoLIMIT 0,3;
SELECT * FROM empORDER BY empnoLIMIT 3,3;
SELECT * FROM empORDER BY empnoLIMIT 6,3;

SELECT * FROM empORDER BY empnoLIMIT 0,3;
SELECT * FROM empORDER BY empnoLIMIT 3,3;
SELECT * FROM empORDER BY empnoLIMIT 6,3;SELECT * FROM empORDER BY empnoLIMIT 9,3;SELECT * FROM empORDER BY empnoLIMIT 10,5;		SELECT * FROM empORDER BY empnoLIMIT 20,5;				SELECT * FROM empORDER BY empnoLIMIT 25,5;							

分组加强

数据分组的总结

固定顺序group by - having - order by - limit