七、多表查询
现实生活中,我们遇到的信息基本都是复合得信息,比如一个商品信息,不仅包含了手机的信息,还包括了一些评论等等,所以涉及到多表查询。
多表查询是指基于两个和两个以上的表查询.在实际应用中查询单个表可能不能满足你的需求,(如下面的课堂练习),需要使用到(dept表和emp表)
-- 多表查询
-- 显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
SELECT * FROM emp, dept -- 图1
-- 如果直接使用SELECT COUNT(*) FROM emp, dept 得到的结果是52,就是emp的每一条信息与dept的每一条进行组合。如下图1,这样的结构显然不是我们想要的,所以我们还需要对信息进行过滤。
SELECT ename,sal,dname,emp.deptno -- 图2
FROM emp, dept
WHERE emp.deptno = dept.deptno -- 只筛选部门一样的信息,这样得到的结果就是我们想要的
-- 小技巧:多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集 两个表至少需要1个过滤条件,三个表需要2个...
-- ?如何显示部门号为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;
自连接
我们上面遇到的多表查询,是将很多不同的表,连接起来组成一张新的表。自连接就是将本表进行多次连接。什么时候会用到呢,我们来理解一下下面这个案例,还是用到之前的emp表
我们看这个表,每个员工信息都有一个mgr信息即是上级领导的编号,那我要是想不显示编号,直接显示领导的名称呢,是不是可以利用多表查询,只不过两个表是同一个表。
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 员工和上级是通过 emp表的 mgr 列关联
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确,可以指定列的别名 列名 as 列的别名 添加别名可以直接在后面写上别名即可
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;
子表查询
子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套;接下来直接理解案例。
-- 子查询的演示
-- 请思考:如何显示与SMITH同一部门的所有员工?
/*
1. 先查询到 SMITH的部门号得到
2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno -- 先查到SMITH的部门号
FROM emp
WHERE ename = 'SMITH'
-- 下面的答案.
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)
/*
上面这个案例就是子表查询
查询与SMITH同一部门的所有员工
首先要得到SMITH的部门号
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
即使得到部门号
在利用得到的部门号,查询所有员工
SELECT *
FROM emp
WHERE deptno = (
部门号;
)
*/
-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员.
/*
1. 查询到10号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
select distinct job -- 先查到10号部门有哪些工作 去重
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 -- 不等于的两种表达 != <>
临时表
直接理解一个案例,还是利用上面用到的emp表
-- 请思考:查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧,把一个子查询当作一个临时表使用
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal -- 我们应该把emp表和我们整理的这个新的表进行组合筛选
FROM emp GROUP BY deptno -- 图一 假设这个表的名字叫做temp,那接下来该怎么做呢
/*
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, temp
where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
*/
-- 所以我们应该先做一个新的临时表,再利用这个临时表temp和emp进行多表查询 格式如下
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptno -- temp.avg_sal表示temp表中的avg_sal这一列
FROM emp, (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp
where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, max(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
-- 查询每个部门的信息(包括:部门名,编号,地址)和人员数量
-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -》 构建一个临时表
select count(*), deptno
from emp
group by deptno;
select dname, dept.deptno, loc , tmp.per_num as '人数'
from dept, (
SELECT COUNT(*) as per_num, deptno
FROM emp
GROUP BY deptno
) tmp
where tmp.deptno = dept.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中,当多个表的列不重复时,才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
常用关键字
all
any(some)
exists / not exists
union
-- all 和 any的使用
-- some 和any 相同,这里以any举例
-- 请思考:显示工资比部门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
)
-- 请思考:如何显示工资比部门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
)
-- exists
CREATE table test1(num INT);
INSERT INTO test1 VALUES(1),(2);
CREATE table test2(num INT);
INSERT INTO test2 VALUES(3),(4);
SELECT * FROM test1 WHERE EXISTS(SELECT * FROM test2 WHERE num>1); -- 执行前面的查询语句,相当于 SELECT * FROM test1
SELECT * FROM test1 WHERE EXISTS(SELECT * FROM test2 WHERE num>4); -- 不执行前面的查询语句,返回空
SELECT * FROM test1 WHERE NOT EXISTS(SELECT * FROM test2 WHERE num>4); -- 取反
-- 即exists后面是true,就执行前面的语句,为false就不执行前面的语句;exists后面的查询语句没查到信息,就相当于返回false
-- exists可以省略,但不建议
-- union 两个查询结果合并,前提是字段相同
SELECT * FROM test1
union -- 去重
SELECT * FROM test2;
SELECT * FROM test1
union all -- 不去重
SELECT * FROM test2
多列子查询
-- 多列子查询
-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1, 字段2 ...) = (select 字段 1,字段2 from 。。。。)
-- 每个字段要对应相等才算满足要求
-- 分析: 1. 得到smith的部门和岗位
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 *
FROM student
WHERE (math, english, chinese) = (
SELECT math, english, chinese
FROM student
WHERE `name` = '宋江'
)
SELECT * FROM student;
表复制
-- 表的复制
-- 为了对某个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
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制 数量变成了原来的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;
-- 就相当于a = 1; b = 2 现在要求交换值一样
合并查询
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查询结果合并,不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查询结果合并,会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3