MySQL复合查询和内外连接的操作代码

管理员 2023-06-22 06:57:51 互联网圈 16 ℃ 0 评论 23812字收藏

一、复合查询

1.1 多表查询

实际开发中常常数据来自区别的表，所以需要多表查询，但是可以将多张表做笛卡尔积后的表当作是一张表，也就是单表查询。
我们用一个简单的公司管理系统，有三张表EMP、DEPT、SALGRADE来演示如何进行多表查询。

语法：from 表1, 表2 ...
这样其实就是对多张表进行笛卡尔积，假定是两张表：

第一张表的第一行和第二张表的所有行组合。
第一张表的第二行和第二张表的所有行组合。…

在这里插入图片描述

这样所有的组合，其实不都是成心义的，所有一般都是根据条件挑选的。

在多表查询中，有可能多张表有相同的字段，为了便于辨别，可使用表名.字段名表示某一张表的某一个字段。

1.2 自连接

自连接是对一张表的查询，但是使用多张表的查询方式，自己做笛卡尔积。
例如：

mysql> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT ‘雇员编号’,
`ename` varchar(10) DEFAULT NULL COMMENT ‘雇员姓名’,
`job` varchar(9) DEFAULT NULL COMMENT ‘雇员职位’,
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT ‘雇员领导编号’,
`hiredate` datetime DEFAULT NULL COMMENT ‘雇佣时间’,
`sal` decimal(7,2) DEFAULT NULL COMMENT ‘工资月薪’,
`comm` decimal(7,2) DEFAULT NULL COMMENT ‘奖金’,
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT ‘部门编号’
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号）。
这是员工表，领导也是属于员工，所以没法只用一条简单查找语句对一张表查找两次，这就需要用到复合查询。
子查询 — 即嵌套查询，使用两条查询语句

使用自连接的两张表查询，这里需要对表使用别名

1.3 子查询

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

1.3.1 单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

1.3.2 多行子查询

返回多行记录的子查询，依然是一列（一个字段）。

in关键字（属于查询出来的多行中的一行，没法用于比较）：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包括10自己的
先拆分，找到10号部门的岗位：

mysql> select distinct job from emp where deptno = 10;
+———–+
| job |
+———–+
| MANAGER |
| PRESIDENT |
| CLERK |
+———–+
3 rows in set (0.00 sec)
— 然后再复合
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 |
+——–+———–+———+——–+
| CLARK | MANAGER | 2450.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| MILLER | CLERK | 1300.00 | 10 |
+——–+———–+———+——–+
3 rows in set (0.00 sec)

all关键字（与查询结果的所有行比较为真的）：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
一样可以拆分

— 先找到部门号为30的所有工资
mysql> select sal from emp where deptno = 30;
+———+
| sal |
+———+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+———+
6 rows in set (0.00 sec)
— 判断 > 所有行
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 |
+——-+———+——–+
4 rows in set (0.00 sec)

any关键字（与查询结果的任意一行比较为真的）：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包括自己部门的员工） some是any的别称很少使用。

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 |
+——–+———+——–+
12 rows in set (0.00 sec)

1.3.3 多列子查询

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

其实很简单，多列也能够比较，和单列一样。

案例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

1.3.4 在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技能，把一个子查询当作一个临时表使用。

显示每一个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

— 先找到每一个部门的平均工资
mysql> select deptno, avg(sal) from emp group by deptno;
+——–+————-+
| deptno | avg(sal) |
+——–+————-+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+——–+————-+
3 rows in set (0.00 sec)
— 然后再用这张表和本来的表做笛卡尔积，多表查询
mysql> select emp.ename, emp.deptno, emp.sal, format(dept_avg,2) from emp , (select deptno , avg(sal) dept_avg from emp group by deptno) tmp wheree tmp.deptno = emp.deptno and emp.sal > dept_avg;
+——-+——–+———+——————–+
| ename | deptno | sal | format(dept_avg,2) |
+——-+——–+———+——————–+
| ALLEN | 30 | 1600.00 | 1,566.67 |
| JONES | 20 | 2975.00 | 2,175.00 |
| BLAKE | 30 | 2850.00 | 1,566.67 |
| SCOTT | 20 | 3000.00 | 2,175.00 |
| KING | 10 | 5000.00 | 2,916.67 |
| FORD | 20 | 3000.00 | 2,175.00 |
+——-+——–+———+——————–+
6 rows in set (0.00 sec)

显示每一个部门的信息（部门名，编号，地址）和人员数量
对有未知的，聚合的信息（人员数量），先将它求出来

mysql> select * from emp limit 3;
+——–+——-+———-+——+———————+———+——–+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——–+——-+———-+——+———————+———+——–+——–+
| 007369 | SMITH | CLERK | 7902 | 1980⑴2⑴7 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02⑵0 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02⑵2 00:00:00 | 1250.00 | 500.00 | 30 |
+——–+——-+———-+——+———————+———+——–+——–+
3 rows in set (0.01 sec)

暴力使用多表查询
注意：和聚合函数一起显示的字段，需要被分组，由于聚合函数对某一个字段是不清楚的，比如count(*)，它是统计总数，其实不对应某一个deptno,ename这些字段

mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘scott.dept.dname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
—- 注意：和聚合函数一起显示的字段，需要被分组，由于聚合函数对某一个字段是不清楚的，比如count(*)，它是统计总数，其实不对应某一个deptno,ename这些字段
mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno, dept.dname, dept.loc;
+————+——–+———-+———-+
| dname | deptno | loc | count(*) |
+————+——–+———-+———-+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+————+——–+———-+———-+
3 rows in set (0.00 sec)

使用子查询

— 先子查询出每一个部门的人员数量
mysql> select deptno, count(*) from emp group by deptno;
+——–+———-+
| deptno | count(*) |
+——–+———-+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+——–+———-+
3 rows in set (0.00 sec)
— 再和部门表组合，根据条件挑选。
mysql> select dept.dname, dept.deptno, dept.loc, dept_cnt from dept, (select deptno, count(*) dept_cnt from emp group by deptno) cnt_table where
cnt_table.deptno = dept.deptno;
+————+——–+———-+———-+
| dname | deptno | loc | dept_cnt |
+————+——–+———-+———-+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+————+——–+———-+———-+
3 rows in set (0.00 sec)

1.4 合并查询

在实际利用中，为了合并多个select的履行结果，可使用集合操作符 union，union all

1.4.1 union

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

将工资大于2500或职位是MANAGER的人找出来
岗位是MANAGER的一定有工资大于2500的，这里自动去重了

1.4.2 union all

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

二、内外连接

2.1 内连接

内连接inner join实际上就是利用where子句对两种表构成的笛卡儿积进行挑选，我们前面学习的查询都是内连接，也是在开发进程中使用的最多的连接查询。

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

前面使用的都是内连接。

on后面是两张表的连接条件，后可以跟where条件语句查询表。

— 用前面的写法
select ename, dname from emp, dept where emp.deptno=dept.deptno and ename=’SMITH’;
— 用标准的内连接写法
select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename=’SMITH’;

两张表连接，其实不是所有行都是有效的，所有使用连接条件更加明确。

2.2 外连接

外连接分为左外连接和右外连接

2.2.1 左外连接

如果联合查询，左边的表完全显示我们就说是左外连接。
甚么叫完全显示呢？
就是两张表通过连接条件连接，但是左边的表有不满足条件的也要全部显示，右边的表显示为NULL。
语法：

select 字段名 from 表名1 left join 表名2 on 连接条件

创建两张表

mysql> select * from exam;
+——+——-+
| id | grade |
+——+——-+
| 1 | 56 |
| 2 | 76 |
| 11 | 8 |
+——+——-+
3 rows in set (0.00 sec)

mysql> select * from stu;
+——+——+
| id | name |
+——+——+
| 1 | jack |
| 2 | tom |
| 3 | kity |
| 4 | nono |
+——+——+
4 rows in set (0.00 sec)

使用内连接

mysql> select * from stu inner join exam on exam.id = stu.id;
+——+——+——+——-+
| id | name | id | grade |
+——+——+——+——-+
| 1 | jack | 1 | 56 |
| 2 | tom | 2 | 76 |
+——+——+——+——-+
2 rows in set (0.00 sec)

使用stu为左表的左外连接

mysql> select * from stu left join exam on exam.id = stu.id;
+——+——+——+——-+
| id | name | id | grade |
+——+——+——+——-+
| 1 | jack | 1 | 56 |
| 2 | tom | 2 | 76 |
| 3 | kity | NULL | NULL |
| 4 | nono | NULL | NULL |
+——+——+——+——-+
4 rows in set (0.00 sec)

使用exam为左表的左外连接

mysql> select * from exam left join stu on exam.id = stu.id;
+——+——-+——+——+
| id | grade | id | name |
+——+——-+——+——+
| 1 | 56 | 1 | jack |
| 2 | 76 | 2 | tom |
| 11 | 8 | NULL | NULL |
+——+——-+——+——+
3 rows in set (0.00 sec)

2.2.1 右外连接

和左外连接对称，左边不满足右边的填充为NULL。

select 字段 from 表名1 right join 表名2 on 连接条件；

mysql> select * from exam right join stu on exam.id = stu.id;
+——+——-+——+——+
| id | grade | id | name |
+——+——-+——+——+
| 1 | 56 | 1 | jack |
| 2 | 76 | 2 | tom |
| NULL | NULL | 3 | kity |
| NULL | NULL | 4 | nono |
+——+——-+——+——+
4 rows in set (0.00 sec)

列出部门名称和这些部门的员工信息，同时列出没有员工的部门
明显要以部门表为主

左外连接是以左表为主
右外连接是以右表为主

到此这篇关于MySQL复合查询和内外连接的文章就介绍到这了,更多相关MySQL复合查询内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！

文章来源：丸子建站

文章标题：MySQL复合查询和内外连接的操作代码

https://www.wanzijz.com/view/57872.html