# 关系型数据库

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

通用日期时间

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时间单位

unit	说明
YEAR	年
QUARTER	季度
MONTH	月
DAY	天
HOUR	时
MINUTE	分
WEEK	周
SECOND	秒
microseconds	毫秒
YEAR_MONTH	年:月
DAY_HOUR	日:时
DAY_MINUTE	日:分
DAY_SECOND	日:秒
HOUR_MINUTE	时:分
HOUR_SECOND	时:秒
MINUTE_SECOND	分:秒

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

# 事务

脏数据：未提交的数据
不可重复读（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;

系统表

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

日期时间

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 from dual;

# PostgreSQL

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

-- 插入或更新 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
FROM
	pg_class c
WHERE
	relkind = 'r'
	AND relname NOT LIKE 'pg_%'
	AND relname NOT LIKE 'sql_%'
    -- AND relchecks=0 -- 过滤掉分表
ORDER BY
	relname

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

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());
select date_trunc('day', now()) - interval '1d' + ('6 hours')::INTERVAL;
-- 获取周数
select extract(week FROM timestamp '2022-01-01') week;
SELECT date_part('week', timestamp '2022-01-01') week;
-- 月末
select date_trunc('month', now() + '1 months') + '-1 days';
-- 遍历两个日期的每一天 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);
-- 提取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

https://github.com/sqlite/sqlite (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)
Java实现 https://sqljet.com (opens new window)
分布式关系数据库 https://github.com/rqlite/rqlite (opens new window)
https://github.com/jlongster/absurd-sql (opens new window)

连接符

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

← 关系型SQL标准