数据库实验报告全

实验一实验目的1.熟悉SQLServerManagementStudio（SSMS）的工作环境2.掌握使用和命令建立数据库的方法3.熟练掌握使用SSMS和T-SQL语句创建、修改和删除表。4.熟练掌握使用SSMS和T-SQL语句插入、修改和删除表数据。实验内容1.采用SQLServerManagementStudio、T-SQL语句两种方式创建产品销售数据库，要求：1)使用SSMS创建数据库CPXS_bak，数据文件初始大小为5MB，最大大小50MB，按5MB增长；日志文件初始为2MB，最大可增长到10MB，按2MB增长；其余参数取默认值。2)用T-SQL语句创建数据库CPXS，数据文件的增长方式改为增长方式按10%比例增长,其余与CPXS_bak。3)用T-SQL语句删除数据库CPXS_bak。2.CPXS数据库包含如下三个表：1)产品（产品编号，产品名称，价格，库存量）2)客户（客户编号，客户名称，地区，负责人，电话）3)销售（产品编号，客户编号，销售日期，数量，销售额）三个表结构如资料中图3.1～图3.3所示，请写出创建以上三个表的T-SQL语句并在查询分析器中运行。3.在SSMS中输入如资料中图3.4～图3.6的商品表、客户表和销售表的样本数据。4.用T-SQL语句向商品表插入如下记录：200001柜式空调3000200200002微波炉1000100200003抽油烟机1200505.用T-SQL语句向CP表中增加“产品简列”列，varchar(50)，允许为NULL。6.将CP表中每种商品的价格打8折。7.将CP表中价格打9折后小于1500的商品删除。二.实验步骤与结果（说明：要写出相关步骤和SQL语句，实验结果可以是运行画面的抓屏，抓屏图片要尽可能的小。）1.1)使用SSMS创建数据库CPXS_bak效果图为2)用T-SQL语句创建数据库CPXS：CREATEDATABASECPXSONPRIMARY(NAME='CPXS_DATA',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\CPXS_DATA.MDF',SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=10%)LOGON(NAME='CPXS_LOG',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\CPXS_LOG.LDF',SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=2MB)3)DROPDATABASECPXS_bak刷新数据库会看到CPXS_bak不存在2.1)USECPXSGOCREATETABLE产品(产品编号char(6)PRIMARYKEY,产品名称char(30)NOTNULL,价格float(8),库存量int,)2)USECPXSGOCREATETABLE客户(客户编号char(6)PRIMARYKEY,客户名称char(30)NOTNULL,地区char(10),负责人char(8),电话char(12))3）USECPXSGOCREATETABLE销售(产品编号char(6),客户编号char(6),销售日期datetime,数量intNOTNULL,销售额float(8)NOTNULL,CONSTRAINTpk_jsPRIMARYKEY(产品编号,客户编号,销售日期))/*pk_js为约束名*/执行完上面的操作就能看见表已经添加进数据库中,如图所示：3.打开表：在其中添加数据：4．1)USECPXSINSERTINTO产品(产品编号,产品名称,价格,库存量)VALUES('200001','柜式空调','3000','200')2)USECPXSINSERTINTO产品(产品编号,产品名称,价格,库存量)VALUES('200002','微波炉','1000','100')3)USECPXSINSERTINTO产品(产品编号,产品名称,价格,库存量)VALUES('200003','抽油烟机','1200','50')可以看见图中的产品表增加了如下内容：5．USECPXSALTERTABLE产品Add产品简列varchar(50)6.USECPXSUPDATE产品set价格=价格*0.87.USECPXSDELETE产品WHERE价格*0.91500三.实验中的问题及心得（说明：此处应写明此次实验遇到的问题有哪些，如何解决的，不能够空。）本次实验的问题都很简单，没有遇到什么困难，基本上书上都有例子，对着例子都能顺利完成，不过我需要做的是记者所写的sql语句还有就是要继续努力。实验二一实验目的1.牢记SELECT语句的基本语法格式；2.掌握利用SQL语句进行多表内/外连接查询和嵌套查询的方法；3.掌握GROUPBY子句、HAVING子句、ORDERBY子句的用法；4.掌握（NOT）IN、（NOT）EXISTS等谓词的用法；5.掌握SUM、AVG、COUNT、MAX、MIN等集合函数的用法；二实验内容及要求对CPXS数据库，完成如下查询任务：根据上述情况完成：1.查询地区在“郑州”的客户编号和客户名称，结果中各列的标题分别指定为：CustomerID和CustomerName。2.查询价格在1000-2000之间的产品信息。3.查询产品名称含有“变频”字样的产品情况。4.按商品编号统计各种产品在国庆黄金周期间的销售单数（记录数）、总销量和销售总额，并对统计结果按照销售总额降序排序。5.列出购买2种以上产品的客户编号。6.（结合上题）列出购买2种以上产品的客户编号、客户名称、地区、负责人和电话。7.统计每个客户在国庆黄金周期间的购物情况（客户编号、名称、消费总金额和最近消费日期），即使某些客户没有消费记录，也要包括其信息。8.查询在国庆黄金周期间没有任何销售记录的产品编号、名称及库存量。三实验过程及结果1.2.3．4．USECPXSSELECTa.产品编号,count(a.销售日期)AS销售单数,sum(a.数量)AS总销量,sum(a.数量*b.价格)AS销售总额FROM销售aINNERJOIN产品bONa.产品编号=b.产品编号GROUPBYa.产品编号ORDERBY销售总额desc5．6.7.USECPXSSELECTc.客户编号,客户名称,sum(a.数量*b.价格)AS消费总额,max(a.销售日期)AS最近消费日期FROM客户cLEFTJOIN销售aONc.客户编号=a.客户编号JOIN产品bONa.产品编号=b.产品编号GROUPBY客户名称,c.客户编号8.四实验中的问题及心得这次试验较上次的困难首先是我们上课的时候没有讲所做的内容，这就需要我们在下面好好地预习，时间仓促，在放假之前也做了一部分，其余的就放假后完成了，其中也出此不少问题大都是马虎的问题。实验三一实验目的6.熟练掌握变量的定义、赋值与使用。7.熟练掌握常用基本运算符。8.熟练掌握流程控制语句，尤其是条件语句和循环语句。9.熟悉并掌握常用的系统函数。10.理解什么是标量函数、内嵌表值函数及多语句表值函数。11.熟练掌握自定义函数的定义和调用方法。二实验内容及要求对CPXS数据库，完成如下任务：9.创建一名为CustomerName的局部变量，并在SELECT语句中使用该变量查找“客户D”购买产品的情况（包括产品名称、单价）。10.用T-SQL语言编程输出3～300之间能被7整除的数。11.查询CP表中各种产品的产品编号、产品名称和价格，对其价格按以下规则进行转换：若价格小于1000，替换为“廉价产品”；若价格在1000-2000之间，替换为“一般产品”；若价格大于2000小于5000，替换为“昂贵产品”；若价格大于5000，替换为“奢侈品”；列标题更改为“评价”。12.使用系统函数，计算今天距“2012-1-1”还剩多少天。13.根据产品编号，查询该产品的名称；（函数名为udf_GetCPName）14.根据客户编号，统计其在某个时期内所购买的商品名称、数量及金额（函数名为udf_GetCPListByCID）；对函数udf_GetCPListByCID，查询“客户A”在“2011-1-1”到“2011-9-30”期间交易的销售日期、产品名称、销售数量和销售金额，并按销售日期升序排序。三实验过程及结果1.useCPXSdeclare@CustomerNamechar(10)set@CustomerName='客户D'SELECT产品.产品名称,产品.价格FROM产品,销售WHERE产品.产品编号=销售.产品编号and销售.客户编号=(SELECT客户编号FROM客户WHERE客户名称=@CustomerName)2.--用T-SQL语言编程输出3～300之间能被7整除的数。--DECLARE@iint,@aintSET@i=3WHILE@i=300BEGINSET@a=@i%7IF@a=0PRINT'3～300之间能被7整除的数为：'+cast(@iasvarchar)SET@i=@i+1END3．USECPXSSELECT产品编号,产品名称,价格,CASEWHEN价格1000then'廉价产品'WHEN价格=1000and价格=2000then'一般产品'WHEN价格2000and价格5000then'昂贵产品'WHEN价格=5000then'奢侈产品'ENDAS评价FROM产品4．SELECTDATEDIFF(DAY,GETDATE(),'2012-01-01')5.USECPXSGOCREATEFUNCTIONudf_GetCPName(@cpNoCHAR(7))RETURNSCHAR(20)ASBEGINDECLARE@cpNamechar(20)SELECT@cpName=(SELECT产品名称FROM产品WHERE产品编号=@cpNo)RETURN@cpNameENDusecpxsdeclare@cpchar(7)set@cp='S00001'selectdbo.udf_GetCPName(@cp)as'产品名称'6.USECPXSGOCREATEFUNCTIONudf_GetCPListByCID(@noCHAR(7),@begintimedatetime,@endtimedatetime)RETURNS@salestable(xs_datachar(10),xs_namechar(15),xs_sumint,xs_moneyint)ASBEGININSERT@salesSELECTa.销售日期,b.产品名称,a.数量AS销售数量,a.数量*b.价格AS销售金额FROM销售a,产品b,客户cWHEREa.产品编号=b.产品编号ANDa.客户编号=c.客户编号ANDc.客户编号=@noANDa.销售日期between@begintimeand@endtimeorderby销售日期ascRETURNENDdeclare@noochar(7),@time1datetime,@time2datetimeset@time1='2011-01-01'set@time2='2011-10-11'set@noo=(select客户编号from客户where客户名称='客户A')select*fromudf_GetCPListByCID(@noo,@time1,@time2)四实验中的问题及心得实验四一实验目的12.熟练掌握使用企业管理器和T-SQL语句创建、查询、更新、修改和删除视图。13.进一步掌握视图与基本表的联系与区别。14.理解索引的概念和作用。15.熟练掌握索引的创建与删除。二实验内容及要求对CPXS数据库，完成如下任务：15.创建视图（1）启动SSMS，在产品销售数据库CPXS中创建价格小于2000的产品视图VIEW_CP_PRICE2000，要求加密并保证对该视图的更新都要符合价格小于2000这个条件，写出创建过程和