MySQL笔记--约束
show databases;
use db_26_homework;
show tables;
select sname, sex
from stu;
create table student
(
sid int,
sname varchar(4) default '无名'
# 默认约束
);
insert into student (sid)
values (2);
# 不写,是 默认值
select *
from student;
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;
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;
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;
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 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'
# 添加失败,现有的记录违反了约束
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);
# 添加外键约束
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步