SQL常用脚本整理,你保存了吗?

​工作中有许多比较常用的SQL脚本,今天开始分几章分享给大家。

创新互联公司为企业级客户提高一站式互联网+设计服务,主要包括成都做网站、成都网站建设、成都app软件开发重庆小程序开发、宣传片制作、LOGO设计等,帮助客户快速提升营销能力和企业形象,创新互联各部门都有经验丰富的经验,可以确保每一个作品的质量和创作周期,同时每年都有很多新员工加入,为我们带来大量新的创意。 

1、行转列的用法PIVOT

( , (), , )
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
*

结果:

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

结果:

2、列转行的用法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

(提示:可以左右滑动代码)

结果:

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

结果:

3、字符串替换SUBSTRING/REPLACE

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

结果:

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

结果:

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

结果:

4、查询一个表内相同纪录 HAVING

如果一个ID可以区分的话,可以这么写

SELECT * FROM HR.Employees

结果:

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

​结果:

对比一下发现,ID为1,2的被过滤掉了,因为他们只有一条记录

如果几个ID才能区分的话,可以这么写

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

​结果:

title在和titleofcourtesy进行拼接后符合条件的就只有ID为6,7,8,9的了

5、把多行SQL数据变成一条多列数据,即新增列

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

结果:

我们将原来的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:
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、连接远程服务器

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

当然也可以参考以前的示例,建立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 值:

Style ID

Style 格式

100 或者 0

mon dd yyyy hh:miAM (或者 PM)

101

mm/dd/yy

102

yy.mm.dd

103

dd/mm/yy

104

dd.mm.yy

105

dd-mm-yy

106

dd mon yy

107

Mon dd, yy

108

hh:mm:ss

109 或者 9

mon dd yyyy hh:mi:ss:mmmAM(或者 PM)

110

mm-dd-yy

111

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)
--结果:
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 1)
--结果:
12/07/20
SELECT CONVERT(varchar(100), GETDATE(), 2)
--结果:
20.12.07
SELECT CONVERT(varchar(100), GETDATE(), 3)
--结果:
07/12/20
SELECT CONVERT(varchar(100), GETDATE(), 4)
--结果:
07.12.20
SELECT CONVERT(varchar(100), GETDATE(), 5)
--结果:
07-12-20
SELECT CONVERT(varchar(100), GETDATE(), 6)
--结果:
07 12 20
SELECT CONVERT(varchar(100), GETDATE(), 7)
--结果:
12 07, 20
SELECT CONVERT(varchar(100), GETDATE(), 8)
--结果:
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 9)
--结果:
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 10)
--结果:
12-07-20
SELECT CONVERT(varchar(100), GETDATE(), 11)
--结果:
20/12/07
SELECT CONVERT(varchar(100), GETDATE(), 12)
--结果:
201207
SELECT CONVERT(varchar(100), GETDATE(), 13)
--结果:
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 14)
--结果:
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 20)
--结果:
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 21)
--结果:
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 22)
--结果:
12/07/20 9:33:18 PM
SELECT CONVERT(varchar(100), GETDATE(), 23)
--结果:
2020-12-07
SELECT CONVERT(varchar(100), GETDATE(), 24)
--结果:
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 25)
--结果:
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 100)
--结果:
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 101)
--结果:
12/07/2020
SELECT CONVERT(varchar(100), GETDATE(), 102)
--结果:
2020.12.07
SELECT CONVERT(varchar(100), GETDATE(), 103)
--结果:
07/12/2020
SELECT CONVERT(varchar(100), GETDATE(), 104)
--结果:
07.12.2020
SELECT CONVERT(varchar(100), GETDATE(), 105)
--结果:
07-12-2020
SELECT CONVERT(varchar(100), GETDATE(), 106)
--结果:
07 12 2020
SELECT CONVERT(varchar(100), GETDATE(), 107)
--结果:
12 07, 2020
SELECT CONVERT(varchar(100), GETDATE(), 108)
--结果:
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 109)
--结果:
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 110)
--结果:
12-07-2020
SELECT CONVERT(varchar(100), GETDATE(), 111)
--结果:
2020/12/07
SELECT CONVERT(varchar(100), GETDATE(), 112)
--结果:
20201207
SELECT CONVERT(varchar(100), GETDATE(), 113)
--结果:
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 114)
--结果:
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 120)
--结果:
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 121)
--结果:
2020-12-07 21:33:18.780

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

文章题目:SQL常用脚本整理,你保存了吗?
URL链接:http://www.gawzjz.com/qtweb/news15/182865.html

网站建设、网络推广公司-创新互联,是专注品牌与效果的网站制作,网络营销seo公司;服务项目有等

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联