目录
个人主页:东洛的克莱斯韦克-CSDN博客
主键
主键:primary key用来唯一的约束该字段里面的数据,不能重复,不能为空(必须有非空约束),一张表中最多只能有一个 主键;主键所在的列通常是整数类型。
实例
create table t(
id int not null primary key comment '学生id',
name varchar(10) not null comment '学生姓名'
);
主键约束:主键对应的字段中不能重复,一旦重复,操作失败。如下操作
insert into t values(1, '张三');
insert into t values(1, '李四');
当表创建好以后但是没有主键的时候,可以再次追加主键,追加主键的字段必须有非空约束,否则会报错。
alter table 表名 add primary key(字段列表)
删除主键
alter table 表名 drop primary key;
复合主键
在创建表的时候,在所有字段之后,使用primary key(主键字段列表)来创建主键,如果有多个字段 作为主键,可以使用复合主键。
create table tt14(
id int unsigned,
course char(10) comment '课程代码',
score tinyint unsigned default 60 comment '成绩',
primary key(id, course) -- id和course为复合主键
);
自增长
auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值 +1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。
自增长的特点:
任何一个字段要做自增长,前提是本身是一个主键
自增长字段必须是整数
一张表最多只能有一个自增长
实例
create table t2
(
id int primary key auto_increment,
name varchar(10) not null
);
只插入name字段的值
insert into t2(name) values('a');
insert into t2(name) values('b');
insert into t2(name) values('c');
结果如下
查看表详细信息
show create table t2 \G
其中AUTO_INCREMENT的值就是下一次要填充的值
在插入后获取上次插入的 AUTO_INCREMENT 的值(批量插入获取的是第一个值)
select last_insert_id();
唯一键 unique
一张表中有往往有很多字段需要唯一性,数据不能重复,但是一张表中只能有一个主键,
唯一键 unique 就可以 解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多,唯一键允许为空,而且可以多个为空,空字段不做唯一性比较。
关于唯一键和主键的区别: 我们可以简单理解成,主键更多的是标识唯一性的。而唯一键更多的是保证在业务上,不要和别的信息 出现重复。
主键和唯一键并不是对立的,而是相辅相成的。
一般而言,我们建议将主键设计成为和当前业务无关的字段,这样,当业务调整的时候,我们可以尽量不会对 主键做过大的调整。
场景
学生信息:id 年龄 性别 身份证号 手机号 家庭住址
约束条件
id:可以设置成主键,用于快速索引,自增 |
年龄:非空约束 |
性别:非空约束,默认值 '未知' |
身份证号:唯一键,非空约束,身份证号一定时唯一的 |
手机号:非空约束 |
家庭住址:非空约束,如果不精确到门牌号的话就不用设为唯一键 |
create table student(
id tinyint unsigned primary key auto_increment comment '用于表示学生的唯一性',
age tinyint unsigned not null comment '学生的年龄',
gender char(2) not null default '未知' comment '学生的性别',
identity_card varchar(20) not null unique comment '学生的身份证号',
phone char(11) not null comment '手机号',
address varchar(50) not null comment '家庭住址'
);
外键
外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,主表则必须是有主键约束或unique 约束。当定义外键后,要求外键列数据必须在主表的主键列存在或为null。
用法
foreign key (字段名) references 主表(列)
场景
学生信息:学生id ,姓名 ,班级id
班级信息:班级id , 班级名称,人数。
方案一
如果我们把班级信息和学生信息揉到一张表中,班级名称字段和人数字段会大量重复。
方案二
那么分别用两张表存储,不加外键约束。这样虽然我们认为这两张表是有关系的,但MySQL服务并不认为这两张表有关系。换句话说,MySQL服务不会拦截一些在逻辑上不和发的操作。
create table class(
id tinyint unsigned not null,
name varchar(40) not null ,
size tinyint unsigned );
create table student(
id tinyint unsigned not null,
name varchar(40) not null ,
class_id tinyint unsigned not null
);
表结构如下
班级表和学生表之间没有加外键约束。
先向班级表插入数据
insert into class values(1,'软件工程1班', 45),(2, '软件工程2班', 50);
结果如下
这表明此时该学校只有这两个班级
再向学生表中插入数据
insert into student values(1,'张三', 1),(2, '李四', 1),(3, '王五', 2);
结果如下
如果此时再向学生表插入 赵六,但他的班级id是3,这显然是不合法的,因为班级表中只有id为1 和 2 的两个班级,但由于没有外键的约束,此时依旧可以插入成功。
那如果我要在班级表中删掉id为1的班级依旧是不合法的,因为id为1的班级此时还有学生,但由于没有外键的约束,依旧可以删除成功
此时我们大概可以理解什么是外键约束了,两张表在业务上是有相关性的,但是在业务上没有建立约束关系,那么就可能出现问题。
解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了,提前告诉mysql 表之间的约束关系,那么当用户插入不符合业务逻辑的数据的时候,mysql不允许你插入。
方案三
那方案三自然是有外键约束的效果咯~
create table class_3(
id tinyint unsigned primary key,
name varchar(40) not null ,
size tinyint unsigned );
create table student_3(
id tinyint unsigned not null,
name varchar(40) not null ,
class_id tinyint unsigned not null,
foreign key (class_id) references class_3(id)
);
表结构如下
依旧是先向班级表中插入数据再向学生表中插入数据
insert into class_3 values(1,'软件工程1班', 45),(2, '软件工程2班', 50);
insert into student_3 values(1,'张三', 1),(2, '李四', 1),(3, '王五', 2);
如果此时再向学生表插入 赵六,但他的班级id是3。那么能插入成功吗?
明显是失败的,报错如下
Cannot add or update a child row: a foreign key constraint fails (`db1`.`student_3`, CONSTRAINT `student_3_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_3` (`id`))
那删除班级表id为1的班级呢?
也失败了,报错如下
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`student_3`, CONSTRAINT `student_3_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_3` (`id`))
所以,这就是外键的作用。