多表查询
前面讲过的基本查询都是对一张表进行查询,但在实际的开发中远远不够。
下面使用表emp,dept,salgrade进行多表查询
emp:
![image-20221004191354055](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221004191354055.png)
dept:
![image-20221004191437441](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221004191437441.png)
salgrade:
![image-20221004191524292](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221004191524292.png)
1.前置-mysql表查询-加强
1.1查询增强
-
使用where子句
如何查找1992.1.1后入职的员工
在mysql中,日期类型可以直接比较,需要注意格式
-
如何使用like操作符
%表示0到多个任意字符 _表示单个任意字符
如何显示首字符为S的员工姓名和工资
如何显示第三个字符为大写O的所有员工的姓名和工资
-
如何显示没有上级的雇员的情况
-
查询表结构
-
使用order by子句
如何按照工资的从低到高的顺序,显示雇员的信息
按照部门号升序而雇员的工资降序排列,显示雇员的信息
练习
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- - 使用where子句
-- 在mysql中,日期类型可以直接比较
-- 如何查找1992.1.1后入职的员工
SELECT * FROM emp
WHERE hiredate > '1992-01-01';
![image-20221005162027153](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005162027153.png)
-- - 如何使用like操作符
-- %表示0到多个字符 _表示单个字符
-- 如何显示首字符为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;
![image-20221005162204237](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005162204237.png)
![image-20221005162228037](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005162228037.png)
-- 使用order by子句
-- 如何按照工资的从低到高的顺序,显示雇员的信息
SELECT * FROM emp
ORDER BY sal ASC;
-- 按照部门号升序而雇员的工资降序排列,显示雇员的信息
SELECT * FROM emp
ORDER BY deptno ASC,sal DESC;
![image-20221005162250721](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005162250721.png)
![image-20221005162312631](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005162312631.png)
1.2分页查询
-
按照雇员的id号升序取出,每页显示3条记录,请分别显示第一页,第二页,第三页
-
基本语法
select ... limit start,rows
表示从start+1行开始取,取出rows行,start从0开始计算
练习
-- 按照雇员的id号升序取出,每页显示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 每页显示记录数*(第几页-1),每页显示记录数
1.3分组函数和分组子句加强
- 使用分组函数和分组子句group by
- 显示每种岗位的雇员总数,平均工资
- 显示雇员总数以及获得补助的雇员数
- 显示管理者的总人数
- 显示雇员工资的最大差额
# 使用分组函数和分组子句groupby
-- 1. 显示每种岗位的雇员总数,平均工资
SELECT COUNT(*),AVG(sal),job FROM emp
GROUP BY job;
-- 2. 显示雇员总数以及获得补助的雇员数
-- 思路:COUNT(列) 如果该列的值为空,是不会统计进去的
SELECT COUNT(*),COUNT(comm)
FROM emp
-- 扩展:统计没有获得补助的雇员数
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
FROM emp
-- 或者
SELECT COUNT(*),COUNT(*)-COUNT(comm)
FROM emp
-- 3. 显示管理者的总人数
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- 4. 显示雇员工资的最大差额
SELECT MAX(sal)-MIN(sal)
FROM emp;
- 数据分组的总结
如果select语句同时包含有group by,having,limit,order by子句,
那么他们的顺序应该为 group by,having,order by,limit
应用案例
请统计每个部门的平均工资,并且是大于1000的,并且按照平均工资从高到低排序,取出前两行记录
-- 请统计每个部门group by 的平均avg工资,
-- 并且是大于1000的,having
-- 并且按照平均工资从高到低排序,order by
-- 取出前两行记录 limit
SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
![image-20221005172508496](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005172508496.png)
2.多表查询
2.1笛卡尔积
- 说明
多表查询是指基于两个或两个以上的表查询,在实际的应用中,查询单个表可能不能满足需求,这时候就要用到多表查询
例子-笛卡尔集(积)
SELECT * FROM emp,dept;
显示的结果如下:共有52行记录
emp表:共有13行记录
![image-20221005175310130](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005175310130.png)
dept表:共有4行记录
![image-20221005175447873](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005175447873.png)
分析如下:
当两张表查询时,规则为
- 从第一张表中,取出一行 和第二张表的每一行进行组合,返回结果[含有两张表的所有列]
- 一共返回的记录数=第一张表的行数*第二张表的行数
- 这样多表查询默认处理返回的结果,称为笛卡尔集(积)
- 解决这个多表的关键就是要写出正确的过滤条件 where
- 多表查询的条件不能少于 表的个数 -1 ,否则会出现笛卡尔积
练习
-
显示雇员名,雇员工资以及所在部门的名字
-
如何显示部门号为10的部门名,员工名和工资
-
显示各个员工的姓名,工资及其工资的级别
-- 1. 显示雇员名,雇员工资以及所在部门的名字
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 2. 如何显示部门号为10的部门名,员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno AND emp.deptno =10;
-- 3. 显示各个员工的姓名,工资及其工资的级别
SELECT ename,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;
-- 4.显示雇员名,雇员工资以及所在部门的名字,并按照部门名排序
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dept.dname DESC;
2.2自连接
- 自连接
自连接是指在同一张表的连接查询
- 自连接的特点
- 将同一张表看做两张表使用
- 需要给表取别名 ,格式为 表名 表别名
思考:显示公司员工和他上级的名字
分析:可以发现员工的名字和上级的名字都是在emp表中
员工和上级是通过emp表的mgr列关联的
![image-20221005184007973](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005184007973.png)
-- 显示公司员工和他上级的名字
SELECT worker.ename AS '职员名', boss.ename AS '上级名'-- 列的别名
FROM emp worker,emp boss -- 为表起别名
WHERE worker.mgr = boss.empno; -- 过滤条件
![image-20221005185626994](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005185626994.png)
3.子查询
- 什么是子查询
子查询是指嵌入在其他SQL语句的select语句,也叫嵌套查询
- 单行子查询
单行子查询是指只返回一行数据的子查询语句
请思考:如何显示与Smith同一部门的所有员工?
- 多行子查询
多行子查询指返回多行数据的子查询 使用关键字 in
3.1多行子查询
练习1
-- 请思考:如何显示与Smith同一部门的所有员工?
/*
1.先查询到Smith的部门编号
2.把上面的select语句当做是一个子查询来使用
*/
SELECT deptno
FROM emp
WHERE ename = 'SMITH';-- 先查询到Smith的部门编号
-- 单行子查询
SELECT *
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE ename = 'SMITH'
);
-- 多行子查询
-- 如何查询和部门10的工作相同 的雇员的名字、岗位、工资、部门号,但是不含10号部门自己的雇员
/*
1.查询到10号部门有哪些工作岗位
2.把上面的查询结果当做是一个子查询来使用
*/
SELECT DISTINCT job
FROM emp
WHERE deptno=10;
SELECT ename,job,sal,deptno
FROM emp
WHERE job IN( -- 返回了一个集合,用in
SELECT DISTINCT job
FROM emp
WHERE deptno=10)
AND deptno !=10; -- 不含10号部门自己的雇员
![image-20221005192645084](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005192645084.png)
![image-20221005192706983](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005192706983.png)
3.2all操作符
- 在多行子查询中使用all操作符
请思考:显示工资比部门30所有员工工资高的员工的姓名、工资和部门号
-- 显示工资比部门30所有员工工资高的 员工的姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
-- 或者
SELECT ename,sal,deptno
FROM emp
WHERE sal>(
SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)
![image-20221005194826425](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005194826425.png)
3.3any操作符
- 在多行子查询中使用any操作符
请思考:如何显示工资比部门30其中一个员工工资高的 员工的姓名、工资和部门号
-- 请思考:如何显示工资比部门30其中一个员工工资高的 员工的姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ANY(
SELECT sal
FROM emp
WHERE deptno = 30
)
-- 或者
SELECT ename,sal,deptno
FROM emp
WHERE sal>(
SELECT MIN(sal)
FROM emp
WHERE deptno = 30
)
![image-20221005195212687](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005195212687.png)
3.4子查询临时表
- 子查询当做一张表来使用
例子
ecshop表:
![image-20221005200101349](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005200101349.png)
要求:查询ecshop中各个类别中价格最高的商品
- 先得到各个类别中,价格最高的商品 --当做一个临时表
![image-20221005200655807](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005200655807.png)
![image-20221005200509235](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005200509235.png)
-
选择临时表和原本的表格,过滤条件为
临时表的cat_id = 原商品表的cat_id
&& 临时表的max_price=原商品表的price
![image-20221005201843596](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005201843596.png)
![image-20221005201928876](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221005201928876.png)