MYSQL的约束与设计

1. SQL约束
2. 表与表之间的关系
3. 数据库范式

1. SQL约束

SQL 约束用于规定表中的数据规则，对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL约束有以下5种：

1.1 主键约束

主键用来唯一标识数据库中的每一条记录，例如下图两条记录的字段值都是一样的，就需要主键id来给它们做唯一标识来区分。

通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

如：身份证，学号不建议做成主键

（1）创建主键

主键的特点是：非空（not null）、唯一。创建主键后，往表里插入数据后，必须给主键赋值，并且主键值不能与表里的重复。

-- 创建主键有两种方式：
-- 1.在创建表的时候给字段添加主键,格式：字段名 字段类型 PRIMARY KEY
create table test (
 id int primary key, -- id 为主键
 name varchar(20),
 age int
)
-- 2.在已建好的表添加主键，格式：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
alter table test add primary key(id);

（2）删除主键

alter table test drop primary key;

（3）主键设置自增长

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。

create table test (
 id int primary key auto_increment, -- id 为主键,并且自增长
 name varchar(20),
 age int
)

主键设置自增长后，插入数据就无需再插入主键列了。

-- 主键自增后插入数据
insert into test (name,age) values ('tom',18);
insert into test (name,age) values ('cat',20);
-- 另一种写法
insert into test values(null,'peter',35);

（4）修改自增长的默认值起始值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法：

-- 1.创建表时指定起始值,格式 ：
-- CREATE TABLE 表名(
-- 	列名 int primary key AUTO_INCREMENT
-- ) AUTO_INCREMENT=起始值;
-- 指定起始值为 1000
create table test (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;
-- 2.创建好表以后修改起始值
-- 格式：ALTER TABLE 表名 AUTO_INCREMENT=起始值;
-- 将创建好的表的起始值改为2000
alter table test auto_increment = 2000;

1.2 唯一约束

什么是唯一约束：表中某一列不能出现重复的值。

-- 1.创建学生表 test, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
-- 格式：字段名 字段类型 UNIQUE
create table test (
 id int,
 name varchar(20) unique
)
-- 添加一个同名的学生
insert into test values (1, '张三');
insert into test values (2, '张三');-- 报错： Duplicate entry '张三' for key 'name'

1.3 非空约束

什么是非空约束：某一列不能为 null。

-- 1.创建表学生表 test, 包含字段(id,name,gender)其中 name 不能为 NULL
-- 格式：字段名 字段类型 NOT NULL
create table test (
    id int,
    name varchar(20) not null,
    gender char(1)
)
-- 添加一条记录其中姓名不赋值
insert into test values (2,null,'男');-- 报错：Column 'name' cannot be null

默认值

-- 创建一个学生表 test，包含字段(id,name,address)， 地址默认值是广州
create table test2 (
 id int,
 name varchar(20),
 address varchar(20) default '广州'
)
-- 添加一条记录,使用默认地址
insert into test2 values (1, '李四', default);

1.4 外键约束

（1）什么是外键约束？

我们先了解一下外键的概念：

什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id
主表：一方，用来约束别人的表
从表：多方，被别人约束的表

由上图我们可以知道，外键约束就是主表主键对从表外键的约束，当从表插入数据时，外键值必须在主表主键中存在，否则无法插入数据。

（2）创建外键约束

-- 1.新建表时添加外键约束
-- 格式：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
-- 创建主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建从表，添加外键约束
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 插入不存在的部门
-- 报错： Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 18, 6);
-- 2.已有表增加外键：
-- 格式：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
-- 2.1删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
-- 2.2在 employee 表情存在的情况下添加外键
ALTER TABLE employee ADD constraint emp_depid_fk foreign key (dep_id) references department(id)

（3）外键的级联

我们先来看一下添加外键约束后主表的一些操作：

-- 1.要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 2.要删除部门 id 等于 1 的部门, 能不能直接删除呢？
-- 报错：Cannot delete or update a parent
delete from department where id=1;

从上面的代码我们可以知道，在添加外键约束后，如果从表中存在和主表主键值相等的外键，那么这个主键值和外键相等的记录就无法删除和修改，这时就需要级联操作了。

什么是级联操作？即：在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

给从表添加级联更新和删除：

-- 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
 department(id) on update cascade on delete cascade
)

这时我们再对主表进行更新和删除，就没有报错了：

-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id=10 where id=1;
-- 删除部门号是 2 的部门
delete from department where id=2;

2. 表与表之间的关系

2.1 一对一关系

一对一（1:1）在实际的开发中应用不多，因为一对一可以创建成一张表。

两种建表原则：

例如学生表和身份证表：

2.2 一对多关系

一对多（1：n）：例如员工和部门表，一个部门对应多个员工，多个员工对应一个部门。

2.3 多对多关系

多对多（n:n）:例如学生和课程表，一个学生可以选择多门课程，一门课程可以被多个学生选择。

3. 数据库范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

3.1 第一范式（1NF）

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

3.2 第二范式（2NF）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示：

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

3.3 第三范式（3NF）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

posted @ 2020-11-19 09:25 渺渺孤烟起阅读(140) 评论(0) 收藏举报

刷新页面返回顶部