SQL常用脚本整理，建议收藏

工作中有许多比力常用的SQL脚本，今天开始分几章分享给大家。
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可以区分的话，可以这么写

1. 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')
14. 当然也可以参考以前的示例，建立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

复制代码

以上内容，在工作中比力常用，能记住最好。不能记住就收藏起来，在必要的时候查询即可。

---

资源分享

下方这份完备的软件测试视频学习教程已经上传CSDN官方认证的二维码，朋侪们假如必要可以自行免费领取 【保证100%免费】

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