主键
什么是主键:唯一标识表中的一个或一组称为主键。
主键的特性:不能重复和不能为空,一个表只能有一个主键,自动增长肯定是主键。
主键常与自动增长配合使用,自动增长从1开始,每次递增1,这样值就不会有重复,适合用来生成唯一的id。
创建主键的方法(表名student):
1.create table student(id int auto_increment primary key,name varchar(20));
2.create table student(id int auto_increment,name varchar(20),primary key(id));
3.(创建组合键)create table student(classname varchar(20),stuname varchar(20),primary key(classname,stuname));
在创建完表之后添加主键(student):
alter table student add primary key(id);
使用alter 添加自动增长(student):
alter table student modifty id int auto_increment primary key;
删除主键(表名student):
alter table student drop primary key;
查看主键方式(表名student):
desc student;(查看表的结构)
show create table student;
唯一键
唯一键特性:不能重复,可以为空,一个表可以有多个唯一键,unique 和unique key 实现效果一样
添加唯一键(表名student):
1.create table student(id int auto_increment primary key,name varchar(20) unique);
2.create table student(id int primary key,name varchar(20),unique (name));
通过修改表进行设置唯一键(表名student,字段是name):
1.alter table student add unique(name);
2.(组合键,name和unique设置为唯一键)alter table student add unique(name),add unique(addr);
删除唯一键(表名student):
alter table student drop index name;
外键
什么是外键:从表中的公共字段
外键有三种操作:一.严格限制二.置空操作三.级联操作
三种键的特性:只有Innodb才支持外键,公共字段可以不同名但是类型一定要一样
1.严格模式:(1). 主表中没有的记录,从表不允许插入(2). 从表中有的记录,主表中不允许删除(3). 删除主表前,先删子表
2.置空操作:如果主表记录删除,或关联字段更新,则从表外键字段被设置为null
3.级联操作:如果主表记录删除,则从表记录也被删除。主表更新,从表外键字段也更新。
置空、级联操作中外键不能是从表的主键
创建表的时候添加外键:
创建主表: create table studentinfo(id int primary key,name varchar(20))engine=innodb;
创建从表:create table studentscore(sid int primary key,score tinyint,foreign key(sid) references studentinfo(id))engine=innodb;(没有指定外键名称,系统默认)
当主表没有的关联字段从表不能插入:
mysql> insert into studentscore values(1,99);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`studentscore`, CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `studentinfo` (`id`))
mysql> insert into studentinfo values(1,'libai');
Query OK, 1 row affected (0.01 sec)
当主表插入成功之后从表可以插入对应的关联字段:
mysql> insert into studentinfo values(1,'libai');
Query OK, 1 row affected (0.01 sec)
mysql> insert into studentscore values(1,99);
Query OK, 1 row affected (0.00 sec)
查看从表外键的名称以便删除:
mysql> show create table studentscore;
| studentscore | CREATE TABLE `studentscore` (
`sid` int(11) NOT NULL,
`score` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`sid`),
CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `studentinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
查找到外键名称为:studentscore_ibfk_1;
通过名称删除外键:
alter table studentscore drop foreign key `studentscore_ibfk_1`;(注意!!!里面的是反引号不是单引号)
通过修改表的方式添加外键:alter table studentscore add foreign key(sid) references studentinfo(id);
下面设置外键的触发限制为置空(主表删除或者更新某个字段,从表将把对应关联字段设置为null)
创建主表的名称为studentinfo1:
create table studentinfo1(id int auto_increment primary key,name varchar(20))engine=innodb;
创建从表的名称为studentscore1:
create table studentscore1(sid int,score tinyint)engine=innodb;
往从表中添加外键名称为sid_id:
alter table studentscore1 add constraint sid_id foreign key(sid) references studentinfo1(id) on delete set null on update set null;(此处从表的关联字段不要设置为主键,因为主键不能为空和重复)
往主表和从表插入数据:
insert into studentinfo1 values(1,'libai'),(2,'dupu'),(3,'baijuyi');
insert into studentscore1 values(1,70),(2,65),(3,50);
查看表的数据:
select * from studentinfo1;
+----+---------+
| id | name |
+----+---------+
| 1 | libai |
| 2 | dupu |
| 3 | baijuyi |
+----+---------+
select * from studentscore1;
+------+-------+
| sid | score |
+------+-------+
| 1 | 70 |
| 2 | 65 |
| 3 | 50 |
+------+-------+
删除主表的id为1一列数据:
delete from studentinfo1 where id=1;
查看从表:
+------+-------+
| sid | score |
+------+-------+
| NULL | 70 |
| 2 | 65 |
| 3 | 50 |
+------+-------+
更新主表id=2的数据:
update studentinfo1 set id=4 where id=2;
查看从表:
+------+-------+
| sid | score |
+------+-------+
| NULL | 70 |
| NULL | 65 |
| 3 | 50 |
+------+-------+
删除从表sid=3的数据:
delete from studentscore1 where sid=3;
查看主表:
+----+---------+
| id | name |
+----+---------+
| 3 | baijuyi |
| 4 | dupu |
+----+---------+
发现从表删除数据对主表没有任何影响
级联操作:
创建主表的名称为studentinfo2:
create table studentinfo2(id int auto_increment primary key,name varchar(20))engine=innodb;
创建从表的名称为studentscore2:
create table studentscore2(sid int,score tinyint)engine=innodb;
插入三条数据:
insert into studentinfo2 values(1,'libai'),(2,'dupu'),(3,'baijuyi');
insert into studentscore2 values(1,70),(2,65),(3,50);
显示如下:
+----+---------+
| id | name |
+----+---------+
| 1 | libai |
| 2 | dupu |
| 3 | baijuyi |
+----+---------+
+------+-------+
| sid | score |
+------+-------+
| 1 | 70 |
| 2 | 65 |
| 3 | 50 |
+------+-------+
设置置空外键:alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete set null on update set null;
再次设置默认外键:alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id);报错,需要把外键删除后在重新设置触发限制
删除名为form_name外键:alter table studentscore2 drop foreign key form_name;
添加触发限制为级联的外键:
alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete cascade on update cascade;
删除主表的id=1的记录:
delete from studentinfo2 where id=1;
查找从表: select * from studentscore2;
+------+-------+
| sid | score |
+------+-------+
| 2 | 65 |
| 3 | 50 |
+------+-------+
发现从表关联字段那一条记录被删除
主表把id=2的记录中id改为id=5
update studentinfo2 set id=5 where id=2;
查看从表: select * from studentscore2;
+------+-------+
| sid | score |
+------+-------+
| 5 | 65 |
| 3 | 50 |
+------+-------+
发现从表中的对应关联字段的值同步主表更新
置空可以和级联联合使用:
删除表studentscore2的外键:alter table studentscore2 drop foreign key form_name;
此时的主表和从表是:
select * from studentinfo2;
+----+---------+
| id | name |
+----+---------+
| 3 | baijuyi |
| 5 | dupu |
+----+---------+
select * from studentscore2;
+------+-------+
| sid | score |
+------+-------+
| 5 | 65 |
| 3 | 50 |
+------+-------+
设置置空和级联外键:alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete set null on update cascade;
当更新其中主表id=5数据时候:
update studentinfo2 set id=6 where id=5;
select * from studentscore2;
+------+-------+
| sid | score |
+------+-------+
| 6 | 65 |
| 3 | 50 |
+------+-------+
发现从表同步更新记录
当删除主表的一条数据时候:
delete from studentinfo2 where id=3;
select * from studentscore2;
+------+-------+
| sid | score |
+------+-------+
| 6 | 65 |
| NULL | 50 |
+------+-------+
发现从表此时使用置空方式
有错的地方或者不足的指出来大家讨论和大家一起进步