Postgres SQL常用语句

最新推荐文章于 2024-03-05 10:53:22 发布

Nate_7090

最新推荐文章于 2024-03-05 10:53:22 发布

阅读量1.2k

点赞数 1

分类专栏： postgreql 文章标签： postgresql

postgreql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

下面记录一些常用的Postgres SQL语句，供大家学习

一，时间操作

（1）提取时间中的精度

   
   EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');--抽取世纪 20
EXTRACT(DAY  from  TIMESTAMP '2001-02-16 20:38:40');--抽取天 16
EXTRACT(DECADE  from  TIMESTAMP '2001-02-16 20:38:40');--年份域除以 10  ,200
EXTRACT(DOW  FROM  TIMESTAMP '2001-02-16 20:38:40');--每周的星期号(0-6；星期天是 0) (仅用于timestamp)
EXTRACT(DOY  from  TIMESTAMP '2001-02-16 20:38:40'); --一年的第几天(1 -365/366) (仅用于 timestamp)
EXTRACT(HOUR  from  TIMESTAMP '2001-02-16 20:38:40');--小时域(0-23)
EXTRACT(MICROSECONDS from TIME '17:12:28.5');--秒域,包括小数部分,乘以 1,000,000。
EXTRACT(MILLENNIUM  from TIMESTAMP '2001-02-16 20:38:40');--千年 3
EXTRACT(MILLISECONDS  from  TIME'17:12:28.5');--秒域,包括小数部分,乘以 1000。28500
EXTRACT(MINUTE  from  TIMESTAMP '2001-02-16 20:38:40');--分钟域(0-59)
EXTRACT(MONTH  from  TIMESTAMP '2001-02-16 20:38:40');--对于 timestamp 数值,它是一年里的月份数(1-12)；对于 interval 数值,它是月的数目,然后对 12 取模(0-11)
EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');--该 天 所 在 的 该 年 的 季 度 (1-4)( 仅 用 于timestamp)
EXTRACT(SECOND  from  TIMESTAMP '2001-02-16 20:38:40');--秒域,包括小数部分(0-59[1])
EXTRACT(WEEK  from  TIMESTAMP '2001-02-16 20:38:40');---该天在所在的年份里是第几周
EXTRACT(YEAR  from  TIMESTAMP '2001-02-16 20:38:40');--年份域

（2）将日期截断为指定精度的日期

   
   date_trunc('hour', timestamp '2014-09-26 08:10:40');--20104-9-26 08:00:00
date_trunc('day', timestamp '2014-09-26 08:10:40');--2014-09-26 00:00:00
date_trunc('week', timestamp '2014-09-26 08:10:40');--2014-09-22 00:00:00 礼拜一
date_trunc('month', timestamp '2014-09-26 08:10:40');--2014-09-00 00:00:00
date_trunc	('year', timestamp '2014-09-26 08:10:40');--2014-00-00 00:00:00

（3）时间运算

   
   date '2001-09-28' + integer '7'  	=》	date '2001-10-05'
date '2001-09-28' + interval '1 hour' 	=》	timestamp '2001-09-28 01:00'
date '2001-09-28' + time '03:00' 	=》	timestamp '2001-09-28 03:00'
interval '1 day' + interval '1 hour' =》	interval '1 day 01:00'
timestamp '2001-09-28 01:00' + interval '23 hours' =》timestamp '2001-09-29 00:00'
time '01:00' + interval '3 hours' =》time '04:00'
- interval '23 hours'	=》interval '-23:00'
date '2001-10-01' - date '2001-09-28'	=》integer '3'
date '2001-10-01' - integer '7'  =》date '2001-09-24'
date '2001-09-28' - interval '1 hour'=》timestamp '2001-09-27 23:00'
time '05:00' - time '03:00' =》interval '02:00'
time '05:00' - interval '2 hours'	=》time '03:00'
timestamp '2001-09-28 23:00' - interval '23 hours'	=》timestamp '2001-09-28 00:00'
interval '1 day' - interval '1 hour'=》interval '23:00'
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' =》interval '1 day 15:00'
interval '1 hour' * double precision '3.5' =》interval '03:30'
interval '1 hour' / double precision '1.5'	=》interval '00:40'

二，字符串类

（1）常用函数

   
   根据delimiter分隔 string
split_part('abc~@~def~@~ghi', '~@~',2)	=>def
select substring('steps:97573' from 7)
 
字串连接 
'Post' || 'greSQL'  =》PostgreSQL
 
字串里二进制位的个
bit_length('jose')	=》32
 
字串中的字符个数
char_length('jose')	=》4
 
把字串转化为小
lower('TOM')  =》tom
 
把字串转化为大写
upper('tom')  =》TOM
 
字串中的字节
octet_length('jose') =>4
 
替换子字串
overlay('Txxxxas' placing 'hom' from 2 for 4) 	=>Thomas
 
指定的子字串的位  
position('om' in 'Thomas')	=>3
 
抽取子字串	
substring('Thomas' from 2 for 3)   =>hom
 
抽取子字串
substr('alphabet',3, 2)	=>ph
 
抽取匹配 POSIX 正则表达式的子字串	
substring('Thomas' from '...$')	=>mas
 
抽取匹配SQL正则表达式的子字串
substring('Thomas' from '%#"o_a#"_' for '#')  =>oma
 
参数第一个字符的 ASCII 码
ascii('x')	=》120
 
给出 ASCII 码的字
chr(65)	=》A
 
把字串string里出现地所有子字串from 替换成子字串 to。 
replace('abcdefabcdef', 'cd', 'XX')   =>abXXefabXXef
 
从字串 string 的 开头/结尾/两边/ 删除只包含 characters （缺省是一个空白）的最长的字串 
trim(both 'x' from 'xTomxx')  =>Tom
 
从 string 开头和结尾删除只包含在characters里（缺省是空白）的字符的最长字串
btrim('xyxtrimyyx','xy') =》trim
 
把每个单词的第一个子母转为大写,其它的保留小写。 单词是一系列字母数字组成的字符,用非字母数字分隔。
initcap('hi thomas') =》Hi Thomas
 
string 中字符的数目		
length('jose')	=》4

（2）格式化

函数	返回类型	描述	例子
to_char(timestamp, text)	text	把时间戳转换成字串	to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)	text	把时间间隔转为字串	to_char(interval'15h 2m 12s','HH24:MI:SS')
to_char(int,text)	text	把整数转换成字串	to_char(125, '999')
to_char(doubleprecision, text)	text	把实数/双精度数转换成字串	to_char(125.8::real,'999D9')
to_char(numeric,text)	text	把 numeric转换成字串	to_char(-125.8,'S999D99')
to_date(text,text)	date	把字串转换成日期	to_date('05 Dec 2000','DD Mon YYYY')
to_timestamp(text, text)	timestamp	把字串转换成时间戳	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision)	timestamp	把 UNIX 纪元转换成时间戳	to_timestamp(200120400)

三，其他

（1）条件表达式

   
   CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END

（2）COALESCE

   
   COALESCE(value[, ...])	返回它的第一个非 NULL 的参数的值。

（3）NULLIF

   
   NULLIF(value1, value2) 	当且仅当 value1 和 value2 相等时,NULLIF 才返回 NULL。 否则它返回 value1

（4）GREATEST 和 LEAST

   
   GREATEST 和 LEAST 函数从一个任意的数字表达式列表里选取最大或者最小的数值。 这些表达式必须都可以转换成一个普通的数据类型,它将会是结果类型
列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候,结果才会是 NULL
GREATEST(value [, ...])
LEAST(value [, ...])

（5）集合的函数

generate_series(start, stop)
generate_series(start, stop, step)

   
   select generate_series(1,10);
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)
 
select generate_series(1,10,3);
 generate_series 
-----------------
               1
               4
               7
              10
(4 rows)
 
select generate_series(5,1,-1);
 generate_series 
-----------------
               5
               4
               3
               2
               1
(5 rows) 
 
2.时间类型
select generate_series(now(),now() + '7 day','1 day');
        generate_series        
-------------------------------
 2012-08-27 22:12:40.915368+08
 2012-08-28 22:12:40.915368+08
 2012-08-29 22:12:40.915368+08
 2012-08-30 22:12:40.915368+08
 2012-08-31 22:12:40.915368+08
 2012-09-01 22:12:40.915368+08
 2012-09-02 22:12:40.915368+08
 2012-09-03 22:12:40.915368+08
(8 rows)
 
select generate_series(to_date('20120827','yyyymmdd'),to_date('20120828','yyyymmdd'),'3 h');
generate_series     
------------------------
 2012-08-27 00:00:00+08
 2012-08-27 03:00:00+08
 2012-08-27 06:00:00+08
 2012-08-27 09:00:00+08
 2012-08-27 12:00:00+08
 2012-08-27 15:00:00+08
 2012-08-27 18:00:00+08
 2012-08-27 21:00:00+08
 2012-08-28 00:00:00+08
(9 rows)

（6）用WITH构造虚拟表，提高性能

   
   WITH running_tmp AS(
	select * from running
)
SELECT * FROM running_tmp;

转载请注明出处: http://bianmaren.com/u/1/blog/detail/11

Nate_7090

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Postgres SQL常用语句

下面记录一些常用的Postgres SQL语句，供大家学习一，时间操作（1）提取时间中的精度EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');--抽取世纪 20EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');--抽取天 16EXTRACT(DECADE
复制链接

扫一扫

专栏目录