数据库

• 数据库按照数据结构来组织、存储和管理数据：层次模型HDB、网状模型、关系模型RDB。
• 主流的关系数据库：
  1. 商用数据库，例如：Oracle，SQL Server，DB2等；
  2. 开源数据库，例如：MySQL，PostgreSQL等；
  3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
  4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。
• NoSQL
  • 非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。
  • 面向文档数据库有 MongDB 和 CouchDB。
  • 列存储数据库有 Cassandra 和 HBase。
  • 键值存储数据库有 Redis、Memcached 和 MemcachedDB。

SQL

Structured Query Language (结构化查询语言)，用来访问和操作数据库系统。

• BIGINT 长整型 \ VARCHAR(N) 变长字符串 是日常用于最广泛的数据类型。
• DDL-定义数据（增删表，修改表结构）、DML-管理数据（增删改数据）、DQL-查询数据。

关系模型

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等）。NULL是空的，不是0也不是空字符串。

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

• 主键

  主键是关系表中记录的唯一标识，即id，如身份证号。

  主键的选取非常重要：不使用任何业务相关的字段作为主键。，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。可以使用多个列作为联合主键，但联合主键并不常用。

  GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。

  python uuid

  # UUID是128位的通用唯一识别码（universally unique identifier）。
  import uuid 
  # 基于MAC地址，时间戳，随机数来生成唯一的uuid，可以保证全球范围内的唯一性。
  guid1 = uuid.uuid1() # > 2879f1ea-c703-11ea-88ab-2cf05d0031b3
  guid4 = uuid.uuid4()
  

• 外键

  关系数据库通过外键可以实现一对多、多对多和一对一的关系。

  外键既可以通过数据库来约束（即在中间表的字段上设置外键），也可以不设置约束，仅依靠应用程序的逻辑来保证。

• 索引

  索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

  对于主键，关系数据库会自动对其创建主键索引。

  在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

  但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。

SQL语法

在线sql

MySQL 常用函数汇总：数值型函数、字符串型函数、日期时间函数、聚合函数。

Mysql官方8.0参考手册 ：强制转换函数、加密压缩、JSON函数等。

MYSQL数据库名表名列名在Linux区分大小写，建议以小写字母方式来存储；关键字不区分，建议大写。

查询数据

-- 语句格式
SELECT
[DISTINCT] -- 去重
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式> -- 条件
[GROUP BY <group by definition> -- 分组
[HAVING <expression> [{<operator> <expression>}…]] --过滤分组后的数据~WHERE
[ORDER BY <order by definition>] -- 排序
[LIMIT[<offset>,] <row count>] -- 分页
]
------
-- 查询students表的所有数据，结果是一个二维表
-- SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询
SELECT * FROM <表名>;
-- 判断当前到数据库的连接是否有效
SELECT 1;

-- 按条件查询students:
SELECT * FROM <表名> WHERE <条件表达式>
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
-- 去重
SELECT COUNT(DISTINCT name,age) FROM students;
-- 模糊查询
[NOT] LIKE  '字符串'
SELECT * FROM students WHERE name LIKE [BINARY] 't%' 
-- 通配符%代表任何长度的字符串(含0)，通配符_只能代表单个字符(不含0),BINARY区分大小写

​ 算术运算符：+ - * / % mod

​ 比较运算符 :

​ 位运算符：

-- 投影查询，仅返回指定列并进行重名
SELECT 列1 [AS] 别名1, 列2 别名2, 列3 别名3 FROM <表名> 别名4 WHERE <条件>
SELECT id, score points, name FROM students AS STU;

-- 排序，ASC升序(default)，DESC降序
-- WHERE子句在前，按score(降序), gender排序(前一字段值可排序时才对后面字段排	序)
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC,gender;

-- 分页查询
LIMIT <M> OFFSET <N> --M记录数，N初始位置
-- LIMIT总是设定为pageSize；OFFSET计算公式为pageSize * (pageIndex - 1)。
LIMIT 3 OFFSET 0;
LIMIT 3 OFFSET 3;
LIMIT 3 OFFSET 6;
LIMIT 6,3;

-- 聚合查询：返回该表的记录总数量，结果仍是一行一列，列名是COUNT(*)的二维表，可设置别名便于处理。
SELECT COUNT(*) num FROM students;
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
-- SUM()数值列的合计值，AVG()数值列的平均值，MAX()列最大值，MIN()列最小值
SELECT AVG(score) average FROM students WHERE gender = 'M';

-- 每页3条记录，如何通过聚合查询获得总页数
-- round() 遵循四舍五入，round(1.45,0) = 1；
-- ceiling()向上舍入为指定小数位数，ceiling(1.45,0) = 2；
-- floor()向下舍入为指定小数位数，floor(1.45,0)= 1;
SELECT CEILING(COUNT(*) / 3) FROM students;

-- 分组聚合，按class_id分组，得出每个class的男女生人数
-- GROUP_CONCAT() 函数：把每个分组的字段值都显示出来
-- WITH ROLLUP关键词：统计记录数量
SELECT class_id , gender, COUNT(*) num FROM students GROUP BY class_id;
SELECT age,sex,GROUP_CONCAT(name) FROM students GROUP BY age,sex;

-- 多表查询，返回多表的乘积(M*N行)，尽量不使用。
-- 要使用‘表名.列名’这样的方式来引用列和设置别名，避免了结果集的列名重复问题。同时表也可以设置别名。
-- set table alias:
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
WHERE s.gender = 'M' AND c.id = 1;

-- 连接查询(Join查询)
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s -- 主表
INNER JOIN classes c -- 内连接，返回同时存在于两张表的行数据
ON s.class_id = c.id; -- 连接条件
-- RIGHT OUTER JOIN返回右表都存在的行；LEFT OUTER JOIN则返回左表都存在的行；FULL OUTER JOIN。

-- 子查询
SELECT name FROM students 
WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');

-- 正则表达式
属性名 REGEXP '匹配方式'

修改数据

INSERT

-- 添加多条新记录
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
-- 方式二
INSERT INTO <表名>
SET <列名1> = <值1>,
    <列名2> = <值2>;
-- 使用 INSERT INTO…FROM 语句复制表数据
INSERT INTO <表2>
(字段1, 字段2,...)
SELECT 字段1, 字段2,...
FROM <表1>;

UPDATE

-- 更新id=1的记录
UPDATE <表名> SET 字段1=值1 [,字段2=值2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句];
UPDATE students SET name='大牛', score=66 WHERE id=1;
UPDATE students SET score=score+10 WHERE score<80; -- 更新字段可用表达式

DELETE

-- 删除id=1，3，5的记录
DELETE FROM <表名> WHERE ...;
DELETE FROM students WHERE  id IN (1,3,5);
-- 清空表数据,无法回滚
TRUNCATE [TABLE] 表名;
-- 当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。

MYSQL

MySQL适用场景：web网站系统、日志记录系统、数据仓库系统、嵌入式系统。

MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld，在后台运行。

可视化图形界面MySQL Workbench。

Mysql数据格式

• 整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
• 小数类型有单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点类型 DECIMAL。
• 日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。
• 字符串类型有 CHAR(M)、VARCHAR(M)、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET。
  • CHAR 是固定长度字符，VARCHAR 是可变长度字符。
  • 性别字段适合定义，为 ENUM；要存储一个人兴趣爱好，最好使用SET类型。
• 二进制字符串有 BIT(M)、BINARY(M)、VARBINARY(M)、TINYBLOB(M)、BLOB(M)、MEDIUMBLOB(M) 和 LONGBLOB(M)。
  • BLOB 主要存储图片、音频信息等

MYSQL(CRUD)

# 连接 MySQL Server
mysql -u root -p
mysql -h 10.0.1.99 -u root -p

HELP 查询内容;
/*多行
  注释*/
-- 列出所有数据库
show databases;
SHOW DATABASES [LIKE '数据库名']; --匹配数据库,部分匹配‘%db’、‘%db%’
-- 创建数据库
create database test;
-- 语法格式
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
show create database test;
-- 修改数据库，只能对数据库使用的字符集和校对规则进行修改
ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}
-- 删除数据库
DROP DATABASE [ IF EXISTS ] <数据库名>
-- 切换数据库
use test;

• information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
• mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
• performance_schema：主要用于收集数据库服务器性能参数。
• sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表。
• sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。
• world：world 数据库是 MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容。

-- 列出当前数据库所有表
show tables;

-- MYSQL视图
-- 查看表结构 DESCRIBE
desc students;
	-- 字段含义:
    -- Null：表示该列是否可以存储 NULL 值。
    -- Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
    -- Default：表示该列是否有默认值，如果有，值是多少。
    -- Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。
CREATE VIEW <视图名> AS <SELECT语句>
ALTER VIEW <视图名> AS <SELECT语句>

-- 参考创建表的sql语句
show create table students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` (                             |
|          |   `id` bigint(20) NOT NULL AUTO_INCREMENT,            |
|          |   `class_id` bigint(20) NOT NULL,                     |
|          |   `name` varchar(100) NOT NULL,                       |
|          |   `gender` varchar(1) NOT NULL,                       |
|          |   `score` int(11) NOT NULL,                           |
|          |   PRIMARY KEY (`id`)                                  |
|          | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
-- 删除表
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
drop table students;

-- 修改表
ALTER TABLE <表名> [修改选项]
-- 修改选项
{ ADD COLUMN <列名> <类型>[约束条件]; -- 在末尾添加字段
  ADD COLUMN <列名> <类型>[约束条件]FIRST; -- 在开头添加字段
  ADD COLUMN <列名> <类型>[约束条件]AFTER <已经存在的字段名>; -- 在已有字段后添加
| CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改字段名称
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型> -- 修改字段数据类型
| DROP COLUMN <列名>
| RENAME [TO] <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL; -- 新增一列birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL; -- 把列名改为birthday，类型改为VARCHAR(20)
ALTER TABLE students DROP COLUMN birthday; -- 删除列

MYSQL约束(CONSTRAINT)

• 主键约束(一个表中只有一个)

  -- 在创建表时设置主键约束
  CREATE TABLE <数据表名> 
  (
  [CONSTRAINT <约束名>] PRIMARY KEY [字段名] -- 单字段主键
  PRIMARY KEY [字段1，字段2，…,字段n] -- 设置联合主键
  字段名 数据类型 AUTO_INCREMENT -- 给字段(只能是整数类型)添加 AUTO_INCREMENT 属性来实现主键自增长
  AUTO_INCREMENT=1 -- 指定自增字段初始值
  );
    
  -- 在修改表时添加主键约束
  ALTER TABLE <数据表名> 
  ADD PRIMARY KEY(<字段名>);
  

• 外键约束

  -- 在创建表时设置外键约束
  CREATE TABLE <数据表名> 
  (
  [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] 
  REFERENCES <主表名> 主键列1 [，主键列2，…]
  );
    
  -- 在修改表时添加外键约束
  ALTER TABLE <数据表名> 
  ADD CONSTRAINT <外键名>
  FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
  

• 唯一约束(一个表中可有多个)

  <字段名> <数据类型> UNIQUE
    
  ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
  

• 检查约束(检查数据表中字段值有效性)

  -- 创建表时
  CHECK(<检查约束>)
  CHECK(salary>0 AND salary<100)
  -- 修改表
  ALTER TABLE <数据表名>
  ADD CONSTRAINT <检查约束名> 
  CHECK(<检查约束>);
  

• 默认值约束

  -- 创建表时
  <字段名> <数据类型> DEFAULT <默认值>;
  -- 修改表
  ALTER TABLE <数据表名>
  CHANGE COLUMN <字段名>	
  <字段名> <数据类型> DEFAULT <默认值>;
  

• 非空约束

  -- 创建表时
  <字段名> <数据类型> NOT NULL;
  -- 修改表
  ALTER TABLE <数据表名>
  CHANGE COLUMN <字段名>
  <字段名> <数据类型> NOT NULL;
  

MYSQL索引

在物理上分为 BTREE 索引和 HASH 索引

哈希（Hash）：把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出（散列值）。

-- 创建表时
CREATE TABLE <数据表名> 
KEY|INDEX [<索引名>] [<索引类型>] (<列名>,…) -- 普通索引
UNIQUE [INDEX|KEY] [<索引名>] [<索引类型>] (<列名>,…) -- 唯一索引
-- 修改表
ALTER TABLE <数据表名>
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
ADD UNIQUE [INDEX|KEY] [<索引名>] [<索引类型>] (<列名>,…)
-- 查看索引
SHOW INDEX FROM <表名> [ FROM <数据库名>]\G;
show index from students\G;
        Table: students -- 数据表名
   Non_unique: 0        -- 是否是唯一索引
     Key_name: PRIMARY  -- 索引的名称:主键索引
 Seq_in_index: 1		-- 该列在索引中的位置，如果索引是单列的，则该列的值为1
  Column_name: id		-- 定义索引的列字段
    Collation: A		-- 升序显示值“A”（升序），若显示为 NULL，则表示无分类
  Cardinality: 10		-- 索引中唯一值数目的估计值
     Sub_part: NULL		-- 列中被编入索引的字符的数量，列的值为 NULL表示整列被编入索引。
       Packed: NULL		-- 关键字如何被压缩
         Null:			-- 显示索引列中是否包含 NULL
   Index_type: BTREE	-- 索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
      Comment:			-- 评注
Index_comment:
      Visible: YES
   Expression: NULL
-- 删除索引
DROP INDEX <索引名> ON <表名>
DROP PRIMARY KEY

实用SQL语句

-- 查看系统所支持的存储引擎类型
-- 常用引擎：MyISAM、InnoDB 和 MEMORY 
show engines;
SHOW VARIABLES LIKE 'default_storage_engine%'; -- 查看默认引擎
SET default_storage_engine=InnoDB; -- 设置默认引擎
-- 修改数据表的存储引擎
ALTER TABLE <表名> ENGINE=<存储引擎名>;

-- 插入或替换
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
-- 插入或更新
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
-- 插入或忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
-- 快照
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

操作示例

set character set utf8;
-- 如果test数据库不存在，就创建test数据库：
CREATE DATABASE IF NOT EXISTS test;

-- 切换到test数据库
USE test;

-- 删除classes表和students表（如果存在）：
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- 创建classes表：
CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建students表：
CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入classes记录：
INSERT INTO classes(id, name) VALUES (1, '一班');
INSERT INTO classes(id, name) VALUES (2, '二班');

-- 插入students记录：
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, '小红', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, '小白', 'F', 81);

-- OK:
SELECT 'ok' as 'result:';

存储过程

（可编程函数）一组为了完成特定功能的 SQL 语句集合，经编译和优化后存储在数据库服务器中，可以被 Java 和 C# 等编程语言调用。

CREATE PROCEDURE <过程名> ([过程参数[,…]]) <过程体>
-- [过程参数[,…] ] 格式
[IN|OUT|INOUT] <参数名> <类型>
-- <过程体> 以关键字 BEGIN 开始，以关键字 END 结束
-- 使用命令行创建存储过程时，使用 DELIMITER 命令将结束命令修改为其他字符
DELIMITER $$
CREATE PROCEDURE ShowStuScore()
BEGIN
SELECT * FROM tb_students_score;
END $$
-- 查看存储过程的状态
SHOW PROCEDURE STATUS LIKE 存储过程名;
-- 查看存储过程的定义
SHOW CREATE PROCEDURE 存储过程名;
-- 删除
DROP PROCEDURE [ IF EXISTS ] <过程名>

触发器

在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器

-- 创建触发器
CREATE TRIGGER <触发器名> 
<BEFORE|AFTER> <INSERT| UPDATE| DELETE > ON <表名> 
FOR EACH Row
<触发器主体>;	
-- 创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。
CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum=@sum+NEW.salary;
-- 查看触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

事件

执行定时任务，事件调度器Event Scheduler

-- 查看even_scheduler是否开启
show processlist;
select @@event_scheduler;
show variables like 'event_scheduler';
-- 开启关闭调度器
set global event_scheduler=On;
-- 创建事件
CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE <schedule> -- 用于定义执行的时间和时间间隔,schedule 的值为一个 AT 子句
    [ON COMPLETION [NOT] PRESERVE] -- 用于定义事件是否循环执行，默认为一次执行，即 NOT PRESERVE
    [ENABLE | DISABLE | DISABLE ON SLAVE] -- 用于指定事件的一种属性
    [COMMENT 'comment'] -- 定义事件的注释
    DO <event_body>; -- 指定事件启动时所要执行的代码： SQL 语句、存储过程或者一个计划执行的事件
    
CREATE EVENT IF NOT EXISTS e_test 
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO tb_eventtest(user,createtime) VALUES ('MySQL',NOW());
-- 查看事件
show events;
SELECT * FROM information_schema.events limit 1\G;
-- 修改事件，同创建。
ALTER EVENT event_name DISABLE;-- 临时关闭事件

数据库事务

数据库事务用来保证多条SQL的全部执行，可以确保该事务范围内的所有操作都可以全部成功或者全部失败，如转账。

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

-- 从id=1的账户给id=2的账户转账100元
BEGIN; -- 开启一个事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 提交该显式事务
ROLLBACK; -- 回滚，主动事务失败

-- 查看当前事务自动提交模式，默认开启即每一条sql指令为一个事务
SHOW VARIABLES LIKE 'autocommit';
SET autocommit = 0|1|ON|OFF;-- 开启关闭

MySQL 事务隔离级别

用户管理

数据库备份恢复

# 数据库备份
mysqldump -u username -p <数据库> [<数据表> ...]> filename.sql
mysqldump -u root -p test > C:\student.sql
mysqldump -u username -p --databases <数据库1> <数据库2> ... > filename.sql #备份多个库
mysqldump -u username -p --all-databases > filename.sql #备份all

#student.sql 文件中的所有表和记录必须恢复到一个已经存在的数据库中。
#恢复数据时，CREATE TABLE 语句会在数据库中创建表，然后执行 INSERT 语句向表中插入记录。
#数据库恢复
mysql -u username -p [<数据库>] < filename.sql

-- 数据表导出
SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]
SELECT * FROM test.person INTO OUTFILE 'C://person.txt';
-- 数据表导入
CREATE TABLE tb_students_copy LIKE tb_students_info;

LOAD DATA INFILE 'C:/file.txt' INTO TABLE test_db.tb_students_copy
FIELDS TERMINATED BY ',' -- 设置字符串为字段之间的分隔符
OPTIONALLY ENCLOSED BY '"' --设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。
LINES TERMINATED BY '?'; -- 设置每行结尾的字符

日志

如果 MySQL 数据库系统意外停止服务，通过错误日志查看出现错误的原因。

通过二进制日志来查看用户分别执行了哪些操作、对数据库文件做了哪些修改，然后来修复数据库。

通用查询日志记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。

慢查询日志记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在。

在 MySQL 配置文件添加相应选项来开启日志
[mysqld]
log-bin=dir/filename # 二进制日志
log-error=dir/filename # 错误日志
log=dir/filename # 通用查询日志
log-slow-queries=dir\filename # 慢查询日志
long_query_time=n # 设置慢查询时间值(s)

-- 错误日志
-- 默认路径：C:\ProgramData\MySQL\MySQL Server\Data\
SHOW VARIABLES LIKE 'log_error';
-- mysqladmin -u root -p flush-logs # 开启新的日志

-- 二进制日志
SHOW VARIABLES LIKE 'log_bin';
SHOW binary logs; -- 查看二进制日志文件列表
SHOW master status; -- 查看当前正在写入的二进制日志文件
-- mysqlbinlog filename.number # 在当前文件夹下查找指定的二进制日志
RESET MASTER; -- 重置
PURGE MASTER LOGS TO 'filename.number'; -- 删除指定
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss'; -- 按时间
SET SQL_LOG_BIN=0/1; -- 暂停/开启
-- mysqlbinlog filename.number | mysql -u root -p # 还原数据库

-- 通用查询日志
SHOW VARIABLES LIKE '%general%';
SET GLOBAL general_log=off;

-- 慢查询日志
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0.001;

-- 日志输出方式：文件或数据表
SHOW VARIABLES LIKE '%log_out%';
SET GLOBAL log_output='FILE,TABLE';

PYTHON-MYSQL

DRIVER

MySQL Connector pip install mysql-connector-python

mysql-connector

PyMySQL import pymysql.cursors

pymysql