Bajins

# 关系型数据库

# 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 反向索引

通用日期时间

获得当前日期+时间（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) 持久性

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

运行模式

  1. 自动提交事务：默认事务管理模式。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。
  2. 显式事务：以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。
  3. 隐性事务：当连接以此模式进行操作时，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;
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

日期时间

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

-- 插入或更新 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),'-','');

日期时间

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);

快速复制备份表

-- 表结构复制，包括索引和约束
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 逻辑或

←