java面試必須要知道的SQL語法,語句

樓主大中小發(fā)表于2011-8-1111:21

只看該作者弱電安防培訓(xùn)--工信部認(rèn)證|廣播：51CTO三地網(wǎng)友線下聚會(huì)|華為ARG3企業(yè)級(jí)路由器問答接龍|大喇叭：51CTO六周年紀(jì)念T恤發(fā)布了！java面試必須要知道的SQL語法，語句java面試必須要知道的SQL語法，語句

java私塾整理收集的SQL方面的面試題，方便大家參考學(xué)習(xí)。

DDL—數(shù)據(jù)定義語言(Create，Alter，Drop，DECLARE)

DML—數(shù)據(jù)操縱語言(Select，Delete，Update，Insert)

DCL—數(shù)據(jù)控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,簡要介紹基礎(chǔ)語句：

1、說明：創(chuàng)建數(shù)據(jù)庫

CreateDATABASEdatabase-name

2、說明：刪除數(shù)據(jù)庫

dropdatabasedbname

3、說明：備份sqlserver

創(chuàng)建備份數(shù)據(jù)的device

USEmaster

EXECsp_addumpdevice'disk','testBack','c:\mssql7backup\MyNwind_1.dat'

開始備份

BACKUPDATABASEpubsTOtestBack

4、說明：創(chuàng)建新表

createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)

根據(jù)已有的表創(chuàng)建新表：

A：createtabletab_newliketab_old(使用舊表創(chuàng)建新表)

B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly

5、說明：刪除新表

droptabletabname

6、說明：增加一個(gè)列

Altertabletabnameaddcolumncoltype

注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類型

的長度。

7、說明：添加主鍵：Altertabletabnameaddprimarykey(col)

說明：刪除主鍵：Altertabletabnamedropprimarykey(col)

8、說明：創(chuàng)建索引：create[unique]indexidxnameontabname(col….)

刪除索引：dropindexidxname

注：索引是不可更改的，想更改必須刪除重新建。

9、說明：創(chuàng)建視圖：createviewviewnameasselectstatement

刪除視圖：dropviewviewname

10、說明：幾個(gè)簡單的基本的sql語句

選擇：select*fromtable1where范圍

插入：insertintotable1(field1,field2)values(value1,value2)

刪除：deletefromtable1where范圍

更新：updatetable1setfield1=value1where范圍

查找：select*fromtable1wherefield1like’%value1%’like的語法很精妙，查資料!

排序：select*fromtable1orderbyfield1,field2[desc]

總數(shù)：selectcountastotalcountfromtable1

求和：selectsum(field1)assumvaluefromtable1

平均：selectavg(field1)asavgvaluefromtable1

最大：selectmax(field1)asmaxvaluefromtable1

最?。簊electmin(field1)asminvaluefromtable1

11、說明：幾個(gè)高級(jí)查詢運(yùn)算詞

A：UNION運(yùn)算符

UNION運(yùn)算符通過組合其他兩個(gè)結(jié)果表（例如TABLE1和TABLE2）并消去表中任何重復(fù)行而派生

出一個(gè)結(jié)果表。當(dāng)ALL隨UNION一起使用時(shí)（即UNIONALL），不消除重復(fù)行。兩種情況下，派

生表的每一行不是來自TABLE1就是來自TABLE2。

B：EXCEPT運(yùn)算符

EXCEPT運(yùn)算符通過包括所有在TABLE1中但不在TABLE2中的行并消除所有重復(fù)行而派生出一個(gè)

結(jié)果表。當(dāng)ALL隨EXCEPT一起使用時(shí)(EXCEPTALL)，不消除重復(fù)行。

C：INTERSECT運(yùn)算符

INTERSECT運(yùn)算符通過只包括TABLE1和TABLE2中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果

表。當(dāng)ALL隨INTERSECT一起使用時(shí)(INTERSECTALL)，不消除重復(fù)行。

注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。

12、說明：使用外連接

A、leftouterjoin：

左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

sql:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

B：rightouterjoin:

右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：fullouterjoin：

全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。

13、說明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a新表名：b)(Access可用)

法一：select*intobfromawhere1<>1

法二：selecttop0*intobfroma

14、說明：拷貝表(拷貝數(shù)據(jù),源表名：a目標(biāo)表名：b)(Access可用)

insertintob(a,b,c)selectd,e,ffromb;

15、說明：跨數(shù)據(jù)庫之間表的拷貝(具體數(shù)據(jù)使用絕對路徑)(Access可用)

insertintob(a,b,c)selectd,e,ffrombin‘具體數(shù)據(jù)庫’where條件

例子：..frombin'"&Server.MapPath(".")&"\data.mdb"&"'where..

16、說明：子查詢(表名1：a表名2：b)

selecta,b,cfromawhereaIN(selectdfromb)或者:selecta,b,cfromawhereaIN

(1,2,3)

17、說明：顯示文章、提交人和最后回復(fù)時(shí)間

selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefrom

tablewheretable.title=a.title)b

18.說明：外連接查詢(表名1：a表名2：b)

selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

19、說明：在線視圖查詢(表名1：a)

select*from(Selecta,b,cFROMa)Twheret.a>1;

20、說明：between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,notbetween不包括

select*fromtable1wheretimebetweentime1andtime2

selecta,b,c,fromtable1whereanotbetween數(shù)值1and數(shù)值2

21、說明：in的使用方法

select*fromtable1wherea[not]in(‘值1’,’值2’,’值4’,’值6’)

22、說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

deletefromtable1wherenotexists(select*fromtable2where

table1.field1=table2.field1)

23、說明：四表聯(lián)查問題：

select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoin

dona.a=d.dwhere

24、說明：日程安排提前五分鐘提醒

sql:select*from日程安排wheredatediff('minute',f開始時(shí)間,getdate())>5

25、說明：一條sql語句搞定數(shù)據(jù)庫分頁

selecttop10b.*from(selecttop20主鍵字段,排序字段from表名orderby排序字段

desc)a,表名bwhereb.主鍵字段=a.主鍵字段orderbya.排序字段

26、說明：前10條記錄

selecttop10*formtable1where范圍

27、說明：選擇在每一組b值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用

于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)

selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwhere

tb.b=ta.b)

28、說明：包括所有在TableA中但不在TableB和TableC中的行并消除所有重復(fù)行而派生出一個(gè)

結(jié)果表

(selectafromtableA)except(selectafromtableB)except(selectafromtableC)

29、說明：隨機(jī)取出10條數(shù)據(jù)

selecttop10*fromtablenameorderbynewid()

30、說明：隨機(jī)選擇記錄

selectnewid()

31、說明：刪除重復(fù)記錄

Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupby

col1,col2,...)

32、說明：列出數(shù)據(jù)庫里所有的表名

selectnamefromsysobjectswheretype='U'

33、說明：列出表里的所有的

selectnamefromsyscolumnswhereid=object_id('TableName')

34、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類似

select中的case。

selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'

thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablename

groupbytype

顯示結(jié)果：

typevenderpcs

電腦A1

電腦A1

光盤B2

光盤A2

手機(jī)B3

手機(jī)C3

35、說明：初始化表table1

TRUNCATETABLEtable1搜索更多相關(guān)主題的帖子:java沙發(fā)大中小發(fā)表于2011-8-1111:41

只看該作者弱電安防培訓(xùn)--工信部認(rèn)證|廣播：51CTO三地網(wǎng)友線下聚會(huì)|華為ARG3企業(yè)級(jí)路由器問答接龍|大喇叭：51CTO六周年紀(jì)念T恤發(fā)布了！36、說明：選擇從10到15的記錄

selecttop5*from(selecttop15*fromtableorderbyidasc)table_別名orderby

iddesc

隨機(jī)選擇數(shù)據(jù)庫記錄的方法（使用Randomize函數(shù)，通過SQL語句實(shí)現(xiàn)）

對存儲(chǔ)在數(shù)據(jù)庫中的數(shù)據(jù)來說，隨機(jī)數(shù)特性能給出上面的效果，但它們可能太慢了些。你不能

要求ASP“找個(gè)隨機(jī)數(shù)”然后打印出來。實(shí)際上常見的解決方案是建立如下所示的循環(huán)：

Randomize

RNumber=Int(Rnd*499)+1

WhileNotobjRec.EOF

IfobjRec("ID")=RNumberTHEN

...這里是執(zhí)行腳本...

endif

objRec.MoveNext

Wend

這很容易理解。首先，你取出1到500范圍之內(nèi)的一個(gè)隨機(jī)數(shù)（假設(shè)500就是數(shù)據(jù)庫內(nèi)記錄的總

數(shù)）。然后，你遍歷每一記錄來測試ID的值、檢查其是否匹配RNumber。滿足條件的話就執(zhí)行由

THEN關(guān)鍵字開始的那一塊代碼。假如你的RNumber等于495，那么要循環(huán)一遍數(shù)據(jù)庫花的時(shí)間可就

長了。雖然500這個(gè)數(shù)字看起來大了些，但相比更為穩(wěn)固的企業(yè)解決方案這還是個(gè)小型數(shù)據(jù)庫了，

后者通常在一個(gè)數(shù)據(jù)庫內(nèi)就包含了成千上萬條記錄。這時(shí)候不就死定了？

采用SQL，你就可以很快地找出準(zhǔn)確的記錄并且打開一個(gè)只包含該記錄的recordset，如下所示

：

Randomize

RNumber=Int(Rnd*499)+1

sql="Select*FROMCustomersWhereID="&RNumber

setobjRec=ObjConn.Execute(SQL)

Response.WriteRNumber&"="&objRec("ID")&""&objRec("c_email")

不必寫出RNumber和ID，你只需要檢查匹配情況即可。只要你對以上代碼的工作滿意，你自可

按需操作“隨機(jī)”記錄。Recordset沒有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就

大大降低了處理時(shí)間。

再談隨機(jī)數(shù)

現(xiàn)在你下定決心要榨干Random函數(shù)的最后一滴油，那么你可能會(huì)一次取出多條隨機(jī)記錄或者

想采用一定隨機(jī)范圍內(nèi)的記錄。把上面的標(biāo)準(zhǔn)Random示例擴(kuò)展一下就可以用SQL應(yīng)對上面兩種情況

了。

為了取出幾條隨機(jī)選擇的記錄并存放在同一recordset內(nèi)，你可以存儲(chǔ)三個(gè)隨機(jī)數(shù)，然后查詢

數(shù)據(jù)庫獲得匹配這些數(shù)字的記錄：

sql="Select*FROMCustomersWhereID="&RNumber&"orID="&RNumber2&"or

ID="&RNumber3

假如你想選出10條記錄（也許是每次頁面裝載時(shí)的10條鏈接的列表），你可以用BETWEEN或者

數(shù)學(xué)等式選出第一條記錄和適當(dāng)數(shù)量的遞增記錄。這一操作可以通過好幾種方式來完成，但是

Select語句只顯示一種可能（這里的ID是自動(dòng)生成的號(hào)碼）：

sql="Select*FROMCustomersWhereIDBETWEEN"&RNumber&"AND"&RNumber&"+

9"

注意：以上代碼的執(zhí)行目的不是檢查數(shù)據(jù)庫內(nèi)是否有9條并發(fā)記錄。

隨機(jī)讀取若干條記錄，測試過

Access語法：Selecttop10*From表名orDERBYRnd(id)

sqlserver:selecttopn*from表名orderbynewid()

mysqlelect*From表名orderByrand()Limitn

Access左連接語法(最近開發(fā)要用左連接,Access幫助什么都沒有,網(wǎng)上沒有Access的SQL說明,只有

自己測試,現(xiàn)在記下以備后查)

語法electtable1.fd1,table1,fd2,table2.fd2Fromtable1leftjointable2on

table1.fd1,table2.fd1where...

使用SQL語句用...代替過長的字符串顯示

語法：

SQL數(shù)據(jù)庫：selectcasewhenlen(field)>10thenleft(field,10)+'...'elsefieldendas

news_name,news_idfromtablename

Access數(shù)據(jù)庫：Selectiif(len(field)>2,left(field,2)+'...',field)FROMtablename;

Conn.Execute說明

Execute方法

該方法用于執(zhí)行SQL語句。根據(jù)SQL語句執(zhí)行后是否返回記錄集，該方法的使用格式分為以下兩

種：

（1）．執(zhí)行SQL查詢語句時(shí)，將返回查詢得到的記錄集。用法為：

Set對象變量名=連接對象.Execute("SQL查詢語言")

Execute方法調(diào)用后，會(huì)自動(dòng)創(chuàng)建記錄集對象，并將查詢結(jié)果存儲(chǔ)在該記錄對象中，通過Set

方法，將記錄集賦給指定的對象保存，以后對象變量就代表了該記錄集對象。

（2）．執(zhí)行SQL的操作性語言時(shí)，沒有記錄集的返回。此時(shí)用法為：

連接對象.Execute"SQL操作性語句"[,RecordAffected][,Option]

?RecordAffected為可選項(xiàng)，此出可放置一個(gè)變量，SQL語句執(zhí)行后，所生效的記錄

數(shù)會(huì)自動(dòng)保存到該變量中。通過訪問該變量，就可知道SQL語句隊(duì)多少條記錄進(jìn)行了操作。

?Option可選項(xiàng)，該參數(shù)的取值通常為adCMDText，它用于告訴ADO，應(yīng)該將Execute

方法之后的第一個(gè)字符解釋為命令文本。通過指定該參數(shù)，可使執(zhí)行更高效。

*BeginTrans、RollbackTrans、CommitTrans方法

這三個(gè)方法是連接對象提供的用于事務(wù)處理的方法。BeginTrans用于開始一個(gè)事物；

RollbackTrans用于回滾事務(wù)；CommitTrans用于提交所有的事務(wù)處理結(jié)果，即確認(rèn)事務(wù)的處理。

事務(wù)處理可以將一組操作視為一個(gè)整體，只有全部語句都成功執(zhí)行后，事務(wù)處理才算成功；若

其中有一個(gè)語句執(zhí)行失敗，則整個(gè)處理就算失敗，并恢復(fù)到處里前的狀態(tài)。

BeginTrans和CommitTrans用于標(biāo)記事務(wù)的開始和結(jié)束，在這兩個(gè)之間的語句，就是作為事務(wù)

處理的語句。判斷事務(wù)處理是否成功，可通過連接對象的Error集合來實(shí)現(xiàn)，若Error集合的成員個(gè)

數(shù)不為0，則說明有錯(cuò)誤發(fā)生，事務(wù)處理失敗。Error集合中的每一個(gè)Error對象，代表一個(gè)錯(cuò)誤信

息。

37、一道SQL語句面試題，關(guān)于groupby

表內(nèi)容：

2005-05-09勝

2005-05-09勝

2005-05-09負(fù)

2005-05-09負(fù)

2005-05-10勝

2005-05-10負(fù)

2005-05-10負(fù)

如果要生成下列結(jié)果,該如何寫sql語句?

勝負(fù)

2005-05-092

2

2005-05-101

2

答：代碼如下：

createtable#tmp(rqvarchar(10),shengfunchar(1))

insertinto#tmpvalues('2005-05-09','勝')

insertinto#tmpvalues('2005-05-09','勝')

insertinto#tmpvalues('2005-05-09','負(fù)')

insertinto#tmpvalues('2005-05-09','負(fù)')

insertinto#tmpvalues('2005-05-10','勝')

insertinto#tmpvalues('2005-05-10','負(fù)')

insertinto#tmpvalues('2005-05-10','負(fù)')

1)selectrq,sum(casewhenshengfu='勝'then1else0end)'勝',sum(casewhenshengfu='

負(fù)'then1else0end)'負(fù)'from#tmpgroupbyrq

2)selectN.rq,N.勝,M.負(fù)from(

selectrq,勝=count(*)from#tmpwhereshengfu='勝'groupbyrq)Ninnerjoin

(selectrq,負(fù)=count(*)from#tmpwhereshengfu='負(fù)'groupbyrq)MonN.rq=M.rq

3)selecta.col001,a.a1勝,b.b1負(fù)from

(selectcol001,count(col001)a1fromtemp1wherecol002='勝'groupbycol001)a,

(selectcol001,count(col001)b1fromtemp1wherecol002='負(fù)'groupbycol001)b

wherea.col001=b.col001

38、請教一個(gè)面試中遇到的SQL語句的查詢問題

表中有ABC三列,用SQL語句實(shí)現(xiàn)：當(dāng)A列大于B列時(shí)選擇A列否則選擇B列，當(dāng)B列大于C列時(shí)選擇B列

否則選擇C列。

示例如下:

select(casewhena>bthenaelsebend),

(casewhenb>cthenbeslecend)

fromtable_name

39、一個(gè)日期判斷的sql語句？

請取出tb_send表中日期(SendTime字段)為當(dāng)天的所有記錄?(SendTime字段為datetime型，包含日

期與時(shí)間)

示例如下:

select*fromtbwheredatediff(dd,SendTime,getdate())=0

40、有一張表，里面有3個(gè)字段：語文，數(shù)學(xué)，英語。其中有3條記錄分別表示語文70分，數(shù)學(xué)80分

，英語58分，請用一條sql語句查詢出這三條記錄并按以下條件顯示出來（并寫出您的思路）：

大于或等于80表示優(yōu)秀，大于或等于60表示及格，小于60分表示不及格。

顯示格式：

語文

數(shù)學(xué)

英語

及格

優(yōu)秀

不及格

示例如下：

select

(casewhen語文>=80then'優(yōu)秀'

when語文>=60then'及格'

else'不及格')as語文,

(casewhen數(shù)學(xué)>=80then'優(yōu)秀'

when數(shù)學(xué)>=60then'及格'

else'不及格')as數(shù)學(xué),

(casewhen英語>=80then'優(yōu)秀'

when英語>=60then'及格'

else'不及格')as英語,

fromtable

41、在sqlserver2000中請用sql創(chuàng)建一張用戶臨時(shí)表和系統(tǒng)臨時(shí)表，里面包含兩個(gè)字段ID和

IDValues,類型都是int型，并解釋下兩者的區(qū)別?

用戶臨時(shí)表:createtable#xx(IDint,IDValuesint)

系統(tǒng)臨時(shí)表:createtable##xx(IDint,IDValuesint)

區(qū)別:

用戶臨時(shí)表只對創(chuàng)建這個(gè)表的用戶的Session可見,對其他進(jìn)程是不可見的.

當(dāng)創(chuàng)建它的進(jìn)程消失時(shí)這個(gè)臨時(shí)表就自動(dòng)刪除.

全局臨時(shí)表對整個(gè)SQLServer實(shí)例都可見,但是所有訪問它的Session都消失的時(shí)候,它也自動(dòng)刪除.

42、sqlserver2000是一種大型數(shù)據(jù)庫，他的存儲(chǔ)容量只受存儲(chǔ)介質(zhì)的限制，請問它是通過什么方

式實(shí)現(xiàn)這種無限容量機(jī)制的。

它的所有數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)文件中(*.dbf),所以只要文件夠大,SQL

Server的存儲(chǔ)容量是可以擴(kuò)

大的.

SQLServer2000數(shù)據(jù)庫有三種類型的文件：

主要數(shù)據(jù)文件

主要數(shù)據(jù)文件是數(shù)據(jù)庫的起點(diǎn)，指向數(shù)據(jù)庫中文件的其它部分。每個(gè)數(shù)據(jù)庫都有一個(gè)主要數(shù)據(jù)文件

。主要數(shù)據(jù)文件的推薦文件擴(kuò)展名是.mdf。

次要數(shù)據(jù)文件

次要數(shù)據(jù)文件包含除主要數(shù)據(jù)文件外的所有數(shù)據(jù)文件。有些數(shù)據(jù)庫可能沒有次要數(shù)據(jù)文件，而有些

數(shù)據(jù)庫則有多個(gè)次要數(shù)據(jù)文件。次要數(shù)據(jù)文件的推薦文件擴(kuò)展名是.ndf。

日志文件

日志文件包含恢復(fù)數(shù)據(jù)庫所需的所有日志信息。每個(gè)數(shù)據(jù)庫必須至少有一個(gè)日志文件，但可以不止

一個(gè)。日志文件的推薦文件擴(kuò)展名是.ldf。

43、請用一個(gè)sql語句得出結(jié)果

從table1,table2中取出如table3所列格式數(shù)據(jù)，注意提供的數(shù)據(jù)及結(jié)果不準(zhǔn)確，只是作為一個(gè)格

式向大家請教。

如使用存儲(chǔ)過程也可以。

table1

月份mon部門dep業(yè)績yj

一月份

01

10

一月份

02

10

一月份

03

5

二月份

02

8

二月份

04

9

三月份

03

8

table2

部門dep

部門名稱dname

01

國內(nèi)業(yè)務(wù)一部

02

國內(nèi)業(yè)務(wù)二部

03

國內(nèi)業(yè)務(wù)三部

04

國際業(yè)務(wù)部

table3（result）

部門dep一月份

二月份

三月份

01

10

null

null

02

10