SQL 常用脚本大全

莫张周刘王  论坛元老 | 2024-8-19 10:19:17 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1040|帖子 1040|积分 3120

1 行转列的用法PIVOT

  1. CREATE table test
  2. (id int,name nvarchar(20),quarter int,number int)
  3. insert into test values(1,N'苹果',1,1000)
  4. insert into test values(1,N'苹果',2,2000)
  5. insert into test values(1,N'苹果',3,4000)
  6. insert into test values(1,N'苹果',4,5000)
  7. insert into test values(2,N'梨子',1,3000)
  8. insert into test values(2,N'梨子',2,3500)
  9. insert into test values(2,N'梨子',3,4200)
  10. insert into test values(2,N'梨子',4,5500)
  11. select * from test
复制代码
结果：

  1. select ID,NAME,
  2. [1] as '一季度',
  3. [2] as '二季度',
  4. [3] as '三季度',
  5. [4] as '四季度'
  6. from
  7. test
  8. pivot
  9. (
  10. sum(number)
  11. for quarter in
  12. ([1],[2],[3],[4])
  13. )
  14. as pvt
复制代码
结果：

2 列转行的用法UNPIOVT

  1. create table test2
  2. (id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
  3. insert into test2 values(1,'苹果',1000,2000,4000,5000)
  4. insert into test2 values(2,'梨子',3000,3500,4200,5500)
  5. select * from test2
复制代码
结果：

  1. --列转行
  2. select id,name,quarter,number
  3. from
  4. test2
  5. unpivot
  6. (
  7. number
  8. for quarter in
  9. ([Q1],[Q2],[Q3],[Q4])
  10. )
  11. as unpvt
复制代码
结果：

3 字符串更换SUBSTRING/REPLACE

  1. SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
复制代码
结果：

  1. SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
复制代码
结果：

  1. SELECT REPLACE('12345678@qq.com','1234567','******')
复制代码
结果：

4 查询一个表内类似记录 HAVING

如果一个ID可以区分的话，可以这么写
  2. SELECT * FROM HR.Employees
复制代码
结果：

  1. select * from HR.Employees
  2. where title in (
  3. select title from HR.Employees
  4. group by title
  5. having count(1)>1)
复制代码
结果：

对比一下发现，ID为1,2的被过滤掉了，由于他们只有一条记录
如果几个ID才华区分的话，可以这么写：
  1. select * from HR.Employees
  2. where title+titleofcourtesy in
  3. (select title+titleofcourtesy
  4. from HR.Employees
  5. group by title,titleofcourtesy
  6. having count(1)>1)
复制代码
结果：

title在和titleofcourtesy举行拼接后符合条件的就只有ID为6,7,8,9的了
5 把多行SQL数据变成一条多列数据，即新增列

  1. SELECT
  2. id,
  3. name,
  4. SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度',
  5. SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度',
  6. SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
  7. SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'
  8. FROM test
  9. GROUP BY id,name
复制代码
结果：

我们将原来的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. --方法1：
  2. Update Table1
  3. set c = (select c from Table2 where a = Table1.a)
  4. where c is null
  6. --方法2：
  7. update  A
  8. set  newqiantity=B.qiantity
  9. from  A,B
  10. where  A.bnum=B.bnum
  12. --方法3：
  13. update
  14. (select A.bnum ,A.newqiantity,B.qiantity from A
  15. left join B on A.bnum=B.bnum) AS C
  16. set C.newqiantity = C.qiantity
  17. where C.bnum ='001'
复制代码
8 连接长途服务器

  1. --方法1：
  2. select *  from openrowset(
  3. 'SQLOLEDB',
  4. 'server=192.168.0.1;uid=sa;pwd=password',
  5. 'SELECT * FROM dbo.test')
  7. --方法2：
  8. select *  from openrowset(
  9. 'SQLOLEDB',
  10. '192.168.0.1';
  11. 'sa';
  12. 'password',
  13. '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 值：

  1. SELECT CONVERT(varchar(100), GETDATE(), 0)
  2. --结果：
  3. 12  7 2020  9:33PM
  4. SELECT CONVERT(varchar(100), GETDATE(), 1)
  5. --结果：
  6. 12/07/20
  7. SELECT CONVERT(varchar(100), GETDATE(), 2)
  8. --结果：
  9. 20.12.07
  10. SELECT CONVERT(varchar(100), GETDATE(), 3)
  11. --结果：
  12. 07/12/20
  13. SELECT CONVERT(varchar(100), GETDATE(), 4)
  14. --结果：
  15. 07.12.20
  16. SELECT CONVERT(varchar(100), GETDATE(), 5)
  17. --结果：
  18. 07-12-20
  19. SELECT CONVERT(varchar(100), GETDATE(), 6)
  20. --结果：
  21. 07 12 20
  22. SELECT CONVERT(varchar(100), GETDATE(), 7)
  23. --结果：
  24. 12 07, 20
  25. SELECT CONVERT(varchar(100), GETDATE(), 8)
  26. --结果：
  27. 21:33:18
  28. SELECT CONVERT(varchar(100), GETDATE(), 9)
  29. --结果：
  30. 12  7 2020  9:33:18:780PM
  31. SELECT CONVERT(varchar(100), GETDATE(), 10)
  32. --结果：
  33. 12-07-20
  34. SELECT CONVERT(varchar(100), GETDATE(), 11)
  35. --结果：
  36. 20/12/07
  37. SELECT CONVERT(varchar(100), GETDATE(), 12)
  38. --结果：
  39. 201207
  40. SELECT CONVERT(varchar(100), GETDATE(), 13)
  41. --结果：
  42. 07 12 2020 21:33:18:780
  43. SELECT CONVERT(varchar(100), GETDATE(), 14)
  44. --结果：
  45. 21:33:18:780
  46. SELECT CONVERT(varchar(100), GETDATE(), 20)
  47. --结果：
  48. 2020-12-07 21:33:18
  49. SELECT CONVERT(varchar(100), GETDATE(), 21)
  50. --结果：
  51. 2020-12-07 21:33:18.780
  52. SELECT CONVERT(varchar(100), GETDATE(), 22)
  53. --结果：
  54. 12/07/20  9:33:18 PM
  55. SELECT CONVERT(varchar(100), GETDATE(), 23)
  56. --结果：
  57. 2020-12-07
  58. SELECT CONVERT(varchar(100), GETDATE(), 24)
  59. --结果：
  60. 21:33:18
  61. SELECT CONVERT(varchar(100), GETDATE(), 25)
  62. --结果：
  63. 2020-12-07 21:33:18.780
  64. SELECT CONVERT(varchar(100), GETDATE(), 100)
  65. --结果：
  66. 12  7 2020  9:33PM
  67. SELECT CONVERT(varchar(100), GETDATE(), 101)
  68. --结果：
  69. 12/07/2020
  70. SELECT CONVERT(varchar(100), GETDATE(), 102)
  71. --结果：
  72. 2020.12.07
  73. SELECT CONVERT(varchar(100), GETDATE(), 103)
  74. --结果：
  75. 07/12/2020
  76. SELECT CONVERT(varchar(100), GETDATE(), 104)
  77. --结果：
  78. 07.12.2020
  79. SELECT CONVERT(varchar(100), GETDATE(), 105)
  80. --结果：
  81. 07-12-2020
  82. SELECT CONVERT(varchar(100), GETDATE(), 106)
  83. --结果：
  84. 07 12 2020
  85. SELECT CONVERT(varchar(100), GETDATE(), 107)
  86. --结果：
  87. 12 07, 2020
  88. SELECT CONVERT(varchar(100), GETDATE(), 108)
  89. --结果：
  90. 21:33:18
  91. SELECT CONVERT(varchar(100), GETDATE(), 109)
  92. --结果：
  93. 12  7 2020  9:33:18:780PM
  94. SELECT CONVERT(varchar(100), GETDATE(), 110)
  95. --结果：
  96. 12-07-2020
  97. SELECT CONVERT(varchar(100), GETDATE(), 111)
  98. --结果：
  99. 2020/12/07
  100. SELECT CONVERT(varchar(100), GETDATE(), 112)
  101. --结果：
  102. 20201207
  103. SELECT CONVERT(varchar(100), GETDATE(), 113)
  104. --结果：
  105. 07 12 2020 21:33:18:780
  106. SELECT CONVERT(varchar(100), GETDATE(), 114)
  107. --结果：
  108. 21:33:18:780
  109. SELECT CONVERT(varchar(100), GETDATE(), 120)
  110. --结果：
  111. 2020-12-07 21:33:18
  112. SELECT CONVERT(varchar(100), GETDATE(), 121)
  113. --结果：
  114. 2020-12-07 21:33:18.780
复制代码
10 SQL中的相除

10.1 方法一

  1. --SQL中的相除
  2. SELECT
  3. CASE WHEN ISNULL(A-B,0)=0 THEN ''
  4. ELSE CAST(CONVERT(DECIMAL(18,2),A*100.0/(A-B)) AS VARCHAR(10))+'%'  
  5. END AS '百分数'  --FROM 表
复制代码
这里我们先要判定被除数是否为0，如果为0给出一个想输出的结果，这里我们返回空白(是字符类型，不是NULL)，在不为0的时间就给出详细的盘算公式，然后转换成字符类型再和“%”举行拼接。例如：
  1. SELECT
  2. CASE WHEN ISNULL(5-2,0)=0 THEN ''
  3. ELSE CAST(CONVERT(DECIMAL(18,2),5*100.0/(5-2)) AS VARCHAR(10))+'%'  
  4. END AS '百分数'  --FROM 表
复制代码
返回的结果：

10.2 方法二

  1. SELECT
  2. (CONVERT(VARCHAR(20),ROUND(41*100.0/88,3))+'%') AS '百分比'
  3. --FROM A
复制代码
执行结果：

11 四舍五入ROUND函数

ROUND ( numeric_expression , length [ ,function ] )
function 必须为 tinyint、smallint 或 int。
如果省略 function 或其值为 0（默认值），则将舍入 numeric_expression。
如果指定了0以外的值，则将截断 numeric_expression。
  1. SELECT ROUND(150.45648, 2);
  2. --保留小数点后两位，需要四舍五入
  3. --结果：
  4. 150.46000
  6. SELECT ROUND(150.45648, 2, 0);
  7. --保留小数点后两位，0为默认值，表示进行四舍五入
  8. --结果：
  9. 150.46000
  11. SELECT ROUND(150.45648, 2, 1);
  12. --保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果，
  13. --与Oracle的TRUNC函数效果相同
  14. --结果：
  15. 150.45000
  17. SELECT ROUND(150.45648, 2, 2);
  18. --保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果，
  19. --与Oracle的TRUNC函数效果相同
  20. --结果：
  21. 150.45000
复制代码
12 对字段出现NULL值的处理

12.1 方法一

  1. --CASE
  2. SELECT
  3. CASE WHEN  '字段名' IS NULL THEN 'NULL'
  4. ELSE CONVERT(VARCHAR(20),'字段名1') END
  5. AS 'NewName'
  6. --结果：
  7. 字段名1
  9. SELECT CASE WHEN NULL IS NULL THEN 'N'
  10. ELSE CONVERT(VARCHAR(20),NULL) END
  11. AS 'NewName'
  12. --结果：
  13. N
复制代码
12.2 方法二

  1. --SQL Server 2005：COALESCE
  2. SELECT COALESCE('字符串类型字段','N') AS 'NewName'
  3. --结果：
  4. 字符串类型字段
  6. SELECT COALESCE(CONVERT(VARCHAR(20),'非字符串类型字段'),'N') AS 'NewName'
  7. --结果：
  8. 非字符串类型字段
  10. SELECT COALESCE(CONVERT(VARCHAR(20),NULL),'N') AS 'NewName'
  11. --结果：
  12. N
  14. --COALESCE,返回其参数中的第一个非空表达式
  15. SELECT COALESCE(NULL,NULL,1,2,NULL)
  16. --结果：
  17. 1
  19. SELECT COALESCE(NULL,11,12,13,NULL)
  20. --结果：
  21. 11
  23. SELECT COALESCE(111,112,113,114,NULL)
  24. --结果：
  25. 111
复制代码
13 COUNT的几种情况

  1. --以下三种方法均可统计出表的记录数
  2. --第一种
  3. select count(*) from tablename
  5. --第二种
  6. select count(ID) from tablename
  8. --第三种,1换成其它值也是可以的
  9. select count(1) from tablename
复制代码
14 UNION ALL多表插入

把两个类似结构的表union后插入到一个新表中，
当然两个以上的类似结构的表也是可以的，
这里的类似是指两个或多个表的列数和每个对应列的类型类似，
列名称可以不同
  1. select *
  2. into table_new
  3. from table_1
  4. union all
  5. select * from table_2
复制代码
15 查看数据库缓存的SQL

  1. use master
  2. declare @dbid int
  3. Select @dbid = dbid from sysdatabases
  4. where name = 'SQL_ROAD'--修改成数据库的名称
  6. select
  7. dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
  8. DB_Name(dbid) as DatabaseName,SQL
  9. from syscacheobjects
  10. where dbid=@dbid
  11. order by dbid,useCounts desc,objtype
复制代码
我们可以看到数据库中当前正在运行的SQL有哪些
16 删除计划缓存

  1. --删除整个数据库的计划缓存
  2. DBCC FREEPROCCACHE
  4. --删除某个数据库的计划缓存
  5. USE master
  6. DECLARE @dbid INT
  7. SELECT @dbid=dbid FROM sysdatabases WHERE NAME = 'SQL_ROAD'
  8. DBCC FLUSHPROCINDB (@dbid)
复制代码
17 SQL换行

SQL的换行
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
  1. PRINT 'SQL'+CHAR(13)+'ROAD'
  2. PRINT 'SQL'+CHAR(10)+'ROAD'
  3. PRINT 'SQL'+CHAR(9)+'ROAD'
复制代码
执行结果：

如果将查询结果以文本格式体现，而不是网格格式体现，SELECT语句也适用，我们先将查询结果改成以文本格式体现

  1. --以文本格式显示结果
  2. SELECT 'SQL'+ CHAR(10)+'ROAD'
  3. SELECT 'SQL'+ CHAR(13)+'ROAD'
  4. SELECT 'SQL' + CHAR(10) + CHAR(13) + 'ROAD'
复制代码
结果如下：

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 常用系统检测脚本

  1. --查看内存状态
  2. dbcc memorystatus
  4. --查看哪个引起的阻塞，blk
  5. EXEC sp_who active
  7. --查看锁住了那个资源id，objid
  8. EXEC sp_lock
复制代码
还有怎样查看查询分析器的SPID，可以在查询分析器的状态栏看到，比如sa(57),这就体现当前查询分析器SPID为57,如许在使用profile的时间就可以指定当前窗体举行监控。状态栏在查询窗口的右下角。

20 获取脚本的执行时间

  1. declare @timediff datetime
  2. select @timediff=getdate()
  3. select * from Suppliers
  4. print '耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate()))
复制代码
结果如下：

在状态栏是不会精确到毫秒的，只能精确到秒

这个脚本可以更加有用的查看SQL代码的执行效率。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

莫张周刘王

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表