Bajins

# 关系型数据库

# Flag

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

分布式/集群数据库

嵌入式/本地数据库

分析型/OLAP数据库

其他/新兴数据库

递归

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

架构模型对比

特性 Oracle PostgreSQL MySQL
(User/Role)与 Schema 一对一，用户名 = Schema 名 用户 ≠ Schema，但可关联 无 Schema 概念（Schema ≈ Database）
Schema 用户拥有的对象集合 命名空间，一个 DB 可有多个 Schema 等同于 Database
Database 一个物理数据库（或 PDB） 一个逻辑数据库，包含多个 Schema 一个命名空间，包含表等对象
User与Role 用户和角色分离 用户是带 LOGIN 的角色，统一模型 用户和角色分离（8.0+ 支持角色）
跨数据库访问 同实例内可通过 DB Link 需 dblink 或 foreign data wrapper 不支持直接跨库访问（需 dbname.tablename 在同一实例）
默认命名空间 用户自己的 Schema public 和用户同名 Schema（在 search_path 中） 当前 USE database 的数据库
权限管理粒度 用户 → 角色 → 对象权限 角色 → 成员 → 对象权限 用户/角色 → 数据库/表级权限
  • Oracle
CREATE USER alice IDENTIFIED BY pwd;
-- 自动创建 Schema ALICE
CREATE TABLE alice.employees (...);

Oracle Instance
└── Database (CDB or PDB)
    ├── User: SCOTT → Schema: SCOTT → Tables, Views...
    ├── User: HR     → Schema: HR     → Tables...
    └── Roles: DBA, CONNECT...
  • PostgreSQL
CREATE USER alice;
CREATE SCHEMA alice AUTHORIZATION alice;
SET search_path TO alice;
CREATE TABLE employees (...); -- 存入 alice Schema

PostgreSQL Cluster (Instance)
├── Database: sales
│   ├── Schema: public
│   ├── Schema: sales_team
│   └── Tables...
├── Database: hr
│   ├── Schema: public
│   └── Tables...
└── Roles: alice (LOGIN), analyst (NOLOGIN), ...
  • MySQL
CREATE DATABASE hr;
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'pwd';
GRANT SELECT ON hr.* TO 'alice'@'localhost';
USE hr;
CREATE TABLE employees (...); -- 在 hr 数据库中

MySQL Instance
├── Database: sales
│   └── Tables...
├── Database: hr
│   └── Tables...
└── Users: 'alice'@'localhost', 'bob'@'%'
└── Roles: analyst, admin (8.0+)

# 事务

  • 脏数据：未提交的数据
  • 不可重复读（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
  • 反范式：用空间换时间，通过适当的数据冗余提高查询效率，但冗余数据会牺牲数据一致性

# SQLite3

连接符

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

←