《数据库原理与应用》实验报告.docx

1、一、实验目的1、使用企业管理器和T-SQL语句创建和管理数据库。2、熟练掌握使用企业管理器和T-SQL语句创建、修改和删除表。3、熟练掌握使用企业管理器和T-SQL语句插入、修改和删除表数据。4、牢记SELECT语句的基本语法格式；5、熟练掌握使用SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、 HAVING子句和集聚函数；6、牢记SELECT语句的基本语法格式；7、熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。8、熟练掌握IN子查询9、熟练掌握比较子查询和EXISTS子查询二、实验内容实验一1、利用企业管理器创建产品销售数据库CPXS。Create datebase

2、 CPXS;2、CPXS数据库包含如下三个表：1、CP (产品编号，产品名称，价格，库存量)产品CREATE TABLE CP(产品编号 CHAR(6) NOT NULL,产品价格 CHAR (30) NOT NULL,价格 FLOAT (8),库存 INT)2、XSS (客户编号，客户名称，地区，负责人，电话)销售商CREATE TABLE XSS(客户编号 CHAR(6) NOT NULL,客户名 CHAR(30) NOT NULL,地区 CHAR(10),负责人 CHAR(8),电话 CHAR(12)3、CPXSB (产品编号，客户编号，销售日期，数量，销售额)产品销 售表CREATE

3、TABLE CPXSB(产品编号CHAR(6),客户编号CHAR(6),销售日期DATETIME,数量INT,销售额FLOAT(8)4、用T-SQ L语句向CP表插入如下记录：200001柜式空调，3000-200lj200002微波炉，1000.100/20000抽油烟机_。1200-53 INSERT INTO cp VALUES(柜式空调INSERT INTO cp VALUES(微波炉INSERT INTO cp VALUES(抽油烟机5、用T-SQL语句向CP表中增加“产品简介”列，varchar(50)，允许为NULLALTER TABLE cp ADD COLUMN 产品简介 V

4、ARCHAR(50);6、将CP表中每种商品的价格打8折。UPDATE CP SET 价格=价格 *0.8;7、将CP表中价格打9折后小于1500的商品删除。DELETE FROM CP WHERE (价格 *0.9)1500;实验二1、简单查询a. 查询各种产品的产品编号、产品名称和价格。SELECT产品编号，产品名称，价格FROM cp;信息结果1 1K况状志产品弟产品名称价悟100001饨电视机2400100002120010000318001000041亶调2000100005太阳弟热水器1760100007L5四调19201000032匹空渊3040wooii2400b. 查询地区在

5、“南京”的客户编号和客户名称，结果中各列的标题分别 指定为：Customer id 和 Customer name。select客户编号as Customer_id,客户名称 as Customer_namefrom xsswhere地区=南京；信息结果1临兄CustomerjdCu stomer_name*000004nr电ms000005广电公司c. 在CP表中增加1列，标题为“评价”，按以下规则确定：若价格小于1000，内容为“廉价产品”，若价格在1000-2000之间，内容 为“一般产品”，若价格在在2000-3000之间，内容为“昂贵产品”，若价格大 于3000，内容为“很昂贵产品”

6、。SELECT产品编号，产品名称,CASEWHEN价格=1000 THEN 廉价产品WHEN 价格=2000 THEN 一般产品WHEN价格=3000 THEN 昂贵产品ELSE 很昂贵产品END平价FROM cp;信息培果1概况 状态产品精产品名税100001匏电视机晶麦产品100002K产品100003冰精产品1000041匹空调f产品100005太阳能水器产品d. 求各产品编号、名称和产品总值。SELECT产品编号，产品名称,（价格*库存量）AS产品总值FROM CP;信息缚果11S况状志产品编号产漏名停产品总值,100001衫色电视机48000010000224000100003冰箱2

7、1500100004匹至调40000100005术日能烘水H14080e. 查询至少购买了至少一种产品的客户编号。SELECT 客户编号 FROM cpxsb GROUP BY 客户编号 HAVING COUNT（ 数量）=1；给果1状悉吾户*，000002000003000004000005000005f. 查询价格在1000-2000的产品信息。SELECT * FROM CP WHERE 1000 价格 AND 价格 1114320b. 求各种产品20XX年3月18日销售额。DATETIME类型后面会有具体的时间，所以后面也得加SELECT 销售额 FROM cpxsb WHERE 销售

8、日期 LIKE信息 给杲1 整兄 状态整查额800020001200c. 求购买二种以上产品的客户编号。-按照客户编号进行分组，统计出现的行数SELECT 客户编号 FROM cpxsb GROUP BY 客户编号 HAVING COUNT（客户编号）=2;客户娜号,000002000006实验三1、查询在20XX年3月18日有销售的产品名称（不允许重复）SELECT DISTINCT 产品名称 FROM cpxsb, cp WHERE cp. 产品编号=cpxsb. 产品编号 AND CONVERT（销售日期,CHAR（30） LIKE信息 结果1 IK兄状志产晶8称,彩色电视机2、查询名称

9、为“家电市场”的客户在20XX年3月18日购买的产品名称和数量SELECT 产品名称，数量 FROM xss, cpxsb, cpWHERE xss. 客户编号=cpxsb. 客户编号 AND xss. 客户名称 家电市场AND cp. 产品编号=cpxsb. 产品编号 AND cpxsb. 销售日期LIKE因为没有购买所以没有记录I信息 靖果邮兄 状态产品名称S3(Null3、查找所有产品的销售日期、客户名称和数量SELECT产品名称，IFNULL（销售日期,0）AS销售日期，IFNULL（客户名称,0）AS客户名称,IFNULL（数量,0）AS数量FROM CP LEFT OUTER JO

10、IN cpxsb ON cp.产品编号=cpxsb. 产品编号 LEFT OUTER JOIN xss ON cpxsb.客户编号 =xss. 客户编号信息结果1 蚌兄 状态产品名称购曾日期客户名称曜 1.5匹空调0001匹空调2006-05-09 0 家电伽32匹空消2020-06-06 C 电器1冰精2004-05-20 0广电公司7太阳能热水器0004、查询所有产品的名称，销售总量和销售总额SELECT 产品名称,IFNULL（SUM（数量）,0） AS销售总量,IFNULL（SUM（数量*价格），0） AS销售总额FROM CP LEFT OUTER JOIN CPXSBON cp.产

11、品编号=cpxsb.产品编号 GROUP BY产品名称信息结果1械兄 状恣产品名稼傩总星销售总额饨电视机61440033600W71260036000太阳蜘水81005、查询所有客户的名称，购买总量和购买总额SELECT客户名称,SUM（数量）AS购买总量，SUM（数量*价格）AS购买总额 FROM XSS,cpxsb,cpWHERE xss.客户编号=cpxsb.客户编号 AND cp.产品编号=cpxsb. 产品编号 GROUP BY xss. 客户编号11宅is果i况状芸喜户名称购买总量购买总麒，家电响58400电器彻13040电圈场512000rm公司712600E公司236006、查

12、询在20XX年3月18日没有销售的产品名称(不允许重复)SELECT产品名称FROM cpWHERE cp. 产品编号 NOT IN(SELECT DISTINCT 产品编号 FROM cpxsb WHERE 销售日期 LIKE信息给栗1产品名称1.5匹空谢匹由 2匹空调水用蹒水器7、查询销售量大于所有20XX年3月18日销售的各产品销售数量的产品、 编号SELECT 产品名称，cpxsb .产品编号 FROM CP,cpxsbWHERE cpxsb. 产品编号 =cp. 产品编号 AND cpxsb.数量 ALL(SELECT 数量 FROM cpxsb WHERE 销售日期信息结果1产品名

13、称产品 冰4S1000038、查询购买了所有产品的客户的名称。SELECT 客户名称 FROM xssWHERE NOT EXISTS(SELECT * FROM CPWHERE NOT EXISTS (SELECT * FROM CPXSBWHERE cpxsb.产品编号=cp.产品编号AND xss.客户编号=cpxsb.客户编号)group by客户名称信息 结果1概况 状态客户名称9、查询购买了客户编号为“000001 ”的客户购买的所有产品的客户 的名称。SELECT 客户名称 FROM xssWHERE 客户编号000001 AND NOT EXISTS(SELECT * FROM

14、 cpxsbWHERE cpxsb.客户编号=000001 AND NOT EXISTS(SELECT * FROM CPXSB CCWHERE CC.产品编号=cpxsb.产品编号 AND XSS.客户编号 =CC.客户编号);信息 结果欢 状态客户名称嘉电市场电矗市场小家电1街场广电公司广电公司10、查询购买总额最多的客户的名称；SELECT 客户名称 FROM XSS,CPXSB,CPWHERE xss.客户编号=cpxsb.客户编号AND cp.产品编号 =cpxsb.产品编号GROUP BY xss.客户名称 ORDER BY SUM(数量*价格)DESC LIMIT 1IM 唤 .

15、害户名称，广电公司11、查询销售总额前三名的产品名称SELECT产品名称AS销售总额前三的产品FROM CP,CPXSBWHERE cp.产品编号=cpxsb.产品编号GROUP BY cpxsb.产品编号ORDER BY SUM（销售额）DESC LIMIT 3I销售总S?前产品彩色电视机1匹空调实验四1、实体完整性的实现（1）对CP表、CPXSB表、XSS表，定义主键约束；ALTER TABLE CP ADD CONSTRAINT PK_CP PRIMARY KEY产品编号）;ALTER TABLE XSS ADD CONSTRAINT PK_XSS PRIMARY KEY 客户编号 ）；

16、ALTER TABLE CPXSB ADD CONSTRAINT PK_CPXSB PRIMARY KEY产品 编号，客户编号）（2）在CP表的产品名称列定义一个唯一约束；ALTER TABLE cp ADD UNIQUE 产品名称）（3）在XSS表的客户名称列定义一个非空约束；ALTER TABLE XSSCHANGE 客户名称客户名称 CHAR（30） NOT NULL2、参照完整性的实现（1）建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，从表CPXSB采用NO ACTION方式。（2）建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除 操作

17、时，从表CPXSB采用CASCADE （级联）方式。（3）增加外键之后，分别在三个表中增加和删除数据，触发外键，证明其 有效性。3、自定义完整性的实现（1）在CP表的价格列上定义大于等于0的检查约束。ALTER TABLE CPXSB ADD CONSTRAINT FK_CPXSB FOREIGN KEY（产品编号）REFERENCES CP（产品编号）ON DELETE NO ACTION ON UPDATE NO ACTION（2）在CP表的库存量列上定义大于等于0，小于等于1000的检查约束。ALTER TABLE CP ADD CONSTRAINT CHK_KUCUN CHECK（库存

18、量=0 AND 库存量=1000）;（3）在CPXSB表的数量列上定义大于等于0，小于等于500的检查约束。ALTER TABLE CPXSB ADD CONSTRAINT CHK_CPXSB_COUNT CHECK（0= 数量 AND 数量=500）;（4）在CPXSB表的销售额列上定义大于等于0的检查约束。ALTER TABLE CPXSB ADD CONSTRAINT CHK_CPXSB_SALES CHECK（销售额 =0）;（5）在以上两个表中增加数据，触发约束，证明其有效性。insert into cp VALUES（100010,哈雷摩托车,200,T00,null）信息所状态S

19、QLJinsert into cp VALUES（1OOO1O.哈雷摩JR,200,-100,cull）（Err 3819 - Check constraint CHK_KUCUN is violated.实验五1、在产品销售数据库CPXS中创建价格小于2000的产品视图VIEW_CP_PRICE2000,要求加密并保证对该视图的更新都要符合价格小于2000这个条件；CREATE VIEW VIEW_CP_PRICE2000 AS （SELECT * FROM CP WHERE 价格2000）WITH CHECK OPTION;WITH CHECK OPTION2、创建各客户购买产品的情况VI

20、EW_GMQK视图，包括客户编号、客户名称 、产品编号、产品名称、价格，购买日期、购买数量。CREATE VIEW VIEW_GMQK（客户编号，客户名称，产品编号，产品名称，价格，购买日期，购买数量）AS（SELECT CPXSB.客户编号，客户名称,cpxsb.产品编号，产品名称，价格，销售日期，数量FROM XSS,cpxsb,CPWHERE xss.客户编号=cpxsb.客户编号 AND cpxsb.产品编号 =cp. 产品编号）WITH CHECK OPTION;3、创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号 小于等于 100010产品数据，CP1表中

21、为编号大于100010产品 数据，以分区列为产品编号，创建可更新的分区视图VIEW_CP12。CREATE TABLE CP1 LIKE CP;INSERT INTO CP1 SELECT * FROM CP WHERE 产品编号=100010;ALTER TABLE CP1 ADD CONSTRAINT CHK_CP1 CHECK（产品编号 =100010;ALTER TABLE CP2 ADD CONSTRAINT CHK_CP2 CHECK（产品编号 100010）;CREATE VIEW view_cp12 AS SELECT * FROM CP1 UNION ALL SELECT *

22、 FROM CP2;4、基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、 名称和价格。SELECT产品编号，产品名称，价格FROM view_cp_price2000;信息给果1底兄 状态产产品名称洛 1000021200100003冰箱1800100005太阳能热水311760100007.5匹空调19205、基于VIEW_GMQK 视图，查询各客户在20004年3月18日购买产品的 情况。SELECT * FROM VIEW_GMQKWHERE CONVERT (VARCHAR (20)，销售日期，21) LIKE 2004-03-18%；6、对视图VIE

23、W_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 FROM VIEW_CP12 WHERE 产品编号=100042；7、将VIEW_CP_PRICE2000视图改为不加密。-解密视图ALTER VIEW VIEW_CP_PRICE2000 ASS

24、ELECT * FROM CP WITH CHECK OPTION；8、将VIEW-GMQK视图删除。-删除 VIEW_GMQKDROP VIEW VIEW_GMQK；实验六1、变量的定义和赋值创建一名为Customer_name的局部变量，并在SELECT语句中使用该变量查 找“广电公司”购买产品的情况。DECLARE Customer_name char (30)= 广电公司;SELECT CPXSB.客户编号，地区，客户名称，销售日期AS购买时间，数量，销售额AS消费金额FROM CPXSB,XSSWHERE CPXSB.客户编号=XSS .客户编号 AND 客户名称=Customern

25、ame;占结果消息.W尸蕙号地区 客户名称 购买肘回教里消塞金教，00W011 iSZ广电公司20OHJ3.18 00:00:00.00012000OOGDOd南京广电公司20044)-18(X):00:000005800000DOT1I镇江广电公司2004-03-18 00:0000000112002、用户自定义数据类型定义、使用和删除用SQL命令定义一名为Customer_id的用户自定义数据类型，要求char(6)，NOT NULL，并把该自定义数据类型用来定义XSS表中的客户编号，然 后删除该自定义数据类型，请叙述该过程，并写出相关语句。创建自定义数据类型，通过EXEC SP_ADDT

26、YPE语句把属性特征包装起来EXEC sp_addtype Customer_id,char(6),not null;修改表的列，用自定义类型修饰ALTER TABLE XSS ALTER COLUMN 客户编号 Customer_id;无法删除类型dbo.Customer_id，因为它正由对象XSS弓|用。可能还有其他对象在引 用此类型。先接触自定义类型的引用，才能删除ALTER TABLE XSS ALTER COLUMN 客户编号 char (6) not null;EXEC sp_droptype Customer_id;创建用户定义类型方法一：CREATE TYPE SSNFROM

27、varchar(11) NOT NULL ;删除用户定义类型drop type ssn定义两个用户定义类型方法二exec sp_addtype iq, float, nullexec sp_addtype shoesize, float , null指定约束条件create rule iq_range as range between 1 and 200create rule shoesize_range as range between 1 and 20绑定约束类型exec sp_bindrule iq_range,iqexec sp_bindrule shoesize_range,shoe

28、size删除用户定义类型exec sp_droptype iqexec sp_droptype shoesize3、T-SQL语言编程(1) 用T-SQL语言编程输出3300之间能被7整除的数。-用T-SQL语言编程输出之间能被整除的数。declare i int=3while i=300beginif i%7=0beginprint iendset i=i+1end(2) 用T-SQL语言编程输出100以内的素数。一用T-SQL语言编程输出以内的素数。declare i int=2declare j int=2 declare sum int=0while i=10beginwhile ji

29、beginif i%j=0beginbreak;endset j=j+1endif j=ibeginset sum=sum+iendset j=2set i=i+1endprint sum实验七1、无参存储过程。编写一无参存储过程用于查询每个客户购买产品的情况(包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量)，然后调用该存储过程。a) -创建无参存储过程b) alter proc usp_Purchase_informationc) asd) begine) select cpxsb.客户编号，CPXSB .产品编号，客户名称，产品名称，价格，销 售日期AS购买日期，数量

30、AS购买数量f) from cp left join CPXSBg) on cp.产品编号=CPXSB .产品编号 left join xssh) on xss.客户编号=CPXSB .客户编号i) endj) -调用存储过程k) exec usp_Purchase_information% w力消息客用扁号产品墉号喜户名称产品名称伯格购买日期购买麴里1:000001 100001广电公司激色电视机24002004 如 8 00:00:00 000120000041QDD01广电处司衫色电视机24002004-0-18 00:00:00.00053DDOOD1100002广电公司洗衣机1200

31、20044)3-18 00:00(X)00014000002100002家电市场12002004XB-13 00:00 00 00025000005100003科大公司冰箱1SOO2004-05-20 00:00 00 00026NULLNULLNULL电近水器2000NULLNULL7NULLNULLNULL太阳能热水器17G0NULLNULL8NULLNULLNULL1匹空调1S00NULLNULL|言MJUUMl IL 1uuii1岛rrr六泅kmMl II 1II2、带有参数的存储过程。编写一加密存储过程，查询指定客户购买产品的 情况。并调用该存储过程查询客户编号为“000002”的客

32、户购买情况。alter proc usp_encryption (Cnum char(6)with encryption as beginselectcpxsb.客户编号，CPXSB .产品编号， 客户名称， 产品名称， 价格，销售日期AS购买日期，数量AS购买数量from CP,xss,CPXSBwhere cp.产品编号=CPXSB .产品编号 and xss.客户编号=CPXSB.客户编号 and cpxsb .客户编号=Cnum;end-存储过程调用exec usp_encryption Cnum=000002IB结果心消皂睿户娘号产品编号客户名称 产品名称价格购买日期购买数里1 藏反

33、100002家龟市场 洗衣机 1200 2004-03-18 00:00 00 000 23、带有通配符参数的存储过程。编写一存储过程，查询指定产品的销售情 况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。alter proc usp_TongCname char(30) = % 冰箱 asbeginselect distinct *from CPXSB,CPwhere cpxsb .产品编号=cp .产品编号 and 产品名称 like Cnameend调用exec usp_Tong一 客户娘号 销售日期一重 销售额 产显麻号 产品名称 价格 原存里 产品篱介1 wwd3

34、 000005 2004X)20 oaoaoo 000 2 GOOD 100003 冰箱1800 12NULL4、带有OUTPUT参数的存储过程。编写一存储过程，查询指定客户在指定时 间段内购买指定产品的数量，存储过程中使用输入和输出参数。并调用该存储 过程查询名称为“家电市场”的客户在20XX年购买“洗衣机”的数量。alter proc usp_outputXname char(30),Time char(4),Cname char(30),Count int outputasbeginselect Count=数量 from CP,CPXSB,XSSwhere cp.产品编号=CPXSB

35、.产品编号 and xss.客户编号=CPXSB.客户编号 and XSS .客户名称=Xname and convert (char (30), CPXSB .销售日期,21) like %+Time+%and CP.产品名称=Cnameendgodeclare total intexec usp_output Xname=家电市场,Cname=洗衣机,Time=2004,Count=total outputselect isnull (total, 0) as 购买数量E靖果主消息5、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时 ，产品编号与CP表中的对应字段一致，销售商

36、编号与销售商表中对应字段一 致。create trigger trig_insert on CPXSB for insert as begin declare cpbh char(6) declare khbh char(6) -插入临时表 set cpbh=(select inserted .产品编号 from inserted) set khbh=(select inserted.客户编号 from inserted) if ( cpbh not in (select 产品编号 from cp) beginprint 插入数据中产品编号与CP表中产品编号不一致 print cpbh rol

37、lback transaction -回滚 end else print 插入一行数据成功if ( khbh not in ( select 客户编号 from XSS ) beginprint 插入数据中客户编号与XSS表中客户编号不一致 print khbh rollback transaction end else print 插入一行数据成功 end-已经受到外键约束，触发器不起作用insert into CPXSB (产品编号，客户编号，销售日期，数量，销售额)values (100004, 000009, 2008-04-08, 3, 1000)消息消息咀级别馅状态。，熊3行INS

38、E3T语句与FOREIGN KEY约束”FK_CPXSB_X5S”冲突该冲突发生于数据佳*dbo. KSS, eoluan ,吝户编号 语句已狒止,6、在CPXSB上创建一after触发器，若对产品编号列和客户编号列修改，则 给出提示信息，并取消修改操作。create trigger trig_updateon CPXSBafter updateasbeginif update （产品编号）beginprint 不能修改产品编号 rollback transaction end if update （客户编号） beginprint 不能修改客户编号 rollback transaction

39、end end实验八（1）根据据产品名称，查询该产品的相关信息；（函数名为FU_CP）if exists（select* from sysobjects where name=FU_CP and type=FN） drop function FU_CP go-内联函数，返回的是一个表查询create function dbo.FU_CP（Cname char 30） returns table as return（select * from CP where 产品名称=Cname ） go（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名 为FU1_CPXS内嵌表值函数和名为FU

40、2_CPXS的多语句表值函数。-内嵌表值函数 create function dbo.FU1_CPXS（year int,quarter int,Cname char（30） returns table as return（select产品名称,sum （数量）as销售数量,sum （销售额）as销售金额 from CPXSB,CPwhere cp .产品编号=cpxsb .产品编号 and DATEPART （YEAR,销售日期）=year and DATEPART （QUARTER,销售日期）=quarter and CP.产品名称=Cname group by 产品名称） go sele

41、ct * from dbo.FU1_CPXS 2 004 1,洗衣机） go-多语句表值函数create function FU2_CPXS（year int,quarter int,Cname char（30） returns xssj table（产品名称char （30）, 销售数量int, 销售金额float ） as begin insert into xssjselect cp.产品名称，SUM（数量）as销售数量，SUM（销售额）as销售 总额from CPXSB,CPwhere CPXSB .产品编号=cp .产品编号and cp.产品名称=Cnameand DATEPART （YEAR