25 MySql多表、外键和数据库设计

1 多表

在实际开发中，单表存储项目数据是不现实的，所以一个项目通常都需要很多张表，并且单表进场会存在冗余问题，也就是同一字段出现大量重复数据，这显然就是不合理的，解决方案就是设计成多张表。

下面就以部门表和员工表为例，介绍多表： 创建两张表

create table department(
    id int primary key auto_increment,
    dept_name varchar(20),
    dept_location varchar(20)
);
create table employee(
    eid int primary key auto_increment ,
    ename varchar(20),
    age int,
    dept_id int
);

添加部门和员工数据

insert into department values (1,'研发部','广州'),(2,'销售部','深圳');
insert into employee(ename, age, dept_id) values ('张百万',20,1);
insert into employee(ename, age, dept_id) values ('赵四',21,1);
insert into employee(ename, age, dept_id) values ('广坤',20,1);
insert into employee(ename, age, dept_id) values ('小冰',20,2);
insert into employee(ename, age, dept_id) values ('艳秋',22,2);
insert into employee(ename, age, dept_id) values ('大玲子',18,2);

表关系分析：员工表中有一个字段dept_id与部门表中的主键对应，员工表的这个字段就叫做外键，拥有外键的表就是从表，与外键对应的主键所在表就是主表。

多表存在的问题：还是以上面的例子为例，当我们在员工表中插入一条不存在的部门id的数据，数据依然可以添加成功，这显然是不合理的，因为实际上我们要做保证插入员工表中的dept_id必须在部门表中存在，解决方案就是使用外键约束。

2 外键约束

外键约束可以让两张表之间产生一个对应的关系，从而保证主从表的引用的完整性。添加外键约束的语法格如下： 新建表时添加

[constraint]  [外键约束名] foreign key(外键字段名) references 主表名(主键字段名)

已有表添加

alter table 从表 add [constraint] [外键约束名] foreign key(外键字段名) references 主表名(主键字段名)

实例代码：

alter table employee add constraint dept_employee foreign key (dept_id) references department(id);

这时候添加的外键约束会产生强制性的外键数据检查，从而保证了数据的完整性和一致性，从而在员工中插入一条不存在的部门id的数据就无法插入成功。

删除外键约束的语法格式：

alter table 从表 drop foreign key 外键约束名称;

实例代码：

alter table employee drop foreign key dept_employee;

外键约束的注意事项：

• 从表外键约束类型必须与主表主键类型一致，否则创建失败

• 添加数据时，应该先添加主表中的数据

• 删除数据时，应该先删除从表中的数据

级联删除操作：若果想实现删除主表中的数据的同时，会自动的删除从表中对应的数据，就需要使用级联删除操作，具体语法是：

on delete cascade;

在员工表中中添加级联删除。实例代码：

create table employee(
    eid int primary key auto_increment ,
    ename varchar(20),
    age int,
    dept_id int,constraint dept_employee foreign key (dept_id) references department(id)
    on delete  cascade
);

3 多表关系设计

表与表之间的三种关系：

• 一对一关系：身份证号与个人
• 一对多关系：学生与班级，房子与业主
• 多对多关系：学生与课程，用户与角色

一对多关系(常见)

一对多关系(1:n)，例如班级和学生，部门和员工·，客户和订单。

一对多关系的建表原则：在从表中创建一个字段，字段作为外键指向主表的主键

多对多关系(常见)

多对多关系(m:n)，例如学生和课程，老师和学生等等。

多对多关系的建表原则：需要创建第三张表，第三张表就是中间表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键。

一对一关系(不常见)

一对一关系，在实际开发中不常见，因为一对一其实就是一张表。

一对一的建表原则：主表的主键和从表的外键，形成主外键关系，并且外键是唯一的uniqueq。

4 多表查询

多表查询DQL就是查询多张表，获取到需要的数据。

接下来以分类表和商品表为例，先准备数据，具体代码如下：

#分类表 (一方 主表)
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表 (多方 从表)
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),		#是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  -- 添加外键约束
  FOREIGN KEY (category_id) REFERENCES category (cid)
);

#分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');


#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');

笛卡尔积

交叉连接查询会产生笛卡尔积，所以基本不会使用，具体语法格式：

select 字段名 from 表1, 表2;

使用交叉连接查询会得到很多无用的数据，因此这种用法很少出现在实际开发中。

笛卡尔积：集合1={1,2}，集合2={a,b}，那么集合1与2的笛卡尔积是{{1,a},{1,b},{2,a},{2,b}}

5 多表查询的分类

内连接查询

内连接的特点：通过指定的条件去匹配两张表中的数据，匹配得上就显示，否则不显示

隐式内连接

from子句 后面直接写多个表名，使用where指定连接条件，这种连接方式是隐式内连接(使用where条件过滤无用数据)

语法格式：

select 字段名 from 左表,右表 where 连接条件;

查询所有商品信息和对应的分类信息

select * from category,products where category_id=category.cid;

显式内连接

使用 inner join ...on这种方式，就是显式内连接，具体的语法格式是：

select 字段名 from 左表 	[inner] join 右表 on  条件;
-- inner 可以省略不写

查询所有商品信息和对应的分类信息，对应的显式连接代码是：

select * from products join category c on products.category_id = c.cid;
select p.pname,p.price
from products p join category c on p.category_id = c.cid where p.price>500 and c.cname='鞋服';

外连接查询

外连接查询有左外连接查询和右外连接查询两种。

左外连接查询，使用left outer join，其中outer是可以省略的，左外连接查询的特点是：

• 以左表为基准，匹配右表中的数据，匹配得上就显示
• 匹配不上，左表中的数据正常显示，右边的展示为null

左外连接查询语法格式：

select 字段名 from 左表 left [outer] join 右表 on 条件

左外连接查询每个分类下的商品个数：

select * from category c left join products p on c.cid = p.category_id;
select c.cname as '分类名称',count(p.pid) as '商品个数'from category c left join products p on c.cid = p.category_id group by c.cname;

右外连接查询，使用right outer join，其中outer是可以省略的，右外连接查询的特点是：

• 以右表为基准，匹配左表中的数据，匹配得上就显示
• 匹配不上，右表中的数据正常显示，左边的展示为null

右外连接查询语法格式：

select 字段名 from 左表 right [outer] join 右表 on 条件

右外连接查询每个分类下的商品个数：

select * from products p right join category c on c.cid = p.category_id;

各种连接方式的总结见下图：

• 内连接：inner join，只获取两张表中，交集部分的数据
• 左外连接：left join ，以左表为基准，查询左表中的所有数据，以及与右表有交集的部分
• 右外连接：right join，以右表为基准，查询右表中的所有数据，以及与左表有交集的部分

5 子查询

子查询：一条select查询语句的结果作为另一条select查询语句的条件

特点是：子查询必须放在小括号中；子查询一般作为父查询的查询条件使用

子查询分类：

• where型子查询：将子查询结果作为父查询的比较条件
• from型子查询：将子查询的结果作为一张表，提供给父层查询使用
• exists型子查询：子查询的结果是单列多行，父层查询使用in函数，包含子查询的结果

where型子查询

语法格式：

select 查询字段 from 表 where 字段=(子查询);

查询价格最高的商品信息

select * from products where price=(select max(price) from products);

from型子查询

语法格式：

select 查询字段 from (子查询) 表别名 where 条件;

查询商品中，价格大于500的商品信息，包括商品名称，价格，商品所属分类名称

select p.pname,p.price,c.cname from products p inner join (select * from category) c on p.category_id=c.cid where p.price>500;

注意:当子查询作为一张表的时候，必须起表别名，否则无法访问表中的字段

exists型子查询

语法格式：

select 查询字段 from 表名 where 字段 in (子查询);

查询价格小于两千的商品，来自哪些分类

select * from category where cid in(select distinct category_id from products where price<2000);

子查询总结:

• 子查询结果查出来的结果是一个字段(单列)，那就在where后面作为条件使用
• 子查询结果查出来的是多个字段(多列)，就当作一张表使用

6 数据库设计

设计数据库要遵循三范式的规则，这样就可以建立冗余小、结构合理的数据库，所以设计数据库必须遵循规则。满足最低要求的范式是第一范式(1NF)。在此基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以此类推。一般来说数据库只需要满足第三范式。

第一范式

概念：具有原子性，每一列都不可拆分，第一范式是最基本的范式。数据库表里面的字段都是单一属性的，不可拆分，如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

第二范式

概念：第二范式在第一范式的基础上，增加了一张表只能描述一件事，这样可以确保表中的每列都和主键相关。

第三范式

概念；表的信息，如果能被推导出来，就不应该单独的设计一个字段来存放，这样可以消除传递依赖

当然除了上面提到的数据库三范式，还有数据库反三范式，上面提到的数据库三范式是以时间换空间的做法，简单来说按照数据库三范式设计的数据库非常节省空间，但是每次查询数据的耗时很长，当时在现在实际开发中，更加注重时间，这样可以提高用户体验，也就有了现在的数据库反三范式，虽然有冗余的部分，但是数据库的查询会非常的快，这就是用空间换时间。

举个数据库反三范式的例子，两张表：用户表有id,name,sex，订单表有id,number,price,name，可以看出看来订单表中的name已经在用户表中存在了，这显然是冗余字段，那么为何还要如此设计，试想当我们要查询订单信息，要求有用户姓名，假如订单表中没有name字段的话，我们需要join连接用户表，当表中的数据非常大时，那么连接查询就会耗费非常大的内存消耗，这时添加name这个冗余字段的优势就体现出来了，这时只需要查一张表即可。

**总结：**创建关系型数据库设计，我们有两种选择

• 尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来更加合理，简约。
• 合理的加入冗余字段，可以减少join操作，从而提高数据库的执行性能