文章目录
- 一、ORACLE数据库
- 1.截取某特殊字符之间的字符串
- 2.取日期字段中每个月第一天到最后一天
- 3.decode函数经典应用
- 4.连接两个字符串，如果一个为空则返回有值的
- 5.返回字符串 每个单词首字母大写
- 6.返回字符串长度
- 7.从左边开始去除空格字符
- 8.从右边开始去除空格字符
- 9.去除左右所有空格
- 10.替代字符串中指定的字符为指定的字符
- 11.返回大于或等于n的 最小整数(向上取整)
- 12.返回小于或等于n的 最小整数(向下取整)
- 13.按照指定的精度进行四舍五入
- 14.按照指定的精度进行截取一个数
- 15.ORACLE中行转列和列转行的使用
- 16.Oracle的connect by level的使用（可用于生成时间列表）
- 17.开窗函数（over()）和分析函数（partition by）--可用于组内统计
- 18.listagg用法-----对分组后的数据按照一定的排序进行字符串连接
- 19.查询条数限制
- 20.SQL中使用正则表达式匹配需要的内容
- 21.TRUNC函数使用
- 22.偏移分析函数 lag()与lead() 用法
- 二、MYSQL数据库
- 1.存在同一列中逗号分隔转换成对应的行
- 三、SqlServer数据库
- 1.生成时间列表
- 2.获取全年日期列表
- 2.获取2020年第一周所有日期
- 3.常用的当前时间的一些处理方法
- 4.查询数据库所有表结构
- 5.查看锁表和解锁
一、ORACLE数据库
1.截取某特殊字符之间的字符串
select substr(test,instr(test,';',1,1)+1,instr(test,';',1,2)-1-instr(test,';',1,1)) from dual;--截取test字符串第一个 ; 到第二个 ; 之间的字符串；
select substr(test,instr(test,';',2,2)+1,instr(test,';',2,3)-1-instr(test,';',2,2)) from dual--截取test字符串第二个 ; 到第三个 ; 之间的字符串；
select REGEXP_SUBSTR(‘虹桥/西安’,’[^/]+’,1,1) from dual --输出：虹桥
select REGEXP_SUBSTR(‘虹桥/西安’,’[^/]+’,1,2) from dual --输出：西安
/*REGEXP_SUBSTR函数的四个参数为（从左到右）：要解析的字符串，正则表达式，字符串解析时的起始位置，拆分后数组里的第几个值（返回值）*/2.取日期字段中每个月第一天到最后一天
select to_char(to_date(substr(test,1,6)||01,'yyyymmdd'),'yyyymmdd') from dual---取test日期所在月份第一天并转成字符型；
select to_char(to_date(substr(test,1,6)||to_char(last_day(to_date(test,'yyyymmdd')),'dd'),'yyyymmdd'),'yyyymmdd') from dual---取test所在月最后一天并转换成字符型；3.decode函数经典应用
select id, decode(sign(score-85),1,'优秀',0,'优秀',-1,
decode(sign(score-70),1,'良好',0,'良好',-1,
decode(sign(score-60),1,'及格',0,'及格',-1,'不及格')))
from student；
//decode( 字段或字段的运算，值1，返回（运算）值1，值2，返回（运算）值2，其他（运算）值3 ）4.连接两个字符串，如果一个为空则返回有值的
select concat('Oracle','11g') from dual; ------Oracle11g5.返回字符串 每个单词首字母大写
select initcap('oracle universal installer') from dual;------Oracle Universal Installer6.返回字符串长度
select length('oitu') from dual;------ 47.从左边开始去除空格字符
select LTRIM(' 123',' ') from dual;------1238.从右边开始去除空格字符
select RTRIM('123 ',' ') from dual;------1239.去除左右所有空格
select TRIM(' 1 23 ') from dual;------1 2310.替代字符串中指定的字符为指定的字符
select replace (' 1 2 3 4 ',' ','a') from Dual;------a1a2a3a4a
//可用于将字符串中所有的空格替换成空值11.返回大于或等于n的 最小整数(向上取整)
select ceil(9.02) from dual;------1012.返回小于或等于n的 最小整数(向下取整)
select floor(9.62) from dual;------913.按照指定的精度进行四舍五入
select round(3.1415926,4) from dual;------3.141614.按照指定的精度进行截取一个数
select trunc(3.1415926,4) from dual;------3.141515.ORACLE中行转列和列转行的使用
-- 行转列 pivot
/*pivot（聚合函数 for 列名 in（类型）） ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询,如select distinct cuscode from custable */
with rlt_tmp as
(
select A,B,C,D
from TEST t
where 1 = 1
and t.date = '${StatDate}'
)
select * from rlt_tmp
pivot (max(D) for (B,C) in (
('Name1',1) AS 名称1,('Value1',1) AS 有效值1,
('Name2',2) AS 名称2,('Value2',2) AS 有效值2,
('Amount1',1) AS 额度1,('Amount2',2) AS 额度2,
('Amount3',3) AS 额度3,('Amount4',4) AS 额度4
))---D为需要转列的值，B，C为转列的条件
-- 列转行
/*
unpivot没有聚合函数，被转换的列可以用临时变量代替，如 ‘季度’，‘销售额’
表数据实例：Fruits
ID NAME Q1 Q2 Q3 Q4
1 苹果 1000 2000 3000 4000
2 橘子 5000 6000 7000 8000
3 香蕉 5500 6500 7500 8500
4 鸭梨 1500 2500 3500 4500
*/
select id,name,季度,销售额 from Fruits unpivot(销售 for 季度 in(Q1,Q2,Q3,Q4) )
/*输出：
ID NAME 季度 销售额
1 苹果 Q1 1000
1 苹果 Q2 2000
1 苹果 Q3 3000
1 苹果 Q4 4000
2 橘子 Q1 5000
2 橘子 Q2 6000
2 橘子 Q3 7000
2 橘子 Q4 8000
3 香蕉 Q1 5500
3 香蕉 Q2 6500
3 香蕉 Q3 7500
3 香蕉 Q4 8500
4 鸭梨 Q1 1500
4 鸭梨 Q2 2500
4 鸭梨 Q3 3500
4 鸭梨 Q4 4500
*/16.Oracle的connect by level的使用（可用于生成时间列表）
-- （1）获取连续的数据(注意：level只用使用<,<=,=符号)
select level from dual connect by level <= 5 -- 1 2 3 4 5
-- （2）获取连续的指定时间(注意：获取连续的时间需要包含当天需要再+1天)
select sysdate-level+1 days from dual connect by level <= 5-- 获取到当前日期往前倒推5个连续日期
--（3）获取某个日期到当期那日期的日期序列
SELECT (TO_DATE('20191001', 'yyyy-mm-dd') + LEVEL-1) CUR_DATE FROM DUAL
CONNECT BY LEVEL <= SYSDATE - TO_DATE('20191001', 'yyyy-mm-dd') + 1--获取19年10月1号到现在的日期
--（4）获取某个日期往前倒推的12个月份
select TO_CHAR(ADD_MONTHS(TO_DATE('2019-11-01', 'yyyy-MM-dd'),-LEVEL+1),'yyyy-MM') days
FROM dual CONNECT BY LEVEL <= 12 --获取到19年11月份往前推的12格月份日期
--（5）获取当前月往前推12个月的每个月月末的日期序列（不包含本月）
select to_char(last_day(ADD_MONTHS(sysdate,-LEVEL-1)),'yyyymmdd') days
FROM dual CONNECT BY LEVEL <= 12
--（6）获取当前日期往前推一个月的日期序列（截止昨天）
select to_char(add_months(trunc(sysdate),-1)+rownum-1,'yyyymmdd') as days
from dual connect by rownum<= trunc(sysdate-add_months(trunc(sysdate),-1))
--（7）当前月1号至昨天日期
select CUR_DATE from
(
SELECT DISTINCT TO_CHAR((TRUNC(SYSDATE, 'mm') + LEVEL-1),'yyyymmdd') CUR_DATE FROM DUAL CONNECT BY LEVEL <= SYSDATE - TRUNC(SYSDATE, 'mm')
) a order by CUR_DATE
--（8）当前年1月至当前月每月最后一天
select CUR_DATE from
(
SELECT DISTINCT TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'yyyy') + LEVEL-1),'yyyymmdd') CUR_DATE FROM DUAL CONNECT BY LEVEL <= SYSDATE - TRUNC(SYSDATE, 'yyyy') + 1
) a order by CUR_DATE
-----------------------------------------------------------------------17.开窗函数（over()）和分析函数（partition by）–可用于组内统计
/*1.分析函数可以在数据中进行分组，然后基于组进行统计某个值，每个组的每一行都可以返回一个值，通常解决group by 无法满足的分组情况；
2.通常情况下分析函数带有开窗函数，窗口字句不能单独出现，必须有order by子句*/
over（partition by class order by sroce）:按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区
over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。
over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行
--查询每个班第一名的成绩（rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来，row_number()只返回一个结果，所以如果同班有两个并列第一会有问题）
SELECT *
FROM (SELECT t.name,
t.class,
t.score,
RANK () OVER (PARTITION BY t.class ORDER BY t.score DESC) mm
FROM st_score t)
WHERE mm = 1;
--其他用法
sum() over(partition by … order by …)：求分组后的总和。
first_value() over(partition by … order by …)：求分组后的第一个。
last_value() over(partition by … order by …)：求分组后的最后一个。
sum() over(order by...rows between unbounded preceding and current row)：累计发生，统计本列截止到当前行数据的累加，实用于某列的滚动计算，unbounded ：不受限制的。
count() over(partition by … order by …)：求分组后的总数。
max() over(partition by … order by …)：求分组后的最大值。
min() over(partition by … order by …)：求分组后的最小值。
avg() over(partition by … order by …)：求分组后的平均值。
lag() over(partition by … order by …)：取出前n行数据。
lead() over(partition by … order by …)：取出后n行数据。18.listagg用法-----对分组后的数据按照一定的排序进行字符串连接
SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) FROM table GROUP BY aaa --，表示连接的分隔符
SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) over(partition by aaa) FROM table --和分析函数联合使用19.查询条数限制
--使用rownum
select * from(select rownum rn,t.a,t.b from test t order by t.a) where rn<5 --查询前5条
--使用fetch
select * from test order by a desc fetch first 1 rows only --根据a排序取第一条
select * from test order by a desc FETCH FIRST 10 PERCENT ROWS ONLY --取前10%的数据行，不包含相同结果
select * from test order by a desc FETCH FIRST 10 PERCENT ROWS ONLY WITH TIES --取前10%的数据行，包含相同结果
--使用OFFSET .... ROWS FETCH NEXT .. ROWS ONLY
select * from test order by a desc offset 10 rows fetch first 10 rows only--调过前10行，取接下来的10行20.SQL中使用正则表达式匹配需要的内容
1.查找源字符串中是否包含正则匹配内容，通常使用在where条件中：
REGEXP_LIKE(source_string,pattern,[match_parameter])
/*
①source_string:源字符串
②pattern:正则表达式
③匹配模式（(i:不区分大小写；c:区分大小写；n:允许使用可以匹配任意字符串的操作符；m:将x作为一个包含多行的字符串）
*/
SELECT
CASE WHEN
regexp_like('Why does a kid enjoy kidding with kids only?', 'kid(s|ding)*', 'i') THEN 'Match Found'
ELSE 'No Match Found'
END AS output
FROM dual; --output:Match Found 查找源字符串中是否包含 kid 、kids或者kidding 这三个字符串
2.正则匹配替换字符串：
REGEXP_REPLACE(source_string,pattern,replace_string,[position],[occurtence],[match_parameter])
/*
①source_string:输入的字符串
②pattern:正则表达式(匹配被替换内容)
③replace_string:替换的字符
④position:标识从第几个字符开始正则表达式匹配（默认为1）
⑤occurtence:标识第几个匹配组（默认为全部都替换掉）
⑥match_parameter:取值范围(i：大小写不敏感；c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符)
*/
SELECT REGEXP_REPLACE ('aAFGDGD123432CXSDFD','[0-9]+','QQQ') AS output FROM dual --output：aAFGDGDQQQCXSDFD
3.利用正则匹配进行字符串截取：
REGEXP_SUBSTR(source_string, pattern,[position],[occurrence]
,[match_parameter])
/*
①source_string:需要进行正则处理的字符串
②pattern:进行匹配的正则表达式
③position:起始位置，从第几个字符开始正则表达式匹配（默认为1）
④occurrence:标识第几个匹配组，默认为1
⑤match_parameter:模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
*/
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL--STR:17
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL--STR:23
4.REGEXP_INSTR(source_string,pattern,[start_position],[occurrence],[return_option],[match_parameter])
/*
①source_string:输入的字符串
②pattern:正则表达式(匹配被替换内容)
③start_position:标识从第几个字符开始正则表达式匹配（默认为1）
④occurrence:标识第几个匹配组。（默认为1）
⑤return_option:返回匹配字符出现的位置（0：返回第一个字符出现的位置。1：pattern下一个字符起始位置）
⑥match_parameter:取值范围(i：大小写不敏感；c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符)
*/
SELECT REGEXP_INSTR ('hello itmyhome', 'e') as num FROM dual --num:2
SELECT REGEXP_INSTR ('World filled with love', 'with', 1, 1, 0, 'i') as num FROM dual--num:14
SELECT REGEXP_INSTR ('my name is itmyhome', 'my', 10, 1, 0, 'i') as num
FROM dual --num:1421.TRUNC函数使用
---①处理日期
select trunc(sysdate) from dual --返回今天日期
select trunc(sysdate,'dd') from dual ---返回今天日期
select trunc(sysdate, 'mm') from dual ---返回当月第一天
select trunc(sysdate,'yy') from dual ---返回当年第一天
select trunc(sysdate,'yyyy') from dual ---返回当年第一天
---②处理数字
select trunc(123.458) from dual --123
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.458
select trunc(123) from dual --123
select trunc(123,1) from dual --123
select trunc(123,-1) from dual --12022.偏移分析函数 lag()与lead() 用法
/*
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。
lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。
lag(field, num, defaultvalue) field需要查找的字段，num往前查找的num行的数据，defaultvalue没有符合条件的默认值。
*/
select distinct datedate tt ,
lag(datedate , 1, null) over (order by datedate) tt1
from testtable
order by datedate asc --将testtable表中的datedate字段往前查找一个作为新的一列tt1
select distinct datedate tt ,
lead(datedate , 1, null) over (order by datedate) tt1
from testtable
order by datedate asc --将testtable表中的datedate字段往后查找一个作为新的一列tt1二、MYSQL数据库
1.存在同一列中逗号分隔转换成对应的行
SELECT help_topic_id FROM mysql.help_topic -- 从0开始的自增序列，最大到636
select substring_index(substring_index(a.contract_ids,',',b.xc),',',-1) as contractid
from
(select
"1,2,3,4,5" as contract_ids
from dual ) as a
join (SELECT @xi:=@xi+1 as xc from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 union SELECT 6) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 union SELECT 6) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 union SELECT 6) xc3,
(SELECT @xi:=0) xc4) as b
on b.xc <= (length(a.contract_ids) - length(replace(a.contract_ids,',',''))+1)
-- 输出：1
-- 2
-- 3
-- 4
-- 5
/*
①表b效果同mysql.help_topic，在表help_topic无权使用的情况下可以这样操作
②整行查询中，若某列为逗号分隔的存储，亦可使用该方式，转换为多行
*/三、SqlServer数据库
1.生成时间列表
SELECT convert(char(10), DATEADD(dd,number, dateadd(day, -3, getdate())),120) AS yyyymmdd
FROM master..spt_values
WHERE
type = 'p' AND DATEDIFF(MI,DATEADD(dd,number,dateadd(day, -3, getdate())),dateadd(day, 15, getdate())) >= 0 --获取当前日期前三天后15天的日期列表
select convert(varchar(10),dateadd(dd,number,convert(varchar(8),getdate(),120)+'01'),120) as dt
from master..spt_values
where type='P' and
dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')--获取当月所有日期列表2.获取全年日期列表
SELECT
dateadd (dd, number, '2020-01-01') date
FROM
master..spt_values -- 系统表
WHERE
type = 'p'
AND number >= 0
AND number < datediff (dd, '2020-01-01', '2021-01-01')2.获取2020年第一周所有日期
SELECT
date
FROM
(
SELECT
date
FROM
(
SELECT
dateadd (dd, number, '2020-01-01') date
FROM
master..spt_values -- 系统表
WHERE
type = 'p'
AND number >= 0
AND number < datediff (dd, '2020-01-01', '2021-01-01')
) t
WHERE
datepart (week, date) = 1
) a3.常用的当前时间的一些处理方法
select GETDATE() as '当前日期时间',
DateName(year,GetDate())+'-'+DateName(month,GetDate())+'-'+DateName(day,GetDate()) as '当前日期',
DateName(quarter,GetDate()) as '第几季度',
DateName(week,GetDate()) as '一年中的第几周',
DateName(DAYOFYEAR,GetDate()) as '一年中的第几天',
DateName(year,GetDate()) as '年',
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(hour,GetDate()) as '时',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒',
DateName(MILLISECOND,GetDate()) as '豪秒',
DateName(WEEKDAY,GetDate()) as '星期几'
select DATEADD(week,DATEDIFF(week,0,getdate()),0) 当前日期周一
select dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0)) 上周一
select dateadd(week,-2,DATEADD(week,DATEDIFF(week,0,getdate()),0)) 上上周一
select dateadd(week,-3,DATEADD(week,DATEDIFF(week,0,getdate()),0)) 上上周一
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2), 120) --当前周三
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2)+7, 120) --未来第一个周三
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2)+14, 120) --未来第二个周三
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2)+21, 120) --未来第三个周三
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2)+28, 120) --未来第四个周三
SELECT CONVERT(CHAR(10), DATEADD(WEEK, DATEDIFF(WEEK,2, GETDATE()), 2)+56, 120) --未来第八个周三
select dateadd(year,datediff(year,0,getdate()),0)--获取当前年的第一天的日期
select dateadd(year,datediff(year,-1,getdate()),-1)--获取当前年最后一天的日期
select dateadd(year,datediff(year,-0,getdate())-1,0) --获取前一年的第一天的日期
select dateadd(year,datediff(year,0,getdate()),-1)--获取前一年的最后一天的日期
--------------------------------------------------------------
2020-05-25 10:42:41.373 2020-05-25 2 22 146 2020 05 25 10 42 41 373 星期一
select GETDATE() as '当前日期时间',
DatePart(year,GetDate())+'-'+DatePart(month,GetDate())+'-'+DatePart(day,GetDate()) as '当前日期',
DatePart(quarter,GetDate()) as '第几季度',
DatePart(week,GetDate()) as '一年中的第几周',
DatePart(DAYOFYEAR,GetDate()) as '一年中的第几天',
DatePart(year,GetDate()) as '年',
DatePart(month,GetDate()) as '月',
DatePart(day,GetDate()) as '日',
DatePart(hour,GetDate()) as '时',
DatePart(minute,GetDate()) as '分',
DatePart(second,GetDate()) as '秒',
DatePart(MILLISECOND,GetDate()) as '豪秒',
DatePart(WEEKDAY,GetDate()) as '星期几'
2020-05-25 10:48:11.390 2050 2 22 146 2020 5 25 10 48 11 390 24.查询数据库所有表结构
SELECT obj.name 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
CASE WHEN col.isnullable = 1 THEN '1'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值,
Coalesce(epTwo.value, '') AS documentation
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name in(
SELECT
ob.name
FROM sys.objects AS ob
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = ob.object_id
AND ep.class = 1
AND ep.minor_id = 0
WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1
)
ORDER BY obj.name ;5.查看锁表和解锁
-- 查询被锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';
--参数说明 spid 锁表进程 ；tableName 被锁表名
-- 解锁语句 需要拿到spid然后杀掉缩表进程
declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
