mysql多表查询

查询加强

查询到的表的结构

-- 查询加强

-- 使用where语句

-- 1.如果查找1991.1.1后入职的员工
-- 注意：mysql,日期类型可以直接比较，需要注意和表中的格式一致
SELECT * FROM  emp
WHERE hiredate>'1991.1.1';

-- 2.使用like操作符（模糊）
-- %:表示0到多个任意字符  _：表示单个任意字符
-- 2.如何显示首字母为S的员工的姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%';

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

-- 4.如果显示没有上级的员工的情况(mgr记录了雇员上级的编号)

SELECT *FROM emp
WHERE mgr IS NULL;

-- 5.查询表的结构
DESC emp;

-- 使用order by 子句
-- 1.如何按照工资从低到高的顺序【升序】,显示雇员信息
SELECT * FROM emp 
ORDER BY sal ASC;-- 默认的，asc写不写都可以
-- 2.按照部门号升序而雇员的工资降序排列，显示雇员信息(排序之后再排序)
-- (这个排序分顺序，前面的前排)意思是先按照部门号进行升序，然后按照相同部门号的内部的薪水进行降序（分组来看）


SELECT * FROM emp
ORDER BY deptno ASC,sal DESC ;

• 排序第二题的结果
  

分页查询

在实际开发中的查询中，如果一个表有10w条记录，我们查询这个表，不可能将10w张记录同时返回，这就设计到分页操作

-- 分页查询
-- 按照雇员的工号升序取出，每页显示3条记录，分别显示第一页，第二页，第三页
-- limit start,rows 表示每次从start+1行开始取，每次取出rows行 

-- 第一页
SELECT * FROM emp
 ORDER BY empno
 LIMIT 0,3;-- 1~3
 
 -- 第二页
 SELECT * FROM emp
 ORDER BY empno
 LIMIT 3,3;-- 4~6
 
 -- 第三页
SELECT * FROM emp
ORDER BY empno 
LIMIT 6,3; -- 7~9
 
 

练习题

-- 练习
 -- 按照雇员的工号降序取出，每页显示5条记录，请分别显示第三页 第五页对应的sql语句
 
 -- 第三页
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 10,5; 
 -- 第五页
 SELECT * FROM emp
 ORDER BY empno DESC
 LIMIT 20,5;
 
 
 
 

分组加强(听起来很迷糊省略)

多子句查询

注意各个子句的顺序

多表查询

多表笛卡尔集

从上面的评论内容可以看成，这个评论的内容一定是来自一个评论表和一个商品表

多表查询的关键在于对笛卡尔集进行过滤操作，而上面2个表都有部门号这个列，必须部门号相同才是有效的记录，所有可以通过部门号相同来进行过滤

注意上面的建议：多表查询的条件不能少于表的个数-1。2张表的过滤条件至少1个，3张表查询过滤条件至少2个，依次类推

多表查询

-- 1.如果显示部门号为10的部门名，员工名和工资(部门号也一起显示)

/*
	员工名 工资 emp表
	部门名 部门号 dept表
*/

-- 过滤条件为工资必须在相应的级别内
SELECT ename,sal,dname,emp.`deptno` FROM emp,dept
WHERE emp.`deptno`= dept.`deptno` AND emp.`deptno`=10;

-- 2.显示各个员工的姓名，工资，及其工资的级别
-- 姓名和工资-->emp表
-- 工资级别--->salgrade表
SELECT ename,sal,grade FROM emp,salgrade 
WHERE sal BETWEEN losal AND hisal;

写一个sql先写一个简单的，然后进行条件的叠加

• 练习

-- 练习
-- 显示雇员名，雇员工资以及所在部门的名字，并按照部门排序[降序排]

-- 雇员名，雇员工资 emp表
-- 部门名 dept表

SELECT  ename,sal,dname , emp.`deptno` FROM emp,dept

WHERE emp.`deptno` = dept.`deptno` ORDER BY emp.`deptno` DESC;

order 放在 where后面

自连结

alias：别名

• 当我们使用同一个表名时将会报错
  
  1.起的别名不需要使用引号引起来2.别名的as可以省略

-- 多表查询的自连接
-- 显示公司员工名字和他的上级名字

SELECT worker.`ename` AS '职员名',boss.`ename` AS   '上级名'
 FROM   emp   worker ,  emp  boss
 WHERE worker.`mgr`= boss.`empno`;
 
SELECT * FROM emp;
 

将1张表当成2张表使用，过滤条件是雇员的上级编号=上级的员工号
查询结果

• 总结
  
  必须对分成的2张表分别取别名，否则将会报错

多行子查询

单行子查询

--  子查询演示
-- ：如何显示与SMITH同一部门的所有员工
# 分析：我们在查询时限制条件，肯定是部门号等于SMTH的部门号
-- 1.可以先查询到SMITH的部门号
-- 2.把上面的select语句当作一个子查询来使用


-- 1.查询到SMITH的部门号
SELECT deptno 
FROM emp WHERE ename = 'SMITH';

-- 2.将上面的SQL进行嵌套


## 因为该子查询返回的只有一行，所有也称为单行子查询
SELECT ename,deptno 
FROM emp WHERE deptno=(SELECT deptno 
FROM emp WHERE ename = 'SMITH');

• 子句返回结果
  
• 嵌套后返回的结果
  
  在mysql中等于用=表示，不等使用！=或者<>表示

多行子查询

-- 多行子查询

-- 如何查询和部门10的工作相同的雇员的
-- 名字 岗位 工资 部门号 但是不含10号部门自己的雇员
-- 思路：
-- 1.先查询到部门=10有哪些工作(需要注意对返回的工作进行去重)
SELECT DISTINCT job FROM emp
WHERE deptno =10;
-- 2.将上面查询的结果当作子查询使用
SELECT job,sal,deptno FROM emp
WHERE job IN(SELECT DISTINCT job FROM emp
WHERE deptno =10);-- job必须是部门号=10中的一个

-- 进一步优化，不能包含10号部门自己的员工

SELECT job,sal,deptno FROM emp
WHERE job IN(SELECT DISTINCT job FROM emp
WHERE deptno =10)AND deptno !=10;

子查询临时表

子句运行结果：先找到各个类别，价格最高的商品 ，并将其作为一个临时表

然后将临时表和esc_goods表进行多表查询并过滤即可得到结果

经过分析:2个表的过滤条件应该是临时表是最高价格=商品表的价格 并且要求cat_id相同

• 完整代码

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

-- 1.查询商品表
-- 先得到各个类别中，价格最高的商品 max+grop by cat_id,当做临时表
 SELECT cat_id,MAX(shop_price) 
	FROM ecs_goods
	GROUP BY cat_id;
	
	
	
# 2.将前面的表作为临时表

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,-- 将临时表起个别名temp
	ecs_goods
	WHERE temp.cat_id = esc_goods.cat_id
	AND  temp.max_price = esc_goods.shop_price;
	

• 最终运行结果
  
  

all 和any

子句：显示部门号=30的所有薪资

SELECT * FROM emp;

**all需要和其中的所有对比**

-- all和any的使用


-- 要求：显示工资比部门30的所有员工的工资高的员工的姓名 工资和部门号

# 子查询

SELECT sal FROM emp
WHERE deptno=30;

-- 子查询将会返回多个结果，直接>将会出错，用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);

any只用和其中的一个对比

-- 演示any的使用

-- 要求：显示工资比部门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
);

多列子查询

多行子查询的意思是子查询的结果返回的是多列的数据

  -- 多列子查询
  -- 思考如何查询与ALLEN的部门和岗位完全相同的所有雇员（并且不含
  -- ALLEN本人）
  -- 对于该种情况的格式(和子查询字段一一对应)
  -- (字段1，字段2...)=(select 字段1，字段2...)
  -- 子查询：找到ALLEN的部门和岗位
  
   SELECT job,deptno FROM emp
   WHERE ename='SMITH'
   
   
   SELECT * FROM emp
   
   WHERE(job,deptno)=( SELECT job,deptno FROM emp
   WHERE ename='ALLEN')AND ename!='ALLEN';

练习

• 数据来源
  

-- 查询和宋江数学 英语 语文成绩完全相同的学生

-- 子查询：查询出宋江的数学 英语 语文成绩

SELECT math,chinese,english FROM student
WHERE `name`='宋江';

-- 复合句
SELECT * FROM student
WHERE (math,chinese,english)=(SELECT math,chinese,english FROM student
WHERE `name`='宋江');

子查询练习

# 在from 中使用子查询
-- 查找每个部门工资高于本部门平均工资的人的资料
-- 提示将子查询当成一个临时表使用（很自然的想到）
-- 子查询
-- 1.找到每个部门的平均工资

SELECT deptno,AVG(sal)AS avg_sal FROM emp
GROUP BY deptno;
-- 2.将上面的子查询作为一个临时表和emp双表笛卡尔积

SELECT ename,job,mgr,hiredate,sal,comm,emp.`deptno`

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

-- 查找每个部门工资最高的人的详细资料

-- 1.先找到每个部门工资最高的人

SELECT MAX(sal)AS max_sal,deptno FROM emp

GROUP BY deptno;


-- 2.进行多表查询

SELECT ename,job,hiredate,sal,comm,emp.`deptno`
FROM emp,(SELECT MAX(sal)AS max_sal,deptno FROM emp

GROUP BY deptno)AS temp

WHERE emp.`deptno`=temp.`deptno`AND sal=max_sal

-- 查询每个部门的信息（包含：部门名 部门编号 地址）
-- 和人员数量
-- 1.部门名 部门编号 地址来自dept表
-- 2.每个部门的人员数量-->构造一个临时表
-- 1.临时表sql
SELECT COUNT(*)AS '部门人员数量',deptno FROM emp
GROUP BY deptno
-- 2.嵌套操作
SELECT dname,dept.deptno,loc,部门人员数量 FROM dept,
(SELECT COUNT(*)AS '部门人员数量',deptno FROM emp
GROUP BY deptno)AS temp
WHERE dept.`deptno`=temp.`deptno`;

• 运行结果
  
  另一种写法(简易显示列)

-- 另一种写法( 可以将一张表中的所有的列都显示出来)
-- 表.*表示将该表所有的列都显示出来
SELECT temp.*,loc,dname FROM dept,
(SELECT COUNT(*)AS '部门人员数量',deptno FROM emp
GROUP BY deptno)AS temp
WHERE dept.`deptno`=temp.`deptno`;

** 如果2个表时只写一个*,就是将2张表的列都显示出来了**

当2张表的列名不重复的时候，才可以直接写列名

表复制和去重

desc+表名 查看表的结构

-- 表的复制
-- 为了对某条sql语句进行效率测试，我们需要海量的数据时，可以使用此法为表
-- 创建海量的数据
-- 1.创建一张表
CREATE TABLE my_tab02
(
id INT ,
`name` VARCHAR(32),
sal DOUBLE ,
job VARCHAR(32),
deptno INT);

-- 2.将emp表的记录复制到my_tab02
-- 将emp表对应的列的信息复制的my_tab02表的对应字段
INSERT INTO my_tab02 -- 所需要的列要匹配
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp;
-- 3.进行自我复制
-- 反复执行该语句，数据将会成倍增加
INSERT INTO my_tab02-- 此时
SELECT * FROM my_tab02

删除一张表的重复记录

-- 如何删除一张表重复的记录
-- 1.先创建一张表my_tab03
CREATE TABLE my_tab03 LIKE emp;-- 将emp表的结构（列信息）复制给my_tab03

-- 2.使用自我复制使得该表有重复记录

-- 将下面的复制语句执行2遍就已经有了13条重复的记录（总共26条记录）
INSERT INTO my_tab03
SELECT * FROM emp;

-- 3.考虑去重my_tab03的记录

-- 思路：
/*
1.先创建一张临时表my_temp，该表的结构和my_tab03一样
2.把my_tab03的记录通过disdtinct处理后复制到my_temp中
3。清除掉my_tab03的记录
4.把my_temp的记录复制到my_tab03中
5.drop掉临时表my_temp
*/

-- 1.先创建一张临时表my_temp，该表的结构和my_tab03一样
CREATE TABLE my_temp LIKE my_tab03
-- 2.把my_tab03的记录通过disdtinct处理后复制到my_temp中

INSERT my_temp 
SELECT DISTINCT * FROM my_tab03

-- 此时my_temp包含13条不重复的记录
-- 3.清除掉my_tab03的记录
DELETE FROM my_tab03

-- 4.把my_temp的记录复制到my_tab03中
INSERT my_tab03 
SELECT * FROM my_temp;
-- 5.删除掉临时表my_temp

DROP my_temp;

有点想java中交换2个变量的值，使用一个中间变量进行过度

其实当将my_tab03表中的数据数据复制到的temp时，可以直接原表删除，然后将临时表该名为我们之间的表即可

合并查询

• 语句一结果
  
• 语句二结果
  
  使用union all合并
  
  只能进行简单的合并，并不能去重，而且只有当2个子句查询的列是一样的才能进行合并
  union合并
  
  可以将相同的记录去除

外连接需求

• 多表查询思路

思路概括为多表笛卡尔积，然后使用使用where子句进行过滤
是根据关联条件，显示所有匹配的记录，如果匹配不上将不会显示

使用前面的多表查询实现

• dept表
  
• emp表
  
  使用多表查询的结果
  

这样的方式不能显示没有员工的部门SALES，emp表没有和dept关联的deptno

由此可以引出我们的外连接来解决这个问题

左外连接：左边这个表即使没有匹配的记录也会将左边这个表的记录显示出来
右外连接：右边这个表即使没有匹配的记录也会将右边这个表的记录显示出来

左外连右外连

exam和stu表

-- 左外连右外连

-- 1.创建stu
CREATE TABLE stu (
	id INT,
	`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');

-- 2.创建exam

CREATE TABLE exam(
	id INT ,
	grade INT);

INSERT INTO exam VALUES (1,56),(2,76),(11,8);
SELECT * FROM stu;


-- 要求：显示所有人的成绩，姓名和id号，如果没有成绩也要显示该人的
-- 姓名和id，成绩显示为空
-- 1.直接使用双表笛卡尔积，然后按照id号相同过滤

SELECT `name`,stu.`id`,grade 
FROM stu,exam
WHERE stu.`id`=exam.`id`

-- 此时发现没有成绩的学生将没有显示
-- 2.改成左外连接(where去除，表之间的,不要)
-- 此时左边的表就是左表，右边就是右表
SELECT `name`,stu.`id`,grade 
FROM stu LEFT JOIN exam ON
 stu.`id`=exam.`id`

-- 此时左表即使没有匹配上也将会显示出来

-- 使用右外连接（显示所有成绩，即使没有名字匹配，显示空）

SELECT `name`,stu.`id`,grade 
FROM stu RIGHT JOIN exam ON
 stu.`id`=exam.`id`

练习

-- 列出部门名称和这些部门的员工信息（名字和工作），同时列出
-- 那些没有员工的部门名
-- 1.使用右外连接实现
SELECT dname,ename,job
FROM emp RIGHT JOIN dept
 ON dept.`deptno`=emp.`deptno`
 -- 2.使用左外连接实现-
 -- 将2个表交换一下位置就可以了
 SELECT dname,ename,job
FROM dept LEFT JOIN emp
 ON dept.`deptno`=emp.`deptno`