MySQL数据库（5）

_雨_

已于 2023-05-03 20:46:08 修改

阅读量768

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

于 2023-05-03 19:48:15 首次发布

本文链接：https://blog.csdn.net/weixin_53616401/article/details/130476246

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

多表查询

多表查询说明

多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

在我们进行多表查询的时候，难免的会发生笛卡尔集的情况发生，那么什么是笛卡尔集呢，笛卡尔集，就是我们在进行多表查询的时候，会产生许多重复的数据，如果两张表中的，有两个相同的字段，那么会导致许多重复，没用的数据，因此我们需要避免笛卡尔集的发生

多表查询的本质

在默认情况下:当两个表查询时，规则

1.从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[合有两张表的所有列].

2.一共返回的记录数第一张表行数*第二张表的行数

3.这样多表查询默认处理返回的结果，称为笛卡尔集

4.解决这个多表的关键就是要与出正确的过滤条件 where,需要程序员进行分析

代码演示：

如果想要避免笛卡尔集的情况发生，在多表查询的最后我们需要使用where条件判断，这样查询出来的数据，才不会有笛卡尔集的情况发生

-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
	分析
	1. 雇员名,雇员工资 来自 emp表
	2. 部门的名字 来自 dept表
	3. 需求对 emp 和 dept查询  ename,sal,dname,deptno
	4. 当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
	FROM emp, dept 
	WHERE emp.deptno = dept.deptno
	
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资 
SELECT ename,sal,dname,emp.deptno
	FROM emp, dept 
	WHERE emp.deptno = dept.deptno AND emp.deptno = 10

-- ?显示各个员工的姓名，工资，及其工资的级别

-- 思路 姓名，工资 来自 emp 13
--      工资级别 salgrade 5
-- 写sql , 先写一个简单，然后加入过滤条件...
SELECT ename, sal, grade 
	FROM emp , salgrade
	WHERE sal BETWEEN losal AND hisal; 


#练习: 显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排].
SELECT ename,sal,dname
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY dname DESC
	
	SELECT * FROM emp
	SELECT * FROM dept

自连接

基本概念

自连接是指在同一张表的连接查询[将同一张表看做两张表

使用通俗的说法来说，就是，也是多表查询，只不过把一张表看成两张不同的表，只不过是为同一张表起了两个不同的名字而已

代码演示：

注意在代码中的使用的是同一张表，只不过分别为表起了别的名字，分别是worker 和 boss

-- 多表查询的 自连接

-- 思考题: 显示公司员工名字和他的上级的名字

--  员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
-- 这里小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
--               2. 需要给表取别名 表名  表别名 
--		 3. 列名不明确，可以指定列的别名 列名 as 列的别名		
SELECT worker.ename AS '职员名' ,  boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;
	#根据emp表我们可以知道，员工的mgr表示的是该员工的上级因此只需要员工的mgr等于上级的empno即可
SELECT * FROM emp;

子查询

基本概念

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

代码演示：

-- 子查询的演示
-- 请思考：如何显示与SMITH同一部门的所有员工?
/*
	1. 先查询到 SMITH的部门号得到
	2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno 
	FROM emp 
	WHERE ename = '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 (
		SELECT DISTINCT job 
		FROM emp 
		WHERE deptno = 10
	) AND deptno <> 10

子查询练习

代码演示：

-- 查询ecshop中各个类别中，价格最高的商品

-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询

SELECT cat_id , MAX(shop_price) 
	FROM ecs_goods
	GROUP BY cat_id
	
	
-- 这个最后答案	
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price 
	FROM (
		SELECT cat_id , MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	) temp , ecs_goods
	WHERE  temp.cat_id = ecs_goods.cat_id 
	AND temp.max_price = ecs_goods.shop_price

all和any

代码演示：

-- all 和 any的使用

-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
#先查询部门号为30的所有员工的工资  在使用all操作符 all表示比我们查询出来中最大的工资还要大 工资的人
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
		) 

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

# any表示比最小的大就可以了
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
		)

多列子查询

代码演示：

-- 多列子查询

-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。)

-- 分析: 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'



-- 请查询 和宋江数学，英语，语文   
-- 成绩 完全相同的学生
#先查询宋江的数学，英语，语文 成绩
SELECT math, english, chinese FROM student WHERE `name` = '宋江'

SELECT * 
	FROM student
	WHERE (math, english, chinese) = (SELECT math, english, chinese FROM student WHERE `name` = '宋江')

SELECT * FROM student;

表复制

表的复制会在一些的特定去情况下去使用，比如我们为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

代码演示：

-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

#创建一张表
CREATE TABLE my_tab01 
	( id INT,
	  `name` VARCHAR(32),
	  sal DOUBLE,
	  job VARCHAR(32),
	  deptno INT);

#查看表的结构	  
DESC my_tab01
#查询表
SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
#就是把emp表中的这些字段（empno, ename, sal, job, deptno） 插入到my_tab01表中
INSERT INTO my_tab01 
	(id, `name`, sal, job,deptno)
	SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;

-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02, 
-- 2. 让 my_tab02 有重复的记录

CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列)，复制到my_tab02

DESC my_tab02;

INSERT INTO my_tab02
	SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3. 考虑去重 my_tab02的记录
/*
	思路 
	(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
	(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
	(3) 清除掉 my_tab02 记录
	(4) 把 my_tmp 表的记录复制到 my_tab02
	(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样

CREATE TABLE my_tmp LIKE my_tab02
-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tmp 
	SELECT DISTINCT * FROM my_tab02;

-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02
	SELECT * FROM my_tmp;
-- (5) drop 掉 临时表my_tmp
DROP TABLE my_tmp;


+
SELECT * FROM my_tab02;