Database
Database - 数据库
关系型数据库
关系模式的组成
一个关系模式应当是一个五元组 R(U, D, DOM, F)
这里 R 是符号化的元组语义：
- U 为一组属性，属性名的集合
- D 为属性组 U 中的属性所来自的域
- DOM 为属性到域的映射
- F 为属性组 U 上一组数据依赖（是一组数据依赖的集合）
由于 D, DOM 与模式涉及关系不大，一般把关系模式看作一个三元组：R <U, F> ，当且仅当 U 上一个关系 r 满足 F 时，r 称为关系模式 R<U, F> 的一个关系。
SQL
一些特殊字符：
` 用于表示数据库名、表名、字段名 & 且 || 连接符 @ 定义变量 % 模糊查询 . 通配符 <> 不等于 # 注释 /* */ 注释
数据库操作：
# 创建数据库 create <database>; # 切换数据库 use <database>; # 查看数据库 show databases; # 修改数据库 alter database <database> [option]; # 删除数据库 drop database <database>
表操作：
# 创建表 create table [database].<table_name> ( <column1> <datatype> [primary key] [autoincrement] [not null], <column2> <datatype> [not null] [default <value>], [foreign key(<column>) references <table_name>.<column>] ); # 删除表 drop table [database].<table_name>; # 插入记录 insert into <table_name> (<column1>[, <column2>, ...]) values(<value1>[, <value2>, ...]); # 删除记录 delete from <table_name> [where <condition>]; # 更新记录 update <table_name> set <column1=value1>, [column2=value2], ... [where <condition>];
select
alter
ALTER TABLE table_name DROP c; ALTER TABLE table_name ADD c INT [FIRST | AFTER <column_name>]; ALTER TABLE table_name MODIFY c CHAR(10); ALTER TABLE table_name MODIFY c BIGINT NOT NULL DEFAULT 100; ALTER TABLE table_name CHANGE c d BIGINT; ALTER TABLE table_name ALTER c DROP DEFAULT; ALTER TABLE table_name RENAME new_table; ALTER TABLE table_name ENGINE = MYISAM; ALTER TABLE table_name DROP FOREIGN KEY key_name;
union
连接两个或两个以上的 select 语句结果组合到一个结果集合中。
order by
排序，默认升序 AES ，降序 DESC
group by
分组，常用函数： COUNT, SUM, AVG
ORM
Object-Relationship Mapping: 对象关系映射，将对关系型数据库的操作规范化，防止注入。ORM 的作用是在关系型数据库和业务实体对象之间作一个映射，在具体的操作业务对象的时候，就不需要再直接编写复杂的 SQL 语句，只需操作对象的属性和方法。
MySQL
瑞典 MySQL AB 公司开发，后被收购，现属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。有开源免费的社区版，也有商业收费版本。
注释符号：
# ... 标准 SQL 语法不支持这种 -- ... 注意两个 dash 后要有一个 空格，标准 SQL 语法不需要接空格 /* ... */
Installation
docker, 官方仓库安装均可
连接 MySQL 数据库： mysql -u <user> -p <pwd>
Data Type
- 整型
- TINYINT
- SMALLINT
- NUMERIC
- MEDIUMINT
- INT/INTEGER
- BIGINT
- 浮点型
- FLOAT
- DOUBLE
- REAL
- 日期/时间
- DATE
- TIME
- YEAR
- DATETIME
- TIMESTAMP
- 字符串
- CHAR
- VARCHAR
- TINYTEXT
- BLOB (Binary Large Object)
- TEXT
- MEDIUMBLOB
- LONGBLOB
- LONGTEXT
MySQL SQL syntax
-- 添加用户 INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'testUser', PASSWORD('testPwd'), 'Y', 'Y', 'Y'); -- 重载授权表 FLUSH PRIVILEGES;
用户权限：
- selectpriv
- insertpriv
- updatepriv
- deletepriv
- createpriv
- droppriv
- reloadpriv
- shutdonwpriv
- processpriv
- filepriv
- grantpriv
- referencespriv
- indexpriv
- alterpirv
-- 授予权限 by grant GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON TestDB.* TO 'testUser@localhost' IDENTIFIED BY 'testPwd';
管理：
USE <DB_name>; SHOW DATABASES; SHOW TABLES; SHOW COLUMNS FROM <table_name>; SHOW INDEX FROM <table_name>; SHOW TABLE STATUS [From <DB_name>][LIKE <'pattern'>]\G; -- \G 查询结果按列打印
索引：
-- 创建索引 CREATE INDEX <index_name> ON <table_name> <(column_name)> -- 添加索引 ALTER TABLE <table_name> ADD INDEX [index_name] <(column_name)> -- 删除索引 DROP INDEX <index_name> ON table_name;
MySQL informationschema
mysql 5.0 以上中有一个重要的系统数据库 informationschema，通过此数据库可访问 mysql 中存在的数据库名、表名、字段名等元数据。 有三个表可用于 sqli :
- informationschema.columns
- tableschema 数据库名
- tablename 表名
- columnname 列名
- informationschema.tables
- tableschema 数据库名
- tablename 表名
- informationschema.schemata
- schemaname 数据库名
MySQL builtin function
- length(str) 返回字符串的长度
- substr(str, pos, len) 从 pos 位置截取 len 长度的子字符串，索引从 1 开始
- mid(str, pos, len) 同 substr()
- ascii(str) 返回字符串 str 最左边字符的 ASCII 码
- ord(str) 将字符或 boolean 转为 ASCII 码
- if(a, b, c) a 为 condition ，为 true 返回 b，为 false 返回 c
- groupconcat()
- benchmark(cnt, sql) 用于测试函数或者表达式的执行速度
- 执行 True False NULL 均可
- 仅支持单行单列的结果，否则报错
- 内部 sql 失败，benchmark 也失败
- datediff(dt1, dt2) 返回两个日期相差的天数
- concat(a, b, c) 返回 a, b, c 的拼接字符串
inner join, left join, right join, full join1
TableA: id firstName lastName ....................................... 1 arun prasanth 2 ann antony 3 sruthy abc 6 new abc TableB: id age place ................ 1 24 kerala 2 24 usa 3 25 ekm 5 24 chennai
inner join
两张表使用内连接查询时，得到的结果是两张表中完全匹配的行集。
SELECT TableA.firstName, TableA.lastName, TableB.age, TableB.Place FROM TableA INNER JOIN TableB ON TableA.id = TableB.id
结果为：
firstName lastName age place .............................................. arun prasanth 24 kerala ann antony 24 usa sruthy abc 25 ekm
只有两张表都有的 id 才内连接了 ( id=1,2,3 )
left join
左连接查询会返回左表中所有行，无论这些行是不是有任何一行在右表中匹配。
SELECT TableA.firstName, TableA.lastName, TableB.age, TableB.Place FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id
结果为：
firstName lastName age place ............................................... arun prasanth 24 kerala ann antony 24 usa sruthy abc 25 ekm new abc NULL NULL
TableA ( id=1,2,3,6 ) 的行都存在于结果中，而 TableB 没有 id = 6 ，所以 age 和 place 为 NULL 空值。
right join
右连接查询会返回右表中所有行，无论这些行是不是有任何一行在左表中匹配。
SELECT TableA.firstName, TableA.lastName, TableB.age, TableB.Place FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id
结果为：
firstName lastName age place ............................................... arun prasanth 24 kerala ann antony 24 usa sruthy abc 25 ekm NULL NULL 24 chennai
TableB ( id=1,2,3,5 ) 的行都存在于结果中，而 TableA 没有 id = 5 ，所以 firstName 和 lastName 为 NULL 空值。
full join
全连接，返回左右表中的所有行。
SELECT TableA.firstName, TableA.lastName, TableB.age, TableB.Place FROM TableA FULL JOIN TableB ON TableA.id = TableB.id
结果为：
firstName lastName age place ............................................... arun prasanth 24 kerala ann antony 24 usa sruthy abc 25 ekm NULL NULL 24 chennai new abc NULL NULL
相当于左连接与右连接的并集。
datetime & timestamp 1
datetime: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp: 1970-01-01 00:00:01 ~ 2038-01-09 03:14:07 (UTC)
或者采用 BIGINT 存储自纪元以来的毫秒数。占用 8 字节。
自 MySQL 5.6.4 开始， datetime 占用 5 字节 + 0 ~ 3 字节的 FSP 分秒精度。
MyISAM & InnoDB 1
MyISAM 和 InnoDB 之间的主要区别在于参照完整性和事务。还有其他区别，例如锁定、回滚和全文搜索。
参照完整性
参照完整性确保表之间的关系保持一致。更具体地说，当一个表（例如 Listings）有一个外键（例如 Product ID）指向另一个表（例如 Products）时，当指向的表发生更新或删除时，这些更改会级联到链接的表。在该示例中，如果重命名产品，则链接的表的外键也会更新；如果从Products表中删除产品，则指向已删除条目的 Listings 表中得到任何列表也将被删除。此外，任何 Listings 表中的新列表都必须具有指向有效的现有条目的外键。
InnoDB 是一个关系型 DBMS (RDBMS)，因此具有参照完整性，而 MyISAM 则没有。
事务和原子性
事务：比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！事务可以管理 insert, update, delete 此类的语句
事务是必须满足4个条件（ACID）：
- 原子性（Atomicity，不可分割性）
- 一致性（Consistency）
- 隔离性（Isolation，独立性）
- 持久性（Durability）
使用数据操作语言 (DML) 语句管理表中的数据，例如 SELECT、INSERT、UPDATE 和 DELETE。事务将两个或多个 DML 语句组合成一个工作单元，因此要么应用整个单元，要么不应用。
MyISAM 不支持事务，而 InnoDB 支持。
如果在使用 MyISAM 表时操作被中断，该操作将立即中止，并且受影响的行（甚至每行中的数据）仍然受到影响，即使该操作没有完成。
如果一个操作在使用 InnoDB 表时被中断，因为它使用具有原子性的事务，任何没有完成的事务都不会生效，因为没有提交。
表锁定与行锁定
当查询 MyISAM 表时，正在查询的整个表将被锁定。这意味着后续查询将仅在当前查询完成后才能执行。如果您正在读取一个大表，并且有频繁的读写操作，这可能意味着大量的查询积压。
而当查询 InnoDB 表时，只有涉及的行被锁定，表的其余部分仍然可进行 CRUD 操作。这意味着查询可以在同一个表上同时运行，只要它们不使用同一行。
InnoDB 中的此功能称为并发。尽管并发性很好，但在表的范围查询时有一个缺点，因为在内核线程之间切换存在开销，我们应该对内核线程设置限制以防止服务器停止。
事务和回滚
当在 MyISAM 中执行一个操作时，更改会立刻生效；在 InnoDB 中，这些更改可以回滚。用于控制事务的最常用命令是 COMMIT、ROLLBACK 和 SAVEPOINT。
- COMMIT - 您可以编写多个 DML 操作，但只有在进行 COMMIT 时才会保存更改
- ROLLBACK - 您可以丢弃任何尚未提交的操作
- SAVEPOINT - 实现回滚到指定保存点
可靠性
MyISAM 不提供数据完整性——硬件故障、不正常的关机操作都可能导致数据损坏。这将需要修复或重建索引和表。
而InnoDB 使用事务日志、双写缓冲区和自动校验和和验证来防止数据损坏。在 InnoDB 进行任何更改之前，它会将事务之前的数据记录到一个名为 ibdata1 的系统表空间文件中。如果发生崩溃，InnoDB 将通过这些日志来自动恢复。 全文索引
InnoDB 在 MySQL 5.6.4 版本之前不支持 FULLTEXT 索引。
但是，这不是使用 MyISAM 的理由。最好使用最新版本的 MySQL 。并不是说使用 FULLTEXT 索引的 MyISAM 表不能转换为 InnoDB 表。
结论
总之，InnoDB 应该是我们默认的存储引擎。在有特定需求时可以选择 MyISAM 或其他数据类型。
atomic DDL 2
MySQL 8.0 推出的新特性，MySQL 5.7 的字典信息保存在非事务表中，并且存放在不同的文件中（.FRM，.PAR，.OPT，.TRN，.TRG 等)。所有 DDL 操作都不是 Crash Safe，而且对于组合 DDL（ALTER 多个表）会出现有的成功有的失败的情况，而不是总体失败。这样主从复制就出现了问题，也导致基于复制的高可用系统不再安全。
DDL: Data Definition Language
只有 InnoDB 才支持，支持数据库、表空间、表、索引的 CREATE、ALTER 以及 DROP 语句，以及 TRUNCATE TABLE 语句；支持存储过程、触发器、视图以及用户定义函数（UDF）的 CREATE 和 DROP 、ALTER 操作，用户和角色的 CREATE、ALTER、DROP 语句，以及适用的 RENAME 语句，以及 GRANT 和 REVOKE 语句。
不支持的语句：
- INSTALL PLUGIN, UNINSTALL PLUGIN
- INSTALL COMPONENT, UNINSTALL COMPONENT
- REATE SERVER, ALTER SERVER, DROP SERVER
首先，8.0 将字典信息存放到事务引擎的系统表（InnoDB 存储引擎）中。这样 DDL 操作转变成一组对系统表的 DML 操作，从而失败后可以依据事务引擎自身的事务回滚保证系统表的原子性。
似乎 DDL 原子性就此就可以完成，但实际上并没有这么简单。首先字典信息不光是系统表，还有一组字典缓存，如：
- Table Share 缓存
- DD 缓存
- InnoDB 中的 dict
此外，字典信息只是数据库对象的元数据，DDL 操作不光要修改字典信息，还要实实在在的操作对象，以及对象本身在内存中缓存。
- 表空间
- Dynamic meta
- Btree
- ibd 文件
- buffer pool 中表空间的 page 页
此外，binlog 也要考虑 DDL 失败的情况。
因此，原子 DDL 在处理 DDL 失败的时候，不光是直接回滚系统表的数据，而且也要保证内存缓存，数据库对象也能回滚到一致状态。
SQLite
轻量级的关系数据库。
查看所有表格： .tables
用法：
.open filename -- 打开数据库文件 .show -- 显示 SQLite命令提示符的默认设置 .q -- 退出 .databases -- 显示数据库 .help -- 帮助 .dump -- 导入/导出数据库 .table -- 显示表 .schema -- 查看表的详细信息
可以使用 sqlite <db_name> [command] 打开数据库或直接执行命令。
常用配置：
.header on -- 输出表头（列名） .mode column -- 列对齐 .timer on -- 开启 CPU 定时器 .width n1,n2... -- 设置输出列的宽度
数据类型
| type | caption |
|---|---|
| NULL | 空值 |
| int | 整型 |
| text | 文本字符串 |
| blob | Binary Large OBject |
| integer | 一个带符号的证书，根据的值的大小占用 1/2/3/4/6/8 字节 |
| real | 值是浮点数，占用 8 字节 |
运算符
| op | caption |
|---|---|
== = |
判等 |
!= <> |
不等于 |
!< |
是否不小于 |
!> |
是否不大于 |
| AND | 并 |
| BETWEEN | 给定范围 |
| EXISTS | 在满足一定条件的制定表中搜索行 |
| IN | 某个值与一系列值比较 |
| NOT | 否定 |
| LIKE | 模糊匹配 |
| GLOB | 模糊匹配，大小写敏感 |
| IS NULL | 是否为 NULL |
| IS | 与 = 类似 |
| IS NOT | 与 != 类似 |
| UNIQUE | 搜索指定表中的每一行，确保唯一性 |
| || | 连接两个字符串 |
文档型数据库
ElasticSearch / ELK(ES + Logstash + Kibana)
MongoDB
installation
CentOS/RedHat Repositry:
[mongodb-org-4.2] name=MongoDB Repository baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.2/x86_64/ gpgcheck=1 enabled=1 gpgkey=https://www.mongodb.org/static/pgp/server-4.2.asc
dnf install -y mongodb-org sed -i 's/127.0.0.1/0.0.0.0/' /etc/mongod.conf service mongod start mongod --bind_ip 0.0.0.0 --port 27017 --dbpath /var/mongo/data/test
db.createUser(
{
user: "adminUser",
pwd: "dRcHKje943ZJ",
roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
}
)
use admin
db.auth("adminUser", "dRcHKje943ZJ")
Usage
// 创建用户 db.createUser({ user: 'testUser', pwd: 'test', roles: [ { role: 'read', db: 'admin' }, { role: 'readWrite', db: 'testDB' } ], }) // 查看用户列表 db.system.users.find() // 查看用户信息 db.runCommand({usersInfo:'usename'}) // 删除用户 db.system.users.remove({user: 'username'}); // 更新用户 db.runCommand( { updateUser: 'name', pwd: 'pwd', customData: {title: 'xxx'} } ) // 修改用户密码 db.changeUserPassword('user', 'pwd'); // 创建集合 db.createCollection('name', option) // [option] { capped: false, autoIndexID: true, size: 1024, // 字节 max: 100 // 最大文档数 } // 删除集合 db.getCollection('dbname').drop() // 文档操作 - find() - insert() - remove() - update() /* query `$set` 设置字段的值 `$rename` 重命名字段 `$push` 将条目推送到数组 `$inc` 递增该字段的值 ObjecetId() 这个函数将字符串转为 objectId 数据类型 */