MySQL（常用函数、多表查询）

1.数据库函数

1.count函数

案例

答案

-- 统计一个班级共有多少学生
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的有多少个
SELECT COUNT(*) FROM student WHERE math > 90;
-- 统计总分大于250的人数有多少人
SELECT COUNT(*) FROM STUDENT WHERE (MATH + CHINESE + ENGLISH) > 250;

count（*）与count（列）的区别

1. count（*）会返回满足条件记录的行数
2. count（列）会统计满足条件的某列有多少行但是会排除null

2.sum函数

案例

答案

-- 统计一个班级数学总成绩
SELECT SUM(MATH) FROM student;
-- 统计一个班级，语文，英语，数学各科的总成绩
SELECT SUM(CHINESE), SUM(ENGLISH), SUM(MATH) FROM student; 
-- 统计一个班级语文，英语，数学的成绩总和
SELECT SUM(CHINESE) + SUM(ENGLISH) + SUM(MATH) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(CHINESE) / COUNT(*) FROM student;

3.avg函数

案例

答案

-- 求一个班级的数学平均分
SELECT AVG(MATH) FROM STUDENT;
-- 求一个班级总分平均分
SELECT AVG(MATH + CHINESE + ENGLISH) FROM STUDENT;

4.max/min函数

案例

答案

-- 求班级最高分和最低分
SELECT MAX(MATH + CHINESE + ENGLISH) FROM STUDENT;
SELECT MIN(MATH + CHINESE + ENGLISH) FROM STUDENT;

5.group by 分组统计

案例

答案

-- 显示每个部门的平均工资和最高工资
SELECT AVG(SAL), MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(SAL), MIN(SAL),DEPTNO,JOB FROM EMP GROUP BY DEPTNO, JOB;
-- 显示平均工资低于2000的部门号和他的平均工资，别名
SELECT DEPTNO, AVG(SAL) AS 平均工资 FROM EMP GROUP BY DEPTNO HAVING 平均工资 < 2000; 

6.字符串相关函数

演示

-- 返回字符串的字符集
SELECT CHARSET(ENAME) FROM EMP;
-- 连接子串将多个列拼成一列
SELECT CONCAT(ENAME,'工作是',JOB) FROM EMP;
-- INSTR (string, substring) 返回substring在string中出现的位置，没有返回0
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- 转换成大写
SELECT UCASE(ENAME) FROM EMP;
-- 转换成小写
SELECT LCASE(ENAME) FROM EMP;
-- LEFT(string2, length) 从string2中的左边起取length个字符
SELECT LEFT(ENAME, 3) FROM EMP;
-- LENGTH(str) str的长度，按照字节
SELECT LENGTH('孙显圣') FROM EMP;
-- REPLACE(str,from_str,to_str) 将str列的from_str，替换为to_str
SELECT REPLACE(JOB,'MANAGER','经理') FROM EMP;
-- STRCMP(expr1,expr2) 逐个字符比较两字符串大小
SELECT STRCMP('HSP','AHP') FROM DUAL;
-- SUBSTRING(str FROM pos FOR len) 从str的pos开始（从1开始）取出len个字符
SELECT SUBSTRING(ENAME, 1, 2) FROM EMP;
-- 去除空格
SELECT LTRIM('  韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;

练习

-- 将第一个 字符取出来然后转为小写，与剩下的字符拼接
SELECT CONCAT(LCASE(SUBSTRING(ENAME, 1, 1)),SUBSTRING(ENAME, 2,LENGTH(ENAME) - 1))  FROM EMP; 

7.数学相关函数

演示

-- ABS(X) 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN(N) 十进制转2进制
SELECT BIN(10) FROM DUAL;
-- CEILING(X) 向上取整
SELECT CEILING(10.3) FROM DUAL;
-- CONV(N,from_base,to_base) 进制转换
SELECT CONV(10,10,2) FROM DUAL;
-- FLOOR(X) 向下取整
SELECT FLOOR(10.3) FROM DUAL;
-- FORMAT(X,D) 保留小数位数
SELECT FORMAT(10.3234,2) FROM DUAL;
-- HEX(N_or_S) 转16进制
SELECT HEX(100) FROM DUAL; 
-- LEAST(value1,value2,...) 求最小值
SELECT LEAST(10,2,3) FROM DUAL;
-- MOD(N,M) 取余
SELECT MOD(5,2) FROM DUAL;
-- RAND() 取随机数,0到1，如果是带参数的则每次取出来的就是相同的随机数
SELECT RAND() FROM DUAL;
SELECT RAND(3) FROM DUAL;

8.日期相关函数

演示

-- 显示所有新闻信息，发布时间只显示日期，不用显示时间
SELECT id, content, DATE(send_time) FROM mes;
-- 查询在10分钟内发布的帖子
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE) <= send_time;
-- 求出2011-11-11 和 1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
-- 求出你活了多少天
SELECT DATEDIFF(CURRENT_DATE,'2002-12-8') FROM DUAL;
-- 如果能活80岁，还能活几天
SELECT  DATEDIFF(DATE_ADD('2002-12-8',INTERVAL 80 YEAR),CURRENT_DATE) FROM DUAL;
-- 返回1970年到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- 把unix_timestamp时间戳转换成指定格式的日期
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H-%i-%s') FROM DUAL;
-- 返回指定的年月日
SELECT YEAR('2023-12-8'), MONTH('2023-12-8'),DAY('2023-12-8') FROM DUAL;

9.加密函数

演示

-- 查询用户
SELECT USER() FROM DUAL;
-- 查询当前使用数据库名称
SELECT DATABASE() FROM DUAL;
-- 为字符串算出一个MD5加密的字符串
SELECT MD5('SXS') FROM DUAL;
-- 演示在数据库中将密码使用md5加密并且查询
CREATE TABLE `user` (id INT,`name` VARCHAR(32) NOT NULL DEFAULT '',pwd CHAR(32) NOT NULL DEFAULT ''
)
-- 插入信息
INSERT INTO USER VALUES(1, 'root', MD5('123456'))
-- 查询MD5加密之后的数据
SELECT * FROM user WHERE name = 'root' AND pwd = MD5('123456')

10.流程控制函数

演示

-- 查询emp表，如果comm是null，显示0.0,
SELECT ENAME, IF(COMM IS NULL, 0.0, COMM) FROM EMP;
SELECT ENAME, IFNULL(COMM,0.0) FROM EMP;
-- 如果emp表的job是clerk，则显示是职员，如果是manager则显示是经理，如果是saleman则显示销售人员
SELECT ename, (SELECT CASE WHEN job = 'CLERK' THEN '职员'WHEN job = 'MANAGER' THEN '经理'WHEN job = 'SALESMAN' THEN '销售人员'ELSE job END) FROM emp;

2.查询增强

增强一

案例

-- 在mysql中，日期类型可以直接比较，但是格式要相同
-- 查询1992.1.1之后入职的员工
SELECT * FROM EMP WHERE HIREDATE > '1992-01-01';
-- 显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp WHERE ename LIKE 'S%';
-- 显示第三个字符为大写的O的所有员工的姓名和薪资
SELECT ename, sal FROM emp WHERE ename LIKE '__O%';
-- 显示没有上级的雇员的情况
SELECT * FROM emp WHERE mgr IS NULL;
-- 查询表结构
DESC emp;

增强二 order by

案例

-- 按照工资从低到高的顺序显示雇员的信息
SELECT * FROM emp ORDER BY sal 
-- 按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM empORDER BY deptno , sal DESC;

增强三 分页查询

案例

-- 按雇员的id升序取出,每页显示3条记录,请分别显示第1页,第2页,第3页
-- 第一页
SELECT * FROM emp 
ORDER BY empno ASC
LIMIT 0, 3
-- 第二页
SELECT * FROM emp 
ORDER BY empno ASC
LIMIT 3, 3
-- 第三页
SELECT * FROM emp 
ORDER BY empno ASC
LIMIT 6, 3

求第几页的公式是

每页显示记录数 * （第几页 - 1），每页显示记录数

练习

-- 第三页
SELECT * FROM EMP 
ORDER BY EMPNO DESC
LIMIT 10, 5
-- 第五页
SELECT * FROM EMP 
ORDER BY EMPNO DESC
LIMIT 20, 5

增强四 group by

案例

-- 显示每种岗位的雇员总数、平均工资
SELECT job,COUNT(*), AVG(SAL)
FROM EMP
GROUP BY job;-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*),COUNT(COMM)
FROM EMP;
-- 没有获得补助的雇员数
SELECT COUNT(IF(COMM IS NULL,1,NULL))
FROM EMP-- 显示管理者的总人数
SELECT  COUNT(DISTINCT MGR)
FROM EMP-- 显示雇员工资的最大差额
SELECT MAX(SAL) - MIN(SAL)
FROM EMP

增强五 数据分组总结

案例

group by 细节

HAVING 子句通常用于筛选聚合函数（如 AVG、SUM、COUNT 等）的结果。它不应直接用于筛选单个行的数据

并且select只能是group by中的元素或者被聚合函数包裹的元素

SELECT deptno, AVG(SAL) AS avg_total
FROM emp
-- 这里还可以添加where筛选单个子句
GROUP BY DEPTNO
HAVING avg_total > 1000
ORDER BY avg_total DESC
LIMIT 0,2;

3.多表查询

1.笛卡尔积

细节

1. 在两个表默认连接条件下会出现笛卡尔积
2. 笛卡尔积就是将第一张表的一条记录与第二张表的所有记录依次连接
3. 笛卡尔积生成的记录条数 = 第一张表的记录条数 * 第二张表的记录条数
4. 多表查询的条件不能少于表的个数 - 1否则就会出现笛卡尔积
5. 连接的条件一般是一个表的外码和另一个表的主码连接，这样生成的记录条数就是第一个表的记录条数

案例

-- 查出雇员名，雇员工资，以及所在部门的名字,还有部门编号
-- 注意部门编号是有两个表的虽然一样，但是也要指明哪个表
SELECT ename, sal, dname,emp.deptno
FROM EMP, DEPT
WHERE EMP.deptno = DEPT.deptno

2.多表查询

案例

-- 显示部门号为10的部门名，员工名和工资
SELECT dname, ename, sal
FROM EMP, DEPT
WHERE EMP.deptno =DEPT.deptno AND DEPt.deptno = 10-- 显示各个员工的姓名，工资以及工资的级别
SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal

3.自连接

案例

-- 显示员工名字以及他上级的名字,必须使用别名来进行自连接
SELECT worker.ename, boss.ename
FROM emp worker, emp boss 
WHERE worker.mgr = boss.empno

4.多行子查询

案例一

-- 显示与SMITH同一部门的所有员工
-- 先得到SMITH的部门编号,然后将其当做子查询
SELECT *
FROM emp
WHERE DEPTNO IN (SELECT DEPTNOFROM empWHERE ename = 'SMITH'
)

案例二

-- 查询和部门10的工作相同的雇员的名字，岗位，工资，部门号，但是不含10自己的SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (
-- 查询部门10的工作SELECT jobFROM empWHERE deptno = 10
) AND ename NOT IN (
-- 查询部门10的名字SELECT enameFROM empWHERE deptno = 10
)
-- 或者
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (
-- 查询部门10的工作SELECT jobFROM empWHERE deptno = 10
) AND deptno <> 10
-- <>是不等号

4.子查询临时表（非常管用）

SELECT goods_id, temp.cat_id, goods_name, shop_price
FROM (-- 查出id分组中最高的价钱，并将其当做一个临时表SELECT cat_id, MAX(shop_price) as maxFROM ecs_goodsGROUP BY cat_id
) temp, ecs_goods
WHERE temp.cat_id = ecs_goods.cat_id AND temp.max = ecs_goods.shop_price
-- 连接条件是临时表中的id对应原表中的id，临时表中的最大值，对应原表中的最大值

5.all和any操作符

all操作符案例

-- 显示工资比部门30的所有员工的工资都高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > all(-- 子查询查出部门30所有员工工资SELECT sal FROM empWHERE deptno = 30
)

any操作符案例

-- 显示工资比部门30的其中一名员工的工资都高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ANY(-- 子查询查出部门30所有员工工资SELECT sal FROM empWHERE deptno = 30
)

6.多列子查询

案例一

-- 查询与smith的部门和岗位完全相同的所有雇员并且不包含smith本人
SELECT *
FROM emp
-- 把要查询的元素也用括号括起来
WHERE (deptno, job) = (-- 子查询：查询smith的部门和岗位SELECT deptno, jobFROM empWHERE ename = 'SMITH'
) AND ename <> 'SMITH'

案例二

-- 主查询：查询和宋江的数学，英语，语文，成绩完全相同的学生
SELECT *
FROM student
WHERE (math, english, chinese) = (-- 子查询：宋江的数学，英语，语文，成绩SELECT math, english, chineseFROM studentWHERE `name` = '宋江'
)

7.子查询练习题

练习一

-- 子查询当做临时表使用，查询每个部门工资高于本部门平均工资的人
SELECT *
FROM (-- 子查询：按照部门分组，求出本部门的平均工资SELECT deptno, AVG(sal) as avgsalFROM empGROUP BY deptno
) temp, emp
WHERE temp.deptno = emp.deptno AND emp.sal > temp.avgsal 

练习二

-- 查找每个部门工资最高的人的详细资料
SELECT *
FROM (-- 子查询：按照部门分组，查找到每组最高的工资SELECT deptno, MAX(sal) as maxFROM empGROUP BY deptno
) temp, emp
WHERE temp.deptno = emp.deptno AND temp.max = emp.sal

练习三

-- 注意：表名.*可以显示这个表的所有列
SELECT temp.*, dname, loc
FROM (-- 子查询：根据部门进行分组，计算人员数量SELECT deptno, COUNT(*) as numFROM empGROUP BY deptno
)temp, dept
WHERE temp.deptno = dept.deptno

8.表复制和去重

1.表的复制

CREATE TABLE my_tab01(id INT,name VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT
);
SELECT * FROM my_tab01-- 将emp表的数据复制到my_tab01中去
INSERT INTO my_tab01(id, name, sal, job, deptno)SELECT empno, ename, sal, job, deptno FROM emp; 

2.自我复制

-- 自我复制
INSERT INTO my_tab01SELECT * FROM my_tab01

3.复制表结构

-- 复制表结构来创建一个表
CREATE TABLE temp LIKE salgrade

4.去重

-- my_tab01是一个具有重复数据的表
-- 1.创建一个临时表temp其结构与my_tab01一样
CREATE TABLE temp like my_tab01
-- 2.将my_tab01的数据使用distinct处理并复制到表temp中
INSERT INTO tempSELECT DISTINCT * FROM my_tab01
-- 3.把my_tab01的数据删除
DELETE FROM my_tab01
-- 4.把temp表的数据复制到my_tab01中去
INSERT INTO my_tab01SELECT * FROM temp
-- 删除临时表
DROP TABLE temp

9.合并查询

union all

union

10.外连接

左外连接

-- 创建两个表，并实现外连接
CREATE TABLE STU (ID INT,NAME VARCHAR(32)
)CREATE TABLE EXAM(ID INT,GRADE INT
);

-- 左外连接
SELECT *
FROM EXAM LEFT JOIN STU
ON EXAM.ID = STU.ID

右外连接

-- 右外连接
SELECT * 
FROM EXAM RIGHT JOIN STU
ON EXAM.ID = STU.ID

练习

-- 列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门名
-- 左外连接
SELECT DNAME, ENAME,JOB
FROM DEPT LEFT JOIN EMP
ON DEPT.deptno = EMP.deptno
-- 右外连接
SELECT DNAME, ENAME,JOB
FROM EMP RIGHT JOIN DEPT
ON EMP.deptno = DEPT.deptno

4.查询与连接的WHERE区别