https://blog.csdn.net/mjb740074431/article/details/89375226
https://blog.csdn.net/m0_46314779/article/details/115682088
-- 查看信息
select * from sc;
select * from course;
select * from student;
--  插入如下学生记录（学号：95030，姓名：李莉，年龄：18）
insert into student(sno,sname,ssex,sage,sdept)
values('95030','李莉','','18','');

--  插入如下选课记录（95030，1）
insert into sc (sno,cno) values('95030','1');

-- 计算机系学生年龄改成20
update student set sage='20' 
where sdept ='IS';

--  把数学系所有学生成绩改成0
update sc set grade='0'
where sno in (
			select sno from student 
			where sdept = 'MA'
);

-- 把低于总平均成绩的女同学成绩提高5分
-- update sc set grade = grade +5
-- where grade in (
-- 			select grade from sc where grade < (
-- 						 select AVG(grade) from sc
-- 			)
-- 	) and sno in(
-- 					select sno from student where ssex = '女'
-- );

-- - Table 'student.sc1' doesn't exist 未解决
update sc set grade = grade +5
where grade in (
			select grade from sc as sc1 where grade < (
						 select AVG(grade) from sc1
			)
	) and sno in(
					select sno from student where ssex = '女'
);
--  update语句中包含的子查询的表和update的表为同一张表时，报错：1093-You can’t specify target table for update in FROM clause

-- mysql不允许update目标表和子查询里面的表为同一张表
-- 
--  解决办法: 利用子查询sql可以改变双层的子查询，即可执行成功  (但性能较差，仅仅适合较小的数据量的)

-- 删除95030学生信息
delete from student where sno = '95030';
--  删除SC表中无成绩的记录
delete from sc where grade is null;
-- 删除张娜的选课记录