# 关系型数据库
# Flag
关系型数据库(Relational database),是指采用了关系模型来组织数据的数据库,其以行和列的形式存储数据,以便于用户理解 ,关系型数据库这一系列的行和列被称为表,一组表组成了数据库
- https://github.com/opengauss-mirror (opens new window)
- https://github.com/MariaDB (opens new window)
- https://github.com/percona/percona-server (opens new window)
- SQL代理 https://github.com/sysown/proxysql (opens new window)
- http://www.h2database.com (opens new window)
- https://sourceforge.net/projects/hsqldb (opens new window)
- https://github.com/FirebirdSQL/firebird (opens new window)
- https://github.com/duckdb/duckdb (opens new window)
- https://github.com/babelfish-for-postgresql (opens new window)
- https://github.com/greenplum-db (opens new window)
递归
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 转时间戳
主流数据库数据类型对应关系
- https://www.postgresql.org/docs/current/datatype.html (opens new window)
- https://dev.mysql.com/doc/refman/8.0/en/data-types.html (opens new window)
- https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html (opens new window)
- https://learn.microsoft.com/zh-cn/sql/t-sql/data-types (opens new window)
- https://www.sqlite.org/datatype3.html (opens new window)
数据类型分类 | 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
- Oracle with as + /+ materialize/ 优化 (opens new window)
- Oracle两种临时表的创建及使用方法 (opens new window)
- https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/preface.html (opens new window)
空字符串''同等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
- https://github.com/topics/postgrest (opens new window)
- https://github.com/topics/postgresql (opens new window)
- https://github.com/dhamaniasad/awesome-postgres (opens new window)
- https://github.com/postgres/postgres (opens new window)
- https://github.com/postgrespro (opens new window)
- https://github.com/citusdata (opens new window)
- https://github.com/neondatabase/neon (opens new window)
- https://github.com/pgpartman (opens new window)
- https://github.com/PostgREST/postgrest (opens new window)
- https://github.com/CrunchyData (opens new window)
- 客户端 https://github.com/sosedoff/pgweb (opens new window)
- https://github.com/prest/prest (opens new window)
- https://github.com/alibaba/PolarDB-for-PostgreSQL (opens new window)
- https://github.com/npgsql (opens new window)
- 管理器 https://github.com/sorintlab/stolon (opens new window)
- https://github.com/achristmascarl/rainfrog (opens new window)
- 连接池工具 https://github.com/pgbouncer/pgbouncer (opens new window)
- 扩展Rust开发框架 https://github.com/tcdi/pgx (opens new window)
- 开发指南 https://github.com/dongxuyang1985/postgresql_dev_guide (opens new window)
- 不剪发的Tony老师《SQL编程思想》作者 (opens new window)
- Postgresql库常用系统表 (opens new window)
- PostgreSQL upsert(插入更新)教程 (opens new window)
- PostgreSQL_树形结构的递归查询 (opens new window)
- postgresql递归查询 (opens new window)
- PostgreSQL窗口函数 (opens new window)
- PostgreSQL 触发器 (opens new window)
- PostgreSQL的存储过程及基本使用 (opens new window)
- PostgreSQL事件触发器实战教程 (opens new window)
- pg12.5中分区表的一些操作 (opens new window)
- Postgresql12 分区表实例以及自动添加分区 (opens new window)
- postgresql常见数值,字符,日期类型常见函数总结 (opens new window)
- PostgreSQL学习手册(目录) (opens new window)
- PostgreSQL中的OID (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),'-','');
日期时间
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
- https://github.com/sqlite/sqlite (opens new window)
- https://github.com/tursodatabase/libsql (opens new window)
- SQLite教程(内置日期和时间函数) (opens new window)
- SQLite 教程 (opens new window)
- SQLite 教程 (opens new window)
- SQLite3 数据类型与亲和类型 (opens new window)
- 查看 https://github.com/coleifer/sqlite-web (opens new window)
- 扩展 https://github.com/mycelial/mycelite (opens new window)
- https://github.com/sqlitebrowser/sqlitebrowser (opens new window)
- 加密SQLite https://github.com/sqlcipher (opens new window)
- https://github.com/utelle/wxsqlite3 (opens new window)
- https://github.com/resilar/sqleet (opens new window)
- Java实现 https://sqljet.com (opens new window)
- 分布式关系数据库 https://github.com/rqlite/rqlite (opens new window)
- https://github.com/jlongster/absurd-sql (opens new window)
- 后端 https://github.com/pocketbase/pocketbase (opens new window)
连接符
连接符 | 说明 |
---|---|
- | 算术减法 |
!= | 关系不等于 |
% | 算术模量 |
& | 逻辑与 |
* | 算术乘法 |
/ | 算术除法 |
| | 逻辑或 |
|| | 字符串串联 |
+ | 算术加法 |
< | 关系小于 |
<< | 按位右移 |
<= | 关系式小于或等于 |
<> | 关系不等于 |
= | 关系等于 |
== | 关系等于 |
> | 关系大于 |
>= | 关系大于或等于 |
>> | 按位左移 |
AND | 逻辑与 |
GLOB | 关系文件名匹配 |
IN | 逻辑输入 |
LIKE | 关系字符串匹配 |
OR | 逻辑或 |
← 关系型SQL标准