【MySQL】复合查询

于 2024-10-05 23:24:11 发布
阅读量380 收藏 10
点赞数 19
分类专栏： MySQL 文章标签： mysql 数据库 linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jupangMZ/article/details/142719391
版权

目录

🌈前言🌈

📁 笛卡尔积

📁 多表查询

📁 内连接 

📁 外连接

 📂 右外连接

 📂 左外连接

📁 自连接

📁 子查询

 📂 单行子查询

 📂 多行子查询

 📂 多列子查询

 📂 在from子句中使用子查询

 📂 合并查询

📁 总结

🌈前言🌈

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

📁 笛卡尔积

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

        

📁 多表查询

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

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

  1. 连接类型：

    • 内连接（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复合查询
Dirty_artist的博客
05-08 1400
多表查询、自连接、子查询
MySQL复合查询(重点)
wangsir的博客
07-13 1675
MySQL复合查询详细讲解
[MySQL] MySQL复合查询（多表查询、子查询）
weixin_67596609的博客
12-08 3487
前面我们学习了MySQL简单的单表查询。但是我们发现，在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询和一些复杂查询。希望本篇文章会对你有所帮助。
MySQL复合查询(多表查询)
分享学习的知识，记录我的技术成长道路
10-17 1166
通常当进行自查询的时候，为了避免冲突，通常需要为表取别名，由于from是在select之前就执行了的，因此select中可以使用from中起的别名。多列子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。书写复杂的查询过程，一定要先明确执行顺序，子查询是第一个执行的，from语句是第二个执行的，where是第三个执行的，select是最后执行的。在了解复合查询之前，我们需要先复习一下在单表中的查询：这些查询都是在oracle标准表中进行的。使用关键字进行比较。
mysql 复合查询
TH_NUM的博客
03-09 3132
MySQL 的复合查询或者嵌套查询，有表两张，要以 clrTheme 表两张为表列，将 clrColor 横向列出，故选择嵌套查询。 MySQL 复合嵌套查询命令如下 select * from clrTheme as t1, ( (select * from clrColor where `sort` = 0) as c1, (select * from clrColor...
MySQL篇七：复合查询
忘忧.
07-10 1367
前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。
MySQL--复合查询
m0_74099572的博客
05-28 941
之前学过了基本的查询，虽然已经够80%的使用场景了，但是依旧需要了解剩下的20%。
MySQL的复合查询
yyl0327的博客
05-13 826
MySQL中的多表查询是一个强大的功能，允许你在一个查询中组合来自多个表的数据。这通常通过JOIN语句实现，但也可以使用子查询或集合操作符（如UNION下面我将介绍几种常见的多表查询方式及其用途。
mysql 复合查询语句
weixin_30332705的博客
08-16 472
INSERT INTO runwa(rshottime,rmoney,renamecount) VALUES (CURDATE(),(select SUM(MONEY) from income where CREATESHOTTIME = CURDATE()),(select count(distinct hostname) from income where CREATESHOTTIM...
MySQL复合查询（灰常重要o）
01-19
1 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序但雇员的工资降序排序 ...
MySQL 创建主键,外键和复合主键的语句
09-11
本文将详细介绍如何在MySQL中创建主键、外键以及复合主键。 1. **创建主键** 主键是数据库表中一个或一组字段，其值在全表中具有唯一性，并且不能为空。它用于唯一标识表中的每一行记录。在MySQL中，创建主键的...
MySQL全文索引、联合索引、like查询、json查询速度哪个快
09-08
在MySQL数据库中，查询效率是优化数据库性能的关键因素。本文将深入探讨全文索引、联合索引、LIKE查询以及JSON查询这四种不同方法在实际应用中的速度比较，并通过实例进行详细解析。 首先，全文索引（Full-text ...
keepalived+MySQL 5.7编译安装及双活配置
最新发布
听雪楼主
10-01 912
keepalived+MySQL 5.7编译安装及双活配置
【MySQL】MySQL 数据库主从复制详解
人生苦短，睡觉要紧，睡醒再说。
10-01 1180
MySQL 主从复制是实现高可用性和负载均衡的重要手段。通过合理配置和监控，可以显著提升数据库的可用性和性能，有效地管理数据同步，实现系统的弹性和鲁棒性。在实际应用中，应根据业务需求和系统架构来合理设计部署方案，并定期监控复制状态，以确保数据的一致性和完整性。了解主从复制的工作原理及其配置过程，对数据库管理员至关重要。希望本文能帮助您深入理解 MySQL 主从复制的相关知识。
【mysql相关总结】
Charles D
09-30 294
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
docker容器mysql数据备份 mysql容器无法启动备份数据
zhangxu1998lq的博客
09-30 361
mysql是旧容器名 因为mysql的配置文件my.cnf的路径没有映射到服务器，我修改了配置文件，配置文件有错误导致，容器启动不了，一直restarting。nmysql是新容器名 mysql是旧容器名 mysql:5.7是我的镜像名。新容器正常启动，旧容器的数据都还在。
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1212
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
mysql复合条件查询
08-19
在MySQL中，你可以使用逻辑运算符和比较运算符来进行复合条件查询。 下面是一个示例，展示了如何在MySQL中进行复合条件查询： ```sql SELECT * FROM table_name WHERE condition1 AND condition2; ``` 在上面的示例中，`table_name` 是你要查询的表名，`condition1` 和 `condition2` 是你的复合条件。 你可以使用逻辑运算符 `AND` 来连接多个条件，要求同时满足这些条件。你还可以使用其他逻辑运算符，如 `OR` 或 `NOT`，根据你的需求。 以下是一个具体的示例： ```sql SELECT * FROM customers WHERE age > 25 AND city = 'New York'; ``` 上述示例将从名为 `customers` 的表中选择满足条件的记录：年龄大于25岁且所在城市为纽约的客户信息。 请根据你的具体需求，将 `table_name` 替换为你要查询的表名，将 `condition1`、`condition2` 等替换为你的实际条件。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

秋刀鱼的滋味@

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值