我SQL常用腳本整理（下）
11、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é)果，這里我們返回空白(是字符類(lèi)型，不是NULL)，在不為0的時(shí)候就給出具體的計(jì)算公式，然后轉(zhuǎn)換成字符類(lèi)型再和“%”進(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é)果：
12、四舍五入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)代碼)
13、對(duì)字段出現(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('字符串類(lèi)型字段','N') AS 'NewName'
--結(jié)果：
字符串類(lèi)型字段
SELECT COALESCE(CONVERT(VARCHAR(20),'非字符串類(lèi)型字段'),'N') AS 'NewName'
--結(jié)果：
非字符串類(lèi)型字段
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é)果：
11114、COUNT的幾種情況
--以下三種方法均可統(tǒng)計(jì)出表的記錄數(shù)
--第一種
select count(*) from tablename
--第二種
select count(ID) from tablename
--第三種,1換成其它值也是可以的
select count(1) from tablename15、UNION ALL多表插入
把兩個(gè)相同結(jié)構(gòu)的表union后插入到一個(gè)新表中，
當(dāng)然兩個(gè)以上的相同結(jié)構(gòu)的表也是可以的，
這里的相同是指兩個(gè)或多個(gè)表的列數(shù)和每個(gè)對(duì)應(yīng)列的類(lèi)型相同，
列名稱(chēng)可以不同
select *
into table_new
from table_1
union all
select * from table_216、查看數(shù)據(jù)庫(kù)緩存的SQL
use master
declare @dbid int
Select @dbid = dbid from sysdatabases
where name = 'SQL_ROAD'--修改成數(shù)據(jù)庫(kù)的名稱(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我們可以看到數(shù)據(jù)庫(kù)中當(dāng)前正在運(yùn)行的SQL有哪些
17、刪除計(jì)劃緩存
--刪除整個(gè)數(shù)據(jù)庫(kù)的計(jì)劃緩存
DBCC FREEPROCCACHE
--刪除某個(gè)數(shù)據(jù)庫(kù)的計(jì)劃緩存
USE master
DECLARE @dbid INT
SELECT @dbid=dbid FROM sysdatabases WHERE NAME = 'SQL_ROAD'
DBCC FLUSHPROCINDB (@dbid)制表符 CHAR(9)
換行符 CHAR(10)
回車(chē) CHAR(13)
PRINT 'SQL'+CHAR(13)+'ROAD'
PRINT 'SQL'+CHAR(10)+'ROAD'
PRINT 'SQL'+CHAR(9)+'ROAD'
如果將查詢(xún)結(jié)果以文本格式顯示，而不是網(wǎng)格格式顯示，SELECT語(yǔ)句也適用，我們先將查詢(xún)結(jié)果改成以文本格式顯示
--以文本格式顯示結(jié)果
SELECT 'SQL'+ CHAR(10)+'ROAD'
SELECT 'SQL'+ CHAR(13)+'ROAD'
SELECT 'SQL' + CHAR(10) + CHAR(13) + 'ROAD'結(jié)果如下：
19、TRUNCATE 與 DELETE
TRUNCATE 是SQL中的一個(gè)刪除數(shù)據(jù)表內(nèi)容的語(yǔ)句，用法是：
TRUNCATE TABLE [Table Name] 速度快,而且效率高,因?yàn)??
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語(yǔ)句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。?
DELETE 語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過(guò)釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。?
TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。
如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用 DELETE。
如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 DROP TABLE 語(yǔ)句。?
對(duì)于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語(yǔ)句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。TRUNCATE TABLE 不能用于參與了索引視圖的表。?
20、常用系統(tǒng)檢測(cè)腳本
--查看內(nèi)存狀態(tài)
dbcc memorystatus
--查看哪個(gè)引起的阻塞，blk
EXEC sp_who active
--查看鎖住了那個(gè)資源id，objid
EXEC sp_lock還有如何查看查詢(xún)分析器的SPID，可以在查詢(xún)分析器的狀態(tài)欄看到，比如sa(57),這就表示當(dāng)前查詢(xún)分析器SPID為57,這樣在使用profile的時(shí)候就可以指定當(dāng)前窗體進(jìn)行監(jiān)控。狀態(tài)欄在查詢(xún)窗口的右下角。
21、獲取腳本的執(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í)行效率。
以上就是今天分享給大家一些比較實(shí)用的SQL腳本，以后想到了其他內(nèi)容再分享給大家，好東西記得分享轉(zhuǎn)發(fā)呀~
推薦閱讀
歡迎長(zhǎng)按掃碼關(guān)注「數(shù)據(jù)管道」