文章目录
1. 约束
- 为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:
- 实体完整性(Entity Integrity) :例如,同一个表中,不能存在两条完全相同无法区分的记录。
- 域完整性(Domain Integrity) :例如:年龄范围0-120,性别范围“男/女”。
- 引用完整性(Referential Integrity) :例如:员工所在部门,在部门表中要能找到这个部门。
- 用户自定义完整性(User-defined Integrity) :例如:用户名唯一、密码不能为空等,本部门经理的工资不得高于本部门职工的平均工资的5倍。
- 根据约束起的作用,可分为:
- NOT NULL 非空约束,规定某个字段不能为空
- UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- DEFAULT 默认值约束
- 列级约束和表级约束区别:表级约束多了 CONSTRAINT constraint_name
- 基本语句:
- 在创建表时规定约束:CREATE TABLE…
- 在表创建后添加约束:ALTER TABLE …
- 查看某个表已有的约束:
SELECT * FROM information_schema.table_constraints WHERE table_name = ‘table name’;- 查看表索引:SHOW INDEX FROM tablename;
2. 非空约束 NOT NULL
- 限定某个字段/某列的值不允许为空:NOT NULL。
- 所有的类型的值都可以是NULL
- 非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
- 一个表可以有很多列都分别限定了非空
- 空字符串’'不等于NULL,0也不等于NULL
- 修改ALTER TABLE 字段约束时,如果要保留字段原有的非空约束,在加其他约束时,需要保留NOT NULL,否则非空约束就会被删除。
# 建表时非空约束
CREATE TABLE emp (
id INT(10) NOT NULL,
name VARCHAR(20) NOT NULL,
sex CHAR NULL
);
# 建表后非空约束
ALTER TABLE emp
MODIFY sex VARCHAR(20) NOT NULL;
ALTER TABLE emp
MODIFY name VARCHAR(15) DEFAULT 'abc' NOT NULL;
# 删除非空约束
ALTER TABLE emp
MODIFY sex VARCHAR(20) NULL;
3. 唯一性约束 UNIQUE
- 用来限制某个字段/某列的值不能重复:UNIQUE。
- UNIQUE KEY 等同于 UNIQUE.
- 单列用法UNIQUE(),组合用法UNIQUE(item, item2, …)。
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一。
- 唯一性约束允许列值为空。
- 如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
- MySQL会给唯一约束的列上默认自动创建一个唯一索引。
- 删除唯一约束只能通过删除唯一索引的方式删除。
- 删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
# 建表时唯一性约束
CREATE TABLE stu3 (
sid INT NOT NULL UNIQUE KEY,
cardid VARCHAR(20) UNIQUE,
phone INT UNIQUE KEY,
name VARCHAR(25),
password VARCHAR(16),
address VARCHAR(18),
age INT,
childs INT,
UNIQUE KEY(name, password),
UNIQUE(address, age),
CONSTRAINT stu3_constraint UNIQUE(sid, cardid)
);
-- UNIQUE KEY 等同于 UNIQUE
-- NOT NULL && UNIQUE 等同于 PRIMARY KEY
-- CONSTRAINT ... UNIQUE() 表级约束
CREATE TABLE stu2 (
sid INT NOT NULL,
name VARCHAR(25) DEFAULT 'abc',
password VARCHAR(16),
cardid VARCHAR(20) UNIQUE,
phone INT UNIQUE KEY,
address VARCHAR(18),
age INT,
childs INT,
CONSTRAINT stu2_name_pwd UNIQUE(name, password)
);
DESC stu2;
DROP TABLE stu2;
# 建表后唯一性约束
ALTER TABLE 表名称 ADD UNIQUE KEY (字段列表);
# sid 既非NULL又唯一时会变为主键PrimaryKey
ALTER TABLE stu2 ADD UNIQUE KEY(sid);
-- sid key : PRI
ALTER TABLE stu2 ADD UNIQUE KEY(address, age, childs);
ALTER TABLE stu2 ADD CONSTRAINT stu2_age_childs UNIQUE(address, age, childs);
# 也可以直接改变字段属性
ALTER TABLE stu2 MODIFY sid INT NOT NULL UNIQUE;
-- sid key: PRI
ALTER TABLE stu2 MODIFY sid INT UNIQUE;
-- sid key: UNI
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'stu2';
# 删除唯一性约束
ALTER TABLE stu2 DROP INDEX stu2_age_childs;
4. 主键约束 PRIMARY KEY
- 用来唯一标识表中的一行记录:PRIMARY 。
- 主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值。
- 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
- 主键约束对应着表中的一列或者多列(复合主键)
- 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
- MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
- 当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高)。
- 如果删除主键约束了,主键约束对应的索引就自动删除了。
- 不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
- 删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在。
# 列级约束PRIMARY KEY
CREATE TABLE temp1(
id INT PRIMARY KEY,
name VARCHAR(20)
);
# 列级约束组合
CREATE TABLE temp2 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY(id, name)
);
# 表级约束
CREATE TABLE temp2 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT temp2_id_pk PRIMARY KEY(id)
);
# 表级约束组合
CREATE TABLE temp3 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT temp3_id_pk PRIMARY KEY(id, name)
);
-- id 和 name 均为非NULL且PRI
# 建表后 PRIMARY KEY
ALTER TABLE temp ADD PRIMARY KEY(id);
ALTER TABLE temp ADD PRIMARY KEY(id, name);
# 删除主键约束,不需要指定主键名
ALTER TABLE temp DROP PRIMARY KEY;
5. 自增约束 AUTO_INCREMENT
- 某个字段的值自增:AUTO_INCREMENT。
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时,可设置自增长
- 自增长列约束的列必须是键列(主键列PRIMARY KEY 或 唯一键列UNIQUE KEY)
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 null,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接赋值为具体值。
- 自增显示在Extra项
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(字段名)
);
-- 不建议只写KEY,虽然默认key = primary key
CREATE TABLE auto(
id INT KEY AUTO_INCREMENT,
name VARCHAR(20)
);
CREATE TABLE auto2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
CREATE TABLE auto3(
id INT UNIQUE KEY AUTO_INCREMENT,
name VARCHAR(20) PRIMARY KEY
);
# 建表后增加自增
ALTER TABLE auto3
MODIFY id INT AUTO_INCREMENT;
# 去掉auto_increment相当于删除
ALTER TABLE auto3
MODIFY id INT;
6. 外键约束FOREIGN KEY
- 限定某个表的某个字段的引用完整性:FOREIGN KEY。 比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分。
- 主表(父表):被引用的表,被参考的表。从表(子表):引用别人的表,参考别人的表。例如:学生表、课程表、选课表:选课表的学生和课程要分别参考学生表和课程表,学生表和课程表是主表,选课表是从表。
- 从表的外键列,必须参考主表的主键或唯一约束的列。因为被依赖/被参考的值必须是唯一的。
- 在创建外键约束时,可以指定外键约束名。如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如student_ibfk_1)。
- 创建表时就指定外键约束的话,先创建主表,再创建从表
- 删表时,先删从表(或先删除外键约束),再删除主表
- 当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据
- 在从表中指定外键约束,并且一个表可以建立多个外键约束
- 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。
- 当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。(根据外键查询效率很高)
- 删除外键约束后,必须手动删除对应的索引。
- 两个表之间有关系,但不一定必须要外键约束。
- 外键约束需要消耗系统资源,对于大并发的 SQL 操作,可能会因为外键约束的系统开销而变得非常慢。建议通过应用层面完成检查数据一致性的逻辑。
- 阿里开发规范:不得使用外键与级联,一切外键概念必须在应用层解决。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) references 主表名(被参考字段)
);
CREATE TABLE depx(
did INT PRIMARY KEY,
dname VARCHAR(20)
);
CREATE TABLE empx(
eid INT PRIMARY KEY,
ename VARCHAR(20),
depxid INT,
FOREIGN KEY (depxid) REFERENCES depx(did)
);
-- depxid KEY: MUL
DESC depx;
DESC empx;
- 建表后添加外键约束
ALTER TABLE 从表名
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];
ALTER TABLE empx
ADD FOREIGN KEY (depxid) REFERENCES depx(did);
- 删除外键约束
# (1)先查看从表约束名,删除外键约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'empx';
# 删除外键
ALTER TABLE empx
DROP FOREIGN KEY empx_ibfk_1;
#(2)查看索引名和删除索引
SHOW INDEX FROM empx;
# 删除索引
ALTER TABLE empx
DROP INDEX depxid;
7. 默认值约束 DEFAULT
- 给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值:DEFAULT。
- 默认值约束一般不在唯一键和主键列上加。
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
create table employee(
eid INT primary key,
salary DOUBLE(10,2) DEFAULT 2000
ename varchar(20) not null,
gender char default '男',
tel char(11) not null DEFAULT '' #默认是空字符串
);
alter table 表名称 modify 字段名 数据类型 default 默认值;
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
ALTER TABLE empx
MODIFY eid INT DEFAULT 0;
ALTER TABLE empx
MODIFY ename VARCHAR(20) DEFAULT 'van' NOT NULL;
ALTER TABLE empx
MODIFY eid INT;
#删除默认值约束,也不保留非空约束
alter table 表名称 modify 字段名 数据类型 ;
#删除默认值约束,保留非空约束
alter table 表名称 modify 字段名 数据类型 not null;
8. 小结
面试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支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。所以说,存储引擎的选择也不完全是随意的。
9. 参考代码
-- 非空约束 --
SELECT DATABASE();
CREATE TABLE emp (
id INT(10) NOT NULL,
name VARCHAR(20) NOT NULL,
sex CHAR NULL
);
DESC emp;
ALTER TABLE emp
MODIFY sex VARCHAR(20) NOT NULL;
ALTER TABLE emp
MODIFY sex VARCHAR(20) NULL;
ALTER TABLE emp
MODIFY name VARCHAR(15) DEFAULT 'abc' NOT NULL;
-- 唯一性约束 --
DROP TABLE stu2;
CREATE TABLE stu2 (
sid INT NOT NULL,
name VARCHAR(25) DEFAULT 'abc',
password VARCHAR(16),
cardid VARCHAR(20) UNIQUE,
phone INT UNIQUE KEY,
address VARCHAR(18),
age INT,
childs INT,
CONSTRAINT stu2_name_pwd UNIQUE(name, password)
);
-- name key: MUL
-- cardid key: UNI
-- phone key: UNI
DESC stu2;
# sid 既非NULL又唯一时会变为主键PrimaryKey
ALTER TABLE stu2 ADD UNIQUE KEY(sid);
-- sid key: PRI
ALTER TABLE stu2 MODIFY sid INT NOT NULL UNIQUE;
ALTER TABLE stu2 MODIFY sid INT UNIQUE;
-- UNI
ALTER TABLE stu2 ADD UNIQUE KEY(address, age, childs);
-- address key: MUL
ALTER TABLE stu2 ADD CONSTRAINT stu2_age_childs UNIQUE(address, age, childs);
-- address key: UNI
-- ALTER TABLE stu2 MODIFY cardid, password VARCHAR(20) UNIQUE;
-- 不能这么用
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'stu2';
ALTER TABLE stu2
DROP INDEX stu2_age_childs;
DROP TABLE stu3;
CREATE TABLE stu3 (
sid INT UNIQUE KEY,
cardid VARCHAR(20) UNIQUE,
phone INT UNIQUE KEY,
name VARCHAR(25),
password VARCHAR(16),
address VARCHAR(18),
age INT,
childs INT,
UNIQUE KEY(name, password),
UNIQUE(address, age),
CONSTRAINT stu3_constraint UNIQUE(sid, cardid)
);
-- stu : UNI
-- cardid : UNI
-- phone : UNI
-- name : MUL
DESC stu3;
ALTER TABLE stu3
ADD UNIQUE KEY(sid);
-- stu : UNI
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'stu3';
-- 表索引
SHOW INDEX FROM stu3;
ALTER TABLE stu3
DROP INDEX sid_3;
# PRIMARY KEY
CREATE TABLE temp(
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- Key: PRI
-- NULL: NO
DESC temp;
CREATE TABLE temp2 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT temp2_id_pk PRIMARY KEY(id)
);
-- Key: PRI
-- NULL: NO
DESC temp2;
DROP TABLE temp2;
CREATE TABLE temp3 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT temp3_id_pk PRIMARY KEY(id, name)
);
DESC temp3;
DROP TABLE temp3;
CREATE TABLE temp4 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY(id, name)
);
DESC temp4;
DROP TABLE temp4;
ALTER TABLE temp
ADD PRIMARY KEY(id);
ALTER TABLE temp
ADD PRIMARY KEY(id, name);
-- 复核主键,两个字段都标记为PRI
ALTER TABLE temp
DROP PRIMARY KEY;
# 自增
CREATE TABLE auto(
id INT KEY AUTO_INCREMENT,
name VARCHAR(20)
);
DESC auto;
-- 默认为primary key,即key = primary key
CREATE TABLE auto2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
DESC auto2;
CREATE TABLE auto3(
id INT UNIQUE KEY AUTO_INCREMENT,
name VARCHAR(20) PRIMARY KEY
);
-- 自增显示在Extra项
DESC auto3;
ALTER TABLE auto3
MODIFY id INT auto_increment;
-- modify了auto,KEY还是uni
ALTER TABLE auto3
MODIFY id INT;
# 外键 FOREIGN
CREATE TABLE depx(
did INT PRIMARY KEY,
dname VARCHAR(20)
);
CREATE TABLE empx(
eid INT PRIMARY KEY,
ename VARCHAR(20),
depxid INT,
FOREIGN KEY (depxid) REFERENCES depx(did)
);
-- depxid KEY: MUL
DESC depx;
DESC empx;
ALTER TABLE empx
ADD FOREIGN KEY (depxid) REFERENCES depx(did);
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'empx';
ALTER TABLE empx
DROP FOREIGN KEY empx_ibfk_2;
SHOW INDEX FROM empx;
ALTER TABLE empx
DROP INDEX depxid;
ALTER TABLE empx
MODIFY eid INT DEFAULT 0;
ALTER TABLE empx
MODIFY ename VARCHAR(20) DEFAULT 'van' NOT NULL;
ALTER TABLE empx
MODIFY eid INT;