數(shù)據(jù)庫(kù)語(yǔ)句大全

1、一、基礎(chǔ)1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)Create DATABASE database-n ame2、說(shuō)明：刪除數(shù)據(jù)庫(kù)drop database dbn ame3、說(shuō)明：備份 sql server-創(chuàng)建備份數(shù)據(jù)的deviceUSE masterMyNwind_1.dat EXEC sp_addumpdevice disk , testBack , c:-開(kāi)始備份BACK UP DATABASE pubs TO testBack4、說(shuō)明：創(chuàng)建新表create table tab name(col1 type1 not n ull p rimary key,col2 type2 not n ull,.)根據(jù)

2、已有的表創(chuàng)建新表:A: create table tab_new like tab_old (使用舊表創(chuàng)建新表 )from tab_old defi niti on onlyB: create table tab_new as select col1,col25、說(shuō)明：刪除新表drop table tab name6、說(shuō)明：增加一個(gè)列Alter table tab name add colu mn col type注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類(lèi)型也不能改變，唯一能改變的是增加varchar類(lèi)型的長(zhǎng)度。7、說(shuō)明：添加主鍵：Alter table tab name add prim

3、ary key(col)說(shuō)明：刪除主鍵： Alter table tab name drop p rimary key(col)8、說(shuō)明：創(chuàng)建索弓丨：create uniq ue in dex idx name on tab name(col.)刪除索弓丨： drop in dex idx name注：索引是不可更改的，想更改必須刪除重新建。9、說(shuō)明：創(chuàng)建視圖：create view view name as select stateme nt刪除視圖： drop view view namei0、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句選擇:select * from tablei where 范圍

4、插入:in sert into table1(field1,field2) values(value1,value2)刪除:delete from tablei where 范圍更新:up date tablei set fieldi=valuei where 范圍查找:select * from tablei where fieldi like'%value1ifte的語(yǔ)法很精妙，查資料！排序:select * from tablei order by field1,field2 desc總數(shù):select count as totalco unt from tablei求和:sel

5、ect sum(fieldi) as sumvalue from tablei平均:select avg(fieldi) as avgvalue from tablei最大:select max(fieldi) as maxvalue from tablei最小:select min (fieldi) as min value from tablei11、說(shuō)明：幾個(gè)高級(jí)查詢運(yùn)算詞A: UNION運(yùn)算符UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLEi和TABLE2 ）并消去表中任 何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL隨UNION 起使用時(shí)（即 UNION ALL ），不消 除重復(fù)行

6、。兩種情況下，派生表的每一行不是來(lái)自TABLEi就是來(lái)自TABLE2 。B: EXCE PT運(yùn)算符EXCE PT運(yùn)算符通過(guò)包括所有在TABLE1 中但不在TABLE2中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng)ALL隨EXCEPT一起使用時(shí)（EXCEPT ALL），不消除重復(fù)行。C: INTERSECT 運(yùn)算符INTERSECT 運(yùn)算符通過(guò)只包括TABLE1和 TABLE2中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng)ALL隨INTERSECT一起使用時(shí)(INTERSECT ALL)，不消除重復(fù)行。注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。12、說(shuō)明：使用外連接A、left outer j

7、oin :左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cB: right outer join:右外連接（右連接）：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。C: full outer join :全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。二、提升1、說(shuō)明：復(fù)制表（只復(fù)制結(jié)構(gòu)，源表名：a新表名：b） （Access可用）法一：select * into b from a wher

8、e 1<>1法二：select top 0 * into b from a2、說(shuō)明：拷貝表（拷貝數(shù)據(jù)，源表名：a目標(biāo)表名：b） （Access可用）in sert into b(a, b, c) select d,e,f from b;3、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝（具體數(shù)據(jù)使用絕對(duì)路徑）（Access可用）select a,b,c from a where a IN (select d from b或者：select a,b,c from a where a INinsert into b(a, b, c) selec t d,e,f from b in具體數(shù)據(jù)庫(kù) ' w

9、here 條件例子：.from b in "&Server.Ma pP ath("."&data.mdb" &" where.4、說(shuō)明：子查詢（表名1 : a表名2： b）(1,2,3)5、說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間select a.title,a.user name,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b6、說(shuō)明：外連接查詢（表名1 : a表名2 : b）select

10、 a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c7、說(shuō)明：在線視圖查詢（表名1: aselect * from (Select a,b,c FROM a) T where t.a > 1;8、說(shuō)明：between 的用法，between 限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值，not between 不包括數(shù)值1 and數(shù)值2select * from table1 where time betwee n time1 and time2select a,b,c, from table1 where a not be

11、twee n9、說(shuō)明：in的使用方法select * from table1 where anot in (值 T 值 2'值 4'值 6')from table2 where10、說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息delete from table1 where not exists ( selecttable1.field1=table2.field111、說(shuō)明：四表聯(lián)查問(wèn)題:select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d

12、ona.a=d.d where .select top 10 b.* from (select top 20主鍵字段，排序字段from表名order by排序字12、說(shuō)明：日程安排提前五分鐘提醒SQL: select * from日程安排 where datediff(minute開(kāi)始時(shí)間,getdate()>513、說(shuō)明：一條sql語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)段desc) a,表名 b where b.主鍵字段 =a.主鍵字段 order by a.排序字段14、說(shuō)明：前10條記錄select top 10 * form table1 where 范圍15、說(shuō)明：選擇在每一組 b值相同的數(shù)據(jù)中對(duì)

13、應(yīng)的 a最大的記錄的所有信息(類(lèi)似這樣 的用法可以用于論壇每月排行榜，每月熱銷(xiāo)產(chǎn)品分析，按科目成績(jī)排名，等等5select a,b,c from table name ta where a=(select max(a) from table name tb wheretb.b=ta.b)16、說(shuō)明：包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表(select a from tableA exce pt (select a from tableB) exce pt (select a from tableC)17、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)se

14、lect top 10 * from table name order by n ewid()18、說(shuō)明：隨機(jī)選擇記錄select n ewid()19、說(shuō)明：刪除重復(fù)記錄Delete from table name where id not in (select max(id) from table name group bycol1,col2,.)20、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名select n ame from sysobjects where type='TableName')21、說(shuō)明：列出表里的所有的select n ame from syscolu mns wh

15、ere id=object_id(22、說(shuō)明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多 重選擇，類(lèi)似 select中的case。select type,sum(case vender whenA then pcs else 0 end),sum(case vender when C the n pcs else 0 en d),sum(case ven der whe n B the n pcs else 0 end) FROM table namegroup by type顯示結(jié)果:type ven der pcs電腦電腦光盤(pán)光盤(pán)手機(jī)手機(jī)23、說(shuō)明

16、：初始化表 tablelTRUNCATE TABLE tablel24、說(shuō)明：選擇從10到15的記錄別名 order by idselect top 5 * from (select top 15 * from table order by id asc) table_ desc I三、技巧1、1=1，1=2的使用，在SQL語(yǔ)句組合時(shí)用的較多"where 1=1 "是表示選擇全部“where 1=2 全部不選,如:begi nif strWhere !=set strSQL = select coun t(*) as Total from + tblName +'w

17、here + strWhereendelsebeginset strSQL = select coun t(*) as Total from + tblName +'Where n ame = LogicalFileNameend我們可以直接寫(xiě)成set strSQL = select coun t(*) as Total from + tblName +'where 安定 +strWhere |2、收縮數(shù)據(jù)庫(kù)-重建索引DBCC REINDEXDBCC INDEXDEFRAG-收縮數(shù)據(jù)和日志DBCC SHRINKDBDBCC SHRINKFILE3、壓縮數(shù)據(jù)庫(kù)dbcc shri

18、nkdatabase(db name)4、轉(zhuǎn)移數(shù)據(jù)庫(kù)給新用戶以已存在用戶權(quán)限exec sp _cha nge_users_log in up date_ one , newn ame', old name go5、檢查備份集RESTORE VERIFYONLY from disk E:dvbbs.bak6、修復(fù)數(shù)據(jù)庫(kù)Alter DATABASE dvbbs SET SINGLE_USERGODBCC CHECKDBdvbbs ,repai r_allow_data_loss) WITH TABLOCKGOAlter DATABASE dvbbs SET MULTI_USERGO7、日志

19、清除SET NOCOUNT ONDECLARE LogicalFileName sys name.MaxMi nu tes INT,NewSize INTUSE table name -要操作的數(shù)據(jù)庫(kù)名Select LogicalFileName ='tablename_log -日志文件名MaxMi nu tes = 10, - Limit on time allowed to wrap log.NewSize = 1 -你想設(shè)定的日志文件的大小 （M） -Setup / in itializeDECLARE Origi nalSize intSelect Origi nalSize

20、 = sizeFROM sysfilesSelect Origi nal Size of+ db_name() + LOG isCONVERT(VARCHAR(30),Origi nalSize) + 8K pages orCONVERT(VARCHAR(30),(OriginalSize*8/1024) + MB 'FROM sysfilesWhere n ame = LogicalFileNameCreate TABLE DummyTra ns(DummyColu mn char (8000) n ot n ull)DECLARE Cou nter INT,StartTime DA

21、TETIME,Tru ncLog VARCHAR(255)Select StartTime = GETDATE(),Tru ncLog = BACK UP LOG + db_n ame() + WITH TRUNCATE_ONLYDBCC SHRINKFILE (LogicalFileName, NewSize)EXEC (Tru ncLog)-Wrap the log if n ecessary.WHILE MaxMinutes > DATEDIFF (mi, StartTime, GETDATE() - time has not expiredAND Orig in alSize =

22、 (Select size FROM sysfiles Where name = LogicalFileName)AND (Origi nalSize * 8 /1024) > NewSizeBEGIN - Outer loo p.Select Cou nter = 0WHILE (Cou nter < Origi nalSize / 16) AND (Cou nter < 50000)BEGIN - up dateInsert DummyTrans VALUES ( Fill Log ')Delete DummyTra nsSelect Cou nter = Cou

23、 nter + 1ENDEXEC (Tru ncLog)Sele ct Final Size ofEND+ db_n ame() + LOG is +CONVERT(VARCHAR(3O),size) + 8K pages or +CONVERT(VARCHAR(3O),(size*8/1O24) + MB'FROM sysfilesWhere n ame = LogicalFileNameDrop TABLE DummyTra nsSET NOCOUNT OFF&說(shuō)明：更改某個(gè)表exec sp_cha ngeobjectow ner table name , dbo 9、存儲(chǔ)更改全