数据库实验习题

《数据库概论》实验

《数据库概论》实验指导书

(2003级)

一、 实验目的：

数据库系统是一门应用性很强的课程，上机实验是数据库课程的重要环节。本实验是结合《数据库概论》课程的学习而开设的实验。本实验目的如下：

1. 通过上机实验，加深对数据库系统理论知识的理解，增强对网络环境下大型数据库的感性认识和实际操作能力；

2. 通过使用具体的DBMS，了解一种实际的数据库管理系统，并掌握其操作技术。熟悉ORACLE数据库的Net8工具，掌握ORACLE数据库的网络连接配置；

3. 学习使用SQL语言，加深对SQL语言的认识和理解，提高SQL语言的实际应用能力； 4. 加强对事务处理、权限管理等概念的认识和理解。

二、 适应专业：信息管理与信息系统，信息与计算机科学

三、 实验学时数：12学时

四、 实验内容及学时安排： 实验准备：

各班 选出10个同学，协助老师 搭建试验环境。包括： 1） 2） 3）

安装ORACLE客户端，配置与主机的数据库连接。 安装软件工具 PLSQL/DEVELOPER 负责本班用户的建立。

分为两批 上午 下午各两个班。

实验一：数据定义/数据操纵语言

实验目的：熟悉Oracle上机环境以及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。

学时数：4学时

实验内容：SQL数据定义语句， SQL数据操纵语句。

实验要求：1. 熟悉Oracle上机环境；

2. 建立数据库表，修改数据库表结构，建立、删除索引；

1/42

《数据库概论》实验

3. 对数据库表进行插入、修改和删除数据的操作。

实验二：数据查询语言，视图定义

实验目的：体会SQL语言数据查询功能的丰富和复杂，视图使用。 学时数：4学时

实验内容：SQL数据查询语句，视图的定义与操纵；

实验要求：对数据库表进行各种查询操作， 建立视图，视图查询，视图更新；

实验三：授权控制与事务处理

实验目的：通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 学时数：2学时

实验内容： 1. SQL数据控制语句；

2. SQL事务处理。

实验要求：

1. 给某一或全部用户授权和收回授权； 2. 事务回退，事务提交。

五、 教材及参考文献

1.《数据库系统概论》（第三版） 萨师煊等编著 高等教育出版社 2000.2 2.《ORACLE 8.x For Windows NT实用教程》 翁正科等编著 清华大学出版社 1998.10

六、 实验环境

实验要求如下环境

服务器端：Windows 2000NT Server；

ORACLE 8.x Database Server For Windows Windows2000 Server 客户端： Windows9x；Oracle 8.x Client 网络协议：TCP/IP

七、 成绩考核

上机实验考核采取实验报告和上机抽查检验相结合的方式。每次实验结束后都必须写出实验报告。

八. 实验报告

2/42

《数据库概论》实验

实验一：数据定义/数据操纵语言

[ 实验日期 ] 年 月 日 [ 实验目的 ]

熟悉Oracle上机环境以及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。

[ 实验内容 ]

Oracle上机环境以及Oracle客户端的配置参见附录。 1． SQL数据定义语句：

例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。

3/42

《数据库概论》实验

4/42

《数据库概论》实验

例1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。

例1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。 例1-4： (建立索引) 为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。为Score表按分数降序建立索引，索引名为GRADE_NDX。

5/42

《数据库概论》实验

例1-5： (删除索引) 删除索引SC_NDX_SNO。

6/42

《数据库概论》实验

例1-6： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。

7/42

《数据库概论》实验

例1-7： (修改数据库表) 删除成绩表Score的参照完整性约束关系。

例1-8： (修改数据库表) 添加成绩表Score的参照完整性约束关系。

2． SQL数据操纵语句：

例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。

8/42

《数据库概论》实验

9/42

《数据库概论》实验

例2-2：(多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表S1中。

10/42

《数据库概论》实验

例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表

Student_Gr中。

例2-4： (修改数据) 将S1表中所有学生的年龄加2。

例2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。

11/42

《数据库概论》实验

例2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。 例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。 例2-8： (删除数据) 删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。 例2-9： (删除数据) 删除Score表中课程号为‘003’ 的成绩信息，根据返回信息解释其原因。 例2-10：(删除数据) 删除学生表S1中学号以96打头的学生信息。

例2-11：(删除数据) 删除数据库表S1中所有学生的数据。

12/42

《数据库概论》实验

例2-12：(删除表) 删除数据库表S1和Student_Gr。 [ 实验要求 ]

① 熟悉Oracle上机环境，掌握Oracle客户端的配置； ② 建立数据库表，修改数据库表结构，建立、删除索引； ③ 对数据库表进行插入、修改和删除数据的操作。 [ 实验方法 ]

① 按照附录中的操作步骤进行客户端的配置； ② 将实验需求用SQL语句表示； ③ 执行SQL语句；

④ 查看执行结果，如果结果不正确，进行修改，直到正确为止。

[ 实验总结 ]

① SQL语句以及执行结果； ② 对重点实验结果进行分析； ③ 实验中的问题和提高； ④ 收获与体会。

实验二：数据查询语言，视图操作

[ 实验日期 ] 年 月 日 [ 实验目的 ]

体会SQL语言数据查询功能的丰富和复杂，视图定义使用。 [ 实验内容 ]

13/42

《数据库概论》实验

3． SQL数据查询语句：

例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。

例3-2： (不选择重复行) 求选修了课程的学生学号。

例3-3： (选择表中的所有列) 求全体学生的详细信息。

14/42

《数据库概论》实验

例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。

例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。

15/42

《数据库概论》实验

例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。

例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。

16/42

《数据库概论》实验

例3-8： (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。

例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。

17/42

《数据库概论》实验

例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。

例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。

18/42

《数据库概论》实验

例3-12：(匹配查询) 求姓名是以“李”打头的学生。 例3-13：(匹配查询) 求姓名中含有“志”的学生。

例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。

例3-16：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。

例3-17：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数。 例3-18：(组函数) 求学生总人数。

例3-19：(组函数) 求选修了课程的学生人数。 例3-20：(组函数) 求计算机系学生的平均年龄。

19/42

《数据库概论》实验

例3-21：(组函数) 求选修了课程001的最高、最低与平均成绩。 例3-22：(分组查询) 求各门课程的平均成绩与总成绩。 例3-23：(分组查询) 求各系、各班级的人数和平均年龄。

例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。

SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=’CS’ GROUP BY SDEPT;

例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO;

例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。 例3-27：(分组查询) 求各系中除01班之外的各班的学生人数。

例3-28：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。

20/42

《数据库概论》实验

例3-29：(自然连接查询) 求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。

例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。

21/42

《数据库概论》实验

例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。

例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩。 例3-34：(子查询) 求与 ‘李丽’ 年龄相同的学生的姓名和系。

例3-35：(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。 例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。

例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。 例3-40：(多个子查询) 求与 ‘李丽’ 同系，且年龄大于 ‘黎明’ 的学生的信息。

22/42

《数据库概论》实验

例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。

例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。 例3-44：(集合查询) 列出所有教师和同学的姓名和性别。 例3-45：(相关子查询) 求未选修课程004的学生的姓名。 例3-46：(相关子查询) 求选修了全部课程的学生的姓名。

例3-47：(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。 例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。 例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。 例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。

23/42

《数据库概论》实验

4． SQL视图的定义与操纵：

例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。

例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。

24/42

《数据库概论》实验

例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。

例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。

25/42

《数据库概论》实验

例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。

例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。 例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。

26/42

《数据库概论》实验

例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。 例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。

[ 实验要求 ]

对数据库表进行各种查询操作。 [ 实验方法 ]

27/42

《数据库概论》实验

① 将实验需求用SQL语句表示； ② 执行SQL语句；

③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。

[ 实验总结 ]

① SQL语句以及执行结果； ② 对重点实验结果进行分析； ③ 实验中的问题和提高； ④ 收获与体会。

实验三：授权控制与事务处理

[ 实验日期 ] 年 月 日 [ 实验目的 ]

通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 [ 实验内容 ]

5． SQL数据控制语句：

例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。 例5-3： (授权) 给所有用户授予Score表上的SELECT权限。

28/42

《数据库概论》实验

例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。 例5-5： (收回授权) 收回上面例子中的所有授予的权限。

6． SQL事务处理：

例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

[ 实验要求 ]

① 建立视图，视图查询，视图更新； ② 给某一或全部用户授权和收回授权； ③ 事务回退，事务提交。 [ 实验方法 ]

① 将实验需求用SQL语句表示； ② 执行SQL语句；

③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。

[ 实验总结 ]

① SQL语句以及执行结果； ② 对重点实验结果进行分析； ③ 实验中的问题和提高； ④ 收获与体会。

附录： 《数据库概论》操作指导

一. 客户端配置

为了使得Oracle和Windows NT 连接起来，必须建立数据库别名（一种连接名称），提供一个和Oracle数据库连接的通路。操作如下：

1. 选择 Windows95&98 开始 -> 程序 -> Oracle for Windows95 -> Oracle net8 easy config 得到如下图所示画面：

29/42

《数据库概论》实验

选择“Yes”按扭，得到下面的画面：

在这个画面上，左边的一组单选按扭是功能选择，含义如下： Add New Service 添加服务名 Modify 修改存在的连接 Delete 删除存在的连接 Test 测试存在的连接

如果选择添加新服务名，需要在右上文本框中输入新服务名（New Service Nami）。如果是其他三种操作，则需要在右下列表框选择其存在的服务名（Exsting Services）。

2. 添加新服务名。在右上文本框中输入一个服务名（如ORA5），然后按“Next”按扭进入下一步。

30/42

《数据库概论》实验

3. 选择通信协议。Oracle数据库与网络是通过指定的协议进行连接的。可选择的画面如下图所示，通常选择TCP/IP协议，单击“Next”按扭，进入下一步。

4. 输入IP地址和端口号。使用TCP/IP协议需要输入IP地址。在Host Name文本框中输入IP地址 （注意：输入哪个服务器地址，需要询问实验指导老师或数据库管理员）。

系统提供一个缺省的端口号，如是1251，可以直接使用它。单击“Next”按扭，进入下一步。

31/42

《数据库概论》实验

5. 系统标识。典型安装的数据库使用的系统标识符号是ORCL，因此可在出现如下图所示的画面之后直接单击“Next”按扭，进入下一步。

6. 测试。可跳过此步，直接单击“Next”按扭，进入下一步。

32/42

《数据库概论》实验

再单击“Finish”按扭，完成配置操作。

说明：由于实验室机器加有保护卡，因此每次重新启动机器，使用ORACLE时，都必须按上述步骤配置服务名（Service Name）。

二. 用户管理

1． 建立用户：

以数据库管理员身份登录Oracle系统（参见 < 四. 登录Oracle >）

按UserName=DXP，PassWord=DXP，Service=ORA5，登录Oracle系统后，输入命令：

CREATE USER Uxxxxxxxx IDENTIFIED BY Kxxxxxxxx

DEFAULT TABLESPACE DXPDATASPACE TEMPORARY TABLESPACE DXPTEMPSPACE;

注意： ① Uxxxxxxxx，用户名；Kxxxxxxxx，口令，是以字母打头的字母数字字符串。 ② ORA5为服务名，即是你所创建的服务名。

33/42

《数据库概论》实验

2． 用户授权。输入命令：

GRANT CONNECT,RESOURCE TO Uxxxxxxxx;

退出Oracle（参见 < 四. 退出Oracle >）。再以新建的用户帐号（用户名和密码）重新登录Oracle。

3． 修改用户密码。

进入Oracle系统后，可以随时修改自己帐户的密码。 ALTER USER Uxxxxxxxx IDENTIFIED BY 新口令；

三. 登录Oracle 方法一:

(1) 在MS-DOS提示符下,执行SQL *PLUS命令

C:> SQLPLUS [RETURN]

SQL *PLUS 将显示它的版本号、日期和版权信息，并提示你输入用户名。 (2) 按提示要求输入用户名后按[RETURN]，SQL *PLUS提示你输入口令。

(3) 如果口令输入正确，SQL *PLUS将宣布你已经与ORACLE连接上了，然后提示： 连接到：

Oracle8 Release 8.0.5.0.0 – Production PL/SQL Release 8.0.5.0.0 - Production SQL> 说明现在你已经进入SQL *PLUS中，并等待你的指示。如下图所示：

输入用户名：U99094180 输入口令：K99094180 SQL提示符

另外，在进入SQL *PLUS时，也可以在命令行上直接输入用户名和口令，中间以“/”分隔。

34/42

《数据库概论》实验

方法二:

(1) 选择 Windows95&98 开始 -> 程序 -> Oracle for Windows95 -> SQL PLUS 8.0 出现Oracle登录窗口。

(2) 按提示要求输入： Username： Uxxxxxxxx Password: Kxxxxxxxx Service: ORA5 其中： Uxxxxxxxx，Kxxxxxxxx，ORA5同前说明。

例如：某一学生学号为99094180，则其用户名为 U99094180，口令为K99094180，数据库别名设为ORA6。

(3) 按 [确定] 按钮。如果输入正确，SQL *PLUS 将显示它的版本号、日期和版权信息以及已与ORACLE连接上的信息，然后显示：

连接到：

Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> SQL 提示符

说明现在你已经进入SQL *PLUS中，并等待你的指示。

注意：在方法一或方法二中，如果口令输入不正确，系统提示出错信息，并要求你重新输入。在向ORACLE三次注册失败后，屏幕上将出现终止服务信息，并退出SQL *PLUS。

35/42

《数据库概论》实验

四. 退出Oracle

当想停止工作并离开SQL *PLUS，则可以在SQL *PLUS命令提示符下输入EXIT或QUIT命令。

SQL> EXIT [RETURN] 或 SQL> QUIT [RETURN]

五. 提示信息

1. 建议使用方法二登录ORACLE。在此方式下，SQL *PLUS有一种缓冲区编辑器，可以用来在SQL >提示符下交互式地输入命令和修改命令。

(1) 在SQL>提示符下，可直接进行拷贝和粘贴操作。 (2) 在SQL>提示符下，可直接对命令缓冲区内容进行编辑。 SQL> ED[RETURN]

出现缓冲区编辑器窗口，自动显示刚刚执行的命令语句。修改编辑，保存退出，返回SQL> 命令提示符状态。

SQL>/[RETURN]

自动执行缓冲区编辑器中的命令语句。

2. 一个事务从执行一条SQL更新语句(INSERT、UPDATE、DELETE)开始，当出现如下任一情况时，就结束该事务：

? 执行COMMIT或ROLLBACK命令(这是一种显式划分事务的方式)； ? 发出DDL命令(如执行CREATE TABLE 命令等)； ? LOGOFF； ? 程序异常结束。

如果在SQL *PLUS中没有上述四种情况，当退出系统时，系统会自动提交最后一个事务。 3. 查询本用户账号下的所有数据库表的名称： SELECT * FROM TAB； 4. 查看一个数据库表的表结构： DESC 表名；

5. ORACLE常用的数据类型： 数据类型 数字型 表示方式 NUMBER(n) NUMBER(n,d) CHAR(n) 说 明 可包括数字0-9、正负号(+、-)和十进制小数点 n: 总有效位数(范围1-38) s: 小数点后面的位数 可包含字母、数字和特殊字符(+、-、%、$) n <= 255； n1 <= 2000 日期和时间。存储方式为： 字符型 VARCHAR(n) VARCHAR2(n1) 日期型 DATE 世纪、年、月、日、时、分、秒。缺省日期格式： 中文版为 09-10月-99，英文版为09-OCT-99。 利用ORACLE提供的日期掩码格式，可按需要显示日期与时间。例如显示系统日期或显示系统日期、时间：

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL；

或 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM DUAL；

36/42

《数据库概论》实验

6. 几个常用的ORACLE函数

(1) LTRIM()、RTRIM()、TRIM()：去除前导、后缀的空格或字符。 (2) UPPER()、LOWER()：字符大小写转换。

(3) SUBSTR(str，m，n)：截取子串。其中，str：字符串；m：开始位置；n：长度。

7. 在Oracle中，可以使用SET AUTOCOMMIT命令来设置事务自动提交环境。一旦设置了自动提交，则在每个SQL操作INSERT、UPDATE或DELETE语句之后，系统就自动进行了提交。一般情况下，最好不要使用自动提交方式。

设置自动提交的语句格式： SET AUTOCOMMIT IMMEDIATE; SET AUTOCOMMIT ON; 设置非自动提交的语句格式： SET AUTOCOMMIT OFF;

Oracle以非自动提交方式为缺省方式。

8. 查询其他用户的数据（必须为授权的用户），应在表名前增加用户名作为限定词。格式为：

用户名.表名

9. 查询本用户所有表上的完整性约束名称。

SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name

FROM User_constraints;

Constraint Type PRIMARY KEY UNIQUE KEY FOREIGN KEY CHECK, NOT NULL Character P U R C

10. 查询本用户所有表上的非空约束和CHECK约束条件。 SELECT Constraint_name, Search_condition FROM User_constraints;

11．查询本用户所有表上定义的完整性约束的约束列名

SELECT Constraint_name, Table_name, Column_name FROM User_cons_columns;

37/42

《数据库概论》实验

六. 教学管理数据库设计 各表的数据结构和数据如下：

1． 学生情况表 Student

SNO(C,5) SNAME(C,10) SDEPT(C,2) SCLASS(C,2) SSEX(C,2) SAGE(N,2) 学号 姓名 系 班级 性别 年龄 96001 马小燕 96002 黎明 96003 刘东明 96004 赵志勇 97001 马蓉 97002 李成功 97003 黎明 97004 李丽 CS 01 CS MA IS CS IS CS 01 01 02 01 03 02 02 女 男 男 女 男 女 女 男 21 18 20 19 20 19 19 18 CS：计算机系 MA：数学系 IS：信息系

男 18 MA 02 96005 司马志明 CS 主关键字：SNO 非空字段：SNAME，SDEPT，SCLASS

2． 课程名称表 Course 3． 教师授课表 Teach

CNO(C,3) CNAME(C,16) CTIME(N,3)

学时数 课程号 课程名称 TNAME(C,8) TSEX(C,2) CNO(C,3) TDATE 教师姓名 性别 课程号 授课日期 TDEPT(C,2) 系 001 数学分析 144 002 普通物理 144 003 微机原理 80 004 数据结构 72 005 操作系统 80 006 数据库原理 80 007 编译原理 60 008 程序设计 40 王成刚 李正科 严敏 赵高 李正科 刘玉兰 王成刚 马悦 男 男 女 男 男 女 男 女 004 003 001 004 003 006 004 008 1999.9.5 CS 1999.9.5 CS 1999.9.5 MA 1999.9.5 IS 2000.2.23 MA 2000.2.23 CS 2000.2.23 IS 2000.9.6 CS 主关键字：CNO 候选关键字：CNAME 主关键字：TNAME，CNO，TDEPT 参照关系：Course(CNO)

4． 成绩表 Score

SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) 学号 课程号 分数 学号 课程号 分数 学号 课程号 分数 96001 001 77.5 96001 003 89 96001 004 86 96001 005 82 96002 001 88 96002 006 90 96005 004 92 96005 005 90 96005 006 89 96005 007 76 96003 001 69 97001 008 95 96004 001 87 96003 003 91 97002 003 91 97002 004 97002 006 92 97004 005 90 97004 006 85 96002 003 92.5 97001 001 96 主关键字：SNO，CNO 参照关系：Student(SNO)，Course(CNO)

38/42

《数据库概论》实验

七. SQL语句

1． SQL数据定义语句：

例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。

例1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。

例1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。 例1-4： (建立索引) 为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。为Score表按分数降序建立索引，索引名为GRADE_NDX。 例1-5： (删除索引) 删除索引SC_NDX_SNO。

例1-6： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。 例1-7： (修改数据库表) 删除成绩表Score的参照完整性约束关系。 例1-8： (修改数据库表) 添加成绩表Score的参照完整性约束关系。

2． SQL数据操纵语句：

例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。 例2-2：(多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表S1中。

例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表

Student_Gr中。

例2-4： (修改数据) 将S1表中所有学生的年龄加2。

例2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。 例2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。 例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。 例2-8： (删除数据) 删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。 例2-9： (删除数据) 删除Score表中课程号为‘003’ 的成绩信息，根据返回信息解释其原因。 例2-10： (删除数据) 删除学生表S1中学号以96打头的学生信息。 例2-11： (删除数据) 删除数据库表S1中所有学生的数据。 例2-12：(删除表) 删除数据库表S1和Student_Gr。

3． SQL数据查询语句：

例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。 例3-2： (不选择重复行) 求选修了课程的学生学号。 例3-3： (选择表中的所有列) 求全体学生的详细信息。 例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。

39/42

《数据库概论》实验

例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。

例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。 例3-8： (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。 例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。 例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。 例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。 例3-12：(匹配查询) 求姓名是以“李”打头的学生。 例3-13：(匹配查询) 求姓名中含有“志”的学生。

例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。

例3-16：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。

例3-17：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数。 例3-18：(组函数) 求学生总人数。

例3-19：(组函数) 求选修了课程的学生人数。 例3-20：(组函数) 求计算机系学生的平均年龄。

例3-21：(组函数) 求选修了课程001的最高、最低与平均成绩。 例3-22：(分组查询) 求各门课程的平均成绩与总成绩。 例3-23：(分组查询) 求各系、各班级的人数和平均年龄。

例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。

SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=’CS’ GROUP BY SDEPT;

例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO;

例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。 例3-27：(分组查询) 求各系中除01班之外的各班的学生人数。

例3-28：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。 例3-29：(自然连接查询) 求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。

例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。 例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。

例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩。

40/42

《数据库概论》实验

例3-34：(子查询) 求与 ‘李丽’ 年龄相同的学生的姓名和系。

例3-35：(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。 例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。

例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。 例3-40：(多个子查询) 求与 ‘李丽’ 同系，且年龄大于 ‘黎明’ 的学生的信息。 例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。

例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。 例3-44：(集合查询) 列出所有教师和同学的姓名和性别。 例3-45：(相关子查询) 求未选修课程004的学生的姓名。 例3-46：(相关子查询) 求选修了全部课程的学生的姓名。

例3-47：(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。 例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。 例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。 例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。 4． SQL视图的定义与操纵：

例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。

例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。 例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。

例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。 例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。 例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。 例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。 例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。 例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。

41/42

《数据库概论》实验

5． SQL数据控制语句：

例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。 例5-3： (授权) 给所有用户授予Score表上的SELECT权限。 例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。 例5-5： (收回授权) 收回上面例子中的所有授予的权限。

6． SQL事务处理：

例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

（附：可以利用业余时间将教材中的习题P.148的第4、第5题作为上机练习）

起草：韦伟 审核： 日期：

42/42

本文来源：https://www.bwwdw.com/article/mbyp.html