SQL常用语法

库表相关

show databases；显示所有的库

use mysql;进入其中一个库

show tables;展示所有的表

show tables from sys;站在一个库中查看新的库的所有表；

create table yaoyi (
Id int
name varchar(20)); 创建新的表，定义字段；

desc yaoyi;显示表的详细内容；

 
 

查询语句

substring_index

截取带有分隔符的字符串,分隔符的左边全部部分
substring_index(str,delim,count)

str:要处理的字符串
delim:分隔符
count:第几个分隔符 (如果是负数,比如-2,就是从右边数第二个分隔符的右边的全部部分)
e.g. substring_index(avg_play,’%’,1) >60 比如数据已经化成了百分比类型,还要having出大于60%的结果

end_time-start_time

得到的是两个时间的差值，但是进位制是100，不是60，所以不能用！！
e.g. 2021-10-02 10:59:05 2021-10-02 11:00:04 : 4099 ,相当于是110004-105905
2021-10-03 11:00:50 2021-10-03 11:01:35 ：85，相当于是110135-110050

TIMESTAMPDIFF

mysql:
–相差1天
select TIMESTAMPDIFF(DAY, ‘2018-03-20 23:59:00’, ‘2018-03-22 00:00:00’);
–相差49小时
select TIMESTAMPDIFF(HOUR, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
–相差2940分钟
select TIMESTAMPDIFF(MINUTE, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
–相差176400秒
select TIMESTAMPDIFF(SECOND, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);

Timediff

返回两个时间的时间差值
SELECT TIMEDIFF(‘2018-05-21 14:51:43’,‘2018-05-19 12:54:43’);# 49:57:00

datediff

mysql: 返回两个时间的天数差值，不能精确到时分秒
SELECT DATEDIFF(‘2018-05-07’,‘2018-05-09’) AS DiffDate;

sqlserver:
SELECTDATEDIFF( Second, ‘2009-8-25 12:15:12’, ‘2009-9-1 7:18:20’) --返回相差秒数
SELECTDATEDIFF( Minute, ‘2009-9-1 6:15:12’, ‘2009-9-1 7:18:20’) --返回相差分钟数
SELECTDATEDIFF( Day, ‘2009-8-25 12:15:12’,‘2009-9-1 7:18:20’)–返回相差的天数
SELECTDATEDIFF( Hour, ‘2009-8-25 12:15:12’, ‘2009-9-1 7:18:20’)–返回相差的小时

DATE_SUB

从某一日期减去时间间隔：
DATE_SUB(date,INTERVAL expr type)
SELECT DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate

Having

必须跟在group by后面
页面顶部

开窗函数

rows–行范围进行定义
SUM(sum_like_cnt) over(partition by tag order by dt rows 6 preceding) --该行以及前6行进行分区

sum(sum_like_cnt) over(partition by tag order by dt rows 6 following) --不能使用了，本来是该行以及后6行进行分区运算，替换方法：

ROWS BETWEEN 0 PRECEDING AND 6 FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW --第一行至当前行
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING --表示指定取值范围：当前行＋前一行＋后两行的值

range–值范围进行定义
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-若order by 字段的某两行是相同的，那么得出的运算值也是相同的
-区分于：rows --> 若order by 字段的某两行是相同的，那么得出的运算值也是不同的

MYsql的自联结（用于实现开窗函数的效果）

mysql如何实现开窗函数？
现有表为：

通过增加序号列的方式实现自联结的约束，让下一行与上一行对齐

# 如果不太理解的话就用增加列的方式，ranknum()，也是一样的
select
	A.user_id
	,A.punch_time
	,TIMESTAMPDIFF(SECOND,A.punch_time,B.punch_time) sub_seconds
from
	(
	    select a.*
	    		,(@i := @i + 1) as ord_num 
	    from t_punch_cade a
	    	,(select @i := 1) d 
	    order by user_id,punch_time
	) as A 
	LEFT JOIN 
	(
	    select a.*
	    ,(@j := @j + 1) as ord_num from t_punch_cade a
	    ,(select @j := 0) c order by user_id,punch_time
	)as B 
	on A.ord_num=B.ord_num and A.user_id=B.user_id

中位数、均值、众数

众数：having all（count…   group by…）
均值：avg
中位数：

两行间隔的差值

有日期：datediff来连接
没有日期：分别生成以1 开始和以2 开始的排序，然后依据序号自连接on一下，将长数据转换成宽数据格式

长宽数据转换

长–>宽：left join select出想要的每一列，然后join
宽–>长：select出想要的列，以相同的方式命列名，然后union all