MySQL学习笔记 - 数据库表约束

最新推荐文章于 2022-07-17 12:08:21 发布

Proud_Jiang

最新推荐文章于 2022-07-17 12:08:21 发布

阅读量305

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/u013177658/article/details/95410876

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1 数据库约束的概念

1.1 约束的作用：

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

1.2 约束种类：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check 注：mysql 不支持

2 主键约束

2.1 主键的作用

用来唯一标识数据库中的每一条记录
在这里插入图片描述

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

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

2.3 创建主键

主键关键字： primary key
主键的特点：非空 not null ; 唯一

创建主键方式：

在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
 id int primary key, -- id 为主键
 name varchar(20),
 age int
)

desc st5;

在这里插入图片描述

-- 插入重复的主键值
insert into st5 values (1, '关羽', 30);

insert into st5 values (1, '关云长', 20); -- 错误代码： 1062 Duplicate entry '1' for key 'PRIMARY'

select * from st5;

-- 插入 NULL 的主键值,
insert into st5 values (null, '关云长', 20);  -- 错误代码： 1048 Column 'id' cannot be null

2.4 删除主键

-- 删除 st5 表的主键
alter table st5 drop primary key;

-- 添加主键
alter table st5 add primary key(id);

2.5 主键自增

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

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

-- 插入数据
insert into st6 (name,age) values ('小乔',18);
insert into st6 (name,age) values ('大乔',20);

-- 另一种写法
insert into st6 values(null,'周瑜',35);

select * from st6;

在这里插入图片描述

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

默认地 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, '刘备');

在这里插入图片描述

2.7 DELETE 和 TRUNCATE 对自增长的影响

DELETE：删除所有的记录之后，自增长没有影响。
TRUNCATE：删除以后，自增长又重新开始。

3 唯一约束

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

3.1 唯一约束的基本格式

字段名 字段类型 UNIQUE

3.2 实现唯一约束

-- 创建学生表 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 没有数据，不存在重复的问题

4 非空约束

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

4.1 非空约束的基本语法格式

字段名 字段类型 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.2 默认值

什么是默认值：

字段名 字段类型 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, '李四光', '深圳');

疑问: 如果一个字段设置了非空与唯一约束，该字段与主键的区别?

主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
自增长只能用在主键上

5 外键约束

5.1 单表的缺点

创建一个员工表包含如下列(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, '销售部', '深圳');

以上数据表的缺点:

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

5.2 解决方案:

在这里插入图片描述

-- 解决方案：分成 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
解决方式： 使用外键约束

5.3 什么是外键约束

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

5.4 创建外键约束的方法

方法一：新建表种添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

方法二：已存在表中添加外键

ALTER TABLE 从表 ADD [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) -- 添加外键约束
);

在这里插入图片描述

5.5 删除外键

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

5.6 外键的级联

出现新的问题：

select * from employee;
select * from department;

-- 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;

-- 要删除部门 id 等于 1 的部门, 能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

什么是级联操作

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

级联操作语法	描述
ON UPDATE CASCADE	级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新
ON DELETE 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;

5.6 数据约束小结

约束名	关键字	说明
主键	primary key	唯一；非空；
默认	default	如果一列没有值，使用默认值
非空	not null	这一列必须有值
唯一	unique	这一列不能有重复值
外键	foreign key	主表中主键列，在从表中外键列

6 表与表之间的关系

6.1 表关系的概念

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

6.2 一对多

一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

6.3 多对多

多对多（m:n）例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的
主键。

6.4 一对一

一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则：
一对一原则 | 说明
— | —
外键唯一 | 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键 | 主表的主键和从表的主键，形成主外键关系

6.4 一对多案例

一个旅游线路分类中有多个旅游线路
表与表的关系
具体操作

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
create table tab_category (
 cid int primary key auto_increment,
 cname varchar(100) not null unique
)
-- 添加旅游线路分类数据：
insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');


-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
 rid int primary key auto_increment,
 rname varchar(100) not null unique,
 price double,
 rdate date,
 cid int,
 foreign key (cid) references tab_category(cid)
)
-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499, '2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23', 2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
select * from tab_route;

6.5 多对多案例

旅游线路与用户收藏的关系，一个用户可以收藏多个线路，一个线路可以被多个用户收藏。
对于多对多的关系我们需要增加一张中间表来维护他们之间的关系
具体操作

-- 用户表
/*
创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
create table tab_user (
	uid int primary key auto_increment,
	username varchar(100) unique not null,
	password varchar(30) not null,
	name varchar(100),
	birthday date,
	sex char(1) default '男',
	telephone varchar(11),
	email varchar(100)
)

-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');

/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
	rid int,
	date datetime,
	uid int,
	-- 创建复合主键
	primary key(rid,uid),
	foreign key (rid) references tab_route(rid),
	foreign key(uid) references tab_user(uid)
)

-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼

Proud_Jiang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记 - 数据库表约束

1 数据库约束的概念1.1 约束的作用：对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。1.2 约束种类：约束名约束关键字主键primary key唯一unique非空not null外键foreign key检查约束check 注：mysql ...
复制链接

扫一扫