课后实训参考答案
单元1（SQL Server概述）
1、使用SQL语句。在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NVARCHAR，长度为20，该列允许为NULL。 USE Sale GO
EXEC sp_addtype MyDataType,'NVARCHAR(20)','NULL' GO
单元2（单表数据）
使用查询窗口或sqlcmd实施查询。本实训使用Xk数据库。 --1．查看系部编号为“03”的系部名称。 USE Xk GO
SELECT DepartName FROM Department WHERE DepartNo='03' GO
--2．查看系部名称中包含有“工程”两个字的系的全名。 USE Xk
徐人凤
共 1 页 第 1 页
5/19/2015 1
1
GO
SELECT DepartName FROM Department
WHERE DepartName LIKE '%工程%' GO
--3．显示共有多少个系部。 USE XK GO
SELECT '系部总数'=COUNT(*) FROM Department GO
--4．显示“01”年级共有多少个班级。 USE XK GO
SELECT '01 级班级数'=COUNT(*) FROM Class
WHERE ClassNo LIKE '2001%' GO
SELECT '01 级班级数'=COUNT(*) FROM Class
徐人凤
共 2 页 第 2 页
5/19/2015 2
2
WHERE ClassName LIKE '01%' GO
SELECT '01 级班级数'=COUNT(*) FROM Student
WHERE ClassNo LIKE '2001%' GO
--5．查看在“周二晚”上课的课程名称和教师。 USE XK GO
SELECT '课程名称'=CouName,'任课教师'=Teacher FROM Course
WHERE SchoolTime='周二晚' GO
--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。 USE XK GO SELECT * FROM Student
WHERE StuName LIKE '张%' OR
徐人凤
共 3 页 第 3 页
5/19/2015 3
3
StuName LIKE '陈%' OR StuName LIKE '黄%' ORDER BY StuName DESC GO --方法 USE XK GO SELECT * FROM Student
WHERE StuName like '[张,陈,黄]%' ORDER BY StuName DESC GO
徐人凤 共 4 页 第 4 页 5/19/2015 4 4
单元2（多表查询）
--1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。
SELECT '系部名称'=DepartName,'平均报名人数'=AVG(WillNum)
FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName GO
--如果小数点后只保留位
SELECT '系部名称'=DepartName,'平均报名数'=CONVERT(DECIMAL(5,2),AVG(WillNum)) FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName GO
--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。
SELECT C.DepartNo,DepartName,COUNT(*)
徐人凤
共 5 页 第 5 页
5/19/2015 5
5
FROM Class C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY C.DepartNo,DepartName GO
--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。
SELECT CouName,Credit,SchoolTime,WillOrder FROM StuCou SC,Course C,Student S
WHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND StuName='甘蕾' ORDER BY WillOrder GO
--4．查看“00电子商务”班的选修报名情况。要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。
SELECT S.StuNo,StuName,C.CouNo,CouName,WillOrder FROM StuCou SC,Course C,Student S,Class CL WHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND CL.ClassNo=S.ClassNo AND
徐人凤
共 6 页 第 6 页
5/19/2015 6
6
ClassName Like '00电子商务' ORDER BY S.StuNo,WillOrder GO
--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人数保留两位小数位。
SELECT '系部名称'=DepartName,'最少报名人数
'=MIN(WillNum),'最多报名人数'=MAX(WillNum),'平均报名人数'=CONVERT(DECIMAL(5,2),AVG(WillNum)),'报名总数'=SUM(WillNum)
FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName WITH CUBE GO
徐人凤 共 7 页 第 7 页 5/19/2015 7 7
单元3（维护数据）
--12．学号为“00000025”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。
INSERT StuCou(StuNo,CouNo,WillOrder,State) VALUES('00000025','001',1,'报名') GO
/*课程报名人数增加人*/ UPDATE Course
SET WillNum=WillNum+1 WHERE CouNo='001' GO
--2．删除学号为“00000025”的学生的选课报名信息。
DELETE StuCou
WHERE StuNo='00000025' GO
/*课程表'00000025'同学报名的那些课程报名人数都要减少1*/ --方法： UPDATE Course
SET WillNum=WillNum-1
徐人凤
共 8 页 第 8 页
5/19/2015 8
8
FROM Course,StuCou
WHERE StuNo='00000025' and Course.CouNo=StuCou.CouNo GO
--方法2：（游标）
DECLARE @CouNo char(3) DECLARE MYCursor cursor for
SELECT CouNo from StuCou WHERE StuNo='00000025' OPEN MYCursor
FETCH NEXT FROM MYCursor INTO @CouNo WHILE @@FETCH_STATUS=0 BEGIN
UPDATE Course SET WillNum=WillNum-1 WHERE CouNo=@CouNo
FETCH NEXT FROM MYCursor INTO @CouNo END
CLOSE MYCursor DEALLOCATE MYCursor GO
徐人凤
共 9 页 第 9 页
5/19/2015 9
9
--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。
UPDATE Student SET StuName='杜小静' FROM Student S,Class C
WHERE S.ClassNo=C.ClassNo AND
StuName='杜晓静' AND ClassName='00多媒体' GO
--“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。 UPDATE StuCou
SET CouNo=(SELECT CouNo FROM Course WHERE CouName='Linux操作系统')
FROM StuCou SC,Student S,Course C,Class Cl WHERE SC.StuNo=S.StuNo AND SC.CouNo=C.CouNo AND S.ClassNo=Cl.ClassNo AND S.StuName='林斌' AND
Cl.ClassName='00电子商务' AND CouName='网络信息检索原理与技术'
徐人凤
共 10 页 第 10 页
5/19/2015 10
10
GO
徐人凤
共 11 页 第 11 页5/19/2015 11 11
单元4（数据库设计）
某公司计划对产品的销售情况进行计算机管理。产品表具有产品编号、产品名称、单价、库存数量4个属性。客户表包括有客户编号、客户姓名、住址、联系电话4个属性。产品的入库表包括有入库日期、产品编号、产品名称、入库数量、单价属性。销售表包括有销售日期、产品编号、产品名称、客户编号、客户姓名、单价、销售数量。
1．绘出产品销售的E-R图。
产品编号 销售日期 P 产品编号 产品名称客户编号 产品 Q 入库 库房 产品名称 单价 库存数量 属性（略） m 销售 n 客户表 入库数量 产品名称 产品编号 单价 入库日期 客户姓名 单价 销售数量 客户编号 客户姓名 住址 联系电话
2、将产品销售的E-R图转换为关系数据模型。 产品表：
徐人凤
共 12 页 第 12 页
5/19/2015 12
12
产品编号 产品名称 单价 库存数量 客户表： 客户编号 客户名称 住址 联系电话 入库表： 入库日期 产品编号 产品名称 入库数量 单价 销售表： 销售产品产品客户客户单价 销售日期 编号 名称 编号 姓名 数量
3、将产品销售关系数据模型规范为Ⅲ范式。 可参考“创建和管理表”实训题。
徐人凤
共 13 页 第 13 页
5/19/2015 13
13
4、请分析主键、外键、公共键。
主键：产品表的产品编号，客户表的客户编号。
外键：入库表的产品编号，销售表的产品编号、客户编号。
5、举例说明如何保证产品销售数据的完整性。 表数据完整性：给产品表、客户表创建主键。
列数据的完整性：可以给指定的列创建约束（参见实施数据完整性实训题目）。
参照完整性：给入库表、销售表创建外键。
徐人凤 共 14 页 第 14 页 5/19/2015 14 14
单元5（创建与管理数据库） 写出实现如下功能的SQL语句。
1．创建名字为Sale的销售数据库。该数据库有一个名为Sale.mdf的主数据文件和名字为Sale_log.ldf的事务日志文件。主数据文件容量为4MB，日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增长量为1MB。 CREATE DATABASE Sale ON
PRIMARY (NAME=Sale,
FILENAME='C:\\Sale.mdf', SIZE =4, MAXSIZE=10, FILEGROWTH=1) LOG ON
( NAME=Sale_log,
FILENAME='D:\\Sale_log.ldf', SIZE=2, MAXSIZE= 10,
FILEGROWTH=1) /*注意结尾处无逗号*/ GO
徐人凤
共 15 页 第 15 页
5/19/2015 15
15
2．在Sale数据库下新增名字为UserGroup的文件组。 USE Sale Go
ALTER DATABASE Sale ADD FILEGROUP UserGroup Go
3．以增加次数据文件的方式扩充Sale数据的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。 USE MASTER GO
ALTER DATABASE Sale ADD FILE (NAME=Sale2,
FILENAME='C:\\Sale2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB)
TO FILEGROUP UserGroup GO
徐人凤
共 16 页 第 16 页
5/19/2015 16
16
4．使用存储过程显示Sale数据库的信息。 SP_helpdb Sale GO
徐人凤 共 17 页 第 17 页 5/19/2015 17 17
单元6（创建与管理数据表）
1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。请将脚本保存为.sql文件。
表1 Customer（客户表）结构
列名 数据类是否允备注 型及长度 许为空 CusNo nvarcharNOT （3） NULL 客户编号 CusNamnvarcharNOT e （10） NULL 客户姓名 Address nvarchar（20） NULL 地址 Tel nvarchar（20） NULL 联系电话
表2 Product（产品表）结构
列名 数据类是否允型及长度 许为空 备注 ProNo nvarcharNOT （5） NULL 产品编号 ProNamnvarcharNOT e （20） NULL 产品名 Price DecimalNOT （8,2） NULL 单价 Stocks DecimalNOT （8,0） NULL 库存数量
表3 ProIn（入库表）结构
徐人凤
共 18 页 第 18 页
5/19/2015 18
18
列名 数据类是否允备注 型及长度 许为空 InputDatNOT e DateTime NULL 入库日期 ProNo nvarcharNOT （5） NULL 产品编号 Quantity DecimalNOT （6,0） NULL 入库数量
表4 ProOut（销售表）结构
列名 数据类是否允备注 型及长度 许为空 SaleDate DateTime NOT NULL 销售日期 CusNo nvarcharNOT （3） NULL 客户编号 ProNo nvarchar NOT r（5） NULL 产品编号 Quantity DecimalNOT （6,0） NULL 销售数量
表5 Customer（客户表）数据
CusNo CusName Address Tel 001 杨婷 深圳 0755-22221111 002 陈萍 深圳 0755-22223333 003 李东 深圳 0755-22225555 004 叶合 广州 020-22227777 005 谭新 广州 020-22229999
表6 Product（产品表）数据
徐人凤
共 19 页 第 19 页
5/19/2015 19
19
ProNo ProName Price Stocks 00001 电视 3000.00 800 00002 空调 2000.00 500 00003 床 1000.00 300 00004 餐桌 1500.00 200 00005 音响 5000.00 600 00006 沙发 6000.00 100
表7 ProIn（入库表）数据
InputDate（入库日期） ProNo（产品编号） Quantity（入库数DateTime nvarchar（5） 量） not null not null Decimal（6,0） not null 2006-1-1 00001 10 2006-1-1 00002 5 2006-1-2 00001 5 2006-1-2 00003 10 2006-1-3 00001 10 2006-2-1 00003 20 2006-2-2 00001 10 2006-2-3 00004 30 2006-3-3 00003 20
表8 ProOut（销售表）数据
SaleDate CusNo ProNo Quantity 2006-1-1 001 00001 10 2006-1-2 001 00002 5 2006-1-3 002 00001 5 2006-2-1 002 00003 10 2006-2-2 001 00001 10 2006-2-3 001 00003 20 2006-3-2 003 00001 10 2006-3-2 003 00004 30 2006-3-3 002 00003 20 徐人凤
共 20 页 第 20 页
5/19/2015 20
20
--该题创建表时同时创建主键、外键。先创建表，再创建主键、外键的方法参见照实训5_3 Use Sale Go
CREATE TABLE Customer
(CusNo nvarchar(3) Primary key, CusName nvarchar(10)not null, Address nvarchar(20), Tel Char(20)) Go
/*创建产品表*/
CREATE TABLE Product
(ProNo nvarchar(5)Primary key, ProName nvarchar(20)not null, Price Decimal(8,2)not null, Stocks Decimal(8,0)not null) Go
/*创建入库表*/
徐人凤
共 21 页 第 21 页
5/19/2015 21
21
CREATE TABLE ProIn (InputDate DateTime not null,
ProNo nvarchar(5)References Product(ProNo), Quantity Decimal(6,0)not null) Go
/*创建销售表*/
CREATE TABLE ProOut (SaleDate DateTime not null,
CusNo nvarchar(3)not null References Customer(CusNo),
ProNo nvarchar(5)not null References Product(ProNo),
Quantity Decimal(6,0)not null) Go
--向Customer表输入数据-- INSERT INTO
Customer VALUES('001','杨婷','深圳','0755-22221111') INSERT INTO
Customer VALUES('002','陈萍','深圳
徐人凤
共 22 页 第 22 页
5/19/2015 22
22
','0755-22223333') INSERT INTO
Customer VALUES('003','李东','深圳','0755-22225555') INSERT INTO
Customer VALUES('004','叶合','广州','020-22227777') INSERT INTO
Customer VALUES('005','谭新','广州','020-22229999') Go
--向Product表中插入数据-- INSERT INTO
Product VALUES('00001','电视',3000.00,800) INSERT INTO
Product VALUES('00002','空调',2000.00,500) INSERT INTO
Product VALUES('00003','床',1000.00,300) INSERT INTO
Product VALUES('00004','餐桌',1500.00,200) INSERT INTO
徐人凤
共 23 页 第 23 页
5/19/2015 23
23
Product VALUES('00005','音响',5000.00,600) INSERT INTO
Product VALUES('00006','沙发',6000.00,100) Go
--向ProIn表中插入数据-- INSERT INTO
ProIn VALUES('2006-1-1','00001',10) INSERT INTO
ProIn VALUES('2006-1-1','00002',5) INSERT INTO
ProIn VALUES('2006-1-2','00001',5) INSERT INTO
ProIn VALUES('2006-1-2','00003',10) INSERT INTO
ProIn VALUES('2006-1-3','00001',10) INSERT INTO
ProIn VALUES('2006-2-1','00003',20) INSERT INTO
ProIn VALUES('2006-2-2','00001',10) INSERT INTO
ProIn VALUES('2006-2-3','00004',30)
徐人凤
共 24 页 第 24 页
5/19/2015 24
24
INSERT INTO
ProIn VALUES('2006-3-3','00003',20) Go
--向ProOut表中插入数据-- INSERT INTO
ProOut VALUES('2006-1-1','001','00001',10) INSERT INTO
ProOut VALUES('2006-1-2','001','00002',5) INSERT INTO
ProOut VALUES('2006-1-3','002','00001',5) INSERT INTO
ProOut VALUES('2006-2-1','002','00003',10) INSERT INTO
ProOut VALUES('2006-2-2','001','00001',10) INSERT INTO
ProOut VALUES('2006-2-3','001','00003',20) INSERT INTO
ProOut VALUES('2006-3-2','003','00001',10) INSERT INTO
ProOut VALUES('2006-3-2','003','00004',30) INSERT INTO
徐人凤
共 25 页 第 25 页
5/19/2015 25
25
ProOut VALUES('2006-3-3','002','00003',20) Go
徐人凤 共 26 页 第 26 页 5/19/2015 26 26
单元7（实施数据完整性）
1．根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。
表1 Customer（客户表）结构
列名 数据类是否允备注 型及长度 许为空 CusNo nvarcharNOT （3） NULL 客户编号 主键 CusNamnvarcharNOT e （10） NULL 客户姓名 Address nvarchar（20） NULL 地址 Tel nvarchar（20） NULL 联系电话
表2 Product（产品表）结构
列名 数据类是否允型及长度 许为空 备注 ProNo nvarcharNOT （5） NULL 产品编号 主键 ProNamnvarcharNOT e （20） NULL 产品名 Price DecimalNOT （8,2） NULL 单价 Stocks DecimalNOT （8,0） NULL 库存数量
表3 ProIn（入库表）结构
列名 数据类是否允备注 型及长度 许为空 InputDate DateTime NOT NULL 入库日期 ProNo NvarcharNOT 产品编号 外键 徐人凤
共 27 页 第 27 页
5/19/2015 27
27
（5） NULL Quantity DecimalNOT （6,0） NULL 入库数量
表4 ProOut（销售表）结构
列名 数据类是否允备注 型及长度 许为空 SaleDate DateTime NOT NULL 销售日期 CusNo nvarcharNOT （3） NULL 客户编号 外键 ProNo nvarchar NOT r（5） NULL 产品编号 外键 Quantity DecimalNOT （6,0） NULL 销售数量
--该题适用于创建表时没有创建主键、外键的情况 /* Customer表的主键为CusNo*/ ALTER TABLE Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CusNo) GO
/* Product表的主键为ProNo*/ ALTER TABLE Product
ADD CONSTRAINT PK_Product PRIMARY KEY (ProNo) GO
/*外键：ProOut 表的ProNo*/
徐人凤
共 28 页 第 28 页
5/19/2015 28
28
ALTER TABLE ProOut
ADD CONSTRAINT FK_ProOut_Product FOREIGN KEY (ProNo)
REFERENCES Product (ProNo)
/*外键：ProOut 表的CusNo*/ ALTER TABLE ProOut
ADD CONSTRAINT FK_ProOut_Customer FOREIGN KEY (CusNo)
REFERENCES Customer(CusNo)
/*外键：ProIn 表的ProNo*/ ALTER TABLE ProIn
ADD CONSTRAINT FK_ProIn_Product FOREIGN KEY (ProNo)
REFERENCES Product(ProNo)
--3．约束客户表Customer的CusNo列值长度为3；产品表Product的ProNo列值长度为5。 ALTER TABLE Customer
ADD CONSTRAINT CK_Customer_CusNo CHECK (LEN(CusNo)=3)
徐人凤
共 29 页 第 29 页
5/19/2015 29
29
GO
ALTER TABLE Product
ADD CONSTRAINT CK_Product_ProNo CHECK (LEN(ProNo)=5) GO
--测试： INSERT INTO
Product VALUES('000071','平板电脑',3000.00,800) GO
--系统会自动截断为长度5位。 --测试： INSERT INTO
Product VALUES('009','DVD',3000.00,800) GO
4．对产品表Product的Stocks列、Price列、入库表ProIn的Quantity列、销售表ProOut的Quantity列值进行约束，使其值必须大于0。 ALTER TABLE Product
ADD CONSTRAINT CK_Product_Stocks CHECK (Stocks>0) GO
徐人凤
共 30 页 第 30 页
5/19/2015 30
30
ALTER TABLE Product
ADD CONSTRAINT CK_Product_Price CHECK (Price>0) GO
ALTER TABLE ProIn
ADD CONSTRAINT CK_ProIn_Quantity CHECK (Quantity>0) GO
ALTER TABLE ProOut
ADD CONSTRAINT CK_ProOut_Quantity CHECK (Quantity>0) GO
5．对销售表ProOut的SaleDate列进行约束，当不输入值时，系统默认其值为系统当前日期。
CREATE DEFAULT CurrentDate AS GETDATE() GO
EXEC sp_bindefault CurrentDate,'ProOut.SaleDate' GO
徐人凤 共 31 页 第 31 页 5/19/2015 31 31
单元8（提高查询速度） 本单元实训使用Sale数据库。
1．用户按照CusName（客户姓名）查询客户信息，希望提高查询速度。 USE Sale GO
CREATE INDEX IX_Customer ON Customer (CusName) GO
2．用户按照ProName（产品名称）查询产品信息，希望提高查询速度。 USE Sale GO
CREATE INDEX IX_Product ON Product (ProName) GO
3．用户按照SaleDate（销售日期）查询销售信息，希望提高查询速度。
徐人凤
共 32 页 第 32 页
5/19/2015 32
32
USE Sale GO
CREATE INDEX IX_ProOut ON ProOut (SaleDate) GO
徐人凤 共 33 页 第 33 页 5/19/2015 33 33
单元9（Transcact-SQL语言编程基础） 本单元实训使用Sale数据库。
1．计算有多少种产品（假设为@x），然后显示一条信息：共有x种产品。
USE Sale GO
DECLARE @x int
SET @x=(SELECT COUNT(*) FROM Product) PRINT '共有'+convert(char(2),@x) +'种产品。' GO
2．编写计算n!（n=20）的程序，并显示计算结果。
DECLARE @i int,@PRODUCT FLOAT --定义整型变量@i用来计数；@Product为存放结果的单元
SELECT @i=1,@PRODUCT=1 --给整型变量@i和@sum赋值 WHILE @i<=20 --当@i小于等于时，执行循环体
BEGIN --定义语句块 SELECT @PRODUCT=@PRODUCT*@i --求和 SELECT @i=@i+1 --计数单元加 END --语句块定义结束
PRINT '20！='+convert(char(11),@PRODUCT) --显示
徐人凤
共 34 页 第 34 页
5/19/2015 34
34
结果 GO
3、创建一自定义函数，能够利用该函数计算出销售总金额（数量Quantity*单价Price）。
CREATE FUNCTION GETProduct (@Quantity decimal(6,0), @Price decimal(8,2)) RETURNS decimal(10,2) AS BEGIN
RETURN (@Quantity*@Price) END --测试 SELECT
Price,Quantity,dbo.GETProduct(Price,Quantity) 'ProductQu'
FROM Product,ProOut
WHERE Product.ProNo=ProOut.ProNo GO
徐人凤
共 35 页 第 35 页
5/19/2015 35
35
单元10（保证数据安全性_视图） 本实训使用Sale数据库。
1．创建视图V_Sale1，显示销售日期、客户编号、客户姓名、产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale1 AS SELECT
SaleDate,CusName,ProName,Price,Quantity,SaleMoney=(Price*Quantity)
FROM ProOut PO,Customer C,Product P
WHERE P.ProNo=PO.ProNo AND C.CusNo=PO.CusNo GO
2．创建视图V_Sale2，统计每种产品的销售数量和销售金额。统计结果包括：产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale2 AS SELECT
P.ProNo,P.ProName,SaleQuantity=SUM(Quantity),SaleMoney=SUM(Price*Quantity) FROM ProOut PO,Product P
徐人凤
共 36 页 第 36 页
5/19/2015 36
36
WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName GO
3．创建视图V_Sale3，统计销售金额在10万以下的产品信息。
CREATE VIEW V_Sale3 AS
SELECT P.ProNo,P.ProName FROM ProOut PO,Product P WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName
HAVING SUM(Price*Quantity)<100000 GO
徐人凤 共 37 页 第 37 页 5/19/2015 37 37
单元11（保证数据安全性_存储过程） 本实训使用Sale数据库。
1．创建存储过P_Sale1，统计每种产品的销售数量和销售金额。 CREATE PROCEDURE P_Sale1 AS SELECT
Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product
WHERE ProOut.ProNo=Product.ProNo GROUP BY Product.ProNo,ProName,Price GO --调用 EXEC P_Sale1
2．创建存储过程P_Sale2，能够根据指定的客户统计汇总该客户购买每种产品的数量和花费金额。 CREATE PROCEDURE P_Sale2 @CusNo nvarchar(3)
徐人凤
共 38 页 第 38 页
5/19/2015 38
38
AS SELECT
Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product
WHERE ProOut.ProNo=Product.ProNo AND CusNo=@CusNo
GROUP BY Product.ProNo,ProName,Price GO --调用
EXEC P_Sale2 '001' EXEC P_Sale2 '002' GO
3．创建存储过程P_Sale3，能够根据指定的产品编号和日期，以输出参数的形式得到该产品的销售金额。 USE SALE GO
CREATE PROCEDURE P_Sale3
@ProNo nvarchar(5),@SaleDate DateTime,@Je Decimal(8,2) OUTPUT
徐人凤
共 39 页 第 39 页
5/19/2015 39
39
AS SET @Je=
(SELECT SUM(Price*Quantity) FROM ProOut,Product
WHERE ProOut.ProNo=Product.ProNo AND Product.ProNo=@ProNo AND SaleDate=@SaleDate GROUP BY Product.ProNo,ProName,Price )
print @je GO --调用
DECLARE @Je Decimal(8,2)
EXEC P_Sale3 '00001','2006-1-1',@Je GO
徐人凤 共 40 页 第 40 页 5/19/2015 40 40
单元12（实现业务逻辑（触发器）） 本实训使用Sale数据库。
1．创建触发器，实现即时更新每种产品的库存数量。 --对入库表进行增加、修改入库数量、删除数据时
--向ProIn表输入数据时（入库），Product表库存Stocks=库存+入库的数量Quantity
CREATE TRIGGER T_INSERT_ProIn ON ProIn FOR INSERT AS
UPDATE Product
SET Stocks=Stocks+(SELECT Quantity FROM INSERTED)
WHERE ProNo=(SELECT ProNo FROM INSERTED) PRINT '已更新产品表的库存数量' GO --测试 INSERT ProIn
values('2006-3-5','00003',100)
徐人凤
共 41 页 第 41 页
5/19/2015 41
41
GO
--删除ProIn一行数据时，Product表库存Stocks=库存-入库表的数量Quantity
CREATE TRIGGER T_DELETE_ProIn ON ProIn FOR DELETE AS
UPDATE Product
SET Stocks=Stocks-(SELECT Quantity FROM DELETED) WHERE ProNo=(SELECT ProNo FROM DELETED) PRINT '已更新产品表的库存数量' GO --测试 DELETE ProIn
WHERE InputDate='2006-3-3' and ProNo='00003' GO
徐人凤
共 42 页 第 42 页
5/19/2015 42
42
--修改ProIn表的Quantity入库数量时 CREATE TRIGGER T_UPDATE_ProIn ON ProIn FOR UPDATE AS
IF UPDATE(Quantity) BEGIN
UPDATE Product
SET Stocks=Stocks+(SELECT Quantity FROM INSERTED)
WHERE ProNo=(SELECT ProNo FROM INSERTED) UPDATE Product --测试 UPDATE ProIn SET Quantity=500
WHERE InputDate='2006-3-5' and ProNo='00003' GO
--在销售表上创建触发器
徐人凤
共 43 页 第 43 页
5/19/2015 43
43
CREATE TRIGGER T_INSERT_ProOut ON ProOut FOR INSERT AS
IF (SELECT Quantity FROM INSERTED)<= (SELECT Stocks FROM Product
WHERE ProNo=(SELECT ProNo FROM INSERTED)) BEGIN
UPDATE Product
SET Stocks=Stocks-(SELECT Quantity FROM INSERTED)
写到这里了 WHERE ProNo=(SELECT ProNo FROM INSERTED)
PRINT '已更新产品表的库存数量' END ELSE BEGIN
PRINT '销售数量低于库存量，不能销售！' ROLLBACK TRAN END GO
徐人凤
共 44 页 第 44 页
5/19/2015 44
44
--测试
INSERT ProOut
VALUES('2006-3-6','001','00003',1000) GO
CREATE TRIGGER T_DELETE_ProOut ON ProOut FOR DELETE AS
UPDATE Product
SET Stocks=Stocks+(SELECT Quantity FROM DELETED) WHERE ProNo=(SELECT ProNo FROM DELETED) PRINT '已更新产品表的库存数量' GO --测试
DELETE ProOut
WHERE SaleDate='2006-3-6' and
ProNo='00003' AND QUANTITY=1000 GO
徐人凤
共 45 页 第 45 页
5/19/2015 45
45
2．使用IF UPDATE(column)尽可能优化上题中的触发器以提高系统效率。
CREATE TRIGGER T_UPDATE_ProOut ON ProOut FOR UPDATE AS
IF UPDATE(Quantity) BEGIN
UPDATE Product SET Stocks=Stocks-(SELECT Quantity FROM INSERTED)
WHERE ProNo=(SELECT ProNo FROM INSERTED)
UPDATE Product SET Stocks=Stocks+(SELECT Quantity FROM DELETED)
WHERE ProNo=(SELECT ProNo FROM DELETED) PRINT '已更新产品表的库存数量' END GO
UPDATE ProOut SET Quantity=50
WHERE SaleDate='2006-1-1' and CusNo='001' and ProNo='00001' and Quantity=10
徐人凤
共 46 页 第 46 页
5/19/2015 46
46
GO
CREATE TRIGGER T3 ON ProIn FOR UPDATE AS
IF UPDATE(Quantity) BEGIN
UPDATE Product SET Stocks=Stocks+(SELECTQuantity FROM INSERTED)
WHERE ProNo=(SELECT ProNo FROM INSERTED)
UPDATE Product SET Stocks=Stocks-(SELECTQuantity FROM DELETED)
WHERE ProNo=(SELECT ProNo FROM INSERTED) END GO
CREATE TRIGGER T6 ON ProOut FOR UPDATE AS
徐人凤
共 47 页 第 47 页
5/19/2015 47
47
IF UPDATE(Quantity) BEGIN
UPDATE Product SET Stocks=Stocks-(SELECT Quantity FROM INSERTED)
WHERE ProNo=(SELECT ProNo FROM INSERTED)
UPDATE Product SET Stocks=Stocks+(SELECTQuantity FROM DELETED)
WHERE ProNo=(SELECT ProNo FROM DELETED) END GO
徐人凤 共 48 页 第 48 页 5/19/2015 48 48
单元13（逐行处理查询结果_游标） 本实训使用Sale数据库。
1．创建存储过程P_SelProduct，逐行显示产品销售信息，内容包括产品编号、产品名称、销售日期、销售数量、销售金额，要求显示格式如下：
产品编号 产品名称 销售日期 销售数量 销售
金额
00001 电视 2006-1-1 10 30000
产品编号 产品名称 销售日期 销售数量 销售
金额
00002 空调 2006-1-2 5 10000
CREATE PROCEDURE P_SelProduct AS
DECLARE @ProNo nvarchar(5),@ProName nvarchar(20),@SaleDate DateTime,@Quantity decimal(6,0),@Je decimal(8,2) --定义游标
DECLARE Crs CURSOR FOR SELECT
P.ProNo,ProName,SaleDate,Quantity,Je=Price*Quanti
徐人凤
共 49 页 第 49 页
5/19/2015 49
49
ty
FROM Product P,ProOut PO WHERE P.ProNo=PO.ProNo ORDER BY SaleDate --打开游标 OPEN Crs --取得第一行数据
FETCH NEXT FROM Crs INTO
@ProNo ,@ProName,@SaleDate,@Quantity,@Je --通过判断@@FETCH_STATUS控制循环 WHILE @@FETCH_STATUS=0 BEGIN
PRINT '产品编号 产品名称 销售日期 销售数量 销售金额 '
PRINT @ProNo+' '+@ProName+' '+STR(YEAR(@SaleDate),4)+
'-'+STR(MONTH(@SaleDate),2)+'-'+STR(DAY(@SaleDate),2)+' '
+Str(@Quantity,6)+' ' +STR(@Je,8,2)+' ' --取得下一行数据
FETCH NEXT FROM Crs INTO
徐人凤
共 50 页 第 50 页
5/19/2015 50
50