数据库¶

MySQL¶

program	description
`mysql`	command-line client
`mysqld`	server daemon
`mysqladmin`	server administration
`mysqldump`	backup and restore

知识结构

主要依据的书籍为：

MySQL Crash Course 入门
MySQL Cookbook 深入理解

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

语句
对象

需要掌握的语句：

SHOW, CREATE, SELECT, ...

需要掌握的对象：

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

运维¶

Info

安装与配置¶

安装后首先查看配置文件如 /etc/mysql，了解如何访问、日志位置。如果之后的过程中 mysqld 没有期望的输出，一般是放到日志去了。
初始化：可选择要不要生成随机密码，密码会在日志中。使用 Docker 部署时也会在日志中生成随机密码。

mysqld --initialize
mysql -u root -p

mysqld --initialize-insecure
mysql -u root --skip-password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

修改配置文件后检查：

mysqld --defaults-file=./my.cnf-test --validate-config

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 值（安全问题）
辨别结果中的空值