MySQL上机第四章-子查询与数据操作

一、上机目的     

                  熟悉子查询

                  掌握数据的添加、修改和删除

---

二、上机内容

（1）查询与“刘伟”老师职称相同的教师号、姓名。

SELECT TNo,TN FROM T WHERE Prof = ANY(SELECT Prof FROM T WHERE TN LIKE('刘伟'));

（2）查询其他系中比计算机系某一个学生年龄小的学生姓名、年龄和系别。(至少2种方法)

SELECT SN,Age,Dept FROM S WHERE Age < ANY(SELECT Age FROM S WHERE Dept LIKE('计算机')) AND Dept NOT LIKE('计算机');

SELECT SN,Age,Dept FROM S WHERE Age < ANY(SELECT max(Age) FROM S WHERE Dept LIKE('计算机')) AND Dept NOT LIKE('计算机');

（3）查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。(至少2种方法)

SELECT TN,Sal FROM T WHERE Sal > ALL(SELECT Sal FROM T WHERE Dept LIKE('计算机'));

SELECT TN,Sal FROM T WHERE Sal > ANY(SELECT MAX(Sal) FROM T WHERE Dept LIKE('计算机'));

 （4）查询讲授课程号为C5的教师姓名。(至少2种方法)

SELECT TN FROM T WHERE TNo = ANY(SELECT TNo FROM TC WHERE CNo='C5');

SELECT TN FROM T WHERE EXISTS(SELECT * FROM TC WHERE TNo=T.TNo AND CNo='C5');

（5）查询不讲授课程号为C5的教师姓名。(至少2种方法)

SELECT TN FROM T WHERE TNo NOT IN (SELECT TNo FROM TC WHERE CNo IN ('C5'));

SELECT TN FROM T WHERE NOT EXISTS(SELECT * FROM TC WHERE TNo=T.TNo AND CNo='C5');

 （6）查询选修所有课程的学生姓名。（使用exists）

SELECT SN FROM S WHERE EXISTS
(SELECT * FROM C WHERE EXISTS(SELECT * FROM SC
WHERE SC.CNo=C.CNo AND SC.Sno=S.Sno));

（7）把刘伟老师的系别改为信息系。

UPDATE T
SET Dept = '信息'
WHERE TN LIKE ('刘伟');

（8）把教师表中工资小于或等于1000元的讲师的工资提高20%。

UPDATE T SET Sal=1.2*Sal
WHERE Prof="讲师" AND Sal<=1000;

（9）把所有教师的工资提高到工资的1.2倍。

UPDATE T
SET Sal=1.2*Sal

（10）把讲授C5课程的教师的岗位津贴增加100元。

UPDATE T SET Comm=Comm+100
WHERE TNo =
ANY(SELECT TC.TNo FROM C,TC WHERE C.CNo=TC.CNo AND TC.CNo='C5');

（11）删除刘伟老师授课的记录。

DELETE
FROM TC
WHERE TNo = ANY(SELECT TNo FROM T
 WHERE TC.TNo=TNo AND T.TN LIKE("刘伟"));

（12）从SC数据表中查询出所有同学的总分和平均分，并将查询结果存放到一个新的数据表cal_table中。

CREATE TABLE IF NOT EXISTS cal_table(
	AVERAGE VARCHAR(20),
	TOTAL VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO cal_table
SELECT sum(Score),avg(Score)
FROM SC
GROUP BY Sno;

（13）求出各系教师的平均工资，结果存放到一个新表avgsal中。

CREATE TABLE IF NOT EXISTS avgsal(
	AVERAGE VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO avgsal
SELECT avg(Sal)
FROM T
GROUP BY Dept;