一、连接查询
1.查询女学生的学生学号及总成绩
SELECT s.sno,SUM(degree)
FROM student s
RIGHT JOIN sc r
ON s.sno=r.sno
WHERE ssex='女'
GROUP BY s.sno
2.查询李勇同学所选的课程号及成绩
SELECT r.cno,degree
FROM sc r
LEFT JOIN student s
ON r.sno=s.sno
WHERE sname='李勇'
3.查询李新老师所授课程的课程名称
SELECT c.cname
FROM course c
RIGHT JOIN teaching g
ON c.cno=g.cno
INNER JOIN teacher t
ON g.tno=t.tno
WHERE tname='李新'
4.查询女教师所授课程的课程号及课程名称
SELECT c.cno,c.cname
FROM course c
RIGHT JOIN teaching g
ON c.cno=g.cno
INNER JOIN teacher t
ON g.tno=t.tno
WHERE tsex='女'
5.查询姓“王”的学生所学的课程名称
SELECT c.cname
FROM course c
RIGHT JOIN sc r
ON c.cno=r.cno
INNER JOIN student s
ON r.sno=s.sno
WHERE sname LIKE '王%'
6.查询选修“数据库”课程且成绩在 80 到 90 之间的学生学号及成绩
SELECT r.cno,degree
FROM sc r
INNER JOIN course c
ON r.cno=c.cno
WHERE cname='数据库'
AND degree BETWEEN 80 AND 90
7.查询选修“C03”课程的学生的平均年龄
SELECT AVG(YEAR(CURDATE())-YEAR((Sbirthday))) 平均年龄
FROM student s
RIGHT JOIN sc
ON s.sno=sc.sno
WHERE cno='C03'
8.查询学习课程名为“数据库”的学生学号和姓名
SELECT s.sno,sname
FROM student s
RIGHT JOIN sc
ON s.sno=sc.sno
INNER JOIN course c
ON sc.cno=c.cno
WHERE c.cname='数据库'
9.查询”李新”教师任课的课程号，选修其课程的学生的学号和成绩
SELECT g.cno,sno,degree
FROM teaching g
RIGHT JOIN sc
ON sc.cno=g.cno
LEFT JOIN teacher t
ON g.tno=t.tno
WHERE tname='李新'
10.查询在第 3 学期所开课程的课程名称，选修其课程的学生学号和成绩
SELECT c.cname,sno,degree
FROM course c
RIGHT JOIN sc
ON c.cno=sc.cno
LEFT JOIN teaching g
ON sc.cno=g.cno
WHERE cterm=3
二、嵌套查询
1.查询至少选修两门课程的男学生姓名
SELECT s.sname
FROM student s
WHERE ssex='男'
AND sno=(
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(sno)>=2
)
2.查询与刘晨同一个系的同学姓名；
SELECT s.sname
FROM student s
WHERE sname!='刘晨'
AND sdept=(
SELECT sdept
FROM student
WHERE sname='刘晨'
)
3.查询学号比刘晨同学小，而年龄比他小的学生姓名，并写出 MySQL 中 sysdate()和 now()函数的功能差别。
SELECT s.sname
FROM student s
WHERE sno<(SELECT sno FROM student WHERE sname='刘晨')
AND (YEAR(CURDATE())-YEAR(Sbirthday))<(
SELECT (YEAR(CURDATE())-YEAR(Sbirthday))
FROM student
WHERE sname='刘晨'
)
NOW()：基于语句开始执行的时间
SYSDATE()：系统实时时间，有延迟导致时间不一致，主库和从库执行时返回值不一样
4.查询出生日期大于所有男同学出生日期的女同学的姓名及系别
SELECT s.sname,sdept
FROM student s
WHERE ssex='女'
AND YEAR(Sbirthday)>ALL(
SELECT YEAR(sbirthday)
FROM student
WHERE ssex='男'
)
5.查询成绩比该课程平均成绩高的学生的学号、课程号及成绩
SELECT cno,degree
FROM sc r
WHERE r.degree>(
SELECT AVG(degree)
FROM sc
WHERE sc.cno=r.cno
)
6.查询不讲授“C01”课的教师姓名
SELECT t.tname
FROM teacher t
WHERE tno NOT IN(
SELECT tno
FROM teaching g
WHERE cno='C01'
)
7.查询没有选修“C02”课程的学生学号及姓名
SELECT s.sno,sname
FROM student s
WHERE sno NOT IN(
SELECT sno
FROM sc
WHERE cno='C02'
)
8.查询选修了“数据库”课程的学生学号、姓名及系别
SELECT s.sno,sname,sdept
FROM student s
WHERE sno IN(
SELECT sno
FROM sc
INNER JOIN course c
ON sc.cno=c.cno
WHERE cname='数据库'
)
9.查询选修了全部课程的学生姓名
SELECT s.sname
FROM student s
WHERE sno IN(
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(sno)=(
SELECT COUNT(cno)
FROM course
)
)
10.分别用子查询和连接查询，求“C01”号课程在 80 分以上的学生信息
子查询：
SELECT *
FROM student s
WHERE sno IN(
SELECT sno
FROM sc
WHERE cno='C01'
AND degree>80
)
连接查询：
SELECT s.*
FROM student s
LEFT JOIN sc r
ON s.sno=r.sno
WHERE cno='C01'
AND degree>80
三、数据更新
1.向 Student 表中插入记录（‘20050203’,‘张静’,‘女’,‘1981-3-21’,‘CS’ ,‘电子商务’）
INSERT INTO `student`(`sno`,`sname`,`ssex`,`sbirthday`,`sdept`,`speciality`)
VALUES('20050203','张静','女','1981-3-21','CS' ,'电子商务')
2.插入学号为 ‘20050302’，姓名为’李四’学生信息
INSERT INTO `student`(`sno`,`sname`)
VALUES('20050302','李四')
3.把男学生记录保存到表 TS 中
CREATE TABLE ts(
SELECT *
FROM student
WHERE ssex='男'
)
4.将学号为’ 20150202’ 的学生姓名改为’张华’，系别改为“CS”，专业改为“多媒体技术”
UPDATE `student`
SET `sname`='张华',`sdept`='CS',`Speciality`='多媒体技术'
WHERE sno=20050202
5.将 ‘20150201’ 学生选修“C03”号课程的成绩改为该课的平均成绩
UPDATE `sc`
SET degree=(
SELECT * FROM(
SELECT AVG(degree)
FROM sc
WHERE cno='C03'
) a
)
WHERE sno='20050201'
AND cno='C03'
6.把女同学的成绩提高 5%
UPDATE `sc`
SET degree=(1+0.05)*degree
WHERE sno IN(
SELECT sno
FROM student
WHERE ssex='女'
)
7.(1) 把选修了“数据库”课程学生的成绩全改为空值（NULL）
UPDATE `sc`
SET degree=NULL
WHERE cno IN(
SELECT cno
FROM course
WHERE cname='数据库'
)
(2) 将’01’课程的成绩更新为随机成绩数据，成绩取值范围为 0 到 100 之间。
UPDATE `sc`
SET degree=FLOOR(RAND()*100)
WHERE cno='C01'
8.删除学号为 20150302 的学生记录
DELETE FROM student
WHERE sno=20050302
9.把“刘晨”同学的选课记录全部删除
DELETE FROM sc
WHERE sno IN(
SELECT sno
FROM student
WHERE sname='刘晨'
)
10.删除电子商务专业所有学生的选课记录
DELETE FROM sc
WHERE sno IN(
SELECT sno
FROM student
WHERE speciality='电子商务'
)
思考题
1.使用存在量词[NOT]EXISTS 的嵌套查询时，何时外层查询的 WHERE 条件为真，何时为假？
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
2.DROP 命令和 DELETE 命令的本质区别是什么？如何恢复通过 DELETE FROM 命令删除的数据。
- 触发条件不同
delete语句是数据库操作语言（dml），这个操作会放到rollbacksegement中，在事务提交后生效，执行时触发相应的trigger。
drop是数据库定义语言（ddl），操作立即生效，原数据不放到rollbacksegment中，不能回滚，操作不触发trigger。- 用法不同
delete只删除内容、释放空间但不删除定义，而delete即可以对行数据进行删除，也可以对整表数据进行删除。
drop是删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。- 执行速度不同
drop的执行速度大于delete。
delete的执行速度小于drop
如何恢复：Delete语句误操作只会删除表数据，而表结构还在。找到数据库的binlog存放位置，利用mysqlbinlog命令的–start-datetime参数快速定位数据位置.
确定起始位置点：
mysqlbinlog -vv --start-datetime='2022-05-19 17:20:00' on.000004| head -1000 |more
确定终止位置点：
mysqlbinlog -vv --start-datetime='2022-05-19 17:20:00' --stop-datetime='2022-05-19 17:23:00' on.000004| tail -1000 |more
将待恢复数据导出
mysqlbinlog -vv --start-position=192 --stop-position=435 on.000004 |grep ^"###" >/tmp/bin_data
将DELETE语句转换为INSERT语句
cat /tmp/bin_data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/tmp/student.sql
将INSERT语句导入数据库中
mysql -hlocalhost -P3306 -uroot -pyL@98 > /tmp/student.sql
即可完成数据恢复
3.利用 INSERT、UPDATE 和 DELETE 命令可以同时对多个表操作吗，如果需要同时操作多个表，如何实现？文章来源：https://www.toymoban.com/news/detail-447160.html
INSERT、UPDATE和DELETE 只能对单表操作
要操作多表，可以用触发器文章来源地址https://www.toymoban.com/news/detail-447160.html
到了这里，关于《数据库原理MySQL》第四次上机实验的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
