目录
一. 约束概念(对表中字段的限制)
利用约束实现数据完整性(数据可靠性,精确性)
完整性分四种
- 实体完整性-- > 同一个表中,不能存在两条完全相同无法区分的记录
- 域完整性 -- > 年龄范围0-120,性别范围“男/女”
- 引用完整性 -- > 员工所在部门,在部门表中要能找到这个部门
- 用户自定义完整性 -- > 用户名唯一、密码不能为空
1.1 约束分类
1.1.1 按字段的个数分类
- 单列约束
- 对列约束
1.1.2 按作用范围
- 列级约束
- 表级约束 :在所有字段后声明的
1.1.3 按约束的作用(功能)
- not null(非空约束)
- unique (唯一性约束)
- primary key (主键约束)
- foreign key (外键约束)
- check (检查约束)
- default (默认值约束)
1.2 添加约束
可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后通过 ALTER TABLE 语句规定 约束。
1.3 查看约束
select * from information_scheme.table_constraints where table_name = '表名';
二. 非空约束
作用 : 限定某列不可以为空
特点:
- 默认,所有的类型的值都可以是NULL,包括INT、FLOAT等数据类型
- 非空约束只能用于单独列上
- 一个表可以有很多列都分别限定了非空
- 空字符串''不等于NULL,0也不等于NULL
2.1 添加非空约束
#建表前添加
CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);
#建表后添加
alter table 表名称 modify 字段名 数据类型 not null;
alter table student modify sid int not null;
2.2 删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null,
相当于修改某个非注解字段,该字段允 许为空
或者
alter table 表名称 modify 字段名 数据类型;#去掉not null,
相当于修改某个非注解字段,该字段允许为空
例子:
ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;
三. unique 唯一性约束
作用: 用来限制某个字段/某列的值不能重复。
特点:
- 同一个表可以有多个唯一约束
- 唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一。
- 可以多此添加null值。
- 在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同。
- MySQL会给唯一约束的列上默认创建一个唯一索引。
3.1 添加唯一性约束
#建表前
方式一:
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key, 字段名 数据类型
);
方式二
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名) );
#建表后
方式一
alter table 表名称 add unique key(字段列表);
方式二
alter table 表名称 modify 字段名 字段类型 unique;
注意: 字段列表中如果是一个字段,表示该列的值唯一。如果是两个或更多个字段,那么复合唯一,即多个字段的组合是唯 一的
3.2 删除唯一性约束
- 添加唯一性约束的列上也会自动创建唯一索引。
- 删除唯一约束只能通过删除唯一索引的方式删除。
- 删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
- 如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和() 中排在第一个的列名相同。也可以自定义唯一性约束名。
ALTER TABLE USER DROP INDEX uk_name_pwd; 注意:可以通过 show index from 表名称; 查看表的索引
四. 主键约束
作用: 用来唯一标识表中的一行记录
特点: 主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值
- 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
- 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
- MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。--> 没必要起名字
- 当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询 的,就根据主键查询,效率更高)。如果删除主键约束了,主键约束对应的索引就自动删除了。
- 需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
4.1 添加主键约束
#建表时,列级约束
create table temp(
id int primary key,
name varchar(20) );
#建表时,表级约束
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式 );
#建表后
ALTER TABLE 表名称
ADD PRIMARY KEY(字段列表);
#字段列表可以是一个字段,也可以是多个字段,如果是多 个字段的话,是复合主键
4.2 删除约束
alter table 表名称 drop primary key;
说明:删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在。注意: 在实际开发中,不要删
五. 自增列 : auto_increment
作用: 某个字段的值自增
特点:
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时,可设置自增长
- 自增长列约束的列必须是键列(主键列,唯一键列)
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 null,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接 赋值为具体值
- 开发中,当我们向主键添加自增约束,就不要给主键字段赋值
- 通常结合主键使用
5.1 指定自增约束
# 建表时
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key, 字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值, primary key(字段名) );
# 建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;
5.2 删除自增约束
#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束 alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
5.3 MySQL 8.0新特性—自增变量的持久化
六. foreign key 约束
作用:限定某个表的某个字段的引用完整性。
比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分。特点:
6.1 主表和从表/父表和子表
特点
6.2 添加外键约束
#建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型 );
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致,逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样,也可以不一样
#建表后
一般情况下,表与表的关联都是提前设计好了的,因此,会在创建表的时候就把外键约束定义好。不
过,如果需要修改表的设计(比如添加新的字段,增加新的关联关系),但没有预先定义外键约束,那
么,就要用修改表的方式来补充定义。
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];
例子:
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
6.3 删除外键约束
流程: 先删约束,在删索引
(1)第一步先查看约束名和删除外键约束
#查看某个 表的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
(2)第二步查看索引名和删除索引。(注意,只能手动删除)
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
6.4 约束等级
6.5 补充知识
开发场景
在 MySQL 里,外键约束是有成本的,需要消耗系统资源。对于大并发的 SQL 操作,有可能会不适合。比如大型网站的中央数据库,可能会 因为外键约束的系统开销而变得非常慢 。所以, MySQL 允许你不使用系统自带的外键约束,在 应用层面 完成检查数据一致性的逻辑。也就是说,即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性。
阿里开发规范
七. check 约束
作用: 检查某个字段的值是否符号xx要求,一般指的是值的范围
说明:MySQL 5.7 不支持8.0支持
age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’)) CHECK(height>=0 AND height<3)
八. default 约束
作用: 给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值。
8.1 添加默认值
# 建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值);
说明:默认值约束一般不在唯一键和主键列上加
#建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束,你还保留非空约束,那么在加默认值约束时,还得保留非空约束,否则非空约束就被 删除了
#同理,在给某个字段加非空约束也一样,如果这个字段原来有默认值约束,你想保留,也要在modify语句中保留默 认值约束,否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
8.2 删除默认值约束
#删除默认值约束,也不保留非空约束
alter table 表名称 modify 字段名 数据类型 ;
#删除默认值约束,保留非空约束
alter table 表名称 modify 字段名 数据类型 not null;
九. 面试
面试 1 、为什么建表时,加 not null default '' 或 default 0答:不想让表中出现 null 值。面试 2 、为什么不想要 null 的值答 : (1)不好比较。 null 是一种特殊值,比较时只能用专门的 is null 和 is not null 来比较。碰到运算符,通 常返回null 。(2)效率不高。影响提高索引效果。因此,我们往往在建表时 not null default '' 或 default 0面试 3 、带 AUTO_INCREMENT 约束的字段值是从 1 开始的吗?在 MySQL 中,默认 AUTO_INCREMENT 的初始值是 1 ,每新增一条记录,字段值自动加 1 。设置自增属性(AUTO_INCREMENT)的时候,还可以指定第 一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一 条记录,同时指定id 值为 5 ,则以后插入的记录的 id 值就会从 6 开始往上增加。添加主键约束时,往往需要设置字段自动增加属性面试 4 、并不是每个表都可以任意选择存储引擎?外键约束(FOREIGN KEY)不能跨引擎使用。MySQL 支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。所以说,存储引擎的选择也不完全是随意的。
十. 综合练习
10.1 练习一
CREATE DATABASE test04_emp;
USE test04_emp;
CREATE TABLE emp2( id INT, emp_name VARCHAR(15) );
CREATE TABLE dept2( id INT, dept_name VARCHAR(15) );
SHOW TABLES;
#1.向表emp2的id列中添加PRIMARY KEY约束
方式一
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
方式二
ALTER TABLE emp2 ADD PRIMARY KEY(id);
#2. 向表dept2的id列中添加PRIMARY KEY约束
方式一
ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY;
方式二
ALTER TABLE dept2 ADD PRIMARY KEY(id);
#3. 向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束,与之相关联的列是dept2表中的id列
ALTER TABLE emp2 ADD dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_id FOREIGN KEY(dept_id)REFERENCES dept2(id);
10.2 练习二
# 3、使用ALTER语句给books按如下要求增加相应的约束
ALTER TABLE books ADD PRIMARY KEY(id);
ALTER TABLE books MODIFY id INT AUTO_INCREMENT;
ALTER TABLE books MODIFY NAME VARCHAR(50) NOT NULL;
ALTER TABLE books MODIFY AUTHORS VARCHAR(100) NOT NULL;
ALTER TABLE books MODIFY price FLOAT NOT NULL;
ALTER TABLE books MODIFY pubdate DATE NOT NULL;
ALTER TABLE books MODIFY num INT(11)NOT NULL;
10.3 练习三
CREATE DATABASE test04_company;
USE test04_company;
CREATE TABLE offices(
officeCode INT(10) PRIMARY KEY,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) ,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15),
CONSTRAINT uk_off_poscode UNIQUE(postalCode)
);
DESC offices;
CREATE TABLE employees(
employeeNumber INT(11) PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25),
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT uk_emp_mob UNIQUE(mobile),
CONSTRAINT fk_emp_offc FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
);
DESC employees;
CREATE DATABASE test04_company;
USE test04_company
CREATE TABLE offices(
officeCode INT(10) PRIMARY KEY,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) ,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15),
CONSTRAINT uk_off_poscode UNIQUE(postalCode)
);
DESC offices;
CREATE TABLE employees(
employeeNumber INT(11) PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25),
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT uk_emp_mob UNIQUE(mobile),
CONSTRAINT fk_emp_offc FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
);
DESC employees;
#3. 将表employees的mobile字段修改到officeCode字段后面
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
#4. 将表employees的birth字段改名为employee_birth
ALTER TABLE employees CHANGE birth employee_birth DATETIME;
#5. 修改sex字段,数据类型为CHAR(1),非空约束
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
#6. 删除字段note
ALTER TABLE employees DROP COLUMN note;
#7. 增加字段名favoriate_activity,数据类型为VARCHAR(100)
ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
#8. 将表employees名称修改为employees_info
RENAME TABLE employees TO employees_info;