SQL 常用腳本大(dà)全
1、行轉列的(de)用法PIVOT
CREATE table test
(id int,name nvarchar(20),quarter int,number int)
insert into test values(1,N'蘋果',1,1000)
insert into test values(1,N'蘋果',2,2000)
insert into test values(1,N'蘋果',3,4000)
insert into test values(1,N'蘋果',4,5000)
insert into test values(2,N'梨子(zǐ)',1,3000)
insert into test values(2,N'梨子(zǐ)',2,3500)
insert into test values(2,N'梨子(zǐ)',3,4200)
insert into test values(2,N'梨子(zǐ)',4,5500)
select * from test
結果：
select ID,NAME,
[1] as '一(yī / yì ／yí)季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
from
test
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt
結果：
2、列轉行的(de)用法UNPIOVT
create table test2
(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,'蘋果',1000,2000,4000,5000)
insert into test2 values(2,'梨子(zǐ)',3000,3500,4200,5500)
select * from test2
結果：
--列轉行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
結果：
3、字符串替換SUBSTRING/REPLACE
SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
結果：
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
結果：
SELECT REPLACE('12345678@qq.com','1234567','******')
結果：
4、查詢一(yī / yì ／yí)個(gè)表内相同紀錄 HAVING
如果一(yī / yì ／yí)個(gè)ID可以(yǐ)區分的(de)話，可以(yǐ)這(zhè)麽寫
SELECT * FROM HR.Employees
結果：
select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)
結果：
對比一(yī / yì ／yí)下發現，ID爲(wéi / wèi)1,2的(de)被過濾掉了(le／liǎo)，因爲(wéi / wèi)他(tā)們隻有一(yī / yì ／yí)條記錄
如果幾個(gè)ID才能區分的(de)話，可以(yǐ)這(zhè)麽寫
select * from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
group by title,titleofcourtesy
having count(1)>1)
結果：
title在(zài)和(hé / huò)titleofcourtesy進行拼接後符合條件的(de)就(jiù)隻有ID爲(wéi / wèi)6,7,8,9的(de)了(le／liǎo)
5、把多行SQL數據變成一(yī / yì ／yí)條多列數據，即新增列
SELECT
id,
name,
SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一(yī / yì ／yí)季度',
SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度',
SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'
FROM test
GROUP BY id,name
結果：
我們将原來(lái)的(de)4列增加到(dào)了(le／liǎo)6列。細心的(de)朋友可能發現了(le／liǎo)這(zhè)個(gè)結果和(hé / huò)上(shàng)面的(de)行轉列怎麽一(yī / yì ／yí)模一(yī / yì ／yí)樣？其實上(shàng)面的(de)行轉列是(shì)省略寫法，這(zhè)種是(shì)比較通用的(de)寫法。
6、表複制
語法1：Insert INTO table(field1,field2,...) values(value1,value2,...)
語法2：Insert into Table2(field1,field2,...) select value1,value2,... from
Table1
（要(yào / yāo)求目标表Table2必須存在(zài)，由于(yú)目标表Table2已經存在(zài)，所以(yǐ)我們除了(le／liǎo)插入源表Table1的(de)字段外，還可以(yǐ)插入常量。）
語法3：SELECT vale1, value2 into Table2 from Table1
（要(yào / yāo)求目标表Table2不(bù)存在(zài)，因爲(wéi / wèi)在(zài)插入時(shí)會自動創建表Table2，并将Table1中指定字段數據複制到(dào)Table2中。）
語法4：使用導入導出(chū)功能進行全表複制。如果是(shì)使用【編寫查詢以(yǐ)指定要(yào / yāo)傳輸的(de)數據】，那麽在(zài)大(dà)數據表的(de)複制就(jiù)會有問題？因爲(wéi / wèi)複制到(dào)一(yī / yì ／yí)定程度就(jiù)不(bù)再動了(le／liǎo)，内存爆了(le／liǎo)？它也(yě)沒有寫入到(dào)表中。而(ér)使用上(shàng)面3種語法直接執行是(shì)會馬上(shàng)刷新到(dào)數據庫表中的(de)，你刷新一(yī / yì ／yí)下mdf文件就(jiù)知道(dào)了(le／liǎo)。
7、利用帶關聯子(zǐ)查詢Update語句更新數據
--方法1：
Update Table1
set c = (select c from Table2 where a = Table1.a)
where c is null
--方法2：
update A
set newqiantity=B.qiantity
from A,B
where A.bnum=B.bnum
--方法3：
update
(select A.bnum ,A.newqiantity,B.qiantity from A
left join B on A.bnum=B.bnum) AS C
set C.newqiantity = C.qiantity
where C.bnum ='001'
8、連接遠程服務器
--方法1：
select * from openrowset(
'SQLOLEDB',
'server=192.168.0.1;uid=sa;pwd=password',
'SELECT * FROM dbo.test')
--方法2：
select * from openrowset(
'SQLOLEDB',
'192.168.0.1';
'sa';
'password',
'SELECT * FROM dbo.test')
當然也(yě)可以(yǐ)參考以(yǐ)前的(de)示例，建立DBLINK進行遠程連接
9、Date 和(hé / huò) Time 樣式 CONVERT
CONVERT() 函數是(shì)把日期轉換爲(wéi / wèi)新數據類型的(de)通用函數。
CONVERT() 函數可以(yǐ)用不(bù)同的(de)格式顯示日期/時(shí)間數據。
語法
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 規定目标數據類型（帶有可選的(de)長度）。data_to_be_converted 含有需要(yào / yāo)轉換的(de)值。style 規定日期/時(shí)間的(de)輸出(chū)格式。
可以(yǐ)使用的(de) style 值：
| Style ID | Style 格式 |
|---|---|
| 100 或者 0 | mon dd yyyy hh:miAM （或者 PM） |
| 101 | mm/dd/yy |
| 102 | yy.mm.dd |
| 103 | dd/mm/yy |
| 104 | dd.mm.yy |
| 105 | dd-mm-yy |
| 106 | dd mon yy |
| 107 | Mon dd, yy |
| 108 | hh:mm:ss |
| 109 或者 9 | mon dd yyyy hh:mi:ss:mmmAM（或者 PM） |
| 110 | mm-dd-yy |
| 111 | yy/mm/dd |
| 112 | yymmdd |
| 113 或者 13 | dd mon yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 120 或者 20 | yyyy-mm-dd hh:mi:ss(24h) |
| 121 或者 21 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-ddThh:mm:ss.mmm（沒有空格） |
| 130 | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yy hh:mi:ss:mmmAM |
SELECT CONVERT(varchar(100), GETDATE(), 0)
--結果：
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 1)
--結果：
12/07/20
SELECT CONVERT(varchar(100), GETDATE(), 2)
--結果：
20.12.07
SELECT CONVERT(varchar(100), GETDATE(), 3)
--結果：
07/12/20
SELECT CONVERT(varchar(100), GETDATE(), 4)
--結果：
07.12.20
SELECT CONVERT(varchar(100), GETDATE(), 5)
--結果：
07-12-20
SELECT CONVERT(varchar(100), GETDATE(), 6)
--結果：
07 12 20
SELECT CONVERT(varchar(100), GETDATE(), 7)
--結果：
12 07, 20
SELECT CONVERT(varchar(100), GETDATE(), 8)
--結果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 9)
--結果：
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 10)
--結果：
12-07-20
SELECT CONVERT(varchar(100), GETDATE(), 11)
--結果：
20/12/07
SELECT CONVERT(varchar(100), GETDATE(), 12)
--結果：
201207
SELECT CONVERT(varchar(100), GETDATE(), 13)
--結果：
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 14)
--結果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 20)
--結果：
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 21)
--結果：
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 22)
--結果：
12/07/20 9:33:18 PM
SELECT CONVERT(varchar(100), GETDATE(), 23)
--結果：
2020-12-07
SELECT CONVERT(varchar(100), GETDATE(), 24)
--結果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 25)
--結果：
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 100)
--結果：
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 101)
--結果：
12/07/2020
SELECT CONVERT(varchar(100), GETDATE(), 102)
--結果：
2020.12.07
SELECT CONVERT(varchar(100), GETDATE(), 103)
--結果：
07/12/2020
SELECT CONVERT(varchar(100), GETDATE(), 104)
--結果：
07.12.2020
SELECT CONVERT(varchar(100), GETDATE(), 105)
--結果：
07-12-2020
SELECT CONVERT(varchar(100), GETDATE(), 106)
--結果：
07 12 2020
SELECT CONVERT(varchar(100), GETDATE(), 107)
--結果：
12 07, 2020
SELECT CONVERT(varchar(100), GETDATE(), 108)
--結果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 109)
--結果：
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 110)
--結果：
12-07-2020
SELECT CONVERT(varchar(100), GETDATE(), 111)
--結果：
2020/12/07
SELECT CONVERT(varchar(100), GETDATE(), 112)
--結果：
20201207
SELECT CONVERT(varchar(100), GETDATE(), 113)
--結果：
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 114)
--結果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 120)
--結果：
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 121)
--結果：
2020-12-07 21:33:18.780
10、SQL中的(de)相除
方法一(yī / yì ／yí)
--SQL中的(de)相除
SELECT
CASE WHEN ISNULL(A-B,0)=0 THEN ''
ELSE CAST(CONVERT(DECIMAL(18,2),A*100.0/(A-B)) AS VARCHAR(10))+'%'
END AS '百分數' --FROM 表
這(zhè)裏我們先要(yào / yāo)判斷被除數是(shì)否爲(wéi / wèi)0，如果爲(wéi / wèi)0給出(chū)一(yī / yì ／yí)個(gè)想輸出(chū)的(de)結果，這(zhè)裏我們返回空白(是(shì)字符類型，不(bù)是(shì)NULL)，在(zài)不(bù)爲(wéi / wèi)0的(de)時(shí)候就(jiù)給出(chū)具體的(de)計算公式，然後轉換成字符類型再和(hé / huò)“%”進行拼接。例如：
SELECT
CASE WHEN ISNULL(5-2,0)=0 THEN ''
ELSE CAST(CONVERT(DECIMAL(18,2),5*100.0/(5-2)) AS VARCHAR(10))+'%'
END AS '百分數' --FROM 表
返回的(de)結果：
方法二
SELECT
(CONVERT(VARCHAR(20),ROUND(41*100.0/88,3))+'%') AS '百分比'
--FROM A
執行結果：
11、四舍五入ROUND函數
ROUND ( numeric_expression , length [ ,function ] )
function 必須爲(wéi / wèi) tinyint、smallint 或 int。
如果省略 function 或其值爲(wéi / wèi) 0（默認值），則将舍入 numeric_expression。
如果指定了(le／liǎo)0以(yǐ)外的(de)值，則将截斷 numeric_expression。
SELECT ROUND(150.45648, 2);
--保留小數點後兩位，需要(yào / yāo)四舍五入
--結果：
150.46000
SELECT ROUND(150.45648, 2, 0);
--保留小數點後兩位，0爲(wéi / wèi)默認值，表示進行四舍五入
--結果：
150.46000
SELECT ROUND(150.45648, 2, 1);
--保留小數點後兩位，不(bù)需要(yào / yāo)四舍五入，這(zhè)裏除0以(yǐ)外都是(shì)有同樣的(de)效果，
--與Oracle的(de)TRUNC函數效果相同
--結果：
150.45000
SELECT ROUND(150.45648, 2, 2);
--保留小數點後兩位，不(bù)需要(yào / yāo)四舍五入，這(zhè)裏除0以(yǐ)外都是(shì)有同樣的(de)效果，
--與Oracle的(de)TRUNC函數效果相同
--結果：
150.45000
12、對字段出(chū)現NULL值的(de)處理
方法一(yī / yì ／yí)
--CASE
SELECT
CASE WHEN '字段名' IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(20),'字段名1') END
AS 'NewName'
--結果：
字段名1
SELECT CASE WHEN NULL IS NULL THEN 'N'
ELSE CONVERT(VARCHAR(20),NULL) END
AS 'NewName'
--結果：
N
方法二
--SQL Server 2005：COALESCE
SELECT COALESCE('字符串類型字段','N') AS 'NewName'
--結果：
字符串類型字段
SELECT COALESCE(CONVERT(VARCHAR(20),'非字符串類型字段'),'N') AS 'NewName'
--結果：
非字符串類型字段
SELECT COALESCE(CONVERT(VARCHAR(20),NULL),'N') AS 'NewName'
--結果：
N
--COALESCE,返回其參數中的(de)第一(yī / yì ／yí)個(gè)非空表達式
SELECT COALESCE(NULL,NULL,1,2,NULL)
--結果：
1
SELECT COALESCE(NULL,11,12,13,NULL)
--結果：
11
SELECT COALESCE(111,112,113,114,NULL)
--結果：
111
13、COUNT的(de)幾種情況
--以(yǐ)下三種方法均可統計出(chū)表的(de)記錄數
--第一(yī / yì ／yí)種
select count(*) from tablename
--第二種
select count(ID) from tablename
--第三種,1換成其它值也(yě)是(shì)可以(yǐ)的(de)
select count(1) from tablename
14、UNION ALL多表插入
把兩個(gè)相同結構的(de)表union後插入到(dào)一(yī / yì ／yí)個(gè)新表中，
當然兩個(gè)以(yǐ)上(shàng)的(de)相同結構的(de)表也(yě)是(shì)可以(yǐ)的(de)，
這(zhè)裏的(de)相同是(shì)指兩個(gè)或多個(gè)表的(de)列數和(hé / huò)每個(gè)對應列的(de)類型相同，
列名稱可以(yǐ)不(bù)同
select *
into table_new
from table_1
union all
select * from table_2
15、查看數據庫緩存的(de)SQL
use master
declare @dbid int
Select @dbid = dbid from sysdatabases
where name = 'SQL_ROAD'--修改成數據庫的(de)名稱
select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name(dbid) as DatabaseName,SQL
from syscacheobjects
where dbid=@dbid
order by dbid,useCounts desc,objtype
我們可以(yǐ)看到(dào)數據庫中當前正在(zài)運行的(de)SQL有哪些
16、删除計劃緩存
--删除整個(gè)數據庫的(de)計劃緩存
DBCC FREEPROCCACHE
--删除某個(gè)數據庫的(de)計劃緩存
USE master
DECLARE @dbid INT
SELECT @dbid=dbid FROM sysdatabases WHERE NAME = 'SQL_ROAD'
DBCC FLUSHPROCINDB (@dbid)
制表符 CHAR(9)
換行符 CHAR(10)
回車 CHAR(13)PRINT 'SQL'+CHAR(13)+'ROAD'
PRINT 'SQL'+CHAR(10)+'ROAD'
PRINT 'SQL'+CHAR(9)+'ROAD'
如果将查詢結果以(yǐ)文本格式顯示，而(ér)不(bù)是(shì)網格格式顯示，SELECT語句也(yě)适用，我們先将查詢結果改成以(yǐ)文本格式顯示
--以(yǐ)文本格式顯示結果
SELECT 'SQL'+ CHAR(10)+'ROAD'
SELECT 'SQL'+ CHAR(13)+'ROAD'
SELECT 'SQL' + CHAR(10) + CHAR(13) + 'ROAD'
結果如下：
18、TRUNCATE 與 DELETE
TRUNCATE 是(shì)SQL中的(de)一(yī / yì ／yí)個(gè)删除數據表内容的(de)語句，用法是(shì)：
TRUNCATE TABLE [Table Name] 速度快,而(ér)且效率高,因爲(wéi / wèi):
TRUNCATE TABLE 在(zài)功能上(shàng)與不(bù)帶 WHERE 子(zǐ)句的(de) DELETE 語句相同：二者均删除表中的(de)全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的(de)系統和(hé / huò)事務日志資源少。
DELETE 語句每次删除一(yī / yì ／yí)行，并在(zài)事務日志中爲(wéi / wèi)所删除的(de)每行記錄一(yī / yì ／yí)項。TRUNCATE TABLE 通過釋放存儲表數據所用的(de)數據頁來(lái)删除數據，并且隻在(zài)事務日志中記錄頁的(de)釋放。
TRUNCATE TABLE 删除表中的(de)所有行，但表結構及其列、約束、索引等保持不(bù)變。新行标識所用的(de)計數值重置爲(wéi / wèi)該列的(de)種子(zǐ)。
如果想保留标識計數值，請改用 DELETE。
如果要(yào / yāo)删除表定義及其數據，請使用 DROP TABLE 語句。
對于(yú)由 FOREIGN KEY 約束引用的(de)表，不(bù)能使用 TRUNCATE TABLE，而(ér)應使用不(bù)帶 WHERE 子(zǐ)句的(de) DELETE 語句。由于(yú) TRUNCATE TABLE 不(bù)記錄在(zài)日志中，所以(yǐ)它不(bù)能激活觸發器。TRUNCATE TABLE 不(bù)能用于(yú)參與了(le／liǎo)索引視圖的(de)表。
19、常用系統檢測腳本
--查看内存狀态
dbcc memorystatus
--查看哪個(gè)引起的(de)阻塞，blk
EXEC sp_who active
--查看鎖住了(le／liǎo)那個(gè)資源id，objid
EXEC sp_lock
還有如何查看查詢分析器的(de)SPID，可以(yǐ)在(zài)查詢分析器的(de)狀态欄看到(dào)，比如sa(57),這(zhè)就(jiù)表示當前查詢分析器SPID爲(wéi / wèi)57,這(zhè)樣在(zài)使用profile的(de)時(shí)候就(jiù)可以(yǐ)指定當前窗體進行監控。狀态欄在(zài)查詢窗口的(de)右下角。
20、獲取腳本的(de)執行時(shí)間
declare @timediff datetime
select @timediff=getdate()
select * from Suppliers
print '耗時(shí):'+ convert(varchar(10),datediff(ms,@timediff,getdate()))
結果如下：
在(zài)狀态欄是(shì)不(bù)會精确到(dào)毫秒的(de)，隻能精确到(dào)秒
這(zhè)個(gè)腳本可以(yǐ)更加有效的(de)查看SQL代碼的(de)執行效率。