MySQL--基础回顾 (四) 约束和表与表之间的关系

数据库表的约束

数据库约束的概述

约束的作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束种类

1、主键约束

主键的作用

用来唯一标识数据库中的每一条记录

哪个字段应该作为表的主键？

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

创建主键

主键关键字：

　　　　primary key

主键的特点：

• 非空 not null
• 唯一

创建主键方式：

1.在创建表的时候给字段添加主键
　　字段名 字段类型 PRIMARY KEY

2.在已有表中添加主键
　　ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除主键

-- 删除 st5 表的主键
alter table st5 drop primary key;
-- 添加主键
alter table st5 add primary key(id);

主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

-- 插入数据
insert into st6 (name,age) values ('小乔',18);
insert into st6 (name,age) values ('大乔',20);
-- 另一种写法
insert into st6 values(null,'周瑜',35);
13 / 26
select * from st6;

修改自增长的默认值起始值

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

• 创建表时指定起始值

　　　　CREATE TABLE 表名(
　　　　　　列名 int primary key AUTO_INCREMENT
　　　　) AUTO_INCREMENT=起始值;

• -- 指定起始值为 1000
  create table st4 (
  　　id int primary key auto_increment,
         name varchar(20)
  ) auto_increment = 1000;
         insert into st4 values (null, '孔明');
         select * from st4;

• 创建好以后修改起始值

ALTER TABLE 表名 AUTO_INCREMENT=起始值;
alter table st4 auto_increment = 2000;
insert into st4 values (null, '刘备');

DELETE 和 TRUNCATE 对自增长的影响

　　DELETE：删除所有的记录之后，自增长没有影响。

　　TRUNCATE：删除以后，自增长又重新开始。

2、唯一约束

什么是唯一约束?

　　表中某一列不能出现重复的值

唯一约束的基本格式

　　字段名 字段类型 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 没有数据，不存在重复的问题

3、非空约束

什么是非空约束：

　　某一列不能为 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,'男');

4、默认值

什么是默认值：

　　字段名 字段类型 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. 自增长只能用在主键上

5、外键约束

单表的缺点

　　创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

以上数据表的缺点:

•  数据冗余
•  后期还会出现增删改的问题

解决方案：

-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
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);
select * from employee;

　　当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id

目标: 需要约束 dep_id 只能是 department 表中已经存在 id

解决方式: 使用外键约束

什么是外键约束

什么是外键：

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

创建约束的语法

新建表时增加外键：
　　[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表增加外键：
　　ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
键字段名);

具体操作：

-- 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)
)
-- 3) 正常添加数据
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);
select * from employee;
-- 4) 部门错误的数据添加失败
-- 插入不存在的部门
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);

删除外键

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);

外键的级联

什么是级联操作？

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

-- 删除 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;

数据约束小结

表与表之间的关系

表关系的概念

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！

一对多

一对多(1:n) 例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则:

在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多

多对多(m:n)

　　例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则:

　　需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

一对一

一对一(1:1)

　　在实际的开发中应用不多.因为一对一可以创建成一张表。

两种建表原则：

表与表之间的关系小结