SQLServer实验五模板.docx
《SQLServer实验五模板.docx》由会员分享，可在线阅读，更多相关《SQLServer实验五模板.docx（18页珍藏版）》请在冰豆网上搜索。
SQLServer实验五模板
实验三、T-SQL基础、查询和视图（2学时）
实验目的：
（1）掌握T-SQL基础
（2）熟练掌握简单SQL查询命令的使用
（3）学习、掌握分组与汇总的函数的使用
（4）学习、掌握在SQL语句中使用函数的方法
（5）学习、掌握连接查询的方法
（6）学习、掌握子查询的方法
（7）创建、维护视图
实验内容：
简单查询
1.*的使用
查询orders表的所有内容
select*fromOrders;
2．orderby使用
查询所有订单的金额，并按照金额的降序排列（单个字段）
selectOsumfromOrders
orderbyOsumdesc;
查询出订单所有内容，按照cno和odate排序，cno降序，odate升序。
注意查看结果，当有多个排序字段时，首先按照第一个字段进行排序，当第一个字段相等时，按照第二个字段排序，且默认升序排序（asc）
select*fromOrders
orderbyCnodesc,Odateasc;
3．where子句
查询库存数量少于100的零件号和名称。
selectPno,PnamefromStore
wherePnum<100;
4．isnull使用
查询邮编为空的顾客的情况。
select*fromCustomer
whereCzipisnull;
5．where子句中使用函数
查询签订日期在2009年的所有订单的信息。
select*fromOrders
wheredatepart（year,Orders.Odate）='2009'
6．模式匹配、通配符、禁止重复distinct使用
查询顾客姓名中以“北京”开头的顾客姓名和电话。
selectdistinctCname,Ctel
fromCustomer
whereCnamelike'北京%';
7.算术运算符在SQL中的使用，定义别名
selectPnumas原始数量,Pnum*2as更新数量
fromStore
8.连字符的使用
selectCname+'位于'+Caddras'顾客地址'
fromCustomer
9.操作符的应用
1）BETWEEN的应用
查询零件数量在200到800之间的零件名称
selectPnamefromStore
wherePnumbetween200and800;
2）IN
在库存表中查询零件类别等于传动或者标准的所有零件
select*fromStore
wherePtypein（'传动','标准'）;
复杂查询
1、查询订单金额大于100的顾客的名称和电话；
selectCustomer.Cname,Customer.Ctel
fromCustomer,Orders
whereOrders.Cno=Customer.CnoandOrders.Osum>100
2、查询所有签订订单的顾客的名称和邮编；
selectdistinctCustomer.Cname,Customer.Czip
fromCustomer,Orders
whereOrders.Cno=Customer.Cno
3、统计每类零件的数量分别为多少；
selectstore.Pname,SUM（store.Pnum）零件数量
fromStore,Orders
wherestore.Pno=Orders.Pno
groupbystore.Pname,store.Pnum
4、统计每个顾客签订订单的次数；
selectCustomer.Cname,count（*）订单次数
fromCustomer,Orders
whereCustomer.Cno=Orders.Cno
groupbyCustomer.Cname,Customer.Cno
5、查询所有顾客签订订单的情况（包括没有签订订单的顾客）；
select*
fromCustomer,Orders
whereCustomer.Cno=Orders.CnoorCustomer.Cno!
=Orders.Cno
6、查询没有卖过一次的零件号（没有订单）；
selectdistinctstore.Pno
fromStore,Orders
wherestore.Pnonotin
（selectdistinctstore.PnofromStore,Orderswherestore.Pno=Orders.Pno）
7、查询每个顾客签订订单的金额总数；
selectOrders.Cno,SUM（Orders.Osum）金额总数
fromOrders,Customer
whereOrders.Cno=Customer.Cno
groupbyOrders.Cno
8、查询所有订单金额的平均值；
selectOrders.Cno,AVG（Orders.Osum）订单金额
fromOrders,Customer
whereCustomer.Cno=Orders.Cno
groupbyOrders.Cno
9、查询至少签订过两次订单的顾客信息。
selectcustomer.Cno,Cname,Ctel,Caddr,czip
fromCustomer,Orders
whereCustomer.Cno=Orders.Cno
groupbyCustomer.Cno,Cname,Ctel,Caddr,Czip
havingCOUNT（*）>=2
视图
1．使用SSMS创建视图向导
通过SSMS的CreateViewWizard创建新视图
1）在SSMS中，展开“数据库”后，在视图处单击鼠标右键。
2），双击菜单“创建视图”
3）选择表order
4）选择字段Ono,Cno,Pno,Onum。
5）输入条件语句WHEREOnum>1000，
selectOno,Cno,Pno,Onum
fromdbo.Orders
where（Onum>100）
6）输入视图名称v_order
8）在SSMS中的“数据库”OrderMag视图下查看视图v_order。
9）在查询窗口中输入并执行语句SELECT*FROMv_order
10）结果如何？
显示的字段是否为前面自己定义的字段？
答：
显示的是前面自己定义的字段。
11）删除视图v_order。
dropviewv_order
删除前如下图：
删除后如下图：
2.在查询中创建视图
1）建立一个视图，包括订单号、零件名称、顾客名称、订单金额等信息。
selectdbo.Orders.Cno,dbo.Store.Pname,dbo.Customer.Cname,dbo.Orders.Onum
fromdbo.Orders
innerjoin
dbo.Customerondbo.Orders.Cno=dbo.Customer.Cno
innerjoin
dbo.Storeondbo.Orders.Pno=dbo.Store.Pno
2）建立一个视图，查询订单金额大于10000元的大客户信息。
selectdbo.Customer.Cname,dbo.Customer.Ctel,dbo.Customer.Caddr,dbo.Customer.Czip,dbo.Orders.Osum
fromdbo.Orders
innerjoin
dbo.Customerondbo.Orders.Cno=dbo.Customer.Cno
and
dbo.Orders.Cno=dbo.Customer.Cno
innerjoin
dbo.Storeondbo.Orders.Pno=dbo.Store.Pno
where（dbo.Orders.Osum>100）
3）建立一个视图，查询每个顾客签订订单的总金额
selectdbo.Customer.Cname,SUM（dbo.Orders.Osum）as订单总金额
fromdbo.Orders
innerjoin
dbo.Customerondbo.Orders.Cno=dbo.Customer.Cno
anddbo.Orders.Cno=dbo.Customer.Cno
anddbo.Orders.Cno=dbo.Customer.Cno
innerjoin
dbo.Storeondbo.Orders.Pno=dbo.Store.Pno
groupbydbo.Customer.Cname
T-SQL基础
根据提供的数据库备份文件，还原数据库
编写一段程序代码，实现随机抽取设备的功能，
要求：
输入学生编号，执行该程序，能够显示该学生姓名、抽取的设备详细信息；
每个学生只能抽取一次。
说明：
如果现有数据库字段无法满足程序需求，可以自行添加所需字段。
/***
表说明：
Student：
学生基本信息表，fno为学生编号
createtableStudent
（
snovarchar（10）,
cnovarchar（10）,
fnovarchar（10）,
snamevarchar（10）,
ssexvarchar
（2）,
sageint,
classvarchar（20）
）
Computer:
电脑设备信息表，fno为设备编号
createtableComputer
（
fnovarchar（10）,
fnamevarchar（10）,
ftypevarchar（10）,
fcpuvarchar（10）,
fmemoryvarchar（10）,
fHardDiskvarchar（10）,
fVideovarchar（10）,
fDispvarchar（10）,
fOthervarchar（50）
）
HomeWork:
作业完成情况表，fno为学生编号，fhwno为作业编号
createtableHomeWork
（
fhwnovarchar（10）,
snovarchar（10）,
fnovarchar（10）,
fhowntypevarchar（50）,
fhownothervarchar（50）
）
TaskList：
作业信息表：
FworkNo为作业编号
createtableTaskList
（
FworkNovarchar（10）,
fnovarchar（10）,
snovarchar（10）,
Fworktypevarchar（50）,
Fworkothervarchar（50）
）
--创建返回表信息的自定义函数，通过传入学生编号，返回有同学姓名和给其电脑信息的表
createfunctionfnGetCOMInfo（@snovarchar（5））
returnstable
as
return
（
selects
升级会员 