百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

day05多表查询01

2022-10-10 分类：野生技术 阅读(168) 评论(0)

多表查询

前面讲过的基本查询都是对一张表进行查询，但在实际的开发中远远不够。

下面使用表emp，dept，salgrade进行多表查询

emp：

image-20221004191354055

dept：

image-20221004191437441

salgrade：

image-20221004191524292

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

-- - 如何使用like操作符
--   %表示0到多个字符   _表示单个字符
--   如何显示首字符为S的员工姓名和工资
 SELECT ename,sal FROM emp 
	WHERE ename LIKE \'S%\';

--   如何显示第三个字符为大写O的所有员工的姓名和工资
 SELECT ename,sal FROM emp 
	WHERE ename LIKE \'__O%\';

image-20221005162113291 image-20221005162139750

-- - 如何显示没有上级的雇员的情况
 SELECT * FROM emp 
	WHERE mgr IS NULL;

-- - 查询表结构
DESC emp;

image-20221005162204237
image-20221005162228037

-- 使用order by子句
-- 如何按照工资的从低到高的顺序，显示雇员的信息
 SELECT * FROM emp 
	ORDER BY sal ASC;

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

image-20221005162250721
image-20221005162312631

1.2分页查询

  1. 按照雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页

  2. 基本语法

    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
  1. 显示每种岗位的雇员总数，平均工资
  2. 显示雇员总数以及获得补助的雇员数
  3. 显示管理者的总人数
  4. 显示雇员工资的最大差额
# 使用分组函数和分组子句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

2.多表查询

2.1笛卡尔积

  • 说明

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

例子-笛卡尔集（积）

SELECT * FROM emp,dept;

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

image-20221005175003770

emp表：共有13行记录

image-20221005175310130

dept表：共有4行记录

image-20221005175447873

分析如下：

当两张表查询时，规则为

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

练习

  1. 显示雇员名，雇员工资以及所在部门的名字

  2. 如何显示部门号为10的部门名，员工名和工资

  3. 显示各个员工的姓名，工资及其工资的级别

-- 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

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

image-20221005185626994

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
image-20221005192706983

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

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

3.4子查询临时表

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

例子

ecshop表：

image-20221005200035647

image-20221005200101349

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

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

image-20221005200655807
image-20221005200509235

  1. 选择临时表和原本的表格，过滤条件为

    临时表的cat_id = 原商品表的cat_id

    && 临时表的max_price=原商品表的price

image-20221005201843596
image-20221005201928876

3.5多列子查询

多列子查询是指查询返回多个列数据的子查询语句

语法：

(字段1，字段2...) = (select 字段1 ，字段2 from ...)

例子

请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员（并且不包含ALLEN本人）

-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员（并且不包含Smith本人）
-- 1.得到ALLEN的部门和岗位
	SELECT deptno , job 
		FROM emp
		WHERE ename = \'ALLEN\';
		
-- 2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT * FROM emp
	WHERE (deptno,job)=(
		SELECT deptno , job 
		FROM emp
		WHERE ename = \'ALLEN\')
		AND ename != \'ALLEN\';

image-20221006161223173

练习

-- 请得到和宋江数学、英语、语文成绩完全相同的学生
SELECT * FROM student
	WHERE (math,english,chinese)=(
		SELECT math,english,chinese
		FROM student
		WHERE `name`= \'宋江\'	
	);

3.6子查询练习

  1. 在from子句中使用子查询

请思考：查找每个部门工资高于本部门平均工资的人的资料

-- 查找每个部门工资高于本部门平均工资的人的资料

 -- 1.先得到对应的部门号和部门对应的平均工资
SELECT deptno,AVG(sal) FROM emp
	GROUP BY deptno;
	
-- 2.把上面的结果当做子查询，和emp进行多表查询

SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,
		(SELECT deptno,
		AVG(sal) AS avg_sal FROM emp
		GROUP BY deptno) temp
	WHERE emp.deptno=temp.deptno AND emp.sal > avg_sal

image-20221006164236567

  1. 查找每个部门工资最高的人的详细信息

    -- 查找每个部门工资最高的人的详细信息
 -- 1. 先得到每个部门的最高工资
 SELECT MAX(sal) FROM emp
	GROUP BY deptno;
	
-- 2.将上面的结果作为子查询，和emp表进行多表查询
SELECT ename,sal,temp.max_sal,emp.deptno
	FROM emp,(
		SELECT MAX(sal) AS max_sal FROM emp
		GROUP BY deptno
	) temp
	WHERE emp.sal = temp.max_sal

    image-20221006165300820

  2. 查询每个部门的信息（包括部门名、编号、地址）和人员数量

-- 查询每个部门的信息
-- 1.部门名、编号、地址来自dept表
-- 2.各个部门的人员数量  ---》构建一个临时表	
SELECT COUNT(*),deptno
		FROM emp
		GROUP BY deptno
		
		
SELECT dname,dept.deptno,loc,person_num 
	FROM dept,(
		SELECT COUNT(*) AS person_num,deptno
		FROM emp
		GROUP BY deptno
		) temp
	WHERE dept.deptno = temp.deptno
	
-- 还有一种写法 表.* 表示将该表所有列都显示出来,可以简化SQL语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT dname,loc,temp.*
	FROM dept,(
		SELECT COUNT(*) AS person_num,deptno
		FROM emp
		GROUP BY deptno
		) temp
	WHERE dept.deptno = temp.deptno

image-20221006172035543

来源：https://www.cnblogs.com/liyuelian/p/16756290.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » day05多表查询01
标签：

相关推荐

  • 暂无文章

热门文章