常用sql写法
postgresql基本数据类型
类型 | 描述 |
---|---|
INTEGER | 整数类型,-2,147,483,648 到 2,147,483,647 |
BIGINT | 大整数类型,-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 |
SMALLINT | 小整数类型,-32,768 到 32,767 |
DECIMAL | 精确数字类型,可定义精度和标度 |
NUMERIC | 高精度数字类型,与 DECIMAL 相同 |
SERIAL | 自增整数类型,等效于 INTEGER + 自增序列 |
BIGSERIAL | 自增大整数类型,等效于 BIGINT + 自增序列 |
CHAR(n) | 固定长度字符类型 |
VARCHAR(n) | 可变长度字符类型,最大长度为 n |
TEXT | 可变长度字符类型,没有长度限制 |
DATE | 日期类型,存储日期 |
TIME | 时间类型,不包含日期 |
TIMESTAMP | 时间戳类型,包含日期和时间 |
TIMESTAMPTZ | 带时区的时间戳类型 |
INTERVAL | 时间间隔类型 |
BOOLEAN | 布尔类型,TRUE 、FALSE 或 NULL |
UUID | 通用唯一标识符类型 |
BINARY | 二进制数据类型 |
BYTEA | 二进制数据类型,与 BINARY 功能类似 |
JSON | JSON 数据类型 |
JSONB | 二进制格式的 JSON 数据类型 |
sql常用函数
时间常用函数
-
获取当前时间
SELECT CURRENT_DATE; -- 当前日期 SELECT CURRENT_TIME; -- 当前时间 SELECT CURRENT_TIMESTAMP; -- 当前时间戳 SELECT NOW(); -- 当前时间戳
-
提取日期和时间(EXTRACT ,DATE_TRUNC)
SELECT EXTRACT(YEAR FROM current_date); -- 提取当前年份 SELECT EXTRACT(MONTH FROM current_date); -- 提取当前月份 SELECT EXTRACT(DAY FROM current_date); -- 提取当前日期 SELECT EXTRACT(HOUR FROM current_time); -- 提取当前小时 SELECT EXTRACT(MINUTE FROM current_time); -- 提取当前分钟 SELECT EXTRACT(SECOND FROM current_time); -- 提取当前秒 SELECT DATE_TRUNC('year', current_date); -- 截取到今年年初 SELECT DATE_TRUNC('month', current_date); -- 截取到本月月初 SELECT DATE_TRUNC('day', current_date); -- 截取到今天 SELECT DATE_TRUNC('hour', current_time); -- 截取到当前小时
-
时间间隔函数(INTERVAL )
SELECT CURRENT_DATE + INTERVAL '1 day'; -- 明天 SELECT CURRENT_DATE - INTERVAL '1 day'; -- 昨天 SELECT CURRENT_DATE + INTERVAL '1 month'; -- 下个月 SELECT CURRENT_DATE - INTERVAL '1 month'; -- 上个月
-
时间格式化字符串(TO_CHAR)
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 格式化当前时间戳
-
字符串转时间(TO_DATE)
SELECT TO_DATE('2024-07-07 00:00:00','YYYY-MM-DD'); -- 解析字符串为日期
时间常见用法
SELECT date_trunc('month', current_date) AS start_of_month;
SELECT extract(month FROM current_date) AS current_month;
SELECT extract(year FROM current_date) AS current_year;
- 获取月份:
SELECT extract(month FROM current_date) AS current_month;
- 获取天:
SELECT extract(day FROM current_date) AS current_day;
- 获取小时:
SELECT extract(hour FROM current_time) AS current_hour;
- 获取分钟:
SELECT extract(minute FROM current_time) AS current_minute;
- 获取秒:
SELECT extract(second FROM current_time) AS current_second;
SELECT
to_char(month_start, 'YYYY-MM') AS month_name
FROM (
SELECT
generate_series(
date_trunc('year', current_date), -- 从今年年初开始
date_trunc('month', current_date), -- 到当前月份
'1 month'::interval -- 以每个月为间隔
) AS month_start
) as months
ORDER BY
month_start;
-
查询近12个月(包括本月))
SELECT to_char(month_start, 'YYYY-MM') AS month_name FROM ( SELECT generate_series( date_trunc('month', CURRENT_DATE) - INTERVAL '11 months', -- 从 12 个月前的月初开始 date_trunc('month', CURRENT_DATE), -- 到本月的月初 '1 month'::interval -- 每个月递增 ) AS month_start ) AS months ORDER BY month_start;
判空常用函数
-
COALESCE
-- 多个字符串,返回一个非空字符串 COALESCE(expression1, expression2, ..., expressionN) SELECT COALESCE(description, '这是一个空字符串') AS middle_name FROM common_server.smx_common_sql;
-
IS NULL和IS NOT NULL
SELECT * FROM my_table WHERE column1 IS NULL; SELECT * FROM my_table WHERE column1 IS NOT NULL;
-
case
SELECT CASE WHEN column1 IS NULL THEN 'Default Value' ELSE column1 END FROM my_table;
类型转行函数
-
使用cast
cast (num_no as VARCHAR)
-
使用
::
操作符expression::VARCHAR
-
使用
TO_CHAR
和TO_DATE
函数 -
使用
ARRAY
类型和UNNEST
函数SELECT ARRAY[1, 2, 3, 4, 5] AS numbers; SELECT UNNEST(ARRAY[1, 2, 3, 4, 5]) AS number;
-
使用case
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
-
自定义函数
CREATE OR REPLACE FUNCTION to_uppercase(input TEXT) RETURNS TEXT AS $$ BEGIN RETURN UPPER(input); END; $$ LANGUAGE plpgsql; SELECT to_uppercase('hello world');
多行转行函数
-
STRING_AGG
STRING_AGG(expression , separator ORDER BY employee_name ASC) -- expression 参数,separator 拼接符号
-
ARRAY_AGG
SELECT ARRAY_AGG(employee_name ORDER BY employee_name ASC ) AS employee_names FROM employees;
-
将数组转换为字符串
SELECT ARRAY_TO_STRING( ARRAY_AGG(num_no ORDER BY num_no desc ),separator ) FROM my_table; -- 将数组转为 指定分隔符的数组
-
UNNEST
SELECT UNNEST(ARRAY[1, 2, 3, 4]);
-
LISTAGGpg:13以上可用LISTAGG(expression, separator) WITHIN GROUP (ORDER BY clause)
操作字符串(时间)函数
-
**SUBSTRING**
函数SUBSTRING(string FROM start [FOR length]) SELECT SUBSTRING('Hello, world!' FROM 1 FOR 5); -- 结果: 'Hello' SELECT SUBSTRING('Hello, world!' ,1 , 5); -- 结果: 'Hello' -- 多种写法 从第一个开始截取5个字符串
-
**LEFT**
函数LEFT(string, n) SELECT LEFT('Hello, world!', 5); -- 结果: 'Hello' -- 从左边第一个开始 ,截取指定个数字符
-
**RIGHT**
函数
RIGHT(string, n)
SELECT RIGHT('Hello, world!', 6); -- 结果: 'world!'
-- 从右边第一个开始 ,截取指定个数字符
-
**POSITION**
函数POSITION(substring IN string) -- substring 在 string中的位置信息 SELECT POSITION(',' IN 'Hello, world!'); -- 结果: 6
-
**SPLIT_PART**
函数SPLIT_PART(string, delimiter, field) SELECT SPLIT_PART('Hello,world,here', ',', 2); -- 结果: 'world' -- 把字符串按照delimiter 分割 按照field 取字符字串 从1开始
-
**OVERLAY**
函数OVERLAY(string PLACING substring FROM start [FOR length]) -- 从start 开始 截取length个字符 替换为substring SELECT OVERLAY('Txxxxas' PLACING 'ex' FROM 2 FOR 4); -- 结果: 'Texas'
-
**TRIM**
函数TRIM([BOTH | LEADING | TRAILING ] [characters FROM] string) -- BOTH 左右两边 LEADING 左边 TRAILING 右边 匹配到characters 转为空 SELECT TRIM(BOTH ' ' FROM ' Hello, world! '); -- 结果: 'Hello, world!' SELECT TRIM(LEADING 'H' FROM 'Hello, world!'); -- 结果: 'ello, world!' SELECT TRIM(TRAILING '!' FROM 'Hello, world!!!'); -- 结果: 'Hello, world'
-
**LPAD**
和**RPAD**
函数LPAD(str, length, fill) -- 指定str 填充fill 在左边到指定字符长度 RPAD(str, length, fill) -- 指定str 填充fill 在右边边到指定字符长度 SELECT LPAD('Hello', 10, '-'); -- 结果: '-----Hello' SELECT RPAD('Hello', 10, '-'); -- 结果: 'Hello-----'
-
**CONCAT**
: 连接字符串SELECT CONCAT('Hello', ', ', 'world!'); -- 'Hello, world!'
-
**LENGTH**
: 返回字符串长度SELECT LENGTH('Hello, world!'); -- 13
-
UPPER
/LOWER
: 转换为大写/小写SELECT UPPER('Hello, world!'); -- 'HELLO, WORLD!' SELECT LOWER('Hello, world!'); -- 'hello, world!'
-
REPLACE
: 替换字符串中的子串SELECT REPLACE('Hello, world!', 'world', 'PostgreSQL'); -- 'Hello, PostgreSQL!'
-
REVERSE
: 反转字符串SELECT REVERSE('Hello, world!'); -- '!dlrow ,olleH'
-
INITCAP
: 每个单词的首字母大写SELECT INITCAP('hello, world!'); -- 'Hello, World!'