SQL常用脚本大全.docx

SQL常用脚本大全（白金版）导读：作为一名数据人，在工作中有许多比拟常用的SQL脚本，今天也整理总结出来提供给 大家，希望能有所帮助。

1、行转列的用法PIVOT
(id int, name nvarchar(20), quarter int, number int) insert into test values (2, N'梨子'，3, 4200)
(select A. bnum , A. newqiantity, B. qiantity from A left join B on A.bnum=B.bnum) AS Cset C. newqiantity 二 C.
8、连接远程服务器一方法1：
select * from openrowset (server=192. 168. 0. 1;uid=sa;pwd=password',
方法2：
select * from openrowset (
SELECT * FROM dbo. test，)
当然也可以参考以前的例如，建立DBLINK进行远程连接9、Date 和 Time 样式 CONVERT
CONVERT0函数是把日期转换为新数据类型的通用函数。
CONVERT ()函数可以用不同的格式显示日期/时间数据。
语法CONVERT(data_type(length), data_to_be_converted,style)
data_type (length) 规定目标数据类型(带有可选的长度)。
data_to_be_converted含有需要转换的值。style规定日期/时间的输出格式。
可以使用的style值:
Style ID
Style格式

109或者9

109或者9
mon dd yyyy hh : mi : ss : mmmAM （或者 PM）
110
mm-dd-yy
in
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)SELECT CONVERT(varchar(100), GETDATE (),1)
SELECT CONVERT(varchar(100), GETDATE(), 2))7
ELECT CONVERT(varchar(100), GETDATE (), 3)SELECT CONVERT(varchar(100), GETDATE (), 4)

SELECT CONVERT(varchar(100), GETDATE (), 5)
SELECT CONVERT(varchar(100), GETDATE (), 6)SELECT CONVERT(varchar(100), GETDATE(), 7)
SELECT CONVERT(varchar(100), GETDATE (), 8)
SELECT CONVERT(varchar(100), GETDATE (), 9)]
SELECT CONVERT(varchar(100), GETDATE (),10)SELECT CONVERT(varchar(100), GETDATE (),11)
SELECT CONVERT(varchar(100), GETDATE (),12)SELECT CONVERT(varchar(100), GETDATE (),13)
SELECT CONVERT(varchar(100), GETDATE (),14)SELECT CONVERT(varchar(100), GETDATE (), 20)
SELECT CONVERT(varchar(100), GETDATE(), 21)SELECT CONVERT(varchar(100), GETDATE (), 22)
SELECT CONVERT(varchar(100), GETDATE (), 23)SELECT CONVERT(varchar(100), GETDATE (), 24)
・结果:
SELECT CONVERT(varchar(100), GETDATE (),100)SELECT CONVERT(varchar(100), GETDATE(),101)
SELECT CONVERT(varchar(100), GETDATE (),102)SELECT CONVERT(varchar(100), GETDATE (),103)
SELECT CONVERT(varchar(100), GETDATE (),104)SELECT CONVERT(varchar(100), GETDATE (),105)
SELECT CONVERT(varchar(100), GETDATE (),106)07 12 2020
SELECT CONVERT(varchar(100), GETDATE (),107)SELECT CONVERT(varchar(100), GETDATE (),108) |

SELECTCONVERT(varchar(100),GETDATE (),109)
SELECTCONVERT(varchar(100),GETDATE (),117SELECTCONVERT(varchar(100),GETDATE (),111)
SELECTCONVERT(varchar(100),GETDATE (),112)
SELECT CONVERT(varchar(100), GETDATE (),113)
SELECT CONVERT(varchar(100), GETDATE (),114)SELECT CONVERT(varchar(100), GETDATE (),120)
2020-12-07 21:33:18ELECT CONVERT(varchar(100), GETDATE0, 121)
-结果:
2020-12-07 21:33:18,78010、SQL中的相除
方法一SQL中的相限
SELECTCASE WHEN ISNULL(A-B, 0)=0 THEN
ELSE CAST(CONVERT(DECIMAL(18, 2), A*100. 0/(A-B)) AS VARCHAR(IO))+' %'END AS '百分数‘一FROM表
这里我们先要判断被除数是否为0,如果为0给出一个想输出的结果，这里我们返回空白（是字符类型，不是NULL）,在不为0的时候就给出具体的计算公式，然后转
换成字符类型再和“％”进行拼接。例如：
CASE WHEN ISNULL (5-2, 0)=0 THENELSE CAST(CONVERT(DECIMAL(18, 2), 5*100. 0/(5-2)) AS VARCHAR(10))+‘%'
END AS '百分数'一FROM表返回的结果:
剧结果靠消息
百分数
1 I 166. 67%
方法二SELECT (CONVERT (VARCHAR (20), ROUND (41*100. 0/88, 3))+'%') AS '百分比'

执行结果:
H结果解消息百分比
1146.591000%
11、四舍五入ROUND函数ROUND ( numeric_expression , length [ ,function ])
function 必须为 tinyint> smallint 或 into如果省略function或其值为 0 (默认值)，那么将舍入numeric_expression0 如果指定了 0以外的值，那么将截断numeric_expression0
SELECT ROUND(150. 45648, 2);一保存小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果,
一保存小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果,（提示：可以左右滑动代码）
insert into test values ⑵ N‘梨子4, 5500)select * from test
i d name11苹果
1i苹果quarter number
10002000
1苹果1苹果
梨子梨子
梨子梨子
40005000
30003500
42005500
sum(number)

for quarter in
12、对字段出现NULL值的处理方法一
CASE WHEN '字段名'IS NULL THEN 'NULL' |else convert (varchar (20),?
|SELECT CASE WHEN NULL IS NULL THEN ? N? p^^C0NVERnVARCTAR(20hNm^ENI^^^^B方法二
—SQL Server 2005： COALESCESELECT COALESCE ('字符串类型字段‘，'N') AS ' NewName'
字符串类型字段
ELECT COALESCE (CONVERT (VARCHAR (20),J 非字符串类型字段‘)，'N') AS ' NewNanie'SELECT COALESCE(CONVERT(VARCHAR(20), NULL), 'N') AS ' NewName'
-COALESCE,返回其参数中的第一个非空表达五SELECT COALESCE(111, 112, 113, 114, NULL)
no13、COUNT的几种情况
-以下三种方法均可统计出表的记录数select count(*) from tablename, select count (ID) from tablename select count (1) from tabienamel
14、UNION ALL多表插入把两个相同结构的表uni on后插入到一个新表中，
当然两个以上的相同结构的表也是可以的，这里的相同是指两个或多个表的列数和每个对应列的类型相同,
列名称可以不同select * into table_new from table_l
inion al：
select * from table 215、查看数据库缓存的SQL
use master declare @dbid intSelect @dbid = dbid from sysdatabases
here name='SQL_ROAD，--修改成数据库的名称 dbid, UseCounts , RefCounts, CacheObjtype, ObjType,DB Name(dbid) as DatabaseName, SQI from syscacheobjects where dbid=@dbid order by dbid, useCounts desc,objtype
我们可以看到数据库中当前正在运行的SQL有哪些16、删除计划缓存
一删除整个数据库的计划缓存
SELECT @dbid=dbid FROM sysdatabases WHERE NAME = y SQL_ROAD，DBCC FLUSIIPROCINDB (@dbid)
17、SQL换行SQL的换行 制表符CHAR (9) 换行符CHAR (10) 回车 CHAR(13)
PRINT ' SQL' +CHAR (13)+' ROAD'PRINT ' SQL' +CHAR (10) +' ROAD'
执行结果:
SQLROAD
SQLROAD
SQL ROAD如果将查询结果以文本格式显示，而不是网格格式显示，SELECT语句也适用，我们
先将查询结果改成以文本格式显示
执行凶 调试①)■ /器回国 炉跪电4洞］副q 亘？1 w妾■都
-以文本格式显示结杲SELECT ' SQL' + CHAR (10)+' ROAD'
SELECT ' SQL' + CHAR (13)+' ROAD，SELECT 'SQL' + CHAR (10) + CHAR (13) + 'ROAD'
结果如下:
SQLROAD
（1行受影响）SQL
ROAD（1行受影响）
SQL
ROAD （1行受影响）18、 TRUNCATE 与 DELETE
TRUNCATE是SQL中的一个删除数据表内容的语句，用法是：
TRUNCATE TABLE [Table Name]速度快，而且效率高，因为：
TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表 中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资 源少。
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页 的释放。
TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。 新行标识所用的计数值重置为该列的种子。
如果想保存标识计数值，请改用DELETEo如果要删除表定义及其数据，请使用DROP TABLE语句。
对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE,而应使用不带 WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录在日志中，所以它不能 激活触发器。TRUNCATE TABLE不能用于参与了索引视图的表。
19、常用系统检测脚本
还有如何查看查询分析器的SPID,可以在查询分析器的状态栏看到，比方sa(57), 这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗 体进行监控。状态栏在查询窗口的右下角。
LYVEN (13.0 RTM) [sa [55)] SQL Road 00:00:00 3 彳亍
20、获取脚本的执行时间declare @timediff datetime select @timediff=getdate() print '耗时：'+ convert (varchar (10), datediff (ms, @timediff, getdate ()))
结果如下：
结果靠消息
■行受影响)
10 4ms 1
在状态栏是不会精确到毫秒的，只能精确到秒LYVEN (13.0 RTM) sa (52) SQL Road [p0:00:001 3 行 这个脚本可以更加有效的查看SQL代码的执行效率。
-END -
(⑴，⑵,⑶，⑷)as pvt
结果:
ID NAME2j梨子
•一季度二季度三季度
300035004200四季度 5500
5000

2、列转行的用法UNPI0VT(id int, name varchar(20), QI int, Q2 int, Q3 int, Q4 int) from test
(提示：可以左右滑动代码)结果：
2梨子3000350042005500--列转行
select id, name, quarter, number fromI"一(M— —、i
([QI], [Q2], [Q3], [Q4]) as unpvt结果:
id name quarter number
1 | 苹果 QI 1000 ;1 苹果 Q2 2000
4000
5000
3000
3500
4200
5500
1 苹果Q31 苹果Q4
2 梨子QI2 梨子Q2
2 梨子Q32 梨子Q4
3、字符串替换 SUBSTRING/REPLACE
SELECT REPLACE (' abcdefg，, SUBSTRING (' abcdefg，, 2, 4),，**')
(无列名) a**fgSELECT REPLACE,, SUBSTRING,, 4, 11)/ ********')
（无列名）135********
SELECT REPLACE 12345678@qq. com，1234567' ,' ******')结果:
(无列名)******8@qq. com
4、查询一个表内相同纪录HAVING如果一个ID可以区分的话，可以这么写
SELECT * FROM HR. Employees
结果:
empid
lastname
firstname
1
| Davis
Sara
2
Funk
Don
3
Lew
Judy
4
Peled
Yael
5
Buck
Sven
6
Suurs
Paul
7
King
Russell
8
Cameron
Maria
9
Dolgopyatova
Zoya

titletitleofcourtesy
CEOMs.
Vice President, Sales Dr.
Sales ManagerMs.
Sales RepresentativeMrs.
Sales ManagerMr.
Sales RepresentativeMr.
Sales RepresentativeMr.
Sales RepresentativeMs.
Sales RepresentativeMs.

"here title inselect title from HR. Employees]
having count (1)empid
lastname
firstname
3
1 T
]Lew
Judy
4
Peled
Yael
5
Buck
Sven
6
Suurs
Paul
7
King
Russell
8
Cameron
Maria
9
Dolgopyatova
Zoya

empid
lastname
firstname
3
1 T
]Lew
Judy
4
Peled
Yael
5
Buck
Sven
6
Suurs
Paul
7
King
Russell
8
Cameron
Maria
9
Dolgopyatova
Zoya

title Sales Sales Sales Sales Sales Sales Sales
Manager Representative Manager Representative Representative Representative Representative
titleofcourtesy Ms.
Mrs.
Mr.
Mr.
Mr.
Ms.
Ms.

比照一下发现，ID为1,2的被过滤掉了，因为他们只有一条记录如果几个II）才能区分的话，可以这么写
^vhere title+titleof courtesy in (select title+titleofcourtesyj from I1R.
^roup by title, titleofcourtesy
empid
lastname
■%
firstname
title
titleofcourtesy
16
*
|Suurs
Paul
Sales
Representative
Mr.
7
King
Russell
Sales
Representative
Mr.
8
Cameron
Maria
Sales
Representative
Ms.
9
Dolgopyatova
Zoya
Sales
Representative
Ms.
title在和titleofcourtesy进行拼接后符合条件的就只有ID为6, 7, 8, 9的了5、把多行SQL数据变成一条多列数据，即新增列
SELECT
name,
SUM (CASE WHEN quarter=l THEN number ELSE 0 END)'一季度'，
SUM (CASE WHEN quarter^ THEN number ELSE 0 END)'二季度'，
SUM (CASE WHEN quarter=3 THEN number ELSE 0 END)'三季度，
SUM (CASE WHEN quarter^ THEN number ELSE 0 END)'四季度'FROM test
GROUP BY id, name结果:
ID NAME 一季度 2 I梨子3000
二季度三季度
35004200
四季度 5500
1 苹果1000
20004000
5000
我们将原来的4列增加到了 6歹细心的朋友可能发现了这个结果和上面的行转列 怎么一模一样？其实上面的行转列是省略写法，这种是比拟通用的写法。
6、表复制语法 1： Insert INTO table (fieldl, field2, . . . ) values (valuel, value2,...)
语法 2： Insert into Table2(fieldl, field2, . . . ) select valuel, value2,... fromTable1
（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入 源表Tablel的字段外，还可以插入常量。）语法 3： SELECT valel, value2 into Table2 from Tablel
（要求目标表Table2不存在，因为在插入时会自动创立表Table2,并将Tablel中指定字段数据复制到Table2中。）
语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的 数据,那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内 存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据 库表中的，你刷新一下mdf文件就知道了。
7、利用带关联子查询Update语句更新数据
Update Tablel set c 二(select c from Table2 where a = Tablel. a) where c is null set newqiantity=B. qiantity
upda t