一、使用DML插入数据
1.往学院表添加新的数据
a.单独写入
insert into tb_college (col_id, col_name, col_tel) values (default, '计算机学院', '028-44556677');
insert into tb_college (col_name, col_tel) values ('外国语学院', '028-55667788');
insert into tb_college values (999, '文学院', '028-77889900');
insert into tb_college values (default, '理学院', '028-89898989');
b.批处理insert
insert into tb_college (col_name, col_tel)
values
('计算机学院', '028-44556677'),
('外国语学院', '028-55667788'),
('文学院', default);
2.往学生表添加新的数据
a.单独写入
insert into tb_student (stu_id, stu_name, stu_sex, stu_birth, col_id) values ('1001', '王大锤', '男', '1995-5-5', '101');
b.批处理
insert into tb_student (stu_id, stu_name, stu_sex, stu_birth, col_id)
values
(1002, '李元芳', '男', '1998-12-1', '102'),
(1003, '武则天', '女', '1995-12-1', '102');
二、使用DML删除和更新数据
1.更新学生表数据
a.单行写入
delete from tb_student where stu_id = 1004;
b.批处理
delete from tb_student where stu_id = 1002 or stu_id = 1003;
delete from tb_student where stu_id in (1002, 1003);
2.查看系统安全变量
show variables like '%safe%'
有子表参照的父表不能被删除
3.及连删除,当删除学院的时候同时删除该学院的学生
alter table tb_student drop constraint fk_student_cid;
-- on delete restrict / cascade / set null
alter table tb_student add constraint fk_student_cid
foreign key (col_id) references tb_college (col_id)
on delete cascade;
注意:这里的外键名字不能跟之前的外键名字重复,但是我已经将之前的外键已经删除了,很奇怪。
delete后面写restrict,意为不让删;写set null,意为父表删了之后子表对应外键的值设置为空,并且要允许为空才行。
4.更新学院表数据
update tb_college set col_name = '计算机学院', col_tel = '028-12345678' where col_id = 102;