MySQL（二）：MySQL约束和表设计

MySQL约束和表设计

1. 数据库建表的三大范式

第一范式 ： 表的每一列的不可再分。

第二范式 ： 表的每一列都完全依赖主键，不产生局部依赖

第三范式 ： 不产生传递依赖，表中每列都直接依赖于主键

2. 约束概述

约束的作用

​ 为了保证添加到表中的数据有效性，正确性。最好在创建表的时候添加约束，如果表中已经存在数据，再添加约束可能会导致约束添加失败。

mysql约束的种类

约束名	约束关键字
主键约束	primary key
唯一约束	unique
非空约束	not null
默认值	default
外键约束	foreign key (列名) references 主表(列名)

2.1 主键约束

​ 用来唯一的标识表中的每一条记录，主键可以是任意的类型。一般我们使用整数或字符串类型。并且最好使用与业务无关的列作为主键

-- 主键约束
-- 创建表学生表st1, 包括字段(id, name, age)将id做为主键
DROP TABLE IF EXISTS st1;
CREATE TABLE st1 (
  id INT PRIMARY KEY,
  NAME VARCHAR(20),
  age INT
);
-- 插入重复的主键
INSERT INTO st1 VALUES('潘金莲',18);
INSERT INTO st1 VALUES('武大郎',58); -- 报错
-- 删除主键
ALTER TABLE st1 DROP PRIMARY KEY;
-- 添加主键约束
ALTER TABLE st1 ADD PRIMARY KEY (id);
-- 主键自增auto_increment
create table test(num INT AUTO_INCREMENT);
-- 设置起始值
ALTER TABLE st1 AUTO_INCREMENT = 1000;
-- 零填充（必须放在数值类型后面）
CREATE TABLE st2(
   sid INT(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO st2 VALUES (1);  -- sid 00001

2.2 唯一约束

字段名 字段类型 unique

-- 唯一约束 unique
CREATE TABLE st3 (
  id INT PRIMARY KEY,
  `name` VARCHAR(20) UNIQUE -- name是唯一的，不能重复相同的name值
)

2.3 非空约束

字段名 字段类型 NOT NULL

-- 非空约束 not null
CREATE TABLE st4 (
  id INT PRIMARY KEY,
  `name` VARCHAR(20),
  gender CHAR(1) NOT NULL -- gender不能为空
)

2.4 默认值

字段名 字段类型 default

-- 默认值 default
CREATE TABLE st5 (
  id INT DEFAULT '1',
  `name` VARCHAR(20),`day16`
  address VARCHAR(30) DEFAULT '广州' -- 默认值为广州
)
INSERT INTO st5(id,NAME) VALUES(2, '张湾'); -- 2 张湾 广州
INSERT INTO st5 VALUES(NULL, '张洼', NULL); -- 1 张洼 广州

2.5 外键约束

foreign key (列名) references 主表名(列名)

-- 外键约束
-- 创建部门表(id, dep_name, dep_location)
CREATE TABLE department (
   id INT PRIMARY KEY AUTO_INCREMENT,
   dep_name VARCHAR(20),
   dep_location VARCHAR(30)
);
-- 创建员工表(id,name,age,dep_id)
CREATE TABLE employee(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(20), 
   age INT,
   dep_id INT,
   FOREIGN KEY (dep_id) REFERENCES department(id)
);

外键的增删和级联

-- 增加外键
-- 新建表时增加
constraint 约束名 foreign key (列名) references 主表名(列名)
-- 已有表时增加
alter table 表名 add constraint 约束名 foreign key (列名) references 主表名(列名)

-- 删除外键
alter table 名 表名 drop foreign key 外键约束的名字

-- 级联设置（跟在声明外键的后面）
on update cascade 级联更新
on delete cascade 级联删除

3.3 表关系

表与表之间的关系	维护
一对一	外键是唯一的 、从表的外键也是主键
一对多，多对一	通过主外键关系维护
多对多	通过中间表维护

3.4 表的设计

一对一关系案例

-- 一对一关系
-- 建表：先建主表，再建从表
-- 学生表(主表)
CREATE TABLE stu(
id INT PRIMARY KEY, -- 学号
NAME VARCHAR(20) -- 姓名
);
-- 个人信息表，从表
CREATE TABLE info (
id INT PRIMARY KEY, -- 编号
address VARCHAR(50),
use_name VARCHAR(20),
weight DOUBLE,
-- 主键又是外键
FOREIGN KEY (id) REFERENCES stu(id)
);

一对多关系案例

/*
 一对多案例
category分类表，为一方，也就是主表，必须提供主键cid
字段: 编号 分类名
product商品表，为多方，也就是从表，必须提供外键category_id
字段: 编号 商品名 价格 分类号(外键)
*/
-- 创建分类表
CREATE TABLE d_category(
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(50)
);
-- 商品表
CREATE TABLE d_product (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(50),
  price DOUBLE,
  cid INT
);
-- 添加外键字段
ALTER TABLE d_product ADD CONSTRAINT p_product_ibfk_1 
FOREIGN KEY (cid) REFERENCES d_category(cid);
-- 删除主键
ALTER TABLE d_product MODIFY pid INT AUTO_INCREMENT;
ALTER TABLE d_product DROP PRIMARY KEY;
-- 添加主键
ALTER TABLE d_product ADD PRIMARY KEY (pid);
-- 删除外键约束
ALTER TABLE d_product DROP FOREIGN KEY p_product_ibfk_1;

-- 1 向分类表中添加数据
INSERT INTO d_category VALUES(NULL, '食品饮料'),(NULL,'粮油调味'),(NULL,'蔬菜生鲜');
-- 2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO d_product VALUES(NULL,'清扬洗发露',24.90,NULL);
-- 3 向商品表添加普通数据，含有外键信息(数据存在)
INSERT INTO d_product VALUES(NULL,'可口可乐', 3.0, 1);
-- 4 向商品表添加普通数据，含有外键信息(数据不存在) 
INSERT INTO d_product VALUES(NULL, '沐浴露', 0.0, 5); -- 异常
-- 5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM d_category WHERE cid = 2;

多对多关系案例

/* 
   多对多案例
-- 商品和订单多对多关系，将拆分成两个一对多。
-- products商品表，为其中一个一对多的主表，需要提供主键pid
字段:编号 名称 价格 分类编号
-- orders 订单表，为另一个一对多的主表，需要提供主键oid
字段:编号 总价
-- orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid
字段:商品编号 订单编号
*/
-- 商品表 d_product
CREATE TABLE d_product (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(50),
  price DOUBLE,
  cid INT
);
-- 订单表
CREATE TABLE d_order(
   oid INT PRIMARY KEY AUTO_INCREMENT,
   total_price DOUBLE
);
-- 订单项表
CREATE TABLE d_orderitem(
   oid INT,
   pid INT,
   PRIMARY KEY (oid,pid),  -- 联合主键（可省略）
   FOREIGN KEY (oid) REFERENCES d_order(oid),-- 订单表和订单项表的主外键关系
   FOREIGN KEY (pid) REFERENCES d_product(pid) -- 商品表和订单项表的主外键关系
);