MySQL学习笔记
MySQL
启动命令
系统偏好设置启动mysql
PATH="$PATH":/usr/local/mysql/bin # 链接
mysql -u root -p # 登录
库的管理
创建
create database xxx;
mysqladmin -u root -p create xxx;
删除
drop database xxx;
mysqladmin -u root -p drop xxx;
选择
use xxx;
# xxx changed
数据类型
数值
数值类型表
日期和时间类型
日期和时间类型表
字符串类型
字符串类型图表
表的创建、删除、数据插入、查询
表的创建
CREATE TABLE table_name (column_name, column_type, ... , PRIMARY KEY (key_name))
ENGINE = InnoDB DEFAULT CHARSET = utf8;
注意： 字段属性如果要空值，尽量NOT NULL， 否则容易报错
数据库中创建表实例
命令行创建
表的删除
DROP TABLE table_name;
表中插入数据
INSERT INTO table_name(field 1, field 2,... field n)
VALUES
(value 1, value 2,... value n);
⚠️ 数据为字符型需要加入单引号或双引号
表中查询数据
SELECT * FROM table_name; # 读取表中所有数据
WHERE子句
语法
SELECT field1, field2, ... , fieldn FROM table1, table2, ... , tablen
[WHERE condition1 [AND [OR]] condition2
默认没有大小写区分, 使用BINARY WHERE可以实现大小写区分
SELECT * from table_name BINARY WHERE filed1 = value1;
=表示等于， <> or !=表示不等于
UPDATE 子句
UPDATE table_name SET field1=value1, field2=value2;
[WHERE Clause]
-
可以同时更新多个字段
-
可以在WHERE语句中指定多个条件
-
一个单独表中同时更新数据
更改指定id的语句
UPDATE table_name SET field1=value1 WHERE id=id_value;
DELETE 子句
DELETE FROM table_name [WHERE Clause];
- 没有制定WHERE子句，那么表中所有记录将被删除
LIKE 子句
SELECT field1, field2...fieldN
FROM table_name
WHERE field1 LIKE condition [AND [OR]] field2 = 'somevalue';
-
LIKE子句中可以用
%来代替任何字符 -
对于DELETE、UPDATE命令也可以使用LIKE子句
LIKE子句实例
UNION 操作符
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT experssion1, expression2, ... expression_n
FROM tables
[WHERE conditions];
-
expression 是要检索的列
-
tables 要检索的表
-
WHERE conditions 可选，要检索的条件
-
DISTINCT 删除重复数据，但是UNION已经默认删除重复
-
ALL 返回所有数据，包含重复的数据
UNION实例p1
UNION实例p2
带有WHERE子句实例
ORDER BY 子句
SELECT field1, field2,...fieldN FROM tbl1, tbl2,... tblN
ORDER BY field1 [ASC / DESC[默认 ASC]]
- 默认升序排序
- 也可以加上WHERE或者LIKE来设置条件
GROUP BY 子句
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数
SELECT col_name, function(col_name)
FROM tbl_name
WHERE col_name operator value
GROUP BY col_name;
GROUP BY 实例1
GROUP BY 实例2
连接的使用
-
INNER JOIN(内连接、等值连接)：获取两个表中匹配的记录
-
LEFT JOIN(左连接)：获取左表所有的记录，即使右表没有对应的记录
-
RIGHT JOIN(右连接)：与LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
连接实例数据
INNER JOIN实例
LEFT JOIN实例
RIGHT JOIN实例
NULL值的处理
- MySQL中不使用=和!=来对NULL进行比较运算
- MySQL提供了3个运算符来对NULL进行处理：
-
IS NULL: 列的值为NULL, 返回true
-
IS NOT NULL: 列的值不为NULL，返回true
-
<=>: 比较操作符，当比较的两个值都相等或者都为NULL，返回true
NULL实例
-
正则表达式
-
使用REGXGP操作符
正则表达式符号表
正则表达式实例
事务
MySQL事务用于处理操作量大、复杂性高的数据，以一个例子来说明就是在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
注意：
-
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
-
事务可以维护数据库的完整性，保证批量的SQL语句要么执行，要么不执行
-
事务用来管理insert, update, delete 语句（涉及数据库信息改动）
重点⚠️
一个事务一般来说要满足的4个条件（ACID）：原子性（Atomicity，或不可分割性)，
一致性(Consistency)、隔离性（Isolation，又称独立性)、持久性(Durability)。
1. 原子性
一个事务(transaction)中所有的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从未执行过一样（类似于撤销的感觉。
2. 一致性
在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发地完成预定的工作。
- 一致性状态：数据库中只包含成功事务提交的结果
- 不一致状态：数据库中包含失败事务的结果
3. 隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时
由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括：
+ 未提交(Read uncommitted)
+ 读提交(read committed)
+ 可重复读(repeatable read)
+ 串行化(Serializable)
4. 持久性
事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句
基本操作步骤
- BEGIN 开始一个事务
- UNDO 撤销事务
- ROLLBACK 回滚事务
- COMMIT 提交事务
# 操作1 begin; # 若干SQL语句 commit; # 提交事务 # 操作2 begin; # 若干SQL语句 rollback; # 回滚事务
使用保存点 SAVEPOINT
-
savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
-
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。
使用SAVEPOINT
SAVEPOINT savepoint_name; # 声明一个savepoint ROLLBACK TO savepoint_name; # 回滚到savepoint删除SAVEPOINT
SAVEPOINT在事务处理完成后(执行一条ROLLBACK或者COMMIT)，自动RELEASE（释放）。
RELEASE SAVEPOINT savepoint_name; # 删除指定savepoint
数据库恢复技术
将数据库恢复到最近一致状态
系统故障: 系统重新启动时
- 撤销所有未完成事务的修改
- 重写所有已完成事务的修改
恢复技术
冗余机制(需要补充)
- 建立冗余数据
-
数据转储(backup)
-
静态转储
-
动态转储
转储期间允许对数据库进行存取或修改- 不用等待正在运行的用户事务结束
- ?
-
转储方式
- 海量转储：每次转储全部数据库，方便但是操作规模大
- 增量转储：只转储上次转储后更新的数据
-
-
登录日志文件(logging)
记录事务对数据库的更新操作的文件，以记录为单位或以数据块为单位。
-
基于记录的日志文件
-
事务标识，操作类型（插入、修改、删除），操作对象（记录ID、Block NO.)，更新前数据旧值和新值。
2. 基于数据库的日志文件 -
与记录相似，无操作类型，存有更新前后数据块的旧值和新值
遵循原则:
- 登记的次序严格按并行事务执行的时间次序
- 必须写日志文件，后写数据库
-
写日志文件：把表示这个修改的日志记录写到日志文件
-
写数据库操作：把对数据的修改写到数据库中
- 利用冗余数据进行恢复（待补充）
反向扫描日志文件，并做所有操作的逆操作
-
-
-
检查点savepoint恢复技术
内容
-
建立检查点时刻所有正在执行的事务清单。
-
这些事务最近一个日志记录的地址
-
动态维护日志文件的方法
- 将当前日志缓冲区中耗费所有的日志记录写入磁盘的日志文件
- 在日志文件写入一个检查点记录
- 将当前数据缓冲区的所有数据记录写入磁盘的数据库中
- 把检查点记录在日志文件中的地址添加，并写入重新开始
恢复步骤
- 从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点记录。
- 由该检查记录得到检查点建立时刻所有正在执行的事务清单
ACTIVE-LIST.
建立两个事务队列:
UNDO-LIST: 需要执行UNDO操作的集合REDO-LIST: 需要执行REDO操作的事务
把ACTIVE-LIST暂时放入UNDO-LIST队列，REDO队列为空。
3. 从检查点开始正向扫描日志文件。- 如果有新开始的事务 \(T_i\)，把 \(T_i\) 暂时放入
UNDO-LIST队列; - 如果有提交的事务 \(T_j\)，把 \(T_j\) 从
UNDO-LIST*队列移到REDO-LIST；直到日志文件结束 - 对
UNDO-LIST中每个事务执行UNDO操作，REDO-LIST执行REDO操作
- 如果有新开始的事务 \(T_i\)，把 \(T_i\) 暂时放入
数据库镜像
通过复制数据实现，通常选择对关键数据和日志文件进行镜像，
在没有出现故障的时候，数据库镜像还可以用于并发操作
ALTER 命令
用于修改数据库表名或者修改数据库表字段时，就需要用到ALTER命令
删除、添加字段
DROP 删除，如果数据表中只有一个字段是无法用DROP删除的
ALTER TABLE tbl_name DROP key_name;
ADD 添加
ALTER TABLE tbl_name ADD key_name TYPE;
ALTER TABLE tbl_name ADD key_name TYPE FIRST; # 添加在第一列
ALTER TABLE tbl_name ADD key_name TYPE AFTER key2_name; # 添加在某字段之后
FIRST和AFTER可以用于ADD和MODIFY子句，要重置字段位置就先DROP再ADD即可
修改字段类型及名称
使用MODIFY或者CHANGE子句
MODIFY使用举例
ALTER TABLE tbl_name MODIFY key_name TYPE; # 将字段类型改为你想要的类型
CHANGE使用举例
ALTER TABLE tbl_name CHANGE key_name1 key_name2 TYPE # 将key1修改，名字为key_name2，类型为TYPE
ALTER TABLE 默认值
不设置默认值，字段默认为NULL，否则
ALTER TABLE tbl_name MODIFY key_name TYPE NOT NULL DEFAULT VALUE; # 字段默认为value不为null
修改默认值
使用ALTER修改字段默认值
ALTER TABLE tbl_name ALTER key_name SET DEFAULT VALUE; # 默认值修改为VALUE
ALTER和DROP删除字段默认值
ALTER TABLE tbl_name ALTER key_name DROP DEFAULT;
ALTER和TYPE修改数据表类型
ALTER TABLE tbl_name ENGINE = MYISAM; # 修改数据库引擎为MYISAM
可以使用SHOW TABLE STATUS语句(搭配LIKE)查看数据库状态
SHOW TABLE STATUS LIKE 'tbl_name'\G
修改表名
通过RENAME TO子句实现
ALTER TABLE tbl_name RENAME TO new_name;
ALTER命令同样可以用于创建和删除表的索引,详见下节
索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
索引分类
-
单列索引
即一个索引只包含单个列，一个表可以有多个单列索引 -
组合索引
即一个索引包含多个列虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行
INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
CREATE INDEX idx_name ON tbl_name (col_name)
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引)
ALTER TABLE tbl_name ADD INDEX ON idx_name(col_name)
创建表时，直接指定索引
CREATE TABLE test_tbl(
id_num INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [idx_name](username(length))
);
删除索引
DROP INDEX [idx_name] ON test_tbl;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建索引
CREATE UNIQUE INDEX idx_name ON test_tbl(username(length))
修改表结构
ALTER TABLE test_tbl ADD UNIQUE [idx_name] (username(length))
创建表时指定索引
CREATE TABLE test_tbl(
id_num INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE INDEX [idx_name] (username(length))
);
使用ALTER 命令添加和删除索引
四种方法添加索引
-
添加一个主键，这意味着索引值必须是唯一的，且不能为
NULLALTER TABLE tbl_name ADD PRIMARY KEY(col_list); -
创建索引的值必须是唯一的（除了
NULL外，NULL可能会出现多次）ALTER TABLE tbl_name ADD UNIQUE idx_name(col_list); -
添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX idx_name(col_list); -
指定了索引为 FULLTEXT ，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT idx_name(col_list);
删除索引
- ALTER 命令中使用 DROP 子句来删除索引
ALTER TABLE tbl_name DROP INDEX idx_name;
使用ALTER命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）
ALTER TABLE tbl_name MODIFY col_name NOT NULL; # 确保列值非空
ALTER TABLE tbl_name ADD PRIMARY KEY (col_name); # 添加主键
ALTER TABLE tbl_name DROP PRIMARY KEY; # 删除主键
删除主键无需索引名，但删除其他类型索引需要
显示索引信息
SHOW INDEX FROM tbl_name\G # \G用来格式化输出
MYSQL 临时表
数据库并发控制
串行: 每个时刻只有一个事务执行
并行执行:
- 在单处理机系统中，实际上是这些并行事务的并行操作轮流交叉运行
- 多处理机可以同时运行多个事务，实现真正的并行运行，这种方式称为同时并发方式
数据并发控制概述
事务是并发控制的基本单位
并发操作带来的数据不一致性包括丢失修改、不可重复读和读“脏”数据。
1. 丢失修改(lost update)
事务 \(T_2\) 和 \(T_1\) 读入同一数据被修改，\(T_2\) 的提交结果破坏了 \(T_1\) 的提交结果，导致 \(T_1\) 的提交结果丢失。
2. 不可重复读(non-repeatable read)
- 事务 \(T_1\) 读取某一数据后，事务 \(T_2\) 对其进行了修改，当事务 \(T_1\) 再次读该数据时，得到的值不一样。
- 幻影(phantom row): 事务1读取某一数据，事务2删除了某些记录或者插入了某些记录，事务1再读取记录会消失或者多出来
3. 读“脏”数据(dirty read)
事务1修改某数据，放回硬盘;
事务2读取同一数据后，事务1撤销，修改数据恢复原值;
此时事务2读取的数据与数据库中的不一致
并发控制机制: 用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰
并发控制的主要技术:
- 封锁(locking)、时间戳(timestamp)、乐观控制法(optimistic scheduler) 和多版本并发控制(multi-version concurrency control, MVCC) 等。
封锁
封锁是并发控制一项非常重要的技术。
- 封锁就是事务T在对某个数据对象操作之前，先向系统发出请求，对其加锁
- 加锁后事务T就对改数据对象有了一定的控制，在锁释放之前只有T才能对数据对象进行操作。
锁的类型
- 排他锁(exclusive lock, X锁,写锁)
- 事务对数据对象加上X锁，只允许该事务对数据对象进行读取和修改，并不能对A加上任何类型的锁，直到锁被释放。
- 共享锁(share lock, S锁，读锁)
- 事务对数据对象加上S锁，只能对该数据对象进行读取，并且其他事务可以读取数据对象但只能再加S锁，不能加X锁，直到先前事务对数据对象的S锁被释放。
封锁协议(Locking Protocol)
目标
- 何时申请X锁和S锁
- 封锁时间、何时释放
不同的封锁协议，在不同程度上为并发的调度与控制提供了帮助, 区别在于什么操作申请封锁，一级解锁时间
常用封锁协议: 三级封锁协议
1级封锁协议
- 事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放
- 正常结束(COMMIT)
- 非正常结束(ROLLBACK)
- 不会丢失修改, 但会造成脏读和不可重复读
2级封锁协议
- 1级封锁协议基础上，事务T在读取数据R前必须先加S锁，读完后即释放S锁
- 2即封锁协议可以防止丢失修改和脏读
- 在2即封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读
3级封锁协议
- 在1级封锁协议的基础上，事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放
- 有效避免对并发操作带来的3种不一致性。
活锁和死锁
和操作系统一样，封锁方法会带来活锁和死锁
活锁
事务 \(T_1\) 封锁了数据 \(R\)，事务 \(T2\) 又请求封锁 \(R\)，于是 \(T_2\) 等待，之后 \(T_i\) 请求封锁 \(R\)，\(T_1\) 解锁，系统首先批准了 \(T_i\) 的请求，之后 \(T_{i+1}\) 请求封锁 \(R\)，\(T_i\) 解锁后系统又批准了 \(T_{i+1}\) 的请求，最后会造成了 \(T_2\) 永久等待，形成活锁。
解决活锁的简单方法是采用先来先服务的策略
死锁
事务 \(T_1\) 封锁了数据 \(R\)，\(T_2\) 封锁了数据 \(R\)，然后 \(T_1\) 又请求封锁 \(R_2\)，因 \(T_2\) 已封锁了 \(R_2\)，于是 \(T_1\) 等待 \(T_2\) 释放 \(R_2\) 的锁；
接着 \(T_2\) 又申请封锁 \(R_1\)，因 \(T_1\) 已经封锁了 \(R_1\)，\(T_2\) 也只能等待 \(T_1\) 释放 \(R_1\) 上的锁，这样就出现了 \(T_1\) 在等待 \(T_2\)，而 \(T_2\) 又在等待 \(T_1\) 的局面， \(T_1\) 和 \(T_2\) 两个事务永远不能结束，形成死锁。
解决死锁的两类方法:
包括死锁的预防、死锁的诊断与解除。
- 死锁的预防
- 一次封锁法
事务一次将所有要是用的数据全部加锁，否则不执行。但降低了系统的并发度，很难精确封锁对象。 - 顺序封锁法
对数据对象规定一个封锁顺序，所有事物桉这个顺序进行封锁，但维护封锁顺序困难，成本高。
- 一次封锁法
- 死锁的诊断
诊断死锁方法与操作系统的方法类似，超时法或事务等待图法。- 超时法
事务等待时间超过规定时间，就认为形成死锁，但有可能误判或者不能即使发现死锁 - 等待图法
转换成一个有向图 \(G=(T,U)\)，\(T\) 是结点集合代表所有正运行的事务， \(U\) 是边的集合表示事务的等待情况， \(T1->T2\) 代表 \(T_1\) 等待 \(T_2\)。图中有环就形成了死锁。
- 超时法
- 死锁的解决
通常采用的方法- 是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务进行运行下去。并且要对事务所执行的修改操作进行恢复。
并发调度的可串行性
可串行化调度
多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同
可串行性是并发事务正确调度的准则
- 若一个调度是冲突可串行化，则一定是可串行化的调度
- 冲突可串行化调度是可串行化调度的充分条件，不是必要条件。
两段锁协议(TwoPhase Locking, 2PL)
将事务分成两个阶段：
- 扩展阶段，事务可以申请获得任何数据项上的任何类型的锁，但是不能释放任何锁；
- 收缩阶段，释放封锁，在这个阶段，事务可以释放任何数据项的任何类型的锁，但是不能再申请任何锁。
- 事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件。
- 不要求事务一次性对所有数据加锁，所以可能会发生死锁。
封锁的粒度
封锁粒度(granularity): 封锁对象的大小
封锁对象可以是逻辑单元，也可以是物理单元
粒度越大，能够封锁的数据单元少，并发度小，系统开销小。
选择封锁粒度，同时考虑封锁开销和并发度两个因素。
多粒度封锁
多粒度树
多粒度树的根结点时整个数据库，表示最大的数据粒度。叶结点表示最小的数据粒度。
多粒度封锁协议
允许多粒度树中的每个节点被独立地加锁。
对一个结点加锁意味着这个结点的所有后裔结点也被以同样类型的锁。
封锁类型
- 显示封锁，应事务的要求直接加到数据对象上的锁。
- 隐式封锁，该数据对象没有被独立加锁，是由于其上级结点加锁二使该数据对象加上了锁。
意向锁
如果对一个结点加意向锁，则说明该节点的下层结点正在被加锁;
对任一结点加锁事，必须先对他的上层结点加意向锁
三种意向锁
意向共享锁(Intent Share Lock, IS锁)
- 对数据对象加 IS 锁，表示它的后裔结点要加 S 锁
意向排他锁(Intent Exclusive Lock, IX锁)
- 对数据对象加 IX 锁，表示它的后裔结点要加 X 锁
共享意向排他锁(Share Intent Exclusive Lcok, SIX锁)
- 对数据对象加 SIX 锁，表示要对他加 S 锁，再加 IX 锁， SIX=S+IX
申请封锁应自上而下，释放封锁应自下而上。
浙公网安备 33010602011771号