MySQL基础——多表查询

多表关系

在数据表中，各个表结构之间存在着各种关系（一对一、一对多、多对多）。

一对一关系：

示例：学生与学生详情的关系，一个学生对应一个详细情况，一个详细情况对应一个学生。

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的UNIQUE。

一对多关系：

示例：学生与班级的关系，一个班级对应多个学生，一个学生对应一个班级。

实现：在一对多关系中，在多的一方建立外键，指向一的一方的主键。

多对多关系：

示例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以给多个学生选择。

实现：在多对多关系中，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

多表查询可以分为连接查询、联合查询和子查询。

连接查询

连接查询有：

• 内连接：相当于查询A、B交集部分数据；

• 外连接：左(右)外连接，查询左(右)表所有数据，以及两张表交集部分数据；

• 自连接：当前表与自身的连接查询，自连接必须使用表别名

以下代码演示所要涉及到的表：

CREATE TABLE emp(
id INT PRIMARY KEY,
NAME VARCHAR(10),
age INT,
job VARCHAR(10),
salary INT,
entrydate DATE,
managerid_id INT,
dept_id INT
);
INSERT INTO emp(id,NAME,age,job,salary,entrydate,managerid_id,dept_id)
VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',NULL,5),
(2,'张无忌',20,'项目经理',12000,'2005-01-11',1,1),
(3,'杨晓',33,'开发',8400,'2000-01-01',2,1),
(4,'韦一笑',48,'开发',11000,'2002-01-01',2,1),
(5,'常遇春',43,'开发',10500,'2004-01-01',3,1),
(6,'小昭',19,'程序员鼓励师',6600,'2004-01-01',2,1),
(7,'灭绝',60,'财务总监',8500,'2002-01-01',1,3),
(8,'周芷若',19,'会计',48000,'2006-01-01',7,3),
(9,'丁敏君',23,'出纳',5350,'2009-01-01',7,3),
(10,'赵敏',20,'市场部总监',12500,'2004-01-01',1,2),
(11,'鹿杖客',56,'职员',3750,'2006-01-01',10,2),
(12,'鹤笔翁',19,'职员',3750,'2007-01-01',10,2),
(13,'房东白',19,'职员',5000,'2009-01-01',10,2),
(14,'张三丰',88,'销售总监',14000,'2004-01-01',1,4),
(15,'玉莲舟',38,'销售',4600,'2004-01-01',14,4),
(16,'宋巧缘',40 ,'销售',4600,'2004-01-01',14,4),
(17,'陈友谅',42,NULL,20000,'2001-01-01',1,NULL);

CREATE TABLE dept(
id INT,
NAME VARCHAR(10)
);
INSERT INTO dept(id,NAME) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');
SELECT*FROM dept;

内连接

内连接可以分为隐式内连接和显式内连接，语法格式如下：

# 隐式内连接
select 字段列表 FROM 表1,表2 where 条件.....;
​
# 显式内连接
select 字段列表 FROM 表1 [inner] join 表2 on 连接条件....;

示例代码如下：

-- 内连接演示
-- 1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id;
SELECT e.`NAME`,d.`name` FROM emp e,dept d WHERE e.`dept_id`=d.`id`;

-- 2.查询每一个员工的姓名，及关联的部门的名称（显示内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id;*/
SELECT e.`NAME`,d.`name` FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id`;

外连接

外连接查询语法格式如下：

# 左外连接
select 字段列表 FROM 表1 left [outer] join 表2 on 条件...;
​
# 右外连接
select 字段列表 FROM 表1 right [outer] join 表2 on 条件...;

示例代码如下：

-- 外连接查询
-- 1.查询emp表的所有数据和对应的部门信息（左外连接）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id;
SELECT e.*,d.`name` FROM emp e LEFT OUTER JOIN dept d ON e.`dept_id`=d.`id`;

-- 2.查询dept表的所有数据和对应的部门信息（右外连接）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id;
SELECT d.*,e.* FROM dept d RIGHT JOIN emp e ON d.`id`=e.`dept_id`;

自连接

自连接查询，可以是内连接查询，也可以是外连接查询，语法格式如下：

select 字段列表 from 表A 别名A join 表名A 别名B on 条件...;

示例代码如下：

-- 自连接
-- 1.查询员工及其所属领导的名字
-- 表结构：emp
-- 连接条件：emp.managerid_id=emp.id;
SELECT a.`NAME`,b.`NAME` FROM emp a,emp b WHERE a.`managerid_id`=b.`id`;

-- 2.查询所有员工emp 及其领导emp,如果员工没有领导，也需要查询出来
-- 表结构：emp
-- 连接条件：emp.managerid_id=emp.id;
SELECT a.`NAME` '员工',b.`NAME` '领导' FROM emp a LEFT JOIN emp b ON a.`managerid_id`=b.`id`;

联合查询

联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集，使用union、union all关键字连接，语法格式如下：

select 字段列表 from 表A union [all] select 字段列表 from 表B

示例代码如下：

-- 联合查询 union,union all
-- 1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
SELECT*FROM emp WHERE salary<5000
UNION
SELECT*FROM emp WHERE age>50;

子查询

在SQL语句中嵌套select语句，称为嵌套查询，又称子查询。语法格式如下：

select * from t1 where column1=(select column1 from t2);

子查询外部的语句可以是insert、update、delete、select的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）；

列子查询（子查询结果为一列）；

行子查询（子查询结果为一行）；

表子查询（子查询结果为多行多列）。

根据子查询位置，可以分为：where之后，from之后，select之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符：= 、<> 、 > 、 >= 、 < 、 <=。

示例代码如下：

-- 标量子查询
-- 1.查询'销售部'的所有员工信息
SELECT id FROM dept WHERE NAME='销售部';
SELECT*FROM emp WHERE dept_id =(SELECT id FROM dept WHERE NAME='销售部');
-- 2.查询在“房东白”入职之后的员工信息
-- a.查询房东白的入职日期
SELECT entrydate FROM emp WHERE NAME='房东白';
-- b.查询指定入职日期之后的入职员工
SELECT*FROM emp WHERE entrydate>(SELECT entrydate FROM emp WHERE NAME='房东白');

列子查询
子查询返回的结果是一列或多列，这种子查询称为列子查询。

常用的操作符：

IN：在指定的集合范围之内，多选一；

NOT IN ：不在指定的集合范围之内；

ANY：子查询返回列表中，有任意一个满足即可；

SOME：子查询返回列表中，有任意一个满足即可；

ALL：子查询返回列表的所有值都必须满足。
示例代码如下：

-- 列子查询
-- 1.查询‘销售部’和‘市场部’的所有员工信息
SELECT id FROM dept WHERE NAME='销售部' OR NAME='市场部';
SELECT*FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME='销售部' OR NAME='市场部');
-- 2.查询比财务部所有人工资都高的员工信息
SELECT id FROM dept WHERE NAME='财务部';
SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='财务部');
SELECT*FROM emp WHERE salary>ALL(SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='财务部'));

行子查询

子查询返回的结果是一行或多列，这种子查询称为行子查询。

常用的操作符：=、<>、IN 、NOT IN。

示例代码如下：

-- 行子查询
-- 1.查询与'张无忌'的薪资及直属领导相同的员工信息
SELECT salary,managerid_id FROM emp WHERE NAME='张无忌';
SELECT*FROM emp WHERE (salary,managerid_id) = (SELECT salary,managerid_id FROM emp WHERE NAME='张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符为IN。

示例代码如下：

-- 表子查询
-- 1.查询与‘鹿杖客’，‘宋巧缘’的职位和薪资相同的员工信息
SELECT job,salary FROM emp WHERE NAME='鹿杖客' OR NAME='宋巧缘';
SELECT*FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE NAME='鹿杖客' OR NAME='宋巧缘');

-- 2.查询入职日期是'2006-01-01'之后的员工信息及其部门(两种写法)
SELECT e.*,d.`name` FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND e.`entrydate`>'2006-01-01' ;
SELECT e.*,d.`name` FROM (SELECT*FROM emp WHERE entrydate>'2006-01-01') e LEFT JOIN dept d ON e.dept_id=d.`id`;