05.多表查询

#05.多表查询| 来源: 网络整理| 查看: 265

1. 多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）多对多一对一 1.1 一对多案例: 部门与员工的关系关系: 一个部门对应多个员工，一个员工对应一个部门实现: 在多的一方建立外键，指向一的一方的主键

1.2 多对多案例: 学生与课程的关系关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 建表SQL DROP TABLE IF EXISTS `student`; CREATE TABLE student( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', `name` VARCHAR(10) COMMENT '姓名', `no` VARCHAR(10) COMMENT '学号' ) COMMENT '学生表'; INSERT INTO student VALUES (NULL, '黛绮丝', '2000100101'),(NULL, '谢逊','2000100102'),(NULL, '殷天正', '2000100103'),(NULL, '韦一笑', '2000100104'); DROP TABLE IF EXISTS `course`; CREATE TABLE course( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', `name` VARCHAR(10) COMMENT '课程名称' ) COMMENT '课程表'; INSERT INTO course VALUES (NULL, 'JAVA'), (NULL, 'PHP'), (NULL , 'MYSQL') ,(NULL, 'HADOOP'); DROP TABLE IF EXISTS `student_course`; CREATE TABLE student_course( `id` INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY, `student` INT NOT NULL COMMENT '学生ID', `courseid` INT NOT NULL COMMENT '课程ID', CONSTRAINT fk_course_id FOREIGN KEY (courseid) REFERENCES course (id), CONSTRAINT fk_student_id FOREIGN KEY (studentid) REFERENCES student (id) )COMMENT '学生课程中间表'; INSERT INTO student_course VALUES (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,2,2),(NULL,2,3),(NULL,3,4); 1.3 一对多案例: 用户与用户详情的关系关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

-- 建表SQL DROP TABLE IF EXISTS `tb_user`; CREATE TABLE tb_user( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', `name` VARCHAR(10) COMMENT '姓名', `age` INT COMMENT '年龄', `gender` CHAR(1) COMMENT '1: 男 , 2: 女', `phone` CHAR(11) COMMENT '手机号' ) COMMENT '用户基本信息表'; INSERT INTO tb_user(id, name, age, gender, phone) VALUES (NULL,'黄渤',45,'1','18800001111'), (NULL,'冰冰',35,'2','18800002222'), (NULL,'码云',55,'1','18800008888'), (NULL,'李彦宏',50,'1','18800009999') ; DROP TABLE IF EXISTS `tb_user_edu`; CREATE TABLE tb_user_edu( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', `degree` VARCHAR(20) COMMENT '学历', `major` VARCHAR(50) COMMENT '专业', `primaryschool` VARCHAR(50) COMMENT '小学', `middleschool` VARCHAR(50) COMMENT '中学', `university` VARCHAR(50) COMMENT '大学', `userid` INT UNIQUE COMMENT '用户ID', CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id) ) COMMENT '用户教育信息表'; INSERT INTO tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) VALUES (NULL,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1), (NULL,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2), (NULL,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3), (NULL,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4) ; 3. 多表查询概述 3.1 数据准备 -- 建表SQL DROP TABLE IF EXISTS `dept`; CREATE TABLE dept( `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY, `name` VARCHAR(50) NOT NULL COMMENT '部门名称' )COMMENT '部门表'; INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部'); DROP TABLE IF EXISTS `emp`; CREATE TABLE emp( `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY, `name` VARCHAR(50) NOT NULL COMMENT '姓名', `age` INT COMMENT '年龄', `job` VARCHAR(20) COMMENT '职位', `salary` INT COMMENT '薪资', `entrydate` DATE COMMENT '入职时间', `managerid` INT COMMENT '直属领导ID', `dept_id` INT COMMENT '部门ID' )COMMENT '员工表'; -- 添加外键 ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id); INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES (1, '金庸', 66, '总裁',20000, '2000-01-01', NULL,5), (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1), (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1), (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1), (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1), (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1), (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3), (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3), (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3), (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2), (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2), (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2), (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2), (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4), (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4), (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4), (17, '陈友谅', 42, NULL,2000, '2011-10-12', 1,NULL) ; 3.2 概述

多表查询就是指从多张表中查询数据，原来查询单表数据，执行的SQL形式为：select * from emp，要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 具体的执行结果如下:

此时，可以看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录(17) 与部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。

而在多表查询中，是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

在SQL语句中，去除无效的笛卡尔积可以给多表查询加上连接查询的条件。

SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;

而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

3.3 分类

连接查询

内连接：相当于查询A、B交集部分数据外连接：左外连接：查询左表所有数据，以及两张表交集部分数据右外连接：查询右表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名子查询

子查询

4. 内连接

内连接查询的是两张表交集部分的数据。内连接的语法分为两种: 隐式内连接、显式内连接。

语法：

-- 隐式内连接 SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ... ; -- 显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

-- 一般查询 SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id = dept.id; -- 别名查询 SELECT e.name,d.name FROM emp e,dept d WHERE e.dept_id = d.id;

查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)

-- 一般查询 SELECT emp.name,dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id; -- 别名查询 SELECT e.name,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

5. 外连接

外连接分为两种

左外连接右外连接

语法：

左外连接：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据

右外连接：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表1(右表)的所有数据，当然也包含表1和表2交集部分的数据

查询emp表的所有数据, 和对应的部门信息

SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;

查询dept表的所有数据, 和对应的员工信息

SELECT e.*,d.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而在日常开发使用时，更偏向于左外连接。

6. 自连接 6.1 自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

查询员工及其所属领导的名字

SELECT e.name,m.name FROM emp e INNER JOIN emp m ON e.managerid = m.id;

查询所有员工emp及其领导的名字emp , 如果员工没有领导, 也需要查询出来

SELECT e.name,m.name FROM emp e LEFT OUTER JOIN emp m ON e.managerid = m.id;

注意事项: 在自连接查询中，必须要为表起别名，要不然不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

6.2 联合查询

对于UNION查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....; 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。 UNION ALL会将全部的数据直接合并在一起，UNION 会对合并之后的数据去重。

查询薪资低于5000的员工和年龄大于50岁的员工（全部结果）

SELECT * FROM emp WHERE salary < 5000 UNION ALL SELECT * FROM emp WHERE age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

SELECT * FROM emp WHERE salary < 5000 UNION SELECT * FROM emp WHERE age > 50;

union 联合查询，会对查询出来的结果进行去重处理。

注意：如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。

7. 子查询

概念 SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类

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

标量子查询（子查询结果为单个值）列子查询（子查询结果为一列）行子查询（子查询结果为一行）表子查询（子查询结果为多行多列） 7.1 标量子查询

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

常用的操作符：= > >= ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE `name` = "研发部")); 7.3 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、、IN 、NOT IN

查询与 "张无忌" 的薪资及直属领导相同的员工信息

查询 "张无忌" 的薪资及直属领导

SELECT e.salary,m.`name` FROM emp e INNER JOIN emp m ON e.managerid = m.id WHERE e.`name` = "张无忌";

查询与 "张无忌" 的薪资及直属领导相同的员工信息

SELECT * FROM emp WHERE (salary, managerid) = (查询到的薪资，查询刀的直属领导ID);

合并

SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary,managerid FROM emp WHERE `name` = "张无忌"); 7.4 表子查询

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

常用的操作符：IN

查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE `name` IN ("鹿杖客", "宋远桥"));

查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

SELECT e.`name`,e.entrydate,d.`name` FROM (SELECT * FROM emp WHERE entrydate > "2006-01-01") e LEFT JOIN dept d ON e.dept_id = d.id; 8. 实操

环境准备

SET foreign_key_checks = 0; -- 临时关闭外键约束 DROP TABLE IF EXISTS `dept`; CREATE TABLE dept( `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY, `name` VARCHAR(50) NOT NULL COMMENT '部门名称' )COMMENT '部门表'; INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部'); DROP TABLE IF EXISTS `emp`; CREATE TABLE emp( `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY, `name` VARCHAR(50) NOT NULL COMMENT '姓名', `age` INT COMMENT '年龄', `job` VARCHAR(20) COMMENT '职位', `salary` INT COMMENT '薪资', `entrydate` DATE COMMENT '入职时间', `managerid` INT COMMENT '直属领导ID', `dept_id` INT COMMENT '部门ID' )COMMENT '员工表'; ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id); INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES (1, '金庸', 66, '总裁',20000, '2000-01-01', NULL,5), (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1), (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1), (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1), (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1), (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1), (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3), (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3), (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3), (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2), (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2), (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2), (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2), (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4), (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4), (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4), (17, '陈友谅', 42, NULL,2000, '2011-10-12', 1,NULL) ; DROP TABLE IF EXISTS `salgrade`; CREATE TABLE salgrade( grade INT, losal INT, hisal INT ) COMMENT '薪资等级表'; INSERT INTO salgrade VALUES (1,0,3000); INSERT INTO salgrade VALUES (2,3001,5000); INSERT INTO salgrade VALUES (3,5001,8000); INSERT INTO salgrade VALUES (4,8001,10000); INSERT INTO salgrade VALUES (5,10001,15000); INSERT INTO salgrade VALUES (6,15001,20000); INSERT INTO salgrade VALUES (7,20001,25000); INSERT INTO salgrade VALUES (8,25001,30000); SET foreign_key_checks = 1; -- 开启外键约束

查询员工的姓名、年龄、职位、部门信息（隐式内连接）

SELECT e.`name` AS "姓名",e.age AS "年龄",e.job AS "职位", d.`name` AS "部门" FROM emp e, dept d WHERE e.dept_id = d.id;

查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

SELECT e.`name` AS "姓名",e.age AS "年龄",e.job AS "职位", d.`name` AS "部门" FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.age < 30;

查询拥有员工的部门ID、部门名称

SELECT e.*, d.`name` AS "部门" FROM emp e, dept d WHERE e.dept_id = d.id;

查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

SELECT e.age AS "年龄", d.`name` AS "部门" FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;

查询所有员工的工资等级

SELECT e.`name` AS "姓名", e.salary AS "工资", s.grade AS "等级" FROM emp e, salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

查询 "研发部" 所有员工的信息及工资等级

SELECT e.`name` AS "姓名", e.salary AS "工资", s.grade AS "等级", d.`name` AS "部门" FROM emp e, salgrade s, dept d WHERE (e.salary BETWEEN s.losal AND s.hisal) AND (d.`name` = "研发部");

查询 "研发部" 员工的平均工资

SELECT d.`name` AS "部门", AVG(e.salary) AS "平均工资" FROM emp e, dept d WHERE e.dept_id = d.id AND d.`name` = "研发部";

查询工资比 "灭绝" 高的员工信息

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE `name` = "灭绝");

查询比平均薪资高的员工信息

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

查询低于本部门平均工资的员工信息

SELECT e.* FROM emp e WHERE salary < (SELECT AVG(m.salary) FROM emp m WHERE m.dept_id = e.dept_id);

查询所有的部门信息, 并统计部门的员工人数

SELECT d.`name` AS "部门",(SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) AS "总人数" FROM dept d;

【本文地址】

公司简介

联系我们