PostgreSQL SQL Base

SQL基礎

數據類型

一、复合类型

https://pan.baidu.com/s/1fC7YlPgUgHBMEMQKPE_TYg

二、Serial类型

对于一个表的主键可以使用Serial数据类型自动生成自增主键
当一个数据类型被定义为Serial数据类型后，其默认值为nextval(“tablename_columnname_seq”)

序列的操作
select currval(‘tablename_columnname_seq’) 获取当前自增主键的值
select nextval(‘tablename_columnname_seq’) 手动自增主键
select setval(‘tablename_columnname_seq’, value) 手动设置自增主键的当前值

三、日期時間

date

例子 描述
1999-01-08 ISO 8601; 任何模式下的1月8日 （推荐格式）
January 8, 1999 在任何datestyle输入模式下都无歧义
1/8/1999 MDY模式中的1月8日；DMY模式中的8月1日
1/18/1999 MDY模式中的1月18日；在其他模式中被拒绝
01/02/03 MDY模式中的2003年1月2日； DMY模式中的2003年2月1日； YMD模式中的2001年2月3日
1999-Jan-08 任何模式下的1月8日
Jan-08-1999 任何模式下的1月8日
08-Jan-1999 任何模式下的1月8日
99-Jan-08 YMD模式中的1月8日，否则错误
08-Jan-99 1月8日，除了在YMD模式中错误
Jan-08-99 1月8日，除了在YMD模式中错误
19990108 ISO 8601; 任何模式中的1999年1月8日
990108 ISO 8601; 任何模式中的1999年1月8日
1999.008 年和一年中的日子
J2451187 儒略日期
January 8, 99 BC 公元前99年

time

例子 描述
04:05:06.789 ISO 8601
04:05:06 ISO 8601
04:05 ISO 8601
040506 ISO 8601
04:05 AM 和04:05一样，AM并不影响值
04:05 PM 和16:05一样，输入的小时必须为 <= 12
04:05:06.789-8 ISO 8601
04:05:06-08:00 ISO 8601
04:05-08:00 ISO 8601
040506-08 ISO 8601
04:05:06 PST 缩写指定的时区
2003-04-12 04:05:06 America/New_York 全名指定的时区

time zone

PST 缩写（太平洋标准时间）
America/New_York 完整时区名
PST8PDT POSIX风格的时区声明
-8:00 PST的ISO-8601偏移
-800 PST的ISO-8601偏移
-8 PST的ISO-8601偏移
zulu UTC的军方缩写
z zulu的短形式

特殊的date/time

epoch date, timestamp 1970-01-01 00:00:00+00（Unix系统时间0）
infinity date, timestamp 比任何其他时间戳都晚
-infinity date, timestamp 比任何其他时间戳都早
now date, time, timestamp 当前事务的开始时间
today date, timestamp 今日午夜 (00:00)
tomorrow date, timestamp 明日午夜 (00:00)
yesterday date, timestamp 昨日午夜 (00:00)
allballs time 00:00:00.00 UTC

interval

例子 描述
1-2 SQL标准格式：1年2个月
3 4:05:06 SQL标准格式：3日4小时5分钟6秒
1 year 2 months 3 days 4 hours 5 minutes 6 seconds 传统Postgres格式：1年2个月3日4小时5分钟6秒钟
P1Y2M3DT4H5M6S “带标志符的”ISO 8601 格式：含义同上
P0001-02-03T04:05:06 ISO 8601 的“替代格式”：含义同上

時間日期操作符

• + date ‘2001-09-28’ + integer ‘7’ date ‘2001-10-05’
• + date ‘2001-09-28’ + interval ‘1 hour’ timestamp ‘2001-09-28 01:00:00’
• + date ‘2001-09-28’ + time ‘03:00’ timestamp ‘2001-09-28 03:00:00’
• + interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00:00’
• + timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ timestamp ‘2001-09-29 00:00:00’
• + time ‘01:00’ + interval ‘3 hours’ time ‘04:00:00’
• - interval ‘23 hours’ interval ‘-23:00:00’
• - date ‘2001-10-01’ - date ‘2001-09-28’ integer ‘3’ (days)
• - date ‘2001-10-01’ - integer ‘7’ date ‘2001-09-24’
• - date ‘2001-09-28’ - interval ‘1 hour’ timestamp ‘2001-09-27 23:00:00’
• - time ‘05:00’ - time ‘03:00’ interval ‘02:00:00’
• - time ‘05:00’ - interval ‘2 hours’ time ‘03:00:00’
• - timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ timestamp ‘2001-09-28 00:00:00’
• - interval ‘1 day’ - interval ‘1 hour’ interval ‘1 day -01:00:00’
• - timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ interval ‘1 day 15:00:00’
• * 900 * interval ‘1 second’ interval ‘00:15:00’
• * 21 * interval ‘1 day’ interval ‘21 days’
• * double precision ‘3.5’ * interval ‘1 hour’ interval ‘03:30:00’
• / interval ‘1 hour’ / double precision ‘1.5’ interval ‘00:40:00’

時間日期操作函數

• age 計算兩個時刻的間隔

1. timestamp1-timestamp2的时间间隔
   age(timestamp1, timestamp2)
   select age(timestamp ‘2021-05-10 10:00:00’, timestamp ‘2021-04-29 17:00:00’)
2. 当前时间到timestamp的时间间隔
   age(timestamp)
   select age(timestamp ‘2021-03-10 15:00:00’)

• now 獲取當前日期時間的十种方式

1. clock_timestamp() –實時時間, 可以认为这是唯一一个实时时间，其他的都是事务开始时间

2. timeofday() –據說是實時時間

3. transaction_timestamp() –事務開始時間

4. current_date –事务开始日期(服务器)

–事务开始时间
7. current_time
8. current_time(precision)
9. localtime
10. localtime(precision)

11. current_timestamp –事務開始時間(服务器)

12. current_timestamp(precision)

13. localtimestamp

14. localtimestamp(precision)

15. statement_timestamp() –事務開始時間

16. now() –事務開始時間

• 時間解析
  date_part(text,timestamp)
  date_part(text,interval)
  extract(field from timestamp)
  extract(field from interval)

• 降低精度
  date_trunc(text,timestamp)
  date_trunc(text,timestamp with time zone, text)
  date_trunc(text,interval)

• 判斷是否是有限時間
  isfinite(date)
  isfinite(timestamp)
  isfinite(interval)

• 調整為標準格式
  justify_days(interval)
  justify_hours(interval)
  justigy_interval(interval)

• 創建時間
  make_date(year int,month int,day int)
  make_interval(year int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)
  make_time(hour int, min int, sec double precision)
  make_timestamp(year int, month int, day int, hour int, min int, sec double precision)
  make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])
  to_timestamp(double precision)

• 覆蓋檢查
  (start1, end1) OVERLAPS (start2, end2)
  (start1, length1) OVERLAPS (start2, length2)

用于日期/时间格式化的模板模式

• HH 一天中的小时 （01-12）
• HH12 一天中的小时 （01-12）
• HH24 一天中的小时 （00-23）
• MI 分钟 （00-59）minute (00-59)
• SS 秒（00-59）
• MS 毫秒（000-999）
• US 微秒（000000-999999）
• SSSS 午夜后的秒（0-86399）
• AM, am, PM or pm 正午指示器（不带句号）
• A.M., a.m., P.M. or p.m. 正午指示器（带句号）
• Y,YYY 带逗号的年（4 位或者更多位）
• YYYY 年（4 位或者更多位）
• YYY 年的后三位
• YY 年的后两位
• Y 年的最后一位
• IYYY ISO 8601 周编号方式的年（4 位或更多位）
• IYY ISO 8601 周编号方式的年的最后 3 位
• IY ISO 8601 周编号方式的年的最后 2 位
• I ISO 8601 周编号方式的年的最后一位
• BC, bc, AD或者ad 纪元指示器（不带句号）
• B.C., b.c., A.D.或者a.d. 纪元指示器（带句号）
• MONTH 全大写形式的月名（空格补齐到 9 字符）
• Month 全首字母大写形式的月名（空格补齐到 9 字符）
• month 全小写形式的月名（空格补齐到 9 字符）
• MON 简写的大写形式的月名（英文 3 字符，本地化长度可变）
• Mon 简写的首字母大写形式的月名（英文 3 字符，本地化长度可变）
• mon 简写的小写形式的月名（英文 3 字符，本地化长度可变）
• MM 月编号（01-12）
• DAY 全大写形式的日名（空格补齐到 9 字符）
• Day 全首字母大写形式的日名（空格补齐到 9 字符）
• day 全小写形式的日名（空格补齐到 9 字符）
• DY 简写的大写形式的日名（英语 3 字符，本地化长度可变）
• Dy 简写的首字母大写形式的日名（英语 3 字符，本地化长度可变）
• dy 简写的小写形式的日名（英语 3 字符，本地化长度可变）
• DDD 一年中的日（001-366）
• IDDD ISO 8601 周编号方式的年中的日（001-371，年的第 1 日时第一个 ISO 周的周一）
• DD 月中的日（01-31）
• D 周中的日，周日（1）到周六（7）
• ID 周中的 ISO 8601 日，周一（1）到周日（7）
• W 月中的周（1-5）（第一周从该月的第一天开始）
• WW 年中的周数（1-53）（第一周从该年的第一天开始）
• IW ISO 8601 周编号方式的年中的周数（01 - 53；新的一年的第一个周四在第一周）
• CC 世纪（2 位数）（21 世纪开始于 2001-01-01）
• J 儒略日（从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数）
• Q 季度（to_date和to_timestamp会忽略）
• RM 大写形式的罗马计数法的月（I-XII；I 是 一月）
• rm 小写形式的罗马计数法的月（i-xii；i 是 一月）
• TZ 大写形式的时区缩写（仅在to_char中支持）
• tz 小写形式的时区缩写（仅在to_char中支持）
• TZH 时区的小时
• TZM 时区的分钟
• OF 从UTC开始的时区偏移（仅在to_char中支持）

四、JSON/JSONP

三、常用運算符與函數

邏輯运算符

1. 逻辑： and or not

2. 比较： < > <= >= = <> or !=

3. 测试区间
   a BETWEEN x AND y
   a NOT BETWEEN x AND y
   a BETWEEN SYMETRIC x AND y
   a NOT BETWEEN SYMETRIC x AND y

4. 测试任意数据类型(包括null)相等
   a IS DISTINCT FROM b –不等于
   a IS NOT DISTINCT FROM b –等于
   相较于 =和<>，比较的更加彻底，将null当做普通值处理，
   =和<>作比较时，只要有一个参数为null，就返回null

5. 测试任意数据类型是否为空
   expression IS NULL
   expression IS NOT NULL

6. 测试布尔值
   boolean_expression IS TRUE
   boolean_expression IS NOT TRUE
   boolean_expression IS FALSE
   boolean_expression IS NOT FALSE
   boolean_expression IS UNKNOWN
   noolean_expression IS NOT UNKNOWN

7. 非空數量測試
   num_nonnulls(VARIADIC “any”) 返回非空参数的数量 num_nonnulls(1,NULL,2)=2
   num_nulls(VARIADIC “any”) 返回空参数的数量 num_nulls(1,NULL,2)=1

8. 注意
   a IS DISTINCT FROM b
   a IS NOT DISTINCT FROM b
   相较于 =和<>，比较的更加彻底，将null当做普通值处理，
   =和<>作比较时，只要有一个参数为null，就返回null

数学運算符及函數

1. 運算符
   +加 -減 *乘 /除 %求餘 ^冪
   |/ 平方根 |/25.0 5
   ||/ 立方根 |/27.0 3
   ! 阶乘 5！ 120
   !! 前缀阶乘 !!5 120
   @ 绝对值 @ -5.0 5
   & 按位与 91&15 11
   | 按位或 32|3 35
   ‘#’ 按位异或 17#5 20
   ~ 按位求反 ~1 -2
   << 按位左移 1<<4 16

   按位右移 8>>2 2

2. 常數、四則運算、隨機數
   pi()
   div(y numeric, x numeric) y/x的整數商
   abs(x) 絕對值
   mod(y,x) y/x的餘數
   random() 返回範圍為0.0<=x<1.0中的隨機值，使用了一個簡單的線性共軛算法。它的速度很快，但是不適合于密碼學的應用；關於更安全的替代方案，參閱pgcrypto模塊。如果setseed()被調用，那麼當前會話中的後續random()調用的結果可以通過使用相同的參數重新發佈setseed()來重複。
   setseed(dp) 為後續的random()調用設置種子(值位於-1.0和1.0之間，包括邊界值)

3. 指數對數
   power(a numeric or dp, b numeric or dp) 求a的b次冪
   exp(x dp or numeric) e的x次方
   sqrt(dp or numeric) 平方根
   cbrt(dp) 立方根
   ln(dp or numeric) 自然對數
   log10(dp or numeric) 以10為底的對數
   log(b numeric, x numeric) 以b為底的對數

4. 數據修整
   ceil(dp or numeric) 不小於參數的最近整數
   floor(dp or numeric) 不大於參數的最近的整數
   round(dp or numeric) 圓整為最近的整數
   round(v numeric, s int) 圓整為s位小數的數字
   trunc(dp or numeric) 截斷(向零靠近)
   trunc(v numeric, s int)截斷為s位小數位置的數字
   scale(numeric) 參數的精度(小數點後的位數)

5. 柱狀圖函數
   width_bucket(op dp, b1 dp, b2 dp, count int) 返回一個桶號，這個桶是在一個柱狀圖中operand將被分配的那個桶，該柱狀圖有count個散佈在範圍b1到b2上的等寬桶。對於超過該範圍的輸入，將返回0或者count+1
   width_bucket(op numeric, b1 numeric, b2 numeric, count int) 返回一個桶號，這個桶是在一個柱狀圖中operand將被分配的那個桶，該柱狀圖有count個散佈在範圍b1到b2上的等寬桶。對於超過該範圍的輸入，將返回0或者count+1
   with_bucker(operand anyelement, thresholds anyarray) 返回一個桶號，這個桶是在給定數組中operand將被分配的桶，該數組列出了桶的下屆。對於一個低於第一個下界的輸入返回0。threholds數組必須被排好序，最小的排在最前面，否則將會得到意想不到的結果。

6. 三角函數
   degrees(dp) 把弧度轉為角度
   radians(dp) 把角度轉為弧度
   sin(x) 正弦(弧度)
   sind(x) 正弦(角度)
   cos(x) 餘弦(弧度)
   cosd(x) 餘弦(角度)
   tan(x) 正切(弧度)
   tand(x) 正切(角度)
   cot(x) 餘切 (弧度)
   cotd(x) 餘切(角度)
   asin(x) 反正弦(弧度)
   asind(x) 反正弦(角度)
   acos(x) 反餘弦(弧度)
   acosd(x) 反餘弦(角度)
   atan(x) 反正切(弧度)
   atand(x) 反正切(角度)
   atan2(y,x) y/x的反正切(弧度)
   atan2d(y,x) y/x的反正切(角度)
   使用基於角度的三角函數更好，因為可以避免sind(30)等特殊情況下的舍入偏差

7. 雙曲函數
   sinh(x) 雙曲正弦
   cosh(x) 雙曲餘弦
   tanh(x) 雙曲切線
   asinh(x) 反雙曲正弦
   acosh(x) 反雙曲餘弦
   atanh(x) 反雙曲切線

字符串函數及操作符

1. 字符串檢查、子串定位、子串替換
   starts_with(string,prefix) 如果string以prefix開始則返回真 starts_with(‘alphabet’,’alph’) t
   position(substring in string)
   strpos(string,substring) 指定子串的位置(和position(substring in string)相同)，只是參數順序相反 strpos(‘high’,’ig’) 2
   replace(string text, from text, to text) 將string中出現的所有子串from替換為子串to replace(‘abcdefabcdef’,’cd’,’XX’) abXXefabXXef
   overlay(string placing string from int [for int]) 替換子串

2. 字符串長度
   length(string) 獲取string中的字符數 length(‘jose’) 4
   length(string bytea, encoding name) string在給定編碼中的字符數。string必須在這個編碼中有效 length(‘jose’,’UTF8’) 4
   bit_length(string)
   octet_length(string) 串中的字節數
   char_length(string)
   character_length(string)

3. 字符串拼接、分割
   string || string
   string || non-string
   non-string || string
   concat(str “any” [,str “any” [, …]]) 任意字符串拼接
   concat_ws(sep text, str “any” [, str “any” [, …]]) 用第一個參數將後續的字符串分隔，NULL被忽略
   repeat(string text, number int) 重複string指定number次 repeat(‘Pg’,4) PgPgPgPg
   split_part(string text, delimiter text, field int) 按delimiter劃分string並返回給定域(從1開始計算) split_part(‘abc”@”def”@”ghi’,’”@”‘,2) def
   string_agg 聚合

4. 字符串截取
   substring(string [from int] [for int])
   substring(string from pattern)
   substring(string from pattern for escape)
   substr(string, from [, count]) 提取子串(與substring(string from fromStr for count) 相同) substr(‘alphabet’,3,2) ph
   left(str text, n int) 截取字符串的前n個字符，n為負數時，將返回除了最後|n|個字符之外的所有字符（從右往左截取）。
   right(str text, n text) 返回字符串中最後n個字符。如果n為負，返回除最前|n|個字符外的所有字符，截取右側n個字符，如果n為負數則從左側開始截取 right(‘abcde’, 2) de

5. 字符串修剪、補空
   trim([leading | trailing | both] [characters] from string)
   trim([leading | trailing | both] [from] string [,characters])
   btrim(‘xyxtrimyyx’,’xyz’) –trim 全串trim
   ltrim(string text [, characters text]) 左側trim ltrim(‘zzzytest’,’xyz’) test
   rtrim(string text, [, characters text]) 右側截取
   lpad(string text, length in [, fill text]) 將string通過前置字符fill(默認是一個空格)填充到長度length。如果string長度已經長於length,則它被(從右邊)截斷。(在左邊補空格至指定位數) lpad(‘hi’,5,’xy’) xyxhi
   rpad(string text, length int [, fill text]) 右側部齊，多則截取

6. 字符串編解轉碼
   ascii(‘x’) –120 解碼ascii
   to_ascii(string text [, encoding text]) 將string從另一個編碼轉換到ASCII(只支持從LATIN1\LATIN2\LATIN9和WIN1250編碼的轉化) to_ascii(‘Karel’) Karel
   to_hex(number int or bigint) 將number轉換到它等效的十六進制表示 to_hex(2147483647) 7fffffff
   char(65) –A 將int裝換為對應的ascii char
   convert(string bytea, src_encoding name, dest_encoding name) 轉換字符串的編碼格式 convert(‘text_in_utf8’,’UTF8’,’LATIN1’)
   convert_from(string bytea,src_encoding name) 將字符串轉換為當前數據庫編碼格式 convert_from(‘text_in_utf8’,’UTF8’)
   convert_to(string text, dest_encoding name) 將字符串轉換為dest_encoding convert_to(‘some text’,’UTF8’)
   pg_client_encoding() 當前客戶端編碼的名字 pg_client_encoding() SQL_ASCII
   decode(string text, format text) 從string文本解碼二進制數據(可以用來做base64解碼) decode(‘MTIzAAE=’,’base64’) \x3132330001
   encode(data bytea, format text) 將二進制數據編碼成一個文本string,支持的格式有base64,hex,escape。其中escape將零字節和高位組字節轉換為八進制序列(\nnn)和雙鞋的反斜線。 encode(‘123\000\001’,’base64’) MTIzAAE=
   md5(string) 計算string的MD5哈希，返回十六進制的結果 md5(‘abc’) 900…f72
   translate(string text, from text, to text) string中任何匹配from集合的一個字符的字符會被替換成to集合中的相應字符。如果from比to長，from中的額外字符會被刪除。 translate(‘12345’,’143’,’ax’) a2x5

7. 字符串變形和格式化
   lower(string)
   upper(string)
   initcap(string) 將每一個詞的第一個字符裝換為大寫，其他的字母轉換為小寫。詞是由非字母數字字符分隔的字母數字字符序列 initcap(‘hi THOMAS’) Hi Thomas
   format(formatstr text [, formatarg “any” [, …]]) 根據格式字符串格式化參數。該函數和C函數的sprintf類似 format(‘Hello %s, %1$s’, ‘World’) Hello World, World
   reverse(str) 返回反轉的字符串 reverse(‘abcde’) edcba

8. 標識符加去引號
   quote_ident(string text) 加引號 quote_ident(‘Foo bar’) “Foo bar”
   quote_literal(value anyelement) 強迫給定值為文本，並且接著將他用引號包圍作為一個文本。嵌入的單引號和反斜線被正確的雙寫。 quote_literal(42.5) ‘42.5’
   quote_nullable(string text) 將給定字符串返回成合適的引用形式，使它可以在一個SQL語句字符串中被用作一個字符串文字；如果參數為空，返回字符串NULL。 quote_nullable(NULL) NULL(字符串)
   quote_nullable(value anyelement) 強迫給定值為文本，並加引號; 參數為NULL時返回字符串NULL quote_nullable(43.5) ‘43.5’
   parse_ident(qualified_identifier text [, strictmode boolean DEFALUT true]) 解析標識符，去掉引號，模式為strict時會將多餘字符報錯。 parse_ident(‘“SomeSchema”.someTable’) {SomeSchema, sometable}

9. 正則匹配和分割
   regexp_match(string text, pattern text [, flags text]) 返回一個POSIX正則表達式與string的第一個posix正則表達式與string的第一個匹配得到的子串 regexp_match(‘foobarbequebaz’,’(bar)(beque)’) {bar, beque}
   regexp_matches(string text, pattern text [, flags text]) 返回一個POSIX正則表達式與string匹配得到的子串。 regexp_matches(‘foobarbequebaz’,’ba.’,’g’) 返回兩行
   regexp_replace(string text, pattern text, replacement text [, flags text]) 替換匹配一個POSIX正則表達式的子串。 regexp_replace(‘Thomas’,’.[mN]a.’,’M’) ThM
   regexp_split_to_array(string text, pattern text [, flags text]) 使用一個POSIX正則表達式作為分隔符劃分string。 regexp_split_to_array(‘hello world’,’\s+’) {hello, world}
   regexp_split_to_table(string text, pattern text [, flags text]) 使用一個POSIX正則表達式作為分隔符劃分stirng regexp_split_to_table(‘hello world’, ‘\s+’) 兩行

---