最新SQLServer2008实验五模板.doc

Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateSQLServer2008实验五模板实验三、T-SQL基础、查询和视图（2学时）实验三、T-SQL基础、查询和视图（2学时）实验目的：（1）掌握T-SQL基础（2）熟练掌握简单SQL查询命令的使用（3）学习、掌握分组与汇总的函数的使用（4）学习、掌握在SQL语句中使用函数的方法（5）学习、掌握连接查询的方法（6）学习、掌握子查询的方法（7）创建、维护视图实验内容：简单查询1. * 的使用查询orders表的所有内容select * from Orders;2 order by 使用查询所有订单的金额，并按照金额的降序排列（单个字段）select Osum from Ordersorder by Osum desc;查询出订单所有内容，按照cno和odate排序，cno降序，odate升序。注意查看结果，当有多个排序字段时，首先按照第一个字段进行排序，当第一个字段相等时，按照第二个字段排序，且默认升序排序（asc）select * from Ordersorder by Cno desc, Odate asc;3where子句查询库存数量少于100的零件号和名称。select Pno,Pname from Storewhere Pnum < 100;4is null 使用查询邮编为空的顾客的情况。select * from Customer where Czip is null;5where子句中使用函数查询签订日期在2009年的所有订单的信息。select * from Orders where datepart(year ,Orders.Odate) = '2009'6模式匹配、通配符、禁止重复distinct使用查询顾客姓名中以“北京”开头的顾客姓名和电话。select distinct Cname,Ctel from Customer where Cname like'北京%'7.算术运算符在SQL中的使用，定义别名select Pnum as 原始数量,Pnum*2 as 更新数量 from Store8.连字符的使用select Cname+'位于'+Caddr as'顾客地址' from Customer 9.操作符的应用1）BETWEEN的应用查询零件数量在200到800之间的零件名称select Pname from Store where Pnum between 200 and 800;2）IN在库存表中查询零件类别等于传动或者标准的所有零件select * from Store where Ptype in ('传动','标准');复杂查询1、查询订单金额大于100的顾客的名称和电话；select Customer.Cname,Customer.Ctelfrom Customer,Orderswhere Orders.Cno=Customer.Cno and Orders.Osum>1002、查询所有签订订单的顾客的名称和邮编；select distinct Customer.Cname,Customer.Czipfrom Customer,Orderswhere Orders.Cno=Customer.Cno3、统计每类零件的数量分别为多少；select store.Pname,SUM(store.Pnum)零件数量from Store,Orderswhere store.Pno=Orders.Pnogroup by store.Pname,store.Pnum4、统计每个顾客签订订单的次数；select Customer.Cname,count(*) 订单次数from Customer,Orderswhere Customer.Cno=Orders.Cnogroup by Customer.Cname,Customer.Cno5、查询所有顾客签订订单的情况（包括没有签订订单的顾客）；select *from Customer,Orderswhere Customer.Cno=Orders.Cno or Customer.Cno!=Orders.Cno 6、查询没有卖过一次的零件号（没有订单）；select distinct store.Pnofrom Store,Orderswhere store.Pno not in(select distinct store.Pno from Store,Orders where store.Pno=Orders.Pno)7、查询每个顾客签订订单的金额总数；select Orders.Cno,SUM(Orders.Osum)金额总数from Orders,Customerwhere Orders.Cno=Customer.Cnogroup by Orders.Cno8、查询所有订单金额的平均值；select Orders.Cno,AVG(Orders.Osum)订单金额from Orders,Customerwhere Customer.Cno=Orders.Cnogroup by Orders.Cno9、查询至少签订过两次订单的顾客信息。select customer.Cno,Cname,Ctel,Caddr,czipfrom Customer,Orderswhere Customer.Cno=Orders.Cno group by Customer.Cno,Cname,Ctel,Caddr,Cziphaving COUNT(*)>=2视图1使用SSMS创建视图向导通过SSMS的Create View Wizard创建新视图1） 在SSMS中，展开“数据库”后，在视图处单击鼠标右键。2），双击菜单“创建视图”3）选择表order4）选择字段Ono, Cno, Pno, Onum。5）输入条件语句WHERE Onum >1000，select Ono, Cno, Pno, Onumfrom dbo.Orderswhere (Onum > 100)6）输入视图名称v_order8）在SSMS中的“数据库” àOrderMagà视图下查看视图v_order。9）在查询窗口中输入并执行语句SELECT * FROM v_order10）结果如何？显示的字段是否为前面自己定义的字段？答：显示的是前面自己定义的字段。11）删除视图v_order。drop view v_order删除前如下图：删除后如下图：2. 在查询中创建视图1) 建立一个视图，包括订单号、零件名称、顾客名称、订单金额等信息。select dbo.Orders.Cno, dbo.Store.Pname, dbo.Customer.Cname, dbo.Orders.Onumfrom dbo.Ordersinner joindbo.Customer on dbo.Orders.Cno = dbo.Customer.Cno inner join dbo.Store on dbo.Orders.Pno = dbo.Store.Pno2）建立一个视图，查询订单金额大于10000元的大客户信息。select dbo.Customer.Cname, dbo.Customer.Ctel, dbo.Customer.Caddr, dbo.Customer.Czip, dbo.Orders.Osumfrom dbo.Orders inner joindbo.Customer on dbo.Orders.Cno = dbo.Customer.Cno anddbo.Orders.Cno = dbo.Customer.Cno inner joindbo.Store on dbo.Orders.Pno = dbo.Store.Pnowhere (dbo.Orders.Osum > 100)3）建立一个视图，查询每个顾客签订订单的总金额select dbo.Customer.Cname, SUM(dbo.Orders.Osum) as 订单总金额from dbo.Orders inner joindbo.Customer on dbo.Orders.Cno = dbo.Customer.Cno and dbo.Orders.Cno = dbo.Customer.Cno and dbo.Orders.Cno = dbo.Customer.Cno inner joindbo.Store on dbo.Orders.Pno = dbo.Store.Pnogroup by dbo.Customer.CnameT-SQL基础 根据提供的数据库备份文件，还原数据库编写一段程序代码，实现随机抽取设备的功能，要求：输入学生编号，执行该程序，能够显示该学生姓名、抽取的设备详细信息； 每个学生只能抽取一次。说明：如果现有数据库字段无法满足程序需求，可以自行添加所需字段。/*表说明：Student：学生基本信息表，fno为学生编号create table Student (sno varchar(10),cno varchar(10),fno varchar(10),sname varchar(10),ssex varchar(2),sage int,class varchar(20)Computer:电脑设备信息表，fno为设备编号create table Computer(fno varchar(10),fname varchar(10),ftype varchar(10),fcpu varchar(10),fmemory varchar(10),fHardDisk varchar(10),fVideo varchar(10),fDisp varchar(10),fOther varchar(50)HomeWork:作业完成情况表，fno为学生编号，fhwno为作业编号create table HomeWork(fhwno varchar(10),sno varchar(10),fno varchar(10),fhowntype varchar(50),fhownother varchar(50)TaskList：作业信息表：FworkNo为作业编号create table TaskList(FworkNo varchar(10),fno varchar(10),sno varchar(10),Fworktype varchar(50),Fworkother varchar(50)-创建返回表信息的自定义函数，通过传入学生编号，返回有同学姓名和给其电脑信息的表create function fnGetCOMInfo(sno varchar(5) returns tableasreturn( select s.Fname,c.FNo,c.Ftype,c.FModule,FCPU,FMemory,FHardDisk,FVideo,FDisp,FOther from Computer c,Student s where c.FUserNo=s.FNo and c.FuserNo=sno)goupdate Computer set FUserNO=null declare cno varchar(5) -输出电脑编号 declare count int select count=count(*) from Computer where FUserNo='001' -是否给该同学分配过电脑，不进行数据库操作 if(count>0) begin set cno=0 end else -未给该同学分配电脑 begin select cno=FNo from Computer where FUserNo is null order by NEWID() -随机产生一台未分配的电脑编号 update Computer set FUserNo='001' where FNO=cno -修改表中分配情况 endgoSELECT * FROM dbo.fnGetCOMInfo('001')/*执行自定义函数 通过修改学生编号即可若以为该同学分配过电脑不做分配电脑的操作只查询其电脑信息*/select * from Computer实验结果 查询没有问题，之后最后一道题由于题目没有给出详细的要求，不知道表中的字段都应该有哪些，所以没有建完。其他的没有问题疑难小结：本实验的疑难之处在于有时会忘记某些语法，其他的都没有什么问题。-