SQL（Structured Query Language）是一種用于管理和操作關(guān)系型數(shù)據(jù)庫的語言。對于數(shù)據(jù)庫管理員和開發(fā)人員來說，熟練掌握SQL是非常重要的。在實(shí)際的數(shù)據(jù)庫操作中，有許多常見而且實(shí)用的SQL腳本可以提高操作效率，并且簡化繁瑣的工作。本文將介紹20個(gè)SQL實(shí)用腳本，幫助你更好地利用數(shù)據(jù)庫，優(yōu)化查詢性能，提高工作效率。

1、行轉(zhuǎn)列的用法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

結(jié)果：

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

結(jié)果：

2、列轉(zhuǎn)行的用法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

(提示：可以左右滑動(dòng)代碼)

結(jié)果：

--列轉(zhuǎn)行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

結(jié)果：

3、字符串替換SUBSTRING/REPLACE

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

結(jié)果：

SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')

結(jié)果：

SELECT REPLACE('12345678@qq.com','1234567','******')

結(jié)果：

4、查詢一個(gè)表內(nèi)相同紀(jì)錄 HAVING

如果一個(gè)ID可以區(qū)分的話，可以這么寫

SELECT * FROM HR.Employees

結(jié)果：

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

結(jié)果：

對比一下發(fā)現(xiàn)，ID為1,2的被過濾掉了，因?yàn)樗麄冎挥幸粭l記錄

如果幾個(gè)ID才能區(qū)分的話，可以這么寫

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

結(jié)果：

title在和titleofcourtesy進(jìn)行拼接后符合條件的就只有ID為6,7,8,9的了

5、把多行SQL數(shù)據(jù)變成一條多列數(shù)據(jù)，即新增列

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é)果：

我們將原來的4列增加到了6列。細(xì)心的朋友可能發(fā)現(xiàn)了這個(gè)結(jié)果和上面的行轉(zhuǎn)列怎么一模一樣？其實(shí)上面的行轉(zhuǎn)列是省略寫法，這種是比較通用的寫法。 

6、表復(fù)制

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

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

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

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

（要求目標(biāo)表Table2不存在，因?yàn)樵诓迦霑r(shí)會(huì)自動(dòng)創(chuàng)建表Table2，并將Table1中指定字段數(shù)據(jù)復(fù)制到Table2中。）

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

7、利用帶關(guān)聯(lián)子查詢Update語句更新數(shù)據(jù)

--方法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、連接遠(yuǎn)程服務(wù)器

--方法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')

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

9、Date 和 Time 樣式 CONVERT

CONVERT() 函數(shù)是把日期轉(zhuǎn)換為新數(shù)據(jù)類型的通用函數(shù)。

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

語法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 規(guī)定目標(biāo)數(shù)據(jù)類型（帶有可選的長度）。

data_to_be_converted 含有需要轉(zhuǎn)換的值。

style 規(guī)定日期/時(shí)間的輸出格式。

可以使用的 style 值：

SELECT CONVERT(varchar(100), GETDATE(), 0)
--結(jié)果：
04  23 2024  9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 1)
--結(jié)果：
04/23/24
SELECT CONVERT(varchar(100), GETDATE(), 2)
--結(jié)果：
24.04.23
SELECT CONVERT(varchar(100), GETDATE(), 3)
--結(jié)果：
23/04/24
SELECT CONVERT(varchar(100), GETDATE(), 4)
--結(jié)果：
23.04.24
SELECT CONVERT(varchar(100), GETDATE(), 5)
--結(jié)果：
23-04-24
SELECT CONVERT(varchar(100), GETDATE(), 6)
--結(jié)果：
23 04 24
SELECT CONVERT(varchar(100), GETDATE(), 7)
--結(jié)果：
04 23, 24
SELECT CONVERT(varchar(100), GETDATE(), 8)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 9)
--結(jié)果：
04  23 2024  9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 10)
--結(jié)果：
04-23-24
SELECT CONVERT(varchar(100), GETDATE(), 11)
--結(jié)果：
24/04/23
SELECT CONVERT(varchar(100), GETDATE(), 12)
--結(jié)果：
240423
SELECT CONVERT(varchar(100), GETDATE(), 13)
--結(jié)果：
23 04 2024 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 14)
--結(jié)果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 20)
--結(jié)果：
2024-04-23 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 21)
--結(jié)果：
2024-04-23 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 22)
--結(jié)果：
04/23/24  9:33:18 PM
SELECT CONVERT(varchar(100), GETDATE(), 23)
--結(jié)果：
2024-04-23
SELECT CONVERT(varchar(100), GETDATE(), 24)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 25)
--結(jié)果：
2024-04-23 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 100)
--結(jié)果：
04 23 2024  9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 101)
--結(jié)果：
04/23/2024
SELECT CONVERT(varchar(100), GETDATE(), 102)
--結(jié)果：
2024.04.23
SELECT CONVERT(varchar(100), GETDATE(), 103)
--結(jié)果：
23/04/2024
SELECT CONVERT(varchar(100), GETDATE(), 104)
--結(jié)果：
23.04.2024
SELECT CONVERT(varchar(100), GETDATE(), 105)
--結(jié)果：
23-04-2024
SELECT CONVERT(varchar(100), GETDATE(), 106)
--結(jié)果：
23 04 2024
SELECT CONVERT(varchar(100), GETDATE(), 107)
--結(jié)果：
04 23, 2024
SELECT CONVERT(varchar(100), GETDATE(), 108)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 109)
--結(jié)果：
04 23 2024  9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 110)
--結(jié)果：
04-23-2024
SELECT CONVERT(varchar(100), GETDATE(), 111)
--結(jié)果：
2024/04/23
SELECT CONVERT(varchar(100), GETDATE(), 112)
--結(jié)果：
20240423
SELECT CONVERT(varchar(100), GETDATE(), 113)
--結(jié)果：
23 04 2024 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 114)
--結(jié)果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 120)
--結(jié)果：
2024-04-23 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 121)
--結(jié)果：
2024-04-23 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 '百分?jǐn)?shù)'  --FROM 表

這里我們先要判斷被除數(shù)是否為0，如果為0給出一個(gè)想輸出的結(jié)果，這里我們返回空白(是字符類型，不是NULL)，在不為0的時(shí)候就給出具體的計(jì)算公式，然后轉(zhuǎn)換成字符類型再和“%”進(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 '百分?jǐn)?shù)'  --FROM 表

返回的結(jié)果：

方法二

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

執(zhí)行結(jié)果：

11、四舍五入ROUND函數(shù)

ROUND ( numeric_expression , length [ ,function ] )
function 必須為 tinyint、smallint  或 int。
如果省略 function 或其值為 0（默認(rèn)值），則將舍入 numeric_expression。
如果指定了0以外的值，則將截?cái)?numeric_expression。

SELECT ROUND(150.45648, 2);
--保留小數(shù)點(diǎn)后兩位，需要四舍五入
--結(jié)果：
150.46000

SELECT ROUND(150.45648, 2, 0);
--保留小數(shù)點(diǎn)后兩位，0為默認(rèn)值，表示進(jìn)行四舍五入
--結(jié)果：
150.46000

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

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

(提示：可以左右滑動(dòng)代碼)

12、對字段出現(xiàn)NULL值的處理

方法一

--CASE
SELECT
CASE WHEN  '字段名' IS NULL THEN 'NULL'
ELSE CONVERT(VARCHAR(20),'字段名1') END
AS 'NewName'
--結(jié)果：
字段名1

SELECT CASE WHEN NULL IS NULL THEN 'N'
ELSE CONVERT(VARCHAR(20),NULL) END
AS 'NewName'
--結(jié)果：
N

方法二

--SQL Server 2005：COALESCE
SELECT COALESCE('字符串類型字段','N') AS 'NewName'
--結(jié)果：
字符串類型字段

SELECT COALESCE(CONVERT(VARCHAR(20),'非字符串類型字段'),'N') AS 'NewName'
--結(jié)果：
非字符串類型字段

SELECT COALESCE(CONVERT(VARCHAR(20),NULL),'N') AS 'NewName'
--結(jié)果：
N

--COALESCE,返回其參數(shù)中的第一個(gè)非空表達(dá)式
SELECT COALESCE(NULL,NULL,1,2,NULL)
--結(jié)果：
1

SELECT COALESCE(NULL,11,12,13,NULL)
--結(jié)果：
11

SELECT COALESCE(111,112,113,114,NULL)
--結(jié)果：
111

13、COUNT的幾種情況

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

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

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

14、UNION ALL多表插入

把兩個(gè)相同結(jié)構(gòu)的表union后插入到一個(gè)新表中，
當(dāng)然兩個(gè)以上的相同結(jié)構(gòu)的表也是可以的，
這里的相同是指兩個(gè)或多個(gè)表的列數(shù)和每個(gè)對應(yīng)列的類型相同，
列名稱可以不同

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

15、查看數(shù)據(jù)庫緩存的SQL

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

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

我們可以看到數(shù)據(jù)庫中當(dāng)前正在運(yùn)行的SQL有哪些

16、刪除計(jì)劃緩存

--刪除整個(gè)數(shù)據(jù)庫的計(jì)劃緩存
DBCC FREEPROCCACHE

--刪除某個(gè)數(shù)據(jù)庫的計(jì)劃緩存
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'

執(zhí)行結(jié)果：

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

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

結(jié)果如下：

18、TRUNCATE 與 DELETE

TRUNCATE 是SQL中的一個(gè)刪除數(shù)據(jù)表內(nèi)容的語句，用法是：

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

DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。 

TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計(jì)數(shù)值重置為該列的種子。

如果想保留標(biāo)識計(jì)數(shù)值，請改用 DELETE。

如果要?jiǎng)h除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。 

對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。

由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。TRUNCATE TABLE 不能用于參與了索引視圖的表。 

29、常用系統(tǒng)檢測腳本

--查看內(nèi)存狀態(tài)
dbcc memorystatus

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

--查看鎖住了那個(gè)資源id，objid
EXEC sp_lock

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

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

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

結(jié)果如下：

在狀態(tài)欄是不會(huì)精確到毫秒的，只能精確到秒

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

以上就是20個(gè)SQL實(shí)用腳本的全部介紹了，涵蓋了行轉(zhuǎn)列、列轉(zhuǎn)行、字符串替換、新增列、連接遠(yuǎn)程服務(wù)器等相關(guān)內(nèi)容，希望這些腳本可以助力您高效管理數(shù)據(jù)庫。

重要聲明：本文來自SQL數(shù)據(jù)庫開發(fā)，經(jīng)授權(quán)轉(zhuǎn)載，版權(quán)歸原作者所有，不代表銳成觀點(diǎn)，轉(zhuǎn)載的目的在于傳遞更多知識和信息。