數據庫原理

第四講SQLServer與數據查詢創建表創建emp表:createtableemp(empnointnotnullprimarykey,enamenvarchar(10)notnull,jobnvarchar(9)notnull,mgrintnotnull,hiredatedatenotnull,salmoneynotnull,commmoney,deptnointnotnull)建完表后，可執行execsp_helpemp查看執行情況。插入數據插入多行數據：insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7521,'ward','salesman',7698,'1981-2-22',1250,500,30),(7566,'jones','manager',7839,'1981-4-2',2975,null,20),(7654,'martin','salesman',7698,'1981-9-28',1250,1400,30)建表、插入數據創建表salgrade如下：createtablesalgrade(gradeintprimarykey,losalmoney,hisalmoney)插入多行數據insertintosalgrade(grade,losal,hisal)values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999)建表、插入數據創建表deptcreatetabledept(deptnointprimarykey,dnamenvarchar(14),locnvarchar(13))插入多行數據insertintodept(deptno,dname,loc)values(10,'accounting','newyork'),(20,'research','dallas'),(30,'sales','chicago'),(40,'operations','boston')Emp表Dept表和Salgrade表數據查詢1、查詢工資高于500，或是崗位為manager的雇員同時還滿足姓名首字母為j。

select*fromempwhere(sal>500orjob='manager')andenamelike'j%‘Orderby子句2、按工資由低到高的順序顯示雇員信息

select*fromemporderbysal3、部門號按升序而工資按降序排列雇員信息

select*fromemporderbydeptno,saldesc4、使用列的別名“年薪”降序排序

selectename,job,sal*12as'年薪'fromemporderby'年薪'desc查詢語句5、查詢員工的最高、最低工資和平均工資

selectMAX(sal)as最高工資,MIN(sal)as最低工資,AVG(sal)as平均工資fromemp

6、計算有多少員工

selectCOUNT(*)fromemp查詢語句7、顯示工資最高的員工名字與工作崗位方法一：selectename,jobfromempwheresal=(selectMAX(sal)fromemp)

方法二：可通過定義一個變量，來取出最大工資值declare@maxsalmoneyselect@maxsal=MAX(sal)fromempprint'maxsal='+convert(nvarchar(12),@maxsal)selectename,jobfromempwheresal=@maxsalGroupby和Having子句groupby用于對查詢的結果分組統計Having子句用于限制分組顯示結果9、顯示每個部門的平均工資和最高工資selectdeptno,AVG(sal)as'部門平均工資',MAX(sal)as'部門最高工資'fromempgroupbydeptnoGroupby和Having子句10、查詢每個部門的每種崗位的平均工資和最高工資

selectdeptno,job,MAX(sal)‘部門各崗位平均工資’,MAX(sal)‘部門各崗位最高工資’

fromempgroupbydeptno,joborderbydeptnoGroupby和Having子句11、查詢平均工資低于2000的部門號和它的平均工資selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptnohavingAVG(sal)<2000對數據分組的總結分組函數只能出現在選擇列表，having和orderby子句中。如果在select語句中同時有groupby，having和orderby，那么它們的出現順序是groupby，having和orderby。在選擇列中如果有列、表達式和分組函數，那么這些列和表達式必須有一個在groupby子句中，否則就會出錯。多表查詢12、顯示雇員名、雇員工資和所在部門的名稱

selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptno13、顯示部門號為10的員工名、工資和部門名

selectename,sal,dnamefromemp,deptwhereemp.deptno=10andemp.deptno=dept.deptno14、顯示各員工的姓名、工資、工資級別、所在部門名稱

方法一：selectename,sal,grade,dnamefromemp,dept,salgradewhereemp.deptno=dept.deptnoandsalbetweenlosalandhisal

方法二：selectename,dname,sal,gradefromempjoindeptonemp.deptno=dept.deptnojoinsalgradeonemp.salbetweensalgrade.losalandsalgrade.hisal自連接15、查詢每個員工的姓名和他的上級的姓名

selecta.ename'員工姓名',b.ename'員工上級姓名'fromempaleftjoinempbona.mgr=b.empno左（右）外連接，即將左（右）邊表中不能與右（左）邊表匹配的元組列出來。嵌套查詢16、顯示與smith同一部門的員工姓名

方法一:

selectenamefromempwheredeptno=(selectdeptnofromempwhereename='smith')

方法二:declare@dept_nointselect@dept_no=deptnofromempwhereename='smith'selectenamefromempwheredeptno=@dept_no嵌套查詢17、查詢和部門10的工作相同的員工的名字、崗位、工資和部門號

selectename,job,sal,deptnofromempwherejobin(selectdistinctjobfromempwheredeptno=10)18、查詢比部門30所有員工的工資都高的員工姓名、工資和部門號

方法一：selectename,sal,deptnofromempwheresal>=(selectMAX(sal)fromempwheredeptno=30)

方法二：

selectename,sal,deptnofromempwheresal>=all(selectsalfromempwheredeptno=30)嵌套查詢19、查詢與smith部門和崗位完全相同的所有雇員的姓名

方法一：

selectenamefromempwheredeptno=(selectdeptnofromempwhereename='smith')andjob=(selectjobfromempwhereename='smith')

方法二：

selects.enamefromemps,emptwheres.deptno=t.deptnoands.job=t.jobandt.ename='smith'嵌套查詢20、查詢高于自己所在部門平均工資的員工的信息

1、先查詢各個部門的平均工資和部門號selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno

2、把上面的查詢當做一張表

select*fromemps,(selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno)twheres.deptno=t.deptnoands.sal>=t.部門平均工資合并查詢21、查詢工資超過2500元或職位是經理的員工的姓名、職位、工資方法一：

selectename,job,salfromempwheresal>2500unionselectename,job,salfromempwherejob='manager'方法二：

selectename,ename,job,salfromempwheresal>2500orjob='manager'合并查詢2、查詢工資超過2500元且職位是經理的員工的姓名、職位、工資

方法一：selectename,job,salfromempwheresal>2500intersectselectename,job,salfromempwherejob='manager'方法二：selectename,ename,job,salfromempwheresal>2500andjob='manager'合并查詢3、查詢工資超過2500元但職位不是經理的員工的姓名、職位、工資方法一：selectename,job,salfromempwheresal>2500exceptselectename,job,salfromempwherejob='manager'

方法二：

selectename,ename,job,salfromempwheresal>2500andjob!='manager'集合操作和邏輯操作雖然集合操作都可轉化為邏輯操作，但集合操作要比邏輯操作快很多。集合操作是高水平DBA常用的。SQL查詢進階22、查詢員工的姓名和他所在部門的名稱

selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno（SQL1992標準）

selectename,dnamefromempjoindeptonemp.deptno=dept.deptno（SQL1999標準）SQL查詢進階23、查詢姓名中第二個字母不是a的員工名，他所在部門名和薪水等級

selectename,dname,gradefromemp,dept,salgradewhereemp.deptno=dept.deptnoandemp.salbetweenlosalandhisalandenamenotlike'_a%'24、將每個員工和他的下屬的名字列出來

selects.ename'員工名字',t.ename'下屬的名字'fromempsrightjoinemptont.mgr=s.empnoSQL查詢進階25、將每個員工和他的上級及下屬的名字都列出來selects.ename'員工名字',t.ename'下屬的名字',u.ename'上級的名字'fromempsleftjoinemptont.mgr=s.empnoleftjoinempuons.mgr=u.empnoSQL查詢進階26、查詢平均工資等級最低的部門的部門名稱

1、先顯示出部門的平均工資等級

selectdeptno,grade'部門平均工資等級'from(selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno)sjoinsalgradeon部門平均工資betweenlosalandhisal

2、再顯示部門最低工資等級

selectMIN(t.部門平均工資等級)'部門最低平均工資等級'from(selectdeptno,grade'部門平均工資等級'from(selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno)sjoinsalgradeon部門平均工資betweenlosalandhisal)t

SQL查詢進階3、顯示平均工資等級最低的部門的部門名稱selectd.dname,t.部門平均工資等級fromdeptdjoin(selectdeptno,grade'部門平均工資等級'from(selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno)sjoinsalgradeon部門平均工資betweenlosalandhisal)tond.deptno=t.deptnowheret.部門平均工資等級=(selectMIN(t.部門平均工資等級)

'部門最低平均工資等級'from(selectdeptno,grade'部門平均工資等級'from(selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno)sjoinsalgradeon部門平均工資betweenlosalandhisal)t)SQL查詢進階27、不使用組函數，查詢薪水的最高值（面試題）

selectename,salfromempwheresalnotin(selectdistincts.salfromemps,emptwheres.sal<t.sal)SQL查詢進階28、顯示比普通員工的最高薪水還要高的經理的姓名1、首先，顯示出所有經理的工號

selectdistinctmgrfromempwheremgrisnotnull

2、查詢出所有普通員工的最高工資

selectMAX(sal)fromempwhereempnonotin(selectdistinctmgrfromempwheremgrisnotnull)

3、顯示比普通員工的工資都高的經理的姓名、工資

selectename,salfromempwhereempnoin(selectdistinctmgrfromempwheremgrisnotnull)andsal>(selectMAX(sal)fromempwhereempnonotin(selectdistinctmgrfromempwheremgrisnotnull))SQL查詢進階查詢一條SQL語句的執行時間的方法：declare@dt1datetimedeclare@dt2datetimeset@dt1=GETDATE()select*fromProduction.TransactionHistory//待查詢執行時間的SQL語句//表格Production.TransactionHistory有113443條記錄set@dt2=GETDATE()selectdatediff(ms,@dt1,@dt2)as'毫秒'ns表示‘納秒’，ms表示‘毫秒’，s表示‘秒’，mcf表示‘微妙’。。TransactSQL入門將T-SQL塊發送給SQLServer服務器用戶執行過程語句引擎將SQL語句發送給SQL語句執行器SQLServer服務器T-SQL引擎SQL語句

執行器過程語句

執行器執行SQL語句將結果發送給用戶T-SQL入門掌握如何定義變量并賦值掌握如何輸出顯示數據掌握IF、WHILE、CASE邏輯控制語句理解SQL中批處理的概念使用變量變量分為：局部變量：局部變量必須以標記@作為前綴，如@age局部變量的使用也是先聲明，再賦值全局變量：全局變量必須以標記@@作為前綴，如@@version全局變量由系統定義和維護，我們只能讀取，不能修改全局變量的值T-SQL入門：局部變量例如：DECLARE@namevarchar(8)DECLARE@seatint聲明局部變量

DECLARE@變量名數據類型例如：SET@name=‘張三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘s25302’

必須確保篩選出的記錄只有1條

或

局部變量示例問題：編寫T-SQL查找李文才的左右同桌？學員信息表分析：第一步，找出“李文才“的座位號；第二步，李文才的座位號加1或減1局部變量示例/*--查找李文才的信息--*/DECLARE@namevarchar(8)--學員姓名SET@name='李文才'--使用SET賦值SELECT*FROMstuInfoWHEREstuName=@name/*--查找李文才的左右同桌--*/DECLARE@seatint--座位號SELECT@seat=stuSeatFROMstuInfo

--使用SELECT賦值

WHEREstuName=@nameSELECT*FROMstuInfoWHERE(stuSeat=@seat+1)OR(stuSeat=@seat-1)GO參考語句全局變量全局變量都使用兩個@標志作為前綴

變量含義@@ERROR最后一個T-SQL錯誤的錯誤號@@IDENTITY最后一次插入的標識值@@LANGUAGE當前使用的語言的名稱@@MAX_CONNECTIONS可以創建的同時連接的最大數目@@ROWCOUNT 受上一個SQL語句影響的行數@@SERVERNAME本地服務器的名稱@@TRANSCOUNT 當前連接打開的事務數@@VERSION

SQLServer的版本信息全局變量print@@versionprint@@servernameprint@@langusge邏輯控制語句IF－ELSE語句SQL中的IF-ELSE語句IF

（條件）

BEGIN

語句1

語句2……

ENDELSE

BEGIN

語句1；語句2；

……

ENDELSE是可選部分如果有多條語句，才需要BEGIN-END語句塊

IF-ELSE示例問題：統計并顯示本班筆試平均分，如果平均分在70以上，顯示“成績優秀“，并顯示前三名學員的考試信息；如果在70以下，顯示“本班成績較差“，并顯示后三名學員的考試信息。學員成績表分析：第一步，統計平均成績存入臨時變量；第二步，用IF－ELSE判斷；IF-ELSE舉例declare@avgScorefloatselect@avgScore=AVG(writtenExam)fromstuMarksprint'平均成績：‘+cast(@avgScoreasvarchar(5))if(@avgScore>=70)beginprint'本班筆試成績優秀,前三名學員成績為：'selecttop3*fromstuMarksorderbywrittenExamdescendelsebeginprint'本班筆試成績較差,后三名學員成績為：'selecttop3*fromstuMarksorderbywrittenExamend設置輸出結果的格式為了文本消息和輸出結果顯示在同一窗口，需要設置輸出結果的格式邏輯控制語句WHILE循環語句SQL中的WHILE語句WHILE

（條件）

BEGIN

語句1

語句2……

BREAK

ENDBREAK表示退出循環如果有多條語句，才需要BEGIN-END語句塊

WHILE循環舉例本次考試成績較差，假定要提分，確保每人筆試都通過。提分規則很簡單，先每人都加2分，看是否都通過，如果沒有全部通過，每人再加2分，再看是否都通過，如此反復提分，若有人通過加分達到100分停止加分，否則直到所有人都通過為止。分析：

第一步，統計沒通過的人數；

第二步，如果有人沒通過，加分；

第三步，循環判斷。WHILE循環舉例---方法一begintransactionsetnocounton--不顯示受影響的行數print'加分前的成績：'print'-------------------------------'select*fromstuMarksdeclare@nopassintselect@nopass=COUNT(*)fromstuMarkswherewrittenExam<60while(@nopass>0)beginupdatestuMarkssetwrittenExam=writtenExam+2wherewrittenExam<100select@nopass=COUNT(*)fromstuMarkswherewrittenExam<60endprint'加分后的成績：'print'-------------------------------'select*fromstuMarksrollbacktransactionWHILE循環舉例---方法二begintransactiondeclare@nint,dclare@mintwhile(1=1)beginselect@n=COUNT(*)fromstuMarkswherewrittenExam<60select@m=COUNT(*)fromstuMarkswherewrittenExam=100if((@n>0)and(@m=0))beginupdatestuMarkssetwrittenExam=writtenExam+2endelsebreakendprint'加分后的學員成績'select*fromstuMarksrollbacktransaction邏輯控制語句CASE－END多分支語句CASE

WHEN

條件1THEN

結果1

WHEN

條件2THEN

結果2……

ELSE

其他結果ENDCASE-END舉例學員成績表問題：采用ABCDE五級打分制來顯示筆試成績。

A級:90分以上

B級：80－89分

C級:70－79分

D級：60－69分

E級：60分以下CASE-END舉例print'ABCDE五級打分制成績如下'selectstuNoas學號,成績=casewhenwrittenExam<60then'E'whenwrittenExambetween60and69then'D'whenwrittenExambetween70and79then'C'whenwrittenExambetween80and89then'B'else'A'endfromstuMarks批處理語句批處理是包含一個或多個SQL語句的組，從應用程序一次性地發送到SQLServer執行SQLServer將批處理語句編譯成一個可執行單元，此單元稱為執行計劃。執行計劃中的語句每次執行一條客戶端應用程序SQLServer服務器批處理語