SQL数据库系统概论第四版第三章

SQL数据库系统概论第四版第三章

标签(空格分隔): MySQL


[TOC]

表结构

学生表(Student)

学号(Sno) 姓名(Sname) 性别(Ssex) 年龄(Sage) 所在系(Sdept)
200215121 李勇 20 CS
200215122 刘晨 19 CS
200215123 王敏 18 MA
200215125 张立 19 1S

课程表(Course)

课程号(Cno) 课程名(Cname) 现行课(Cpno) 学分(Ccredit)
1 数据库 5 4
2 数学 2
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 2
7 PASCAL语言 6 4

学生选课表(SC)

学号(Sno) 课程号(Cno) 成绩(Grade)
200215121 1 92
200215121 2 85
200215121 3 88
200215122 2 90
200215122 3 80

常用SQL语句

查询指定列

[例1] 查询全体学生的学号与姓名。

SELECT Sno,Sname

FROM Student;

[例2] 查询全体学生的姓名、学号、所在系。

SELECT Sname,Sno,Sdept

FROM Student;

[例3] 查询全体学生的详细记录。

SELECT Sno,Sname,Ssex,Sage,Sdept

FROM Student;

或

SELECT *

FROM Student;

[例4] 查全体学生的姓名及其出生年份。

SELECT Sname,2004-Sage /*假定当年的年份为2004年*/ FROM Student;

[例5] 查询全体学生的姓名、出生年份和所有系,要求用小 写字母表示所有系名

SELECT Sname,„Year of Birth: ‘,2004-Sage,

ISLOWER(Sdept)

FROM Student;

[例6] 查询选修了课程的学生学号。

SELECT Sno FROM SC;

等价于:

SELECT ALL Sno FROM SC;

[例7] 查询计算机科学系全体学生的名单。

SELECT Sname

FROM Student

WHERE Sdept=„CS‟;

[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname,Sage

FROM Student

WHERE Sage < 20;

[例9] 查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno

FROM SC

WHERE Grade<60;

[例10] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的 姓名、系别和年龄

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23;

[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage NOT BETWEEN 20 AND 23;

[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和 性别。

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN ( ‘IS’,’MA’,’CS’ );

[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。 SELECT Sname,Ssex

FROM Student

WHERE Sdept NOT IN ( ‘IS’,’MA’,’CS’ );

[例14] 查询学号为200215121的学生的详细情况。

SELECT *

FROM Student

WHERE Sno LIKE „200215121′;

等价于:

SELECT *

FROM Student

WHERE Sno = ‘ 200215121 ‘;

[例15] 查询所有姓刘学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname LIKE „刘%‟;

[例16] 查询姓”欧阳”且全名为三个汉字的学生的姓名。

SELECT Sname

FROM Student

WHERE Sname LIKE ‘欧阳__’;

[例17] 查询名字中第2个字为”阳”字的学生的姓名和学号。

SELECT Sname,Sno

FROM Student

WHERE Sname LIKE „__阳%‟;

[例18] 查询所有不姓刘的学生姓名。

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname NOT LIKE ‘刘%’;

[例19] 查询DB_Design课程的课程号和学分。

SELECT Cno,Ccredit

FROM Course

WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\„;

[例20] 查询以”DB_”开头,且倒数第3个字符为 i的课程的详细情况。 SELECT *

FROM Course

WHERE Cname LIKE ‘DB\_%i_ _’ ESCAPE ‘ \ „;

[例21] 某些学生选修课程后没有参加考试,所以有选课记录,但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno,Cno

FROM SC

WHERE Grade IS NULL

[例22] 查所有有成绩的学生学号和课程号。

SELECT Sno,Cno

FROM SC

WHERE Grade IS NOT NULL;

[例23] 查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname

FROM Student

WHERE Sdept= ‘CS’ AND Sage<20;

改写[例12]

[例12] 查询信息系(IS)、数学系(MA)和计算机科学系 (CS)学生的姓名和性别。

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN ( ‘IS’,’MA’,’CS’ )

可改写为:

SELECT Sname,Ssex

FROM Student

WHERE Sdept= ‘ IS ‘ OR Sdept= ‘ MA’ OR Sdept= ‘ CS ‘;

[例24] 查询选修了3号课程的学生的学号及其成绩,查询结果按分 数降序排列。

SELECT Sno,Grade

FROM SC

WHERE Cno= ‘ 3 ‘

ORDER BY Grade DESC;

[例25] 查询全体学生情况,查询结果按所在系的系号升序排 列,同一系中的学生按年龄降序排列。

SELECT *

FROM Student

ORDER BY Sdept,Sage DESC;

[例26] 查询学生总人数。

SELECT COUNT(*)

FROM Student;

[例27] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)

FROM SC;

[例28] 计算1号课程的学生平均成绩。

SELECT AVG(Grade)

FROM SC

WHERE Cno= ‘ 1 ‘;

[例29] 查询选修1号课程的学生最高分数。

SELECT MAX(Grade)

FROM SC

WHER Cno= „ 1 ‟;

[例30]查询学生200215012选修课程的总学分数。

SELECT SUM(Ccredit)

FROM SC, Course

WHER Sno=’200215012′ AND SC.Cno=Course.Cno;

[例31] 求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)

FROM SC

GROUP BY Cno;

[例32] 查询选修了3门以上课程的学生学号。

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*) >3;

[例33] 查询每个学生及其选修课程的情况

SELECT Student.*,SC.*

FROM Student,SC

WHERE Student.Sno = SC.Sno;

[例34] 对[例33]用自然连接完成。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC

WHERE Student.Sno = SC.Sno;

[例35]查询每一门课的间接先修课(即先修课的先修课)

SELECT FIRST.Cno,SECOND.Cpno

FROM Course FIRST,Course SECOND

WHERE FIRST.Cpno = SECOND.Cno;

[例 36] 改写[例33]

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);

[例37]查询选修2号课程且成绩在90分以上的所有学生

SELECT Student.Sno, Sname

FROM Student, SC

WHERE Student.Sno = SC.Sno AND

/* 连接谓词*/

SC.Cno= „2‟ AND SC.Grade > 90; /* 其他限定条件 */

[例38]查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade

FROM Student,SC,Course /*多表连接*/

WHERE Student.Sno = SC.Sno

and SC.Cno = Course.Cno;

[例39] 查询与“刘晨”在同一个系学习的学生。

此查询要求可以分步来完成

① 确定“刘晨”所在系名

SELECT Sdept

FROM Student WHERE Sname= ‘ 刘晨 ‘;

结果为: CS

② 查找所有在IS系学习的学生。

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept= ‘ CS ‘;

将第一步查询嵌入到第二步查询的条件中

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname= „ 刘晨 ‟);

此查询为不相关子查询。

用自身连接完成[例39]查询要求

SELECT S1.Sno,S1.Sname,S1.Sdept

FROM Student S1,Student S2

WHERE S1.Sdept = S2.Sdept AND

S2.Sname = ‘刘晨’;

[例40]查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno,Sname ③ 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN

(SELECT Sno ② 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN

(SELECT Cno ① 首先在Course关系中找出 FROM Course “信息系统”的课程号,为3号 WHERE Cname= „信息系统‟

)

);

用连接查询实现[例40]

SELECT Sno,Sname

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno AND

SC.Cno = Course.Cno AND

Course.Cname=„信息系统‟;

例:假设一个学生只可能在一个系学习,并且必须属于一个 系,则在[例39]可以用 = 代替IN :

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept =

(SELECT Sdept

FROM Student

WHERE Sname= „刘晨‟);

[例41]找出每个学生超过他选修课程平均成绩的课程

号。

SELECT Sno, Cno

FROM SC x

WHERE Grade >=(SELECT AVG(Grade)

FROM SC y

WHERE y.Sno=x.Sno);

[例42] 查询其他系中比计算机科学某一学生年龄

小的学生姓名和年龄

SELECT Sname,Sage

FROM Student

WHERE Sage < ANY (SELECT Sage

FROM Student WHERE Sdept= ‘ CS ‘) AND Sdept <> „CS ‘ ; /*父查询块中 的条件 */

用聚集函数实现[例42]

SELECT Sname,Sage

FROM Student

WHERE Sage <

(SELECT MAX(Sage)

FROM Student

WHERE Sdept= „CS ‘)

AND Sdept <> ‘ CS ‟;

[例43] 查询其他系中比计算机科学系所有学生年龄都小 的学生姓名及年龄。

方法一:用ALL谓词

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 ‟;

[例44]查询所有选修了1号课程的学生姓名。

用嵌套查询

SELECT Sname

FROM Student

WHERE EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND Cno= ‘ 1 ‘);

用连接运算

SELECT Sname

FROM Student, SC

WHERE Student.Sno=SC.Sno AND SC.Cno= ’1′;

[例45] 查询没有选修1号课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno = Student.Sno AND Cno=’1′);

[例39]查询与“刘晨”在同一个系学习的学生。

可以用带EXISTS谓词的子查询替换:

SELECT Sno,Sname,Sdept

FROM Student S1

WHERE EXISTS

(SELECT *

FROM Student S2

WHERE S2.Sdept = S1.Sdept AND

S2.Sname = „刘晨‟);

[例46] 查询选修了全部课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM Course

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno= Student.Sno AND Cno= Course.Cno )

);

[例47]查询至少选修了学生200215122选修的全部课程的学

生号码。

用NOT EXISTS谓词表示:

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(SELECT *

FROM SC SCY

WHERE SCY.Sno = ‘ 200215122 ‘ AND

NOT EXISTS

(SELECT *

FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno AND

SCZ.Cno=SCY.Cno));

[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。

方法一:

� SELECT *

� FROM Student

� WHERE Sdept= ‘CS’

� UNION

� SELECT *

� FROM Student

� WHERE Sage<=19;

方法二:

SELECT DISTINCT *

FROM Student

WHERE Sdept= ‘CS’ OR Sage<=19;

[例49] 查询选修了课程1或者选修了课程2的学生。

SELECT Sno

FROM SC

WHERE Cno=’ 1 ‘

UNION

SELECT Sno

FROM SC

WHERE Cno= ‘ 2 ‘;

[例50] 查询计算机科学系的学生与年龄不大于19岁

的学生的交集

SELECT *

FROM Student

WHERE Sdept=’CS’

INTERSECT

SELECT *

FROM Student

WHERE Sage<=19

注:sql server 无交运算

[例50] 实际上就是查询计算机科学系中年龄不大

于19岁的学生

SELECT *

FROM Student

WHERE Sdept= „CS‟ AND Sage<=19;

[例51] 查询选修课程1的学生集合与选修课程2的学生集

合的交集

SELECT Sno

FROM SC

WHERE Cno=’ 1 ‘

INTERSECT

SELECT Sno

FROM SC

WHERE Cno=’2 ‘;

[例51]实际上是查询既选修了课程1又选修了课程2

的学生

SELECT Sno

FROM SC

WHERE Cno=’ 1 ‘ AND Sno IN

(SELECT Sno FROM SC

WHERE Cno=’ 2 ‘);

[例52] 查询计算机科学系的学生与年龄不大于19岁的学

生的差集。

SELECT *

FROM Student

WHERE Sdept=’CS’

EXCEPT

SELECT *

FROM Student

WHERE Sage <=19;

注:sql server 无差运算

[例52]实际上是查询计算机科学系中年龄大于19岁

的学生

SELECT *

FROM Student

WHERE Sdept= ‘CS’ AND Sage>19;

推荐阅读更多精彩内容