数据定义及更新语句练习

 用SQL语句建立mySPJ数据库，包括S，P，J，和SPJ四个基本表(参见实验指导

书)，要求实现关系的三类完整性

 分别使用插入、删除、修改语句更新基本表中的数据 实验方法和步骤

 建立数据库mySPJ

 打开“开始－>程序－>Microsoft SQL Server－>企业管理器”

 在企业管理器左边的树标签中依次打开“Microsoft SQL Server－> SQL

Server组－>(local)(Windows NT)－>数据库”， (local)(Windows NT)前的红色标记转化为绿色标记表明SQL Server服务已启动  从企业管理器的“工具”菜单中选择“SQL 查询分析器”，在其窗口书写创建数

据库mySPJ的语句，点击执行按钮（或F5键）执行该SQL语句  在企业管理器左边的树标签中查看数据库是否建立成功  注意可以直接在企业管理器中创建数据库

 在mySPJ中建立S,P,J和SPJ四个基本表

 在查询分析器中使用CREATE TABLE语句建立各表，注意设置主键外键约

束

 注意主键外键约束的建立有两种方法

可以通过SQL语句建立 也可以在企业管理器中设置 比较两种用法

 更新语句的使用

 使用Insert 语句将习题中给出的示例记录插入各表  使用Update语句更新表中的记录

 将p表中的所有红色零件的重量增加5

 将spj表中所有天津供应商的QTY属性值减少10

 使用Delete语句删除p表中的所有红色零件的记录  SQL语句执行后返回基本表查看更新后的结果，若与期望不符，分析原因并

记录在实验报告中

重点难点

 重点

 CREATE TABLE 语句以及INSERT、 UPDATE、 DELETE语句的使用  表中各字段数据类型和长度的选择

 三类完整性的设置用SQL语句如何实现

 难点

 关于外键及如何保证参照完整性的理解  违反各类完整性时系统如何处理  级联更新和级联删除的使用

注意事项

 注意在企业管理器中可以直接创建数据库、表，插入、删除、修改记录以及设置主

键外键约束

 本实验的目的是掌握数据定义及更新语句，故要求在查询分析器中通过SQL语句实

现实验内容

1

实验二 简单查询和连接查询 实验内容

 完成下面的简单查询

①查询所有“天津”的供应商明细；

②查询所有“红色”的14公斤以上的零件。 ③查询工程名称中含有“厂”字的工程明细。

 完成下面的连接查询

①等值连接：求s表和j表的相同城市的等值连接。

②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应商、工程、零件排序。

③笛卡尔积：求s和p表的笛卡尔积 ④左连接：求j表和spj表的左连接。 ⑤右连接：求spj表和j表的右连接。 实验方法和步骤

 在Microsoft SQL Server中打开实验一建立的mySPJ数据库  在Microsoft SQL Server查询分析器中设计并执行SQL语句  记录执行结果，若有错误，分析原因并改正 重点难点

 重点

 简单SELECT 语句使用  自然连接的使用

 难点

 自然连接的实现过程

 左外连接和右外连接的区别与联系

注意事项

 实验过程中对有疑问的语句要多实践

 尝试多种方法来解决同一问题，巩固所学理论知识 例如：查询所有“天津”的供应商明细 方法1：

SELECT * FROM S WHERE CITY=„天津‟ 方法2：

SELECT SNO,SNAME,CITY,STATUS FROM S WHERE CITY=„天津‟ 部分实验结果

①等值连接：求s表和j表的相同城市的等值连接

②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应商、工程、零件排序。

2

实验三 分组查询和嵌套查询 实验内容 分组查询

 求各种颜色零件的平均重量。

 求北京供应商和天津供应商的总个数。  求各供应商供应的零件总数。

 求各供应商供应给各工程的零件总数。  求使用了100个以上P1零件的工程名称。  求各工程使用的各城市供应的零件总数。 嵌套查询

 in连接谓词查询

 查询没有使用天津供应商供应的红色零件的工程名称 查询供应了1000个以上零件的供应商名称(having)

 比较运算符查询

 求重量大于所有零件平均重量的零件名称

 Exists连接谓词

 查询供应J1的所有的零件都是红色的供应商名称  至少用了供应商S1所供应的全部零件的工程号JNO

实验方法和步骤

 在Microsoft SQL Server中打开实验一建立的mySPJ数据库  在Microsoft SQL Server查询分析器中设计并执行SQL语句  记录执行结果，若有错误，分析原因并改正 重点难点

 重点

 分组查询及集函数的使用  嵌套查询

 难点

3

 GROUP BY子句的使用，HAVING短语的使用  EXISTS量词的使用

关于分组

 GROUP BY 子句将查询结果按某一列或多列分组，值相等的为一组。

 对查询结果分组的目的是为了细化集函数的作用对象，分组后每个组都有一个函数

值

 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，

则可以是使用HAVING短语指定筛选条件

回顾：分组实例

 例 求各个课程号及相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC

GROUP BY Cno;

 例 查询选修了3门以上课程的学生学号 SELECT Sno FROM SC

GROUP BY Sno

HAVING COUNT(*)>3; 关于集函数

 Select count(*) from spj

得到表spj的记录数

 select count(sno) from spj

得到表spj 中sno字段非空记录数  select count(distinct sno) from spj

得到表spj 中sno字段值非相同记录数

部分实验结果

 查询没有使用天津供应商供应的红色零件的工程名称

 查询供应了1000个以上零件的供应商名称

 求重量大于所有零件平均重量的零件名称

 查询供应J1的所有的零件都是红色的供应商名称

4

 至少用了供应商S1所供应的全部零件的工程号JNO

实验四 视图操作和数据控制 实验内容

 定义如下视图

 查询北京的供应商的编号、名称和城市  查询S1供应商的所有供应明细

 查询各工程名称使用的各种颜色零件的个数

 查询上面定义的视图

 数据控制（GRANT 和REVOKE语句）

 使用GRANT把对S表查询的权利授予WangLi。

 使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。  使用REVOKE把LiMing对P表插入、删除的权利回收。  实现习题四(P148)中第8，9题

实验方法和步骤

 在Microsoft SQL Server中打开实验一建立的mySPJ数据库  在Microsoft SQL Server查询分析器中设计并执行SQL语句

 注意视图创建后只能看到视图的定义，语句并不执行，视图定义后可以像基本表一

样使用

 注意数据控制语句使用前必须先创建用户

 授权之后执行相应的SQL语句查看验证授权语句是否正确执行  记录执行结果，若有错误，分析原因并改正 重点难点

 视图的创建和查询  理解视图的执行过程

 验证SQL SERVER中哪些视图可以更新

 授权后对授权语句的验证以及收回授权后的验证

视图部分实验结果

 查询北京的供应商的编号、名称和城市

 查询S1供应商的所有供应明细

 查询各工程名称使用的各种颜色零件的个数

5

实验五 触发器和存储过程 实验内容

 触发器和存储过程

有一个小型的图书管理数据库，包含的表为：

bookstore（bookid，bookname，bookauthor，purchasedate，state）； --图书库存表

borrowcard（cardid，ownername）；--借书证表

borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表 写一个存储过程，实现借书操作，要求有事务处理。（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。 （2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。（3）要求用触发器实现表的完整性控制。 实验方法和步骤

 触发器

 在Microsoft SQL Server中打开实验一建立的mySPJ数据库  在查询分析器中编写触发器，参考CREATE TRIGGER语句

 存储过程

 创建图书借阅数据库，注意保证三类完整性

 在查询分析器中编写存储过程，参考CREATE PROCEDURE语句

 参考事务的开始和结束语句 ，并在事务执行过程中引入错误触发事件，可

以考虑在增加借书记录时违反实体完整性或参照完整性，以此体会事务的错误保护机制

重点难点

 重点

 触发器的初步使用  存储过程的初步使用

 难点

 存储过程中事务的使用

 如何通过系统的设置实现类似的功能，而不需触发器，比较两者的执行结果

6