Bajins

# 关系型数据库

# Flag

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

遵循ACID事务规则

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) 持久性

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

# 数据库设计

范式：Normal Format

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

  • 范式是离散数学里的概念
  • 范式目标是在满足组织和存储的前提下使数据结构冗余最小化
  • 范式级别越高，表的级别就越标准
  • 第一范式：1NF，确保表中每一列数据的原子性，不可再分！
  • 第二范式：2NF，在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。
  • 第三范式：3NF，再满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。
  • BCNF范式,4NF,5NF
  • 反范式：用空间换时间，通过适当的数据冗余提高查询效率，但冗余数据会牺牲数据一致性

# ORACLE

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

系统表

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

修改字段

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

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

-- 清空数据 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;

死锁

-- 查询死锁会话
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;

# Postgre

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

查询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),'-','');

日期时间

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' + interval '6 hours';
-- 获取周数
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);

# SQLite3

连接符

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

←