SQL常用脚本整理（上）

于 2023-09-11 08:30:13 发布
阅读量121 收藏
点赞数
文章标签： sql java 数据库 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liyue071714118/article/details/132820153
版权

点击关注公众号，SQL干货及时获取

后台回复：1024，获取海量学习资源
SQL刷题专栏
SQL145题系列
大家注意：
因为微信改了推送机制，会有小伙伴刷不到当天的文章，
一些比较实用的知识和信息，错过了就是错过了。
所以建议大家加个星标，就能第一时间收到推送了。

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

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

结果：

9a3e1a93aff4696024145e32c6d1c451.png

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

结果：

66732b18a3f17cbecad033305b4203ed.png

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

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

结果：

b50e2ea510b80b13d00add98f34df6d1.png

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

结果：

e80d287d92b7b14c445edf436c5257eb.png

3、字符串替换SUBSTRING/REPLACE

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

结果：

aa8ecea96fd102be9e4fc88455f37f35.png

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

结果：

e6e615fcca2bd7b3c393a598ba4b4b67.png

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

结果：

d7ae8451c80a06b219d5532661e73d83.png

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

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

SELECT * FROM HR.Employees

结果：

ddb28ae3f614cc580f5bf3c74a6a9786.png

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

结果：

8fb313deeb387ba00d9b202700593574.png

对比一下发现，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)

结果：

6c5b1d7237faee7ed96b88dcdde3b359.png

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

结果：

b2f6acbd28511fd0a066342ae6d26010.png

我们将原来的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 IDStyle 格式
100 或者 0mon dd yyyy hh:miAM （或者 PM）
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109 或者 9mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 或者 13dd mon yyyy hh:mm:ss:mmm(24h)
114hh:mi:ss:mmm(24h)
120 或者 20yyyy-mm-dd hh:mi:ss(24h)
121 或者 21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130dd mon yyyy hh:mi:ss:mmmAM
131dd/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两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。


有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行
数据前线
 
 
后台回复关键字：1024，获取一份精心整理的技术干货
 
 
后台回复关键字：进群，带你进入高手如云的交流群。
 
 
推荐阅读
 
  


                

        

        

    




    

      

        

            

              
                
                  SQL数据库开发
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
                
  • 
                  
                    打赏
                    
                  
                  
    打赏
    
                
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










                



	

		

			

				
					
sql多行转列

				
			

			

				

					12-22
				

			

		

		

			
				
主要是关于sqlserver的多行转列的问题，这是我经过个人测试的语句。

			
		

	



                

              
                



	

		

			

				
					
SQL查询多行转一行多列

				
			

			

				

					子非鱼39938的博客
				

				

					04-25
					
					3916
					
				

			

		

		

			
				
SQL查询多行转一行多列

			
		

	



                


  
              

                


	

		

			

				
					
sql行转列三个方法

				
			

			

				

					miaoao611的博客
				

				

					08-15
					
					6835
					
				

			

		

		

			
				
select uid, sum(if(course=‘语文’,score,NULL)) as。sum((if(course=‘数学’,score,NULL)) as。由多行变一行，group by聚合，由一列变多列，衍生提前。select uid, ‘数学’ as course,select uid, ‘语文’ as course，1.行转列sum+if/case when。2.列转行union。...

			
		

	





	

		

			

				
					
【大数据Hive3.x数仓开发】HiveSQL行转列应用-多行转多列case when&多行转单列concat_ws,collect

				
			

			

				

					锵锵锵锵蒋的博客
				

				

					04-08
					
					818
					
				

			

		

		

			
				
关于多行转多列，多行转单列的应用场景及实现，涉及case when,concat_ws，collect等函数。

			
		

	



		

			
		



	

		

			

				
					
hivesql 多行转多列

				
			

			

				

					杨玉庆的专栏
				

				

					02-22
					
					291
					
				

			

		

		

			
				
hivesql 多行转多列 快手面试

			
		

	





	

		

			

				
					
sqlServer内容整理

					
最新发布

				
			

			

				

					05-31
				

			

		

		

			
				
sqlServer内容整理

			
		

	





	

		

			

				
					
mysql常用监控脚本命令整理

				
			

			

				

					12-15
				

			

		

		

			
				
#/bin/sh  #检测mysql server是否正常提供服务 mysqladmin -u sky -ppwd -h localhost ping  #获取mysql当前的几个状态值 mysqladmin -u sky -ppwd -h localhost status  #获取数据库当前的连接信息 ...

			
		

	





	

		

			

				
					
ORACLE数据库常用监控sql脚本

				
			

			

				

					01-29
				

			

		

		

			
				
关于最耗资源、表空间使用情况、查看锁等监控脚本整理。

			
		

	





	

		

			

				
					
mysql 常用命令用法总结脚本之家整理版

				
			

			

				

					12-15
				

			

		

		

			
				
一、启动与退出 1、进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql> 2、退出MySQL：quit或exit 二、库操作 1、、创建数据库 命令：create database...

			
		

	





	

		

			

				
					
常用日期相关的SQL语句

				
			

			

				

					04-24
				

			

		

		

			
				
平常整理的一些常用日期相关SQL 日期相减取得相隔天数： 	SELECT DATEDIFF(D,'2008/09/01','2008/09/03') 一个月第一天的SQL 脚本：   SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)  ...

			
		

	





	

		

			

				
					
SQL行列转换

				
			

			

				

					Hudas的博客
				

				

					04-09
					
					2万+
					
				

			

		

		

			
				
本文主要介绍SQL常见的行列转换

			
		

	





	

		

			

				
					
SQL中怎么将行转成列？

					
热门推荐

				
			

			

				

					流楚丶格念的博客
				

				

					08-23
					
					3万+
					
				

			

		

		

			
				
我们以MySQL数据库为例，来说明行转列的实现方式。首先，假设我们有一张分数表（tb_score），表中的数据如下图：然后，我们再来看一下转换之后需要得到的结果，如下图：可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。通常，我们有两种方式来实现这种转换。

			
		

	





	

		

			

				
					
SQL Server- 行列转换 行转列，多行转多列 - max 函数用法

				
			

			

				

					weixin_33835690的博客
				

				

					04-17
					
					1093
					
				

			

		

		

			
				
效果如图，把同一个 code， 按 cate 列分为 Actual 和 Budget 两行，再把mode 每种类型转换成 列名 ，主要用到了 max 函数，很实用



 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
 drop table #t  
   create ta...

			
		

	





	

		

			

				
					
sqlserver 多行转一列_一列转多行多列，这几招超简单，我不允许你还不会

				
			

			

				

					weixin_35282584的博客
				

				

					01-12
					
					1756
					
				

			

		

		

			
				
如下图所示，如何将一列数据转换为多行多列，今天与大家分享几种方法，尤其是最后一种一定要掌握。第一种：借用OFFSET函数在单元格中输入公式=OFFSET($A$1,ROW(A1)+COLUMN(A1)*4-4,)，向右向下填充。公式说明：FFSET函数主要用来返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的结果可以是单个单元格，也可以是单元格区域。它的语法结构为：=OFFSET(基点,...

			
		

	





	

		

			

				
					
SQLServer多行多列数据转为一行

				
			

			

				

					朱金拖的专栏
				

				

					08-16
					
					9837
					
				

			

		

		

			
				
SQLServer多行多列数据转为一行

			
		

	





	

		

			

				
					
server多列转行 sql_SQL Server 行转列，列转行。多行转成一列

				
			

			

				

					weixin_39614561的博客
				

				

					12-22
					
					270
					
				

			

		

		

			
				
一、多行转成一列(并以","隔开)表名：A表数据：想要的查询结果：查询语句：SELECT  name ,value = ( STUFF(( SELECT    ',' + valueFROM      AWHERE     name = Test.nameFORXML PATH('')), 1, 1, '') )FROM    A AS TestGROUP BY name;PS：STUFF语句就是...

			
		

	





	

		

			

				
					
mysql多行变多列（衍生形式）

				
			

			

				

					rainyspring4540的专栏
				

				

					05-06
					
					8424
					
				

			

		

		

			
				
我的这篇文章整理了mysql多行变多列的2种常见形式：http://blog.csdn.net/rainyspring4540/article/details/50231435
这里就不赘述了，下面说下它更复杂的衍生形式：
表结构：

create table student (
  name varchar(20) not null, -- 姓名
  course varchar(20) 

			
		

	





	

		

			

				
					
sql 多行转多列,多行转一列合并数据,列转行

				
			

			

				

					枫叶soli
				

				

					10-16
					
					1万+
					
				

			

		

		

			
				
多行转多列：
   要实现该功能pivot行数应该是必不可少吧，下面我就来说说该函数如何使用
 局限性：该函数只能针对已知行数要转为几列才能使用，动态行转动态列不支持（个人所知，如可以实现请指教）
用法：pivot( x for  x in ())


select n1,n2,n3 from 
(SELECT t1.code,t1.name
  FROM cd_data_dict

			
		

	





	

		

			

				
					
Linux上执行mysql的sql 脚本

				
			

			

				

					05-12
				

			

		

		

			
				
在Linux上执行MySQL的SQL脚本，可以使用以下命令：  ``` mysql -u <username> -p <password> <database_name> < <path_to_sql_file> ```  其中，`<username>`是MySQL数据库的用户名，`<password>`是MySQL数据库的...

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
SQL数据库开发

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值