热搜词

day05多表查询01

技术分享 2年前 (2022-10-06) 0 999+
关注

多表查询

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

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

emp：

day05多表查询01

dept：

day05多表查询01

salgrade：

day05多表查询01

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

day05多表查询01 

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

day05多表查询01 day05多表查询01

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

day05多表查询01 day05多表查询01 

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

day05多表查询01 day05多表查询01

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

day05多表查询01

2.多表查询

2.1笛卡尔积

  • 说明

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

例子-笛卡尔集（积）

SELECT * FROM emp,dept;

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

day05多表查询01

emp表：共有13行记录

day05多表查询01

dept表：共有4行记录

day05多表查询01

分析如下：

当两张表查询时，规则为

  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列关联的

day05多表查询01 

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

day05多表查询01

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号部门自己的雇员

day05多表查询01 day05多表查询01

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 		)

day05多表查询01

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 		)

day05多表查询01

3.4子查询临时表

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

例子

ecshop表：

day05多表查询01

day05多表查询01

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

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

day05多表查询01 day05多表查询01

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

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

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

day05多表查询01 day05多表查询01

发表评论

相关文章

关于使用plsql操作oracle的一点小技巧和几个常用的查询语句
关于使用plsql操作oracle的一点小技巧和几个常用的查询语句
（系列六）.net8 全局异常捕获机制
（系列六）.net8 全局异常捕获机制
OpenAI官方开源多智能体框架「Swarm」，并不是我想要的多智能体框架
OpenAI官方开源多智能体框架「Swarm」，并不是我想要的多智能体框架
轻松构建游戏登录能力，打造玩家流畅体验
轻松构建游戏登录能力，打造玩家流畅体验
SaaS架构：开放平台架构设计
SaaS架构：开放平台架构设计
Springboot — 使用国内的 AI 大模型 对话
Springboot — 使用国内的 AI 大模型 对话
当前内容话题
上一页： PyCharm安装PyQt5及其工具（Qt Designer、PyUIC、PyRcc）详细教程
  • 0
  • 微