實驗報告1：數(shù)據(jù)庫查詢

1、第1個實驗單表查詢 第2章實驗一(1)查詢員工的姓名、職務和薪水。select employeeName,headShip,salaryfrom Employee(2) 查詢名字中含有“有限”的客戶名稱和所在地。select customerName,address from Customerwhere customerName like'%有限%'(3)查詢出姓“張”并且姓名的最后一個字為“梅”的員工。select *from Employeewhere employeeName like'%張_梅%'(4)查詢住址中含有“上海”或“南昌”的女員工，并顯示其姓

2、名、所屬部門、職稱、住址、出生日期和性別，其中如果出生日期為空，顯示“不詳”，否則按格式“yyyy-mm-dd”顯示，性別用“男”和“女”顯示。select employeeName,headShip,address,department, sex=CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '不確定' END, isnull(convert( char(10),birthday,120),'不詳')出生日期from Employeewhere

3、 sex='F' and (address like '%上海%'or address like '%南昌%' ) (5)查詢出職務為“職員”或職務為“科長”的女員工的信息。select *from Employeewhere sex='F' and (headShip like '職員'or headShip like '科長' )(6)選取編號不在C20050001C20050004之間的客戶編號、客戶名稱、客戶地址。select customerNo,customerName, address

4、 from Customer WherecustomerNo not in('C20050001','C20050002','C20050003','C20050004')(7) 在表OrderMaster中挑出銷售金額大于等于5000元的訂單。select * from OrderMasterwhere orderSum>=5000(8)選取訂單金額最高的前10%的訂單數(shù)據(jù)。select top 10 * from OrderMasterorder by orderSum DESC(9)計算出一共銷售了幾種商品。selec

5、t count(distinct productNo )from OrderDetail(10)計算OrderDetail表中每種商品的銷售數(shù)量、平均銷售單價和總銷售金額，并且依據(jù)銷售金額由大到小排序輸出。select productNo 產品編號,sum(quantity) 銷售數(shù)量,sum(price*quantity)總銷售金額,sum(price*quantity)/sum(quantity) 平均銷售單價from OrderDetailgroup by productNoorder by sum(price*quantity) desc(11)按客戶編號統(tǒng)計每個客戶2008年2月的訂

6、單總金額。select customerNo 客戶編號, sum(orderSum)訂單金額from OrderMasterwhere year(orderDate)=2008 and month(orderDate)=2group by customerNo (12)統(tǒng)計至少銷售了10件以上的商品編號和銷售數(shù)量。select productNo 產品編號,sum(quantity) 銷售數(shù)量from OrderDetailgroup by productNohaving sum(quantity)>=10(13)統(tǒng)計在業(yè)務科工作且在1973年或1967年出生的員工人數(shù)和平均工資。sel

7、ect count(*)員工人數(shù),sum(salary)/count(*) 平均工資from Employeewhere department='財務科'and (year(birthday)=1973 or year(birthday)>=1967)(14) 實驗問題： 給出SQL語句實現(xiàn)分組聚集操作的執(zhí)行過程。在分組聚合中，先對查詢結果進行分組，然后聚合函數(shù)分別作用于每個組，查詢結果按組聚合輸出。也就是先執(zhí)行from 語句；再執(zhí)行where語句；其次執(zhí)行group by語句；之后使用聚合函數(shù)進行計算；最后使用having語句進行篩選分組 WHERE和HAVING子句都

8、是用于指定查詢條件的，請給出你對這兩個子句的理解，用實例說明。Where子句和having子句的作用對象不同，where子句作用于表和視圖，having子句作用于組group，Having子句必須和group by子句配合使用；Where在分組和聚集計算之前選取輸入行，因此，它控制那些行進行聚集計算，而having在分組和聚集之后選取分組的行。因此where子句不包含聚集函數(shù)，相反，having子句總是包含聚集函數(shù)的。實例：Where子句，查詢住址在南昌的員工信息select *from Employee Where address like '%南昌%'Having子句，查詢

9、業(yè)績最好的業(yè)務員的名字 select a.employeeName from Employee a,( select top 1 sum(orderSum) 金額,salerNo from OrderMaster group by salerNo ) b where a.employeeNo=b.salerNo 在分組聚集操作中，為什么在查詢列中，除了集聚函數(shù)運算，其它表達式必須包含在GROUP BY子句中。如果查詢列除聚集函數(shù)外的表達式不包含在group by子句中，則聚合沒有意義。 分析條件BETWEEN . AND、AND、OR等關鍵字的使用方法。BETWEEN . AND是范圍查詢，可

10、用于查詢屬性值在某一范圍內的元組，between后是屬性的下限值，and后是屬性的上限值；AND是邏輯查詢，實現(xiàn)邏輯與運算；OR是邏輯查詢，實現(xiàn)邏輯或運算。 請總結SQL語句中的單表查詢語句的使用方法。SQL語句中的簡單查詢語句有：1. 查詢所有列Select * from 表名2. 查詢指定列Select 列名 from 表名3. 消除重復元組Select distinct 列名 from 表名4. 給屬性列取別名Select 列名 as 別名From 表名5. 字符匹配查詢Like <匹配字符串> escape 換碼字符6. 排序運算Order by <表達式1>

11、asc|desc ,<表達式2> asc|desc第2個實驗多表查詢 第2章實驗二(1)找出同一天進入公司服務的員工。select distinct a.employeeNo,a.hireDatefrom Employee a,Employee bwhere a.hireDate=b.hireDate and a.employeeNo!=b.employeeNo (2)查找與“陳詩杰”在同一個單位工作的員工姓名、性別、部門和職務。select employeeName, sex, department,headShipfrom Employee where department i

12、n (select department from Employee where employeeName='陳詩杰') and employeeNo not in (select employeeNo from Employee where employeeName='陳詩杰') (3)在Employee表中查詢薪水超過員工平均薪水的員工信息。select *from Employee where salary>=(select sum(salary)/count(*) from Employee ) (4) 查找有銷售記錄的客戶編號、名稱和訂單總額。s

13、elect a.customerNo,customerName ,orderSum from Customer a,OrderMaster bwhere a.customerNo=b.customerNo and a.customerNo in(select customerNo from OrderMaster)(5)查詢沒有訂購商品的客戶編號和客戶名稱。select customerNo,customerName from Customer where customerNo not in(select customerNo from OrderMaster)(6)使用子查詢查找32M DR

14、AM的銷售情況，要求顯示相應的銷售員的姓名、性別，銷售日期、銷售數(shù)量和金額，其中性別用“男”、“女”表示。select e.employeeName, sex=CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '不確定' END, f.orderDate,f.orderSum ,d.quantityfrom Employee e,OrderMaster f,OrderDetail dwhere f.salerNo=e.employeeNo and d.orderNo

15、=f.orderNo and ductNo in(select productNo from Product where productName='32M DRAM' )(7)查詢OrderMaster表中訂單金額最高的訂單號及訂單金額。select orderSum,orderNo from OrderMaster where orderSum=(select max(orderSum)from OrderMaster )(8)在訂單主表中查詢訂單金額大于“E2005002業(yè)務員在2008-1-9這天所接的任一張訂單的金額”的所有訂單信息。select *from

16、OrderMasterwhere orderSum >(select min(orderSum)from OrderMaster where orderDate='20080109' and salerNo='E2005002')(9)查詢單價高于400元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價。select a.orderNo,ductName,a.quantity,a.price from OrderDetail a,Product bwhere ductNo=ductNo and a.price>400(10)分別

17、使用左外連接、右外連接、完整外部連接查詢單價高于400元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價，并分析比較檢索的結果。左外連接select a.orderNo,ductName,a.quantity,a.price from OrderDetail a left outer join Product b on ductNo=ductNo where a.price>400右外連接select a.orderNo,ductName,a.quantity,a.price from OrderDetail a right outer join Prod

18、uct b on ductNo=ductNo where a.price>400完整外部連接select a.orderNo,ductName,a.quantity,a.price from OrderDetail a full outer join Product b on ductNo=ductNo where a.price>400select a.orderNo,ductName,a.quantity,a.price from OrderDetail a full outer join Product b on a

19、.productNo=ductNo where a.price>400(11)使用左外連接查找每個客戶的客戶編號、名稱、訂貨日期、訂單金額，其中訂貨日期不要顯示時間，日期格式為“yyyy-mm-dd”，按客戶編號排序，同一客戶再按訂單金額降序排序輸出。select a.customerNo ,a.customerName ,isnull(convert( char(10),b.orderDate,120),'不詳'),b.orderSum from Customer a right outer join OrderMaster b on a.customerNo

20、=b.customerNo order by a.customerNo , b.orderSum desc(12)查找每個員工的銷售記錄，要求顯示銷售員的編號、姓名、性別、商品名稱、數(shù)量、單價、金額和銷售日期，其中性別使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式顯示。Select a.employeeNo ,a.employeeName ,a.sex,ductName,c.quantity,c.price,b.orderSum,isnull(convert( char(10),b.orderDate,120),'不詳')日期from Employee

21、a, OrderMaster b,OrderDetail c,Product dwhere a.employeeNo=b.salerNo and b.orderNo=c.orderNo and ductNo=ductNo(13) 查找32M DRAM的銷售情況，要求顯示相應的銷售員的姓名、性別，銷售日期、銷售數(shù)量和金額，其中性別用“男”、“女”表示。select e.employeeName, sex=CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '

22、不確定' END, f.orderDate,f.orderSum ,d.quantityfrom Employee e,OrderMaster f,OrderDetail dwhere f.salerNo=e.employeeNo and d.orderNo=f.orderNo and ductNo in(select productNo from Product where productName='32M DRAM' )(14)找出公司男業(yè)務員所接且訂單金額超過2000元的訂單號及訂單金額。 select orderNo, orderSum from Or

23、derMaster where salerNo in(select employeeNo from Employee where sex='M' )and orderSum>2000(15)求出每位客戶的總訂購金額，顯示出客戶號及總訂購金額，并按總訂購金額降序排列。Select customerNo, sum(orderSum )總訂購金額from OrderMaster group by customerNoorder by sum(orderSum ) desc(16)查詢每種商品的總銷售數(shù)量及總銷售金額，要求顯示出商品編號、商品名稱、總數(shù)量及總金額，并按商品號從小到

24、大排列。select *from (select productNo,productName from Product)a,(select productNo ,sum(quantity)數(shù)量, sum(quantity*price)金額from OrderDetail group by productNo)bwhere ductNo=ductNoorder by ductNo(17) 實驗問題： 連接操作類型有哪些？分析外連接在現(xiàn)實應用中的意義。連接操作類型包括內連接等值與非等值連接、自表連接、外連接（左外連接，右外連接，全外連接）。外連接的意義：對于存在多對多

25、關系的兩個關系，可以使關聯(lián)不上的部分信息也能顯示出來。 查詢表可以用在什么地方？使用查詢表要注意哪些地方？查詢表用在from 子句之后，必須為查詢表設置一個變量名，用該名表示查詢表。查詢表可以作為被查詢的表， 分析SQL語句中的IN和OR關鍵字有何異同點？它們可以互換嗎？給出實例說明。In可用于集合查詢，也可用于嵌套子查詢中；Or則為邏輯查詢，邏輯或；當in用于集合查詢時，in和or之間可以相互轉換；當in用于嵌套子查詢時，則不可以相互轉換。舉例：選取編號在C20050001C20050004之間的客戶編號、客戶名稱、客戶地址。使用in查詢：select customerNo,customer

26、Name, address from CustomerWhere customerNo in('C20050001','C20050002','C20050003','C20050004')使用or查詢：select customerNo,customerName, address from Customer Where customerNo='C20050001'or customerNo='C20050002'or customerNo='C20050003'or custome

27、rNo='C20050004'當in用于子查詢時，in和or不可以互換舉例：查詢單筆訂單金額查過2000的客戶名select customerName from Customer Where customerNo in(select customerNo from OrderMaster where orderSum >2000) 分析哪幾種情況需要使用自表連接。當同一表中的某一元組變量與另外的元組變量相關時，可以使用自表連接，從而得到一些特殊的數(shù)據(jù)。自表連接一般用來判斷并篩選單一表中的一些數(shù)據(jù)。 第3個實驗復雜查詢 第2章實驗三(1) 在訂單明細表中查詢訂單金額最高的訂

28、單。 select *from OrderDetailwhere quantity*price=(select max(quantity*price) from OrderDetail)(2) 找出至少被訂購3次的商品編號、訂單編號、訂貨數(shù)量和訂貨金額，并按訂貨數(shù)量的降序排序輸出。select ductNo,a.orderNo,(quantity* quantity)訂貨金額from OrderDetail awhere ductNo in(select productNo from OrderDetail group by productNo having count(

29、productNo)>=3 )(3) 查找銷售總額少于5000元的銷售員編號、姓名和銷售額。select a.employeeNo ,a.employeeName ,sum(b.orderSum )業(yè)績額from Employee a, OrderMaster bwhere a.employeeNo=b.salerNo group by a.employeeNo ,a.employeeName having sum(b.orderSum )>=5000 、 (4) 找出目前業(yè)績未超過5000元的員工，并按銷售業(yè)績的降序排序輸出。select a.employeeNo ,a.empl

30、oyeeName ,sum(b.orderSum )業(yè)績額from Employee a, OrderMaster bwhere a.employeeNo=b.salerNo group by a.employeeNo ,a.employeeName having sum(b.orderSum )<5000 order by sum(b.orderSum ) desc(5) 查詢訂單中所訂購的商品數(shù)量沒有超過10個的客戶編號和客戶名稱。. 在某客戶所有訂單中，只要有一次訂單中訂購的商品總數(shù)量超過10時，就認為不符合題目要求select c.customerNo,c.customerNam

31、e from Customer c,OrderMaster awhere a.customerNo=c.customerNo and a.orderNo not in (select orderNo from OrderDetail group by orderNo having sum(quantity)>10).在某客戶所有訂單中，當全部訂單中每次訂購的商品總數(shù)量都超過10 時，就認為不符合題目要求select c.customerNo,c.customerName from Customer c,OrderMaster awhere a.customerNo=c.customerN

32、o and a.orderNo in (select orderNo from OrderDetail group by orderNo having sum(quantity)<=10).在某客戶所有訂單中，當全部訂單累計的總訂購商品數(shù)量超過10 時，就認為不符合題目要求select c.customerNo,c.customerName from Customer cwhere c.customerNo in (select b.customerNo from OrderDetail a, OrderMaster b where a.orderNo=b.orderNo group b

33、y b.orderNo,b.customerNo having sum(quantity)<=10)(6) 查找訂貨金額最大的客戶名稱和總貨款。select a.customerName, b.總金額from Customer a, ( select top 1 sum(orderSum) 總金額, customerNo from OrderMaster group by customerNo ) bwhere a.customerNo=b.customerNo (7) 查找至少訂購了3種商品的客戶編號、客戶名稱、商品編號、商品名稱、數(shù)量和金額。select ductNo,d.

34、productName ,e.customerNo, e.customerName ,f.price , f.quantity from Product d, Customer e, OrderDetail f, OrderMaster g,(select customerNo from OrderMaster a, OrderDetail b where a.orderNo=b.orderNo group by customerNo having count(productNo)>=3) ductNo=ductNo andf.orderNo=g.orde

35、rNo andg. customerNo=e. customerNo ande.customerNo=c.customerNo(8) 找出目前銷售業(yè)績超過4000元的業(yè)務員編號及銷售業(yè)績，并按銷售業(yè)績從大到小排序。select salerNo , sum(orderSum )銷售業(yè)績from OrderMastergroup by salerNo having sum(orderSum )>4000(9) 求每位客戶訂購的每種商品的總數(shù)量及平均單價，并按客戶號、商品號從小到大排列。select a.customerNo ,ductNo,sum(quantity)總數(shù)量,sum(

36、price)/sum(quantity)平均單價from OrderMaster a, OrderDetail bwhere a.orderNo=b.orderNo group by a.customerNo,ductNo order by a.customerNo ,ductNo(10) 查詢業(yè)績最好的的業(yè)務員號、業(yè)務員名及其總銷售金額。select employeeNo , employeeName ,銷售業(yè)績from Employee a,(select top 1 sum(orderSum )銷售業(yè)績,salerNo from OrderMaster group b

37、y salerNo )bwhere a.employeeNo =b.salerNo(11) 查詢訂購的商品至少包含了訂單“200803010001”中所訂購商品的訂單。select orderNofrom OrderDetail xwhere not exists( select * from OrderDetail y where orderNo='200803010001' and not exists ( select * from OrderDetail where productNo=ductNo and orderNo=x.orderNo )(12)查詢總

38、訂購金額超過“C20070002”客戶的總訂購金額的客戶號、客戶名及其住址。select a.customerNo , customerName, address from Customer a,( select sum(orderSum )訂購金額, customerNo from OrderMaster group by customerNo )bwhere a.customerNo=b. customerNo and 訂購金額>( select sum(orderSum ) from OrderMaster group by customerNo having customerNo

39、='C20070002')(13)查詢總銷售金額最高的銷售員編號、訂單編號、訂單日期和訂單金額。select orderNo ,orderSum,b.salerNo , orderDatefrom OrderMaster a,(select top 1 sum(orderSum )銷售業(yè)績,salerNo from OrderMaster group by salerNo )bwhere a.salerNo=b.salerNo (14) 用存在量詞查找沒有訂貨記錄的客戶名稱。select customerName from Customer xwhere not exists

40、( select * from OrderMaster a where x.customerNo=a.customerNo)(15)查詢既訂購了“52倍速光驅”商品，又訂購了“17寸顯示器”商品的客戶編號、訂單編號和訂單金額。select customerNo,orderNo,orderSumfrom OrderMaster where customerNo in( select a.customerNo from OrderMaster a, OrderMaster b,OrderDetail c, OrderDetail d where a.customerNo=b.customerNo and c.orderNo=a.orderNo and d.orderNo=b.orderNo and ductNo=(select productNo from Product where productName='52倍速光驅' ) and ductNo=(select productNo from Product where productName='17寸顯示器