# 关系型数据库

Flag
事务
数据库设计
ORACLE
PostgreSQL
SQLite3

# Flag

关系型数据库（Relational database），是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库

递归

Common Table Expression(公共表表达式)：在CTE中可以包括对自身的引用，因此这种表达式也被称为递归CTE

-- ORACLE
查询语句 start with 起始条件 connect by prior 递归条件
-- 其他数据库
with recursive cte as(
 select user_id,manager_id,name,name as manager_name from employee where manager_id=-1
 union all
 select c.user_id,c.manager_id,c.name,p.name as manager_name from cte p, employee c on p.user_id=c.manager_id
)
select user_id,manager_id,name,manager_name from cte

索引类型

索引类型	说明
B-TREE	B树索引
HASH	哈希索引
GENERALIZED SEARCH TREE	GIST平衡树结构索引
GENERALIZED INVERTED	GIN倒排索引索引
SPATIAL	空间索引
FULL TEXT	全文索引
BITMAP	位图索引
INVERTED	反向索引

通用日期时间

SQL 函数 (opens new window)
- 姚鑫 - 知乎 (opens new window)

获得当前日期+时间（date+time）函数

localtime
localtimestamp
current_date
CURRENT_TIME
current_timestamp

CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

INTERVAL时间单位

时间单位	简写	说明
MILLENNIUM	ml	1000年
CENTURY	cen	100年
DECADE	dec	10年
YEAR	y/yr	年
QUARTER		季度
MONTH	mon	月
WEEK	w	周
DAY	d	天
HOUR	h/hr	时
MINUTE	m/min	分
SECOND	s/sec	秒
MILLISECOND	ms	毫秒
MICROSECOND	us	微秒
NANOSECOND	ns	纳秒
YEAR_MONTH	yr_mon	年:月
DAY_HOUR	d_hr	日:时
DAY_MINUTE	d_min	日:分
DAY_SECOND	d_sec	日:秒
HOUR_MINUTE	hr_min	时:分
HOUR_SECOND	hr_sec	时:秒
MINUTE_SECOND	min_sec	分:秒

TRUNC(NUMBER)表示截断数字，TRUNC(date)表示截断日期
date_trunc 开始的日期时间
date_part 从时间戳中提取年月日时分秒周
to_date 转日期
TO_TIMESTAMP 转时间戳

主流数据库数据类型对应关系

数据类型分类	SQL Server	MySQL	Oracle	PostgreSQL	SQLite
布尔值	bit	bit	SMALLINT	bit
布尔值				boolean/bool
二进制	binary	binary	BLOB
二进制	double	double	BINARY_DOUBLE
二进制	float	float	BINARY_FLOAT
二进制	image	blob	BLOB
二进制	sql_variant	longblob	BLOB	bytea
二进制	image	longblob	BLOB
二进制	image	longblob	LONGRAW
二进制	image	mediumblob	BLOB
二进制	image	longblob	RAW
二进制	uniqueidentifier	VARCHAR	ROWID
二进制	sql_variant	longblob	BLOB
二进制（外部）			BFILE
日期	date	date	DATE	date	date
日期	datetime	datetime	DATE
日期	datetime2	datetime	DATE
日期	datetimeoffset	datetime	DATE
日期	smalldatetime	datetime	DATE
日期	time	time	DATE	time	time
日期	timestamp	timestamp	TIMESTAMP	timestamp	timestamp
日期（带时区）			TIMESTAMP WITH LOCAL TIME ZONE	timestamp with time zone/timestamptz
日期（带时区）				time with time zone/timetz
数字	bigint	bigint	INTEGER	bigint/int8
数字	decimal	decimal	DECIMAL	numeric/decimal	decimal
数字	double	double	FLOAT	double precision/float8	double
数字	float	float	FLOAT		float
数字	int	int	INTEGER	integer/int/int4	interger
数字	int	mediumint	INTEGER
数字	money	decimal	DECIMAL	money
数字	numeric	numeric	NUMBER
数字	REAL	REAL	REAL	real/float4
数字	smallint	smallint	SMALLINT	smallint/int2	smallint
数字	smallmoney	numeric	NUMERIC
数字	tinyint	tinyint	SMALLINT
数字	year	year	NUMBER
字符串	char	char	CHAR	character/char	char
字符串	varchar	varchar	VARCHAR2	character varying/varchar	varchar/vargraphic
字符串	varchar	enum	VARCHAR2
字符串	nchar	varchar	NCHAR		graphic
字符串	nvarchar	varchar	NVARCHAR2
字符串	nvarchar(max)	varchar	NVARCHAR2
字符串	nvarchar	varchar	NVARCHAR2
字符串	VARCHAR	set	VARCHAR2
字符串	uniqueidentifier	VARCHAR	ROWID
字符串	uniqueidentifier	VARCHAR	UROWID
字符串	varchar(max)	varchar	VARCHAR2
字符串（大）	text	text	CLOB	text
字符串（大）	text	longtext	CLOB	text
字符串（大）	ntext	text	NCLOB	text
字符串（大）	text	text	CLOB	text
字符串（大）	TEXT	tinytext	CLOB	text
字符串（大）	xml	TEXT	CLOB	xml
字符串（大）	text	text	LONG
字符串（大）	text	mediumtext	CLOB
字符串（大）	ntext	text	CLOB
字符串（大）	text	JSON	JSON	json
字符串（二进制）	varbinary	tinyblob	BLOB
字符串（二进制）	varbinary	varbinary	BLOB
字符串（二进制）	varbinary(max)	varbinary	BLOB
字符串（二进制）	varbinary	varbinary	BLOB	jsonb

# 事务

脏数据：未提交的数据
不可重复读（Non-Repeatable Reads）：一个事务先后读取同一条记录，而事务在两次读取之间该数据被其它事务所修改，则两次读取的数据不同
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据

遵循ACID (opens new window)事务规则

1、A (Atomicity) 原子性

一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，只要有一个操作失败，整个事务就失败，需要回滚。例如：从A账户转10元至B账户，分为两个步骤：1.从A账户取10元；2.存入10元至B账户。这两步要么一起完成，要么一起不完成，当只完成第一步，第二步失败的情况下，钱就会回滚到A账户中去，否则的话，钱就会莫名其妙少了10元。

2、C (Consistency) 一致性

数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。例如：现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。

3、I (Isolation) 独立性/隔离性

并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。例如：现在从A账户转10元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的10的。

4、D (Durability) 持久性

一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失

运行模式

自动提交事务：默认事务管理模式。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。
显式事务：以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。
隐性事务：当连接以此模式进行操作时，sql将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。它生成连续的事务链。

# 数据库设计

范式：Normal Format

符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。一个数据库表之间的所有字段之间的联系的合理性。

范式是离散数学里的概念
范式目标是在满足组织和存储的前提下使数据结构冗余最小化
范式级别越高，表的级别就越标准

第一范式：1NF，确保表中每一列数据的原子性，不可再分！
第二范式：2NF，在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。
第三范式：3NF，再满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。
BCNF范式,4NF,5NF
反范式：用空间换时间，通过适当的数据冗余提高查询效率，但冗余数据会牺牲数据一致性

# ORACLE

空字符串''同等NULL，字符串与数字类型会自动转换

Oracle Call Interface（OCI）是Oracle提供的一组C语言API，用于开发Oracle数据库应用程序。 OCI提供了一种直接访问Oracle数据库的方式，使开发人员可以编写高效且可扩展的应用程序

-- 创建会话级临时表来存储原数据，并删除表数据
CREATE GLOBAL TEMPORARY TABLE TEST_BAK AS (SELECT * FROM TEST);

-- 查看临时表数据
SELECT * FROM TEST_BAK;

-- Oracle中类似于PostgreSQL的unnest
-- https://docs.oracle.com/en/database/oracle/oracle-database/23/addci/extensibility-constants-types-and-mappings.html
select * from table(sys.odcinumberlist(3,4,3))
select * from table(sys.odcivarchar2list('3a','4b','3d'))

-- 常量子查询
字段 IN (SELECT 10000 FROM dual UNION ALL SELECT 20000 FROM dual)

-- 清空数据 delete是dml操作；truncate是ddl操作，ddl隐式提交不能回滚，会回收表空间
DELETE FROM TEST;
-- 将目标字段数据清空
--UPDATE TEST SET MEASURED = NULL;

-- 修改表字段
ALTER TABLE TEST MODIFY MEASURED NUMBER(18,6);

-- 还原表结构
INSERT INTO TEST SELECT * FROM TEST_BAK;

-- 删除临时表
DROP TABLE TEST_BAK;


-- 替代LISTAGG拼接超4000字符长度
SELECT
REGEXP_REPLACE(RTRIM(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()')) AS CLOB),', '),'(,)+',',') a,
REGEXP_REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()')).getclobval(),','),'(,)+',',') b,
REGEXP_REPLACE(RTRIM(REGEXP_REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(e, colname || ',')) AS CLOB),'<E>|</E>',''),','),'(,)+',',') c
FROM table_name
GROUP BY grouping_column;

系统表

USER_TABLES 当前用户拥有的表：TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED
DBA_TABLES 包括系统表：多了OWER列
ALL_TABLES 所有用户的表：多了OWER列
ALL_OBJECTS 当前用户有访问权限的所有对象：OWER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
USER_TAB_COLUMNS 当前用户拥有的表字段
ALL_TAB_COLUMNS
DBA_TAB_COLUMNS
USER_TAB_COMMENTS 当前用户拥有的表注释：TABLE_NAME,TABLE_TYPE,COMMENTS
DBA_TAB_COMMENTS ：多了OWER列
ALL_TAB_COMMENTS ：多了OWER列
USER_COL_COMMENTS 当前用户拥有的表字段注释： TABLE_NAME,COLUMN_NAME,COMMENTS
DBA_COL_COMMENTS ：多了OWER列
ALL_COL_COMMENTS ：多了OWER列

SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS LIKE '%摘要%'

分组获取最新一条数据（查询各组最新的一条记录）

over partition by 分析函数（开窗函数）

SELECT * FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY 分组字段名 ORDER BY 排序字段名 DESC) rn,t.* FROM test1 t
    ) WHERE rn = 1;

SELECT t.* FROM test1 t GROUP BY 分组字段名 ORDER BY 排序字段名 DESC FETCH FIRST 1 ROWS ONLY;


SELECT * FROM (
    select eb_vipcode,eb_time,MAX(eb_time) over(partition by eb_vipcode) as "atime" from eb_daskexpdateinfo
    ) x where eb_time = "atime";

SELECT * FROM (
    select ID_,COMPANY_NAME,USAGE_RATE,CREATE_TIME
    ,MAX(CREATE_TIME) over(partition by COMPANY_NAME) as "atime" from SPEC_RATE_ORIGIN
    ) x where CREATE_TIME = "atime";

group by

SELECT eb_vipcode,MAX(eb_time) AS "atime" FROM eb_daskexpdateinfo group by eb_vipcode

inner join

SELECT A.* FROM SPEC_RATE_ORIGIN A INNER JOIN (
    SELECT COMPANY_NAME,MAX(CREATE_TIME) AS "atime" FROM SPEC_RATE_ORIGIN group by COMPANY_NAME
    ) B ON A.COMPANY_NAME = B.COMPANY_NAME AND A.CREATE_TIME = B."atime";

一次执行多条SQL

INSERT ALL
 INTO a表(字段) VALUES(各个值1)
 INTO a表(字段) VALUES(其它值2)
 INTO a表(字段) VALUES(其它值3)
SELECT 1 FROM DUAL;

使用begin…end;

如何在Oracle中一次执行多条sql语句 (opens new window)

begin
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
end;

插入或更新 upsert

MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);

死锁

-- 查询死锁会话
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name
, s.machine, s.terminal, o.object_name, s.logon_time, p.SPID
 FROM v$locked_object l, all_objects o, v$session s,v$process p
 WHERE l.object_id = o.object_id AND l.session_id = s.sid AND s.paddr = p.addr
 ORDER BY sid, s.serial#;

-- 结束
alter system kill session 'sid,serial#';

orakill SID spid

查看所有表结构

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.COLUMN_ID                                  AS "序号",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default                               AS "默认值"
       --t4.created                                  AS "建表时间"
FROM cols t1
LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
LEFT JOIN user_objects t4 ON t1.table_name = t4.OBJECT_NAME
WHERE NOT EXISTS (
    SELECT t4.Object_Name
    FROM User_objects t4
    WHERE t4.Object_Type = 'TABLE' AND t4.Temporary = 'Y' AND t4.Object_Name = t1.Table_Name
)
ORDER BY t1.Table_Name, t1.Column_ID;

-- 查询表字段信息
WITH ct AS (
    SELECT u3.table_name,u3.column_name,u4.constraint_type FROM user_cons_columns u3
    JOIN user_constraints u4 ON u4.constraint_name=u3.constraint_name AND u4.constraint_type='P'
)
SELECT DISTINCT u1.column_id
, u1.column_name
, u1.data_type AS column_type
, (CASE WHEN u1.data_type='NVARCHAR2' THEN u1.data_length/2 ELSE u1.data_length END) AS data_length
, (CASE WHEN DECODE(u1.nullable, 'Y', 'N', 'N', 'Y', 'N')='Y' THEN 0 ELSE 1 END) AS nullable
, u1.data_precision AS data_precision
, u1.data_scale AS data_scale
, u2.comments
, DECODE(ct.constraint_type,'P','PRI') AS column_key
FROM user_tab_columns u1
LEFT JOIN user_col_comments u2 ON u1.table_name=u2.table_name AND u1.column_name=u2.column_name
LEFT JOIN ct ON ct.table_name=u1.table_name AND ct.column_name=u1.column_name
WHERE u1.table_name= '表名'
ORDER BY u1.column_id

日期时间

Oracle日期格式转换 to_date,to_char,to_timetamp 相互转换 (opens new window)

SELECT SYSDATE, SYSTIMESTAMP FROM dual;
SELECT TO_CHAR(TO_TIMESTAMP('2023-05-08 10:10:10', 'yyyy-mm-dd hh24:mi:ss'), 'ww') FROM dual;
-- 得到当天凌晨0点0分0秒的日期
SELECT TRUNC(SYSDATE) FROM DUAL;
-- 获取今天最后的时间（即午夜之前的那一刻）
SELECT TRUNC(SYSDATE) + 0.99999 AS last_time FROM DUAL;
SELECT TRUNC(SYSDATE) + 1 - 1/86400 AS last_time FROM dual;
SELECT TRUNC(SYSDATE) + 1 - INTERVAL '1' SECOND AS last_time FROM DUAL;
SELECT TRUNC(SYSDATE) + INTERVAL '1' DAY - INTERVAL '1' SECOND AS last_time FROM DUAL;
SELECT TRUNC(SYSDATE) + INTERVAL '23' HOUR + INTERVAL '59' MINUTE + INTERVAL '59' SECOND AS last_time FROM DUAL;
-- 获取本周开始日期（星期一）
SELECT TRUNC(SYSDATE, 'IW') FROM DUAL;
-- 获取本周结束日期（星期日）
SELECT TRUNC(SYSDATE, 'IW') + 7 - 1 FROM DUAL;
-- 获取当月开始时间和结束时间
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL
SELECT TRUNC(LAST_DAY(SYSDATE))+ 0.99999 FROM DUAL
-- 获取今年开始时间和结束时间
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'YYYY-MM-DD HH24:MI:SS') AS start_date,
       TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'YYYY') + 0.99999), 'YYYY-MM-DD HH24:MI:SS') AS end_date
FROM dual;
SELECT TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-01-01', 'YYYY-MM-DD HH24:MI:SS') AS start_date,
       TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AS end_date
FROM dual;
SELECT TRUNC(SYSDATE, 'YYYY') AS start_date,
  TRUNC(SYSDATE + INTERVAL '1' YEAR, 'YYYY') - INTERVAL '1' SECOND AS end_date
FROM dual
-- FM格式模型来实现不要前导零
SELECT TO_CHAR(SYSDATE, 'FMMM') AS one_digit_month
FROM dual;


-- 获取倒推时间列表
SELECT TRUNC(sysdate - NumToDSInterval(level-1, 'hour'), 'MI') AS ds -- 'day','hour','minute','second'
, TRUNC(sysdate - NumToYMInterval(level-1, 'month'), 'MI') AS ym -- 'year','month'
FROM dual CONNECT BY level <= 12;

SELECT TO_CHAR(Add_Months(TRUNC(sysdate,'YYYY'), Level-1), 'FMMonth') AS month_name FROM Dual CONNECT BY Level <= 12;
SELECT TRUNC(SYSDATE - LEVEL/24, 'HH24') AS HOURMIN FROM DUAL CONNECT BY LEVEL <= 12 ORDER BY 1;

-- 将数据按半小时进行分组
SELECT TO_CHAR(record_date, 'YYYY-MM-DD HH24') || CASE WHEN TO_CHAR(record_date, 'MI') < '30' THEN ':00' ELSE ':30' END AS half_hour,
       SUM(col_8) AS total_money
FROM table_name
GROUP BY TO_CHAR(record_date, 'YYYY-MM-DD HH24') || CASE WHEN TO_CHAR(record_date, 'MI') < '30' THEN ':00' ELSE ':30' END

SELECT TRUNC(record_date, 'HH24') + CASE WHEN TO_CHAR(record_date, 'MI') < '30' THEN INTERVAL '0' MINUTE ELSE INTERVAL '30' MINUTE END AS half_hour,
       SUM(col_8) AS total_money
FROM table_name
GROUP BY TRUNC(record_date, 'HH24') + CASE WHEN TO_CHAR(record_date, 'MI') < '30' THEN INTERVAL '0' MINUTE ELSE INTERVAL '30' MINUTE END

随机取数

SELECT *
FROM test a
--WHERE rownum = FLOOR(DBMS_RANDOM.VALUE(1, 10))
WHERE MOD(rownum, floor(DBMS_RANDOM.VALUE(1, 10))) = 0
FETCH FIRST ROW ONLY

-- 重建排序
SELECT * FROM (
    SELECT a.*, rownum as rn
    FROM test a
)
WHERE MOD(rn, floor(DBMS_RANDOM.VALUE(1, 1))) = 0
ORDER BY dbms_random.value
FETCH FIRST ROW ONLY;

-- Oracle类PostgreSQL的generate_series函数
SELECT ROWNUM AS num FROM dual CONNECT BY ROWNUM <= 3;
SELECT LEVEL AS num FROM DUAL CONNECT BY LEVEL <= 3;
-- 使用序列
CREATE SEQUENCE seq_num START WITH 1 INCREMENT BY 1 NOMAXVALUE;
SELECT NEXT VALUE FOR seq_num AS num FROM DUAL WHERE NEXT VALUE FOR seq_num <= 3;

# PostgreSQL

文本搜索和分析 https://github.com/zombodb/zombodb (opens new window)

.\initdb.exe -D "D:\PostgreSQL\data" --locale=C --username=postgres --encoding=UTF8
.\pg_ctl.exe -D "D:\PostgreSQL\data" -l 日志文件 start

-- 插入或更新 upsert
INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

-- https://zhuanlan.zhihu.com/p/342722338
-- https://dba.stackexchange.com/questions/91247/optimizing-a-postgres-query-with-a-large-in/91539
-- https://www.postgresql.org/docs/current/arrays.html
-- https://www.postgresql.org/docs/current/functions-array.html
-- 临时表
with tmp_table as (
    select unnest('{
        10000, 11000, 12000, 13000, 14000, 15000, 16000, 17000, 18000, 19000, 20000, 21000, 22000
    }'::bigint[]) "aid"
)
select * from tmp_table

-- https://zhuanlan.zhihu.com/p/38381497
-- WITH ORDINALITY返回记录的每一行行号
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;

-- 常量子查询
字段 in ( VALUES (10000), (11000), (12000), (13000), (14000), (15000), (16000), (17000), (18000), (19000), (20000));

-- JSON
-- https://www.postgresql.org/docs/current/functions-json.html
-- https://www.crunchydata.com/developers/playground/basics-of-jsonb
SELECT * FROM jsonb_to_recordset('[{"name": "batman"}, {"name": "superman"}]'::jsonb) AS x(name TEXT);
SELECT * FROM json_array_elements_text('["batman", "superman"]');

查询Postgres数据库中的所有表信息（表名、备注）

SELECT
	relname AS tabname
	, cast(obj_description( relfilenode, 'pg_class') AS VARCHAR ) AS COMMENT
    , b.description
FROM pg_class c
LEFT JOIN pg_description b ON b.objsubid =0 AND a.oid=b.objoid
WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%'
    -- AND relchecks=0 -- 过滤掉分表
ORDER BY relname


-- 查询表字段信息
SELECT col_description(a.attrelid,a.attnum) AS comments
--, format_type(a.atttypid,a.atttypmod) AS column_type"
, t.typname AS column_type
, a.attname AS column_name
, CASE WHEN a.attnotnull THEN 0 ELSE 1 END AS nullable
, CASE WHEN p.conname IS NULL THEN '' ELSE 'PRI' END AS column_key
, CASE WHEN a.attlen>0 THEN a.attlen
        WHEN t.typname='numeric' THEN pc.numeric_precision
        WHEN t.typname='timestamp' THEN pc.datetime_precision
        ELSE a.atttypmod-4
  END AS data_length
, CASE WHEN a.attlen>0 THEN a.attlen
        WHEN t.typname='numeric' THEN pc.numeric_precision-pc.numeric_scale
        WHEN t.typname='timestamp' THEN pc.datetime_precision
        WHEN t.typname='varchar' THEN pc.character_maximum_length
        ELSE a.atttypmod-4
  END AS data_precision
, pc.numeric_scale AS data_scale
FROM pg_class c
LEFT JOIN pg_attribute a ON a.attrelid=c.oid
LEFT JOIN pg_constraint as p ON c.oid=p.conrelid AND p.conkey[1]=a.attnum AND p.contype='p'
LEFT JOIN pg_type t ON t.oid=a.atttypid
LEFT JOIN pg_namespace n ON n.oid=c.relnamespace
-- LEFT JOIN pg_tables tb ON tb.schemaname=n.nspname AND tb.tablename=c.relname
LEFT JOIN information_schema.columns pc ON pc.table_schema=n.nspname AND pc.table_name=c.relname AND pc.column_name=a.attname
WHERE a.attisdropped='f' AND a.attnum>0 AND c.relname='表名'
-- ORDER BY a.attnum

查询Postgres数据库中的表字段名、类型、注释、注释是否为空

-- information_schema.COLUMNS
SELECT
	a.attname AS NAME,
	col_description( a.attrelid, a.attnum ) AS COMMENT,
	format_type( a.atttypid, a.atttypmod ) AS type,
	a.attnotnull AS notnull
FROM
	pg_class AS c,
	pg_attribute AS a
WHERE
	c.relname = '表名'
	AND a.attrelid = c.oid
	AND a.attnum >0

UUID

-- 不建议在高并发下使用，以下三个语句都可以生成
SELECT uuid_in(md5(random()::text || now()::text)::cstring);
SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring);
SELECT md5(random()::text || clock_timestamp()::text)::uuid;
-- 去掉-，32位
SELECT REPLACE(md5(random()::text || clock_timestamp()::text),'-','');

日期时间

https://www.postgresql.org/docs/current/functions-datetime.html (opens new window)

select to_timestamp('2022-08-02 00:00:00', 'yyyy-MM-dd hh24:mi:ss');
select to_date('2022-08-02 23:59:59', 'yyyy-MM-dd HH24:mi:ss');
select to_char(now(), 'yyyy-MM-dd HH24:mi:ss');
select date_trunc('day', now()) - interval '1d' + ('6 hours')::INTERVAL;
-- 得到当天凌晨0点0分0秒的日期
select date_trunc('day', now());
-- 获取今天最后的时间（即午夜之前的那一刻）
SELECT CURRENT_DATE + INTERVAL '1 day' - INTERVAL '1 microsecond';
SELECT (date_trunc('day', now()) + INTERVAL '1 day' - INTERVAL '1 microsecond')::timestamp;
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP) + INTERVAL '1 day - 1 microsecond';
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP) + INTERVAL '1 day' - INTERVAL '1 microsecond';
-- 月末
select date_trunc('month', now() + '1 months') + '-1 days';

-- 获取周数
select extract(week FROM timestamp '2022-01-01') week;
SELECT date_part('week', timestamp '2022-01-01') week;
-- 遍历两个日期的每一天 https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql
select * from generate_series('2022-09-21 00:00:00'::TIMESTAMP, '2022-09-22 00:00:00'::TIMESTAMP, '1 day');
-- 遍历两个日期的每小时
select * from generate_series('2022-09-21 00:00:00'::TIMESTAMP, '2022-09-22 00:00:00'::TIMESTAMP, '1 hour');
-- 每一天每一小时每一行的列数据
select
    d::date::text || ' ' ||
        to_char(d::time, 'HH24:MM:SS') || ' - ' ||
        to_char(d::time + interval '1 hour' - interval '1 second', 'HH24:MM:SS') as hour
from
    generate_series(
       (date '2022-09-21')::timestamp,
       (date '2022-09-22')::timestamp,
       interval '1 hour'
     ) as d
-- 计算时间差，实际时间不到1小时时，DATEDIFF会返回1，而DATE_PART返回0
SELECT DATE_PART('day', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp) * 24 + 
        DATE_PART('hour', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp);
-- 计算月份差
SELECT 12*EXTRACT(YEAR from age('2023-06-01','2022-01-01'))+EXTRACT(MONTH from age('2023-06-01','2022-01-01'));
-- 提取days时结果是准确的；但提取hours的时候，出现的小时数没有考虑日期，最大23
SELECT EXTRACT(DAYS FROM NOW() - '2020-01-31 10:00:00'::timestamp);
SELECT ROUND((EXTRACT(EPOCH FROM timestamp '2023-12-25 12:00:00' - timestamp '2023-12-20 00:00:00') / 86400)::NUMERIC, 2) AS days_diff;

快速复制备份表

-- 表结构复制，包括索引和约束
create table schema.table_name_bak (like schema.table_name including all);

整数除法保留小数

select 1/2::float8
select 1/2::numeric
select 1/cast(2 as numeric)

# SQLite3

连接符

连接符	说明
-	算术减法
!=	关系不等于
%	算术模量
&	逻辑与
*	算术乘法
/	算术除法
\|	逻辑或
\|\|	字符串串联
+	算术加法
<	关系小于
<<	按位右移
<=	关系式小于或等于
<>	关系不等于
=	关系等于
==	关系等于
>	关系大于
>=	关系大于或等于
>>	按位左移
AND	逻辑与
GLOB	关系文件名匹配
IN	逻辑输入
LIKE	关系字符串匹配
OR	逻辑或

← 关系型SQL标准