數(shù)據(jù)庫(kù)原理

第四講SQLServer與數(shù)據(jù)查詢創(chuàng)建表創(chuàng)建emp表:createtableemp(empnointnotnullprimarykey,enamenvarchar(10)notnull,jobnvarchar(9)notnull,mgrintnotnull,hiredatedatenotnull,salmoneynotnull,commmoney,deptnointnotnull)建完表后，可執(zhí)行execsp_helpemp查看執(zhí)行情況。插入數(shù)據(jù)插入多行數(shù)據(jù)：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)建表、插入數(shù)據(jù)創(chuàng)建表salgrade如下：createtablesalgrade(gradeintprimarykey,losalmoney,hisalmoney)插入多行數(shù)據(jù)insertintosalgrade(grade,losal,hisal)values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999)建表、插入數(shù)據(jù)創(chuàng)建表deptcreatetabledept(deptnointprimarykey,dnamenvarchar(14),locnvarchar(13))插入多行數(shù)據(jù)insertintodept(deptno,dname,loc)values(10,'accounting','newyork'),(20,'research','dallas'),(30,'sales','chicago'),(40,'operations','boston')Emp表Dept表和Salgrade表數(shù)據(jù)查詢1、查詢工資高于500，或是崗位為manager的雇員同時(shí)還滿足姓名首字母為j。

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

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

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

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

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

6、計(jì)算有多少員工

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

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

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

fromempgroupbydeptno,joborderbydeptnoGroupby和Having子句11、查詢平均工資低于2000的部門號(hào)和它的平均工資selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptnohavingAVG(sal)<2000對(duì)數(shù)據(jù)分組的總結(jié)分組函數(shù)只能出現(xiàn)在選擇列表，having和orderby子句中。如果在select語(yǔ)句中同時(shí)有g(shù)roupby，having和orderby，那么它們的出現(xiàn)順序是groupby，having和orderby。在選擇列中如果有列、表達(dá)式和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)在groupby子句中，否則就會(huì)出錯(cuò)。多表查詢12、顯示雇員名、雇員工資和所在部門的名稱

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

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

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

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

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

方法一:

selectenamefromempwheredeptno=(selectdeptnofromempwhereename='smith')

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

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

方法一：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、先查詢各個(gè)部門的平均工資和部門號(hào)selectdeptno,AVG(sal)'部門平均工資'fromempgroupbydeptno

2、把上面的查詢當(dāng)做一張表

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

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

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

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

方法二：

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

selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno（SQL1992標(biāo)準(zhǔn)）

selectename,dnamefromempjoindeptonemp.deptno=dept.deptno（SQL1999標(biāo)準(zhǔn)）SQL查詢進(jìn)階23、查詢姓名中第二個(gè)字母不是a的員工名，他所在部門名和薪水等級(jí)

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

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

1、先顯示出部門的平均工資等級(jí)

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

2、再顯示部門最低工資等級(jí)

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

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

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

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

selectdistinctmgrfromempwheremgrisnotnull

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

selectMAX(sal)fromempwhereempnonotin(selectdistinctmgrfromempwheremgrisnotnull)

3、顯示比普通員工的工資都高的經(jīng)理的姓名、工資

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

執(zhí)行器過(guò)程語(yǔ)句

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

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

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

或

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

--使用SELECT賦值

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

變量含義@@ERROR最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)@@IDENTITY最后一次插入的標(biāo)識(shí)值@@LANGUAGE當(dāng)前使用的語(yǔ)言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時(shí)連接的最大數(shù)目@@ROWCOUNT 受上一個(gè)SQL語(yǔ)句影響的行數(shù)@@SERVERNAME本地服務(wù)器的名稱@@TRANSCOUNT 當(dāng)前連接打開的事務(wù)數(shù)@@VERSION

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

（條件）

BEGIN

語(yǔ)句1

語(yǔ)句2……

ENDELSE

BEGIN

語(yǔ)句1；語(yǔ)句2；

……

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

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

（條件）

BEGIN

語(yǔ)句1

語(yǔ)句2……

BREAK

ENDBREAK表示退出循環(huán)如果有多條語(yǔ)句，才需要BEGIN-END語(yǔ)句塊

WHILE循環(huán)舉例本次考試成績(jī)較差，假定要提分，確保每人筆試都通過(guò)。提分規(guī)則很簡(jiǎn)單，先每人都加2分，看是否都通過(guò)，如果沒(méi)有全部通過(guò)，每人再加2分，再看是否都通過(guò)，如此反復(fù)提分，若有人通過(guò)加分達(dá)到100分停止加分，否則直到所有人都通過(guò)為止。分析：

第一步，統(tǒng)計(jì)沒(méi)通過(guò)的人數(shù)；

第二步，如果有人沒(méi)通過(guò)，加分；

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

WHEN

條件1THEN

結(jié)果1

WHEN

條件2THEN

結(jié)果2……

ELSE

其他結(jié)果ENDCASE-END舉例學(xué)員成績(jī)表問(wèn)題：采用ABCDE五級(jí)打分制來(lái)顯示筆試成績(jī)。

A級(jí):90分以上

B級(jí)：80－89分

C級(jí):70－79分

D級(jí)：60－69分

E級(jí)：60分以下CASE-END舉例print'ABCDE五級(jí)打分制成績(jī)?nèi)缦?selectstuNoas學(xué)號(hào),成績(jī)=casewhenwrittenExam<60then'E'whenwrittenExambetween60and69then'D'whenwrittenExambetween70and79then'C'whenwrittenExambetween80and89then'B'else'A'endfromstuMarks批處理語(yǔ)句批處理是包含一個(gè)或多個(gè)SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱為執(zhí)行計(jì)劃。執(zhí)行計(jì)劃中的語(yǔ)句每次執(zhí)行一條客戶端應(yīng)用程序SQLServer服務(wù)器批處理語(yǔ)