Mysql数据库表的约束与规范化
1.约束种类
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯⼀ | unique |
⾮空 | not null |
外键 | f oreign key |
检查约束 | check 注:mysql不⽀持 |
主键
通常不⽤业务字段作为主键,单独给每张表设计⼀个 id 的字段,把 id 作为主键。主键是给数据库和程序使⽤的,不是给最终的客户使⽤的。所以主键有没有含义没有关系,只要不重复,⾮空就⾏。
如:身份证,学号不建议做成主键。
创建主键
主键关键字:primary key
主键的特点:
- ⾮空 not null
- 唯⼀
创建主键的⽅式:
- 在创建表的时候给字段添加主键
> 字段名 字段类型 PRIMARY KEY
- 在已有表中添加主键
alter table 表名 add primary (字段名)
实操一:
– 创建学生表
creat table st5(
id int primary key,
name varchar(20),
age int);
删除主键
-- 删除st5表的主键
alter table st5 drop primary key;
-- 添加主键
alter table st5 add primary key(id);
主键⾃增
主键如果让我们⾃⼰添加很有可能重复,我们通常希望在每次插⼊新记录时,数据库⾃动⽣成主键字段的值
AUTO_INCREMENT 表示⾃动增⻓(字段类型必须是整数类型)
修改⾃增⻓的默认值起始值
默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使⽤下列 SQL 语法
创建表时指定起始值
创建好以后修改起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
DELETE 和 TRUNCATE 对⾃增⻓的影响
DELETE:删除所有的记录之后,⾃增⻓没有影响。
TRUNCATE:删除以后,⾃增⻓⼜重新开始。
唯⼀约束
字段名 字段类型 UNIQUE
实操:
-- 创建学⽣表 st7, 包含字段(id, name),name 这⼀列设置唯⼀约束, 不能出现同名的学
⽣
create table st7 (
id int,
name varchar(20) unique
);
-- 添加⼀个同名的学⽣
insert into st7 values (1, '张三');
select * from st7;
-- Duplicate entry '张三' for key 'name'
insert into st7 values (2, '张三');
-- 重复插⼊多个 null 会怎样?
insert into st7 values (2, null);
insert into st7 values (3, null);
# null 没有数据,不存在重复的问题
非空约束
字段名 字段类型 NOT NULL
实操:
-- 创建表学⽣表 st8, 包含字段(id, name, gender)其中 name 不能为 NULL
create table st8 (
id int,
name varchar(20) not null,
gender char(1)
);
-- 添加⼀条记录其中姓名不赋值
insert into st8 values (1, '张三疯', '男');
select * from st8;
-- Column 'name' cannot be null
insert into st8 values (2, null, '男');
默认值
字段名 字段类型 default 默认值
实操:
-- 创建⼀个学⽣表 st9, 包含字段(id, name, address), 地址默认值是⼴州
create table st9 (
id int,
name varchar(20),
address varchar(20) default '⼴州'
);
-- 添加⼀条记录, 使⽤默认地址
insert into st9 values (1, '李四', default);
select * from st9;
insert into st9 (id, name) values (2, '李⽩');
-- 添加⼀条记录,不使⽤默认地址
insert into st9 values (3, '李四光', '深圳');
疑问:如果⼀个字段设置了⾮空与唯⼀约束,该字段与主键的区别?
1. 主键数在⼀个表中,只能有⼀个。不能出现多个主键。主键可以单列,也可以是多列。
2. ⾃增⻓只能⽤在主键上。
外键约束
多个表之间的关系
在这里插入图片描述
实操:
-- 1) 删除副表/从表 employee
drop table employee;
-- 2) 创建从表employee并添加外键约束emp_depid_fk
-- 多⽅,从表
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)
);
删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
-- 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);
外键的级联
什么是级联操作:
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
级联操作语法 | 描述 |
---|---|
on update cascade | 级联更新,只能是创建表的时候创建级联关系。更新主表中的主键,从表中的外键列也⾃动同步更新 |
on dalete cascade | 级联删除 |
-- 删除 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
);
-- 再次添加数据到员⼯表和部⻔表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⽼王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⼤王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⼩王', 18, 2);
-- 删除部⻔表? 能不能直接删除?
drop table department;
-- 把部⻔表中id等于1的部⻔改成id等于10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- 删除部⻔号是 2 的部⻔
delete from department where id=2;
数据约束⼩结
约束名 |关键字| 说明
-------- | -----
主键 |primary ke|唯⼀且⾮空
默认| default |如果⼀列没有值,使⽤默认值
⾮空 |not null |这⼀列必须有值
唯⼀| unique |这⼀列不能有重复值
外键 |foreign key |主表中主键列,在从表中外键列
表与表之间的关系⼩结
表与表的关系 | 关系的维护 |
---|---|
⼀对多 | 主外键的关系 |
多对多 | 中间表,两个⼀对多 |
⼀对⼀ | 1)特殊⼀对多,从表中的外键设置为唯⼀ 2)从表中的主键⼜是外键 |
数据规范化
什么是范式
好的数据库设计对数据的存储性能和后期的程序开发,都会产⽣重要的影响。建⽴科学的,规范的数据库就需要满⾜⼀些规则来优化数据的设计和存储,这些规则就称为范式。
三⼤范式
⽬前关系数据库有六种范式:第⼀范式(1NF)、第⼆范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、 第四范式(4NF)和第五范式(5NF,⼜称完美范式)。
满⾜最低要求的范式是第⼀范式(1NF)。在第⼀范式的基础上进⼀步满⾜更多规范要求的称为第⼆范式(2NF), 其余范式以次类推。⼀般说来,数据库只需满⾜第三范式(3NF)就⾏了。
三⼤范式⼩结
范式 | 特点 |
---|---|
1NF | 原⼦性:表中每列不可再拆分。 |
2NF | 不产⽣局部依赖,⼀张表只描述⼀件事情。 |
3NF | 不产⽣传递依赖,表中每⼀列都直接依赖于主键。⽽不是通过其它列间接依赖于主键。 |