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

1、數(shù)據(jù)庫根本SQL語句大全數(shù)據(jù)庫根本-SQL語句大全、根底1、說明：創(chuàng)立數(shù)據(jù)庫Create DATABASE database-name2、說明：刪除數(shù)據(jù)庫drop databasedbn ame3、說明：備份 sql server- 創(chuàng)立備份數(shù)據(jù)的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'- 開始備份BACKUP DATABASE pubs TO testBack4、說明：創(chuàng)立新表create tabletabnam

2、e(col1type1 not null primary key,col2 type2 notnull ,.)根據(jù)已有的表創(chuàng)立新表：A：createtabletab_newlike tab_old (使用舊表創(chuàng)立新表)B：createtabletab_newasselect col1,col2 from tab_olddefinition only5、說明：刪除新表drop tabletab name6、說明：增加一個列Alter table tab name add colu mn col type注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類

3、型的長度。7、 說明：添加主鍵： Alter tabletab name add primary key(col)說明：刪除主鍵：Alter tabletab name drop primary key(col)8、 說明：創(chuàng)立索弓丨：createuniqueindexidxnameon tabname(col .)刪除索弓丨：drop in dexidx name注：索引是不可更改的，想更改必須刪除重新建。9、 說明：創(chuàng)立視圖：create viewview name as select stateme nt二、提升1說明：復(fù)制表只復(fù)制結(jié)構(gòu)，源表名：a新表名：b Access可用法一： se

4、lect * into b from a where 1<>1法二：select top 0 * in tob from a2、 說明：拷貝表拷貝數(shù)據(jù),源表名：a目標(biāo)表名：b Access可用insertin toba,b,cselectd,e,ffromb;3、 說明：跨數(shù)據(jù)庫之間表的拷貝具體數(shù)據(jù)使用絕對路徑Access可用insertintoba,b,cselectd,e,ffrombin '具體數(shù)據(jù)庫where 條件例子：.from b in'"&Server.MapPath".' "data.mdb"&

5、amp;"' where.4、說明：子查詢表名1： a 表名2: bselect a,b,c from a where a IN select d from b 或者： select a,b,c from a where a IN 1,2,35、說明：顯示文章、提交人和最后回復(fù)時間selecta.title,a.user name,b.adddatefrom table a,select maxadddate adddate from table where table.title=a.titleb6、說明：外連接查詢 表名1： a 表名2： bselect a.a, a.b

6、, a.c, b.c, b.d, b.f from 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ù)范圍時包括了邊界值,notbetween不包括select * from table1 where timebetwee ntime1 and time2select a,b,c, from table1 where a notbetween 數(shù)值 1 and 數(shù)值 29、說明：in的使用

7、方法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 * from a left innerjoi nb on a.a=b.b right inner join c on a.a=c.cinnerjoi

8、 nd on a.a=d.d where12、說明：日程安排提前五分鐘提醒select * from 日程安排 where datediff('minute',f開始時間，getdate()>13、 說明：一條sql語句搞定數(shù)據(jù)庫分頁select top 10 b.* from (select top 20 主鍵字段,排序字段from 表名 order by 排序字段desc) a,表名 b where b.主鍵字段 =a.主鍵字段 order by a.排序字段14、說明：前10條記錄select top 10 * form table1 where 范圍15、 說明：

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

10、說明：隨機取岀10條數(shù)據(jù)select top 10 * fromtable nameorder by n ewid()18、說明：隨機選擇記錄select n ewid()19、說明：刪除重復(fù)記錄Delete fromtable namewhere id not in (select max(id) from tablename group by col1,col2, )20、說明：列岀數(shù)據(jù)庫里所有的表名selectnamefromsysobjectswheretype='U'21、說明：列岀表里的所有的selectnamefromsyscolumnswhereid=objec

11、t_id('TableName')22、 說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現(xiàn) 多重選擇，類似 select 中的case。select type,sum( case venderwhen 'Athenpcs else 0 end),sum( case vender when 'C' then pcs else0 end),sum( case vender when 'B' then pcs else 0 end)FROM table namegroup by type顯示結(jié)果：type

12、 ven der pcs電腦 A 1電腦 A 1光盤 B 2光盤 A 2 B 3 C 323、說明：初始化表 table1TRUNCATE TABLE table124、說明：選擇從 10到15的記錄select top 5 * from(selecttop 15 * from table order by id asc) table 另V名 orderby id desc三、技巧1、1=1 , 1=2的使用，在SQL語句組合時用的較多“ where 1=1是表示選擇全部“ where1=2 全部不選，如：if strWhere !='beginset strSQL = 'se

13、lect cou nt(*) as Total from '+ tblName +' where '+ strWhereendelsebeginset strSQL = 'selectcou nt(*)as Total from '+ tblName +''end我們可以直接寫成set strSQL = 'select cou nt(*) as Total from '+ tblName +' where 1=1 安定 '+ strWhere2、收縮數(shù)據(jù)庫-重建索引DBCC REINDEXDBCC INDE

14、XDEFRAG-收縮數(shù)據(jù)和日志DBCC SHRINKDBDBCC SHRINKFILE3、壓縮數(shù)據(jù)庫dbcc shri nkdatabase(db name)4、轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限exec sp_cha nge_users_logi n'update_ on e',' newn ame','old name'go5、檢查備份集RESTORE VERIFYONLY from disk='E:dvbbs.bak'6、修復(fù)數(shù)據(jù)庫Alter DATABASE dvbbs SET SINGLE_USERGODBCC CHEC

15、KDB('dvbbs',repair_allow_data_loss)WITH TABLOCKGOAlter DATABASE dvbbs SET MULTI_USERGO7、日志去除SET NOCOUNTONDECLARE LogicalFileName sys name,MaxMi nutes INT,NewSize INTUSEtable name-要操作的數(shù)據(jù)庫名SelectLogicalFileName = 'table name_log',-日志文件名MaxMi nutes =10,- Limit on time allowed to wrap lo

16、g.NewSize =1- 你想設(shè)定的日志文件的大小(M)- Setup / in itializeDECLARE Origi nalSize intSelectOrigi nalSize= sizeFROM sysfilesWhere n ame = LogicalFileNameSelect 'Original Size of '+ db_name() +' LOG is '+CONVERT(VARCHAR(30),Orig in alSize)+ ' 8K pages oCONVERT(VARCHAR(30),(Origi nalSize*8/10

17、24)+ 'MB'FROM sysfilesWhere n ame = LogicalFileNameCreate TABLE DummyTrans(DummyColumn char (8000) not null )DECLARE Cou nter INT,StartTime DATETIME,Tru ncLogVARCHAR(255)SelectStartTime = GETDATE(),TruncLog = 'BACKUP LOG '+ db_name()+ ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (Log

18、icalFileName, NewSize)EXEC (TruncLog)- Wrap the log if n ecessary.WHILEMaxMinutes > DATEDIFF (mi, StartTime, GETDATE()- time has not expiredAND OriginalSize= (Select size FROM sysfiles Where n ame = LogicalFileName)AND (OriginalSize *8/1024)> NewSizeBEGIN - Outer loop.SelectCou nter = 0WHILE (

19、Counter < OriginalSize/16) AND (Counter <50000)BEGIN - updateInsertDummyTrans VALUES ('Fill Log')DeleteDummyTra nsSelect Cou nter = Cou nter +1ENDEXEC (TruncLog)ENDSelect 'Final Size of '+ db_name() +' LOG is '+CONVERT(VARCHAR(30),size) +' 8K pages or '+CONVERT(

20、VARCHAR(30),(size*8/1024) + 'MB'FROM sysfilesWhere n ame = LogicalFileNameDrop TABLE DummyTransSET NOCOUNTOFF8說明：更改某個表exec sp_cha ngeobjectow ner'table name','dbo'9、存儲更改全部表CreatePROCEDUREdbo.User_Cha ngeObjectOw nerBatchOldOwner as NVARCHAR(128),NewOw ner as NVARCHAR(128)ASDECLA