- 莫偷懒,切勿“复制-粘贴”,亲自敲一遍代码效果好。
- 自己输入后,如果运行不正确,可以参考一下这里的代码。
--[例3.16] 查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
--[例3.17] 查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student;
--[例3.18] 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
--或
SELECT *
FROM Student;
--[例3.19] 查全体学生的姓名及其出生年份。
SELECT Sname,2016-Sage /*假设当时为2016年*/
FROM Student;
--[例3.20] 查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。
SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;
--[例3.21] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
--等价于:
SELECT ALL Sno FROM SC;
--[例3.22] 查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept='CS';
--[例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
--[例3.24]查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
--[例3.25] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
--[例3.26] 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
--[例3.27]查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');
--[例3.28]查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA','CS');
--[例3.29] 查询学号为201215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE '201215121';
--等价于:
SELECT *
FROM Student
WHERE Sno = ' 201215121 ';
--[例3.30] 查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
--[例3.31] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_ _';
--【注】数据库字符集为ASCII时,一个汉字需要两个_
-- 数据库字符集为 GBK 时,一个汉字需要一个_
--[例3.32] 查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_ _阳%';
--[例3.33] 查询所有不姓刘的学生姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
--[例3.34] 查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\' ;
--[例3.35] 查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\' ;
--ESCAPE '\' 表示“ \” 为换码字符
--[例3.36] 某些学生选修课程后没有参加考试,所以有选课记录,但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL
--[例3.37] 查所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
--[例3.38] 查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;
--改写[例3.27]
--[例3.27] 查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS ','MA ','IS');
--可改写为:
SELECT Sname, Ssex
FROM Student
WHERE Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';
--[例3.39]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno, Grade
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;
【例3.40】
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
SELECT *
FROM Student
--[例3.41] 查询学生总人数。
SELECT COUNT(*)
FROM Student;
--[例3.42] 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
--[例3.43] 计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= '1';
--[例3.44] 查询选修1号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
--[例3.45] 查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
【例3.46】
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
SELECT * FROM SC
【例3.47】
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >2;
SELECT * FROM SC
【例3.48】
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=86;
SELECT * FROM SC
--错误的使用:
--SELECT Sno, AVG(Grade)
--FROM SC
--WHERE AVG(Grade)>=90
--GROUP BY Sno;
【例3.49】
--等值连接
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
--自然连接
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
--等值连接
SELECT *
FROM Student, SC
WHERE Student.Sno = SC.Sno;
--[例 3.50] 对[例 3.49]用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
【例3.51】
SELECT Student.Sno,Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>86;
【例3.52】
SELECT FIRST_TABLE.Cno, SECOND_TABLE.Cpno
FROM Course FIRST_TABLE, Course SECOND_TABLE
WHERE FIRST_TABLE.Cpno = SECOND_TABLE.Cno;
【例3.53】
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
【例3.54】
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course --多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
SELECT *
FROM Student, SC, Course --多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
【例3.54-3.55】
--嵌套查询
--一个SELECT-FROM-WHERE语句称为一个查询块
--将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT * /*外层查询/父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= '2');
--不相关子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
--相关子查询
SELECT Sno, Cno
FROM SC x
WHERE Grade >=( SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
【例3.55】
--不相关子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
--连接查询(自身连接)
SELECT S1.Sno, S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨';
--可以用带EXISTS谓词的子查询替换:
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨');
【例3.56】
SELECT Sno,Sname -- ③ 最后在Student关系中取出Sno和Sname
FROM Student
WHERE Sno IN
(SELECT Sno -- ② 然后在SC关系中找出选修了3号课程的学生学号
FROM SC
WHERE Cno IN
(SELECT Cno --① 首先在Course关系中找出“信息系统”的课程号,为3号
FROM Course
WHERE Cname= '信息系统'
)
);
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname='信息系统';
【例3.57】
SELECT Sno, Cno
FROM SC x
WHERE Grade >=( SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
【例3.57+】
SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >=Avg_sc.avg_grade
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE Student.Sno=SC1.Sno;
【例3.58】
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY ( SELECT Sage
FROM Student
WHERE Sdept= 'CS')
AND Sdept <> 'CS'; /*父查询块中的条件 */
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= 'CS ')
AND Sdept <> 'CS';
SELECT * FROM Student
【例3.59】
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL ( SELECT Sage
FROM Student
WHERE Sdept= 'CS')
AND Sdept <> 'CS'; /*父查询块中的条件*/
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= 'CS ')
AND Sdept <> 'CS';
SELECT * FROM Student
【例3.60】
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= '1');
【例3.61】
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
【例3.62】
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno= Student.Sno
AND Cno= Course.Cno
)
);
【例3.63】
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = '201215122' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
【例3.64】
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;