MySQL-6.表的高级查询（多表查询、子查询、表复制、合并查询、表外连接）

最新推荐文章于 2024-04-14 08:23:21 发布

再学点叭

最新推荐文章于 2024-04-14 08:23:21 发布

阅读量860

点赞数 29

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

本文链接：https://blog.csdn.net/weixin_46356448/article/details/137482278

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

6.1 多表查询

基于两个或以上表的查询，默认从表1取出一行，与表2的每一行组合，返回的记录数为表1×表2，默认返回的结果为笛卡尔集，需写出正确的WHERE条件进行筛选。
多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集。
指定显示某个表的列：表.列

# 显示雇员名,雇员工资及所在部门的名字
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno;

# 显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10

# 显示各个员工的姓名，工资，及其工资的级别
# 思路: 姓名，工资来自emp,工资级别salgrade
# 先写一个简单，然后加入过滤条件... 
select ename, sal, grade
    from emp , salgrade
    where sal between losal and hisal;

自连接：在同一张表的连续查询，即将该表看做两张表；

需要给表取别名：表名表别名；

列名不明确可以指定列的别名。

# 显示公司员工名字和他的上级的名字
# 员工名字在 emp, 上级的名字在 emp
# 员工和上级是通过 emp 表的 mgr 列关联
SELECT worker.ename AS '职员名' , boss.ename AS '上级名' 
    FROM emp worker, emp boss
    WHERE worker.mgr = boss.empno;

6.2 表子查询（嵌套查询）

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

2. 单行子查询是指只返回一行数据的子查询语句。

3. 多行子查询指返回多行数据的子查询，使用关键字 in。

# 显示与 SMITH 同一部门的所有员工
# 先查询 SMITH 的部门号
SELECT deptno FROM emp WHERE ename = 'SMITH';
# 把上面的 select 语句当做一个子查询来使用
SELECT * FROM emp
    WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

# 查询和部门10的工作相同的雇员名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员
# 先查询部门10有哪些工作
SELECT DISTINCT job FROM emp WHERE depno = 10;
# 把上面查询的结果当做子查询使用
SELECT ename, job, sal, depno
    FROM emp
    WHERE job in (SELECT DISTINCT job FROM emp WHERE depno = 10)
        AND depno <> 10;

4. 可以将子查询当做临时表使用：在FROM中使用

# 查询 ecshop 中各个类别中，价格最高的商品
# 查询商品表
# 先得到各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
SELECT cat_id , MAX(shop_price) as max_price
    FROM ecs_goods
    GROUP BY cat_id
# 把子查询当做一张临时表可以解决很多复杂的查询，再给这个临时表去个别名temp
SELECT *
    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;

# 查找每个部门工资高于本部门平均工资的人的资料
# 先得到每个部门的部门号和对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
    FROM emp 
    GROUP BY deptno;
# 把上面的结果当做子查询,临时表,和 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 > temp.avg_sal;

5. 在多行子查询中使用 all 操作符：所有

all和any的区别：all强调所有，any则是其中之一即可。

# 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
# 先查询部门30员工的工资
SELECT sal FROM emp WHERE deptno = 30;
# 比部门30的所有员工工资高——all关键字
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

# 也可以使用MAX()
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);

# 也可以使用MIN
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

6. 多列子查询：查询返回多个列的子查询

# 查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
# 得到 allen 的部门和岗位
SELECT depno, job FROM emp WHERE `name` = "allen";

SELECT * 
    FROM emp
    WHERE (depno, job) = (
        SELECT depno, job FROM emp
            WHERE `name` = "allen") AND name != "allen";

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

6.3 表复制和去重

也称自我复制数据(蠕虫复制)，有时需对sql语句测试效率需要大量数据，用此进行自我复制。

# 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
    (id, `name`, sal, job,deptno)
    SELECT empno, ename, sal, job, deptno FROM emp;

# 自我复制
INSERT INTO my_tab01
    SELECT * FROM my_tab01;

# 删除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

create table my_tmp like my_tab02;
insert into my_tmp select distinct * from my_tab02;
delete from my_tab02;
insert into my_tab02 select * from my_tmp;
drop table my_tmp;

6.4 合并查询

合并多个SELECT语句的查询结果，可以使用集合操作符号：union all、union，直接加在两句之间。

union all：对两个结果集取并集，不会去掉重复行

union：取并集，去掉重复行。

SELECT ename,sal,job FROM emp WHERE sal>2500;
SELECT ename,sal,job FROM emp WHERE job='MANAGER';

SELECT ename,sal,job FROM emp WHERE sal>2500
    UNION ALL
        SELECT ename,sal,job FROM emp WHERE job='MANAGER';

SELECT ename,sal,job FROM emp WHERE sal>2500
    UNION
        SELECT ename,sal,job FROM emp WHERE job='MANAGER';

6.5 表外连接

外连接可以弥补多表查询无法做到的操作。

多表查询利用 where 子句对两张表或者多张表，形成的笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示（即弥补这一部分）。

# 列出部门名称和这些部门的员工名称和工作，同时显示出没有员工的部门
# 多表查询：无法同时显示出没有员工的部门
SELECT dname, ename, job
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
    ORDER BY dname;

# 右连接：显示部门的全部信息，把部门做右表
SELECT dname, ename, job
    FROM emp RIGHT JOIN dept
    ON dept.deptno = emp.deptno
# 左连接：把部门做左表
SELECT dname, ename, job
    FROM dept LEFT JOIN emp
    ON dept.deptno = emp.deptno

左外连接：如果左侧的表完全显示我们就说是左外连接

select ... from 表1 left join 表2 on 条件; (表1为左表）

右外连接：如果右侧的表完全显示我们就说是右外连接

select ... from 表1 right join 表2 on 条件;

# 外连接
# 学生表stu和成绩表exam
# 左连接：显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空
# 如果使用多表查询，只能显示有成绩的学生，没有成绩的无法显示
SELECT `name`, stu.id, grade
    FROM stu, exam
    WHERE stu.id = exam.id;

# 左连接：
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;