[MySQL]复合查询
目录
????前言????
???? 笛卡尔积
???? 多表查询
???? 内连接
???? 外连接
???? 右外连接
???? 左外连接
???? 自连接
???? 子查询
???? 单行子查询
???? 多行子查询
???? 多列子查询
???? 在from子句中使用子查询
???? 合并查询
???? 总结
????前言????
欢迎收看本期【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♪(・ω・)ノ