MySQL之——约束

 约束

在我们学习创建表的时候会看到定义字段后面有一个约束字段的语句，我们这节就要来学习这个"约束"，要学习的约束一共有五种，他们分别是：

constraint

约束类型	非空约束	默认约束	唯一约束	主键约束	外键约束
关键字	not null	default	unique	primary key	foreign key

非空约束(not null)

        根据字面意思可知，不能为空，null值就是没有值或者缺少值，非空就是不可以在插入时不给出该字段的值，也就是该字段必须有值。

• 创建时设置非空约束

create table 表名(
字段名 字段类型 not null
);

create table student(
name varchar(32) not null,
age int
);

insert into table (age) values (20);

#报错，因为name设置的是非空约束

• 已有字段添加非空约束

alter table 表名
modify column 字段名 字段类型 not null；

alter student 
modify column class int not null;

• 删除非空约束

alter table 表名
modify column 字段名 字段类型；

NULL和空串不同，NULL是没有值，空串是" "（两个引号中间没有字符）

默认约束(default)

默认约束就是给相应字段一个默认值

create table 表名(
字段名 字段类型 default 默认值，
...
);

drop table if exists id
create table id(
id not null default 666,
name varchar(20)
);
#不报错
insert into users(name) values("Sam");

删除默认约束

alter table 表名 alter column 列名 drop default;

唯一约束(unique)

        用于确保特定列或者列组合的唯一性，被约束的列的值在整个表中是唯一的，唯一约束默认允许空值（null），因此多个空值不违反唯一约束。

create table users(
id int not null default 666,
name varchar(20) unique
);
insert into users(id,name) values(1,"San");
insert into users(id,name) values(2,"San");#报错 name有唯一约束
insert into users(id) values(1);
insert into users(id) values(3);

• 表级，可以给约束起名，可以创建多列的唯一约束（联合唯一约束）

create table users(
    id int not null default 666,
    name varchar(20),
    [constrain 约束名] unique(id,name)
);
insert into users(id,name) values(1,'Li');
insert into users(id,name) values(2,'Li');#不报错，联合唯一约束里的行需要全都不一样才能报错
insert into users(id,name) values(1,'Li');#报错
insert into users(name) values('Li');
insert into users(name) values('Li');#报错，id默认666

• 已有字段添加唯一约束

alter table 表名 modify 字段名 字段类型 unique;

alter table 表名 add [constraint 约束名] unique(字段名)

• 删除唯一约束

alter table 表名
drop index 约束名;

alter table 表名
drop key 约束名;

主键约束(primary)

        唯一标识表中每行的这个列(或者这组列)称为主键。主键用来表示一个特定的行。没有主键的话更新或删除表中特定的行很困难，因为没有安全的方法保证只涉及相关的行。

因此

• 每一个表都应定义主键
• 主键的值不能修改
• 不使用可能会修改值的列作为主键（即与业务无关，通常用id作为主键）

特点

• 唯一性：主键要求每一行数据的主键值都是唯一的，不能有重复值
• 非空性：主键要求主键列的值不能为空，即不能为NULL
• 单一性：每个表只能有一个主键，主键可以由一个列或者多个列组成，形成复合主键

列级

create table users(
id int primary key,
name varchar(20) unique
)
insert into users(id,name) values(1,'Z');
insert into users(id,name) values(1,'X');#报错 主键唯一
insert into users(name) values('Z');#报错 主键非空
insert into users(id) values(2);#不报错 唯一约束可以空

表级，可以给约束起名，可以创建多列的联合主键

create table 表名(
字段1 字段类型，
字段2 字段类型，
...
[constraint 约束名] primary key(字段1[，字段2..])
)；

删除主键

alter drop 表名
drop primary key;

自动递增(auto_increment)

        auto_increment:设置auto_increment的列，当每增加一行时自动增量，每个表只允许一个auto_increment列

create table users(
id int primary key auto_increment,
name varchar(20) unique
)

例如从1开始，插入了三个则id会变成3，如果第四个插入的id设置为6，则下一个自增从6开始

外键约束(foreign)

        外键为表中的某一字段，该字段是另一表的主键值，外键用来在两个表的数据之间建立链接，一个表中可以有一个或多个外键。外键的作用是保持数据的一致性，完整性。

注意：

• 外键字段可以为null，外键为空的数据也叫孤儿数据
• 有了外键引用之后，表分为父表和子表
  • 创建表时先创建父表在创建子表
  • 插入数据时，先插入父表数据再插入子表数据
  • 删除时先删除子表，在删除附表
• 子表外键类型要和父表外键类型一致

[constraint 外键名] foreign key (列名) references 父表名(主键);

create table student(
id int primary key auto_increment,
name varchar(20) unique
);

create table score(
id int primary key auto_increment,
student_id int,
degree int,
constraint fk1 foreign key (student_id) reference student(id)
);

接下来我们来完成一个练习

根据下面提供的表结构和表中数据，使用 SQL 语句创建四个表并插入数据：学生表（student）、课程表（course）、成绩表（score）、教师表（teacher）。

student (学生表) 

属性名	数据类型	可否为空	含 义
id	Int	否	学号（主键）
name	Varchar(20)	否	姓名
sex	Char(2)	否	性别
birthday	Date	可	出生日期
class	Char(5)	可	所在班级

course（课程表）

属性名	数据类型	可否为空	含 义
id	Char(5)	否	ID（主键）
name	Varchar(20	否	名称
teacher_id	Int	否	教工编号（外键）

score(成绩表)

属性名	数据类型	可否为空	含 义
id	Int	否	ID（自增主键）
student_id	Int	否	学生ID（外键）
course_id	Char(5)	否	课程ID（外键）
degree	Decimal(4,1)	可	成绩

teacher(教师表)

属性名	数据类型	可否为空	含 义
id	Int	否	ID（主键）
name	Varchar(20)	否	姓名
sex	Char(2)	否	性别
birthday	Date	可	出生日期
prof	Varchar(20)	可	职称
depart	Varchar(10)	否	教工所在部门

表中数据

student

id	name	sex	birthday	class
108	曾华	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆君	男	1974-06-03	95031

course

id	name	teacher_id
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	831

score

id	student_id	course_id	degree
1	103	3-245	86
2	105	3-245	75
3	109	3-245	68
4	103	3-105	92
5	105	3-105	88
6	109	3-105	76
7	101	3-105	64
8	107	3-105	91
9	108	3-105	78
10	101	6-166	85
11	107	6-166	79
12	108	6-166	81

teacher

id	name	sex	birthday	prof	depart
804	李诚	男	1958-12-02	副教授	计算机系
856	张旭	男	1969-03-12	讲师	电子工程系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系

答案：

#学生
create table if not exists student(
id int not null primary key,
name varchar(20) not null,
sex char(2) not null,
birthday date,
class char(5) 
);
#老师
create table if not exists teacher(
id int not null primary key,
name varchar(20) not null,
sex char(2) not null,
birthday date,
prof varchar(20),
depart varchar(10) not null
);
#课程
create table if not exists course(
id char(5) not null primary key,
name varchar(20) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(id)
);
#分数
create table if not exists score(
id int not null primary key auto_increment,
student_id int not null,
course_id char(5) not null,
degree decimal(4,1),
foreign key(student_id) references student(id),
foreign key(course_id) references course(id)
);


insert into student(id,name,sex,birthday,class) values
(108,"曾华","男","1977-09-01","95033"),
(105,"匡明","男","1975-10-02","95031"),
(107,"王丽","女","1976-01-23","95033"),
(101,"李军","男","1976-02-20","95033"),
(109,"王芳","女","1975-02-10","95031"),
(103,"陆君","男","1975-06-03","95031");

insert into teacher(id,name,sex,birthday,prof,depart) values
(804,"李诚","男","1958-12-02","副教授","计算机系"),
(856,"李诚","男","1969-03-12","讲师","电子工程系"),
(825,"王萍","女","1972-05-05","助教","计算机系"),
(831,"刘冰","女","1977-08-14","助教","电子工程系");

insert into course(id,name,teacher_id) values
("3-105","计算机导论",825),
("3-245","操作系统",804),
("6-166","数字电路",856),
("9-888","高等数学",831);

insert into score(student_id,course_id,degree) values
(103,"3-245",86),
(105,"3-245",75),
(109,"3-245",68),
(103,"3-105",92),
(105,"3-105",88),
(109,"3-105",76),
(101,"3-105",64),
(107,"3-105",91),
(108,"3-105",78),
(101,"6-166",85),
(107,"6-166",79),
(108,"6-166",81);