一套簡單實用的SQL腳本，總有你需要的
本文轉(zhuǎn)載自微信公眾號「SQL數(shù)據(jù)庫開發(fā)」，作者丶平凡世界。轉(zhuǎn)載本文請聯(lián)系SQL數(shù)據(jù)庫開發(fā)公眾號。
1、行轉(zhuǎn)列的用法PIVOT
- CREATE table test
- (id int,name nvarchar(20),quarter int,number int)
- insert into test values(1,N'蘋果',1,1000)
- insert into test values(1,N'蘋果',2,2000)
- insert into test values(1,N'蘋果',3,4000)
- insert into test values(1,N'蘋果',4,5000)
- insert into test values(2,N'梨子',1,3000)
- insert into test values(2,N'梨子',2,3500)
- insert into test values(2,N'梨子',3,4200)
- insert into test values(2,N'梨子',4,5500)
- select * from test
結(jié)果：
- 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
結(jié)果：
2、列轉(zhuǎn)行的用法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
(提示：可以左右滑動代碼)
結(jié)果：
- --列轉(zhuǎn)行
- select id,name,quarter,number
- from
- test2
- unpivot
- (
- number
- for quarter in
- ([Q1],[Q2],[Q3],[Q4])
- )
- as unpvt
結(jié)果：
3、字符串替換SUBSTRING/REPLACE
- SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
結(jié)果：
- SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
結(jié)果：
- SELECT REPLACE('12345678@qq.com','1234567','******')
結(jié)果：
4、查詢一個表內(nèi)相同紀(jì)錄 HAVING
如果一個ID可以區(qū)分的話，可以這么寫
SELECT * FROM HR.Employees
結(jié)果：
- select * from HR.Employees
- where title in (
- select title from HR.Employees
- group by title
- having count(1)>1)
結(jié)果：
對比一下發(fā)現(xiàn)，ID為1,2的被過濾掉了，因為他們只有一條記錄
如果幾個ID才能區(qū)分的話，可以這么寫
- select * from HR.Employees
- where title+titleofcourtesy in
- (select title+titleofcourtesy
- from HR.Employees
- group by title,titleofcourtesy
- having count(1)>1)
結(jié)果：
title在和titleofcourtesy進(jìn)行拼接后符合條件的就只有ID為6,7,8,9的了
5、把多行SQL數(shù)據(jù)變成一條多列數(shù)據(jù)，即新增列
- 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
結(jié)果：
我們將原來的4列增加到了6列。細(xì)心的朋友可能發(fā)現(xiàn)了這個結(jié)果和上面的行轉(zhuǎn)列怎么一模一樣?其實上面的行轉(zhuǎn)列是省略寫法，這種是比較通用的寫法。
6、表復(fù)制
語法1：Insert INTO table(field1,field2,...) values(value1,value2,...)
語法2：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
(要求目標(biāo)表Table2必須存在，由于目標(biāo)表Table2已經(jīng)存在，所以我們除了插入源表Table1的字段外，還可以插入常量。)
語法3：SELECT vale1, value2 into Table2 from Table1
(要求目標(biāo)表Table2不存在，因為在插入時會自動創(chuàng)建表Table2，并將Table1中指定字段數(shù)據(jù)復(fù)制到Table2中。)
語法4：使用導(dǎo)入導(dǎo)出功能進(jìn)行全表復(fù)制。如果是使用【編寫查詢以指定要傳輸?shù)臄?shù)據(jù)】，那么在大數(shù)據(jù)表的復(fù)制就會有問題?因為復(fù)制到一定程度就不再動了，內(nèi)存爆了?它也沒有寫入到表中。而使用上面3種語法直接執(zhí)行是會馬上刷新到數(shù)據(jù)庫表中的，你刷新一下mdf文件就知道了。
7、利用帶關(guān)聯(lián)子查詢Update語句更新數(shù)據(jù)
- --方法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、連接遠(yuǎn)程服務(wù)器
- --方法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')
當(dāng)然也可以參考以前的示例，建立DBLINK進(jìn)行遠(yuǎn)程連接
9、Date 和 Time 樣式 CONVERT
CONVERT() 函數(shù)是把日期轉(zhuǎn)換為新數(shù)據(jù)類型的通用函數(shù)。
CONVERT() 函數(shù)可以用不同的格式顯示日期/時間數(shù)據(jù)。
語法
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 規(guī)定目標(biāo)數(shù)據(jù)類型(帶有可選的長度)。data_to_be_converted 含有需要轉(zhuǎn)換的值。style 規(guī)定日期/時間的輸出格式。
可以使用的 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)
- --結(jié)果：
- 12 7 2020 9:33PM
- SELECT CONVERT(varchar(100), GETDATE(), 1)
- --結(jié)果：
- 12/07/20
- SELECT CONVERT(varchar(100), GETDATE(), 2)
- --結(jié)果：
- 20.12.07
- SELECT CONVERT(varchar(100), GETDATE(), 3)
- --結(jié)果：
- 07/12/20
- SELECT CONVERT(varchar(100), GETDATE(), 4)
- --結(jié)果：
- 07.12.20
- SELECT CONVERT(varchar(100), GETDATE(), 5)
- --結(jié)果：
- 07-12-20
- SELECT CONVERT(varchar(100), GETDATE(), 6)
- --結(jié)果：
- 07 12 20
- SELECT CONVERT(varchar(100), GETDATE(), 7)
- --結(jié)果：
- 12 07, 20
- SELECT CONVERT(varchar(100), GETDATE(), 8)
- --結(jié)果：
- 21:33:18
- SELECT CONVERT(varchar(100), GETDATE(), 9)
- --結(jié)果：
- 12 7 2020 9:33:18:780PM
- SELECT CONVERT(varchar(100), GETDATE(), 10)
- --結(jié)果：
- 12-07-20
- SELECT CONVERT(varchar(100), GETDATE(), 11)
- --結(jié)果：
- 20/12/07
- SELECT CONVERT(varchar(100), GETDATE(), 12)
- --結(jié)果：
- 201207
- SELECT CONVERT(varchar(100), GETDATE(), 13)
- --結(jié)果：
- 07 12 2020 21:33:18:780
- SELECT CONVERT(varchar(100), GETDATE(), 14)
- --結(jié)果：
- 21:33:18:780
- SELECT CONVERT(varchar(100), GETDATE(), 20)
- --結(jié)果：
- 2020-12-07 21:33:18
- SELECT CONVERT(varchar(100), GETDATE(), 21)
- --結(jié)果：
- 2020-12-07 21:33:18.780
- SELECT CONVERT(varchar(100), GETDATE(), 22)
- --結(jié)果：
- 12/07/20 9:33:18 PM
- SELECT CONVERT(varchar(100), GETDATE(), 23)
- --結(jié)果：
- 2020-12-07
- SELECT CONVERT(varchar(100), GETDATE(), 24)
- --結(jié)果：
- 21:33:18
- SELECT CONVERT(varchar(100), GETDATE(), 25)
- --結(jié)果：
- 2020-12-07 21:33:18.780
- SELECT CONVERT(varchar(100), GETDATE(), 100)
- --結(jié)果：
- 12 7 2020 9:33PM
- SELECT CONVERT(varchar(100), GETDATE(), 101)
- --結(jié)果：
- 12/07/2020
- SELECT CONVERT(varchar(100), GETDATE(), 102)
- --結(jié)果：
- 2020.12.07
- SELECT CONVERT(varchar(100), GETDATE(), 103)
- --結(jié)果：
- 07/12/2020
- SELECT CONVERT(varchar(100), GETDATE(), 104)
- --結(jié)果：
- 07.12.2020
- SELECT CONVERT(varchar(100), GETDATE(), 105)
- --結(jié)果：
- 07-12-2020
- SELECT CONVERT(varchar(100), GETDATE(), 106)
- --結(jié)果：
- 07 12 2020
- SELECT CONVERT(varchar(100), GETDATE(), 107)
- --結(jié)果：
- 12 07, 2020
- SELECT CONVERT(varchar(100), GETDATE(), 108)
- --結(jié)果：
- 21:33:18
- SELECT CONVERT(varchar(100), GETDATE(), 109)
- --結(jié)果：
- 12 7 2020 9:33:18:780PM
- SELECT CONVERT(varchar(100), GETDATE(), 110)
- --結(jié)果：
- 12-07-2020
- SELECT CONVERT(varchar(100), GETDATE(), 111)
- --結(jié)果：
- 2020/12/07
- SELECT CONVERT(varchar(100), GETDATE(), 112)
- --結(jié)果：
- 20201207
- SELECT CONVERT(varchar(100), GETDATE(), 113)
- --結(jié)果：
- 07 12 2020 21:33:18:780
- SELECT CONVERT(varchar(100), GETDATE(), 114)
- --結(jié)果：
- 21:33:18:780
- SELECT CONVERT(varchar(100), GETDATE(), 120)
- --結(jié)果：
- 2020-12-07 21:33:18
- SELECT CONVERT(varchar(100), GETDATE(), 121)
- --結(jié)果：
- 2020-12-07 21:33:18.780
以上內(nèi)容，在工作中比較常用，能記住最好。不能記住就收藏起來，在需要的時候查詢即可。
