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

一、实验目的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 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 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 产品简介 VARCHAR(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. 查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别 指定为：Customer id 和 Customer name。select客户编号as Customer_id,客户名称 as Customer_namefrom xsswhere地区='南京；信息结果1临兄CustomerjdCu stomer_name*000004<nr电ms000005广电公司c. 在CP表中增加1列，标题为“评价”，按以下规则确定：若价格小于1000，内容为“廉价产品”，若价格在1000-2000之间，内容 为“一般产品”，若价格在在2000-3000之间，内容为“昂贵产品”，若价格大 于3000，内容为“很昂贵产品”。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冰箱21500100004匹至调40000100005术日能烘水H14080e. 查询至少购买了至少一种产品的客户编号。SELECT 客户编号 FROM cpxsb GROUP BY '客户编号' HAVING COUNT（ 数量'）=1；给果1状悉吾户*，000002000003000004000005000005f. 查询价格在1000-2000的产品信息。SELECT * FROM CP WHERE 1000< 价格 AND '价格'<2000;信息结果1嘏兄 状态产品第号产品财100002120020100003水植180012100005太阳86水81176081000071.5匹空调192020g. 查询产品名称含有”空调”的产品情况。SELECT * FROM CP WHERE产品名称空调信冬蛆果1状态产品骑号=品三称价悟B量产品简介,1000041匹空调200。20100007L5ES 调1920201000082匹空凋30406100011做空调24002002、使用分组和集聚函数先将CPXSB表数据修改如下图所示:产品漏号窘户编号销售日期1谐邸1000010000012004-03-181200010000110000042004-03-1858GOO1000020000012004-03-1811SJ01000020000022004-03-1823GOO1000030000052004-05-20260001000040000022OO6-OS-O98/9000再完成如下查询：a. 计算所有产品总价格。SELECT SUM（价格*库存量）AS产品总值FROM CP;信息 结果1 陆兄状态产品莒值> 1114320b. 求各种产品20XX年3月18日销售额。DATETIME类型后面会有具体的时间，所以后面也得加SELECT 销售额 FROM cpxsb WHERE 销售日期 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、查询名称为“家电市场”的客户在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 JOIN 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.'产品编号'=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、查询在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产品名称产品"> 冰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 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 1I'M 唤 .害户名称，广电公司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 客户编号 ）；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表进行更新和删除 操作时，从表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（库存量=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）信息所状态SQLJinsert 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、创建各客户购买产品的情况VIEW_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表中为编号大于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'）;- CREATE TABLE CP2 LIKE CP;INSERT INTO CP2 SELECT * FROM CP WHERE 产品编号 >='100010'ALTER TABLE CP2 ADD CONSTRAINT CHK_CP2 CHECK（产品编号 >'100010'）;CREATE VIEW view_cp12 AS SELECT * FROM CP1 UNION ALL SELECT * 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、对视图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 FROM VIEW_CP12 WHERE 产品编号='100042'；7、将VIEW_CP_PRICE2000视图改为不加密。-解密视图ALTER VIEW VIEW_CP_PRICE2000 ASSELECT * 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 客户名称=Customername;占结果消息.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_ADDTYPE语句把属性特征包装起来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 varchar(11) NOT NULL ;删除用户定义类型drop type ssn定义两个用户定义类型方法二exec sp_addtype iq, 'float', 'null'exec 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','iq'exec sp_bindrule 'shoesize_range','shoesize'删除用户定义类型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 j<ibeginif 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购买日期，数量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广电公司洗衣机120020044)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”的客户购买情况。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='000002'IB结果心消皂睿户娘号产品编号客户名称 产品名称价格购买日期购买数里1 藏反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 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 .产品编号 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表中的对应字段一致，销售商编号与销售商表中对应字段一 致。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 rollback 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行INSE3T语句与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 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内嵌表值函数和名为FU2_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 select * 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