计算机与信息学院
数据库原理实验报告
专 业： 班 级： 学 号： 姓 名： 指导教师：
计算机科学与技术 2012级本科班
07173
2014年 06月 18 日
实验项目列表
序号 实验项目名称 1 2 3 4 5 6 7 8 9 实验一：数据库的创建管理 实验二、表的创建、管理及数据操作 实验三：单表查询 实验四：连接查询 实验五：嵌套查询 实验六：视图和索引的创建与管理 实验七：T-SQL语言编程基础 实验八：存储过程 以下选做： 实验九：函数 成绩 10 实验十：触发器 11 实验十一：系统安全管理
计算机与信息学院实验报告纸
实验一 数据库创建与管理
一、实验目的与要求
1、熟练掌握SSMS中界面方式创建和管理数据库。
2、熟练掌握SSMS查询编辑器T-SQL语句创建和管理数据库。 3、熟练掌握备份和还原数据库。
二、实验内容
1、界面方式创建和管理数据库 （1）创建数据库 （2）修改数据库 （3）删除数据库
2、利用企业管理器备份和还原数据库 （1）备份数据库 （2）还原数据库
3、T-SQL语句方式创建和管理数据库
（1）创建SPJ数据库：在SSMS中“新建查询”，输入以下语句并运行 CREATE DATABASE SPJ ON
(NAME=?SPJ_Data?,FELENAME='C:\\Program Files\\Microsoft Server\\MSSQL\\data\\SPJ_Data.MDF' , SIZE = 3,
MAXSIZE = 10,
FILEGROWTH = 10%) LOG ON
(NAME = 'SPJ_Log', FILENAME = 'C:\\Program Files\\Microsoft Server\\MSSQL\\data\\SPJ_Log.LDF' , SIZE = 1,
FILEGROWTH = 10%)
（2）修改SPJ数据库：在查询分析器中输入以下语句并运行 ALTER DATABASE SPJ MODIFY FILE
(NAME='SPJ_Data', SIZE=4,
SQL SQL MAXSIZE=UNLIMITED) ALTER DATABASE SPJ ADD FILE
(NAME='SPJ_Data_2', FILENAME='C:\\Program Files\\Microsoft Server\\MSSQL\\Data\\SPJ_Date_2.ndf',SIZE=1, MAXSIZE=10,
FILEGROWTH=10%)
（3）删除SPJ数据库：DROP DATABASE SPJ
4、界面方式创建数据库XSBOOK，写出操作过程。
5、T-SQL语句方式创建XSBOOK，写出相应T-SQL语句。
SQL
三、实验步骤
1、界面方式创建和管理数据库 （1）创建数据库
1）在对象资源管理器中连接到数据库引擎服务器；
2）将数据库引擎服务器展开，右击\数据库\，然后单击\新建数据库\命令； 3）键入新数据库的名称student，同时用指定的数据库名student作为前缀创建主数据库和事务日志文件，例如：student_Data.mdf 和 student_Log.ldf。 （2）修改数据库
1）要更改数据库文件和日志文件的参数，选中“student”数据库右击鼠标“属性”，在“文件”选项卡中进行修改。
2）要更改文件组，选中“student”数据库，右击鼠标“属性”，在“文件组”选项卡中进行修改。 （3）删除数据库
选择“student”数据库，右击鼠标选择“删除”命令即可。 2、利用企业管理器备份和还原数据库 （1）备份数据库
选择“student”数据，右击鼠标选择“任务|备份”命令，其余默认。 （2）还原数据库
选择“数据库”，右击鼠标选择“还原数据库”命令，打开还原数据库对话框，在“目标数据库”中输入student，在“还原的源”中选择源设备，打开选择刚备份好的student.bak文件即可。 3、T-SQL语句方式创建和管理数据库
（1）创建SPJ数据库：在SSMS中“新建查询”，输入以下语句并运行
，
结果如下图： （2）修改SPJ数据库：在查询分析器中输入以下语句并运行
结果如下图：
（3）删除SPJ数据库
结果如下图：
4、界面方式创建数据库XSBOOK，写出操作过程。
1) 在对象资源管理器中连接到数据库引擎服务器； 2) 将数据库引擎服务器展开，右击\数据库\，然后单击\新建数据库\命令； 3) 键入新数据库的名称XSBOOK，同时用指定的数据库名XSBOOK作为前缀创建主数据库
5、T-SQL语句方式创建XSBOOK，写出相应T-SQL语句。
CREATE DATABASE XSBOOK ON
(NAME = 'XSBOOK_Data',
FILENAME='C:\\ProgramFiles\\MicrosoftSQLServer\\MSSQL.1\\MSSQL\\Data\\XSBOOK_Data.MDF' ,
SIZE = 3, MAXSIZE = 10, FILEGROWTH = 10%) LOG ON
(NAME = 'XSBOOK_Log',
FILENAME='C:\\ProgramFiles\\MicrosoftSQLServer\\MSSQL.1\\MSSQL\\Data\\XSBOOK_Log.LDF' ,
SIZE = 1,
FILEGROWTH = 10%)
四、实验心得
第一次接触数据库的创建与管理，刚开始利用界面创建，感觉很轻松，但是利用T-SQL语句创建的时候，由于是第一次接触这种语句，老是犯错误，用语句创建数据库的时候由于路径写错，总是创建不成功，这个还是没有用心去发现。所以，用心做事才能把事情做得更好。
实验二 表的创建、管理及数据操作
一、 实验目的与要求
1、 复习使用T-SQL语句创建和管理数据库。
2、 熟练掌握使用界面和T-SQL语句创建、修改和删除表。 3、 熟练掌握使用界面和T-SQL语句插入、修改和删除表数据。
二、 实验内容
1、用T-SQL语句创建产品销售数据库CPXS：
数据文件初始大小为5MB，最大大小50MB，增长方式按10%比例增长；日志文件初始为2MB，最大可增长到10MB，按2MB增长；其余参数取默认值。
2、用T-SQL语句将CPXS数据文件的增长方式改为按5MB增长。 3、CPXS数据库包含如下三个表：
CP（产品编号，产品名称，价格，库存量）
XSS（客户编号，客户名称，地区，负责人，电话）
CPXSB（产品编号，客户编号，销售日期，数量，销售额） 三个表结构如图3.1～图3.3所示，请写出创建以上三个表的T-SQL语句并在查询分析器中运行。
图2.1 CP表结构
图2.2 XSS表结构
图2.3 CPXSB表结构
4、输入如图3.4～图3.6的CP表、XSS表和CPXSB表的样本数据。
图2.4 CP表的样本数据
图2.5 XSS表的样本数据
图2.6 CPXSB表的样本数据
5、用T-SQL语句向CP表插入如下记录： 200001 柜式空调 3000 200 200002 微波炉 1000 100 200003 抽油烟机 1200 50 6、用T-SQL语句向CP表中增“产品简列”列，varchar(50)，允许为NULL。 7、将CP表中每种商品的价格打8折。
8、将CP表中价格打9折后小于1500的商品删除。
三、 实验步骤
1、用T-SQL语句创建产品销售数据库CPXS：
数据文件初始大小为5MB，最大大小50MB，增长方式按10%比例增长；日志文件初始为2MB，最大可增长到10MB，按2MB增长；其余参数取默认值。
CREATE DATABASE CPXS ON
(NAME = 'CPXS_Data',
FILENAME='C:\\ProgramFiles\\MicrosoftSQLServer\\MSSQL.1\\MSSQL\\Data\\CPXS_Data.MDF' , SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 10%) LOG ON
(NAME = 'CPXS_Log',
FILENAME='C:\\ProgramFiles\\MicrosoftSQLServer\\MSSQL.1\\MSSQL\\Data\\CPXS_Log.LDF' , SIZE = 2MB, MAXSIZE=10MB FILEGROWTH = 2MB)
截图如下：
2、用T-SQL语句将CPXS数据文件的增长方式改为按5MB增长。
ALTER DATABASE CPXS MODIFY FILE
(NAME='CPXS_Data', SIZE=5)
截图如下：
3、CPXS数据库包含如下三个表：
CP（产品编号，产品名称，价格，库存量）
XSS（客户编号，客户名称，地区，负责人，电话）
CPXSB（产品编号，客户编号，销售日期，数量，销售额） 三个表结构如图3.1～图3.3所示，请写出创建以上三个表的T-SQL语句并在查询分析器中运行。
CREATE TABLE CP
(产品编号 CHAR(6) PRIMARY KEY NOT NULL, 产品名称 CHAR(30) NOT NULL, 价格 FLOAT, 库存量 INT )
CREATE TABLE XSS
(客户编号 CHAR(6) PRIMARY KEY NOT NULL, 产品名称 CHAR(30) NOT NULL, 地区 CHAR(10), 负责人 CHAR(8), 电话 CHAR(12) )
CREATE TABLE CPXSB (产品编号 CHAR(6), 客户编号 CHAR(6), 销售日期 DATETIME, 数量 INT, 销售额 FLOAT,
PRIMARY KEY(产品编号,客户编号,销售日期))
截图如下：
4、输入如图3.4～图3.6的CP表、XSS表和CPXSB表的样本数据。
已经手工输入好。
4、 用T-SQL语句向CP表插入如下记录：
INSERT INTO CP
VALUES('200001','柜式空调',3000,200) INSERT INTO CP
VALUES('200002','微波炉',1000,100) INSERT INTO CP
VALUES('200001','抽油烟机',1200,50)
截图如下：
5、 用T-SQL语句向CP表中增“产品简列”列，varchar(50)，允许为NULL。
ALTER TABLE CP
ADD 产品简列 VARCHAR(50)
截图如下：
6、 将CP表中每种商品的价格打8折。
UPDATE CP
SET 价格= 价格* 0.8
7、将CP表中价格打9折后小于1500的商品删除。
UPDATE CP
SET 价格= 价格* 0.9 DELETE FROM CP
WHERE 价格< 1500
四、实验心得
通过本章节的学习与试验，了解了如何使用T-SQL语句创建、修改和删除表以及表中的数据。这算是学习数据库这门课正式入门了。由于刚开始学习这些方面的操作，所以一些T-SQL语句还不是很熟，实际操作时还要借助书本才能完成，不过我相信，只要勤加练习，肯定能独立完成。
试验三 单表查询
一、试验目的与要求
1、牢记SELECT语句的基本语法格式。 2、熟练掌握使用SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、HAVING字句和集函数。
二、试验内容
对CPXS数据库，完成如下单表查询： 1、简单查询
（1）查询各种产品的产品编号、产品名称和价格。
（2）查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别指定为：Customer id和Customer name。
（3）查询CP表中各种产品的产品编号、产品名称和价格，对其价格按以下规则进行转换；若价格小于1000，替换为“廉价产品”，若价格在1000-2000之间，替换为“一般产品”，若价格大于2000小于等于3000，替换为“昂贵产品”，若价格大于3000，替换为“很昂贵产品”，列标题更改为“评价”。
（4）求各产品编号、名称和产品总值。
（5）查询至少购买了至少一种产品的客户编号（不重复）。 （6）查询价格在1000-2000的产品信息。
（7）查询产品名称含有”冰箱_A”的产品情况。 2、使用分组和集函数
先将CPXSB表数据修改如下图所示：
图3.1
再完成如下查询：
（1）计算所有产品总价格。
（2）求各种产品2004年3月18日销售额。 （3）求购买二种以上产品的客户编号。
三、试验步骤
1、简单查询
（1）查询各种产品的产品编号、产品名称和价格。 SELECT 产品编号,产品名称,价格 FROM CP 截图如下：
（2）查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别指定为：Customer id和Customer name。
SELECT 客户编号 Customerid,客户名称 Customername FROM XSS
WHERE 地区= '南京'
截图如下：
（3）查询CP表中各种产品的产品编号、产品名称和价格，对其价格按以下规则进行转换；若价格小于1000，替换为“廉价产品”，若价格在1000-2000之间，替换为“一般产品”，若价格大于2000小于等于3000，替换为“昂贵产品”，
若价格大于3000，替换为“很昂贵产品”，列标题更改为“评价”。
SELECT 产品编号,产品名称,评价= CASE
WHEN 价格<= 1000 THEN '廉价产品'
WHEN 价格> 1000 AND 价格<= 2000 THEN '一般产品' WHEN 价格> 2000 AND 价格<= 3000 THEN '昂贵产品' ELSE '很昂贵产品' END FROM CP
截图如下：
（4）求各产品编号、名称和产品总值。
SELECT 产品编号,产品名称,产品总值 = 价格 * 库存量 FROM CP
截图如下：
（5）查询至少购买了至少一种产品的客户编号（不重复）。
SELECT DISTINCT 客户编号
FROM CPXSB
截图如下：
（6）查询价格在1000-2000的产品信息。
SELECT * FROM CP
WHERE 价格BETWEEN 1000 AND 2000
截图如下：
（7）查询产品名称含有”冰箱_A”的产品情况。
SELECT * FROM CP
WHERE 产品名称LIKE '冰箱\\_A' ESCAPE '\\'
截图如下：
2、使用分组和集函数
（1）计算所有产品总价格。
SELECT SUM(价格* 库存量) 总价格 FROM CP
截图如下：
（2）求各种产品2004年3月18日销售额。
SELECT DISTINCT 产品编号,SUM(销售额) 销售额 FROM CPXSB
WHERE 销售日期= '2004-03-18'
GROUP BY 产品编号
截图如下：
（3）求购买二种以上产品的客户编号。
SELECT 客户编号 FROM CPXSB
GROUP BY 客户编号HAVING COUNT(*) >= 2
截图如下：
四、试验心得
通过本次课程的学习与试验，学会了如何使用T-SQL语句来进行单表的查询，仍然由于对语句的不熟悉，开始实验的时候总要靠书本才能写出完整的语句，不过写了几次后，便能独立写出，相比上一次试验有了很大的提高。
试验四 连接查询
一、试验目的与要求
熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。
二、试验内容
对CPXS数据库，完成如下连接查询： 1、内连接（要求用两种方式书写）
（1）查询在2004年3月18日有销售的产品名称（不允许重复）。
（2）查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。 2、外连接
（1）查找所有产品情况及销售他们的销售日期、购买他们的客户编号和数量，若产品没有销售记录，也要包括其情况。
（2）查找所有购买情况，若客户没有购买产品，也要包括其情况。
三、试验步骤
1、内连接（要求用两种方式书写）
（1）查询在2004年3月18日有销售的产品名称（不允许重复）。 SQL标准语句：
SELECT DISTINCT CP.产品名称 FROM CP,CPXSB
WHERE CP.产品编号= CPXSB.产品编号 AND 销售日期= '2004-03-18'
T-SQL扩展语句：
SELECT DISTINCT CP.产品名称 FROM CPXSB INNER JOIN CP ON (CP.产品编号= CPXSB.产品编号 AND 销售日期= '2004-03-18')
截图如下：
（2）查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。
SQL标准语句：
SELECT CP.产品名称,CPXSB.数量 FROM XSS,CPXSB,CP
WHERE XSS.客户编号=CPXSB.客户编号 AND CPXSB.产品编号=CP.产品编号 AND XSS.客户名称='家电市场' AND CPXSB.销售日期='2004-03-18'
T-SQL扩展语句：
SELECT CP.产品名称,CPXSB.数量 FROM XSS INNER JOIN CPXSB ON XSS.客户编号=CPXSB.客户编号 AND XSS.客户名称='家电市场' INNER JOIN CP
ON CPXSB.产品编号=CP.产品编号 AND CPXSB.销售日期='2004-03-18'
截图如下：
2、外连接
（1）查找所有产品情况及销售他们的销售日期、购买他们的客户编号和数量，若产品没有销售记录，也要包括其情况。
SELECT CP.*,CPXSB.客户编号,销售日期,数量 FROM CP LEFT OUTER JOIN CPXSB ON CP.产品编号=CPXSB.产品编号
截图如下：
（2）查找所有购买情况，若客户没有购买产品，也要包括其情况。
SELECT XSS.客户编号,CPXSB.产品编号,销售日期,数量,销售额,CP.产品名称 FROM XSS LEFT OUTER JOIN CPXSB ON XSS.客户编号=CPXSB.客户编号 LEFT OUTER JOIN CP
ON CPXSB.产品编号=CP.产品编号
截图如下：
四、试验心得
通过本次课程的学习与试验，学会了使用SQL标准语句和T-SQL扩展语句进行表的连接查询。刚开始的时候由于分不清SQL标准语句和T-SQL扩展语句的区别，导致试验很难成功，但是通过向同学和老师的请教之后，弄清楚了二者的区别，之后做起试验来就觉得挺容易了。
试验五 嵌套查询
一、试验目的与要求
1、熟练掌握IN子查询。
2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）。 3、熟悉掌握EXISTS子查询（尤其要注意如何将全称量词和逻辑蕴含用EXISTS谓词代替）。
二、试验内容
对CPXS数据库，完成如下嵌套查询：
1、查询在2004年3月18日没有销售的产品名称（不允许重复）。（使用IN子查询与EXISTS子查询）
2、查询名称为“家电市场”的客户在2004年3月18日购买的产品名称。（使用IN子查询与EXISTS子查询）
3、查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。（使用ALL谓词与集函数）
4、查询购买了所有产品的客户的名称。
5、查询购买了客户编号为“000001”的客户购买的所有产品的客户的名称。
三、试验步骤
1、查询在2004年3月18日没有销售的产品名称（不允许重复）。（使用IN子查询与EXISTS子查询）。
用IN子查询：
SELECT CP.产品名称 FROM CP
WHERE 产品编号 IN
(SELECT 产品编号 FROM CPXSB
WHERE 销售日期!= '2004-03-18')
用EXISTS子查询：
SELECT CP.产品名称 FROM CP WHERE EXISTS
(SELECT * FROM CPXSB
WHERE CP.产品编号= CPXSB.产品编号 AND 销售日期!= '2004-03-18')
截图如下：
2、查询名称为“家电市场”的客户在2004年3月18日购买的产品名称。（使用IN子查询与EXISTS子查询）。
用IN子查询：
SELECT CP.产品名称 FROM CP
WHERE 产品编号IN
(SELECT 产品编号 FROM CPXSB WHERE 客户编号IN
(SELECT 客户编号 FROM XSS
WHERE 客户名称= '家电市场' )
AND 销售日期= '2004-03-18' )
用EXISTS子查询：
SELECT CP.产品名称 FROM CP
WHERE EXISTS
(SELECT * FROM CPXSB
WHERE CP.产品编号 = CPXSB.产品编号 AND 销售日期 = '2004-03-18' AND EXISTS (SELECT * )
FROM XSS
WHERE CPXSB.客户编号= XSS.客户编号 AND 客户名称= '家电市场')
截图如下：
3、查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。（使用ALL谓词与集函数）。
用ALL谓词：
SELECT 产品编号 FROM CPXSB WHERE 数量> ALL
(SELECT SUM(数量) FROM CPXSB
WHERE 销售日期= '2004-03-18' GROUP BY 产品编号)
用集函数：
SELECT 产品编号 FROM CPXSB WHERE 数量>
(SELECT MAX(数量) FROM CPXSB
WHERE 销售日期= '2004-03-18' )
截图如下：
4、查询购买了所有产品的客户的名称。
SELECT 客户名称 FROM XSS
WHERE NOT EXISTS
(SELECT * FROM CP
WHERE NOT EXISTS
(SELECT * FROM CPXSB
WHERE CPXSB.产品编号=CP.产品编号
AND XSS.客户编号=CPXSB.客户编号))
截图如下：
5、查询购买了客户编号为“000001”的客户购买的所有产品的客户的名称。
SELECT 客户名称 FROM XSS
WHERE NOT EXISTS
(SELECT * FROM CP WHERE EXISTS
(SELECT *
FROM CPXSB CPXSB1
WHERE CPXSB1.产品编号=CP.产品编号
AND CPXSB1.客户编号='000001') AND NOT EXISTS
(SELECT *
FROM CPXSB CPXSB2
WHERE CPXSB2.产品编号=CP.产品编号 AND CPXSB2.客户编号=XSS.客户编号 AND XSS.客户编号!='000001') )
截图如下：
四、试验心得
通过本次课程的学习与试验，学会了用IN子查询、EXISTS子查询与集函数进行表的嵌套查询，只是在IN子查询用EXISTS子查询转换的时候脑袋有点转不过来，需要想很久才能想明白这二者之间的关系，很是头痛。只有通过大量的练习与理解才能熟练使用，融会贯通。
试验六 视图与索引的创建与管理
一、试验目的与要求
1、熟练掌握使用界面和T-SQL语句创建、查询、更新和删除视图。 2、进一步掌握视图与基本表的联系与区别。 3、理解索引的概念和作用。 4、熟练掌握索引的创建与删除。
二、试验内容
对于CPXS数据库，完成如下操作： 1、创建视图
（1）在产品销售数据库CPXS中创建价格小于2000的产品视图VIEW_CP_PRICE2000，要求加密并保证对该视图的更新都要符合价格小于2000这个条件，写出利用界面的创建过程和对应的T-SQL语句。
（2）打开查询分析器，用T-SQL语句创建各客户购买产品的情况VIEW_GMQK视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。
（3）创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号小于等于?100010?产品数据，CP2表中为编号大于?100010?产品数据，以分区列为产品编号， 创建可更新的分区视图VIEW_CP12。 2、查询视图
（1）基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、名称和价格。
（2）基于VIEW_GMQK视图，查询各客户在20004年3月18日购买产品的情况。 3、更新视图
利用T-SQL语句对于视图VIEW_CP12进行以下数据更新。 （1）插入一条CP记录（?100042?,?数码相机?,3500,2）。 （2）将产品编号为?100042?的价格改为3000. （3）删除产品编号为?100042?的产品。 4、修改视图
将VIEW_CP_PRICE2000视图不加密。 5、删除视图
将VIEW_GMQK视图删除。 6、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。
（2）先创建各客户购买产品的强情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、价格、购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。
7、索引的删除
删除索引ind_view1。
三、试验步骤
1、创建视图
（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.产品编号,产品名称,价格,CPXSB.销售日期,数量 FROM XSS,CP,CPXSB
WHERE XSS.客户编号=CPXSB.客户编号 AND CPXSB.产品编号=CP.产品编号
截图如下：
（3）创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号小于等于?100010?产品数据，CP2表中为编号大于?100010?产品数据，以分区列为产品编号， 创建可更新的分区视图VIEW_CP12。
//创建表CP1并插入数据 CREATE TABLE CP1
(产品编号char(6) PRIMARY KEY CHECK (产品编号< '100010'), 产品名称char(30), 价格float,
库存量int,
产品简列varchar(50) )
INSERT INTO CP1 SELECT * FROM CP
WHERE 产品编号<'100010' //创建表CP2并插入数据 CREATE TABLE CP2
(产品编号char(6) PRIMARY KEY CHECK (产品编号> '100010'), 产品名称char(30),
价格float, 库存量int,
产品简列varchar(50) )
INSERT INTO CP2 SELECT * FROM CP
WHERE 产品编号>'100010' //创建分区视图
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-03-18'
截图如下：
3、更新视图
利用T-SQL语句对于视图VIEW_CP12进行以下数据更新。 （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 FROM 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 截图如下：
6、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。
CREATE UNIQUE INDEX ind_cp ON CP(产品编号)
截图如下：
（2）先创建各客户购买产品的强情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、价格、购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。
//创建VIEW1视图
CREATE VIEW VIEW1 WITH SCHEMABINDING AS
SELECT XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,CPXSB.销售日期,数量 FROM dbo.XSS,dbo.CP,dbo.CPXSB WHERE XSS.客户编号=CPXSB.客户编号 AND CPXSB.产品编号=CP.产品编号
//建立唯一聚簇索引
CREATE UNIQUE CLUSTERED INDEX ind_view1 ON VIEW1(客户编号,产品编号,销售日期)
截图如下：
7、索引的删除
删除索引ind_view1。
DROP INDEX VIEW1.ind_view1
四、试验心得
通过本次课程的学习与试验，了解了什么是视图和索引，并掌握了如何创建视图和索引。感觉用建立好的视图进行查询比用表查询要简单得多。 在索引上关于聚簇与非聚簇的概念有一点小迷糊，总体来说感觉这次的试验做的还是挺好的。
试验七 T-SQL语言编程基础
一、试验目的与要求
1、熟练掌握变量的定义和赋值。
2、熟练掌握用户自定义数据类型定义、使用和删除。 3、熟练掌握各种运算符。
4、熟练掌握流程控制语句，尤其是条件语句和循环语句。 5、熟练并掌握常用的系统函数。
二、试验内容
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、变量的定义和赋值
创建一名为CUSTOMER_NAME的局部变量，并在SELECT语句中使用该变量查找“厂电公司”购买产品的情况。
DECLARE @CUSTOMER_NAME VARCHAR(30) SELECT @CUSTOMER_NAME = '厂电公司' SELECT * FROM XSS
WHERE 客户名称=@CUSTOMER_NAME
截图如下：
2、用户自定义数据类型定义、使用和删除
用SQL命令定义一名为Customer_id的用户自定义数据类型，要求char(6),NOT NULL，并把该自定义数据类型用来定义XSS表中的客户编号，然后删除该自定义数据类型，请叙述该过程，并写出相关语句。
//建立自定义数据类型
sp_addtype 'Customer_id','char(6)','not null' //用自定义数据类型来定义XSS1表 CREATE TABLE XSS1
( 客户编号Customer_id PRIMARY KEY NOT NULL,
客户名称char(30) NOT NULL, 地区char(10), 负责人char(8),
电话char(12)) DROP TABLE XSS1
//删除自定义数据类型
sp_droptype 'Customer_id'
截图如下：
3、T-SQL语言编程
（1）用T-SQL语言编程输出3-300之间能被7整除的数。
DECLARE @i int SET @i=3
WHILE @i<=300 BEGIN
END
IF @i%7!=0 SET @i=@i+1 ELSE PRINT @i SET @i=@i+1
截图如下：
（2）用T-SQL语言编程输出3-100之内的素数。
DECLARE @a int,@i int SET @i=3 WHILE @i<=100
BEGIN
SET @a=2
WHILE @a<=sqrt(@i)
IF (@i%@a=0) break SET @a=@a+1
BEGIN
END
IF @a>sqrt(@i) PRINT @i SET @i=@i+1
END
截图如下:
四、试验心得
通过本次课程的学习与试验，了解了变量的定义与赋值、用户自定义数据类型的概念以及用T-SQL语言编程的知识。其中关于用户自定义数据类型的删除，刚开始不知道为什么删除不了，最后通过查阅课件才知道必须要先把使用该自定义类型的表删除之后才能删除自定义的数据类型。关于T-SQL语言编程，我感觉总体与C语言相差不大，像一些条件语句等，但是写法有很大不同，像变量的定义等，刚开始由于不适应，老是写错，这个需要大量的练习。
试验八 存储过程
一、试验目的与要求
熟练掌握存储过程的创建、调用和删除。
二、试验内容
对于CPXS数据库，创建如下存储过程： （1）无参存储过程
编写一无存储存过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
（2）带有参数的存储过程
编写一加密存储，查询指定客户购买产品的情况。并调用该存储，查询客户编号为“000002”的客户购买情况。 （3）带有通配符参数的存储过程
编写一存储，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。 （4）带有OUTPUT参数的存储过程
编写一存储，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
三、试验步骤
（1）无参存储过程
编写一无存储存过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
CREATE PROC proc_1 AS
SELECT XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,CPXSB.销售日期,数量 FROM XSS,CP,CPXSB
WHERE XSS.客户编号=CPXSB.客户编号 AND CPXSB.产品编号=CP.产品编号 EXEC proc_1
截图如下：
（2）带有参数的存储过程
编写一加密存储，查询指定客户购买产品的情况。并调用该存储，查询客户编号为“000002”的客户购买情况。
CREATE PROC proc_2 @客户编号char(6) WITH ENCRYPTION AS SELECT * FROM CPXSB
WHERE CPXSB.客户编号=@客户编号 EXEC proc_2 '000002'
截图如下：
（3）带有通配符参数的存储过程
编写一存储，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。
CREATE PROC proc_3 @产品名称char(30)='%冰箱%' AS
SELECT CP.产品名称,CPXSB.产品编号,客户编号,销售日期,数量,销售额 FROM CPXSB,CP
WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称LIKE @产品名称 EXEC proc_3
截图如下：
（4）带有OUTPUT参数的存储过程
编写一存储，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
CREATE PROC proc_4 @产品名称char(30),@销售日期datetime OUTPUT AS
SELECT CP.产品名称,CPXSB.产品编号,客户编号,销售日期,数量,销售额 FROM CPXSB,CP
WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称=@产品名称 DECLARE @销售日期datetime
EXEC proc_4 '洗衣机',@销售日期OUTPUT
截图如下：
四、试验心得
通过本次课程的学习与试验，学会了存储过程的创建、调用和删除。感觉这次试验挺简单的。做了很多次试验了，对T-SQL语句越来越熟练，所以做起来更加的得心应手了，如果在课堂上更加仔细的听课，课下及时的练习，效果会更好的。
试验九 函数
一、试验目的与要求
1、理解什么是标量函数、内嵌表值函数及多语句表值函数。
2、熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。
二、试验内容
1、函数的定义
对于CPXS数据库，定义完成如下功能的函数： （1）据产品名称，查询该产品的相关信息。（函数名为FU_CP）。
（2）按某年某季度统计给定产品名称的销售数量及销售金额，分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）。 2、函数的调用
（1）对函数FU_CP，查询产品名称为“MP3”的产品情况。
（2）对函数FU1_CPXS，查询2004年第3季度彩色电视机的销售数量和销售金额。
（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额。
（4）对函数FU3_CPXS，查询厂电公司2004年第1季度销售的产品名称、销售数量和销售金额。
三、试验步骤
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(10)) RETURNS TABLE AS RETURN
(SELECT 产品名称,SUM(数量) AS 销售数量,SUM(销售额)AS 销售总额 FROM CPXSB,CP
WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称=@产品名称
AND DATEPART(YY,销售日期)=@YEAR AND DATEPART(QQ,销售日期)=@QUARTER GROUP BY 产品名称)
截图如下：
//建立多语句表值函数
CREATE FUNCTION FU2_CPXS(@YEAR INT,@QUARTER INT,@产品名称char(10)) RETURNS @销售情况TABLE (产品名称char(10), 销售数量int, 销售金额float) AS BEGIN
INSERT INTO @销售情况
SELECT 产品名称,SUM(数量) AS 销售数量,SUM(销售额)AS 销售总额 FROM CPXSB,CP
WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称=@产品名称
AND DATEPART(YY,销售日期)=@YEAR AND DATEPART(QQ,销售日期)=@QUARTER GROUP BY 产品名称 RETURN END
截图如下：
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）。
CREATE FUNCTION FU3_CPXS(@YEAR INT,@QUARTER INT,@客户名称char(30)) RETURNS TABLE AS RETURN
(SELECT 产品名称,SUM(数量) AS 销售数量,SUM(销售额)AS 销售总额 FROM CPXSB,CP,XSS
WHERE CPXSB.产品编号=CP.产品编号 AND CPXSB.客户编号=XSS.客户编号 AND 客户名称=@客户名称
AND DATEPART(YY,销售日期)=@YEAR AND DATEPART(QQ,销售日期)=@QUARTER GROUP BY 产品名称)
截图如下：
2、函数的调用
（1）对函数FU_CP，查询产品名称为“MP3”的产品情况。
SELECT * FROM FU_CP('MP3')
截图如下：
（2）对函数FU1_CPXS，查询2004年第3季度彩色电视机的销售数量和销售金额。
SELECT * FROM FU1_CPXS(2004,3,'彩色电视机')
截图如下：
（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额。
SELECT * FROM FU2_CPXS(2004,1,'洗衣机') 截图如下：
（4）对函数FU3_CPXS，查询厂电公司2004年第1季度销售的产品名称、销售数量和销售金额。
SELECT * FROM FU3_CPXS(2004,1,'厂电公司') 截图如下：
四、试验心得
通过本次课程的学习与试验，了解了如何在数据库中使用T-SQL完成具有相应功能的标量函数、内嵌表值函数和多语句表值函数。刚开始由于对于内嵌表值函数和多语句表值函数的转换有点搞不清，所以使得试验难以进行下去，不过最后进行一些资料的查找以及老师的帮助下，了解了二者之间的联系并最终完成了试验。
试验十 触发器
一、试验目的与要求
1、熟练掌握后触发器和替代触发器的区别。 2、熟练掌握后触发器和替代触发器的创建。
二、试验内容
对于CPXS数据库，完成如下各项：
（1）写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，销售商编号与销售商表中对应字段一致。其中触发器分别用后触发器和替代触发器实现。
（2）在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。
三、试验步骤
（1）写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，销售商编号与销售商表中对应字段一致。其中触发器分别用后触发器和替代触发器实现。
用后触发器：
CREATE TRIGGER tr_cpxsb ON CPXSB FOR INSERT AS
IF NOT EXISTS (SELECT * FROM CP WHERE 产品编号=
(SELECT 产品编号 FROM INSERTED))
OR NOT EXISTS (SELECT * FROM XSS WHERE 客户编号=
(SELECT 客户编号 FROM INSERTED))
RAISERROR('数据不一致',16,1) ROLLBACK TRANSACTION
BEGIN
END
用替代触发器：
CREATE TRIGGER tr_cpxsb ON CPXSB INSTEAD OF INSERT AS
IF NOT EXISTS (SELECT * FROM CP WHERE 产品编号=
(SELECT 产品编号 FROM INSERTED))
OR NOT EXISTS (SELECT * FROM XSS WHERE 客户编号=
(SELECT 客户编号 FROM INSERTED))
RAISERROR('数据不一致',16,1) ROLLBACK TRANSACTION
BEGIN
END
截图如下：
（2）在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。 方法1：用IF UPDATE(column)
CREATE TRIGGER TR1_CPXSB ON CPXSB FOR UPDATE AS
IF UPDATE(产品编号) OR UPDATE(客户编号) BEGIN
RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION
END
方法2：用IF COLUMNS_UPDATED()函数
CREATE TRIGGER TR2_CPXSB ON CPXSB FOR UPDATE AS
IF (COLUMNS_UPDATED()&3)>0 BEGIN
RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION
END
截图如下：