天天看點
傳回

SQL 常用腳本大全

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

結果：

SQL 常用腳本大全
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

結果：

SQL 常用腳本大全

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

結果：

SQL 常用腳本大全
--列轉行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

結果：

SQL 常用腳本大全

3、字元串替換SUBSTRING/REPLACE

SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')

結果：

SQL 常用腳本大全
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')

結果：

SQL 常用腳本大全
SELECT REPLACE('[email protected]','1234567','******')

結果：

SQL 常用腳本大全

4、查詢一個表内相同記錄 HAVING

如果一個ID可以區分的話，可以這麼寫

SELECT * FROM HR.Employees

結果：

SQL 常用腳本大全
select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)

結果：

SQL 常用腳本大全

對比一下發現，ID為1,2的被過濾掉了，因為他們隻有一條記錄

如果幾個ID才能區分的話，可以這麼寫

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

結果：

SQL 常用腳本大全

title在和titleofcourtesy進行拼接後符合條件的就隻有ID為6,7,8,9的了

5、把多行SQL資料變成一條多列資料，即新增列

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

結果：

SQL 常用腳本大全

我們将原來的4列增加到了6列。細心的朋友可能發現了這個結果和上面的行轉列怎麼一模一樣？其實上面的行轉列是省略寫法，這種是比較通用的寫法。

6、表複制

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

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

Table1

（要求目标表Table2必須存在，由于目标表Table2已經存在，是以我們除了插入源表Table1的字段外，還可以插入常量。）

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

（要求目标表Table2不存在，因為在插入時會自動建立表Table2，并将Table1中指定字段資料複制到Table2中。）

文法4：使用導入導出功能進行全表複制。如果是使用【編寫查詢以指定要傳輸的資料】，那麼在大資料表的複制就會有問題？因為複制到一定程度就不再動了，記憶體爆了？它也沒有寫入到表中。而使用上面3種文法直接執行是會馬上重新整理到資料庫表中的，你重新整理一下mdf檔案就知道了。

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進行遠端連接配接

9、Date 和 Time 樣式 CONVERT

CONVERT() 函數是把日期轉換為新資料類型的通用函數。

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

文法

CONVERT(data_type(length),data_to_be_converted,style)

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

可以使用的 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中的相除

方法一

--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 表

這裡我們先要判斷被除數是否為0，如果為0給出一個想輸出的結果，這裡我們傳回空白(是字元類型，不是NULL)，在不為0的時候就給出具體的計算公式，然後轉換成字元類型再和“%”進行拼接。例如：

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 表

傳回的結果：

SQL 常用腳本大全

方法二

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

執行結果：

SQL 常用腳本大全

11、四舍五入ROUND函數

ROUND ( numeric_expression , length [ ,function ] )

function 必須為 tinyint、smallint 或 int。

如果省略 function 或其值為 0（預設值），則将舍入 numeric_expression。

如果指定了0以外的值，則将截斷 numeric_expression。

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

SELECT ROUND(150.45648, 2, 0);
--保留小數點後兩位，0為預設值，表示進行四舍五入
--結果：
150.46000

SELECT ROUND(150.45648, 2, 1);
--保留小數點後兩位，不需要四舍五入，這裡除0以外都是有同樣的效果，
--與Oracle的TRUNC函數效果相同
--結果：
150.45000

SELECT ROUND(150.45648, 2, 2);
--保留小數點後兩位，不需要四舍五入，這裡除0以外都是有同樣的效果，
--與Oracle的TRUNC函數效果相同
--結果：
150.45000

12、對字段出現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的幾種情況

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

--第二種
select count(ID) from tablename

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

14、UNION ALL多表插入

把兩個相同結構的表union後插入到一個新表中，

當然兩個以上的相同結構的表也是可以的，

這裡的相同是指兩個或多個表的列數和每個對應列的類型相同，

列名稱可以不同

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'--修改成資料庫的名稱

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'

執行結果：

SQL 常用腳本大全

如果将查詢結果以文本格式顯示，而不是網格格式顯示，SELECT語句也适用，我們先将查詢結果改成以文本格式顯示

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

結果如下：

SQL 常用腳本大全

18、TRUNCATE 與 DELETE

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

TRUNCATE TABLE [Table Name] 速度快,而且效率高,因為:

TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。

DELETE 語句每次删除一行，并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。

TRUNCATE TABLE 删除表中的所有行，但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。

如果想保留辨別計數值，請改用 DELETE。

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

對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器。TRUNCATE TABLE 不能用于參與了索引視圖的表。

19、常用系統檢測腳本

--檢視記憶體狀态
dbcc memorystatus

--檢視哪個引起的阻塞，blk
EXEC sp_who active

--檢視鎖住了那個資源id，objid
EXEC sp_lock

還有如何檢視查詢分析器的SPID，可以在查詢分析器的狀态欄看到，比如sa(57),這就表示目前查詢分析器SPID為57,這樣在使用profile的時候就可以指定目前窗體進行監控。狀态欄在查詢視窗的右下角。

SQL 常用腳本大全

20、擷取腳本的執行時間

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

結果如下：

SQL 常用腳本大全

在狀态欄是不會精确到毫秒的，隻能精确到秒

SQL 常用腳本大全

這個腳本可以更加有效的檢視SQL代碼的執行效率。

來源：數倉寶貝庫

連結：

https://mp.weixin.qq.com/s/V4WkmA_A_Y8xUrrkuvl0sg

常用腳本
上一篇: 【UML】UML模組化
下一篇: 一台有藝術加持的智能手機，努比亞Z40SPro星空典藏版釋出，還真挺好看的。努比亞Z40SPro星空典藏版在硬體方面和常

繼續閱讀