《SQLServer实验五模板》由会员分享,可在线阅读,更多相关《SQLServer实验五模板(19页珍藏版)》请在金锄头文库上搜索。
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 1002、查询所有签订订单的顾客的名称和邮编;select distinct Customer.Cname,Customer.Czipfrom Customer,Orderswhere Orders.Cno=Customer.C
3、no3、统计每类零件的数量分别为多少;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、查询所有顾客签订订单的情况(包括没有签订订单的顾客);sele
4、ct *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
5、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
6、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视图下
7、查看视图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
8、.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.Or
9、ders.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
10、 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 (sn
11、o 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)
12、,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) r