MySQL笔记--约束
/**
约束
*/
show databases;
use db_26_homework;
show tables;
select sname, sex
from stu;
/**
1 默认约束 default(如果没有赋值，设置指定的值)
*/
create table student
(
sid int,
sname varchar(4) default '无名'
# 默认约束
);
insert into student (sid)
values (2);
# 不写，是 默认值
select *
from student;
/**
非空约束 not null (不能为 null)
*/
create table student1
(
sid int,
sname varchar(4) default '无名',
sex char(1) not null
# 非空约束
);
insert into student1 (sid, sname)
values (1, 'he');
# [HY000][1364] Field 'sex' doesn't have a default value
insert into student1
values (3, 'h3h', null);
# [23000][1048] Column 'sex' cannot be null
select *
from student1;
/**
唯一约束 unique (如果值不为 null，值不能重复)
*/
drop table student2;
create table student2
(
sid int,
sname varchar(4) default '无名',
sex char(1) not null,
sorder int unique
# 唯一约束
);
insert into student2 (sid, sex, sorder)
values (1, '男', 1);
# [23000][1062] Duplicate entry '1' for key 'student2.sorder'
# 重复的 key 1
insert into student2 (sid, sex, sorder)
values (1, '男', null);
# 可以为 null，可以为多个 null
/**
检查约束 check ( 对列的取值范围设置 ) ! MsSQL 不支持
*/
/**
主键约束（不能重复，不能为 null，可以被引用)
*/
create table student3
(
sid int primary key,
sname varchar(11)
);
insert into student3 (sid, sname)
values (1, '韩寒');
# [23000][1062] Duplicate entry '1' for key 'student3.PRIMARY'
# 主键约束 不能重复
insert into student3 (sid, sname)
values (null, '张三');
# [23000][1048] Column 'sid' cannot be null
# 主键不能为 null
insert into student3 (sname)
values ('里斯');
# [HY000][1364] Field 'sid' doesn't have a default value
# sid 没有默认值
desc student3;
/**
主键自增约束 auto_increment
特点1 不赋值、值为 null，值自动增长
特点2 自动自增 = 最大值 + 1
*/
create table student4
(
sid float(4, 1) primary key auto_increment,
# 只能用于数字类型的主键列
sname varchar(11)
);
insert into student4 (sname)
values ('张三');
# 不赋值，或者为 null，值自动增长
insert into student4 (sid, sname)
values (11, '里斯');
# 自动增长
insert into student4 (sid, sname)
values (null, '王武');
# 设置 为 null，自动增长
select *
from student4;
/**
创建表后操作约束
添加
*/
create table student5
(
sid int,
sname varchar(11)
);
desc student5;
alter table student5
change sanme sname varchar(11) default '无名';
# 添加默认约束
alter table student5
change sid sid int unique;
# 添加 唯一约束
alter table student5
change sid sid int not null;
# 添加 非空约束
alter table student5
change sid sid int primary key;
# 添加主键约束
alter table student5
change sid sid int auto_increment;
# 添加 自增长约束
alter table student5
modify sid int auto_increment;
# 添加 自增长约束
desc student5;
/**
删除普通约束 not null/default/default
*/
create table student6
(
sid int primary key default,
snmae varchar(11) not null unique,
sex char(1) default '女'
);
desc student6;
alter table student6
change sex sex char(1);
# 删除约束
alter table student6
modify snmae varchar(11);
# 删除约束
desc student6;
insert into student6
values (2, '11', '女');
# [23000][1062] Duplicate entry '11' for key 'student6.snmae'
/**
删除 键约束
*/
desc student6;
show create table student6;
/**
CREATE TABLE `student6` (
`sid` int NOT NULL AUTO_INCREMENT,
`snmae` varchar(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `snmae` (`snmae`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
alter table student6
drop index snmae;
# 删除 唯一约束
desc student6;
alter table student6
add unique key hehe (snmae);
# 给 sname 添加唯一约束 名字为 hehe,作用为：在删除时使用
# 如果添加约束时，没有指定名字，约束名是类名
alter table student6
drop index hehe;
# 删除名为 hehe 的唯一约束
/**
删除主键约束 一个表只能有一个主键
*/
create table student7
(
sid int primary key auto_increment,
sname varchar(11)
);
alter table student7
drop primary key;
# [42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key
# 删除主键约束,需要先删除自增
alter table student7
modify sid int;
# 删除 自增约束
desc student7;
/**
创建表之后，添加主键约束
*/
alter table student7
add primary key heh (sid);
# 设置 主键别名 heh 或者使用 change | modify
desc student7;
show create table student7;
# 查看创建表的语句
/**
CREATE TABLE `student7` (
`sid` int NOT NULL,
`sname` varchar(11) DEFAULT NULL,
PRIMARY KEY (`sid`)
# 可以在最后一行定义主键约束和唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
/**
联合主键 两列共同组成一个主键
成绩表 : 学号+课程号 为联合主键
特点1：两个值都不能为 null，不能同时重复
*/
create table tab_score
(
sid int, # 学号
cid int, # 课程编号
score float(4, 1),
primary key (sid, cid)
);
insert into tab_score (sid, cid, score)
values (2, 1, 100);
/**
删除联合主键
*/
alter table tab_score
drop primary key;
# 没有删除 not null 的约束
/**
创建表后添加 联合主键
*/
desc tab_score;
select *
from tab_score;
delete
from tab_score;
# 清空表
alter table tab_score
add primary key (sid, cid);
# [23000][1062] Duplicate entry '2-1' for key 'tab_score.PRIMARY'
# 添加失败，现有的记录违反了约束
/**
外键约束
a 表中要引用 b 表的记录，可以在 a 表中定义一列作为外键列，来引用 b 表的主键
此时 a 表为从表，b 表为主表
特点1：一个从表中 可以定义多个外键
特点2：可以为 null
特点3：可以重复
特点4：如果外键列有值，次值必须在主键的主键列中存在
特点5：外键列只能引用主表的主键
特带你6：删除主表记录前 需要先删除从表的引用或者删除从表的外键约束
*/
create table teacher
(
tid int primary key,
tname varchar(11)
);
drop table student8;
create table student8
(
sid int primary key,
sname varchar(11),
mytid int,
constraint fk_1 foreign key (mytid) references teacher (tid),
# 外键约束
# constraint
# fk_1 外键约束名，用于删除
# foreign key (mytid) 指定从表中的外键列
# references teacher(tid) 指定主表和主表的主键列
mytid_heard int,
constraint fk_2 foreign key (mytid_heard) references teacher (tid)
);
desc student8;
# MUL 外键
insert into teacher (tid, tname)
values (1, '张老师');
insert into student8 (sid, sname, mytid, mytid_heard)
values (100, '韩寒', null, null);
insert into student8 (sid, sname, mytid, mytid_heard)
values (1, '韩寒', 1, 1);
insert into student8 (sid, sname, mytid, mytid_heard)
values (2, '韩寒', 1, 1);
insert into student8 (sid, sname, mytid, mytid_heard)
values (2, '韩寒', 2, 1);
# [23000][1062] Duplicate entry '2' for key 'student8.PRIMARY'
#
/**
删除外键约束
*/
alter table student8
drop foreign key fk_1;
# 删除外键约束
/**
创建表后添加外键约束
*/
alter table student8
add constraint foreign key fk_1 (mytid) references teacher (tid);
# 添加外键约束