1月阅读周·MySQL数据库入门:操作关联表之添加和删除数据篇
背景
去年下半年,我在微信书架里加入了许多技术书籍,各种类别的都有,断断续续的读了一部分。
没有计划的阅读,收效甚微。
新年伊始,我准备尝试一下其他方式,比如阅读周。每月抽出1~2个非连续周,完整阅读一本书籍。
这个“玩法”虽然常见且板正,但是有效,已经坚持阅读十二个月。
已读完书籍:《架构简洁之道》、《深入浅出的Node.js》、《你不知道的JavaScript(上卷)》、《你不知道的JavaScript(中卷)》、《你不知道的JavaScript(下卷)》、《数据结构与算法JavaScript描述》、《WebKit技术内幕》、《前端架构:从入门到微前端》、《秒懂算法:用常识解读数据结构与算法》、《JavaScript权威指南》、《JavaScript异步编程设计快速响应的网络应用》、《编写可测试的JavaScript代码》。
当前阅读周书籍:《MySQL数据库入门》。
添加数据
在实际开发中,最常见的关联关系就是多对一关系。接下来,在表student和表grade中添加外键约束来建立两个表的关联关系。具体语句如下:
alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);
语句执行成功后,查看外键约束是否成功添加,查询结果如下:
mysql>show create table student;
+---------+--------------------------------+
| Table | Create Table
+---------+--------------------------------+
| student | CREATE TABLE 'student' (
'sid' int(4) NOT NULL,
'sname' varchar(36) DEFAULT NULL,
'gid' int(4) NOT NULL,
PRIMARY KEY ('sid'),
KEY 'FK_ID' ('gid'),
CONSTRAINT 'FK_ID' FOREIGN KEY ('gid') REFERENCES 'grade' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+--------------------------------+
1 row in set (0.00 sec)
从查询结果可以看出,student表的外键约束已经成功添加。此时表student和表grade之间是多对一的关系。因为外键列只能插入参照列存在的值,所以如果要为两个表添加数据,就需要先为主表grade添加数据,具体语句如下:
INSERT INTO grade(id,name)VALUES(1,'软件一班');
INSERT INTO grade(id,name)VALUES(2,'软件二班');
在上述语句中,添加的主键id为1和2,由于student表的外键与grade表的主键关联,因此在为student表添加数据时,gid的值只能是1或2,不能使用其他的值,具体语句如下:
INSERT INTO student(sid,sname,gid)VALUES(1,'王红',1);
INSERT INTO student(sid,sname,gid)VALUES(2,'李强',1);
INSERT INTO student(sid,sname,gid)VALUES(3,'赵四',2);
INSERT INTO student(sid,sname,gid)VALUES(4,'郝娟',2);
上述语句执行成功后,两个表之间的数据就具有关联性。假如要查询软件一班有哪些学生,首先需要查询软件一班的id,然后根据这个id在student表中查询该班级有哪些学生,具体步骤如下。
(1)在grade表中查询出班级名称为“软件一班”的id,具有语句如下:
SELECT id FROM grade WHERE name='软件一班';
上述语句执行成功后,结果如下:
mysql>SELECT id FROM grade WHERE name='软件一班';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.01 sec)
从上述结果可以看出,软件一班的id为1。
(2)在student表中,查询gid=1的学生,即为软件一班的学生,具体语句如下:
SELECT sname FROM student WHERE gid=1;
上述语句执行成功后,结果如下:
mysql>SELECT sname FROM student WHERE gid=1;
+-------+
| sname |
+-------+
| 王红 |
| 李强 |
+-------+
2 rows in set (0.00 sec)
从上述结果可以看出,软件一班只有两个学生,一个是王红,一个是李强。
删除数据
在某些情况下还需要删除关联表中的数据,例如学校的软件一班取消了,就需要在数据库中将该班级以及该班级的学生一起删除。由于grade表和student表之间具有关联关系。参照列被参照的值是不能被删除的,因此,在删除软件一班时,一定要先删除该班级的所有学生,然后再删除班级,具体步骤如下。
(1)将软件一班的所有学生全部删除,具体语句如下:
delete from student where sname='王红';
delete from student where sname='李强';
上述语句执行成功后,可以使用SELECT语句查询,查询结果如下:
mysql>select * from student where gid=1;
Empty set (0.00 sec)
从上述语句可以看出,student表中已经没有任何学生的记录了。
(2)在grade表中,将软件一班删除,具体语句如下:
delete from grade where id=1;
上述语句执行成功后,可以使用SELECT语句查询,查询结果如下:
mysql>select * from grade;
+----+----------+
| id | name |
+----+----------+
| 2 | 软件二班 |
+----+----------+
1 row in set (0.00 sec)
从查询结果可以看出,软件一班被成功地删除了。这样就删除了关联表中的数据。如果直接删除表grade中的“软件二班”,看看会出现什么情况,具体语句如下:
delete from grade where id=2;
执行结果如下:
mysql>delete from grade where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails ('chapter05'.'student', CONSTRAINT 'FK_ID' FOREIGN KEY ('gid') REFERENC
ES 'grade' ('id'))
由此运行结果可以看出,在两个具有关联关系的表中删除数据时,一定要先删除从表中的数据,然后再删除主表中的数据,否则会报错。
需要注意的是,在实际情况中,想要删除“软件一班”,并不需要删除“软件一班”的学生,可以将表student中“王红”和“李强”的gid改成NULL,只要主表中该列没有被从表参照就可以删除。但是在建表时,gid字段有非空约束,所以只能将“王红”和“李强”的记录删除。
总结
在实际开发中,最常见的关联关系就是多对一关系。
外键列只能插入参照列存在的值,所以如果要为两个表添加数据,就需要先为主表添加数据。
在两个具有关联关系的表中删除数据时,一定要先删除从表中的数据,然后再删除主表中的数据,否则会报错。
作者介绍
非职业「传道授业解惑」的开发者叶一一。
《趣学前端》、《CSS畅想》等系列作者。华夏美食、国漫、古风重度爱好者,刑侦、无限流小说初级玩家。
如果看完文章有所收获,欢迎点赞👍 | 收藏⭐️ | 留言📝。
- 点赞
- 收藏
- 关注作者
评论(0)