实验三 数据查询(4学时)

[实验目标]

1、掌握单表查询的应用

2、熟练掌握连接查询的应用

3、掌握嵌套查询和集合查询

[实验内容]

一、根据实验二，建立 SC 数据库（实验条件），含有 student 、 course 和 sc 三个表

二、数据查询实验

1 、单表查询

1 ）选择指定的列

【例】 查询全体学生的学号和姓名

2 ）查询全部列

【例】 查询全体学生的详细信息

3 ）对查询后的指定列进行命名

【例】 查询全部学生的 “ 姓名 ” 及其 “ 出生年 ” 两列

4 ）消除取值重复的行

【例】 查询选修了课程的学生学号

5 ）选择表中若干元组（满足条件的）

（ I）大小比较

【例】 查询计算机系（ IS ）全体学生名单

【例】 查询全体 20 岁以下的学生姓名和年龄

（ II）确定范围

【例】 查询所有在 20 到 23 岁（含 20 和 23 ）的学生姓名、系别和年龄

（ III） in 和 not in 确定集合

【例】 查询 IS 系和 CS 系的全体学生姓名和性别

【例】 查询既不属于 IS 系，也不属于 MA 系的学生姓名和年龄

（ IV）字符匹配（ like % _ ）

【例】 查询所有姓李的学生姓名和性别

【例】 查询所有 “2002” 年入学的学生学号、姓名和系别

【例】 查询所有不姓 “ 刘 ” 的学生信息

【例】 查询名称含有 “ 数据 ” 的课程号、课程名及学分

（ V）涉及空值的查询（ is null ）

【例】 查询没有先修课的课程号和课程名。

【例】 查询所有有成绩的学生学号、课程号及成绩

6 ）查询结果排序（ order by ）

【例】 查询选修了 3 号课程的学生学号和成绩，结果按成绩降序排列。

7 ）聚集函数

count 、 sum 、 avg 、 max 、 min

【例】 查询学生总数

【例】 查询所有课程的总学分

【例】 查询全体学生平均年龄

【例】 查询 1 号课程的最高分

8 ）分组统计（ group by ）

【例】 查询男女学生各有多少人。

【例】 查询每个课程的课程号和平均分。

【例】查询选修了3门课程以上的学生学号。

【例】查询不及格门数2门以上的学生学号。

2 、连接查询

1 ）等值与非等值连接查询

【例】 查询每个学生及其的选修课程情况

2 ）自身连接

【例】 查询每个学生的间接选修课

3 ）外连接

【例】 查询所有学生选修课程情况（含没选修课程的学生）

【例】查询所有学生选修课程情况（含没选修课程的学生，属性有学号，姓名，课程名和成绩）

4 ）符合条件连接

【例】 查询选修了 2 号课程且成绩在 90 分以上的所有学生学号和姓名

【例】 查询每个学生的学号、姓名，选修课程名和成绩。

3 、嵌套查询

1）带有IN谓词的子查询（ 属性 in （子查询的查询结果） ）

【例】查询与王敏同学在同一个系的学生信息。

【例】查询不与王敏同学不在同一个系的学生信息。

【例】查询选修了课程名为“信息系统”的学生学号和姓名。

【例】查询曾与刘晨一同上课的学生学号和姓名。（假设：一个课程只有一个上课班）

2）带有比较运算符的子查询（=，>=，<=，<>或!=）

【例】查询与王敏同学在同一个系的所有学生信息 （=判断）

【例】查询每个学生超过该课程最低分的课程号。（同类课程不是最低分的）

【例】查询每个学生超过他选修课程平均成绩的课程号。

【例】查询每个学生超过该课程平均成绩的课程号。

3）带有ANY或ALL谓词的子查询

【例】查询其他系中比计算机系某一学生年龄小的学生姓名，性别、年龄和所在系。

【例】查询其他系中比计算机系所有年龄都小的学生姓名和年龄。

4 ）带有Exists谓词的子查询

【例】查询所有选修了1号课程的学生姓名。

【例】查询选修了全部课程的学生姓名。

【例】查询至少选修了学生200215122选修的全部课程的学生学号。

4、集合查询

1)并UNION

【例】 查询计算机系的学生及年龄不大于19岁的学生详细信息。

【例】查询选修了1号课程的及年龄不大于19岁的学生详细信息。

2)交INTERSECT

【例】查询选修了1号课程的与年龄不大于19岁的 学生 详细信息 的交集。

3)差EXCEPT

【例】查询计算机科学系的学生与年龄不大于19岁的学生详细信息的差集。

[课后作业]

1、通过SPJ数据库完成课后针对SPJ的查询练习。

2、根据SPJ数据库用SQL语句完成以下查询。

1）查询“天津”的供应商详细信息。

2）查询不是“天津”的供应商代码。

3）查询供应 工程 J1零件的供应商代码。

4）查询供应商S1供应过的商品代码。

5）查询供应商S1供应工程J1的零件种类有几种。

6）查询供应商S1供应工程J1的各种零件的零件代码和其数量和。

7）查询J1工程使用的零件种类有几种。

8）查询J1工程使用的各种零件代码和其数量和。

9）查询J1工程使用的零件总数大于300的零件代码和数量。

10）查询J1工程使用的各种零件，其数量至少大于J3使用的S2供应的所有零件数量和的，零件代码和数量。

11）查询J1工程使用的各种零件，其数量至少大于J3使用的同类数量和的，零件代码和数量。

12）查询至少使用了J1工程所用的几类零件的工程的工程号。

13）查询至少使用了所有红色零件的工程号。

14）查询使用了全部红色零件的工程号。

15）查询工程所在地与供应其零件的供应商在同一个城市的工程号和供应商号。

16）查询没有使用天津供应商生产的红色零件的工程号JNO。

17）查询至少使用了供应商S1供应的全部零件的工程号JNO。

18）查询使用了全部上海产的零件的工程号JNO。

19）查询没有使用J2所使用的所有零件的工程号JNO。

3、根据SC数据库用SQL语句完成以下任务。

1）将不及格的每个学生成绩增加5分。

2）将低于该课程平均分的学生成绩，成绩提高10%。

3）把计算机科学系所有学生增加1岁。

4）把学分为3分以内（含3分）的课程成绩提高3分，学分为3分以上的课程成绩提高4分。

5）建立名字为“成绩单”的视图，属性有 学生号，学生姓名、课程号、课程名和成绩 。

6）删除1号课程。