极其实用的sql脚本【建议收藏】
一、基礎(chǔ)部分
?
1、創(chuàng)建數(shù)據(jù)庫
CREATE?DATABASE?dbname?
2、刪除數(shù)據(jù)庫
DROP?DATABASE?dbname?
3、創(chuàng)建新表
CREATE?TABLE?tabname( col1 type1 [not?null] [primary?key], col2 type2 [not?null],.. )?
根據(jù)已有的表創(chuàng)建新表：?
使用舊表創(chuàng)建新表 create?table?tab_new as? select? col1, col2… from?tab_old?
4、刪除新表
DROP?TABLE?tablename?
5、增加一個(gè)列
Alter?table?tabname?add?column?col?type?
6、添加主鍵：
Alter?table?tabname?add?primary?key(col)?
刪除主鍵：
Alter?table?tabname?drop?primary?key(col)?
7、創(chuàng)建索引：
create?[unique]?index?idxname?on?tabname(col….)?
刪除索引：
drop?index?idxname注：索引是不可更改的，想更改必須刪除重新建。
?
8、創(chuàng)建視圖：
create?view?viewname?as?select?statement?
刪除視圖：
drop?view?viewname?
9、幾個(gè)簡單的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%’--排序： select?*?from?table1?order?by?field1,field2 [desc] --總數(shù)： 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?
10、幾個(gè)高級查詢運(yùn)算詞
A：UNION 運(yùn)算符?
UNION 運(yùn)算符通過組合其他兩個(gè)結(jié)果表，并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。?
B：EXCEPT 運(yùn)算符?
EXCEPT運(yùn)算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí)?(EXCEPT ALL)，不消除重復(fù)行。
?
C：INTERSECT 運(yùn)算符
INTERSECT運(yùn)算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng)?ALL隨 INTERSECT 一起使用時(shí)?(INTERSECT ALL)，不消除重復(fù)行。?
注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。?
11、使用外連接?
A、left （outer） join：?
左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。?
?
B：right （outer） join
右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。?
C：full/cross （outer） join：?
全外連接：不僅包括符號連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。
?
12、Group by
對列進(jìn)行分組，常與聚合函數(shù)(count,sum,max,min,avg?)一起使用
注意：
-
在分組時(shí)：不能以text,ntext,image類型的字段作為分組依據(jù)
-
在select統(tǒng)計(jì)函數(shù)中的字段，不能和普通的字段放在一起；
?
?
二、進(jìn)階部分
?
1、復(fù)制表(只復(fù)制表結(jié)構(gòu),源表名：a 新表名：b)
--方法一 僅用于SQL Server： select?*?into?b?from?a?where?1<>1 --方法二： select?top?0?*?into?b?from?a?
2、拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)?
insert?into?b(a, b, c) select?d,e,f?from?b;?
?
3、子查詢(表名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)?
4、顯示文章、提交人和最后回復(fù)時(shí)間
select? a.title, a.username, b.adddate from?table?a, (select?max(adddate) adddate from?table? where?table.title=a.title) b?
5、外連接查詢(表名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?
6、在線視圖查詢(表名1：a )
select?*?from?( SELECT?a,b,c?FROM?a ) T where?t.a >?1;?
7、between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括
select?*?from?table1 where?time?between?time1?and?time2select?a,b,c,?from?table1 where?a?not?between?數(shù)值1?and?數(shù)值2?
8、in 的使用方法
select?*?from?table1 where?a [not]?in?(‘值1’,’值2’,’值4’,’值6’)?
9、兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息?
delete?from?table1 where?not?exists?( select?*?from?table2 where?table1.field1=table2.field1 )?
10、四表聯(liá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?on?a.a=d.d where?.....
11、日程安排提前五分鐘提醒?
select?*?from?日程安排 where?datediff('minute',f開始時(shí)間,getdate())>5?
12、一條sql 語句搞定數(shù)據(jù)庫分頁
select?top?10?b.* from?( select?top?20?主鍵字段,排序字段 from?表名?order?by?排序字段?desc ) a, 表名 b where?b.主鍵字段 = a.主鍵字段 order?by?a.排序字段具體?
實(shí)現(xiàn)：關(guān)于數(shù)據(jù)庫分頁：
?
declare?@start?int,@end?int @sql??nvarchar(600) set?@sql=’select?top’+str(@end-@start+1)+’+from?T where?rid?not?in( select?top’+str(@str-1)+’Rid?from?T?where?Rid>-1)’ exec sp_executesql @sql?
?
13、前10條記錄
select?top?10?* form?table1 where?范圍?
?
14、包括所有在?TableA中但不在?TableB和TableC中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表
(select?a?from?tableA ) except? (select?a?from?tableB) except? (select?a?from?tableC)?
15、隨機(jī)取出10條數(shù)據(jù)
select?top?10?* from?tablename order?by?newid()?
?
16、說明：刪除重復(fù)記錄
--方法一 delete?from?tablename?where?id?not?in?(select?max(id)?from?tablename?group?by?col1,col2,...) --方法二 select?distinct?*?into?temp?from?tablename delete?from?tablename insert?into?tablename?select?*?from?temp?
評價(jià)：這種操作牽連大量的數(shù)據(jù)的移動，這種做法不適合大容量但數(shù)據(jù)操作3),例如：在一個(gè)外部表中導(dǎo)入數(shù)據(jù)，由于某些原因第一次只導(dǎo)入了一部分，但很難判斷具體位置，這樣只有在下一次全部導(dǎo)入，這樣也就產(chǎn)生好多重復(fù)的字段，怎樣刪除重復(fù)字段
alter?table?tablename --添加一個(gè)自增列 add??column_b?int?identity(1,1) delete?from?tablename where?column_b?not?in( select?max(column_b) from?tablename group?by?column1,column2,... ) alter?table?tablename?drop?column?column_b?
17、列出數(shù)據(jù)庫里所有的表名
use?master go select?name?from?sysobjects where?type='U'?// U代表用戶?
18、列出表里的所有的列名
use?master go select?name? from?syscolumns where?id=object_id('TableName')?
?
19、初始化表table1
TRUNCATE?TABLE?table1?
20、選擇從10到15的記錄
select?top?5?* from?( select?top?15?* from?table? order?by?id?asc ) table_別名 order?by?id?desc?
?
三、開發(fā)技巧
?
1、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、收縮數(shù)據(jù)庫
--重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收縮數(shù)據(jù)和日志 DBCC SHRINKDB DBCC SHRINKFILE?
3、壓縮數(shù)據(jù)庫
dbcc?shrinkdatabase(dbname)?
4、轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限
exec sp_change_users_login?'update_one','newname','oldname' go?
5、檢查備份集
RESTORE?VERIFYONLY?from?disk='E:\dvbbs.bak'?
6、修復(fù)數(shù)據(jù)庫
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?INTUSE?tablename?-- 要操作的數(shù)據(jù)庫名 SELECT??@LogicalFileName =?'tablename_log',?-- 日志文件名 @MaxMinutes =?10,?-- Limit on time allowed to wrap log. @NewSize =?1??-- 你想設(shè)定的日志文件的大小(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?sysfilesWHERE?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 expiredAND?@OriginalSize = (SELECT?size?FROM?sysfiles?WHERE?name?= @LogicalFileName)AND?(@OriginalSize *?8?/1024) > @NewSizeBEGIN?-- Outer loop. SELECT?@Counter =?0WHILE???((@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 '?+CONVERT(VARCHAR(30),size) +?' 8K pages or '?+CONVERT(VARCHAR(30),(size*8/1024)) +?'MB'FROM?sysfilesWHERE?name?= @LogicalFileName DROP?TABLE?DummyTrans SET?NOCOUNT?OFF?
8、更改某個(gè)表
exec?sp_changeobjectowner?'tablename','dbo'?
9、存儲更改全部表
CREATE?PROCEDURE?dbo.User_ChangeObjectOwnerBatch @OldOwner?as?NVARCHAR(128), @NewOwner?as?NVARCHAR(128) ASDECLARE?@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?nameOPEN???curObject FETCH?NEXT?FROM?curObject?INTO?@Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN????? if?@Owner=@OldOwner beginset?@OwnerName = @OldOwner +?'.'?+?rtrim(@Name)exec sp_changeobjectowner @OwnerName, @NewOwner end -- select @name,@NewOwner,@OldOwnerFETCH?NEXT?FROM?curObject?INTO?@Name, @Owner ENDclose?curObject deallocate?curObject GO?
10、SQL SERVER中直接循環(huán)寫入數(shù)據(jù)
declare?@i?int set?@i=1 while?@i<30 begininsert?into?test?(userid)?values(@i)set?@i=@i+1 end案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎(chǔ)上，使他們剛好及格:
Name? ? ? ? score
Zhangshan? ?80
Lishi? ? ? ? ? ? ? 59
Wangwu? ? ? ?50
Songquan? ? ?69
?
while((select?min(score)?from?tb_table)<60) begin update?tb_table?set?score =score*1.01 where?score<60 if??(select?min(score)?from?tb_table)>60breakelsecontinue end轉(zhuǎn)自：SQL數(shù)據(jù)庫開發(fā)?，作者丶平凡世界?
總結(jié)
以上是生活随笔為你收集整理的极其实用的sql脚本【建议收藏】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 每日两SQL（7），欢迎交流~
- 下一篇： 你真正的去了解过Linux吗？不单单是一