【MySQL】复合查询

🌈前言🌈

    欢迎收看本期【MySQL】，本期内容将讲解MySQL中复合查询的内容，主要包括了笛卡尔积的概念，内外连接等概念，以及使用这些SQL语句。

📁 笛卡尔积

    在离散数学中，笛卡尔积是指两个集合中所有可能元素的组合。在数据库中也是如此，即**对两张表进行笛卡尔积，会将两表中每一条记录进行组合**。

📁 多表查询

    多表查询是指在一个SQL查询中同时涉及多个表的数据检索，这种查询通常用于获取不同表之间相关的消息。

    多表查询的基础就是将两张表先进行笛卡尔积，在使用where子句进行条件筛选。

连接类型：- 内连接（INNER JOIN）：只返回在两个表中都有匹配的记录。- 左外连接（LEFT JOIN）：返回左表中的所有记录，以及右表中匹配的记录，如果没有匹配则结果为 NULL。- 右外连接（RIGHT JOIN）：返回右表中的所有记录，以及左表中匹配的记录，如果没有匹配则结果为 NULL。- 全外连接（FULL OUTER JOIN）：返回两个表中所有的记录，无论是否有匹配，未匹配的部分用 NULL 填充。- 交叉连接（CROSS JOIN）：返回两个表中所有可能的组合，即笛卡尔积。
```
 上述，本文只讲解内连接，外连接中的左右外连接。

 例如，我们有以下两个表，一张表存储学生信息，另一张表存储部分学生的选课信息，我们想要通过多表查询，得到已选课的学生信息以及选课情况：
```

mysql> select * from student;
+--------+--------+
| stu_id | name   |
+--------+--------+
|      1 | 张三   |
|      2 | 李四   |
|      3 | 王五   |
|      4 | 赵六   |
+--------+--------+

mysql> select * from enrollments;
+---------+--------+---------+
| enro_id | stu_id | course  |
+---------+--------+---------+
|       1 |      1 | math    |
|       2 |      1 | science |
|       3 |      2 | history |
|       4 |      5 | art     |
+---------+--------+---------+

    现在我们要进行多表查询，即对两张表进行笛卡尔积。

mysql> select * from student,enrollments;
+--------+--------+---------+--------+---------+
| stu_id | name   | enro_id | stu_id | course  |
+--------+--------+---------+--------+---------+
|      4 | 赵六   |       1 |      1 | math    |
|      3 | 王五   |       1 |      1 | math    |
|      2 | 李四   |       1 |      1 | math    |
|      1 | 张三   |       1 |      1 | math    |
|      4 | 赵六   |       2 |      1 | science |
|      3 | 王五   |       2 |      1 | science |
|      2 | 李四   |       2 |      1 | science |
|      1 | 张三   |       2 |      1 | science |
|      4 | 赵六   |       3 |      2 | history |
|      3 | 王五   |       3 |      2 | history |
|      2 | 李四   |       3 |      2 | history |
|      1 | 张三   |       3 |      2 | history |
|      4 | 赵六   |       4 |      5 | art     |
|      3 | 王五   |       4 |      5 | art     |
|      2 | 李四   |       4 |      5 | art     |
|      1 | 张三   |       4 |      5 | art     |
+--------+--------+---------+--------+---------+

    这时，我们在使用where子句进行筛选，得出我们想要的正确结果来：

mysql> select * from student,enrollments where 
                        student.stu_id = enrollments.stu_id;
+--------+--------+---------+--------+---------+
| stu_id | name   | enro_id | stu_id | course  |
+--------+--------+---------+--------+---------+
|      1 | 张三   |       1 |      1 | math    |
|      1 | 张三   |       2 |      1 | science |
|      2 | 李四   |       3 |      2 | history |
+--------+--------+---------+--------+---------+

📁 内连接

    有了笛卡尔积的基础，了解了什么是多表查询后，我们来学习什么是内连接。

    内连接，就是在笛卡尔积的基础上，保留下来两张表都有的记录。

    上文多表查询的代码，最终结果就是一个内连接的结果，但是这样写有一点麻烦，我们使用规范的SQL语句。

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

   ** 我们想要通过内连接，得到已选课的学生信息以及选课情况：**

mysql> select * from student inner join enrollments 
                        on student.stu_id = enrollments.stu_id;
+--------+--------+---------+--------+---------+
| stu_id | name   | enro_id | stu_id | course  |
+--------+--------+---------+--------+---------+
|      1 | 张三   |       1 |      1 | math    |
|      1 | 张三   |       2 |      1 | science |
|      2 | 李四   |       3 |      2 | history |
+--------+--------+---------+--------+---------+

📁 外连接

📂 右外连接

    右外连接，保存右表中所有记录，当记录对应的字段在左表不存在时，使用NULL填充。

mysql> select * from student right join enrollments 
                        on student.stu_id = enrollments.stu_id;
+--------+--------+---------+--------+---------+
| stu_id | name   | enro_id | stu_id | course  |
+--------+--------+---------+--------+---------+
|      1 | 张三   |       1 |      1 | math    |
|      1 | 张三   |       2 |      1 | science |
|      2 | 李四   |       3 |      2 | history |
|   NULL | NULL   |       4 |      5 | art     |
+--------+--------+---------+--------+---------+

📂 左外连接

    左外连接，保存左表中所有记录，当记录对应的字段在右表不存在时，使用NULL填充。

mysql> select * from student left join enrollments 
                        on student.stu_id = enrollments.stu_id;
+--------+--------+---------+--------+---------+
| stu_id | name   | enro_id | stu_id | course  |
+--------+--------+---------+--------+---------+
|      1 | 张三   |       2 |      1 | science |
|      1 | 张三   |       1 |      1 | math    |
|      2 | 李四   |       3 |      2 | history |
|      3 | 王五   |    NULL |   NULL | NULL    |
|      4 | 赵六   |    NULL |   NULL | NULL    |
+--------+--------+---------+--------+---------+

📁 自连接

    自连接是指同一张表连接查询。

    例如，有一张员工表，记录了员工工号和领导的工号，现在想查询员工名字和对应的领导名字：

mysql> select * from employees;
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
|           1 | 张三          |       NULL |
|           2 | 李四          |          1 |
|           3 | 王五          |          1 |
|           4 | 赵六          |          2 |
+-------------+---------------+------------+

mysql> select  e1.employee_name as employee,e2.employee_name as manager 
                    from employees as e1 inner join employees as e2 
                    on e1.employee_name = e2.employee_name;
+----------+---------+
| employee | manager |
+----------+---------+
| 张三     | 张三    |
| 李四     | 李四    |
| 王五     | 王五    |
| 赵六     | 赵六    |
+----------+---------+

📁 子查询

   ** 子查询是指嵌入在其他sql语句中的select语句，也叫做嵌套语句。**

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+

📂 单行子查询

** 返回一行记录的子查询。**

** 查询和smith在同一个部门的员工：**

mysql> select * from emp where deptno = (select deptno from emp where ename like 'smith');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+

📂 多行子查询

** 返回多行记录的子查询。**

    **in **关键字：用于比较某个值是否存在于子查询返回的结果集中。

//查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但不包含10号部门自己
mysql> select ename,job,sal,deptno from emp where job in 
       (select distinct job from emp where deptno=10) and deptno != 10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| SMITH | CLERK   |  800.00 |     20 |
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+

   ** any **关键字：用于比较某个值和子查询中返回的结果集中的任意值。

//显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
mysql> select ename, sal, deptno from emp where sal > 
                        any(select sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+

   ** all **关键字：用于比较某个值和子查询返回的结果集中的所有值。

//显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
mysql> select ename,sal,deptno from emp where sal > all (select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

📂 多列子查询

    单行子查询是指子查询只返回单列，单列数据；多行子查询是指返回单列多行数据，都是针对单列而言，而多列子查询则是指查询返回多个列数据的子查询语句。

//查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from emp where (deptno, job)=(select deptno, job 
                    from emp where ename='SMITH') and ename != 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+

📂 在from子句中使用子查询

    select语句得到的结果集就是一个临时表，因此我们也可以在临时表中进行查询，再次得到一个结果集。

//显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
mysql> select ename,deptno,sal,asal from emp,
                (select avg(sal) as asal from emp group by deptno) as temp 
                 where emp.sal > temp.asal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | asal        |
+-------+--------+---------+-------------+
| FORD  |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| CLARK |     10 | 2450.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 2175.000000 |
| JONES |     20 | 2975.00 | 2175.000000 |
| FORD  |     20 | 3000.00 | 1566.666667 |
| KING  |     10 | 5000.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 1566.666667 |
| CLARK |     10 | 2450.00 | 1566.666667 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 1566.666667 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| FORD  |     20 | 3000.00 | 2916.666667 |
| KING  |     10 | 5000.00 | 2916.666667 |
| SCOTT |     20 | 3000.00 | 2916.666667 |
| JONES |     20 | 2975.00 | 2916.666667 |
+-------+--------+---------+-------------+

📂 合并查询

union：

    该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

//将工资大于2500或职位是MANAGER的人找出来
mysql> select ename,sal,job from emp where sal > 2500 
                union select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+

union all：

//将工资大于2500或职位是MANAGER的人找出来
mysql> select ename,sal,job from emp where sal > 2500 
            union all select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+

📁 总结

    以上就是本期【MySQL】的全部内容了，主要包含了多表查询的基础，即笛卡尔积，内外连接，自连接和子查询的概念，运用多个表进行讲解。

    如果感觉本期内容对你有帮助，欢迎点赞，关注，收藏Thanks♪(･ω･)ﾉ

标签： mysql 数据库 linux

本文转载自: https://blog.csdn.net/jupangMZ/article/details/142719391
版权归原作者 秋刀鱼的滋味@ 所有，如有侵权，请联系我们删除。

【MySQL】复合查询

🌈前言🌈

📁 笛卡尔积

📁 多表查询

📁 内连接

📁 外连接

📂 右外连接

📂 左外连接

📁 自连接

📁 子查询

📂 单行子查询

📂 多行子查询

📂 多列子查询

📂 在from子句中使用子查询

📂 合并查询

📁 总结

发表评论

“【MySQL】复合查询”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

【MySQL】复合查询

🌈前言🌈

📁 笛卡尔积

📁 多表查询

📁 内连接

📁 外连接

** 📂 右外连接**

** 📂 左外连接**

📁 自连接

📁 子查询

** 📂 单行子查询**

** 📂 多行子查询**

** 📂 多列子查询**

** 📂 在from子句中使用子查询**

** 📂 合并查询**

📁 总结

发表评论

“【MySQL】复合查询”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

📂 右外连接

📂 左外连接

📂 单行子查询

📂 多行子查询

📂 多列子查询

📂 在from子句中使用子查询

📂 合并查询