SQLServer2008實驗五模板

1、實驗三、T-SQL基礎(chǔ)、查詢和視圖（2學(xué)時）實驗?zāi)康模海?）掌握T-SQL基礎(chǔ)（2）熟練掌握簡單SQL查詢命令的使用（3）學(xué)習(xí)、掌握分組與匯總的函數(shù)的使用（4）學(xué)習(xí)、掌握在SQL語句中使用函數(shù)的方法（5）學(xué)習(xí)、掌握連接查詢的方法（6）學(xué)習(xí)、掌握子查詢的方法（7）創(chuàng)建、維護視圖實驗內(nèi)容：簡單查詢1. * 的使用查詢orders表的所有內(nèi)容select * from Orders;2 order by 使用查詢所有訂單的金額，并按照金額的降序排列（單個字段）select Osum from Ordersorder by Osum desc;查詢出訂單所有內(nèi)容，按照cno和odate排序，cno降序

2、，odate升序。注意查看結(jié)果，當(dāng)有多個排序字段時，首先按照第一個字段進行排序，當(dāng)?shù)谝粋€字段相等時，按照第二個字段排序，且默認升序排序（asc）select * from Ordersorder by Cno desc, Odate asc;3where子句查詢庫存數(shù)量少于100的零件號和名稱。select Pno,Pname from Storewhere Pnum < 100;4is null 使用查詢郵編為空的顧客的情況。select * from Customer where Czip is null;5where子句中使用函數(shù)查詢簽訂日期在2009年的所有訂單的信息。selec

3、t * from Orders where datepart(year ,Orders.Odate) = '2009'6模式匹配、通配符、禁止重復(fù)distinct使用查詢顧客姓名中以“北京”開頭的顧客姓名和電話。select distinct Cname,Ctel from Customer where Cname like'北京%'7.算術(shù)運算符在SQL中的使用，定義別名select Pnum as 原始數(shù)量,Pnum*2 as 更新數(shù)量 from Store8.連字符的使用select Cname+'位于'+Caddr as'顧客地址

4、' from Customer 9.操作符的應(yīng)用1）BETWEEN的應(yīng)用查詢零件數(shù)量在200到800之間的零件名稱select Pname from Store where Pnum between 200 and 800;2）IN在庫存表中查詢零件類別等于傳動或者標準的所有零件select * from Store where Ptype in ('傳動','標準');復(fù)雜查詢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、統(tǒng)計每類零件的數(shù)量分別為多少；select store.Pname,SUM(store.Pnum)零件數(shù)量from Store,Orderswhere store.Pno=Orders.Pnogroup by store.Pname,store.Pnum4、統(tǒng)計每個顧客簽訂訂單的次數(shù)；s

6、elect Customer.Cname,count(*) 訂單次數(shù)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、查詢每個顧客簽訂訂單的金額總數(shù)；select Orders.Cno,SUM(Orders.Osum)金額總數(shù)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創(chuàng)建視圖向?qū)ㄟ^SSMS的Create View Wizard創(chuàng)建新視圖1） 在SSMS中，展開“數(shù)據(jù)庫”后，在視圖處單擊鼠標右鍵。2），雙擊菜單“創(chuàng)建視圖”3）

9、選擇表order4）選擇字段Ono, Cno, Pno, Onum。5）輸入條件語句WHERE Onum >1000，select Ono, Cno, Pno, Onumfrom dbo.Orderswhere (Onum > 100)6）輸入視圖名稱v_order8）在SSMS中的“數(shù)據(jù)庫” àOrderMagà視圖下查看視圖v_order。9）在查詢窗口中輸入并執(zhí)行語句SELECT * FROM v_order10）結(jié)果如何？顯示的字段是否為前面自己定義的字段？答：顯示的是前面自己定義的字段。11）刪除視圖v_order。drop view v_order刪

10、除前如下圖：刪除后如下圖：2. 在查詢中創(chuàng)建視圖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基礎(chǔ) 根據(jù)提供的數(shù)據(jù)庫備份文件，還原數(shù)據(jù)庫編寫一段程序代碼，實現(xiàn)隨機抽取設(shè)備的功能，要求：輸入學(xué)生編號，執(zhí)行該程序，能夠顯示該學(xué)生姓名、抽取的設(shè)備詳細信息； 每個學(xué)生只能抽取一次。說明：如果現(xiàn)有數(shù)據(jù)庫字段無法滿足程序需求，可以自行添加所需字段。/*表說明：Student：學(xué)生基本信息表，fno為學(xué)生編號create table Student (sno varchar(10),cno varchar(10),fno varchar(10),sname varchar(10),ssex varchar(2),sage int,class varchar(20)Computer:電腦設(shè)

14、備信息表，fno為設(shè)備編號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:作業(yè)完成情況表，fno為學(xué)生編號，fhwno為作業(yè)編號create table HomeWork(fhwno varchar(10),sno varchar(10),fno var

15、char(10),fhowntype varchar(50),fhownother varchar(50)TaskList：作業(yè)信息表：FworkNo為作業(yè)編號create table TaskList(FworkNo varchar(10),fno varchar(10),sno varchar(10),Fworktype varchar(50),Fworkother varchar(50)-創(chuàng)建返回表信息的自定義函數(shù)，通過傳入學(xué)生編號，返回有同學(xué)姓名和給其電腦信息的表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' -是否給該同學(xué)分配過電腦，不進行數(shù)據(jù)庫操作 if(count>0) begin set cno=0 end else -未給該同學(xué)分配電腦 begin select cno=FNo from Computer where FUserNo is null order by NEWID() -隨機產(chǎn)生一臺未分配的電腦編號 update Computer set FUse