sql常用功能实现

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/luanpeng825485697/article/details/80503053

全栈工程师开发手册 (作者:栾鹏)
数据架构师全解

条件语句

注意:if else和case when语句都是一个新的列,

if else语句

格式:IF( expr1 , expr2 , expr3 )

expr1 的值为 TRUE,则返回值为 expr2
expr1 的值为FALSE,则返回值为 expr3

select u.id,if(u.sex=1,'男','女') sexstr from user u;

case when语句

格式为case ..when .. then..when..then..else..end

实际相当于java中的switch case

case expr1
  when 'value1' then 'str1'
  when 'value2' then 'str2else 'strn' end

例如

select id,
       case when t.diarytype=0 then t.imgtwo
            when t.diarytype=1 then t.imgone 
            else t.imgthree end as info 
from diary t

查询表的所有列名

其实读者可以自己去看看information_schema.columns数据表中包含的内容,就知道怎么查找了

select column_name from information_schema.columns where table_name='diary'

join语句的使用

Table A
aid   adate 
1      a1 
1      a1_1
2      a2 
3      a3


TableB
bid bdate 
1    b1 
1    b1_1 
2    b2 
2    b2_1 
4    b4 

比如表A 中每行为一个用户的签到时间,包含用户id和签到时间,每个用户包含多个签到时间
表B每行为用户的签退时间,包含用户id和签退时间,每个用户包含多个签退时间。

left join 关键字

就是将左表包含的所有对象的所有可能的特征(特征来自与左表和右表)组合统计出来。
所以left join是获取A表中的用户所有的签到签退时间组合特征。

其操作过程就是遍历左表中的每一个记录,看看在右表中是否有能与其匹配的,将左表的这一条记录和右表所有匹配的n个记录合并成新的n个记录,再遍历左表的下一个记录。

select a.id,a.adate,b.bdate from a left join b on a.id = b.id
id      adate       bdate
1       a1          b1
1       a1          b1_1
1       a1_1        b1
1       a1_1        b1_1
2       a2          b2
2       a2          b2_1
3       a3          NULL

right join 关键字

就是将右表包含的所有对象的所有可能的特征(特征来自与左表和右表)组合统计出来。

所以right join是获取获取B表中的用户所有的签到签退时间组合特征

select b.id,a.adate,b.bdate from a right join b on a.id = b.id
id      adate       bdate
1       a1          b1_1
1       a1_1        b1_1
1       a1          b1
1       a1_1        b1
2       a2          b2
2       a2          b2_1
4       NULL        b4  

inner join 关键字

就是将左右表中同时包含的对象的所有可能的特征(特征来自与左表和右表)组合统计出来。

select a.id,a.adate,b.bdate from a inner join b on a.id = b.id
id      adate       bdate
1       a1          b1_1
1       a1_1        b1_1
1       a1          b1
1       a1_1        b1
2       a2          b2
2       a2          b2_1

字符串操作

拼接字符串

直接使用+号链接就行。

2018+'-'+16+'-'+18  结果为'2018-16-18'

连接查询内容成字符串concat

select concat(name,'aaa')  from student;  

字符串反序 reverse

select reverse(name) as newname from user

获取字符串长度length

select length(name) as name_len from user

截取字符串substr

substr(string string,num start,num length);

string为字符串;

start为起始位置;

length为长度。

区别:

mysql中的start是从1开始的,而hibernate中的start是从0开始的。

-- mydate是yyyy-mm-dd格式的字符串
select userid,substr(mydate,9,2) as day_of_month from diary

从左边截取left

left 从左边截取(被截取字段,截取长度)

-- mydate是yyyy-mm-dd格式的字符串。
select left(mydate,4) as year from diary

从右边截取right

right从右边截取(被截取字段,截取长度)

-- mydate是yyyy-mm-dd格式的字符串。
select right(mydate,2) as day from diary

获取子字符串的位置

instr(字段名, 子字符串)

这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0,否则返回位置(从1开始)

-- mydate是yyyy-mm-dd格式的字符串。查询2018年的记录
select * from diary order by instr(mydate,2018+'-')>0 DESC

-- mydate是yyyy-mm-dd或yy-mm-dd或yyyymmdd格式的字符串。通过下面可以获取-出现的位置,进一步判断是那种格式
select instr(mydate,'-') from diary

locate(子字符串,字段名)

这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0,否则返回位置(从1开始)。
从后向前查找需要先reverse字符串

-- imgone是upload/img/2018_06/PHY3RP352264.jpg的地址。获取文件名长度(不包含格式)
select locate('/',reverse(imgone))-locate('.',reverse(imgone))-1 as imgfile_len from diary where imgone is not null;

转换数据类型cast()和convert()

相同点:都是进行数据类型转换,实现的功能基本等同

不同点:两者的语法不同,cast(value as type) 、 convert(value,type)

所支持的类型如下:

    二进制,同带binary前缀的效果 : BINARY

    字符型,可带参数 : CHAR()

    日期 : DATE

    时间: TIME

    日期时间型 : DATETIME

    浮点数 : DECIMAL

    整数 : SIGNED

    无符号整数 : UNSIGNED
-- mydate是yyyy-mm-dd格式的字符串
select userid,cast(substr(mydate,9,2) as SIGNED) as day_of_month from diary

-- mydate是yyyy-mm-dd格式的字符串
select userid,convert(substr(mydate,9,2),SIGNED) as day_of_month from diary


需要注意的是:下公式得到的结果是201,并不会报错。这两个函数是从首部搜索,遇到阻碍就停止。
select cast('201p-21' as SIGNED) as day_of_month from diary
select convert('201p-21',SIGNED) as day_of_month from diary

日期相关操作

日期函数

1.   当前系统日期、时间
     select getdate()  

2. dateadd   在向指定日期加上一段时间的基础上,返回新的 datetime 值
    例如:向日期加上2select dateadd(day,2,'2004-10-15')   –返回:2004-10-17 00:00:00.000

3. datediff 返回跨两个指定日期的日期和时间边界数。
    select datediff(day,'2004-09-01′,'2004-09-18′)    –返回:17

4. datepart 返回代表指定日期的指定日期部分的整数。
   SELECT DATEPART(month, '2004-10-15′)   –返回 10

5. datename 返回代表指定日期的指定日期部分的字符串
    SELECT datename(weekday, '2004-10-15′)   –返回:星期五

6. day(), month(),year() –可以与datepart对照一下
 select 当前日期=convert(varchar(10),getdate(),120),当前时=convert(varchar(8),getdate(),114)

select datename(dw,'2004-10-15′)

select 本年第多少周=datename(week,'2004-10-15′),今天是周几=datename(weekday,'2004-10-15′)

oracle 当前日期为当年第几周,几天,几月,几季度;

select to_char(TO_DATE('20170101', 'YYYYMMDD'), 'iw') as week, --oracle求当年的第几周(如果1号不是周一,则1号所在的周为去年的最后一周,下一周为今年的第一周。)
      to_char(TO_DATE('20170101', 'YYYYMMDD'), 'ww') as week2, --oracle求当年的第几周(按照7天一个轮回。求第几个轮回,从1开始)
      to_char(TO_DATE('20170101', 'YYYYMMDD'), 'yyyy') as year, --oracle求第几年
      to_char(TO_DATE('20170101', 'YYYYMMDD'), 'mm') as month, --oracle求当年的第几月
      to_char(TO_DATE('20170101', 'YYYYMMDD'), 'ddd') as day, --oracle求当年的第几天
      to_char(TO_DATE('20170401', 'YYYYMMDD'), 'q') as quarter -- oracle求当年的第几季度
 from diary

to_char格式说明:

Year:
yy 两位年 显示值:07
yyy 三位年 显示值:007
yyyy 四位年 显示值:2007
Month:
mm 两位月 显示值:11
mon abbreviated 字符集表示 显示值:11月,若是英文版,显示nov
month 字符集表示 显示值:11月,若是英文版,显示november
Day:
d 当周第几天,按照7天一个轮回,不是星期几。
dd 当月第几天 显示值:02
ddd 当年第几天 显示值:02
dy 当周第几天简写 显示值:星期五,若是英文版,显示fri
day 当周第几天全写 显示值:星期五,若是英文版,显示friday
ddspth 当周第几天,英文second
Hour:
hh 12小时进制 显示值:01
hh24 24小时进制 显示值:13
Minute:
mi 60进制 显示值:45
Second:
ss  60进制 显示值:25
其它
Q 季度 显示值:4
WW 当年第几周 显示值:44
W 当月第几周 显示值:1

统计日期是星期几

-- mydate是日期格式的列。MYSQL用法
select userid,weekday(mydate) as day_of_week from diary   

-- mydate是yyyy-mm-dd格式的字符串。MYSQL用法。
select userid,weekday(str_to_date(mydate,"%Y-%m-%d")) as day_of_week from diary



%Y:代表4位的年份
%y:代表2为的年份

%m:代表月, 格式为(01……12)  
%c:代表月, 格式为(1……12)

%d:代表月份中的天数,格式为(00……31)  
%e:代表月份中的天数, 格式为(0……31) 

%H:代表小时,格式为(00……23)  
%k:代表 小时,格式为(0……23)  
%h: 代表小时,格式为(01……12)  
%I: 代表小时,格式为(01……12)  
%l :代表小时,格式为(1……12)

%i: 代表分钟, 格式为(00……59) 【只有这一个代表分钟,大写的I 不代表分钟代表小时】

%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)  
%T:代表 时间,格式为24 小时(hh:mm:ss) 

%S:代表 秒,格式为(00……59)  
%s:代表 秒,格式为(00……59) 

统计距离某一天之间差别的天数

-- mydate是yyyy-mm-dd格式的字符串。mysql语法。结果可能为负
select userid,datediff(str_to_date(mydate,"%Y-%m-%d"),str_to_date("2016-06-30","%Y-%m-%d")) as days_distance from diary

-- mydate是yyyy-mm-dd格式的字符串。mysql语法。结果可能为负
select userid,datediff(mydate,'2017-06-30') as days_distance from diary

统计日期是一个月的哪一天

这里写代码片

根据日期查询,本周,本月,本年,今日相关记录

昨天
select * from tb where datediff(day, 时间字段 ,getdate()) = 1
今天
select * from tb where datediff(day, 时间字段 ,getdate()) = 0
本周
select * from tb where datediff(week, 时间字段 ,getdate()) = 0
上周
select * from tb where datediff(week, 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week, 时间字段 ,getdate()) = -1

上月
Select * From TableName Where DateDiff(month, DateTimCol, GetDate()) = 1
本月
Select * From TableName Where DateDiff(month, DateTimCol, GetDate()) = 0
下月
Select * From TableName Where DateDiff(month, GetDate(), DateTimCol ) = -1
本年
Select * From TableName Where DateDiff(year, GetDate(), DateTimCol ) = 0

去重distinct

统计每个用户活跃的天数。所以要求用户和日期组合去重

-- mydate是yyyy-mm-dd格式的日期。
select userid,count(*) as action_days from
(
    select distinct userid,mydate from diary
)t group by userid;

分组计算个数、均值、求和、最大值、最小值、均值

分组函数为group by key,经过group by操作后,每个key列是不重复的。相当于去重操作了。

统计数量

例如统计每个用户每天发布的日志数量

select userid,mydate,count(1) as num from
(
    select userid,mydate from diary
)t 
group by userid,mydate;

求和

这里通过求和实现统计个数功能相同,新建一列cnt值为1,再分组求和,等价于求个数。

select userid,sum(cnt) as diary_count from
(
    select userid,1 as cnt from diary where userid is not null
)t 
group by userid;

求个数、求和、最大值、最小值、均值、极差

select userid,count(1) as diary_count,sum(money) as total_money,min(money) as min_money,max(money) as max_money,avg(money) as avg_money,max(money)-min(money) as range_money from
(
    select userid,money from diary where userid is not null and money is not null
)t 
group by userid;

分区partition by(Oracle中才有)

partition by 与group by不同之处在于前者返回的是分组里的每一条数据,并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

分区排序row_number() over

用于给数据库表中的记录进行标号,在使用的时候,其后还跟着一个函数 over(),而函数 over() 的作用是将表中的记录进行分组和排序。

说明:返回结果集分区内行的序列号,每个分区的第一行从1开始。
语法:row_number() over ([ <partition_by_clause> ] <order_by_clause>)
备注:order by 子句可确定在特定分区中为行分配唯一 row_number 的顺序。
参数:<partition_by_clause> :将 from 子句生成的结果集划入应用了 row_number 函数的分区。
<order_by_clause>:确定将 row_number 值分配给分区中的行的顺序。
返回类型:bigint 。

order by 表示排序

-- mysql中没有row_number()函数。下列实现每个用户按日期排序后顺序
select diaryid,mydate,row_number() over(partition by userid order by mydate desc) as order_index from diary

row_number() 顺序排序
rank() over 跳跃排序,如果有两个第一级别时,接下来是第三级别
dense_rank() over (连续排序,如果有两个第一级别时,接下来是第二级)

展开阅读全文

没有更多推荐了,返回首页