数据库¶

关系代数¶

Operation	Description	Symbol
Select	satisfy a predicate	$\sigma_{predicate}(relation)$
Project	select a subset of attributes	$\Pi_{attribute-list}(relation)$
Cartesian-Product	combine two relations	$relation_1 \times relation_2$
Join	combine two relations	$\sigma_{common\ predicate}(relation_1\times relation_2) = relation_1 \bowtie_{predicate} relation_2$
Basic Set Operation	union, intersection, difference	$relation_1 \cup relation_2, relation_1 \cap relation_2, relation_1 - relation_2$
Assignment	assign a new name to a relation	$new\_relation \leftarrow expression$
Rename	rename the attributes of a relation	$\rho_{new\_name(new\_attribute\_list)}(relation)$

Schema Diagram¶

关系
- 主键用下划线标记
- 外键引出箭头，指向外键所在实体对应的键

MySQL¶

知识结构

主要依据的书籍为：

MySQL Crash Course 入门
MySQL Cookbook 深入理解

可以按两种方式梳理 MySQL 的知识：

语句
对象

需要掌握的语句：

SHOW, CREATE, SELECT, ...

需要掌握的对象：

数据库
表
函数
视图
存储过程
游标
事务

安装、维护和技巧¶

MySQL Debian 有一个配置文件 /etc/mysql/debian.conf 记录了用户 debian-sys-maint 的相关信息。这个用户其实是用于维护、日志记录的。
root 默认认证方式似乎是 auth_plugin 而不是密码，初次安装后一般都连接不上。可以使用 sudo mysql 进入（这不需要密码），然后执行下面的命令修改密码为 root：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

连接命令：mysql
- -u root 指定用户
- -p 要求密码
- --socket /var/run/mysqld/mysqld.sock 可能找不到默认的 socket 文件，此时需要查询 journalctl mysql 找到
使用 MySQL WorkBench 提供 GUI 操作界面
基本维护命令：

SHOW DATABASES; TABLES; STATUS;
SHOW CREATE DATABASE some_db; TABLE some_table;
SHOW GRANTS; ERORRS; WARNINGS;
DESCRIBE some_table; SHOW COLUMNS FROM some_table;
USE some_db;
HELP some_command;

注释：-- 开头
如果只需查看某个信息但它太长了，可以使用 \G 结尾，这样会以列的形式显示。比如查看 SHOW CREATE TABLE some_table\G：

mysql> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_num` int NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int NOT NULL,
  PRIMARY KEY (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

数据类型¶

==todo==

语句¶

CREATE：知道数据库、表、视图、索引、过程、用户怎么创建

# 数据库
CREATE DATABASE databasename;
# 表
CREATE TABLE tablename
(
    column datatype [NULL|NOT NULL] [CONSTRAINTS],
    #...
);
# 视图
CREATE [OR REPLACE] VIEW viewname
AS
SELECT 
#...
;
# 索引
CREATE INDEX indexname
ON tablename (column [ASC|DESC], ...);
# 过程
CREATE PROCEDURE procedurename( [parameters] )
BEGIN
#...
END;
# 用户
CREATE USER username[@hostname]
[IDENTIFIED BY [PASSWORD] 'password'];

SELECT：
```
SELECT columnname [AS name], ...
FROM tablename, ...
[WHERE
GROUP BY 
HAVING 
ORDER BY columnname [ASC|DESC]
LIMIT start OFFSET offset];
```
- 变种：
  - SELECT DISTINCT 应用于所有选择的列
  - SELECT Concat(a, 'b', ...) 拼接字段，可以结合 RTrim() 等函数
- ORDER BY
  - 可以使用非检索列
- WHERE
  - 操作符 = <> != < <= > >= BETWEEN AND IS NULL IN (val1, val2, ...)
  - 组合子句：AND OR，最好使用圆括号
  - 取反：NOT 可用于 IN、BETWEEN、EXISTS 子句
  - LIKE 匹配整个列（完全匹配）
    - 通配符：% 任意字符任意次数，但不匹配 NULL；_ 单个字符。
  - REGEXP 在列值内进行匹配（部分匹配）
    - . 任意单字符
    - a|b 其中之一
    - [abC] 其中一个字符，[^abC] 否定，[a-z] 范围，[:alnum:] 字符类
    - \\ 前导匹配特殊字符和元字符，如 \\n，自身为 \\\
      
      为什么是两个斜杠
      
      MySQL 自己解释一个，正则表达式库解释另一个。
    - 重复元字符：* 0 或多个，+ 1 或多个，? 0 或 1，{n} 指定数目，{n,m} 指定范围。用例：[[:digit:]]{4}
    - 定位符：^ 开始，$ 结尾，[[:<:]] [[:<:]] 词开始结尾
  NULL 的比较
  
  MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！也就是说：NULL = NULL 是错的。
  
  应当使用 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

ALTER TABLE tablename 仅列出部分常用：

ALTER TABLE tbl_name
  [alter_option [, alter_option] ...]
  [partition_options];
alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
}
table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | UNION [=] (tbl_name[,tbl_name]...)
}

视图¶

视图是虚拟的表，使用时动态检索数据，本质上是一个包装的查询，可以用与表基本相同的方式利用。

视图的常用场景：

存储一个基础的查询，多次使用
结合计算字段，对数据进行格式化
过滤不想要的数据

使用方法：

可以嵌套
不能索引
可以和表一起使用，比如和表联结
可以 ORDER BY

具体操作：

CREATE VIEW view_name AS;
SHOW CREATE VIEW view_name;
CREATE OR REPLACE VIEW view_name AS;

更新视图：视图的更新是对基础表的更新。只要出现了分组、联结、子查询、并等操作，就不能更新视图。大部分视图都是不能更新的。

函数¶

Concat()
# 文本
Left, Right; Length; Locate, SubString; Lower, Upper; LTrim, Trim, RTrim; Soundex
# 日期
Date, Time, Year, Month, Day, Hour, Minute, Second; Now, CurDate, CurTime; AddDate, AddTime; DateDiff; Date_Add, Date_Format; DayOfWeek
# 数值
Abs, Cos, Exp, Mod, Pi, Rand, Sin, Sqrt, Tan

日期

如果要的是日期，请使用Date() 比如，存储的order_date 值为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败。

字符

建议使用 CHAR_LENGTH 而不是 LENGTH，因为 LENGTH 对特殊字符可能返回意想不到的结果。比如对于 ¥，前者返回 1，后者返回 2。

触发器¶

触发器是一些语句，在事件发生时自动执行。MySQL 触发器能够相应 DELETE、INSERT、UPDATE 事件。

CREATE TRIGGER trigger_name
BEFORE|AFTER DELETE|INSERT|UPDATE
ON table_name
FOR EACH ROW
BEGIN
    -- ...
END;

对于每个表每个事件只允许一个触发器，因此每个表最多有 6 个触发器。
如果 BEFORE 触发器失败，操作不执行；如果操作失败，AFTER 触发器不执行。

联结¶

很多子查询都可以使用联结完成。

自联结很简单，用别名理解即可。

内部联结 `INNER JOIN`¶

内部联结得到笛卡尔积。传递给 ON 的条件和直接使用 WHERE 建立联结相同：

SELECT * FROM a, b WHERE a.a = b.a;
SELECT * FROM a INNER JOIN b ON a.a=b.a;

用于联结的列会重复出现。手动指定需要的列就是所谓的“自然联结”。

外部联结 `LEFT|RIGHT OUTER JOIN`¶

需要包含没有关联行的那些行时，使用外部联结。LEFT|RIGHT 指定从哪一侧的表格选择所有行。

SELECT * FROM a LEFT OUTER JOIN b ON a.a=b.a;

交叉联结 `CROSS JOIN`¶

约束¶

外键¶

`ON` 子句¶

可以在外键被 UPDATE 或 DELETE 时指定操作：

RESTRICT 默认，拒绝操作
CASCADE 级联操作，删除或更新主表的行时，从表的行也会被删除或更新
SET NULL 从表的外键列被设置为 NULL

`INSERT`¶

NEW 关键字引用新插入的行
AUTO_INCREMENT 在 INSERT 执行后才生成值

建议：

BEFORE 用于数据验证和净化
AFTER 用于日志记录和数据审计

`DELETE`¶

OLD 关键字引用被删除的行，全只读

`UPDATE`¶

OLD 关键字引用被更新的行，全只读
NEW 关键字引用新插入的行

事务¶

使用¶

事务处理（transaction processing）用于维护数据库完整性，确保一批操作要么全部执行，要么全部不执行。

三个术语：Rollback、Commit、Savepoint

无法回退 CREATE 或 DROP

DROP DATABASE 等语句无法作为事务的一部分

执行一条 ROLLBACK 或 COMMIT 意味着事务结束。

# Example1 回退
SELECT * FROM table;
START TRANSACTION;
DELETE FROM table;
SELECT * FROM table;
ROLLBACK;
SELECT * FROM table;

# Example2 多表一致性
START TRANSACTION;
DELETE FROM table1 WHERE id=1;
DELETE FROM table2 WHERE id=1;
COMMIT;

# Example3 保存点
START TRANSACTION;
DELETE FROM table1 WHERE id=1;
SAVEPOINT delete1;
DELETE FROM table2 WHERE id=1;
ROLLBACK TO delete1;
COMMIT;

锁¶

安全¶

用户¶

USE mysql;
SELECT user, host FROM user;
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
RENAME USER 'oldname' TO 'newname';
DROP USER 'username'@'hostname';
SHOW GRANTS FOR 'username'@'hostname';
GRANT SELECT, INSERT, UPDATE, DELETE ON databasename.tablename TO 'username'@'hostname';
REVOKE SELECT, INSERT, UPDATE, DELETE ON databasename.tablename FROM 'username'@'hostname';
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');

聚合与分组¶

AVG, COUNT, MAX, MIN, SUM

可为这些函数指定DISTINCT
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

🤔这一点是合理的，因为如果有某一列被选择，却不对它进行分组，那它的值要怎么显示？

组合查询¶

多个SELECT语句之间使用UNION合成一个结果返回
显然，每个查询的列、表达式等必须相同、兼容
会自动去除重复行，使用UNION ALL保留。WHERE无法实现这种。
UNION组合只能使用一条ORDER BY，出现在最后的SELECT之后。

全文本搜索¶

原理是为指定列中的词创建索引，搜索针对这些词进行。快速决定匹配和频率等。
只有 MyISAM 引擎支持，而 InnoDB 不支持

创建表时使用 FULLTEXT 子句

CREATE TABLE a
(
    FULLTEXT(a_column)
) ENGINE=MyISAM;

用 Match() 和 Against() 执行全文本搜索
```
WHERE Match(note_text) Against('rabbit');
```
- 这一表达式给出全文本搜索的 rank
- 查询扩展 WITH QUERY EXPANSION
- 布尔文本搜索 IN BOOLEAM MODE，全文本布尔操作符查表
- 此外有一些使用上的说明，比如出现频率过高的词自动忽略、短词自动忽略、中日文等无词分隔符的语言无法搜索等

数据修改¶

插入：

INSERT INTO table() VALUE (), (), ...;

除了 VALUE，SELECT 的结果也可以插入，且 SQL 不关心返回的列名，只关心位置，这样可以从不同表中导入数据

更新：

UPDATE table
SET col=newval, col=newval, ...
WHERE condition;

可以使用子查询
若发生错误则该更新的全部操作被取消，UPDATE IGNORE 忽略错误

删除：

DELETE table
WHERE condition;

TRUNCATE TABLE;

SQL 没有撤销按钮

表操作¶

CREATE TABLE

PRIMARY KEY () 关键字指定主键，可以是多个列
AUTO_INCREMENT 自动增量
- 在 INSERT 时仍然可以指定值，且后续增量将基于该值
- SELECT last_insert_id(); 获得最后一个 AUTO_INCREMENT 值
NOT NULL 不允许空值
DEFAULT value 默认值，只能是常量
ENGINE= 指定引擎
- 外键不能跨引擎
- 如果需要事务处理，使用 InnoDB
- 其余情况都可以用 MyISAM
- MEMORY 引擎：临时表，存放于内存

DROP TABLE

RENAME TABLE a TO b, c TO d, ...;

存储过程¶

CREATE PROCEDURE proc_name(
    OUT p1 INT,
    OUT p2 DECIMAL(5,2)
)
BEGIN
    -- ...
    SELECT *
    INTO p1
    FROM table;
END;

CALL proc_name(@p1, @p2);
SELECT @p1, @p2;
SHOW PROCEDURE STATUS LIKE 'proc_name';

存储过程的重点在于业务规则和智能处理。

CREATE PROCEDURE proc_name(
) COMMENT 'some comment'
BEGIN

    DECLARE var_name INT;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
    -- ...
    COMMIT;

    IF condition THEN
        -- ...
    ELSEIF condition THEN
        -- ...
    ELSE
        -- ...
    END IF;
END;

游标¶

游标是一个指向结果集的指针，可以用于遍历结果集。使用流程：

声明游标
打开游标（真正执行查询）
检索数据
关闭游标

DECLARE cur_name CURSOR FOR SELECT * FROM table;
OPEN cur_name;
FETCH cur_name INTO var1, var2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
REPEAT
    FETCH cur_name INTO var1, var2;
UNTIL done END REPEAT;

MySQL 编程¶

本节以 Java JDBC 为例。

基础¶

SQL 程序设计应当关注 API 的以下几点：

连接到服务器，选择数据库，断开连接

两步，分别指定 driver、初始化连接并获得 Connection 对象。

Class.forName();
DriverManager.getConnection();

检查错误

发生错误时，MySQL 提供三个值：错误码、描述性信息、五个字符的 SQLSTATE（ANSI ODBC 标准）。

Java 的异常处理是通过捕获异常实现的，最基本地会打印 printStackTrace() 指明错误的地方。

Exception 对象具有 getMessage() 方法。JDBC 方法常抛出 SQLException 对象，支持 getErrorCode() 和 getSQLState() 方法。某些方法产生 SQLWarning 对象，积累在一个列表中不会立即抛出，可以闲时打印。

try{

}
catch (Exception e)
{
    e.printStackTrace();
    catch (Exception e)
    {
      System.err.println ("Cannot connect to server");
      System.err.println (e);
      if (e instanceof SQLException)  
      {
        printException ((SQLException) e);
      }
    }
}
public static void printException (SQLException e)
{
    System.err.println ("SQLException: " + e.getMessage ());
    System.err.println ("SQLState: " + e.getSQLState ());
    System.err.println ("Vendor code: " + e.getErrorCode ());
}

执行 SQL 语句，获得结果

SQL 语句分为两类，JDBC 使用不同的调用执行它们。一类返回影响的行数 int，使用 executeUpdate()，一类返回结果集 ResultSet，使用 executeQuery()。使用后记得关闭语句和结果集。

结果集一开始指向第一行的前一行，使用 next() 方法直到返回假。getXXX() 接受列指标或列名。也可以直接获取泛型的 getObject()，后续再转换。

结果集元数据 ResultSetMetaData 类型通过 .getMetaData 获取，再使用 .getColumnCount() 获得列数。

Statement s = conn.createStatment();
int count = s.executeUpdate();
s.close();
Statement s = conn.createStatment();
s.executeQuery();
ResultSet rs = s.getResultSet();
ResultSetMetaData md = rs.getMetaData();
int ncols = md.getColumnCount();
int count = 0;
while(rs.next())
{
    int id = rs.getInt(1); // column1
    String name = rs.getString(2); // column2
    //...
    ++count;
}
rs.close();
s.close();

处理语句中的特殊字符和 NULL 值（安全问题）
辨别结果中的空值

设计¶

一些重要思想：

冗余造成信息不一致

E-R 模型¶

基本概念：实体集、关系集、属性
entity set, extension of the entity set, attributes possessed by each member of an entity set, simple attributes, composite and multivalued attributes, value
relationships of the same type, relationship instance, participation, entity's role, descriptive attributes, degree of the relationship set
Complex Attributes: domain (value set), simple, composite, single-valued, multivalued, derived
Mapping Cardinalities: one-to-one, one-to-many, ..., total, partial participation, minimum and maximum cardinality
Primary Key: primary key of the "many" side is a minimal superkey, replace the non-binary relationship set as an entity, weak entity set, identifying entity set, discriminator attributes, strong entity set, existence dependent, own, identifying relationship
Removing Redundant Attributes in Entity Sets:

关系型数据库¶

标准化方法：

一个给定的关系提要是不是好的形式
如果不是，无损分解为更小的关系提要

Operation	Description	Symbol
Select	satisfy a predicate	\(\sigma_{predicate}(relation)\)
Project	select a subset of attributes	\(\Pi_{attribute-list}(relation)\)
Cartesian-Product	combine two relations	\(relation_1 \times relation_2\)
Join	combine two relations	\(\sigma_{common\ predicate}(relation_1\times relation_2) = relation_1 \bowtie_{predicate} relation_2\)
Basic Set Operation	union, intersection, difference	\(relation_1 \cup relation_2, relation_1 \cap relation_2, relation_1 - relation_2\)
Assignment	assign a new name to a relation	\(new\_relation \leftarrow expression\)
Rename	rename the attributes of a relation	\(\rho_{new\_name(new\_attribute\_list)}(relation)\)