SQLServer2008实验五模板

1、实验三、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降序

2、，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年的所有订单的信息。selec

3、t * 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'顾客地址

4、' 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 Order

5、s.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、统计每个顾客签订订单的次数；s

6、elect 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,Orderswhe

7、re 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 Custom

8、er.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）

9、选择表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删

10、除前如下图：删除后如下图：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 d

11、bo.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）建立一个视图，查

12、询每个顾客签订订单的总金额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.Cnam

13、eT-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:电脑设

14、备信息表，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 var

15、char(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 tableasret

16、urn( 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 FUse