...

SQL 常用腳本大全

2022-03-21

1、行轉列的用法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'梨子',1,3000)
insert into test values(2,N'梨子',2,3500)
insert into test values(2,N'梨子',3,4200)
insert into test values(2,N'梨子',4,5500)
select * from test


結果：

圖片


select ID,NAME,
[1] as '一季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
from
test
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt


結果：

圖片


2、列轉行的用法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,'梨子',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、查詢一個表内相同紀錄 HAVING


如果一個ID可以區分的話，可以這(zhè)麼(me)寫


SELECT * FROM HR.Employees


結果：


圖片


select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)


結果：


圖片


對(duì)比一下發(fā)現，ID爲1,2的被(bèi)過(guò)濾掉了，因爲他們隻有一條記錄


如果幾個ID才能(néng)區分的話，可以這(zhè)麼(me)寫


select * from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
group by title,titleofcourtesy
having count(1)>1)


結果：


圖片


title在和titleofcourtesy進(jìn)行拼接後(hòu)符合條件的就(jiù)隻有ID爲6,7,8,9的了


5、把多行SQL數據變成(chéng)一條多列數據，即新增列


SELECT 
 id,
 name,
 SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度',
 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


結果：


圖片


我們將(jiāng)原來的4列增加到了6列。細心的朋友可能(néng)發(fā)現了這(zhè)個結果和上面(miàn)的行轉列怎麼(me)一模一樣(yàng)？其實上面(miàn)的行轉列是省略寫法，這(zhè)種(zhǒng)是比較通用的寫法。 


6、表複制


語法1：Insert INTO table(field1,field2,...) values(value1,value2,...)

語法2：Insert into Table2(field1,field2,...) select value1,value2,... from 

Table1

（要求目标表Table2必須存在，由于目标表Table2已經(jīng)存在，所以我們除了插入源表Table1的字段外，還(hái)可以插入常量。）

語法3：SELECT vale1, value2 into Table2 from Table1

（要求目标表Table2不存在，因爲在插入時(shí)會(huì)自動創建表Table2，并將(jiāng)Table1中指定字段數據複制到Table2中。）

語法4：使用導入導出功能(néng)進(jìn)行全表複制。如果是使用【編寫查詢以指定要傳輸的數據】，那麼(me)在大數據表的複制就(jiù)會(huì)有問題？因爲複制到一定程度就(jiù)不再動了，内存爆了？它也沒(méi)有寫入到表中。而使用上面(miàn)3種(zhǒng)語法直接執行是會(huì)馬上刷新到數據庫表中的，你刷新一下mdf文件就(jiù)知道(dào)了。


7、利用帶關聯子查詢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')


當然也可以參考以前的示例，建立DBLINK進(jìn)行遠程連接


9、Date 和 Time 樣(yàng)式 CONVERT


CONVERT() 函數是把日期轉換爲新數據類型的通用函數。

CONVERT() 函數可以用不同的格式顯示日期/時(shí)間數據。


語法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 規定目标數據類型（帶有可選的長(cháng)度）。data_to_be_converted 含有需要轉換的值。style 規定日期/時(shí)間的輸出格式。


可以使用的 style 值：


Style IDStyle 格式
100 或者 0mon dd yyyy hh:miAM （或者 PM）
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109 或者 9mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 或者 13dd mon yyyy hh:mm:ss:mmm(24h)
114hh:mi:ss:mmm(24h)
120 或者 20yyyy-mm-dd hh:mi:ss(24h)
121 或者 21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mm:ss.mmm（沒(méi)有空格）
130dd mon yyyy hh:mi:ss:mmmAM
131dd/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中的相除


方法一


--SQL中的相除
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è)裡(lǐ)我們先要判斷被(bèi)除數是否爲0，如果爲0給出一個想輸出的結果，這(zhè)裡(lǐ)我們返回空白(是字符類型，不是NULL)，在不爲0的時(shí)候就(jiù)給出具體的計算公式，然後(hòu)轉換成(chéng)字符類型再和“%”進(jìn)行拼接。例如：


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 表


返回的結果：

圖片


方法二


SELECT 
(CONVERT(VARCHAR(20),ROUND(41*100.0/88,3))+'%') AS '百分比' 
--FROM A


執行結果：

圖片




11、四舍五入ROUND函數


ROUND ( numeric_expression , length [ ,function ] )
function 必須爲 tinyint、smallint  或 int。
如果省略 function 或其值爲 0（默認值），則將(jiāng)舍入 numeric_expression。
如果指定了0以外的值，則將(jiāng)截斷 numeric_expression。


SELECT ROUND(150.45648, 2);
--保留小數點後(hòu)兩(liǎng)位，需要四舍五入
--結果：
150.46000

SELECT ROUND(150.45648, 2, 0);
--保留小數點後(hòu)兩(liǎng)位，0爲默認值，表示進(jìn)行四舍五入
--結果：
150.46000

SELECT ROUND(150.45648, 2, 1);
--保留小數點後(hòu)兩(liǎng)位，不需要四舍五入，這(zhè)裡(lǐ)除0以外都(dōu)是有同樣(yàng)的效果，
--與Oracle的TRUNC函數效果相同
--結果：
150.45000

SELECT ROUND(150.45648, 2, 2);
--保留小數點後(hòu)兩(liǎng)位，不需要四舍五入，這(zhè)裡(lǐ)除0以外都(dōu)是有同樣(yàng)的效果，
--與Oracle的TRUNC函數效果相同
--結果：
150.45000


12、對(duì)字段出現NULL值的處理


方法一


--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,返回其參數中的第一個非空表達式
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的幾種(zhǒng)情況


--以下三種(zhǒng)方法均可統計出表的記錄數
--第一種(zhǒng)
select count(*) from tablename

--第二種(zhǒng)
select count(ID) from tablename

--第三種(zhǒng),1換成(chéng)其它值也是可以的
select count(1) from tablename


14、UNION ALL多表插入


把兩(liǎng)個相同結構的表union後(hòu)插入到一個新表中，
當然兩(liǎng)個以上的相同結構的表也是可以的，
這(zhè)裡(lǐ)的相同是指兩(liǎng)個或多個表的列數和每個對(duì)應列的類型相同，
列名稱可以不同


select *
into table_new
from table_1
union all
select * from table_2


15、查看數據庫緩存的SQL


use master
declare @dbid int
Select @dbid = dbid from sysdatabases
where name = 'SQL_ROAD'--修改成(chéng)數據庫的名稱

select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name(dbid) as DatabaseName,SQL
from syscacheobjects
where dbid=@dbid
order by dbid,useCounts desc,objtype


我們可以看到數據庫中當前正在運行的SQL有哪些


16、删除計劃緩存


--删除整個數據庫的計劃緩存
DBCC FREEPROCCACHE

--删除某個數據庫的計劃緩存
USE master
DECLARE @dbid INT
SELECT @dbid=dbid FROM sysdatabases WHERE NAME = 'SQL_ROAD'
DBCC FLUSHPROCINDB (@dbid)

17、SQL換行

SQL的換行
制表符 CHAR(9)
換行符 CHAR(10)
回車 CHAR(13)

PRINT 'SQL'+CHAR(13)+'ROAD'
PRINT 'SQL'+CHAR(10)+'ROAD'
PRINT 'SQL'+CHAR(9)+'ROAD'

執行結果：

圖片


如果將(jiāng)查詢結果以文本格式顯示，而不是網格格式顯示，SELECT語句也适用，我們先將(jiāng)查詢結果改成(chéng)以文本格式顯示


圖片


--以文本格式顯示結果
SELECT 'SQL'+ CHAR(10)+'ROAD'
SELECT 'SQL'+ CHAR(13)+'ROAD'
SELECT 'SQL' + CHAR(10) + CHAR(13) + 'ROAD'


結果如下：

圖片



18、TRUNCATE 與 DELETE


TRUNCATE 是SQL中的一個删除數據表内容的語句，用法是：

TRUNCATE TABLE [Table Name] 速度快,而且效率高,因爲: 
TRUNCATE TABLE 在功能(néng)上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事(shì)務日志資源少。 


DELETE 語句每次删除一行，并在事(shì)務日志中爲所删除的每行記錄一項。TRUNCATE TABLE 通過(guò)釋放存儲表數據所用的數據頁來删除數據，并且隻在事(shì)務日志中記錄頁的釋放。

 
TRUNCATE TABLE 删除表中的所有行，但表結構及其列、約束、索引等保持不變。新行标識所用的計數值重置爲該列的種(zhǒng)子。


如果想保留标識計數值，請改用 DELETE。


如果要删除表定義及其數據，請使用 DROP TABLE 語句。

 
對(duì)于由 FOREIGN KEY 約束引用的表，不能(néng)使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能(néng)激活觸發(fā)器。TRUNCATE TABLE 不能(néng)用于參與了索引視圖的表。 


19、常用系統檢測腳本


--查看内存狀态
dbcc memorystatus

--查看哪個引起(qǐ)的阻塞，blk
EXEC sp_who active

--查看鎖住了那個資源id，objid
EXEC sp_lock


還(hái)有如何查看查詢分析器的SPID，可以在查詢分析器的狀态欄看到，比如sa(57),這(zhè)就(jiù)表示當前查詢分析器SPID爲57,這(zhè)樣(yàng)在使用profile的時(shí)候就(jiù)可以指定當前窗體進(jìn)行監控。狀态欄在查詢窗口的右下角。


圖片


20、獲取腳本的執行時(shí)間


declare @timediff datetime
select @timediff=getdate()
select * from Suppliers
print '耗時(shí):'+ convert(varchar(10),datediff(ms,@timediff,getdate()))


結果如下：

圖片

在狀态欄是不會(huì)精确到毫秒的，隻能(néng)精确到秒


圖片


這(zhè)個腳本可以更加有效的查看SQL代碼的執行效率。


來源：數倉寶貝庫