《数据库原理与应用》
课程实验指导书
苏州工业职业技术学院信息工程系
2008．1
目录
目录………………………………………………………………………………………Ⅰ 前言……………………………………………………………………………………… 实验一 初识SQL Server 2000………………………………………………… 实验二 数据库的创建和管理………………………………………………… 实验三 表的创建、管理及数据操作…………………………………………… 实验四 单表查询…………………………………………………………… 实验五 连接查询………………………………………………………… 实验六 嵌套查询…………………………………………………… 实验七 视图的创建和管理…………………………………………………… 实验八 游标的使用…………………………………………………… 实验九 T-SQL语言编程基础………………………………………………… 实验十 函数………………………………………………………………………… 实验十一 索引、默认值约束和默认值对象……………………………… 实验十二 数据完整性的实现………………………………………………… 实验十三 存储过程………………………………………………… 实验十四 触发器………………………………………………… 实验十五 系统安全管理………………………………………………… 实验十六 数据备份、恢复和导入导出……………………………………… 实验十七 综合训练…………………………………………………
前言
数据库技术是计算机学科中的一个重要分支，发展迅速、应用非常广泛，几乎涉及了所有应用领域。例如，办公系统、生产管理、财务管理、人事管理、工业管理等，都广泛应用了数据库技术。
本实验指导书是《数据库原理与应用》课程的配套实验资料。通过安排实验及布置的任务，让学生熟练掌握使用关系数据库管理系统SQL Server 2000进行数据库及表的创建和管理、查询、Transact—SQL程序设计、各类约束的创建及使用、视图及索引的创建与管理、SQL Server 的存储过程的创建和管理、SQL Server 的触发器创建和管理、SQL Server的安全性管理、数据库的备份及恢复。并能根据实际应用需求进行数据库设计和实现，提高学生的实际动手能力，为其今后在相关领域学习和工作打下较好的基础。
实验一 初识SQL Server 2000
一．实验目的与要求
1、熟悉实验机房对SQL Server 2000实验环境的设置、学会独立使用该系统； 2、熟悉SQL Server 2000中企业管理器和查询分析器两个常用管理工具；
二．实验内容
?
练习启动、停止和暂停服务管组件的服务，了解SQL Server2000中包括的服务器组件，掌握服务管理器和使用。
练习Microsoft SQL Server Enterprise Manager的使用。 练习Microsoft SQL 查询分析器的使用。
? ?
三．实验步骤
1、练习启动、停止和暂停服务管组件的服务，了解SQL Server2000中包括的服务器组件，掌握服务管理器和使用。 1) 启动SQL Server服务
2) 停止SQL Server服务
2、练习Microsoft SQL Server / Enterprise Manager 打开Microsoft SQL Server / Enterprise Manager 1） 观察SQL Server 组下已经建立的服务器组 2） 打开其中的一个服务器，观察其中的文件夹 3） 打开“数据库”文件夹，观察已经建立的数据库 4） 打开其中的某一个数据库，观察其中的内容
5） 打开“安全性”文件夹，进入“登录”，观察可以登录该服务器的用
户及其属性。 3、练习Microsoft SQL 查询分析器
打开Microsoft SQL查询分析器，在窗口中输入SELECT * FROM Products并单击（或按F5）执行查询，认识查询分析器的命令操作方式
四．分析与回答
1．SQL Server 2000中包含哪些组件，其功能各是什么？
2．画出SQL Server Enterprise Manager控制台目录结构？ 答：
3．DBMS的作用是什么？SQL Server 2000的体系结构是怎样的？
4．SQL Server 2000中联机交互方式有哪两种，分别是通过什么管理工具来体现？ 答：
实验二 数据库创建与管理
一、实验目的与要求
? 熟练掌握SQL Server Enterprise Manager 创建和管理数据库； ? 熟练掌握使用SQL语句创建和管理数据库 ? 熟练掌握备份和还原数据库
二、实验内容和步骤
1、利用企业管理器创建和管理数据库 (1) 创建数据库
ⅰ展开服务器组，然后展开服务器
ⅱ右击\数据库\，然后单击\新建数据库\命令
ⅲ键入新数据库的名称student，同时用指定的数据库名student作为前缀创建主数据库
和事务日志文件，例如：student_Data.mdf 和 student_Log.ldf
ⅳ要更改新建主数据库文件的默认值，单击\常规\选项卡。若要更改新建事务日志文件的默认值，单击\事务日志\选项卡”， 要更改\文件名\、\位置\、\初始大小（MB）\和\文件组\等列的默认值，单击要更改的适当单元格，再键入新值。要指定文件增长方式和文件大小按界面提示选择相应的项。
(2) 删除数据库
选择student数据库，右击鼠标选择“删除”命令即可。 2、利用企业管理器备份和还原数据库
(3) 备份数据库
选择student数据，右击鼠标选择“所有任务|备份数据库”命令，在弹出对话框中输入备份名称，选择“备份到”栏目中的“添加”命令，输入具体的文件名为位置，默认情况备份文件存放在SQL Server的安装目录中如“C:\\Program Files\\Microsoft SQL Server\\MSSQL\\BACKUP\\”；当需要将备份文件存放到自定义文件夹中时，修改上述默认路径。
(3) 还原数据库
选择student数据，右击鼠标选择“所有任务|还原数据库”命令，在对话框中会列出已备份过的文件记录，输入相应的选项值按确定即可。
3、 使用SQL语句在查询分析器中完成以下操作
(1)创建SPJ数据库：打开查询分析器，输入以下语句并运行
CREATE DATABASE SPJ
ON (NAME = 'SPJ_Data', FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\SPJ_Data.MDF' , SIZE = 2, MAXSIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = 'SPJ_Log', FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\SPJ_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
(2)修改SPJ数据库：在查询分析器中输入以下语句并运行(了解)
ALTER DATABASE SPJ
MODIFY FILE(NAME='SPJ_Data',SIZE=3,MAXSIZE=UNLIMITED) ALTER DATABASE SPJ
ADDFILE(NAME='SPJ_Data_2',FILENAME='C:\\ProgramFiles\\MicrosoftSQLServer\\MSSQL\\Data\\SPJ_Date_2.ndf',SIZE=1, MAXSIZE=10,FILEGROWTH=10%)
(3)删除SPJ数据库： DROP DATABASE SPJ
三、分析与回答
1 在企业管理器中实现数据库XSBOOK的操作，写出操作过程。
2 在查询分析器中实现XSBOOK，写出操作过程。
实验三 表的创建、管理及数据操作
一、实验目的与要求
? 复习使用企业管理器和T-SQL语句创建和管理数据库。 ? 熟练掌握使用企业管理器和T-SQL语句创建、修改和删除表。 ? 熟练掌握使用企业管理器和T-SQL语句插入、修改和删除表数据。
二、实验内容和步骤
1、启动企业管理器，创建产品销售数据库CPXS：
数据文件初始大小为5MB，最大大小50MB，增长方式按10%比例增长；日志文件初始为2MB，最大可增长到10MB，按2MB增长；其余参数取默认值。
2、用T-SQL语句将CPXS数据文件的增长方式改为按5MB增长。
3、CPXS数据库包含如下三个表：
CP（产品编号，产品名称，价格，库存量）产品
XSS（客户编号，客户名称，地区，负责人，电话）销售商
CPXSB（产品编号，客户编号，销售日期，数量，销售额）产品销售表 三个表结构如图3.1～图3.3所示，请写出创建以上三个表的T-SQL语句并在查询分析器中运行。
图3.1 CP表结构
图3.2 XSS表结构
图3.3 CPXSB表结构
4、在企业管理器中输入如图3.4～图3.6的CP表、XSS表和CPXSB表的样本数据。
图3.4 CP表的样本数据
图3.5 XSS表的样本数据
图3.6 CPXSB表的样本数据
4、 用T-SQL语句向CP表插入如下记录：
200001 200002 200003 柜式空调 微波炉 抽油烟机 3000 1000 1200 200 100 50 5、用T-SQL语句向CP表中增加“产品简列”列，varchar(50)，允许为NULL。 6、将CP表中每种商品的价格打8折。
7、将CP表中价格打9折后小于1500的商品删除。
实验四 单表查询
一、实验目的与要求
? 牢记SELECT 语句的基本语法格式；
? 熟练掌握使用SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、HAVING 子句和集函数；
二、实验内容和步骤
对CPXS数据库，完成如下单表查询： 1、
简单查询 (将你完成正确的语句写在横线上) ? 查询各种产品的产品编号、产品名称和价格。
____________________________________________________________ _____________________________________________________________ ? 查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别指定为：Customer id和Customer name。
____________________________ _________________________________ _____________________________________________________________ ? 查询CP表中各种产品的产品编号、产品名称和价格，对其价格按以下规则进行转换；若价格小于1000，替换为“廉价产品”，若价格在1000-2000之间，替换为“一般产品”，若借书数在大于2000小于3000，替换为“昂贵产品”，若借书数大于3000，替换为“很昂贵产品”，列标题更改为“评价”。
_____________________________________________________________ _____________________________________________________________ _____________________________________________________________ ? 求各产品编号、名称和产品总值。
_____________________________________________________________ _____________________________________________________________ ? 查询至少购买了至少一种产品的客户编号（不重复）。
_____________________________________________________________ _____________________________________________________________ ? 查询价格在1000-2000的产品信息。
_____________________________________________________________ _____________________________________________________________ ? 查询产品名称含有”冰箱_A”的产品情况。
_____________________________________________________________ _____________________________________________________________ 2、使用分组和集函数
先将CPXSB表数据修改如下图所示：
再完成如下查询：
? 计算所有产品总价格。
_____________________________________________________________ _____________________________________________________________ ? 求各种产品20XX年3月18日销售额。
_____________________________________________________________ _____________________________________________________________ ? 求购买二种以上产品的客户编号。
_____________________________________________________________ _____________________________________________________________
三、注意事项
1、在上实验课前需预习！！！
2、每次查询完成需观察结果，并分析其正确性！
实验五 连接查询
一、实验目的与要求
?
熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。
二、实验内容和步骤
对CPXS数据库，完成如下连接查询：
1、
内连接 (将你完成的语句写在横线上，要求用两种方式书写) ? 查询在20XX年3月18日有销售的产品名称（不允许重复）。 SQL标准语句：
_____________________________________________________________ _____________________________________________________________ T-SQL扩展语句：
_____________________________________________________________ _____________________________________________________________
? 查询名称为“家电市场”的客户在20XX年3月18日购买的产品名称和数量。 SQL标准语句：
____________________________________________________________ _____________________________________________________________ T-SQL扩展语句：
____________________________________________________________ _____________________________________________________________ 2、外连接
? 查找所有产品情况及销售他们的销售日期、购买他们的客户编号和数
量，若产品没有销售记录，也要包括其情况。
_____________________________________________________________ _____________________________________________________________ ? 查找所有购买情况，若客户没有购买产品，也要包括其情况。
_____________________________________________________________ _____________________________________________________________
三、注意事项
1、在上实验课前需复习并写出相应的查询语句！！！ 2、每次查询完成需观察结果，并做适当分析！
实验六 嵌套查询
一、实验目的与要求
熟练掌握IN子查询
? 熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替） ? 熟练掌握EXISTS子查询（尤其要注意如何将全称量词和逻辑蕴含用
EXISTS谓词代替）
?
二、实验内容和步骤
对CPXS数据库，完成如下嵌套查询：
? 查询在20XX年3月18日没有销售的产品名称（不允许重复）。 用IN子查询：
_____________________________________________________________ _____________________________________________________________ 用EXISTS子查询：
_____________________________________________________________ _____________________________________________________________ ? 查询名称为“家电市场”的客户在20XX年3月18日购买的产品名称和数量。 用IN子查询：
____________________________________________________________ _____________________________________________________________ 用EXISTS子查询：
____________________________________________________________ _____________________________________________________________
? 查询销售量大于所有20XX年3月18日销售的各产品销售数量的产品
编号。
用ALL谓词：
_____________________________________________________________ _____________________________________________________________ 用集函数：
_____________________________________________________________ _____________________________________________________________
? 查询购买了所有产品的客户的名称。
_____________________________________________________________ _____________________________________________________________ ? 查询购买了客户编号为“000001”的客户购买的所有产品的客户的名
称。
_____________________________________________________________ _____________________________________________________________
三、注意事项
1、在上实验课前需复习并写出相应的查询语句！！！ 2、每次查询完成需观察结果，并做适当分析！
实验七 视图的创建和管理
一、实验目的与要求
? 熟练掌握使用企业管理器和T-SQL语句创建、查询、更新、修改和删除
视图。 ? 进一步掌握视图与基本表的联系与区别。
二、实验内容和步骤
1、创建视图
（1）启动企业管理器，在产品销售数据库CPXS中创建价格小于2000的产品视图VIEW_CP_PRICE2000，要求加密并保证对该视图的更新都要符合价格小于2000这个条件，写出创建过程和对应的T-SQL语句：
（2）打开查询分析器，用T-SQL语句创建各客户购买产品的情况VIEW_GMQK视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。
（3）创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号小于等于’100010’产品数据，CP1表中为编号大于‘100010’产品数据，以分区列为产品编号， 创建可更新的分区视图VIEW_CP12。
2、查询视图
（1）基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、名称和价格。
（2）基于VIEW_GMQK视图，查询各客户在20004年3月18日购买产品的情况。
3、更新视图
利用T-SQL语句对于视图VIEW_12进行以下数据更新。 （1） 插入一条CP记录（’'100042','数码相机',3500,2）。
（2） 将产品编号为’100042’的价格改为3000。
（3） 删除产品编号为’100042’的产品。
4、修改视图
将VIEW_CP_PRICE2000视图不加密。
5、删除视图
将VIEW-GMQK视图删除。
三、分析与回答
视图与基本表的区别与联系。
实验八 游标的使用
一、实验目的与要求
? 掌握使用SQL-92语法和T-SQL扩展声明游标，并理解相关参数含义。 ? 掌握游标的使用过程。
二、实验内容和步骤
1、声明游标
（1）使用SQL-92语法声明一只进只读游标CUR1：要求结果集20XX年3月18日销售情况。
（2）使用T-SQL扩展声明一滚动动态游标CUR2：要求结果集为客户信息，并能通过该游标修改客户名称列。
2、打开游标
打开CUR2游标。
3、读取游标中数据
编写程序，实现依次读取游标CUR2中各行数据。
4、关闭游标
关闭CUR2游标。
5、释放游标
释放CUR2游标。
三、分析与回答
1．什么是游标？为什么要使用游标？
2．对游标的操作语句有哪些？分别代表什么含义？
实验九 T-SQL语言编程基础
一、实验目的与要求
? 熟练掌握变量的定义和赋值。
? 熟练掌握用户自定义数据类型定义、使用和删除。 ? 熟练掌握各种运算符。
? 熟练掌握流程控制语句，尤其是条件语句和循环语句。 ? 熟悉并掌握常用的系统函数。
二、实验内容和步骤
1、变量的定义和赋值
创建一名为 Customer_name的局部变量，并在SELECT语句中使用该变量查找“广电公司”购买产品的情况。
2、用户自定义数据类型定义、使用和删除
用SQL命令定义一名为Customer_id的用户自定义数据类型，要求char(6)，NOT NULL，并把该自定义数据类型用来定义XSS表中的客户编号，然后删除该自定义数据类型，请叙述该过程，并写出相关语句。
3、T-SQL语言编程
（1）用T-SQL语言编程输出3～300之间能被7整除的数。
（2）用T-SQL语言编程输出3～100之内的素数。
三、分析与回答
1 在SQL Server 2000中，有哪些运算符？试举例说明你认为较为常用的运算符的用法。（不少于3个）
2 试举例说明我们较为常用的系统内置函数。（不少于3个）
实验十 函数
一、实验目的与要求
? 理解什么是标量函数、内嵌表值函数及多语句表值函数。
? 熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调
用。
二、实验内容和步骤
1、函数的定义
对于CPXS数据库，定义完成如下功能的函数：
（1）据产品名称，查询该产品的相关信息；（函数名为FU_CP）
（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）
2、函数的调用
（1）对函数FU_CP，查询产品名称为“mp3”的产品情况；
（2）对函数FU1_CPXS，查询20XX年第3季度彩色电视机的销售数量和销售金额；
（3）对函数FU2_CPXS，查询20XX年第1季度洗衣机的销售数量和销售金额；
（4）对函数FU3_CPXS，查询广电公司20XX年第1季度销售
的产品名称、销售数量和销售金额。
三、分析与回答
试说明内嵌表值函数、多语句表值函数的联系与区别。
实验十一 索引、默认值约束和默认值对象
一、实验目的与要求
? 理解索引的概念和作用。
? 熟练掌握索引的创建与删除。 ? 熟练掌握默认值约束的定义和删除。 ? 熟练掌握默认值对象的定义、使用和删除。
二、实验内容和步骤
对于CPXS数据库，完成如下操作：
1、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。
（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、
产品编号、产品名称、价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。（请注意7个SET
选项设置）。
2、索引的删除
删除ind_view1索引。；
3、默认值约束的定义
对库存量字段添加默认值为0的约束def_kcl。
4、默认值约束的删除 删除def_kcl默认值约束。
5、默认值对象的定义、使用和删除
（1）定义一值为0的默认值对象kcl_def，并将其绑定到CP表的库存量字段；然后再删除此默认值对象，请叙述该过程，并写出相关SQL语句。
（2） 用SQL命令定义一名为city的用户自定义数据类型，要求char(6)，NULL，再定义一值为“北京”的默认值对象city_def，然后将city_def默认值对象绑定到city自定义数据类型，最后删除city_def默认值对象，请叙述该过程，并写出相关SQL语句。
三、分析与回答
（1） 说明索引的概念和作用。
（2） 说明聚簇索引和非聚簇索引的含义和区别。
实验十二 数据完整性的实现
一、实验目的与要求
? 熟练掌握域完整性的实现。 ? 熟练掌握实体完整性的实现。 ? 熟练掌握参照完整性的实现。
二、实验内容和步骤
对于CPXS数据库，删除CP表、CPXSB表、XSS表上已有所有约束：
1、实体完整性的实现
（1）对CP表、CPXSB表、XSS表，定义主键约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。
（2）在CP表的产品名称列定义一个唯一约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。
2、参照完整性的实现
（1）利用企业管理器建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，从表CPXSB采用NO ACTION方式，写出其过程。
（2）利用SQL语句建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，从表CPXSB采用CASCADE（级联）方式，写出其过程。
3、域完整性的实现
（1）在CP表的价格列上定义大于等于0的检查（CHECK）约束。
（2）定义一个大于等于0的规则check_kcl，并将其绑定到CP表的库存量列，然后解除绑定，删除该规则，请写出相关SQL语句。
4、综合训练
创建一个“学生档案”数据库，并用CREATE TABLE在该数据库中创建“人事”表和“成绩”两个表，其中“人事”表包括学号、姓名、性别、电话号码、家庭住址字段组成。“成绩”表包括学号、选修课程名称、成绩三个字段。分别在“人事”表的学号字段设置主键约束，在“成绩”表的学号字段设置外键约束，在成绩字段设置大于等于0小于等于100的检查约束，在家庭住址字段设置惟一约束，在性别字段设置默认值为“男”。
三、分析与回答
在SQL Server中讨论参照完整性，请体会如下含义： ? 插入从表中记录时：有限制和忽略两种方式；
? 删除主表中的记录时：有限制、忽略和级联三种方式； ? 修改主表中的主键时：有限制、忽略和级联三种方式。 试根据自身学习，分别说明其含义。
实验十五 系统安全管理
一、实验目的与要求
? 理解SQL Server中的安全管理机制。
? 理解帐户、用户、服务器角色和数据库角色的含义。
? 熟练掌握Windows NT登录帐户和SQL Server登录帐户的建立与
删除。
? 熟练掌握添加与删除服务器角色成员。
? 熟练掌握添加和删除一个登录帐户为某个数据库的用户。 ? 熟练掌握添加和删除数据库角色。 ? 熟练掌握添加和删除数据库角色成员。
? 熟练掌握用户、角色的授权、收回权限和拒绝权限。
二、实验内容和步骤
1、创建Windows NT登录帐户
在SQL Server中添加一个帐户名为“WANG”的Windows NT登录帐户。
2、创建SQL Server登录帐户
在SQL Server中添加一个帐户名为“LIU”，密码为“123456”的SQL Server登录帐户。
3、添加服务器角色成员
将“WANG”这个Windows NT登录帐户添加到系统管理员服务器角色中。
4、添加一个登录帐户为某个数据库的用户
将“LIU” 这个SQL Server登录帐户添加为CPXS数据库中一个用户，名称也为“LIU”。
5、添加数据库角色
在CPXS数据库中添加一个名为“ROLE”的角色。
6、添加数据库角色成员
将CPXS数据库中名为“LIU”这个用户添加为“ROLE”角色成员。
7、用户、角色的授权
授予“LIU”用户和“ROLE”角色对CPXSB表的查询权限和数量列的修改权限。
8、收回用户、角色的
收回“LIU”用户的所有权限。并查看是否能打开CPXSB表。
9、拒绝用户、角色的权限
重新授予“LIU”用户对CPXSB表的查询权限和数量列的修改权限。再拒绝该用户的所有权限。并查看是否能打开CPXSB表，并通过此实例阐述收回权限与拒绝权限的区别。
实验3：
create database CPXS on
(name='CPXS_data',
filename='D:\\dbf\\CPXS_data.mdf', size=5,
maxsize=50, filegrowth=10% )
log on
(name='CPXS_log',
filename='D:\\dbf\\CPXS_log.mdf', size=2,
maxsize=10, filegrowth=2 )
alter database CPXS
modify file (name='CPXS_data',filegrowth=5)
create table CP (
产品编号 char(6) primary key, 产品名称 char(30) not null, 价格 float(8), 库存量 int )
create table XSS (
客户编号 char(6) primary key, 客户名称 char(30) not null, 地区 char(10),
负责人 char(8), 电话 char(12) )
create table CPXSB (
产品编号 char(6) foreign key references CP(产品编号), 客户编号 char(6) foreign key references XSS(客户编号), 销售日期 datetime, 数量 int not null,
销售额 float(4) not null,
constraint PK_CPXSB primary key(产品编号,客户编号,销售日期) )
insert into CP
values('200001','柜式空调',3000,200)
insert into CP
values('200002','微波炉',1000,100,NULL)
insert into CP
values('200003','抽油烟机',1200,50,NULL)
alter table CP
add 产品简列 varchar(50)
update CP
set 价格=价格*0.8
delete from CP
where 价格*0.9<1500
实验四： 1.
select 产品编号,产品名称,价格 from CP
select 客户编号 as 'Customer id' ,客户名称 as 'Customer name' from XSS
where 地区='南京'
select 产品编号,产品名称,评价= case
when 价格<1000 then '廉价产品'
when 价格>=1000 and 价格<2000 then '一般产品' when 价格>=2000 and 价格<3000 then '昂贵产品' else '很昂贵产品' end from CP
select 产品编号,产品名称,产品总值=价格*库存量 from CP
select 客户编号 from CPXSB
group by 客户编号
having count(产品编号)>=1
select * from CP
where 价格>1000 and 价格<2000
select * from CP
where 产品名称 like '冰箱_A' 2.
select 总价格=sum(价格) from CP
select 产品编号 ,sum(销售额) 销售额 from CPXSB
where 销售日期='2004-3-18' group by 产品编号
select 客户编号 from CPXSB
group by 客户编号
having count(产品编号)>=2
实验五
1.查询在20XX年3月18日有销售的产品名称（不允许重复）。 SQL标准语句：
select distinct 产品名称 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and 销售日期='2004-3-18'
T-SQL扩展语句： select distinct 产品名称 from CP inner join CPXSB
on CP.产品编号=CPXSB.产品编号 and 销售日期='2004-3-18'
2.查询名称为“家电市场”的客户在20XX年3月18日购买的产品名称和数量。 SQL标准语句： select 产品名称,数量 from CP,XSS,CPXSB
where CP.产品编号=CPXSB.产品编号 and XSS.客户编号=CPXSB.客户编号 and 销售日期='2004-3-18' and 客户名称='家电市场'
T-SQL扩展语句： select 产品名称,数量 from CP inner join CPXSB
on CP.产品编号=CPXSB.产品编号 and 销售日期='2004-3-18' inner join XSS
on XSS.客户编号=CPXSB.客户编号 and 客户名称='家电市场'
3.查找所有产品情况及销售他们的销售日期、购买他们的客户编号和数量， 若产品没有销售记录，也要包括其情况。 select CP.*,销售日期, 客户编号,数量 from CP left outer join CPXSB
on CP.产品编号=CPXSB.产品编号
4.查找所有购买情况，若客户没有购买产品，也要包括其情况。 select XSS.*,产品编号,销售日期,数量,销售额 from CPXSB right join XSS
on XSS.客户编号=CPXSB.客户编号
实验六
查询在20XX年3月18日没有销售的产品名称（不允许重复）。 用IN子查询：
select distinct 产品名称 from CP
where 产品编号 not in
(select 产品编号 from CPXSB
where 销售日期='2004-3-18')
用EXISTS子查询：
select distinct 产品名称 from CP
where not exists (select * from CPXSB
where 销售日期='2004-3-18'and CP.产品编号=CPXSB.产品编号 )
查询名称为“家电市场”的客户在20XX年3月18日购买的产品名称和数量。 用IN子查询：
select 产品名称,数量 from CP,CPXSB where 客户编号 in
( select 客户编号 from XSS where 客户名称='家电市场' )
and 销售日期='2004-3-18' and CP.产品编号=CPXSB.产品编号
用EXISTS子查询：
select 产品名称,数量 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and 销售日期='2004-3-18' and exists (
select * from XSS where 客户名称='家电市场'and XSS.客户编号=CPXSB.客户编号 )
查询销售量大于所有20XX年3月18日销售的各产品销售数量的产品编号。 用ALL谓词：
select 产品编号 from CPXSB group by 产品编号 having sum(数量)>all
(select sum(数量) from CPXSB where 销售日期='2004-3-18' group by 产品编号 )
查询购买了所有产品的客户的名称。 select 客户名称 from XSS
where not exists (
select * from CP where not exists (
select * from CPXSB
where 产品编号=CP.产品编号 and 客户编号=XSS.客户编号 ) )
查询购买了客户编号为“000001”的客户购买的所有产品的客户的名称。
select 客户名称 from XSS where not exists (
select * from CP where exists (
select * from CPXSB x
where x.产品编号=CP.产品编号 and x.客户编号='000001' and not exists (
select * from CPXSB y
where y.客户编号=XSS.客户编号 and y.产品编号=CP.产品编号 ) ) )
and 客户编号 !='000001'
实验七
（1）启动企业管理器，在产品销售数据库CPXS中创建价格小于2000的产品视图VIEW_CP_PRICE2000，
要求加密并保证对该视图的更新都要符合价格小于2000这个条件， 写出创建过程和对应的T-SQL语句： create view VIEW_CP_PRICE2000 with encryption as
select* from CP where 价格<2000
with check option
（2）打开查询分析器，用T-SQL语句创建各客户购买产品的情况VIEW_GMQK视图，包括客户编号、
客户名称、产品编号、产品名称、价格，购买日期、购买数量。 create view VIEW_GMQK as
select XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,销售日期 as '购买日期',数量 as'购买数量'
from CP,XSS,CPXSB
where CP.产品编号=CPXSB.产品编号 and XSS.客户编号=CPXSB.客户编号
（3）创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号小于等于’100010’
产品数据，CP1表中为编号大于‘100010’产品数据，以分区列为产品编号， 创建可更新的分区视图VIEW_CP12。 create table CP1
(产品编号 char(6) primary key check(产品编号<='100010'), 产品名称 char(30) not null, 价格 real, 库存量 int,
产品简列 varchar(50) )
create table CP2
(产品编号 char(6) primary key check(产品编号>'100010'), 产品名称 char(30) not null, 价格 real, 库存量 int,
产品简列 varchar(50) )
create view VIEW_CP12 as
select * from CP1 union all
select * from CP2
2、查询视图
（1）基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、名称和价格。
select 产品编号,产品名称,价格 from VIEW_CP_PRICE2000
（2）基于VIEW_GMQK视图，查询各客户在20004年3月18日购买产品的情况。 select * from VIEW_GMQK where 购买日期='2004-3-18'
3、更新视图
利用T-SQL语句对于视图VIEW_12进行以下数据更新。 （1） 插入一条CP记录（’ '100042','数码相机',3500,2）。 insert into VIEW_CP12
values('100042','数码相机',3500,2,null)
（2） 将产品编号为’100042’的价格改为3000。 update VIEW_CP12 set 价格=3000
where 产品编号='100042'
（3） 删除产品编号为’100042’的产品。
delete VIEW_CP12
where 产品编号='100042'
4、修改视图
将VIEW_CP_PRICE2000视图不加密。 alter view VIEW_CP_PRICE2000 as
select* from CP where 价格<2000 with check option
5、删除视图
将VIEW-GMQK视图删除。 drop view VIEW_GMQK
实验八
1、声明游标
（1）使用SQL-92语法声明一只进只读游标CUR1：要求结果集20XX年3月18日销售情况。
declare CUR1 insensitive cursor for
select *from CPXSB where 销售日期='2004-3-18' for read only
（2）使用T-SQL扩展声明一滚动动态游标CUR2：要求结果集为客户信息，并能通过该游标修改客户名称列。 declare CUR2 cursor scroll for
select * from XSS
for update of 客户名称
2、打开游标
打开CUR2游标。 open CUR2
3、读取游标中数据
编写程序，实现依次读取游标CUR2中各行数据。
fetch next from CUR2
while @@FETCH_STATUS=0 begin
fetch next from CUR2 end
4、关闭游标
关闭CUR2游标。 close CUR2
5、释放游标
释放CUR2游标。 deallocate CUR2
实验九
1、变量的定义和赋值
创建一名为 Customer_name的局部变量，并在SELECT语句中使用该变量查找“广电公司”购买产品的情况。
declare @Customer_name varchar(20) set @Customer_name='广电公司'
select 客户名称,CPXSB.*from CPXSB,XSS
where 客户名称=@Customer_name and XSS.客户编号=CPXSB.客户编号
2、用户自定义数据类型定义、使用和删除
用SQL命令定义一名为Customer_id的用户自定义数据类型，要求char(6)，NOT NULL， 并把该自定义数据类型用来定义XSS表中的客户编号，然后删除该自定义数据类型，请叙述该过程，并写出相关语句。
sp_addtype 'Customer_id','char(6)','not null' sp_droptype 'Customer_id'
先用创建语句自定义数据类型，然后打开“数据库-CPXSB-表”，右击XSS,选择“设计表”，将客户编号的数据类型 改为Customer_id,保存即可
将客户编号的数据类型改回char(6),然后用第二个语句删除该用户自定义数据类型
3、T-SQL语言编程
（1）用T-SQL语言编程输出3～300之间能被7整除的数。 declare @i int,@j int set @i=3 set @j=7
while @i<=300 begin
if(@i%@j)=0 print @i
set @i=@i+1
end
（2）用T-SQL语言编程输出3～100之内的素数。 declare @i int,@j int,@t int set @i=3
while @i<100 begin
set @j=2 while @j<@i begin
set @t=@i%@j if @t=0 break set @j=@j+1 if(@j>=@i) print @i end
set @i=@i+1 end
实验十
1、函数的定义
对于CPXS数据库，定义完成如下功能的函数： （1）据产品名称，查询该产品的相关信息；（函数名为FU_CP） create function FU_CP(@产品名称 char(30)) returns table as return
select * from CP where 产品名称=@产品名称
（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
create function FU1_CPXS(@year int,@quarter int,@产品名称 char(30)) returns table as return
select 产品名称,sum(数量) as 销售数量,sum(销售额) as 销售金额 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称
group by 产品名称
create function FU2_CPXS(@year int,@quarter int,@产品名称 char(30)) returns @f2 table (
产品名称 char(30), 销售数量 int, 金额 real ) as begin
insert into @f2
select 产品名称,sum(数量) as 销售数量,sum(销售额) as 金额 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称 group by 产品名称 return end
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）
create function FU3_CPXS(@客户名称 char(30),@year int,@quarter int) returns table as return
select 客户名称,产品名称 as 销售商品名称,数量,销售额 as 金额 from CP,XSS,CPXSB
where CPXSB.产品编号=CP.产品编号 and CPXSB.客户编号=XSS.客户编号
and 客户名称=@客户名称 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter
2、函数的调用
（1）对函数FU_CP，查询产品名称为“mp3”的产品情况； select * from FU_CP('MP3')
（2）对函数FU1_CPXS，查询20XX年第3季度彩色电视机的销售数量和销售金额； select * from FU1_CPXS(2004,3,'彩色电视机')
（3）对函数FU2_CPXS，查询20XX年第1季度洗衣机的销售数量和销售金额；
select * from FU2_CPXS(2004,1,'洗衣机')
（4）对函数FU3_CPXS，查询广电公司20XX年第1季度销售的产品名称、销售数量和销售金额。
select * from FU3_CPXS('广电公司',2004,1)
实验十一 对于CPXS数据库，完成如下操作： 1、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。 create unique nonclustered index on CP(产品名称)
（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、
价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。（请注意7个SET选项设置）。 create view view1 with schemabinding as
select XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,销售日期 as 购买日期,数量 as 购买数量
from dbo.CP,dbo.XSS,dbo.CPXSB
where XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
create unique clustered index ind_view1 on dbo.view1(客户编号,产品编号,购买日期)
2、索引的删除
删除ind_view1索引。； drop index view1.ind_view1
3、默认值约束的定义
对库存量字段添加默认值为0的约束def_kcl。 alter table CP
add constraint def_kcl default 0 for 库存量
4、默认值约束的删除 删除def_kcl默认值约束。 alter table CP
drop constraint def_kcl
5、默认值对象的定义、使用和删除
（1）定义一值为0的默认值对象kcl_def，并将其绑定到CP表的库存量字段；然后再删除此默认值对象，
请叙述该过程，并写出相关SQL语句。 create default kcl_def as 0
exec sp_bindefault 'kcl_def','CP.库存量'
（2） 用SQL命令定义一名为city的用户自定义数据类型，要求char(6)，NULL，再定义一值为“北京”的默认值对象city_def，
然后将city_def默认值对象绑定到city自定义数据类型，最后删除city_def默认值对象，请叙述该过程，并写出相关SQL语句。 sp_addtype 'city','char(6)','null'
create default city_def as '北京' exec sp_bindefault 'city_def','city' exec sp_unbindefault 'city' drop default city_def
实验十二 对于CPXS数据库，删除CP表、CPXSB表、XSS表上已有所有约束：
1、实体完整性的实现
（1）对CP表、CPXSB表、XSS表，定义主键约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。 alter table CP
add constraint cp_pk primary key (产品编号)
alter table CPXSB
add constraint cpxsb_pk primary key (产品编号,客户编号,销售日期)
alter table XSS
add constraint xss_pk primary key (客户编号)
（2）在CP表的产品名称列定义一个唯一约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。 alter table CP
add constraint 产品名称_uk unique (产品名称)
2、参照完整性的实现
（1）利用企业管理器建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，
从表CPXSB采用NO ACTION方式，写出其过程。
alter table CPXSB
add constraint CP_CPXSB_FK foreign key(产品编号) references CP(产品编号)
on delete no action on update no action
（2）利用SQL语句建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，
从表CPXSB采用CASCADE（级联）方式，写出其过程。
alter table CPXSB
add constraint XSS_CPXSB_FK foreign key (客户编号) references XSS(客户编号) on delete cascade on update cascade
3、域完整性的实现
（1）在CP表的价格列上定义大于等于0的检查（CHECK）约束。
alter table CP
add constraint 价格_CK check (价格>0)
（2）定义一个大于等于0的规则check_kcl，并将其绑定到CP表的库存量列，然后解除绑定，删除该规则，请写出相关SQL语句。 create rule check_kcl as @kcl>=0 sp_bindrule 'check_kcl','CP.库存量' sp_unbindrule 'CP.库存量' drop rule check_kcl
4、综合训练
创建一个“学生档案”数据库，并用CREATE TABLE在该数据库中创建“人事”表和“成绩”两个表，
其中“人事”表包括学号、姓名、性别、电话号码、家庭住址字段组成。“成绩”表包括学号、选修课程名称、成绩三个字段。
分别在“人事”表的学号字段设置主键约束，在“成绩”表的学号字段设置外键约束，在成绩字段设置大于等于0小于等于100的检查约束，
在家庭住址字段设置惟一约束，在性别字段设置默认值为“男”。 create database 学生档案 on (
name='学生档案_data',
filename='c:\\sql\\data\\学生档案_data.mdf' )
log on
(
name='学生档案_log',
filename='c:\\sql\\log\\学生档案_data.ldf' )
create table 人事 (
学号 char(8) primary key, 姓名 char(16),
性别 char(2) default '男', 电话号码 char(12),
家庭住址 char(50) unique )
create table 成绩 (
学号 char(8) foreign key references 人事(学号), 选修课程名称 char(16),
成绩 numeric(9) check(成绩>=0 and 成绩<=100) )
实验十三
对于CPXS数据库，创建如下存储过程： 1、无参存储过程
编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。 create procedure gmqk as
select XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,销售日期 as '购买日期',数量 as'购买数量'
from CP,XSS,CPXSB
where CP.产品编号=CPXSB.产品编号 and XSS.客户编号=CPXSB.客户编号
exec gmqk
2、带有参数的存储过程
编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。
create procedure gmqk1 @客户编号 char(6) with encryption as