一、連接查詢

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()：基于語句開始執(zhí)行的時間
SYSDATE()：系統(tǒng)實時時間，有延遲導致時間不一致，主庫和從庫執(zhí)行時返回值不一樣

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”，專業(yè)改為“多媒體技術”

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.刪除電子商務專業(yè)所有學生的選課記錄

DELETE FROM sc
WHERE sno IN(
	SELECT sno
	FROM student
	WHERE speciality='電子商務'
)

思考題
1.使用存在量詞[NOT]EXISTS 的嵌套查詢時，何時外層查詢的 WHERE 條件為真，何時為假？

exists對外表用loop逐條查詢，每次查詢都會查看exists的條件語句，當exists里的條件語句能夠返回記錄行時(無論記錄行是的多少，只要能返回)，條件就為真，返回當前l(fā)oop到的這條記錄，反之如果exists里的條件語句不能返回記錄行，則當前l(fā)oop到的這條記錄被丟棄，exists的條件就像一個bool條件，當能返回結果集則為true，不能返回結果集則為false

2.DROP 命令和 DELETE 命令的本質區(qū)別是什么？如何恢復通過 DELETE FROM 命令刪除的數據。

1. 觸發(fā)條件不同
   delete語句是數據庫操作語言（dml），這個操作會放到rollbacksegement中，在事務提交后生效，執(zhí)行時觸發(fā)相應的trigger。
   drop是數據庫定義語言（ddl），操作立即生效，原數據不放到rollbacksegment中，不能回滾，操作不觸發(fā)trigger。
2. 用法不同
   delete只刪除內容、釋放空間但不刪除定義，而delete即可以對行數據進行刪除，也可以對整表數據進行刪除。
   drop是刪除內容和定義，并釋放空間。執(zhí)行drop語句，將使此表的結構一起刪除。
3. 執(zhí)行速度不同
   drop的執(zhí)行速度大于delete。
   delete的執(zhí)行速度小于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 命令可以同時對多個表操作嗎，如果需要同時操作多個表，如何實現？

INSERT、UPDATE和DELETE 只能對單表操作
要操作多表，可以用觸發(fā)器文章來源地址http://www.zghlxwxcb.cn/news/detail-447160.html

到了這里，關于《數據庫原理MySQL》第四次上機實驗的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！