学习目标：

Mysql多表设计

三范式

多表查询（Very important）

一、mysql多表设计

1、表与表之间可以总结出如下关系：

 

 

2、一对多

【案例】

表设计：记录部门中的员工信息

需求分析：

员工表和部门表

分析实体类的属性

分析实体类之间的关系

分析外键如何设置

 

步骤一：建表

在创建的数据库中建表

create table dept(

         id int primary key auto_increment,

         name varchar(20)

);

create table employee(

         id int primary key auto_increment,

         name varchar(20),

         age int ,

         salary double

);

步骤二：添加数据

/*给部门表添加数据*/

insert into dept values(null,'人事部');

insert into dept values(null,'财务部');

insert into dept values(null,'公关部');

insert into dept values(null,'总经理办公室');

/*给雇员表添加数据*/

insert into employee values(null,'小乔',18,10000);

insert into employee values(null,'大乔',19,10000);

insert into employee values(null,'曹操',20,12000);

insert into employee values(null,'周瑜',21,13000);

insert into employee values(null,'刘备',22,14000);

步骤三：添加关系

分析：部门表和员工表目前都是独立的存在，无法知道这些员工是属于哪个部门，因此员工表内的信息还存在着一些缺陷。

解决方案：可以在员工表中添加一列部门编号用来标识设置该员工所属的部门，如下：

通过上图分析，可以在多方表中添加一列来设置一对多的关系。

/*employee表中添加一个dept_id列*/

alter table employee add dept_id int;

update employee set dept_id =1 where id = 1;

update employee set dept_id =1 where id = 2;

update employee set dept_id =2 where id = 3;

update employee set dept_id =3 where id = 4;

update employee set dept_id =4 where id = 5;

3.外键约束

3.1、建表之后添加外键约束

语法 :  alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)

【示例】

给现成的dept表和employee表添加外键约束。

添加外键约束

报错如下：

 

原因分析

人事部已经在dept表中被删除，因此无法使用人事部的主键作为employee中的外键。

 

解决方案：

1、删除从表中的数据（删除大小乔）

2、修改数据（将大小乔的dept_id修改为dept表中存在的主键）

3、在dept表中添加主键为1的人事部。

以上任选其一即可。

这里使用第三种方案，重新插入一条人事部的记录，主键必须为1。

测试：再次删除人事部看其效果。

 

3.2、建表的时候添加外键约束

/*创建部门表*/

create table dept(

         id int primary key auto_increment,

         name varchar(20)

);

/*创建员工表*/

create table employee(

         id int primary key auto_increment,

         name varchar(20),

         age int ,

         salary double,

         dept_id int,

         foreign key (dept_id) references dept(id)

);

4、多对多

表设计

建表语句：

-- 创建程序员表

create table coder (

         id int primary key auto_increment,

         name varchar(30),

         salary double

);

-- 创建项目表

create table project(

         id int primary key auto_increment,

         name varchar(100)

);

-- 创建中间表

create table coder_project(

         coder_id int,

         project_id int,

         foreign key (coder_id) references coder(id),

         foreign key (project_id) references project(id)   

);

 

drop table coder_project;

drop table coder;

drop table project;

5、一对一

表设计：

二、三范式

1 什么是范式

范式是指: 设计数据库表的规则(Normal Form)​ 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储.

2 范式的基本分类

​ 我们一起来学习一下关系数据库的三种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）, 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

3 第一范式

​ 即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。第一范式：每一列不能再拆分

总结：如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）。

4 第二范式

​ 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分。​ 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

第二范式：

1. 一张表只描述一件事情
2. 表中的每一个字段都依赖于主键

总结：如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相同数据。

5 第三范式

​ 在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。

第三范式：从表的外键必须使用主表的主键

总结：如果不准守第三范式，可能会有相同数据无法区分，修改数据的时候多张表都需要修改（不方便修改）。遵守第三范式通过 id 可以区分相同数据，修改数据的时候只需要修改一张表（方便修改）。

6、多表设计案例：

业务需求：

1、一个学生可以选择多门课程

2、一个课程可以有多个学生学习

3、每个学生的每门课程必须有成绩

4、一个老师可以教多门课程

5、一门课程只有一个老师来教

 

create table student(

         id int primary key auto_increment,

         name varchar(20),

         city varchar(10),

         age int

);

create table teacher(

         id int primary key auto_increment,

         name varchar(20)

);

create table course(

         id int primary key auto_increment,

         name varchar(20),

         teacher_id int,

         foreign key (teacher_id) references teacher(id)

);

 

create table student_course(

         student_id int,

         course_id int,

         score int,

         foreign key (student_id) references student(id),

         foreign key (course_id) references course(id)

);

三、多表查询

实际的工作中，我们所需要的数据，通常会来自多张表。那么就涉及到多表关联查询的问题。

也就是指，如何使用sql语句一次性查询多张表的数据。

【示例】

数据准备：

有两张表，一张是水果表A,一张是价格表B

建表：

create table A(

  id int primary key auto_increment,

  name varchar(20) not null

);

insert into A values(1,'苹果');

insert into A values(2,'橘子');

insert into A values(3,'香蕉');

 

create table B(

   id int primary key auto_increment,

   price double

);

insert into B values(1,2.30);

insert into B values(2,3.50);

insert into B values(4,null);

1、迪卡尔积

Select  *   from   a,b;

2、内连接查询

- 多表联合查询为了不出理笛卡尔积、那就要添加条件、（隐式内连接）

-隐式内连接：

Select  *  from  a,b  where  a.id = b.id;

-显示内连接：inner  join   on   表示过滤条件

Select  *  from  a  inner  join   b  on  a.id = b.id;

3、外连接

（1）-左外连接

格式：left  outer  join ..  on ..

Select  *  from   a  left  outer  join  b  on  a.id = b.id;

（2）-右外连接

格式：right  outer  join .. on ..

Select  * from a right outer join  b  on a.id = b.id;

（3）-全外连接

格式：full  outer  join  .. on .. (不支持、用union代替)

Select  * from  a  left  outer join b  on a.id = b.id

Union

Select *  from a  right  outer  join b on  a.id = b.id;

多表查询总结：

4、关联子查询

关联子查询：将一个查询作为另一个查询的一部分（条件）

（1）、子查询：

需求：查询年龄最大的学生的信息.

-- 1. 从 student 表中查询年龄最大的学生信息

select max(age) from student;  -- 25

-- 2. 根据上一条查询语句执行的结果, 再次从 student 表中查询最大年龄的学生信息

-- 语法 : 关联子查询, 将一条 select 语句的执行作为另一条 select 语句的条件使用.

-- 注意 : 关联子查询语句一定要放在小括号中执行.

select * from student where age = (select max(age) from student);

（2）、in的用法:

in();在一个范围

-- 需求：查询分数不及格的所有的学生信息

-- 1. 从中间表中查询成绩不及格的学生编号

select student_id from student_course where score < 60;

-- 2. 根据查询的结果, 从 student 表中查询对应的学生信息

-- Subquery returns more than 1 row 子查询返回了多条语句

select * from student where id = (select student_id from student_course where score < 60);  错误, 多条语句不可以使用 = 符号连接

-- 说明 : 多个结果可以使用 in 实现包装.

select * from student where id in (select student_id from student_course where score < 60);

(3)、all的用法

All等于那个最大值

-- all 的用法 :

-- 1. 查询所有的年龄信息

select age from student;

-- 2. 查询年龄中最大学生信息

-- >= all  大于等于所有 (等于那个最大的)

select * from student where age >= all(select age from student);   -- max(age) 聚合函数

（4）any 和 some

any : 表示任何一个

-- 查询 `成绩是90` 的学生的信息

-- 1. 从中间表中查询成绩为 90 的学生编号

select student_id from student_course where score = 90;

some： 表示任何一个和any的作用相同

some 是 SQL-92标准的ANY的等效物.

-- 查询 `成绩是90` 的学生的信息

-- 2. 根据结果从学生表中查询学生信息

-- =any / =some 等于任意一个都成立.  

-- any 疑问句和否定句   some 肯定句

select * from student where id = any(select student_id from student_course where score = 90);

select * from student where id = some(select student_id from student_course where score = 90);

(5)、as的用法（重点）

as:不仅可以用来做列的别名，还可以将查询结果通过as作为一张 `临时表` 来使用。

需求 : 查询不及格的学生信息和不及格分数

-- 1. 从 `student_course` 表中查询不及格的信息

-- 1. 从中间表中查询不及格的信息

select * from student_course where score < 60;

上图的结果 as temp;

说明 : 如果进行多表联合查询, 结果表必须作为一张 `临时表` 才可以使用. 使用 as 关键字实现.,

-- 多表联合查询的语句 :  

-- 1. 从中间表中查询不及格的信息

select * from student_course where score < 60;  -- 查询出来的结果被称为 `结果表`

-- 2. 将结果表和学生表进行多表关联查询

-- 格式 : select * from a, b where 条件;

-- 语句 : 如何使用结果表进行多表关联查询 ??? 必须将结果表作为一张 `临时表` 来使用. 

-- 使用 : 通过 as 关键字将结果表作为临时表.  (结果表) as 临时表

-- 说明 : 多表关联查询中, * 表示多张表中的所有列. 如果需要指定列, 需要在 * 之前添加表名.

select student.*, temp.score from student, (select * from student_course where score < 60) as temp

    where student.id = temp.student_id;

如果多表中没有相同的列名, 可以省略表的名称.

select student.*, temp.score from student, (select * from student_course where score < 60) as temp

    where id = student_id;

 

(6)、limit的用法

作用：限制查询结果返回的数量

格式：select * from 表名 LIMIT [offset], row_count;

-- limit 限制返回的结果数目

select * from student;

-- limit 值;   (表示数量)

select * from student limit 5;

-- limit 值1, 值2;  (值1 -> 表示偏移量, 值2 -> 表示数量)

select * from student limit 2, 5;  -- 分页查询

 

总结图：