Mysql数据库基础（五）

多表关系

在项目开发进行数据库结构设计时，根据业务需求和业务模块之间的关系进行分享并设计表的结构，而由于业务之间是相互关联的，所以各个表结构之间也存在着各种联系，主要分为 一对多（多对一）、多对多、一对一 这三种

一对多（多对一）

例如部门与员工的关系：一个部门可以对应多个员工，而一个员工则对应一个部门，这种可以通过在多的一方建立外键，指向一的一方的主键

多对多

例如学生与课程的关系：一个学生可以选修多门课程，而一个课程可以被多名学生选修，这种可以通过建立第三张中间表，该表至少包含两个外键，分别关联两方主键

创建学生表并插入数据：

-- 学生表
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');

创建课程表并插入数据：

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');

上述两张表没有关联，可以通过中间表进行关联：

create table student_course(
	id int auto_increment comment '主键' primary key,
	studentid int not null comment '学生ID',
	courseid int not null comment '课程ID',
	constraint fk_courseid foreign key (courseid) references course(id),
	constraint fk_studentid foreign key (studentid) references student(id)
)comment '学生课程中间表';

insert into student_course 
values (null,1,1),(null,1,2),(null,1,3),(null,2,3),(null,3,4);

一对一

例如用户与用户详情的关系。一对一的关系多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段则放在另一张表中，用以提升操作效率

这种关系跟一对多（多对一）的处理类似，区别在于该外键是唯一（UNIQUE）的

创建用户基本信息表并插入数据：

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, '冰冰', 352, '1', '18800002222'),
(null, '码云', 55, '1', '18800008888'),
(null, '李彦宏', 50, '1', '18800009999');

创建用户详情表并插入数据：

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);

可以看出对 userid 使用了唯一 UNIQUE

多表查询

从多张表中查询数据。按照 SELECT * FROM A表,B表 来进行查询会造成数据冗余，即笛卡尔积。很明显，这种数据并不是我们所需要的，所以在进行多表查询时我们需要消除无效的笛卡尔积

笛卡尔积：两个集合的所有组合情况，即A集合有2条数据，B集合有3条数据，那么总共会形成6条数据

多表查询主要分为 连接查询 和 子查询

连接查询分为 内连接、外连接、自连接
子查询，又被称为嵌套查询，分为 标量子查询、列子查询、行子查询、表子查询

连接查询

内连接

内连接所查询的是两张表所交集的部分，分为隐式内连接和显式内连接

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件;

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

查询员工所对应的部门：

-- 隐式内连接
SELECT emp.name as '员工' , dept.name as '部门' FROM emp,dept WHERE emp.dept_id = dept.id;

-- 显式内连接
SELECT emp.name as '员工' , dept.name as '部门' FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

外连接

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

-- 左外连接：表1数据 + 表1和表2交集
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接：表2数据 + 表1和表2交集
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

练习：

-- emp表的所有数据和其对应的部门信息
SELECT e.*,d.name as '部门' FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

-- dept表的所有数据与其所在的员工
SELECT e.name as '员工', d.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

自连接

自连接可以是内连接，也可以是外连接

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

练习：

-- 员工的所属领导
SELECT ework.name as '员工',emana.name as '所属领导' FROM emp ework JOIN emp emana ON ework.managerid = emana.id;
SELECT ework.name as '员工',emana.name as '所属领导' FROM emp ework , emp emana WHERE ework.managerid = emana.id;

-- 员工的所属领导，没有领导也要查出来
SELECT ework.name as '员工',emana.name as '所属领导' FROM emp ework LEFT JOIN emp emana ON ework.managerid = emana.id;

联合查询

将多次查询的结果合并起来形成一个新的结果集：

1
2
3

SELECT 字段列表 FROM 表A
UNION [ALL]
SELECT 字段列表 FROM 表B;

练习：

-- 薪资低于10000的员工和年龄大于30岁的员工
SELECT * FROM emp Where age > 30
UNION ALL
SELECT * FROM emp WHERE salary < 10000;

多张表的列数必须保持一致，字段类型也必须要保持一致

UNION ALL 会将全部查询返回的数据直接合并在一起，而 UNION 会对合并之后的数据去重

子查询

子查询语法：

1	SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)

该外部语句除了可以是查询外，还能是增删改其中一种

标量子查询

所返回的结果是单个值（字符串、数字、日期等），是最简单的形式

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

练习：

1 2	-- 杨逍之后入职的员工信息 SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = '杨逍');

列子查询

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

常见的操作符如下图所示：

练习：

-- 查询销售部和市场部的所有员工信息
SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE name = '销售部' || name = '市场部');

-- 查询比财务部所有人工资高的员工信息
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 = '财务部'));

-- 查询比研发部其中任意一人工资高的员工信息
SELECT * FROM emp WHERE salary > SOME (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));

行子查询

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

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

练习：

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

行子查询

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

常用的操作符有：IN

练习：

-- 查询与'杨逍'薪资和工作相同的员工信息
SELECT * FROM emp WHERE (salary,job) IN (SELECT salary,job FROM emp WHERE name = '杨逍');

-- 查询在2004-09-07后入职的员工部门信息
SELECT * FROM (SELECT * FROM emp WHERE entrydate > '2004-09-07') e LEFT JOIN dept d ON e.dept_id = d.id;

案例练习

建表

上述练习没有删除 emp表和 dept表的数据，但是基本操作相同，除了数据

现在重新创建 dept表，并插入相应数据：

CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, NAME VARCHAR ( 10 ) );

INSERT INTO dept
VALUES
	( NULL, '研发部' ),
	( NULL, '市场部' ),
	( NULL, '财务部' ),
	( NULL, '销售部' ),
	( NULL, '总经办' ),
	( NULL, '人事部' );

创建 emp表，并插入数据：

CREATE TABLE emp (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 10 ),
	age INT,
	job VARCHAR ( 10 ),
	salary INT,
	entrydate date,
	managerid INT,
	dept_id INT,
	CONSTRAINT fk_dept FOREIGN KEY ( dept_id ) REFERENCES dept ( id ) 
);

INSERT INTO emp
VALUES
	( NULL, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
	( NULL, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
	( NULL, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
	( NULL, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
	( NULL, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
	( NULL, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
	( NULL, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
	( NULL, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3 ),
	( NULL, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
	( NULL, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
	( NULL, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
	( NULL, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
	( NULL, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
	( NULL, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
	( NULL, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
	( NULL, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
	( NULL, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );

创建 salgrade表，并插入相应数据：

CREATE TABLE salgrade ( grade INT, losal INT, hisal INT ) COMMENT '薪资等级表';

INSERT INTO salgrade
VALUES
	( 1, 0, 3000 ),
	( 2, 3001, 5000 ),
	( 3, 5001, 8000 ),
	( 4, 8001, 10000 ),
	( 5, 10001, 15000 ),
	( 6, 15001, 20000 ),
	( 7, 20001, 25000 ),
	( 8, 25001, 30000 );

练习

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

1	SELECT e.name,e.age,e.job,d.name as '部门' FROM emp e, dept d WHERE e.dept_id = d.id;

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

1	SELECT e.name,e.age,e.job,d.name as '部门' FROM emp e INNER JOIN dept d ON e.dept_id = d.id && e.age < 30;

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

1	SELECT DISTINCT d.id,d.name as '部门' FROM emp e , dept d WHERE e.dept_id = d.id;

4、查询所有年龄大于40岁的员工及其所属部门名称，如果没有也需要展示出来

1	SELECT e.*,d.name as '部门' FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;

5、查询所有员工的工资等级

1	SELECT e.*,s.grade AS '薪资等级' FROM emp e,salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

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

1	SELECT e.,s.grade FROM (SELECT FROM emp WHERE emp.dept_id = (SELECT id FROM dept WHERE name = '研发部')) e,salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

7、查询’研发部’的平均工资

1	SELECT d.name as '部门',AVG(e.salary) AS '平均工资' FROM emp e,dept d WHERE e.dept_id = d.id and d.name = '研发部';

8、查询工资比’灭绝’高的员工信息

1	SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE emp.name = '灭绝');

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

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

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

1 2	SELECT AVG(e.salary) FROM emp e WHERE e.dept_id = 3; SELECT * FROM emp e2 WHERE salary < (SELECT AVG(e.salary) FROM emp e WHERE e.dept_id = e2.dept_id);

11、查询所有的部门信息，并统计部门的人数

1 2	SELECT COUNT() FROM emp WHERE dept_id = 1; SELECT d.id,d.name,(SELECT COUNT() FROM emp e WHERE e.dept_id = d.id) AS '人数' FROM dept d;

12、查询所有学生的选课情况，展示学生名称、学号、课程名称

1
2
3

-- 所涉及表：student、course、student_course
-- 连接条件：student.id = student_course.studentid、course.id = student_course.courseid
SELECT s.name,s.no,c.name FROM student s,course c,student_course sc WHERE s.id = sc.studentid AND c.id = sc.courseid;