MySQL基础学习2: 多表的设计以及表之间的关系

多表设计以及之间的关系

系统设计中，实体之间的关系(数据库表)有三种:一对一，一对多，多对多.
实例：把网上商城里面用的实体创建成表,并且将他们之间建立关系.

一对多

举例：用户和订单

一个用户可以有多个订单

--用户表
CREATE TABLE `user`(
	id INT PRIMARY KEY auto_increment,
	username VARCHAR(20)
);
--订单表
CREATE TABLE orders(
	id INT PRIMARY KEY auto_increment,
	totalprice DOUBLE,
	user_id INT
);

如何处理一对多

在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.

ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES user(id);

添加了外键约束之后有如下特点:
1.主表中不能删除从表中已引用的数据
2.从表中不能添加主表中不存在的数据
作用：保持数据库中表的完整性和一致性

多对多

举例: 订单和商品

一个订单可以有多个商品，一个商品也可以对应多个订单

--商品表
create table products(
	id int primary key auto_increment,
	name varchar(20),
	price double
);

-- 创建中间表
create table order_product(
	oid int,
	pid int,
	PRIMARY KEY(oid,pid)
);

如何处理多对多

一般新建一张中间表,这个中间表只放双方的id,然后就拆成了两个一对多的关系.然后一般将两个id设成联合主键,然后对每个id添加外键约束

alter table order_product add foreign key (oid) references orders(id);
alter table order_product add foreign key (pid) references products(id);

注意这里不是在某一方的表中添加外键了，而是新建一个表，分别将订单表和商品表的主键作为外键。

一对一

举例: 人和身份证号

每个人对一个身份证号，同时一个身份证号也只能唯一对应那个人。

--person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(20) -- 姓名
)
--idcard表
CREATE TABLE idcard(
	id INT PRIMARY KEY AUTO_INCREMENT,
	num VARCHAR(20) -- 身份证号
)

如何处理一对一

1.将一张表的主键设置外键，如在idcard中设置外键：

alter table idcard add foreign key (id) references person(id);

2.两张表合二为一,例如,在person表中加入idcard的字段:

--person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(20), -- 姓名
    num VARCHAR(20) -- 身份证号
)

总结:
对于一对一关系，我们在表中描述时，可以在任意一方描述
对于一对多关系，我们在表中描述时，在多的一方描述
对于多对多关系，我们在表中描述时，会产生一个中间表

参考：传智播客Java就业班