MySQL从零开始了解数据库开发之复合查询_Mysql

今天我们来学习复合查询，复合查询包括以下内容：多表查询，子查询，自连接。理解复合查询就一句话：mysql一切皆为表。

1 多表查询

多表查询顾名思义是从多张表中查询数据，而之前都是从单表读取的。在实际开发过程中，同一模块的数据会存放在很多中，查询时进行多表查询时非常频繁的操作

本节我们用一个简单的公司管理系统，有三张表emp,dept,salgrade来演示如何进行多表查询。
表结构：

现在我们要显示雇员名、雇员工资以及所在部门的名字， 因为雇员名，雇员工资和所在部门数据分别来自emp和dept表，所以这里需要多表查询。

执行语句

select * from emp , dept;

我们会得到一个大表：

这个表是由笛卡尔积得到的，emp表中的每个数据都与dept的数据进行一次匹配整合。

笛卡尔积得到的这张大表就是我们进行多表查询的基础，这个操作将两张表的数据整合到一张表里。接下来我们可以通过增加where条件限制，获取有效的数据：

接下来就可以通过指定列查询需要的数据了！

同样下面几个例子也就可以快速写出来

显示部门号为10的部门名，员工名和工资

select  emp.ename,dept.dname , emp.sal from emp , dept where emp.deptno = dept.deptno and emp.deptno = 10;

显示各个员工的姓名，工资，及工资级别

mysql> select  *  from emp , salgrade where emp.sal between losal and hisal;

2 自连接

刚才我们是对两张不同的表进行笛卡尔积，然后通过大表的数据进行读取，那么对于同一张表也可以进行笛卡尔积！
注意需要对这个表取一下别名，不然会认为是同一张表语法而报错

这样就得到了同一张表的笛卡尔积的结果，称之为自连接。那么什么场景需要是需要自连接来解决的呢？我们来个场景：

显示员工ford的上级领导的编号和姓名（mgr代表员工领导的编号）

因为领导也是员工，所以都储存在员工表中。先分析一下首先先选取匹配的领导，然后找到员工ford；

mysql> select t1.ename , t2.ename '领导', t2.empno from emp t1 , emp t2 where t1.mgr = t2.empno and t1.ename = 'ford';
+-------+--------+-------+
| ename | 领导   | empno |
+-------+--------+-------+
| ford  | jones  |  7566 |
+-------+--------+-------+
1 row in set (0.00 sec)

这就就成功筛选出来了；

3 子查询

子查询用于多表查询，多表查询问题的本质就是想办法将多表转换为单表

3.1 单行子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。区别于上面连接构建笛卡尔积的方法，子查询是通过构建子查询来完成查询任务。

子查询的核心逻辑是：mysql万物皆为表，查询结果也是一种表。

比如下面几个场景

查询smith同一部门的员工

首先需要向找到smith是哪一个部门，然后根据这个部门去查询员工

mysql> select * from emp where ename = 'smith';
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal    | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | null |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select deptno  from emp where ename = 'smith';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)
mysql> select * from emp where deptno = (select deptno  from emp where ename = 'smith');
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800.00 | null |     20 |
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | null |     20 |
|  7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | null |     20 |
|  7876 | adams | clerk   | 7788 | 1987-05-23 | 1100.00 | null |     20 |
|  7902 | ford  | analyst | 7566 | 1981-12-03 | 3000.00 | null |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

这样我们就通过对子查询查询出来的部门去找到了目标员工。

查询与10号部门的工作岗位相同的雇员的名字，岗位，工资，部门名称，但是不包含10自己的

我们来拆解一下查询内容：

首先先找到10号部门的工作岗位
然后根据工作岗位去查询雇员信息
查询的雇员信息与部门表做笛卡尔积找到部门名称

这样一步一步就可以：

mysql> select job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| manager   |
| president |
| clerk     |
+-----------+
3 rows in set (0.00 sec)
mysql> select * from emp where job in (select distinct job from emp where deptno = 10);
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 | null |     20 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 | null |     20 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 | null |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 | null |     10 |
|  7839 | king   | president | null | 1981-11-17 | 5000.00 | null |     10 |
|  7876 | adams  | clerk     | 7788 | 1987-05-23 | 1100.00 | null |     20 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 | null |     30 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 | null |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
8 rows in set (0.01 sec)
mysql> select * from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800.00 | null |     20 |
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | null |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | null |     30 |
|  7876 | adams | clerk   | 7788 | 1987-05-23 | 1100.00 | null |     20 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950.00 | null |     30 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
mysql> select ename , job , sal , dname from (select ename , job , sal , deptno  from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10) as tmp , deept where tmp.deptno = dept.deptno;
+-------+---------+---------+----------+
| ename | job     | sal     | dname    |
+-------+---------+---------+----------+
| smith | clerk   |  800.00 | research |
| jones | manager | 2975.00 | research |
| blake | manager | 2850.00 | sales    |
| adams | clerk   | 1100.00 | research |
| james | clerk   |  950.00 | sales    |
+-------+---------+---------+----------+
5 rows in set (0.00 sec)

所以查询出来的结果也是可以做笛卡尔积的，并且可以作为查询的数据表

3.2 多行子查询

显示工资比部门30的所有员工的工资高的员工的姓名，工资与部门号

首先找到部门30所有员工的最高工资
根据tmp的结果作为筛选条件去查询员工信息

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 > (select max(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)

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

首先找到部门30所有员工的最低工资
根据tmp的结果作为筛选条件去查询员工信息

mysql> select ename , sal , deptno from emp where sal > (select min(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)

总结一下，子查询的结果是可以作为查询范围的。并且可以使用以下关键字进行优化：

all关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename, sal, deptno from emp where sal > all(select sal from emp wheredeptno=30);

any关键字：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename, sal, deptno from emp where sal > any(select sal from emp wheredeptno=30);

3.3 多列子查询

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

来看这个场景：

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

首先查询smith的部门与岗位
将查询出来的部门岗位作为筛选条件，去除smith本人

mysql> select deptno , job from emp where ename = 'smith';
+--------+-------+
| deptno | job   |
+--------+-------+
|     20 | clerk |
+--------+-------+
1 row in set (0.00 sec)
mysql> select * from emp where (deptno , job) = (select deptno , job from emp where ename = 'smith');
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7369 | smith | clerk | 7902 | 1980-12-17 |  800.00 | null |     20 |
|  7876 | adams | clerk | 7788 | 1987-05-23 | 1100.00 | null |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp where (deptno , job) = (select deptno , job from emp where ename = 'smith') and ename != 'smith';
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7876 | adams | clerk | 7788 | 1987-05-23 | 1100.00 | null |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

3.4 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 ename , t1.deptno , sal , t1.avg_sal from (select deptno , avg(sal) avg_sal from emp group by deptno) t1, emp t2 where t1.deptno = t2.deptno and t2.sal > t1.avg_sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| allen |     30 | 1600.00 | 1566.666667 |
| jones |     20 | 2975.00 | 2175.000000 |
| blake |     30 | 2850.00 | 1566.666667 |
| scott |     20 | 3000.00 | 2175.000000 |
| king  |     10 | 5000.00 | 2916.666667 |
| ford  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)
mysql> select ename , dname , sal , avg_sal from (select ename , t1.deptno deptno, sal , avg_sal from (select deptno , avg(sal) avg_sal from emp group by deptno) t1, emp t2 where t11.deptno = t2.deptno and t2.sal > t1.avg_sal) t1 , dept t2 where t1.deptno = t2.deptno;
+-------+------------+---------+-------------+
| ename | dname      | sal     | avg_sal     |
+-------+------------+---------+-------------+
| king  | accounting | 5000.00 | 2916.666667 |
| jones | research   | 2975.00 | 2175.000000 |
| scott | research   | 3000.00 | 2175.000000 |
| ford  | research   | 3000.00 | 2175.000000 |
| allen | sales      | 1600.00 | 1566.666667 |
| blake | sales      | 2850.00 | 1566.666667 |
+-------+------------+---------+-------------+
6 rows in set (0.00 sec)

这样就成功筛选出来了！

显示每个部门的信息（部门名，编号，地址）和人员数量

对emp表进行人员统计
将上面的表看作临时表

mysql> select count(*), deptno from emp group by deptno;
+----------+--------+
| count(*) | deptno |
+----------+--------+
|        3 |     10 |
|        5 |     20 |
|        6 |     30 |
+----------+--------+
3 rows in set (0.01 sec)
mysql> select dept.deptno, dname, mycnt, loc from dept,
    -> (select count(*) mycnt, deptno from emp group by deptno) tmp
    -> where dept.deptno=tmp.deptno;
+--------+------------+-------+----------+
| deptno | dname      | mycnt | loc      |
+--------+------------+-------+----------+
|     10 | accounting |     3 | new york |
|     20 | research   |     5 | dallas   |
|     30 | sales      |     6 | chicago  |
+--------+------------+-------+----------+
3 rows in set (0.00 sec)

3.5 合并查询

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

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

来看一个场景：

将工资大于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 |
+-------+---------+-----------+

4 内外连接

数据表的连接分为内连接与外连接

4.1 内连接

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

语法：

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

对于前面的场景可以使用内连接进行书写：显示smith的名字和部门名称

-- 用前面的写法
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';

4.2 外连接

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

左外连接：如果联合查询，左侧的表完全显示我们就说是左外连接

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

右外连接：如果联合查询，右侧的表完全显示我们就说是右外连接。

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

我们建立一个场景，就可以了解外连接做了一件什么是事情了：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

mysql> select * from stu left join exam on stu.id = exam.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)

可以看到对应没有成绩的，通过左外连接也查询到了。

查询所有成绩，如果这个成绩没有对应学生，也要将成绩显示出来

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

这样就能看到左外连接与右外连接做了一项什么工作了！

到此这篇关于mysql从零开始了解数据库开发之复合查询的文章就介绍到这了,更多相关mysql复合查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！