SQL常用函数

目录

 

1.截断字符串: substr(x,y,z)

2.计数：count(column_name)

3. 获得字符串x的长度: length(x)

4.修改表名

5.分页查询: limit offset  

6.选择关系中某一条或几条元组进行显示：limit  m,n

7.将分组聚集后的一组中的数据连接起来: group_concat(x,y)

8.插入数据

9.元组数据拼接

10.四舍五入round(x,y)

11.不四舍五入，数字统统截断trunc(x,y)/truncate(x,y)

12.类型转换cast(a as b)

13.随机显示数据 newid()/rand()/random()

14.自定义函数create function

15.返回当前日期now()/getdate()

16.计算日期差age(a,b)

17.截断日期年月日extract(field from interval)/date_part(text, interval)

18.截断日期向前规约date_trunc(x,y)

18.汇总统计rollup(a,b)

19.按照某个属性排序然后自动生成行号row_number() over( order by x)

20.按照某个属性进行排名并自动生成排名序号rank() over(order by x)/dense_rank() over()

21.按照某个属性进行排序并按照排序分组ntile(x) over(order by y)

22.日期相减interval-interval

23.生成插入数据generate_series(begin,end,step)

24.将字符串转换成大写upper(x)

25.递归with recursive

---

1.截断字符串: substr(x,y,z)

x:要截取的字符串

y:截断起始位置（字符索引从1开始），若为负数则从倒数第y个开始

z:要截取的字符串的长度，若省略则截到末尾

2.计数：count(column_name)

column_name:列名

可使用 count(distinct column_name)先去一次重再计数

该函数计算指定列名出现几次，若使用count(*)则计算表中有多少行

3. 获得字符串x的长度: length(x)

x:列名

4.修改表名

alter table 旧表名 rename to 新表名

5.分页查询: limit <M> offset <N> 

M:pageSize   限制每页显示数据数量为M

N:pageSize(pageIndex-1): 从第几条数据开始查询

如：limit 5 offset 5 即每页限制为五条数据，并从第二页开始看起(五条数据为一页）

该子句要写在最后

6.选择关系中某一条或几条元组进行显示：limit  m,n

从第m+1条数据开始,显示n条数据

7.将分组聚集后的一组中的数据连接起来: group_concat(x,y)

该函数为聚集函数，配合group by 使用

x:要连接的字段，即分组聚集后的一组中的数据

y:连接时使用到的符号

8.插入数据

直接插入

INSERT INTO 表名 VALUES(...);

如果不存在则插入，否则忽略

INSERT OR IGNORE INTO 表名 VALUES(...);

如果不存在则插入，否则替换

INSERT OR REPLACE INTO 表名 VALUES(...);

9.元组数据拼接

不同的DBMS有不同的语法或者函数，下面列几个常用的，详情请查看官方技术手册

sqlserver：直接使用+

oracle/SQLite/postgresql:使用||

mysql:使用concat(a,b),a,b为参数

10.四舍五入round(x,y)

x:要处理的数字

y:要保留的小数的位数，如果为负数，则从个位开始往前推

对小数按规定的位数进行四舍五入处理

11.不四舍五入，数字统统截断trunc(x,y)/truncate(x,y)

x:要处理的数字

y:要保留的小数的位数，如果为负数，则从个位开始往前推，直接将对应位数变为0

同样是对数字进行截断处理，只不过不四舍五入而是统统截断

有的数据库使用的是truncate()函数

12.类型转换cast(a as b)

a:转换前的类型

b:转换后的类型

可用于整数，浮点数，字符串的相互转换.

postgresql中还有这样一种语法a::b,将a转换为b

若转换为sring,可使用text这种类型

13.随机显示数据 newid()/rand()/random()

随机显示数据是一个常见需求，常用于各种item推荐

原理是对每一个数据随机生成一个唯一标识符,然后按照标识符进行排序，再显示排序后的数据就相当于对原数据进行了随机显示

不同数据库使用的函数不同

sqlServer:newid()         mysql:rand()          postgresql:random()

使用时的格式为 select * from tanle order by newid()/rand()/random() limit ..

14.自定义函数create function

不同的数据库创建函数的格式不太一样

postgreSql:

CREATE FUNCTION function_name(p1 type, p2 type)
 RETURNS type AS 
$$
BEGIN
 -- 逻辑
END;
$$
LANGUAGE language_name;

其中type多种多样，除基本类型外还可以返回table(数据表),逻辑内必须有return,如果使用pgSql,那么language_name就是plpgsql

参考:https://pg.sjk66.com/stored-procedure/create-function.html

15.返回当前日期now()/getdate()

now()返回当前日期，日期格式多种多样，有timestamp,date,interval等

如果使用 Sql Server 数据库，则使用getdate() 函数

interval参考:https://pg.sjk66.com/postgresql/interval.html

16.计算日期差age(a,b)

a:timestamp,一个日期格式

b:timestamp，一个日期格式

返回一个interval,一个日期描述，形如 43 years 9 mons 27 days，通常要和日期格式化函数配合使用

17.截断日期年月日extract(field from interval)/date_part(text, interval)

field:可以为year/month/day/week/hour/epoch(总秒域)等，表示要抽取的段

interval:日期描述

date_part()与extract()功能相同，两者都返回一个double

18.截断日期向前规约date_trunc(x,y)

x:模式

y:日期

date_trunc(text, timestamp)

timestamp

截断成指定的精度；向前规约

date_trunc('hour', timestamp '2001-02-16 20:38:40')

2001-02-16 20:00:00

18.汇总统计rollup(a,b)

用于group by 时使用，格式为 group by rollup(a,b...)

即先对a汇总统计，在对b汇总统计..最后汇总整个统计，统计是空出的字段为null

19.按照某个属性排序然后自动生成行号row_number() over( order by x)

x:要排序的属性

例:select row_number() over (order by joindate) as row_number,firstname,surname from members

20.按照某个属性进行排名并自动生成排名序号rank() over(order by x)/dense_rank() over()

x:要排序的属性，分组属性(使用聚集函数)也可以使用

例:
select facid, total from (
    select facid, sum(slots) total, rank() over (order by sum(slots) desc) as rank
            from bookings
        group by facid
    ) as t1
    where rank = 1  

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

21.按照某个属性进行排序并按照排序分组ntile(x) over(order by y)

x:要分成几组

y:要排序的属性

 ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记 录生成的序号就是这条记录所有的数组元素的索引（从1开始）。

例:select name, (case when class=1 then 'high'
        when class=2 then 'average'
        else 'low'
        end)as revenue
    from (
        select name as name, ntile(3) over (order by sum(case
                when memid = 0 then slots * guestcost
                else slots * membercost
            end) desc) as class
        from cd.bookings bks
        natural join cd.facilities 
        group by name
    ) as lsl
order by class, name        

22.日期相减interval-interval

两个interval相减直接返回天数

23.生成插入数据generate_series(begin,end,step)

函数	参数类型	返回类型	描述
generate_series(start, stop)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为一
generate_series(start, stop, step)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为step
generate_series(start, stop, step_interval)	timestamp or timestamp with time zone	timestamp 或 timestamp with time zone(same as argument type)	生成一个数值序列，从start 到 stop，步进为step

 

 

 

 

 

 

strp_interval可以是'1 day','1 month'等数值

24.将字符串转换成大写upper(x)

要转换的字符串

25.递归with recursive

recursive是关键字，用with定义的临时表可在表中查询使用，使用union/union all连接数据

例：/*递归：临时表内可以使用当前临时表,每次递归使用上一层数据*/
with recursive lsl as
(select recommendedby as recommender
 from members
 where memid=27
 union all
 select recommendedby as recommender
 from members as tmp join lsl on tmp.memid=lsl.recommender
 )
 select memid as recommender,firstname,surname 
 from members
 where memid in(select * from lsl)
 order by recommender desc