sql语法总结

最新推荐文章于 2025-03-22 02:21:30 发布

原创最新推荐文章于 2025-03-22 02:21:30 发布 · 1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#sql

一、临时表with…as的用法

仅支持8.0以上的版本

创建一个临时表

with tmp as (select * from tb_name)

创建多个临时表

with
tmp1 as (select * from tb_name1),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3)

8.0以下的版本可用以下语句创建临时表

CREATE TEMPORARY TABLE  tmp_table SELECT * FROM table_name
#tmp_table临时表的表名

二、常用窗口函数

1、常用的排名函数

row_number每一行都会生成一个序号

select row_number() OVER(PARTITION BY [需要分组的名称] order by [排序的名称] desc) as row_num,* from [Order]

rank函数当结果相同时显示同一名次，下一名次时跳过已经排名的人数

select rank() OVER(PARTITION BY [需要分组的名称] order by [排序的名称] desc) as rank1,* from [Order]
#比如排名结果为1,1,3

dense_rank函数当结果相同时显示同一名次，下一名次时不跳过已经排名的人数

select dense_rank() OVER(PARTITION BY [需要分组的名称] order by [排序的名称] desc) as dense_rank1,* from [Order]
#比如排名结果为1,1,2

ntile函数可以对序号进行分组处理

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]

2、其他常用窗口函数

SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table

这里有3点需要牢记：

聚合功能：在上述例子中，我们用了SUM()，但是你也可以用COUNT(), AVG()之类的计算功能
PARTITION BY：你只需将它看成GROUP BY子句，但是在窗口函数中，你要写PARTITION BY
ORDER BY：ORDER BY和普通查询语句中的ORDER BY没什么不同。注意，输出的顺序要仔细考虑

3、窗口函数的优点

简单

窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。

快速

这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。

多功能性

最重要的是，窗口函数具有多种功能，本文并没有提及这个功能，比如，包括添加移动平均线，添加行号和滞后数据，等等。

三、文本函数

长度–length函数返回文本字段中值的长度

select length('abc')
#结果为3

连接–concat函数用于将两个或多个字符串连接起来，形成一个单一的字符串

group_concat函数用于分组拼接同一列的字符串

select concat('abc','def')
#结果为'abcdef'
select [分组的条件列],group_concat([需要竖向合并的列]) from [表名] group by [分组的条件列]

分割–SUBSTRING_INDEX函数用于将字符串依据某个指定分隔符进行切分，并返回指定位置分隔符前的字符。

select SUBSTRING_INDEX('180,78kg,67',',','1')
#结果为180
select SUBSTRING_INDEX('180,78kg,67',',','-1')
#结果为67
select SUBSTRING_INDEX(SUBSTRING_INDEX('180,78kg,67',',','2'),',','-1')
#结果为78kg

定位–instr放回字符在字符串中第一次出现的位置，从1开始，没有返回0

select instr('abcd','a')
#返回1
select instr('bcd','a')
#返回0

截取–substring函数截取字符串

select substring('abcd',2)
#返回'bcd'
select substring('abcd',2,2)
#返回'bc' 同mid函数

其他文本函数

lcase(s) #将字符串 s 的所有字母变成小写字母
LEFT(s,n) #返回字符串 s 的前 n 个字符
LOWER(s) #将字符串 s 的所有字母变成小写字母
LPAD(s1,len,s2) #在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
LTRIM(s) #去掉字符串 s 开始处的空格
REPEAT(s,n) #将字符串 s 重复 n 次
REPLACE(s,s1,s2)	#将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s)	 #将字符串s的顺序反过来
RIGHT(s,n)	#返回字符串 s 的后 n 个字符
RTRIM(s)	#去掉字符串 s 结尾处的空格
STRCMP(s1,s2)	 #比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
UPPER(s)	#将字符串转换为大写

四、日期函数

#日期类：
adddate(d,n) #计算起始日期 d 加上 n 天的日期
ADDTIME(t,n)	#时间 t 加上 n 秒的时间
CURDATE()	 #返回当前日期
CURRENT_TIMESTAMP()	 #返回当前日期和时间
DATE()	#从日期或日期时间表达式中提取日期值
DATEDIFF(d1,d2)	#计算日期 d1->d2 之间相隔的天数
DATE_FORMAT(d,f)	#按表达式 f的要求显示日期 d	f用“%Y%m%d%r”表示
DAY(d)	#返回日期值 d 的日期部分
DAYNAME(d)	#返回日期 d 是星期几，如 Monday,Tuesday
DAYOFMONTH(d)	 #计算日期 d 是本月的第几天
DAYOFWEEK(d)	#日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR(d)	#计算日期 d 是本年的第几天
LAST_DAY(d)	#返回给给定日期的那一月份的最后一天
LOCALTIMESTAMP()	#返回当前日期和时间
MONTH(d)	#返回日期d中的月份值，1 到 12
MONTHNAME(d)	#返回日期当中的月份名称，如 Janyary
QUARTER(d)	#返回日期d是第几季节，返回 1 到 4
PERIOD_DIFF(period1, period2)	 #返回两个时段之间的月份差值
STR_TO_DATE(string, format_mask)	#将字符串转变为日期
YEAR(d)	#返回年份
SUBDATE(d,n)	#日期 d 减去 n 天后的日期

#时间类
CURTIME()	 #返回当前时间
HOUR(t)	#返回 t 中的小时值
MAKETIME(hour, minute, second)	#组合时间，参数分别为小时、分钟、秒
MICROSECOND(date)	#返回日期参数所对应的微秒数
MINUTE(t)	#返回 t 中的分钟值
SECOND(t)	#返回 t 中的秒钟值
SEC_TO_TIME(s)	#将以秒为单位的时间 s 转换为时分秒的格式
SUBTIME(t,n)	#时间 t 减去 n 秒的时间
TIME_FORMAT(t,f)	#按表达式 f 的要求显示时间 t	，f可以用‘%r’表示
TIME_TO_SEC(t)	#将时间 t 转换为秒
TIMEDIFF(time1, time2)	#计算时间差值

五、数字函数

abs() #绝对值
ACOS() #反余弦值（参数是弧度）
asin() #反正弦值(参数是弧度)
atan(x) #反正切值(参数是弧度)
atan2(n,m) #反正切值(参数是弧度)
avg() #平均值
CEIL(x)	#返回大于或等于 x 的最小整数
cos() #余弦值（参数是弧度）
cot() #余切值（参数是弧度）
count() #计数
DEGREES(x)	#将弧度转换为角度	
n DIV m	#整除
EXP(x)	#返回 e 的 x 次方
FLOOR(x)	#返回小于或等于 x 的最大整数
GREATEST(expr1, expr2, expr3, ...)	#返回列表中的最大值
LEAST(expr1, expr2, expr3, ...)	#返回列表中的最小值
ln() #返回数字的自然对数
LOG(x)	#返回自然对数(以 e 为底的对数)
LOG10(x)	#返回以 10 为底的对数
LOG2(x)	 #返回以 2 为底的对数
min() #最小值
max() #最大值
mod(x,y) #返回 x 除以 y 以后的余数
PI()	#返回圆周率(3.141593)
POW(x,y)	#返回 x 的 y 次方
RADIANS(x)	#将角度转换为弧度
RAND()	#返回 0 到 1 的随机数
ROUND(x,n)	#返回离 x 最近的数，保留n位小数
SIGN(x)	#返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SIN(x)	#正弦值(参数是弧度)
SQRT(x)	#返回x的平方根
SUM()	#返回指定字段的总和
TAN(x)	#正切值(参数是弧度)
TRUNCATE(x,y)	#返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）