day05多表查询01

多表查询

前面讲过的基本查询都是对一张表进行查询，但在实际的开发中远远不够。
下面使用表emp，dept，salgrade进行多表查询
emp：
dept：
salgrade：
1.前置-mysql表查询-加强

1.1查询增强

• 使用where子句
  如何查找1992.1.1后入职的员工
  

在mysql中，日期类型可以直接比较，需要注意格式

• 如何使用like操作符
  %表示0到多个任意字符      _表示单个任意字符
  如何显示首字符为S的员工姓名和工资
  如何显示第三个字符为大写O的所有员工的姓名和工资
  
• 如何显示没有上级的雇员的情况
  
• 查询表结构
  
• 使用order by子句
  如何按照工资的从低到高的顺序，显示雇员的信息
  按照部门号升序而雇员的工资降序排列，显示雇员的信息
  

练习

1. SELECT * FROM emp;
2. SELECT * FROM dept;
3. SELECT * FROM salgrade;
5. -- - 使用where子句
6. -- 在mysql中，日期类型可以直接比较
7. --  如何查找1992.1.1后入职的员工
8. SELECT * FROM emp
9.         WHERE hiredate > '1992-01-01';

复制代码

1. -- - 如何使用like操作符
2. --   %表示0到多个字符   _表示单个字符
3. --   如何显示首字符为S的员工姓名和工资
4. SELECT ename,sal FROM emp
5.         WHERE ename LIKE 'S%';
7. --   如何显示第三个字符为大写O的所有员工的姓名和工资
8. SELECT ename,sal FROM emp
9.         WHERE ename LIKE '__O%';

复制代码

1. -- - 如何显示没有上级的雇员的情况
2. SELECT * FROM emp
3.         WHERE mgr IS NULL;
5. -- - 查询表结构
6. DESC emp;

复制代码

1. -- 使用order by子句
2. -- 如何按照工资的从低到高的顺序，显示雇员的信息
3. SELECT * FROM emp
4.         ORDER BY sal ASC;
6. -- 按照部门号升序而雇员的工资降序排列，显示雇员的信息
7. SELECT * FROM emp
8.         ORDER BY deptno ASC,sal DESC;

复制代码

1.2分页查询

• 按照雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页
  
• 基本语法
  1. select ... limit start,rows

  复制代码
  表示从start+1行开始取，取出rows行，start从0开始计算
  

练习

1. -- 按照雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页
2. SELECT * FROM emp
3.         ORDER BY empno
4.         LIMIT 0,3
5.        
6. SELECT * FROM emp
7.         ORDER BY empno
8.         LIMIT 3,3
9.        
10. SELECT * FROM emp
11.         ORDER BY empno
12.         LIMIT 6,3

复制代码

公式：

1. SELECT * FROM emp
2.         ORDER BY empno
3.         LIMIT 每页显示记录数*（第几页-1）,每页显示记录数

复制代码

1.3分组函数和分组子句加强

• 使用分组函数和分组子句group by
  

• 显示每种岗位的雇员总数，平均工资
  
• 显示雇员总数以及获得补助的雇员数
  
• 显示管理者的总人数
  
• 显示雇员工资的最大差额
  

1. # 使用分组函数和分组子句groupby
3. -- 1. 显示每种岗位的雇员总数，平均工资
4. SELECT COUNT(*),AVG(sal),job FROM emp
5.         GROUP BY job;
7. -- 2. 显示雇员总数以及获得补助的雇员数
8. -- 思路：COUNT(列)  如果该列的值为空，是不会统计进去的
9. SELECT COUNT(*),COUNT(comm)
10.         FROM emp
11.        
12. -- 扩展：统计没有获得补助的雇员数
13. SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
14.         FROM emp        
15. -- 或者
16. SELECT COUNT(*),COUNT(*)-COUNT(comm)
17.         FROM emp
18.        
19. -- 3. 显示管理者的总人数
20. SELECT COUNT(DISTINCT mgr)
21.         FROM emp;
23. -- 4. 显示雇员工资的最大差额
24. SELECT MAX(sal)-MIN(sal)
25.         FROM emp;

复制代码

• 数据分组的总结
  

如果select语句同时包含有group by，having，limit，order by子句，
那么他们的顺序应该为 group by，having，order by，limit

应用案例

请统计每个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录

1. -- 请统计每个部门group by 的平均avg工资，
2. -- 并且是大于1000的，having
3. -- 并且按照平均工资从高到低排序，order by
4. -- 取出前两行记录 limit
6. SELECT deptno,AVG(sal) AS avg_sal
7.         FROM emp
8.         GROUP BY deptno
9.         HAVING avg_sal > 1000
10.         ORDER BY avg_sal DESC
11.         LIMIT 0,2

复制代码

2.多表查询

2.1笛卡尔积

• 说明
  

多表查询是指基于两个或两个以上的表查询，在实际的应用中，查询单个表可能不能满足需求，这时候就要用到多表查询

例子-笛卡尔集（积）

1. SELECT * FROM emp,dept;

复制代码

显示的结果如下：共有52行记录

emp表：共有13行记录
dept表：共有4行记录
分析如下：
当两张表查询时，规则为

• 从第一张表中，取出一行 和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
  
• 一共返回的记录数=第一张表的行数*第二张表的行数
  
• 这样多表查询默认处理返回的结果，称为笛卡尔集（积）
  
• 解决这个多表的关键就是要写出正确的过滤条件 where
  
• 多表查询的条件不能少于 表的个数 -1 ，否则会出现笛卡尔积
  

练习

• 显示雇员名，雇员工资以及所在部门的名字
  
• 如何显示部门号为10的部门名，员工名和工资
  
• 显示各个员工的姓名，工资及其工资的级别
  

1. -- 1. 显示雇员名，雇员工资以及所在部门的名字
2. SELECT ename,sal,dname,emp.deptno
3. FROM emp,dept
4. WHERE emp.deptno = dept.deptno;
6. -- 2. 如何显示部门号为10的部门名，员工名和工资
7. SELECT ename,sal,dname,emp.deptno
8. FROM emp,dept
9. WHERE emp.deptno = dept.deptno AND emp.deptno =10;
11. -- 3. 显示各个员工的姓名，工资及其工资的级别
12. SELECT ename,sal,grade
13. FROM emp,salgrade
14. WHERE sal BETWEEN losal AND hisal;
16. -- 4.显示雇员名，雇员工资以及所在部门的名字，并按照部门名排序
17. SELECT ename,sal,dname
18. FROM emp,dept
19. WHERE emp.deptno = dept.deptno
20. ORDER BY dept.dname DESC;

复制代码

2.2自连接

• 自连接
  

自连接是指在同一张表的连接查询

• 自连接的特点
  
  
  
  • 将同一张表看做两张表使用
    
  • 需要给表取别名 ，格式为  表名  表别名
    
  
  

思考：显示公司员工和他上级的名字

分析：可以发现员工的名字和上级的名字都是在emp表中
员工和上级是通过emp表的mgr列关联的

1. -- 显示公司员工和他上级的名字
2. SELECT worker.ename AS '职员名', boss.ename AS '上级名'-- 列的别名
3.         FROM emp worker,emp boss -- 为表起别名
4.         WHERE worker.mgr = boss.empno; -- 过滤条件

复制代码

3.子查询

• 什么是子查询
  

子查询是指嵌入在其他SQL语句的select语句，也叫嵌套查询

• 单行子查询
  

单行子查询是指只返回一行数据的子查询语句

请思考：如何显示与Smith同一部门的所有员工？

• 多行子查询
  

多行子查询指返回多行数据的子查询  使用关键字 in
3.1多行子查询

练习1

1. -- 请思考：如何显示与Smith同一部门的所有员工？
2. /*
3.         1.先查询到Smith的部门编号
4.         2.把上面的select语句当做是一个子查询来使用
5. */
7. SELECT deptno
8. FROM emp
9. WHERE ename = 'SMITH';-- 先查询到Smith的部门编号
11. -- 单行子查询
12. SELECT *
13.         FROM emp
14.         WHERE deptno = (SELECT deptno
15.                         FROM emp
16.                         WHERE ename = 'SMITH'
17.                         );
19. -- 多行子查询
20. -- 如何查询和部门10的工作相同 的雇员的名字、岗位、工资、部门号，但是不含10号部门自己的雇员
21. /*
22.         1.查询到10号部门有哪些工作岗位
23.         2.把上面的查询结果当做是一个子查询来使用
24. */
26. SELECT DISTINCT job
27.         FROM emp
28.         WHERE deptno=10;
30. SELECT ename,job,sal,deptno
31.         FROM emp
32.         WHERE job IN( -- 返回了一个集合，用in
33.                 SELECT DISTINCT job
34.                 FROM emp
35.                 WHERE deptno=10)
36.                 AND deptno !=10; -- 不含10号部门自己的雇员

复制代码

3.2all操作符

• 在多行子查询中使用all操作符
  

请思考：显示工资比部门30所有员工工资高的员工的姓名、工资和部门号

1. -- 显示工资比部门30所有员工工资高的 员工的姓名、工资和部门号
2. SELECT ename,sal,deptno
3.         FROM emp
4.         WHERE sal>ALL(
5.                 SELECT sal
6.                 FROM emp
7.                 WHERE deptno = 30
8.                 )
9. -- 或者
10. SELECT ename,sal,deptno
11.         FROM emp
12.         WHERE sal>(
13.                 SELECT MAX(sal)
14.                 FROM emp
15.                 WHERE deptno = 30
16.                 )

复制代码

3.3any操作符

• 在多行子查询中使用any操作符
  

请思考：如何显示工资比部门30其中一个员工工资高的 员工的姓名、工资和部门号

1. -- 请思考：如何显示工资比部门30其中一个员工工资高的 员工的姓名、工资和部门号
2. SELECT ename,sal,deptno
3.         FROM emp
4.         WHERE sal>ANY(
5.                 SELECT sal
6.                 FROM emp
7.                 WHERE deptno = 30
8.                 )
9.                
10. -- 或者
11. SELECT ename,sal,deptno
12.         FROM emp
13.         WHERE sal>(
14.                 SELECT MIN(sal)
15.                 FROM emp
16.                 WHERE deptno = 30
17.                 )

复制代码

3.4子查询临时表

• 子查询当做一张表来使用
  

例子

ecshop表：

要求：查询ecshop中各个类别中价格最高的商品

• 先得到各个类别中，价格最高的商品 --当做一个临时表
  

• 选择临时表和原本的表格，过滤条件为
  临时表的cat_id = 原商品表的cat_id
  && 临时表的max_price=原商品表的price
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！