SQL常用脚本整理

最新推荐文章于 2023-09-06 15:47:12 发布
最新推荐文章于 2023-09-06 15:47:12 发布
阅读量103 收藏
点赞数
文章标签： mysql 数据库 python java sql
原文链接：https://mp.weixin.qq.com/s?__biz=MzU5MjI3NzIxMw==&mid=2247502070&idx=2&sn=189f55b99eddc9cfe93e604aaa28c7bb&chksm=fe20b344c9573a52552b1f790c2cb94a755144d1f694b98d8a5c9a91e9b3754e42993d098755&scene=126&&sessionid=0
版权 
 
 
750675b1349bd20691102ddfc336f0fc.gif
 
 
 
 
公众号后台回复“图书“，了解更多号主新书内容
 
 
     作者：平凡世界
 
 
     来源：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
 
 
结果：
 
 
c5d9b2d66e330622ea94ec651ce89241.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
 
 
结果：
 
 
8eaa876626a19ce1152ca4a079ead0a1.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
 
 
(提示：可以左右滑动代码)
 
 
结果：
 
 
df07a3f46068c6b4bec57345df95e444.png
 
 
--列转行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
 
 
结果：
 
 
505c6669f482b16770941a5805fa3170.png
 
 
3、字符串替换SUBSTRING/REPLACE
 
 
SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
 
 
结果：
 
 
5107a5a869fa697c84cc7a4d5ad857f1.png
 
 
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
 
 
结果：
 
 
ca89e82ef221e15130d0a4e6d48cd533.png
 
 
SELECT REPLACE('12345678@qq.com','1234567','******')
 
 
结果：
 
 
73fe43321a73b4d67d3f744c75630e5b.png
 
 
4、查询一个表内相同纪录 HAVING
 
 
如果一个ID可以区分的话，可以这么写
 
 
SELECT * FROM HR.Employees
 
 
结果：
 
 
5ad3e6e58f392dbe39387f66a715bae8.png
 
 
select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)
 
 
结果：
 
 
a06c882fc667fa230292db5dc5a432be.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)
 
 
结果：
 
 
905c2c7d7901639c770559e8430571c6.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
 
 
结果：
 
 
1bad180298344fd9be732eb9d0dd7c2a.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
 
 
以上内容，在工作中比较常用，能记住最好。不能记住就收藏起来，在需要的时候查询即可。
 
 
◆ ◆ ◆  ◆ ◆
麟哥新书已经在当当上架了，我写了本书：《拿下Offer-数据分析师求职面试指南》，目前当当正在举行活动，大家可以用相当于原价5折的预购价格购买，还是非常划算的：
 
 
 
 
数据森麟公众号的交流群已经建立，许多小伙伴已经加入其中，感谢大家的支持。大家可以在群里交流关于数据分析&数据挖掘的相关内容，还没有加入的小伙伴可以扫描下方管理员二维码，进群前一定要关注公众号奥，关注后让管理员帮忙拉进群，期待大家的加入。

管理员二维码：


猜你喜欢

● 卧槽！原来爬取B站弹幕这么简单
● 厉害了！麟哥新书登顶京东销量排行榜！
● 笑死人不偿命的知乎沙雕问题排行榜

● 用Python扒出B站那些“惊为天人”的阿婆主！
● 你相信逛B站也能学编程吗
 


                

        

        

    




    

      

        

            

              
                
                  IT农民工1
                
              
            

            

                关注
              
            

        

        

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

      

      










                



	

		

			

				
					
sqlServer内容整理

				
			

			

				

					05-31
				

			

		

		

			
				
sqlServer内容整理

			
		

	



                

              
                



	

		

			

				
					
SQL常用脚本

				
			

			

				

					weixin_44834205的博客
				

				

					11-27
					
					1402
					
				

			

		

		

			
				
SQL常用脚本

			
		

	



                


  
              

                


	

		

			

				
					
SQL 常用脚本

				
			

			

				

					u011695973的博客
				

				

					12-21
					
					401
					
				

			

		

		

			
				
常用脚本

			
		

	





	

		

			

				
					
SQL server常用脚本

				
			

			

				

					weixin_65636377的博客
				

				

					05-31
					
					906
					
				

			

		

		

			
				
创建和查询数据库对象、配置数据库对象的权限、在SQL server数据库中创建用户、创建视图和存储过程、SQL server建表语句、授予对存储过程的权限、撤销存储过程权限、Cmd操作SQL server

			
		

	



		

			
		



	

		

			

				
					
SQL Server SQL脚本

				
			

			

				

					unber的博客
				

				

					01-09
					
					3006
					
				

			

		

		

			
				
本节的主要内容是要教大家怎么通过编写 SQL 脚本来查询、更新并且运行数据库。

			
		

	





	

		

			

				
					
【python学习笔记】：SQL常用脚本（二）

				
			

			

				

					Jiangziyadizi的博客
				

				

					02-26
					
					932
					
				

			

		

		

			
				
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。还有如何查看查询分析器的SPID，可以在查询分析器的状态栏看到，比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。新行标识所用的计数值重置为该列的种子。当然两个以上的相同结构的表也是可以的，

			
		

	





	

		

			

				
					
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脚本？及作用和命令

					
热门推荐

				
			

			

				

					柳落青
				

				

					05-28
					
					2万+
					
				

			

		

		

			
				
1：什么是SQL脚本？
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称为这样的文件为sql脚本。
2：创建包含sql命令的sql脚本文件
文件中包含一些列的sql语句，每条语句最后以;结尾，文件内容示例如下：
--创建表，使用“--”进行注释
create table 表名称
    (
      Guid Varchar(38) not null primary key,
      Title Varchar(255),
    ) TYPE=InnoDB;
    
--在

			
		

	





	

		

			

				
					
常用sql脚本

				
			

			

				

					weixin_55102157的博客
				

				

					08-29
					
					542
					
				

			

		

		

			
				
常用SQL脚本

			
		

	





	

		

			

				
					
简单实用SQL脚本

				
			

			

				

					Goo_Woo的专栏
				

				

					11-05
					
					2314
					
				

			

		

		

			
				
简单实用SQL脚本

行列互转



createtabletest(idint,namevarchar(20),quarterint,profileint)
insertintotestvalues(1,'a',1,1000)
insertintotestvalues(1,'a',2,2000)
insertintotestvalues(1,'a',3,4000)
insertintotestvalues(1,'a',4,5000)
insertintotest...

			
		

	





	

		

			

				
					
从零开始编写数据库sql脚本并运行

				
			

			

				

					weixin_44924882的博客
				

				

					07-11
					
					2万+
					
				

			

		

		

			
				
编写sql脚本
一，概述
在日常开发中，我们一般是通过数据库的可视化软件例如Navicat来管理数据库，包括创建删除修改等，这无疑是非常便捷的，但是当进行项目搬迁的时候，用Navicat一个一个的重新进行编写，就有点麻烦了，尤其是如果数据库中要插入大量的数据时，此时如果通过sql脚本，那将大大的减少我们的工作量。
二，下面我们就来编写一个简单的sql脚本：
1，创建数据库
创建数据库时我们需要指定数据库的编码格式，以及数据库的编码排序规则，详细的代码如下：
drop database if EXISTS d

			
		

	





	

		

			

				
					
【SQL Server】非常实用的查询脚本，建议收藏起来

				
			

			

				

					lsdaini的博客
				

				

					09-06
					
					605
					
				

			

		

		

			
				
分享几个在实际工作中常用的数据库脚本

			
		

	





	

		

			

				
					
一套简单实用的SQL脚本，总有你需要的

				
			

			

				

					weixin_43167418的博客
				

				

					12-12
					
					2262
					
				

			

		

		

			
				
工作中有许多比较常用的SQL脚本，今天开始分几章分享给大家。1、行转列的用法PIVOTCREATE table test
(id int,name nvarchar(20),quarte...

			
		

	





	

		

			

				
					
SqlServer数据库常用脚本合集-持续更新

				
			

			

				

					情绪不稳定的98年程序员
				

				

					04-02
					
					1152
					
				

			

		

		

			
				
数据查询类

关键字查询存储过程


SELECT DISTINCT obj.Name 存储过程名,xtype
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%PL_EndCon%'

查询当前正在执行的SQL


SELECT TOP 100
 t.hostname,
 t.loginame,
 percent_complete,
 [session_id],
 der.[reques

			
		

	





	

		

			

				
					
sql server 字符串列多行转换为一行拼接和转换为多行

				
			

			

				

					qingwenc的博客
				

				

					08-26
					
					1273
					
				

			

		

		

			
				
--STUFF：从位置1开始，截取1个字符串，替换为'',目的去除第一个逗号
--FOR XML PATH('')：行转列拼接字符串
STUFF((SELECT distinct ',' + aa.Customer FROM BC_OutQAReport aa  FOR XML PATH('')), 1, 1, '')  as Customer 




			
		

	





	

		

			

				
					
SQL 常用脚本大全

				
			

			

				

					cainiao_python的博客
				

				

					03-14
					
					2472
					
				

			

		

		

			
				
1、行转列的用法PIVOTCREATEtabletest
(idint,namenvarchar(20),quarterint,numberint)
insertintot...

			
		

	





	

		

			

				
					
sql server 脚本下载

					
最新发布

				
			

			

				

					12-31
				

			

		

		

			
				
在SQL Server中，可以使用以下步骤来下载脚本：  1. 首先，打开SQL Server Management Studio（SSMS）工具，连接到要下载脚本的目标数据库。  2. 在“对象资源管理器”窗口中，展开“数据库”节点，找到目标数据库。...

			
		

	



              



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

  

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

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

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

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值