數(shù)據(jù)庫基本SQL語句大全

1、數(shù)據(jù)庫基本SQL語句大全數(shù)據(jù)庫基本-SQL語句大全、基礎(chǔ)1、說明：創(chuàng)建數(shù)據(jù)庫Create DATABASE database-name2、說明：刪除數(shù)據(jù)庫 drop database dbname3、說明：備份 sql server- 創(chuàng)建備份數(shù)據(jù)的deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.d at'- 開始備份BACKUP DATABASE pubs TO testBack4、說明：創(chuàng)建新表create table tabn

2、ame(col1 type1 not null primary key,col2 typ e2 not null ,.)根據(jù)已有的表創(chuàng)建新表：A：createtabletab_newlike tab_old (使用舊表創(chuàng)建新表)B：createtabletab_newasselect col1,col2 from tab_olddefinition only5、說明：刪除新表 drop table tabname6、說明：增加一個(gè)列Alter table tabname add column col type注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varc

3、har類型的長度。7、說明：添力口主鍵：Alter table tabname add primary key(col)說明：刪除主鍵： Alter table tabname drop primary key(col)8、說明：創(chuàng)建索弓I： create unique index idxname on tabname(col .) 刪除索弓 I： drop index idxname 注：索引是不可更改的，想更改必須刪除重新建。9、說明：創(chuàng)建視圖：create view viewname as select statement刪除視圖：drop view viewname二、提升1、說明：復(fù)

4、制表（只復(fù)制結(jié)構(gòu)，源表名：a 新表名：b） （Access可用）法一： select * into b from a where 1<>1法二：select top 0 * into b from a2、說明：拷貝表（拷貝數(shù)據(jù)，源表名：a目標(biāo)表名：b） （Access可用）insertintob（a,b,c）selectd,e,ffromb;3、說明：跨數(shù)據(jù)庫之間表的拷貝（具體數(shù)據(jù)使用絕對路徑）（Access可用）insertintob（a,b,c）selectd,e,ffrombin '具體數(shù)據(jù)庫where 條件例子：.from b in '"&

5、Server.MapPath（"."&"data.mdb"&"' where.4、說明：子查詢（表名1: a 表名2: b）select a,b,c from a where a IN （select d from b或者： select a,b,c from a where a IN （1,2,3）5、說明：顯示文章、提交人和最后回復(fù)時(shí)間select a.title,a.username,b.adddatefrom table a,（select max（adddate） adddate from table wher

6、e table.title=a.title）b6、說明：外連接查詢（表名1: a表名2: b）selecta.a, a.b, a.c, b.c, b.d, b.ffrom a LEFT OUT JOIN b ON a.a = b.c7、說明：在線視圖查詢 （表名1: aselect * from （Select a,b,c FROM a） T where t.a > 1;8、說明：between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括select * from table1 where time between time1 and time2se

7、lect a,b,c, from table1 where a not between數(shù)值 1 and 數(shù)值 29、說明：in的使用方法select * from table1 where a not in （'值 1','值 2','值 4', 值6'）10、說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息delete from table1 where not exists （ select * from table2 where table1.field1=table2.field111、說明：四表聯(lián)查問題：select * fr

8、om a left inner join b on a.a=b.b right inner joi n c on a.a=c.c inner join d on a.a=d.d where .12、說明：日程安排提前五分鐘提醒select * from 日程安排 where datediff('minute',f開始時(shí)間,getdate()>513、說明：一條sql語句搞定數(shù)據(jù)庫分頁select top 10 b.* from (select top 20 主鍵字段，排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段

9、=a.主鍵字段orderbya.排序字段14、說明：前10條記錄select top 10 * form table1 where 范圍15、說明：選擇在每一組 b值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜，每月熱銷產(chǎn)品分析，按科目成績排名，等等.)select a,b,c from tablename ta where a=(select max(a) from table name tb where tb.b=ta.b)16、說明：包括所有在 TableA 中但不在 TableB和TableC中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表(select a

10、from tableA except (select a from tableB) excep t (select a from tableC)17、說明：隨機(jī)取出10條數(shù)據(jù)select top 10 * from tablename order by newid()18、說明：隨機(jī)選擇記錄select newid()19、說明：刪除重復(fù)記錄Delete from tablename where id not in (select max(id) from tablename group by col1,col2, )20、說明：列出數(shù)據(jù)庫里所有的表名selectnamefromsysobje

11、ctswheretype='U'21、說明：列出表里的所有的selectnamefromsyscolumnswhereid=object_id('TableName')22、說明：列示type、vender > pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn) 多重選擇，類似 select 中的case。selecttype,sum( case venderwhen 'A'thenpcs else 0end),sum(case vender when'C' then pcs else0 end),sum(caseven

12、der when'B' then pcs else 0 end)FROM tablename group by type顯示結(jié)果：typevenderpcs電腦A1電腦A1光盤B2光盤A2手機(jī)B3手機(jī)C323、說明：初始化表table1TRUNCATE TABLE table124、說明：選擇從10到15的記錄select top 5* from (selecttop15*fromtableorderby id asc) table 另U名order by id desc三、技巧1、1=1, 1=2的使用，在SQL語句組合時(shí)用的較多"where 1=1”是表示選擇全部

13、"where 1=2"全部不選，如： if strWhere !=' beginset strSQL = 'select count(*) as Total from '+ tblName + ' where '+ strWhereend elsebegin set strSQL = 'select count(*) as Total fro m '+ tblName + ''end我們可以直接寫成set strSQL = 'select count(*) as Total from '

14、+ tblName + ' where 1=1 安定'+ strWhere2、收縮數(shù)據(jù)庫-重建索引 DBCC REINDEX DBCC INDEXDEFRAG -收縮數(shù)據(jù)和日志 DBCC SHRINKDB DBCC SHRINKFILE3、壓縮數(shù)據(jù)庫 dbcc shrinkdatabase(dbname)4、轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限 exec sp_change_users_login 'update_one','newname','oldname' go5、檢查備份集 RESTORE VERIFYONLY from d

15、isk='E:dvbbs.bak'6、修復(fù)數(shù)據(jù)庫Alter DATABASE dvbbs SET SINGLE_USER GO DBCC CHECKDB('dvbbs',repair_allow_data_loss)WITH TABLOCKGO Alter DATABASE dvbbs SET MULTI_USER GO7、日志清除SET NOCOUNTONDECLARE Logical sysname, MaxMinutes INT, NewSize INTUSEtablename-要操作的數(shù)據(jù)庫名Select Logical = 'tablename

16、_log',- 日志文件名MaxMinutes = 10,- Limit on time allowed to wrap log. NewSize = 1- 你想設(shè)定的日志文件的大小(M)- Setup / initializeDECLARE OriginalSize intSelect OriginalSize = sizeFROM sysfilesWhere name = LogicalSelect 'Original Size of '+ db_name() + ' LOG is '+CONVERT(VARCHAR(30),OriginalSize

17、) + ' 8K pages o r '+CONVERT(VARCHAR(30),(OriginalSize*8/1024) + 'MB'FROM sysfilesWhere name = LogicalCreate TABLE DummyTrans (DummyColumn char (8000) not null )DECLARE Counter INT, StartTime DATETIME, TruncLog VARCHAR(255) Select StartTime = GETDATE(), TruncLog = 'BACKUP LOG 

18、9;+ db_name() + ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (Logical, NewSize) EXEC (TruncLog) - Wrap the log if necessary. WHILEMaxMinutes > DATEDIFF (mi, StartTime, GETDATE()- time has not expiredAND OriginalSize = (Select size FROM sysfiles Wher e name = Logical)AND (OriginalSize * 8 /1024)&g

19、t; NewSizeBEGIN - Outer loop. Select Counter = 0 WHILE (Counter < OriginalSize /16) AND (Counter < 50000)BEGIN - updateInsert DummyTrans VALUES ('Fill Log')Delete DummyTransSelect Counter = Counter + 1ENDEXEC (TruncLog) END Select 'Final Size of '+ db_name() + ' LOG is '

20、;+CONVERT(VARCHAR(30),size) + ' 8K pages or '+CONVERT(VARCHAR(30),(size*8/1024) + 'MB' FROM sysfiles Where name = LogicalDrop TABLE DummyTransSET NOCOUNTOFF8、說明：更改某個(gè)表exec sp_changeobjectowner 'tablename','dbo'9、存儲(chǔ)更改全部表Create PROCEDUREdbo.User_ChangeObjectOwnerBatch OldOwner as NVARCHAR(128), NewOwner as NVARCHAR(128) AS