生活随笔
收集整理的這篇文章主要介紹了
精妙SQL语句收集(转)
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
SQL語句先前寫的時候，很容易把一些特殊的用法忘記，我特此整理了一下SQL語句操作。??一、基礎??1、說明：創建數據庫??CREATE?DATABASE?database-name??2、說明：刪除數據庫??drop?database?dbname??3、說明：備份sql?server??—?創建?備份數據的?device??USE?master??EXEC?sp_addumpdevice?‘disk’,?‘testBack’,?‘c:/mssql7backup/MyNwind_1.dat’??—?開始?備份??BACKUP?DATABASE?pubs?TO?testBack??4、說明：創建新表??create?table?tabname(col1?type1?[not?null]?[primary?key],col2?type2?[not?null],..)??根據已有的表創建新表：??A：create?table?tab_new?like?tab_old?(使用舊表創建新表)??B：create?table?tab_new?as?select?col1,col2…?from?tab_old?definition?only??5、說明：刪除新表??drop?table?tabname??6、說明：增加一個列??Alter?table?tabname?add?column?col?type??注：列增加后將不能刪除。DB2中列加上后數據類型也不能改變，唯一能改變的是增加varchar類型的長度。??7、說明：添加主鍵：?Alter?table?tabname?add?primary?key(col)??說明：刪除主鍵：?Alter?table?tabname?drop?primary?key(col)??8、說明：創建索引：create?[unique]?index?idxname?on?tabname(col….)??刪除索引：drop?index?idxname??注：索引是不可更改的，想更改必須刪除重新建。??9、說明：創建視圖：create?view?viewname?as?select?statement??刪除視圖：drop?view?viewname??10、說明：幾個簡單的基本的sql語句??選擇：select?*?from?table1?where?范圍??插入：insert?into?table1(field1,field2)?values(value1,value2)??刪除：delete?from?table1?where?范圍??更新：update?table1?set?field1=value1?where?范圍??查找：select?*?from?table1?where?field1?like?‘%value1%’?—like的語法很精妙，查資料!??排序：select?*?from?table1?order?by?field1,field2?[desc]??總數：select?count?as?totalcount?from?table1??求和：select?sum(field1)?as?sumvalue?from?table1??平均：select?avg(field1)?as?avgvalue?from?table1??最大：select?max(field1)?as?maxvalue?from?table1??最小：select?min(field1)?as?minvalue?from?table1??11、說明：幾個高級查詢運算詞??A：?UNION?運算符??UNION?運算符通過組合其他兩個結果表（例如?TABLE1?和?TABLE2）并消去表中任何重復行而派生出一個結果表。當?ALL?隨?UNION?一起使用時（即?UNION?ALL），不消除重復行。兩種情況下，派生表的每一行不是來自?TABLE1?就是來自?TABLE2。??B：?EXCEPT?運算符??EXCEPT?運算符通過包括所有在?TABLE1?中但不在?TABLE2?中的行并消除所有重復行而派生出一個結果表。當?ALL?隨?EXCEPT?一起使用時?(EXCEPT?ALL)，不消除重復行。??C：?INTERSECT?運算符??INTERSECT?運算符通過只包括?TABLE1?和?TABLE2?中都有的行并消除所有重復行而派生出一個結果表。當?ALL?隨?INTERSECT?一起使用時?(INTERSECT?ALL)，不消除重復行。??注：使用運算詞的幾個查詢結果行必須是一致的。??12、說明：使用外連接??A、left?outer?join：??左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。??SQL:?select?a.a,?a.b,?a.c,?b.c,?b.d,?b.f?from?a?LEFT?OUT?JOIN?b?ON?a.a?=?b.c??B：right?outer?join:??右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。??C：full?outer?join：??全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。??二、提升??1、說明：復制表(只復制結構,源表名：a?新表名：b)?(Access可用)??法一：select?*?into?b?from?a?where?1<>1??法二：select?top?0?*?into?b?from?a??2、說明：拷貝表(拷貝數據,源表名：a?目標表名：b)?(Access可用)??insert?into?b(a,?b,?c)?select?d,e,f?from?b;??3、說明：跨數據庫之間表的拷貝(具體數據使用絕對路徑)?(Access可用)??insert?into?b(a,?b,?c)?select?d,e,f?from?b?in?‘具體數據庫’?where?條件??例子：..from?b?in?‘”&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、說明：顯示文章、提交人和最后回復時間??select?a.title,a.username,b.adddate?from?table?a,(select?max(adddate)?adddate?from?table?where?table.title=a.title)?b??6、說明：外連接查詢(表名1：a?表名2：b)??select?a.a,?a.b,?a.c,?b.c,?b.d,?b.f?from?a?LEFT?OUT?JOIN?b?ON?a.a?=?b.c??7、說明：在線視圖查詢(表名1：a?)??select?*?from?(SELECT?a,b,c?FROM?a)?T?where?t.a?>?1;??8、說明：between的用法,between限制查詢數據范圍時包括了邊界值,not?between不包括??select?*?from?table1?where?time?between?time1?and?time2??select?a,b,c,?from?table1?where?a?not?between?數值1?and?數值2??9、說明：in?的使用方法??select?*?from?table1?where?a?[not]?in?(‘值1′,’值2′,’值4′,’值6’)??10、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息??delete?from?table1?where?not?exists?(?select?*?from?table2?where?table1.field1=table2.field1?)??11、說明：四表聯查問題：??select?*?from?a?left?inner?join?b?on?a.a=b.b?right?inner?join?c?on?a.a=c.c?inner?join?d?on?a.a=d.d?where?…..??12、說明：日程安排提前五分鐘提醒??SQL:?select?*?from?日程安排?where?datediff(‘minute’,f開始時間,getdate())>5??13、說明：一條sql?語句搞定數據庫分頁??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、說明：選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)??select?a,b,c?from?tablename?ta?where?a=(select?max(a)?from?tablename?tb?where?tb.b=ta.b)??16、說明：包括所有在?TableA?中但不在?TableB和TableC?中的行并消除所有重復行而派生出一個結果表??(select?a?from?tableA?)?except?(select?a?from?tableB)?except?(select?a?from?tableC)??17、說明：隨機取出10條數據??select?top?10?*?from?tablename?order?by?newid()??18、說明：隨機選擇記錄??select?newid()??19、說明：刪除重復記錄??Delete?from?tablename?where?id?not?in?(select?max(id)?from?tablename?group?by?col1,col2,…)??20、說明：列出數據庫里所有的表名??select?name?from?sysobjects?where?type=‘U’??21、說明：列出表里的所有的??select?name?from?syscolumns?where?id=object_id(‘TableName’)??22、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現多重選擇，類似select?中的case。??select?type,sum(case?vender?when?‘A’?then?pcs?else?0?end),sum(case?vender?when?‘C’?then?pcs?else?0?end),sum(case?vender?when?‘B’?then?pcs?else?0?end)?FROM?tablename?group?by?type??顯示結果：??type?vender?pcs??電腦?A?1??電腦?A?1??光盤?B?2??光盤?A?2??手機?B?3??手機?C?3??23、說明：初始化表table1??TRUNCATE?TABLE?table1??24、說明：選擇從10到15的記錄??select?top?5?*?from?(select?top?15?*?from?table?order?by?id?asc)?table_別名?order?by?id?desc??三、技巧??1、1=1，1=2的使用，在SQL語句組合時用的較多??“where?1=1″?是表示選擇全部???“where?1=2“全部不選，??如：??if?@strWhere?!=”??begin??set?@strSQL?=?‘select?count(*)?as?Total?from?[‘?+?@tblName?+?‘]?where?‘?+?@strWhere??end??else??begin??set?@strSQL?=?‘select?count(*)?as?Total?from?[‘?+?@tblName?+?‘]’??end??我們可以直接寫成??set?@strSQL?=?‘select?count(*)?as?Total?from?[‘?+?@tblName?+?‘]?where?1=1?安定?‘+?@strWhere??2、收縮數據庫??–重建索引??DBCC?REINDEX??DBCC?INDEXDEFRAG??–收縮數據和日志??DBCC?SHRINKDB??DBCC?SHRINKFILE??3、壓縮數據庫??dbcc?shrinkdatabase(dbname)??4、轉移數據庫給新用戶以已存在用戶權限??exec?sp_change_users_login?‘update_one’,’newname’,’oldname’??go??5、檢查備份集??RESTORE?VERIFYONLY?from?disk=‘E:/dvbbs.bak’??6、修復數據庫??ALTER?DATABASE?[dvbbs]?SET?SINGLE_USER??GO??DBCC?CHECKDB(‘dvbbs’,repair_allow_data_loss)?WITH?TABLOCK??GO??ALTER?DATABASE?[dvbbs]?SET?MULTI_USER??GO??7、日志清除??SET?NOCOUNT?ON??DECLARE?@LogicalFileName?sysname,??????????@MaxMinutes?INT,??????????@NewSize?INT????USE?????tablename?????????????—?要操作的數據庫名??SELECT??@LogicalFileName?=?‘tablename_log’,??—?日志文件名??@MaxMinutes?=?10,???????????????—?Limit?on?time?allowed?to?wrap?log.??????????@NewSize?=?1??????????????????—?你想設定的日志文件的大小(M)??—?Setup?/?initialize??DECLARE?@OriginalSize?int??SELECT?@OriginalSize?=?size????FROM?sysfiles????WHERE?name?=?@LogicalFileName??SELECT?‘Original?Size?of?‘?+?db_name()?+?‘?LOG?is?‘?+??????????CONVERT(VARCHAR(30),@OriginalSize)?+?‘?8K?pages?or?‘?+??????????CONVERT(VARCHAR(30),(@OriginalSize*8/1024))?+?‘MB’????FROM?sysfiles????WHERE?name?=?@LogicalFileName??CREATE?TABLE?DummyTrans????(DummyColumn?char?(8000)?not?null)????DECLARE?@Counter???INT,??????????@StartTime?DATETIME,??????????@TruncLog??VARCHAR(255)??SELECT??@StartTime?=?GETDATE(),??????????@TruncLog?=?‘BACKUP?LOG?‘?+?db_name()?+?‘?WITH?TRUNCATE_ONLY’??DBCC?SHRINKFILE?(@LogicalFileName,?@NewSize)??EXEC?(@TruncLog)??—?Wrap?the?log?if?necessary.??WHILE?????@MaxMinutes?>?DATEDIFF?(mi,?@StartTime,?GETDATE())?—?time?has?not?expired????????AND?@OriginalSize?=?(SELECT?size?FROM?sysfiles?WHERE?name?=?@LogicalFileName)?????????AND?(@OriginalSize?*?8?/1024)?>?@NewSize?????BEGIN?—?Outer?loop.??????SELECT?@Counter?=?0??????WHILE??((@Counter?<?@OriginalSize?/?16)?AND?(@Counter?<?50000))????????BEGIN?—?update??????????INSERT?DummyTrans?VALUES?(‘Fill?Log’)???????????DELETE?DummyTrans??????????SELECT?@Counter?=?@Counter?+?1????????END????????EXEC?(@TruncLog)?????END????SELECT?‘Final?Size?of?‘?+?db_name()?+?‘?LOG?is?‘?+??????????CONVERT(VARCHAR(30),size)?+?‘?8K?pages?or?‘?+??????????CONVERT(VARCHAR(30),(size*8/1024))?+?‘MB’????FROM?sysfiles????WHERE?name?=?@LogicalFileName??DROP?TABLE?DummyTrans??SET?NOCOUNT?OFF??8、說明：更改某個表??exec?sp_changeobjectowner?‘tablename’,’dbo’??9、存儲更改全部表??CREATE?PROCEDURE?dbo.User_ChangeObjectOwnerBatch???@OldOwner?as?NVARCHAR(128),???@NewOwner?as?NVARCHAR(128)??AS??DECLARE?@Name???as?NVARCHAR(128)??DECLARE?@Owner??as?NVARCHAR(128)??DECLARE?@OwnerName??as?NVARCHAR(128)??DECLARE?curObject?CURSOR?FOR???select?‘Name’???=?name,????‘Owner’???=?user_name(uid)???from?sysobjects???where?user_name(uid)=@OldOwner???order?by?name??OPEN??curObject??FETCH?NEXT?FROM?curObject?INTO?@Name,?@Owner??WHILE(@@FETCH_STATUS=0)??BEGIN???????if?@Owner=@OldOwner???begin????set?@OwnerName?=?@OldOwner?+?‘.’?+?rtrim(@Name)????exec?sp_changeobjectowner?@OwnerName,?@NewOwner???end??—?select?@name,@NewOwner,@OldOwner???FETCH?NEXT?FROM?curObject?INTO?@Name,?@Owner??END??close?curObject??deallocate?curObject??GO????10、SQL?SERVER中直接循環寫入數據??declare?@i?int??set?@i=1??while?@i<30??begin?????insert?into?test?(userid)?values(@i)?????set?@i=@i+1??end??小記存儲過程中經常用到的本周，本月，本年函數??Dateadd(wk,datediff(wk,0,getdate()),-1)??Dateadd(wk,datediff(wk,0,getdate()),6)??Dateadd(mm,datediff(mm,0,getdate()),0)??Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))??Dateadd(yy,datediff(yy,0,getdate()),0)??Dateadd(ms,-3,DATEADD(yy,?DATEDIFF(yy,0,getdate())+1,?0))??上面的SQL代碼只是一個時間段??Dateadd(wk,datediff(wk,0,getdate()),-1)??Dateadd(wk,datediff(wk,0,getdate()),6)??就是表示本周時間段.??下面的SQL的條件部分,就是查詢時間段在本周范圍內的:??Where?Time?BETWEEN?Dateadd(wk,datediff(wk,0,getdate()),-1)?AND?Dateadd(wk,datediff(wk,0,getdate()),6)??而在存儲過程中??select?@begintime?=?Dateadd(wk,datediff(wk,0,getdate()),-1)??select?@endtime?=?Dateadd(wk,datediff(wk,0,getdate()),6) ?
轉載于:https://www.cnblogs.com/royi123/p/5510807.html
創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎
總結
以上是生活随笔為你收集整理的精妙SQL语句收集(转)的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
