第3.14课 上课 MySQL数据库进阶， 数据库设计

3_14

MySQL数据库进阶

 数据库设计

良好的数据库设计：

1）节省数据的存储空间；

2）能够保证数据的完整性；

3）方便进行数据库应用系统的开发；

  数据建模：实体之间关联关系

   主键,外键

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。

 

主键：

帮助MySQL以最快的速度把一条特定的数据记录找到。

主键必须是唯一的

主键应该是紧凑的，因此整数类型比较适合

 

外键：

引用另外一个数据表的某条记录。

外键列类型尽可能与主键列类型保持一致

外键列应该加上NOT NULL

   主表,从表

1、当主表中没有对应的记录时，不能将记录添加到从表

——成绩表中不能出现在学员信息表中不存在的学号；

 

2、不能更改主表中的值而导致从表中的记录孤立

——把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；

 

3、从表存在与主表对应的记录，不能从主表中删除该行

——不能把有成绩的学员删除了

 

4、删除主表前，先删从表

——先删学员成绩表、后删除学员信息表

 

 

 

   一对一（1:1）

    主键关联

# 一对一主键关联关系

-- 用户表

CREATE TABLE e_user (

id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

t_name varchar(10) DEFAULT NULL,

t_password varchar(50) DEFAULT NULL,

t_salary decimal(10,2) DEFAULT NULL,

t_birthday date DEFAULT NULL,

t_sex char(3) DEFAULT '男'

);

-- 订单表

CREATE TABLE e_order (

id int PRIMARY KEY, # 不需要AUTO_INCREMENT

order_code varchar(20) DEFAULT NULL,

create_date timestamp DEFAULT NOW()

);

-- 添加 一对一主键关联关系

ALTER TABLE e_order ADD CONSTRAINT fk_e_user_e_order FOREIGN KEY (id) REFERENCES e_user (id);

 

-- 先插入主表再插入从表数据

insert into e_user values(1,'lis','123456', 1000.0,'1980-09-22','男');

insert into e_order values(1,'168','2009-09-22');

 

select * from e_user;

select * from e_order;

-- 先删除从表再删除主表数据

DROP TABLE IF EXISTS e_order;

DROP TABLE IF EXISTS e_user;

 

    唯一外键关联

#一对一外键关联关系

-- 用户表

CREATE TABLE e_user (

id int PRIMARY KEY AUTO_INCREMENT,

t_name varchar(10) DEFAULT NULL,

t_password varchar(50) DEFAULT NULL,

t_salary decimal(10,2) DEFAULT NULL,

t_birthday date DEFAULT NULL,

t_sex char(3) DEFAULT '男'

);

-- 订单表

CREATE TABLE e_order (

id int PRIMARY KEY AUTO_INCREMENT,

u_id int NOT NULL UNIQUE, # 外键需要设置为UNIQUE 唯一标识

order_code varchar(20) DEFAULT NULL,

create_date timestamp DEFAULT NOW()

);

 

-- 添加 一对一外键关联关系

ALTER TABLE e_order ADD CONSTRAINT fk_e_user_e_order FOREIGN KEY (u_id) REFERENCES e_user (id);

 

-- 先插入主表再插入从表数据

insert into e_user values(1,'lis','123456', 1000.0,'1980-09-22','男');

insert into e_order values(null,1,'168','2009-09-22');

 

select * from e_user;

select * from e_order;

-- 先删除从表再删除主表数据

DROP TABLE IF EXISTS e_order;

DROP TABLE IF EXISTS e_user;

 

   一对多（1:N）：通过在从表添加外键

#一对多外键关联关系

-- 用户表

CREATE TABLE e_user (

id int PRIMARY KEY AUTO_INCREMENT,

t_name varchar(10) DEFAULT NULL,

t_password varchar(50) DEFAULT NULL,

t_salary decimal(10,2) DEFAULT NULL,

t_birthday date DEFAULT NULL,

t_sex char(3) DEFAULT '男'

);

-- 订单表

CREATE TABLE e_order (

id int(11) PRIMARY KEY AUTO_INCREMENT,

u_id int(11) NOT NULL, # 删除 UNIQUE 唯一标识，就是一对多

order_code varchar(20) DEFAULT NULL,

create_date timestamp DEFAULT NOW()

);

-- 添加 一对多外键关联关系

ALTER TABLE e_order ADD CONSTRAINT fk_e_user_e_order FOREIGN KEY (u_id) REFERENCES e_user (id);

 

-- 先插入主表再插入从表数据

insert into e_user values(1,'lis','123456', 1000.0,'1980-09-22','男');

insert into e_order values(null,1,'169','2009-09-22');

 

select * from e_user;

select * from e_order;

-- 先删除从表再删除主表数据

DROP TABLE IF EXISTS e_order;

DROP TABLE IF EXISTS e_user;

 

   多对多（N:N）：通过中间表作映射

#多对多外键关联关系

 

# 货品表

CREATE TABLE e_goods(

id int PRIMARY KEY AUTO_INCREMENT, #id

title VARCHAR(200), #货品名称

brand_name VARCHAR(200), #品牌名称

price DOUBLE #价格

);

# 订单表

CREATE TABLE e_order (

id int PRIMARY KEY AUTO_INCREMENT,

order_code varchar(20) DEFAULT NULL,

create_date timestamp DEFAULT NOW()

);

 

#订单明细表 通过中间表建立多对多的关联关系

CREATE TABLE e_order_item(

order_id int, #订单编号

goods_id int, #物品编号

order_num int, #购买数量

PRIMARY KEY(order_id, goods_id), # 联合主键

CONSTRAINT FK_ORDER_ITEM_ORDER_ID FOREIGN KEY (order_id) REFERENCES e_order(id),

CONSTRAINT FK_ORDER_ITEM_GOODS_ID FOREIGN KEY (goods_id) REFERENCES e_goods(id)

);

 

insert into e_goods values(1,'手机','苹果',2000);

insert into e_order values(1,'168','2009-09-22');

insert into e_order_item value(1,1,2);

 

select * from e_goods;

select * from e_order;

select * from e_order_item;

 

# 先删除中间表才能够删除主表

DROP TABLE IF EXISTS e_order_item;

DROP TABLE IF EXISTS e_user;

DROP TABLE IF EXISTS e_order;

 

  数据建模实战：E购通电子商务平台

   用户表

   货品类型表

   货品表

   订单表

   订单明细表

   限时抢购表

   图片轮播表