跳转至

MySQL进阶

add_circle2025-03-03update2025-03-04

1. 架构体系

作为开发人员，在日常开发过程中我们需要经常和MySQL数据库打交道。如果公司有DBA（数据库管理员）还稍微好点，但如果没有DBA的话，我们就很有必要了解下MySQL的整个架构体系了。因为开发中我们如果不了解一条SQL是怎么查询的，那可想而知我们写出来的SQL语句肯定是有可能存在很多问题的，而面试过程中关于数据库的问答是必然出现的，因此于情于理，我们就有必要学习MySQL的架构体系了。MySQL是一个单进程多线程的数据库管理系统软件，分为四层架构体系采用解耦式的方式，使得MySQL的运作及其轻便高效。如下图就是MySQL的整体架构图：

img

从上面的架构图，我们可以看出MySQL的架构自上向下大致可以分为连接层、服务层、引擎层和文件层四大部分。对外暴露的是连接层，连接层调用SQL查询接口，交给服务层。

注意：MySQL也有三层逻辑架构的说法，分别是网络连接层（连接器+连接池），核心服务层，存储引擎层（引擎层与文件层合并层一层）。

1.1 连接层

连接层位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python、PHP、Go等，各语言都是通过各自的API接口与MySQL的连接层建立连接。连接层包括通信协议、线程处理、用户名密码认证等。

1.2 服务层

服务层是整个数据库服务器的核心，在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，解析器，查询优化器(自动优化SQL以匹配索引)， 缓存查询的处理以及部分内置函数执行(如日期,时间,数学运算,加密)等等。往下一层的各个存储引擎提供的功能都集中在这一层操作，如存储过程，触发器，视图等。

名称 说明
系统管理和控制工具（Management Services & Utilities） 提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，
保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。
SQL接口（SQL Interface） 用来接收客户端发送的SQL命令，并将SQL命令发送到其他部分，返回用户需要查询的结果。
例如 SELECT、FROM 就是调用 SQL 接口。
解析器（Parser） 也叫解析树，在SQL命令传递到解析器的时候会被解析器验证和解析成一棵“解析树”，
然后根据MySQL中的一些语法规则对“解析树”做进一步的语法验证与识别数据结构，确认SQL命令是否合法。
查询优化器（Optimizer） SQL 语句在真正的查询操作之前会使用查询优化器对SQL查询语句进行优化，
同时验证用户是否有权限进行查询，缓存中是否有可用的最新数据，查询优化器使用“选取-投影-连接”策略进行查询。
例如 SELECT id, name FROM student WHERE sex = "女";中，
SELECT查询先根据WHERE子句进行选取，而不是将表全部查询出来以后再进行 sex 过滤。
SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，
将这两个查询条件连接起来生成最终查询结果。
缓存（Caches） 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等。
在实际开发中，我们一般不适用缓存，所以在mysql8.0以后，实际上这个部分已经逐步被移除了。当然，即便在低版本下，我们也是关闭缓存的。

1.3 引擎层

引擎层主要提供了各种存储引擎（Storage Engine），存储引擎的主要作用是接收上层传下来的指令，负责数据的写入和读取，与底层的表文件进行交互，不同的存储引擎保存的数据格式不一样，使用场景也不一样。MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时接口屏蔽了不同存储引擎之间的差异。

因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作表的类型，除了mysql以外，很多其他数据库不叫存储引擎，而是叫表类型）。

通过以下SQL语句，可以查看MySQL中支持的存储引擎：

show engines;

MySQL中最常用的存储引擎就是InnoDB与MyISAM。

因为Memcached、Redis等内存缓存系统的出现，所以Momory存储引擎现今使用非常少了，MySQL5.5版本之后开始采用InnoDB为默认存储引擎，之前版本默认的存储引擎为MyISAM。

1.3.1 InnoDB

InnoDB存储引擎，是MySQL当前版本（5.5以后）的默认存储引擎，具有支持事务处理（transaction），外键约束（foreign key），行锁设计，崩溃恢复历史回滚 等特点，是mysql最重要和使用最广泛的存储引擎。innoDB保存表的数据，索引与表结构都在一个ibd文件中。

1.3.1.1 事务处理

事务可以让多条写操作（添加、删除、修改）的SQL语句以一个整体的方式在mysql内部执行，保证多条SQL语句要么一起执行成功，要么一起执行失败！

image-20220602120547032

1.3.1.2 外键约束

外键约束，也叫主外键关联，指代mysql中提供一个监控数据表与表之间进行级联绑定的索引。

image-20220602121549125

上面的表中，如果我们设置user_id作为id的外键，则可以设置让user表中的小明同学被删除以后，address中的对应数据会被mysql自动级联删除掉。当然，这种外键工作中，我们一般不适用mysql提供的，而是在编程语言中，进行外键的维护（虚拟外键，逻辑外观）。

1.3.1.3 行锁设计

所谓的锁，就是基于锁的机制，对数据表中的数据进行锁定，保证数据在写入(添加，更新，删除)的时候，不会因为并发导致出现一致性的问题。InnoDB中提供的锁有表级锁，行级锁。

表级锁，指在同一时间内，客户端连接修改一个表数据时，会把当前表进行锁定，其他客户端连接访问操作当前表时会阻塞等待。

行级锁，指在同一时间内，客户端连接修改一个表数据时，会把当前要修改的数据所在那一行数据进行锁定，其他客户端的其他客户端连接访问操作当前表的其他行数据畅通无阻，但修改同一行数据时会阻塞等待。

不管是表级锁还是行级锁，都是针对SQL语句中的写入命令生效，读取数据没有影响。

1.3.1.4 崩溃恢复

innoDB存储引擎在数据操作时，针对所执行的所有SQL语句，都会记录到一个redo日志文件中。当mysql因为意外而出现系统奔溃或宕机了，那么在mysql重启以后，mysql内部会自动通过redo日志对比丢失的数据，并进行恢复（历史回滚）。

1.3.2 MyISAM

MyISAM存储引擎，是MySQL早期版本（5.5以前）的默认存储引擎，拥有较高的插入、查询速度，表锁设计，支持全文索引的特点，但不支持事务处理和外键约束，也不支持崩溃恢复。MyISAM用3个文件来存储数据，frm文件存储表的定义、MYD文件存放数据、MYI文件存放索引。mysql8.0以后不再使用frm保存数据表的定义，而是叫sdi。

InnoDB与MyISAM的区别：

  1. 锁设计，InnoDB支持表级锁（(table-level locking)）与行级锁（row-level locking），而myISAM只支持表级锁。用户在操作myISAM类型表时，select，update，delete，insert等语句都会给表自动加锁，导致其他客户端连接的数据操作都会被阻塞，因此并发访问受限。当然，InnoDB虽然提供了行级锁，但也只是在使用了索引时是有效的，如果没使用索引也会锁全表，行锁大幅度提高了多用户并发操作的性能。同时InnoDB还支持MVCC（Multi-Version Concurrency Control，多版本并发控制）机制，只需要很小的开销，就可以实现非锁定读，从而大大提高数据库系统的并发性能（MVCC 可以看作是行级锁的一个升级版本）。

  2. 事务安全，InnoDB支持完整的事务安全机制（ACID），具有提交(commit)和回滚(rollback)事务的能力，所以在写入数据时可以有效保证数据的安全性以及一致性。

ACID：Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(持久性)。后面讲到事务再说。

  1. 外键约束，MyISAM不支持，而 InnoDB 支持。但是开发中一般不在数据库使用外键，而是在应用层实现逻辑外键或虚拟外键，使用外键会造成级联更新，且级联更新是强阻塞，存在数据库更新风暴的风险；外键使用时也会影响数据库的插入速度。

因为插入一张表的时候，mysql会自动去检查关联的外键所在表的全部数据是否一致。

  1. 主键约束，MyISAM允许没有主键的表存在。InnoDB表必须有主键，如果没有设定主键，就会自动生成一个用户不可见的6字节隐藏列作为主键列(对于用户而言，MyISAM与InnoDB都可以创建没有外键的表)。

  2. 崩溃恢复，MyISAM不支持，而InnoDB支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log（重做日志）、undo log（回滚日志）、bin log（二进制日志）。

1.4 文件层

文件层主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进程ID文件pid和socket文件等。

数据文件中主要包括了：db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、iblogfile0和iblogfile1文件等。

配置文件用于存在MySQL所有的配置信息，在Unix/Linux系统中配置文件格式为xxxx.cnf，而在Windows系统下则是xxxx.ini。注意：在ubuntu下具体的配置文件路径是/etc/mysql/mysql.conf.d/mysqld.cnf。

pid文件是存放MySQL运行时的进程号的文件，主要存在于Unix/Linux环境中，文件具体位置在mysqld.cnf或my.ini中通过配置指定。

socket文件和pid文件一样，都是MySQL在Unix/Linux环境中运行才会有的文件。在Unix/Linux环境中，客户端可以直接通过socket来连接MySQL。

1.4.1 日志文件

日志文件主要用于记录程序运行过程中的操作历史，便于对程序的运行过程进行监控与维护。MySQL中的日志主要包括：错误日志（error log）、通用查询日志（general query log）、二进制日志（binary log）、慢查询日志（slow query log）、中继日志（relay log）等。

说明：

为了节约性能，有些MySQl的日志功能默认是关闭的，数据库日志的开启有2种方式： 临时开启, 通过mysql交互终端临时设置，服务器关机或mysql重启，则日志的配置信息还原。 永久开启, 通过mysql的配置文件进行参数设置.windows下的配置文件：mysqi.ini，Linux/Unix系统：mysqld.cnf

1.4.1.1 错误日志（error log）

主要存储的是MySQL运行过程中产生的错误信息。固定开启的，可以使用下面的SQL语句来查看MySQL中的错误日志位置。

注意：如果MySQL运行过程中，SQL语句相关的错误，并不会在错误日志中出现。

-- mysql终端下执行
SHOW VARIABLES LIKE 'log_error';

image-20220606091243232

通过Linux系统提供的tail -f 可以监控日志，也可以通过ELBK日志分析系统进行监控。

tail -f /var/log/mysql/error.log
1.4.1.2 通用查询日志（General query log）

任何执行的sql语句都会写入这个日志中。默认是关闭的，可以通过下面的SQL语句来查看MySQL中的通用查询日期的开启状态与日志存储路径。

SHOW VARIABLES LIKE '%general%'; -- OFF表示关闭，相当于0

image-20220606091525148

在mysql交互终端下设置临时开启

set global general_log = 1;  -- 开启日志， -- 1相当于'ON'，表示开启
set global general_log_file = '日志文件的绝对路径';  -- 设置日志路径，一般默认即可，不要更改路径。

在mysql配置文件中添加如下配置可以设置永久开启【开发中本地数据库可以开启，但是在线上的生产服务器中不能开启】

-- bash终端下，sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
general_log = 1
general_log_file = /var/lib/mysql/ubuntu.log

-- :wq 保存退出

image-20220606092116202

重启mysql

service mysql restart

监控通用查询日志

sudo tail -f /var/lib/mysql/ubuntu.log
1.4.1.3 二进制日志（binary log）

是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（并不会记录查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间。binlog 的主要目的是复制和恢复。默认关闭，但是生产环境中务必开启bin-log的。启用binlog虽然会使数据库服务器性能降低，但是binlog日志能恢复误删数据，能复制数据的这些用处而言，这点性能的损耗不算什么。

-- 查看二进制日志的开启情况
show variables like "%log_bin%";

在mysql配置文件中添加如下配置可以设置永久开启。临时打开，实际上是没有什么意义的。所以此处不提。

# server-id 必须唯一
server-id=1
log-bin=mysql-bin
log-bin-index=mysql-bin.index 
# binglog日志的最大有效期，一般不设置
# binlog_expire_logs_seconds=2592000

终端基本操作

-- 查看所有二进制日志文件
-- show master logs;  
show binary logs;  -- 等价于上面一句

-- 查看mysql中最新的一个二进制日志的存储信息[常用语用于主从配置，集群配置的]
show master status;

-- 查看具体某个二进制日志中的记录内容
show binlog events in 'binlog.000003';  -- binlog.000003 仅仅是举例，具体要通过 show binary logs; 来查看具体二进制日志的文件名。

-- 删除指定日志文件【慎用！慎用！慎用！】
purge master logs to 'binlog.000003';  -- binlog.000003 仅仅是举例，具体要通过 show binary logs; 来查看具体二进制日志的文件名。
1.4.1.4 慢查询日志（slow query log）

记录数据库中运行时长超过指定时间（longquerytime，慢查询时间）的SQL语句，用于数据库优化。默认关闭的。可以通过下面的SQL语句来查看慢查询日志的开启情况。一般建议建议，识别监控整个数据库运作过程中的所有执行比较慢的SQL，往往用于测试 与优化。

show variables like '%slow_query%';

在mysql交互终端下设置临时开启

set global slow_query_log=1;
set long_query_time=2;

在mysql配置文件中添加如下配置可以设置永久开启。

# bash终端下，sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/ubuntu-slow.log
long_query_time = 2

可以使用慢日志分析工具(mysqldumpslow，默认在mysql安装以后内置了的)来获取慢查询日志，选项如下：
参数 说明 备注
-s 表示按照何种方式排序 c：访问计数
l：锁定时间（L的小写），被锁的时间
r：返回记录
t：查询时间，mysql执行SQL的时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t 即 top n，根据排序返回前多少条数据
-g 即 grep 关键字，匹配慢查询sql语句中是否包含关键字内容，
支持正则匹配模式，不缺分大小写

操作：

# mysql终端下可以通过sleep睡眠函数模拟慢查询
select sleep(10);

# 查看所有慢查询SQL语句的记录信息
sudo mysqldumpslow /var/lib/mysql/ubuntu-slow.log

# 得到返回记录数量最多的20个：
sudo mysqldumpslow -s r -t 20 /var/lib/mysql/ubuntu-slow.log

# 得到平均访问次数最多的20条：
sudo mysqldumpslow -s ar -t 20 /var/lib/mysql/ubuntu-slow.log

# 得到平均访问次数最多,并且里面含有 xxx字符的20条
sudo mysqldumpslow -s ar -t 20 -g "xxx" /var/lib/mysql/ubuntu-slow.log

如果出现错误如：Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.

在表示要分析的sql慢查询日志太大了，请拆分后再分析。

拆分的命令为：tail -100000 /var/lib/mysql/ubuntu-slow.log > /var/lib/mysql/ubuntu-slow.log.1

上面一条语句中，表示从/var/lib/mysql/ubuntu-slow.log读取最后10W条慢查询记录，复制到新的文件中 /var/lib/mysql/ubuntu-slow.log.1

2. 数据库设计

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型，并建立数据库中的表结构以及表与表之间的关联关系的过程。数据库设计能有效的对项目中的数据进行存储，并可以高效的对已存储的数据进行访问。好的数据库设计可以减少数据冗余，降低数据维护成本，节约存储空间，并提供高效的访问数据能力。

好的数据库的产生，离不开理论支持，还有实际开发经验。

在实际开发中，一些中大型的IT企业中，往往会存在DBA（数据库管理员），如果存在DBA则数据库设计这块就是DBA去负责的。

2.1 范式理论

范式理论（normalization，规范化理论）是在关系型数据库设计过程中，为了消除重复数据减少冗余数据，从而让数据库更好更科学地组织数据，让磁盘空间得到更有效利用的一种规范化设计理论。目前可以查找到的数据库范式有5条，分别是1NF，2NF，3NF，4NF，5NF。范式理论满足高等级的范式的先决条件是满足低等级范式，其中等级最低的是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库设计只需满足第三范式(3NF）就行了。范式理论仅仅是指导我们设计好的数据库的一种指导理论，并非语法标准，所以在实际开发中，有时候为了更合理的开发出更优秀的项目，也会存在逆范式（Denormalization）设计。

2.1.1 1NF：不可分割

第一范式（1NF，First Normal Form），是指数据表的每一列都是不可分割的基本数据项，有原子性特点。即数据表中的某个字段列不能有多个成员值。不符合第一范式则不能称为关系数据库。

不满足1NF的表设计：

商品信息表

商品信息
大马士革丝滑沐浴露,50,23.90,清怡
威王洁厕净,100,14.90,威王
西兰空气清新剂,100,12.90,西兰
立白洗衣液,120,36.90,立白
立白洗衣粉,100,29.90,立白

满足1NF的表设计：

商品名称 商品数量 商品单价 品牌
大马士丝滑革沐浴露 50 23.90 清怡
威王洁厕净 100 14.90 威王
西兰空气清新剂 100 12.90 西兰
立白洗衣液 120 36.90 立白
立白洗衣粉 100 29.90 立白

不满足1NF的表设计：

用户信息表：

姓名 年龄 性别
王小明,李白,刘晓宇 17 男
周艳,张蔷,江艺 17 女

满足1NF的表设计：

姓名 年龄 性别
王小明 17 男
李白 17 男
刘晓宇 17 男
周艳 17 女
张蔷 17 女
江艺 17 女

2.1.2 2NF：不可重复

第二范式（2NF，Second Normal Form）是在满足第一范式的基础上提出了进一步的规范要求：确保表中的每个非主键列完全依赖于主键列，不能出现多行重复且与主键不完全依赖的非主键列。所谓完全依赖是指不能存在仅依赖主键列一部分的非主键列（主要针对联合主键而言）。如果存在，那么这个非主键列和主键列的这一部分应该分离出来形成一个新的数据表，新数据表与原数据表之间是一对多的关系。

不满足2NF的表设计：

create table order (
    goods_id int auto_increment,
    order_number char(32),
    primary key (goods_id, order_number),
)

订单表

订单编号(联合主键) 商品ID(联合主键) 商品名称 购买数量 商品单价 用户ID 用户名
20220501001001 1 大马士革丝滑沐浴露 1 23.90 1 王小明
20220501001001 2 威王洁厕净 1 14.90 1 王小明
20220501001002 1 大马士革丝滑沐浴露 1 23.90 2 李白
20220501001002 2 威王洁厕净 1 14.90 2 李白
20220501001002 3 西兰空气清新剂 5 12.90 2 李白

满足2NF的表设计：

订单表

订单编号(主键) 用户ID 用户名
20220501001001 1 王小明
20220501001002 2 李白

订单项目表

记录ID（主键） 订单ID 商品ID 购买数量
1 1 1 1
2 1 2 1
3 2 1 1
4 2 2 1
5 2 3 5

2.1.3 3NF：不可冗余

第三范式（3NF，Third Normal Form）是在满足第二范式的基础上进一步提出了更好的要求：确保数据表中的每一列数据都和主键列直接相关，而不能间接相关，即消除传递依赖，去除冗余字段。所谓的传递依赖是指非主键列字段不依赖于主键列，而是完全依赖于另一个非主键列。而这些不依赖于主键的字段就是所谓的冗余字段，一般的处理方式就是另建一张新数据表与原数据表进行关联。由此，新数据表与原数据表之间的关系是一对多或多对多的关系。

不符合3NF的表设计：

订单表

订单ID（主键） 订单编号 用户ID 用户名
1 20220501001001 1 王小明
2 20220501001002 2 李白

符合3NF的表设计：

用户表

用户ID 用户名
1 王小明
2 李白

订单表

订单ID 订单编号 用户ID
1 20220501001001 1
2 20220501001002 2

不符合3NF的表设计

成绩表

成绩ID 课程ID 课程名 老师ID 分数 学生ID 学生名
1 1 Python 1 75.0 1 王小明
2 2 MySQL 2 68.5 1 李白
3 1 Python 1 80 2 刘晓宇
4 1 Python 1 86 3 刘晓宇

符合3NF的表设计

成绩表

成绩ID 课程ID 学生ID 分数
1 1 1 75.0
2 2 1 68.5
3 1 2 80
4 1 3 86

课程信息表

课程ID 课程名 老师ID
1 Python 1
2 MySQL 2

同理，学生信息表也是如此

学生ID 姓名
1 王小明
2 李白
3 刘晓宇

练习，基于范式理论，对超市构建一个超市自动化管理系统，超市下面有门店，门店有负责人，地址，门店名，楼层。超市有员工，员工属于不同的部门，一个部门有多个员工，有些员工属于门店的负责人，有些员工属于门店的普通职员。部门有部门的领导人，部门的地址，部门的人员。

门店
     id,  负责人(员工id)，地址，门店名，楼层
部门
     id, 领导人(员工id)，地址
员工
     姓名、部门id，门店的id

2.1.4 数据表间的关系

数据表之间因为不同的数据库设计理论，表与表之间的会存在多种常见的关联关系：一对一、一对多、多对多。

2.1.4.1 一对一（1:1）

A表中的1条记录对应着B表中的0~1条记录，而B表中的1条记录也对应着A表中的1条记录，则A表和B表为一对一关系。例如：文章信息表与文章详情表、商品信息表与商品详情表，用户信息表与用户详情表。

文章信息表(A表)

ID 标题 字数 发布时间 阅读量
1 红菊 3385 2021-12-03 13:30:22 30212
2 秦腔 1930 2022-03-10 22:01:30 502202
3 高考 1990 2022-04-13 18:54:32 4016

文章详情表(B表)

ID 文章id 描述 内容
1 1 秋绽红腮丝蕊黄，盈枝挂露自芬芳。 淡怀脱俗轻扬意，笑对寒风不觉凉....
2 2 中国的传统戏曲对于Teenagers往往是遭到排斥的，所以我时常被视为异类....
3 3 10月14日至16日，浙江省进行了高考改革后的第三次学考和选考....
2.1.4.2 一对多（1:n）

A表中的1条记录对应着B表中的0~多条记录，而B表中的1条记录只对应着A表中的1条记录，则A表和B表为一对多关系。例如：商品分类表与商品信息表、文章分类表与文章信息表、部门表与职工表、院系与课程、老师与课程等等。

商品分类表(A表)

ID 分类名
1 手机
2 电脑
3 洗衣机

商品信息表(B表)

ID 商品标题 分类ID
1 华为meta40 1
2 华为meta50 1
3 华为meta30 1
4 海尔Mate1洗衣机 3
2.1.4.3 多对多（n:m）

A表中的1条记录对应着B表中的0多条记录，而B表中的1条记录对应着A表中的0多条记录，则A表和B表为多对多关系。在多对多关系中，需要创建1张关系表（中间人）来绑定映射两张数据表的关系。例如：学生表与课程表、活动表与商品表、会议表与员工表等。

课程表

ID 课程名
1 Python
2 Java

学生表

ID 姓名
1 王小明
2 李白

成绩表(中间人)

成绩ID 课程ID 学生ID 分数
1 1 1 79.5
2 1 2 100
3 2 2 88

2.1.5 逆范式：以空间换时间

逆范式（Denormalization）指的是通过增加冗余或重复的数据来提高数据库的读性能，减少运行时带来的查询时间消耗。往往也可以总结为以空间换时间，所谓的空间就是硬盘空间，所谓的时间就是查询时间。因此以空间换时间，就是使用廉价的硬盘空间来换取更快的查询数据速度。

商品分类表

分类ID 分类名
1 手机
2 日化用品

商品信息表

商品ID 商品标题 所属分类
1 大马士革丝滑沐浴露 2
2 威王洁厕净 2
3 西兰空气清新剂 2
4 立白洗衣液 2
5 华为P50 1
6 华为meta40 1

逆范式化设计：

商品分类表

分类ID 分类名 商品总数
1 手机 2
2 日化用品 4

虽然增加了一个字段，但是减少了连表查询，这就是典型的逆范式设计（也就是以空间换时间）。

除了上面的例子以外，类似文章表（点赞总数、收藏总数、阅读总数、评论总数）、商品表（销量，评价）都会存在逆范式的应用。

2.2 设计步骤

数据库设计的步骤一般可以分成4个阶段或6个阶段，这里我们只讨论四个阶段的情况：

  • 需求分析阶段（数据是什么；数据具有哪些属性；数据与数据之间是否存在关联）
  • 逻辑分析阶段（使用E-R图对数据库进行逻辑建模(或抽象建模)，不需要考虑我们所选用的数据库管理系统）
  • 物理设计阶段（根据数据自身的特点把逻辑设计转换为物理设计, 结合对应的数据库管理系统的特点，进行数据库设计）
  • 维护设计阶段（1.对新的需求进行建表；2.索引优化；3. 架构升级；4.大表拆分）

2.2.1 需求分析阶段

这是数据库设计的第一个阶段，在这个阶段中我们首先需要熟悉实际业务流程，以此分析出软件系统中需要存储的数据（实体），分析这些数据的特点（实体具有哪些属性），最后了解数据的生命周期以及数据与数据之间的联系（Relationship），也就是实体（数据表）之间的关联关系。

实体（Entity）就是需要保存到数据库中，主观或客观存在的并且能相互区分的信息主体（信息集合），实际一般都是描述事物或概念的名词，在后续的物理设计阶段，实体就会转换成数据表保存到数据库中。例如用户、商品、订单、厂商等。数据库的表名就可以看做一个实体对象。一个数据库是由很多个实体对象构成的，然后它们之间往往存在一定的关联关系和属性，因此，常见实体间的关系也可以归纳为3种：1对1、1对多、多对多。

而属性（Attribute）则是描述实体的特征信息，在后续的物理设计阶段，属性就会转换成数据列保存到数据库中。举例子：用户实体拥有属性【id、姓名、年龄、性别、手机号码】、商品实体拥有属性【id、商品名称、商品类型、商品价格、商品图片、商品描述、厂商id】、订单实体拥有属性【id、订单编号、用户id、商品id、下单日期、订单价格】、厂商实体拥有属性【id、名称、地址、联系人、联系电话】。

在需求分析阶段中，我们需要完成的工作就是：

  1. 找出所有明面或者隐藏的实体。
  2. 分析出实体所包含的属性都有哪些。
  3. 实体对实体之间的关系（有的实体是没有关联的，有的实体是存在1对1，1对多，多对多的关联）。

举个栗子，例如我们要做一个商城。那么我们需要根据需求分析报告、策划原型或UI设计图纸等进行分析总结，提取要保存到数据库中的实体信息。

实体列表：

商城首页：导航菜单、商品分类、商品信息、用户信息、Banner广告、文章分类、搜索历史等。
商品列表：商品分类等。
购物车页面：订单、支付方式等。
个人中心页面：足迹、积分、红包、退货记录、收藏历史、用户信息、收货地址、修改密码记录等。

属性列表：

导航菜单：菜单名，菜单链接，是否显示，序号等
商品分类：分类名称等
商品信息：商品标题，商品价格，商品图片，商品描述，商品属性，是否显示，序号等
用户信息：账户名，登陆口令（哈希值），昵称，头像等
Banner广告：图片路径，广告链接，是否显示，序号等
文章分类：分类名称等
搜索历史：关键字，搜索时间等

产品分类：分类名称等

订单：订单编号，订单标题，订单总价格，订单实付价格，支付方式，支付状态，支付时间等
支付方式：平台名称，平台官网，支付账户，支付应用ID，支付应用秘钥，启用状态等

足迹：用户ID，访问地址，访问时间，信息类型等
积分：积分来源，积分数量，用途，产生时间，有效时间等
红包：红包来源，红包金额，使用状态等
退货记录：订单ID，商品ID，退货状态等
收藏历史：商品ID，用户ID，收藏时间等
收货地址：地址名称，所属省份，所属城市，所属地区，详细地址，收货时间，联系人，联系电话等
修改密码记录：用户ID，修改时间，旧登陆口令（哈希值）等

实体间的关系

不存在关联关系的实体：
    导航菜单
    Banner广告

存在关联关系实体：
    商品分类
        1:n    商品信息
    文章分类
        1:n    文章信息
    用户信息
        1:n    搜索历史
        1:n    收货地址
        1:n    修改密码记录
        1:n    积分
        n:m   红包<红包发放记录>
        n:m   商品<收藏历史, 足迹>
        1:n    订单
                    n:m    商品<订单项目>
                    n:1     支付方式
                    1:n     退货记录

2.2.2 逻辑分析阶段

逻辑分析阶段就是基于上一阶段的分析结果使用E0R图对实体、实体的属性与实体间的关系进行逻辑建模。

E-R图也称实体-联系图(Entity Relationship Diagram，也叫实体关系模型)于1976年由陈品山提出用于描述数据概念关系的概念模型，E-R图提供了表示实体、属性和关系的方法。常用的E-R画图工具有：亿图、visio、processon、draw.io、mastergo等。逻辑分析阶段有时候也会构建UML图（统一建模语言）。

E-R图主要有3要素构成，要素与要素之间的从属关系以及关联关系使用实线来绑定。

名称 描述
实体 使用矩形框表示实体。框内就是实体名。
属性 使用椭圆形框表示实体的属性。框内就是属性名
联系 使用菱形框表示实体与实体之间的1:1、1:n、n:m的关联关系。

preview

image-20220605225001358

作业：把上面的超市练习的题目，使用E-R图自己画一个出来。

image-20220607145602637

作业：把上面举例的商城例子中的所有实体、属性、联系通过E-R画出来。

image-20220607151542676

2.2.3 物理设计阶段

就是基于上一阶段的E-R概念模型，将概念模型结构转换成特定DBMS所支持的数据模型的过程。物理模型的构建工具：navicat、workbanch、Powerdesigner等。

image-20220606010836521

作业：

把上面的超市以及商城作业中的E-R图转换成物理模模型。

2.2.4 维护设计阶段

经过了上面几个阶段设计以后，数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中因为时间、业务、数据量不断增长，必须不断地对其进行监控、维护、调整、优化、升级。

3. 进阶操作

3.1 视图

视图（View）就是从一个或多个表中导出来的表，是一种只保存SQL语句的虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不看整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。 注意，视图中的数据是依赖于真实表中的数据。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。视图常用于一些权限要求比较多的系统项目中。

视图的优点：

  1. 定制用户数据，聚焦特定的数据
  2. 简化数据操作
  3. 提高数据的安全性
  4. 共享所需数据

基本操作格式：

-- 创建视图
create view 视图表名  as SQL语句;

-- 查看视图
desc 视图表名
show tables
show create table/view 视图表名

-- 视图的数据的读写操作与普通表一致。
select * from 视图表名

update 视图表名 set 字段1=字段1的值, 字段2=字段2的值... where 条件表达式;
-- 修改视图的数据，建议直接改视图对应的那个数据表中的数据，而不是直接改视图，因为视图中如果包含以下4种情况，是不能修改视图中的数据
-- 1. 视图中包含SUM()、COUNT()、MAX()和MIN()等函数；
-- 2. 视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字；
-- 3. 视图对应的表存在没有默认值的列，而且该列没有包含在视图里；
-- 4. 包含子查询的视图;

-- 修改视图中代表的SQL语句
ALTER VIEW 视图表名  AS SQL语句;

-- 删除视图
DROP VIEW 视图表名 [,视图表名]；

课堂例子：

drop table employee;
create table employee (
    id int auto_increment primary key,
    name char(20),
    department varchar(50),
    money decimal(8,2)
);


-- 提供给财务部门的人
select * from employee;

-- 创建1个视图
create view staff as select id,name,department from employee;

-- 提供给其他部门的人
select * from staff;

除了视图以外，在MySQL中还有一种临时表（TEMPORARY），也是一种特殊的数据表。但是这种数据库仅存在本次客户端连接中，客户端断开了临时表就没有了。

3.2 子查询

子查询是将一个查询语句嵌套在另一个查询语句中，那么被嵌入到内部的语句就是子语句，而外部包含的就是主语句。

内层的子查询语句的查询结果，可以为外层查询语句提供查询条件或数据源。

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还可以包含比较运算符：= 、 !=、> 、<等。

常见格式：

主语句 from (子语句) as 表别名;
主语句 where (子语句);

操作：

-- 查询大于平均年龄的学生信息
select name,age from student where age > (select avg(age) from student);

-- 查询指定学生（吴杰）在班的所有同学信息
select * from student where class = (select class from student where name = '吴杰') and name != '吴杰';

-- 子查询作为数据源
select id,name from (select * from student order by id desc limit 5) as t;

练习：查询黄老师授课的课程中，大于平均成绩的学生。

select student.name from student
left join achievement on student.id=achievement.sid
left join course on course.id=achievement.cid
left join lecturer on course.lecturer_id=lecturer.id
where lecturer.name='黄老师' and achievement.achievement > (
select avg(achievement) from achievement
left join course on course.id=achievement.cid
left join lecturer on course.lecturer_id=lecturer.id
where lecturer.name='黄老师');

3.2 查询结果格式定义

3.2.1 普通字段合并格式定义

select concat(id,',',name, ',',age) from student;

select concat_ws(',',id,name,age) from student;

3.2.2 分组字段合并格式定义

select class, group_concat(name) from student group by class;

-- concat_ws与group_concat组合使用
select any_value(name), group_concat(concat_ws('-',id,name) order by id) from student group by class;

3.3 自关联查询

自关联也叫自连接，是单表同时存在主键与外键情况下的单表自连接查询操作。例如：员工与领导，省份与城市与地区，一级菜单与二级菜单与三级菜单，权限，推荐人/分销，等数据，都是常见的需要运用自关联操作的场景。

行政区划表-area

id name parent_id
1 河南省
2 广东省
3 郑州市 1
4 开封市 1
5 广州市 2
6 深圳市 2
7 中原区 3
8 新郑市 3
9 荥阳市 3
10 龙亭区 4
11 鼓楼区 4
12 通许县 4

处理这种自关联设计的表结构，可以在自己的脑海中对当前表进行拆分。

province省份表

id name
1 河南省
2 广东省

city 城市表

id name parent_id
3 郑州市 1
4 开封市 1
5 广州市 2
6 深圳市 2

area 地区表

id name parent_id
7 中原区 3
8 新郑市 3
9 荥阳市 3
10 龙亭区 4
11 鼓楼区 4
12 通许县 4 
-- 河南省的城市
select id from area where parent_id = (select id from area where name = '河南省');

-- 河南省的所有城市的地区
select * from area where parent_id in (select id from area where parent_id = (select id from area where name = '河南省'));

-- 查询龙亭区属于哪一个省份
select name from area where id = (select parent_id from area where id = (select parent_id from area where name = '龙亭区'));

面试题：

自关联表的寻根问题。

3.4 联合查询

联合查询（union query），也叫合并查询，主要用于把多个查询结果合并成一个结果集返回，支持单表或多表操作，有两个关键字：

  • union，合并查询结果，并对重复结果进行去重。
  • union all，合并查询结果，但不会对重复结果进行去重。

union的基本使用：

-- 查询男生中平均成绩最好的3个学生 与 女生中成绩最好的5个学生。
(select any_value(name), avg(achievement) as score from student s
left join achievement a on s.id = a.sid
where s.sex = 1
group by a.sid
order by score desc limit 3)  --  因为limit在SQL语句中属于语句的结束关键字，所以在联合查询中需要加上（）
union
(select any_value(name), avg(achievement) as score from student s
left join achievement a on s.id = a.sid
where s.sex = 2
group by a.sid
order by score desc limit 5);

union all的基本使用：

-- 查询男生数量最少与女生数量最少的班级5个班级
(select class, count(id) as c from student where sex = 1 group by class order by c limit 5)
union all
(select class, count(id) as c from student where sex = 2 group by class order by c limit 5);

作业：

1. 一个商城中，对于商品的分类，是存在多级分类的情况，需要大家设计出合理的数据表，可以保存多级商品分类。
2. 在上面一题的基础上，创建一个手机的顶级分类，并且基于手机这个顶级分类，查询出手机下面的所有子分类的子分类下的分类信息。
id name pid
1 手机
2 笔记本
5 游戏手机 1
6 5G手机 1
7 拍照手机 1
8 合约机 5
9 性能机 5
10 美颜手机 6
11 音乐手机 6 
create table goods_category(
    id int auto_increment primary key, 
    name varchar(50),
    pid int
)

select * from goods_category where pid in (select id from goods_category where pid in (select id from goods_category where name = '手机'));

3.5 事务

事务（Transaction），是以功能或业务作为逻辑单位，把一条或多条SQL语句组成一个不可分割的操作序列来执行的数据库机制。事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等要求数据一致性或完整性比较高的场景。MySQL 中只有使用了 Innodb 存储引擎才支持事务操作。它可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行成功，要么全部执行失败，当事务中的某条SQL语句执行失败或产生错误，整个事务内部的所有SQL语句都将会回滚，所有受到影响的数据将返回到事务开始前的状态。

3.5.1 基本使用

3.5.1.1 准备数据
drop table if exists users;
create table users(
  id int auto_increment primary key,
  name varchar(50),
  money decimal(8,2)
);

insert into users values(1, '小明',1000);
insert into users values(2,'小红',1000);
insert into users values(3,'小白',1000);
3.5.1.2 事务提交
begin; -- 使用begin关键语句表示手动开启事务，以下所有的SQL语句，就会被MySQL视作一个不可分割的整体
update users set money=money-200 where name = '小明';
update users set money=money+200 where name = '小红';
commit;  -- 使用commit关键语句表示手动提交事务
3.5.1.3 事务回滚
begin; -- 使用begin关键语句表示手动开启事务，以下所有的SQL语句，就会被MySQL视作一个不可分割的整体
update users set money=money-200 where name = '小明';
update users set money=money+200 where name = '小红';
rollback; -- 使用rollback关键语句表示手动回滚事务，事务中所执行的所有的SQL语句都相当于没有执行一样。
-- 当然，什么时候选择提交事务，什么时候选择回滚事务呢？这个就需要结合我们将来所编写的python功能代码进行判断了。
3.5.1.4 多点回滚
begin;
update users set money=money-200 where name='小明';
select * from users;
SAVEPOINT s1; -- POINT就是事务的节点，savepoint可以给当前位置打个标记，将来如果不希望完全回滚事务，则可以选择回滚到某一个事务节点。p1是自定义的事务节点名称
update users set money=money+100 where name='小红';
select * from users;
SAVEPOINT p2;
update users set money=money+100 where name='小白';
select * from users;
SAVEPOINT p3;


ROLLBACK TO s1; -- 注意：这里并非回滚代码，而是恢复代码造成的影响，而且也不是退出事务，仅仅是回滚事务中指定保存点的影响效果。
select * from users;   -- 注意，这里仅仅是事务内部的回滚，并没有退出事务操作的，此时还在事务内部。
rollback;  -- 或执行commit;   此时才是真正的退出事务。

-- mysql 事务的多点回滚在开发中主要应用于嵌套事务。

注意：

mysql不支持嵌套事务，如果要实现嵌套事务，可以改支持的数据库，例如：postgresql。

3.5.2 事务控制

上面对于事务的处理都是手动操作的，MySQL中还支持使用AUTOCOMMIT来控制是否自动提交事务。mysql中默认是开启开启自动提交事务的(AUTOCOMMIT=1)，也就是在打开客户端连接时，会默认把每一条SQL语句当成一个独立的事务进行处理[为了方便实现事务使用过程中的redolog（重做日志）与undolog（回滚日志）]。

-- 查看MySQL中的AUTOCOMMIT值，MySQL中默认是开启自动提交事务的。这种情况下，MySQL会把每个sql语句当成一个事务，然后自动的commit提交事务。
SHOW VARIABLES LIKE '%AUTOCOMMIT%';

-- 关闭自动提交事务，MySQL会把当前会话连接的所有DML操作当成一个会话级别的事务进行管理，直到输入rollback或commit，当前事务才算结束。
-- 而结束该会话事务前，新的MySQL连接中是无法读取到任何该会话的操作结果的。
SET AUTOCOMMIT=0;
-- 开启自动提交事务
SET AUTOCOMMIT=1;

注意：是否开启自动提交事务，并不影响手动操作事务的过程。

3.5.3 四大特性-ACID

  1. 原子性（Atomicity）：事务内的所有DML语句，作为一个不可分割的整体，要么都成功，要么都失败，由redo log（重做日志）来实现
  2. 一致性（Consistency）：事务执行前后的数据完整性是一致的，由undo log（回滚日志）来实现。
  3. 隔离性（Isolation）：事务执行过程中对其他事务可以设置不同的隔离级别，隔离性由锁来实现不同的隔离级别
  4. 持久性（Durability）：事务一旦提交，其结果就是永久性更改的，由redo log（重做日志）来实现
3.5.3.1 原子性

原子性在数据库中的体现就是事务回滚，回滚能够撤销所有已经执行的sql语句，InnoDB实现回滚靠的是回滚日志undo log。在mysql中，每次执行DML语句都会先往 undo log 写入一条反向的SQL并且持久化，当系统崩溃时，扫描没有 commit 的事务对应的 undo log，按照undo log中不同类型SQL语句执行回滚操作。

  • insert 类型：undo log 记录了 id ，根据 id 写入反向的delete语句
  • delete 类型：undo log 记录了id对应的删除的数据，写入反向的insert语句
  • update 类型：undo log 记录了修改前的数据，写入反向的update语句
3.5.3.2 一致性

一致性是指事务在从一个一致性状态（事务开始前）切换到另一个一致性状态（事务结束后），不管事务是提交还是回滚，数据具有完整性约束的。例如上面的转账，在事务开始前，小红与小明两人的总金额是2000，在事务结束后不管事务提交或回滚，小明与小红的总金额还是2000的。

InnoDB中事务的一致性是由undo log（回滚日志）来实现。

3.5.3.3 持久性

对于一个已经提交的事务，在事务提交后即使系统崩溃或宕机了，这个事务对数据库中所做的更改也不能丢失。Mysql为了提高性能使用了 BufferPool （缓存池），事务会先修改或读取 BufferPool 中的数据，如果内存中不存在当前要操作数据，会从硬盘读取到内存的 BufferPool 中。Mysql 在 更新记录写入 BufferPool 之前会把记录先写到 redolog （重做日志），当事务提交时会先将 redolog 通过刷盘机制持久化到硬盘，如果出现宕机，mysql重启后将 redolog 中的事务重放执行。

InnoDB中基于事务实现，提供了2个事务日志，redo log(重做日志) 和 undo log（回滚日志），其中redo log 分2部分，其中逻辑日志（redo log buffer，重做日志缓冲）与 物理日志（保存在硬盘中，iblogfile0、iblogfile1），而undo log则属于逻辑日志，保存缓冲区的表空间。

SHOW VARIABLES like "%innodb%";
-- innodb_log_buffer_size ： 日志缓存区的内存大小是16M
-- innodb_log_files_in_group ：redo log日志组的数量，2则表示2个redolog交替使用
-- innodb_log_group_home_dir ：redo log日志组的存储目录，./表示在mysql的data目录下
-- innodb_flush_log_at_trx_commit：刷盘策略，默认1，值可以是0，1，2
--                                 0. MySQL的每次事务提交时不进行刷盘操作，由mysql自己的主县城每隔1秒进行刷盘
--                                 1. MySQL的每次事务提交时都会同步刷盘一次数据
--                                 2. MySQL的每次事务提交时只会把redolog日志缓存区数据写入文件系统缓存，而不会通过硬盘中，由操作系统决定什么时候同步到物理日志

image-20220609091328150

3.5.3.4 隔离性

隔离性是指并发过程中不同的客户端事务应该是隔离的，并发执行的各个事务之间不能互相干扰，在没有隔离性约束下，并发事务就可能出现脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

innoDB存储引擎 遵循了SQL:1992标准中的四种隔离级别：

  1. 读未提交（RAED UNCOMMITED，RU）：基于行级锁（但使用查询语句不会加锁），允许事务中的查询SQL语句读取其他事务中没提交的数据，这会导致出现脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）等问题的出现。这种最低的隔离级别，我们不会使用到它。

image-20220608110319666

  1. 读已提交（RAED COMMITED，RC）：基于行级锁，只允许事务中的查询SQL语句读取其他事务中已经提交的数据，可以避免脏读，但是不可重复读、幻读等问题还是会出现。db2或oracle等数据库的默认隔离级别，不可重复读、幻读可以通过代码程序来判断避免的。

image-20220608110804995

  1. 可重复读（REPEATABLE READ，RR）：基于行级锁，确保事务中多次读取同一范围的数据会返回第一次查询结果的快照，不会返回不同的数据行，但是可能出现幻读。MySQL的默认数据隔离级别就是RR级别，开发中经常把MySQL的隔离级别降低为RC级别。

image-20220608111904702

  1. 串行化（SERIALIZABLE）：基于表级锁，将全部的查询语句加上共享锁，让事务基于串行化的方式一个个执行，解决了幻读的问题，但是影响了并发性。分布式事务（XA）中默认就是串行化。

image-20220608113202888

查询数据库的事务隔离级别有两种，全局事务（global ）与会话事务（session）。

全局事务级别是针对整个数据库中所有的客户端连接。

会话事务级别是针对当前客户端连接，会话事务隔离级别设置比全局事务隔离级别权重大。

-- 查询全局事务隔离级别
show global variables like '%transaction_isolation%';   -- mysql8.0版本之前，配置项名为：tx_isolation
SELECT @@global.transaction_isolation;  -- 等价于上一句

-- 查询会话事务隔离级别
show session variables like '%transaction_isolation%';
SELECT @@session.transaction_isolation;  -- 等价于上一句
SELECT @@transaction_isolation;  -- 等价于上一句

临时设置数据库的隔离级别

-- 设置全局事务隔离级别【注意，修改了事务隔离级别，并不会影响到当前已打开的客户端连接会话的隔离级别】
SET GLOBAL TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
-- 设置会话事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

永久设置数据库的隔离级别

注意，修改了事务隔离级别，并不会影响到当前已打开的客户端连接会话的隔离级别

# 打开配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = READ-COMMITTED

# 保存配置文件，并重启MySQL，最好pycharm也重启下。
service mysql restart

面试过程中针对事务的常见问题：

1. 什么是事务？事务有哪些特性？事务的隔离级别有哪些？

2. 什么是脏读？什么是幻读？什么是不可重复读？都在哪些隔离级别下会出现？

3.6 函数

MySQL常用函数非常多， 常用的函数分类则包括数值型、字符串型、日期时间、聚合、加密、控制流程等函数。当然， MySQL中也支持自定义函数。最后要注意：要少用或不用函数，虽然mysql提供了非常多函数，但是使用了函数以后，肯定损耗性能的，MySQL提供给我们的使用的核心作用并非替代编程语言，而是存储数据。

3.6.1 数值型函数

函数 描述
ABS 求绝对值
SQRT 求二次方根
MOD 求余数
CEIL 返回不小于参数的最小整数，即向上取整
FLOOR 向下取整，返回值转化为一个BIGINT
RAND 生成一个0~1之间的随机数，传入整数参数时，用来产生可重复使用的随机数
ROUND 对所传参数进行四舍五入
POW 幂运算
PI 圆周率
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 返回f1进制数变成f2进制数
FORMAT(value,n) 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位 
select FORMAT(avg(achievement),2) as score from student
left join achievement on student.id = achievement.sid
where student.id = 10
group by achievement.sid;


select format(RAND()*100,0);

3.6.2 字符串型函数

函数 描述
LENGTH 返回字符串的字节长度，和字符集有关，utf8中一个多字节字符的字节长度为3，gbk则是2个
CHAR_LENGTH 返回字符串的字符长度
CONCAT(s1,s2,…) 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
CONCAT_WS(x, s1,s2,......,sn) 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT 替换字符串函数
LOWER 将字符串中的字母转换为小写
UPPER 将字符串中的字母转换为大写
LEFT 从左侧字截取符串，返回字符串左边的若干个字符
RIGHT 从右侧字截取符串，返回字符串右边的若干个字符
TRIM 删除字符串左右两侧的空格
REPLACE 字符串替换函数，返回替换后的新字符串
SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE(s) 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
FINDINSET(s1,s2) 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 
select length('hello');
select name, CHAR_LENGTH(description) from student;

drop table address
create table address(
    id int auto_increment primary key ,
    uid int,
    name char(20),
    province char(50),
    city char(50),
    area char(100),
    address varchar(500)
);

select name, concat(province, city, area, address) as addr from address;
select name, CONCAT_WS('-',province, city, area, address) as addr from address;


select INSERT('Quadratic', 3, 4, 'What');
select INSERT('Quadratic', 3, 0, 'What');

select upper('hello'), lower('HEELO');

select left('hello', 2), right('hello', 2);

select TRIM(' 12345 '), length(TRIM(' 12345 '));


select SUBSTRING('hello world', 2, 5);

select REVERSE('hello');

select FIND_IN_SET('a', 'abc');  -- enum, set

3.6.3 日期时间函数

函数 描述 格式
CURRENT_DATE 返回当前系统的日期值 2021-02-26
CURRENT_TIME 返回当前系统的时间值 15:00:42
NOW 返回当前系统的日期和时间值 2021-02-26 15:00:42
UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 1614322842
FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 2021-02-26 15:00:42
MONTH 获取指定日期中的月份 2
MONTHNAME 获取指定日期中的月份英文名称 February
DAYNAME 获取指定曰期对应的星期几的英文名称 Friday
DAYOFWEEK 获取指定日期对应的一周的索引位置值 6
WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53 8
DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366 57
DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31 26
YEAR 获取年份，返回值范围是 1970〜2069 2021
TIMETOSEC 将时间参数转换为秒数 54042
SECTOTIME 将秒数转换为时间，与TIMETOSEC 互为反函数 15:00:42
DATE_ADD 向日期添加指定的时间间隔 DATE_ADD('2021-02-26', interval 1 day);
DATE_SUB 向日期减去指定的时间间隔 DATE_SUB('2021-02-26', interval 1 week );
ADDTIME 时间加法运算，在原始时间上添加指定的时间 ADDTIME('2021-02-26 15:00:42', '00:30:00');
SUBTIME 时间减法运算，在原始时间上减去指定的时间 SUBTIME('2021-02-26 15:00:42', '00:30:00');
DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 DATEDIFF('2021-02-26', '2022-01-26');
TIMEFDIFF 获取两个时间之间间隔，返回参数 1 减去参数 2 的值 select timediff(current_time(), '06:30:00');
DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值 DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
WEEKDAY 获取指定日期在一周内的对应的工作日 WEEKDAY('2021-02-26 15:00:42'); 
-- 获取当前日期
select CURRENT_DATE();

-- 获取当前时间
select CURRENT_TIME();

-- 获取当前日期时间
select now();

-- 获取当前时间戳[秒]
select UNIX_TIMESTAMP();

-- 把指定时间戳转换成 日期时间格式
select from_unixtime(1624589517);

-- 获取日期中的月份
select MONTH(now());

-- 获取日期中的月份单词
select MONTHNAME(now());  # June

-- 获取一个星期中的周几（单词）
select DAYNAME(now());  # Tuesday


select DATE_ADD('2022-06-07', interval 1 day);
select DATE_SUB('2022-06-07', interval 1 day);

select DATEDIFF('2021-02-26', '2022-01-26');

select timediff(current_time(), '06:30:00');

3.6.4 聚合函数

函数 描述
MAX 查询指定列的最大值
MIN 查询指定列的最小值
COUNT 统计查询结果的行数
SUM 求和，返回指定列的总和
AVG 求平均值，返回指定列数据的平均值

3.6.5 加密函数

针对项目中的用户密码往往我们都是直接在编程语言中进行加密处理了，不会使用mysql的加密函数，但是针对mysql系统内部实际上也要对数据库管理员进行密码加密，此时我们就会使用加密函数。

函数 描述
AES_ENCRYPT(str,key) 用密钥key对字符串str利用AES加密算法加密后的结果，
是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key) 用密钥key对字符串str利用AES加密算法解密后的结果
DECODE(str,key) 使用key作为密钥解密加密字符串str
ENCODE(str,key) 使用key作为密钥加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt加密字符串str
MD5() 计算字符串str的MD5校验和
SHA1() 计算字符串str的安全散列算法(SHA)校验和
PASSWORD(str) 对字符串str进行加密，低版本MySQL默认使用password对数据库系统账号进行密码加密 
-- 使用AES高级加密算法基于参数2秘钥对参数1数据进行加密，得到二进制数据（BOLB）
select AES_ENCRYPT('123456', 'hwkwkwk2');

-- 使用AES高级加密基于参数2秘钥对参数1进行解密，得到原数据
select AES_DECRYPT(AES_ENCRYPT('123456', 'hwkwkwk2'), 'hwkwkwk2');

-- md5或sha1加密
select md5('hello'), sha1('hello'), sha('hello');

-- 使用password也可以加密
select password('hello');

3.6.6 系统信息函数

函数 描述
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENTUSER()、SYSTEMUSER()，SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” 
select version(), user(), database(), CONNECTION_ID();

3.6.7 流程控制函数

函数 描述
if(expr,v1,v2) 判断，流程控制
ifnull(v1,v2) 判断是否为空

case 搜索语句

-- 用法1
CASE  <表达式>
   WHEN <值1> THEN <操作1>
   WHEN <值2> THEN <操作2>
   ...
   ELSE <操作>
END CASE;

-- 用法2：
CASE
    WHEN <条件1> THEN <命令1>
    WHEN <条件2> THEN <命令2>
    ...
    ELSE <其他命令>
END CASE;

SELECT
CASE WEEKDAY(NOW())
   WHEN 0 THEN '星期一'
   WHEN 1 THEN '星期二'
   WHEN 2 THEN '星期三'
   WHEN 3 THEN '星期四'
   WHEN 4 THEN '星期五'
   WHEN 5 THEN '星期六'
   ELSE '星期天'
END as text;


SELECT
CASE
   WHEN WEEKDAY(NOW())=0 THEN '星期一'
   WHEN WEEKDAY(NOW())=1 THEN '星期二'
   WHEN WEEKDAY(NOW())=2 THEN '星期三'
   WHEN WEEKDAY(NOW())=3 THEN '星期四'
   WHEN WEEKDAY(NOW())=4 THEN '星期五'
   WHEN WEEKDAY(NOW())=5 THEN '星期六'
   ELSE '星期天'
END as text;

3.6.8 自定义函数

MySQL中支持自定义函数，但是这个功能默认是关闭的。可通过以下语句查看是否开启自定义函数功能。

show variables like '%func%';

临时开启自定义函数功能

SET GLOBAL log_bin_trust_function_creators = 1;
3.6.8.1 创建函数

基本写法

DELIMITER $$  -- DELIMITER $$  定义语句结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。
set global log_bin_trust_function_creators=1$$  -- 开启自定义函数的权限
DROP FUNCTION IF EXISTS 自定义函数名$$  -- 如果自定义函数名已经存在了，就删除掉。
CREATE FUNCTION 自定义函数名([参数列表]) RETURNS 返回结果的数据类型
BEGIN -- 函数体放在BEGIN 与 END之间
    SQL语句;
    RETURN 返回结果的值;
END $$  -- 函数结束
set global log_bin_trust_function_creators=0$$  -- 关闭自定义函数的权限
DELIMITER ; -- 自定义函数结束以后，恢复原来的语句结束符

例子：

DELIMITER $$
set global log_bin_trust_function_creators=1$$

DROP FUNCTION IF EXISTS get_student_total_by_class$$

create function get_student_total_by_class(class_num int) returns int
BEGIN
    declare total int default 0; -- 声明一个变量total，类型为int，默认值为0
    select count(id) from student where class=class_num into total;  -- 把查询结果赋值(into)给total变量
    return total;  -- 返回total作为函数结果
END $$


set global log_bin_trust_function_creators=0$$
DELIMITER ;

select get_student_total_by_class(305);
select get_student_total_by_class(301);

-- 查询某个自定义函数的定义
show create function  get_student_total_by_class; -- 无法查询内置函数的定义语句
3.6.8.2 删除函数
DROP FUNCTION [ IF EXISTS ] 自定义函数名;

3.7 存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程可以没有。

3.7.1 创建存储过程

CREATE PROCEDURE 存储过程名称 (in/out/inout [proc_parameter[,...]])
begin
    -- 存储过程的函数体->SQL语句
end ;

举个例子：

-- 案例1：
delimiter $$
drop procedure if exists procedure_name$$
create procedure procedure_name(in n int)
begin
    declare total int default 0;
    declare num int default 0;
    SET num = 1; -- 赋值语句，如果值的结果是一个SQL语句的结果，则需要使用into
    SELECT num;  -- 打印，因为MySQL中没有提供print函数，所以我们调试就需要使用select
    while num<=n do
        set total = total + num;
        set num = num + 1;
    end while;
    select total;
end $$

delimiter ;

call procedure_name(10);



delimiter $$
drop procedure if exists procedure_name$$
create procedure procedure_name(in age int , inout content varchar(100))
-- in 表示外界传递进来的参数，叫入参
-- out 表示由内部处理后返回给外界的数据，叫出参[相当于返回值]
-- inout 表示外界传递进来的参数，并经过处理后可以在外界调用结果的进出参
begin
  if age < 18 then
    set content='未成年人';
  elseif age < 40 then
    set content ='青年';
  else
    set content ='中老年';
  end if;

end$$

delimiter ;

call procedure_name(50, @content); -- 出参必须使用@开头声明变量
select @content;

3.7.2 调用存储过程

call 存储过程名称();

3.7.3 查看储存过程

因为存储过程是基于数据库保存的，所以查询存储过程必须指定数据库

-- 查询指定数据库中的所有的存储过程或函数
select routine_type, routine_name  from information_schema.routines where routine_schema='students';

-- 查询存储过程的状态信息和创建信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure  procedure_name;

3.7.4 删除存储过程

DROP PROCEDURE  [IF EXISTS] procedure_name;

存储过程中往往会与函数类似，一般处理复杂的业务功能，而且往往内部都会采用事务批量执行SQL语句。

3.8 触发器

触发器（trigger）就是在预设条件满足以后，自动执行的SQL语句的数据库特性，一般在编程开发中，也可以称之为钩子（Hook），中间件（Middleware）。

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中预定义的SQL语句集合。触发器的这种特性可以协助应用程序在数据库端确保数据的完整性 ，常用于日志记录， 数据校验等操作。

触发器类型 触发器中new 和 old的使用
INSERT 类型触发器 new 对象表示将要或者已经新增的数据
UPDATE 类型触发器 old 对象表示修改之前的数据 , new 对象表示将要或已经修改后的数据
DELETE 类型触发器 old 对象表示将要或者已经删除的数据

3.8.1 创建触发器

create trigger 触发器名称 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器，注意：mysql中只支持行级触发器，不支持语句触发器。
begin
    -- 一条或SQL语句;
end;

举个栗子，通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

首先创建一张职员表与日志表：

create table emp(
    id int auto_increment primary key,
    name varchar(50),
    age tinyint,
    salary decimal(8,2)
);

create table logs(
  id int(11) not null auto_increment primary key,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
);

创建 insert 类型触发器，完成插入数据时的日志记录 :

-- 创建添加数据的触发器
DELIMITER $$
create trigger logs_insert_trigger
after insert
on emp
for each row
begin
    insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id, concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end $$

DELIMITER ;

-- 测试
insert into emp values (null, '小明', 18, 20000);

创建 update 类型触发器，完成更新数据时的日志记录 :

-- 创建更新类型的触发器
DELIMITER $$
create trigger logs_update_trigger
after update
on emp
for each row
begin
  insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end $$

DELIMITER ;

-- 测试
update emp set salary=salary+500 where id=1;

创建delete 类型的触发器 , 完成删除数据时的日志记录 :

DELIMITER $$
create trigger logs_delete_trigger
after delete
on emp
for each row
begin
  insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end $$

DELIMITER ;

-- 测试
delete from emp where id = 1;

3.8.2 删除触发器

drop trigger [schema_name.]trigger_name;  -- 如果没有指定 schema_name，默认为当前数据库 。

3.8.3查看触发器

可以查看触发器的状态、语法等信息。

show triggers;

3.9 索引基础

索引（index，key），是帮助MySQL高效获取数据的数据结构。类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。索引往往存储在磁盘上的文件中，实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

因此总结来说，索引的使用就是一种以空间换时间策略，以硬盘空间保存索引与数据位置的映射关系，在查询数据时间，如果有字段被设置了索引，则MySQL查询优化器会根据索引查找到最优的数据查询计划，可以缩短数据查询所需要消耗的时间。但是数据表在DML操作时，因为数据库需要时刻维护索引与数据位置的关系，因此在DML操作时会耗费额外的资源来完成表的索引检索操作，建议在读多写少的时候创建合适数量的索引。读少写多（类似日志就是读少写多）

索引一般用于在作为排序（order by 关键字后面）、查询条件（where关键字后面）、 字段投影（select关键字后面）。

3.9.1 索引分类和管理

3.9.1.1 主键索引

一般就是一张表只有1个主键索引。索引值唯一，如果是数字类型的主键则一般设置自增（auto_increment）且不能为NULL。当然，主键字段的数据类型，也可以不设置为数值类型。

-- 第一种：在表创建时声明主键
CREATE TABLE user_info(
    id int PRIMARY KEY auto_increment,
    name varchar(10)
);

drop table if exists user_info;
CREATE TABLE user_info(
    id int auto_increment,
    name varchar(10),
    primary key(id)
);

-- 第二种：使用alter增加主键
ALTER TABLE table_name ADD PRIMARY KEY (columnName);
alter table user_info modify id int auto_increment primary key;  -- modify修改数据类型
3.9.1.2 唯一索引

索引列的值必须多行唯一，但允许有空值NULL，一张表中可以有多个唯一索引。

-- 第一种： 建表时设置索引
drop table if exists user_profile;
create table user_profile(
    id int auto_increment primary key ,
    username varchar(50) not null unique comment '登录账号',
    mobile varchar(15) null default null unique  comment '手机号',
    email varchar(150) null default null unique comment '邮箱',
    id_card varchar(18) null default null comment '身份证'
);


drop table if exists user_profile;
create table user_profile(
    id int auto_increment primary key ,
    username varchar(50) not null comment '登录账号',
    mobile varchar(15) null default null comment '手机号',
    email varchar(150) null default null comment '邮箱',
    id_card varchar(18) null default null comment '身份证',
    unique index username_unique(username),
    unique index mobile_unique(mobile),
    unique index email_unique(email)
);

-- 第二种：也可以后续新增或修改字段
ALTER TABLE user_profile ADD UNIQUE INDEX id_card_unique (id_card);
ALTER TABLE user_profile modify id_card varchar(18) null default null unique comment '登录账号';
3.9.1.3 普通索引

索引字段的值可重复出现多次，一张表中可以有多个普通索引。

-- 第一种：
drop table if exists article;
create table article(
    id int auto_increment primary key ,
    title varchar(50) comment '文章标题',
    pud_date datetime default now()
     index title_index(title),
     key pud_date_index(pud_date)
);

-- 第二种：也可以后续新增或修改字段
create index title_index on article (title);
ALTER TABLE article ADD INDEX pud_date_index (pud_date);
3.9.1.4 全文索引

主要是针对大文本字段的内容检索，如：文章内容。全文索引在mysql5.7版本以前，只针对MyISAM存储引擎有效，在MySQL5.7以后，InnoDB存储引擎才支持全文索引。虽然MySQl8.0版本中InnoDB支持全文索引，但是只针对英文内容生效，对中文不生效，所以如果我们将来要完成中文全文索引的话，我们一般会采用elastiscsearch、xunsearch、sphinx等。

-- 建表
CREATE TABLE article_info(
    id int auto_increment primary key,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
);

insert into article_info values (null, 'welcome to python', 'hello world');
insert into article_info values (null, 'welcome to beijing', 'hello, beijing');

-- 可以通过查询计划关键字explain 来查看是否命中了索引
-- 使用方法是match(字段名...) against(‘关键字') 进行全文差早
explain select * from article_info where match(title,body) against('welcome');  -- type=FULLTEXT 命中全文索引
explain select * from article_info where title like 'welcome';  -- type=ALL表示 全表扫描，最慢
explain select * from article_info where id =2; -- type=const, key=PRIMARY 表示命中主键索引，最快
-- MySQL中的全文索引只针对英语内容生效，要针对中文进行全文搜索，我们后面会学习到es可以实现

ALTER TABLE article_info ADD FULLTEXT title_fulltext (title, body);
3.9.1.5 单值索引

也叫单列索引，即一个索引只包含单个字段列，一个表可以有多个单列索引，单列索引有单列主键索引，单列唯一索引，单列普通索引，单列全文索引。

-- 第一种： 
create table goods_info(
    id int auto_increment primary key,        -- 单列主键索引
    goods_sn varchar(64) unique,                -- 单列唯一索引
    title varchar(255),
    description text,
    index title_index(title),                            -- 单列普通索引
    fulltext description_fulltext(description) -- 单列全文索引
)

-- 第二种：
CREATE unique| INDEX | fulltext index_name ON table_name(columnName);
ALTER TABLE table_name ADD INDEX index_name ON (columnName);
3.9.1.6 复合索引

也叫联合索引或多列索引，即一个索引包含多个字段列，一个表中可以有多个复合索引。但是使用时要使用复合索引的字段列就不要其他索引重复了。

-- 第一种：

create table order_items(
    order_id int,
    goods_id int,
    primary key(order_id, goods_id)  -- 联合主键
);

create table users2(
    id int auto_increment primary key,
    username varchar(20),
    email varchar(255),
    unique username_mail_unique(username, email)
);

CREATE TABLE article_info(
    id int auto_increment primary key,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
);

-- 第二种：
CREATE INDEX index_name ON table_name(columnName1，columnName2...);
ALTER TABLE table_name ADD INDEX index_name ON (columnName1，columnName2...);
3.9.1.7 查询索引
-- 第一种：
SHOW INDEX FROM 数据表名;
-- 第二种：
SHOW KEYS FROM 数据表名;
3.9.1.8 删除索引
-- 第一种： 
DROP INDEX 索引名称 ON 数据表名;

-- 第二种：
ALTER TABLE 数据表名 DROP INDEX 索引名称;

-- 删除主键索引，不需要主键索引名称，因为一个表中只允许1个主键
ALTER TBALE 数据表名 DROP PRIMARY KEY;

3.9.2 索引注意事项

3.9.2.1 适合建索引的场景
1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单列/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段
3.9.2.2 不适合建索引的场景
1. Where条件里用不到的字段不创建索引
2. 表记录太少（300w以上建）
3. 写多读少的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。（比如：国籍、性别）

3.10 外键约束

外键(FOREIGN KEY)也是索引的一种，是通过A表中的一个字段列指向B表中的主键，来对两张表进行关联的一种索引。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。当然使用外键会影响数据库性能，而且在对数据表进行DML写操作时，还会引起从表数据的完整性约束检查，所以一般开发中都是不采用数据库提供的物理外键约束，而是基于代码层面来实现逻辑外键，也叫虚拟外键。

1:1的关联关系中，两张表的主键进行主外键关联，或者把其中1张表的主键放到另外一张表中充当外键。

1:多的关联关系中，1的表中的主键放在多表中作为外键

多:多的关联关系中，两张多表中的主键，保存到第三张关系表中记录为外键。

商品分类表(主表)

ID(主键) 分类名
1 手机
2 电脑
3 洗衣机

商品信息表(子表)

ID 商品标题 分类ID(外键)
1 华为meta40 1
2 华为meta50 1
3 华为meta30 1
4 海尔Mate1洗衣机 3

3.10.1 创建外键

3.10.1.1 1:1关系的主外键约束
# 1:1 主外键关联
create table article(
    id int auto_increment primary key,
    title varchar(20)
);

create table article_info(
    id int auto_increment primary key,
    content text,
    CONSTRAINT article_info_foreign_key FOREIGN KEY(id) REFERENCES article(id)
);

create table goods(
    id int auto_increment primary key,
    title varchar(20)
);

create table goods_info(
    id int auto_increment primary key,
    goods_id int unique,
    content text,
    CONSTRAINT goods_info_foreign_key FOREIGN KEY(goods_id) REFERENCES goods(id)
);
3.10.1.2 1:多的主外键约束
create table goods_category(
    id int auto_increment primary key,
    name varchar(20)
);

create table goods(
    id int auto_increment primary key,
    cid int,
    title varchar(20),
    CONSTRAINT goods_foreign_key FOREIGN KEY(cid) REFERENCES goods_category(id)
);
3.10.1.3 多对多的主外键约束
create table user(
    id int auto_increment primary key,
    name varchar(20)
);

create table coupon(
    id int auto_increment primary key,
    title varchar(50),
    money decimal(8,2)
);

create table user_coupon(
    id int auto_increment primary key ,
    uid int,
    cid int,
    CONSTRAINT coupon_foreign_key FOREIGN KEY(cid) REFERENCES coupon(id),
    CONSTRAINT user_foreign_key FOREIGN KEY(uid) REFERENCES user(id)
);

除了上面在建表时创建外键约束以外，还可以在后续通过 alter table 来创建外键。

ALTER TABLE 当前表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键名)
REFERENCES 外键表名(外键表的主键名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

3.10.2 级联操作

所以的级联（CASCADE）操作，是通过MySQL的内部维护主外键的过程中，在操作主表时，是否对于外键表进行联动操作的一种机制。

MySQL中，提供的级联类型：

级联类型 描述
RESTRICT 限制保护，当删除主键对应的数据时，必须先把当前主键对应的外键数据全部删除。也就是必须先删外键才能删主键。
CASCADE 联动删除，当删除主键对应的数据时，外键所在的数据也会被删除掉
SET NULL 空值保留，当删除主键对应的数据时，外键被设置值为NULL
NO ACTION 无操作，当删除主键对应的数据时，外键所在的数据不进行任何操作[mysql 8.0以后，无效]
SET DEFAULT 默认取值，当删除主键对应的数据时，外键会被DEFAULT默认值取代[mysql 8.0以后废弃]

举个例子。

-- 限制保护
create table article(
    id int auto_increment primary key,
    title varchar(20)
);

create table article_info(
    id int auto_increment primary key,
    content text,
    CONSTRAINT article_info_foreign_key FOREIGN KEY(id) REFERENCES article(id)
     ON DELETE RESTRICT
);


-- 级联操作
drop table if exists goods_category, goods;
create table goods_category(
    id int auto_increment primary key,
    name varchar(20)
);

create table goods(
    id int auto_increment primary key,
    cid int,
    title varchar(20),
    CONSTRAINT goods_foreign_key FOREIGN KEY(cid) REFERENCES goods_category(id)
    on delete cascade
);

insert into goods_category values (null, '手机');
insert into goods values (null, 1, '华为meta40'), (null, 1, '华为meta50'), (null, 1, '华为P50');


-- set null 空值保留
drop table if exists goods_category, goods;
create table goods_category(
    id int auto_increment primary key,
    name varchar(20)
);

create table goods(
    id int auto_increment primary key,
    cid int,
    title varchar(20),
    CONSTRAINT goods_foreign_key FOREIGN KEY(cid) REFERENCES goods_category(id)
    on delete SET NULL
);

insert into goods_category values (null, '手机');
insert into goods values (null, 1, '华为meta40'), (null, 1, '华为meta50'), (null, 1, '华为P50');

4. Python操作MySQL

编程语言作为客户端，连接并操作mysql，通常都是2种方式：

  1. 数据库驱动模块：pymysql 、mysqldb。
  2. 数据库ORM模块：SQLAlchemy。

不管哪一种方式，mysql都只能识别SQL语句，所以上面的几个模块的作用就是充当客户端发送sql语句，通过socket通信间接并操作mysql。当然，其中第二种操作方式，是基于第一种方式而进行高度封装实现的。

4.1 pymysql

官方代码托管仓库：https://github.com/PyMySQL/PyMySQL

官方文档：https://pymysql.readthedocs.io/en/latest/

4.1.1 安装pymysql模块

pip install pymysql
# 因为客户端连接mysql时需要针对密码进行加密，而且socket通信也是加密连接通信，linux/uxin需要安装以下模块
pip install cryptography

4.1.2 快速入门

4.1.2.1 直接调用connect与cursor操作数据库
import pymysql.cursors


# 创建和数据库服务器的连接
# 返回值：pymysql.connections.Connection 的实例对象
# from pymysql.connections import Connection
conn = pymysql.connect(user="root", password="123", host="127.0.0.1", port=3306, database="students")

# 操作数据库，需要创建游标对象
cursor = conn.cursor()

sql = """SELECT * FROM `student` where name!='吴杰' and sex=1"""
# 数据库完成以后，手动关闭连接
result = cursor.execute(sql)
print(result)
for row in cursor.fetchall():
    print(row)
    # print(f'row["id"]={row["id"]}, row["name"]={row["id"]}')

# 关闭回收游标[相当于关闭文件操作的管道资源]
cursor.close()
# 关闭数据库连接[相当于关闭socket通信的连接资源]
conn.close()
4.1.2.2 基于上下文管理器操作数据库

Connection类与Cursor类都在内部实现了执行上下文管理器协议，所以可以使用with来操作，自动完成关闭操作

import pymysql.cursors


with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    print(conn)
    # 要操作数据，就需要创建游标对象
    # from pymysql.cursors import Cursor
    with conn.cursor() as cursor:
        print(cursor)
        # 中间可以使用游标完成对数据库的操作
        sql = """SELECT * FROM `student` where name!='吴杰' and sex=1"""  # 在python操作SQL语句，单个SQL语句不加分号也可以

        # 执行SQL语句
        # 如果查询语句，则返回值是查询结果的总数
        # 如果DML语句，则返回值是当前当前数据表受影响的行数
        result = cursor.execute(sql)
        print(result)
        for row in cursor.fetchall():
            print(row)
            # print(f'row["id"]={row["id"]}, row["name"]={row["id"]}')

4.1.3 数据操作

4.1.3.1 添加一条数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """添加一条数据"""
        sql = "insert into student (name, sex, class, age, description) VALUES ('王小明', 1, 301, 17, '这家伙很懒，一句话都没有留下.')"
        result = cursor.execute(sql)
        # cursor是默认把单个DML语句作为事务进行包装执行的，所以需要在执行SQL语句，手动提交事务
        conn.commit()
        # 查看受影响的行数
        print(result)
        # 查看新增数据的主键
        print(cursor.lastrowid)
4.1.3.2 预处理SQL语句

预处理SQL（Prepare），是一种特殊的 SQL 处理方式；预处理不会直接执行 SQL 语句，而是先将 SQL 语句进行语句编译，生成执行计划，然后通过 Execute 命令携带 SQL 参数执行 SQL 语句，在使用过程中一共提供了2种预处理机制：参数绑定与命名绑定。

预处理SQL语句，可以有效地防止SQL注入攻击。

MySQL终端下准备数据表和数据

create table users(
    id int auto_increment primary key ,
    username varchar(50),
    password varchar(64)
);

insert into users values (null, 'root', 'asdklasdfgmdm233232'),
                         (null, 'admin', 'dmt44m3mfdf90vxcvm,()*I');

模拟SQL注入攻击

在终端下输入一些特殊字符或者特殊内容，让SQL语句的判断，执行结果失效，这个行为就是SQL注入攻击

import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """模拟SQL注入攻击"""
        username = input('请输入登录账户:')
        password = input('请输入登录口令:')
        sql = f'select username, password from users where username="{username}" and password="{password}"'
        print(sql)
        result = cursor.execute(sql)
        if result:
            print("登录成功！")
        """
        正常使用肯定没有问题，
        root
        123456
        但是防止不了SQL注入
        在终端下输入一些特殊字符或者特殊内容，让SQL语句的判断，执行结果失效，这个行为就是SQL注入攻击
        root
        12345' or '1
        12345" or "1
        """
        # ' or '1 表示让where表达式，后面跟着一个永远为True的条件，让where条件判断失效

采用预处理SQL防止SQL注入

import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        username = input('请输入登录账户:')
        password = input('请输入登录口令:')
        # 预处理，可以识别并检测SQL语句参数中是否携带了攻击代码或者SQL注入语句
        sql = f"select username, password from users where username=%(username)s and password=%(password)s"
        print(sql)
        params = {"username": username, "password": password}
        result = cursor.execute(sql, params)
        if result:
            print("登录成功！")
        else:
            print("登录失败！")
        """
        在终端下输入一些特殊字符或者特殊内容，让SQL语句的判断，执行结果失效，这个行为就是SQL注入攻击
        root
        12345' or '1
        """
4.1.3.3 基于预处理添加一条数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """添加一条数据"""
        # 预处理SQL语句[参数绑定]
        # sql = "insert into student (name, sex, class, age, description) VALUES (%s, %s, %s, %s, %s)"
        # args = ("小明1号", 1, 301, 17, "这家伙很懒，一句话都没有留下.")
        # 预处理SQL语句[命名绑定]
        sql = "insert into student (name, sex, class, age, description) VALUES (%(name)s, %(sex)s, %(class)s, %(age)s, %(description)s)"
        args = {
            "name": "小明2号",
            "sex": 1,
            "class": 301,
            "age": 17,
            "description": "这家伙很懒，一句话都没有留下."
        }
        # 批量执行SQL语句
        result = cursor.execute(sql, args)
        # cursor是默认把单个DML语句作为事务进行包装执行的，所以需要在执行SQL语句，手动提交事务
        conn.commit()
        # 查看受影响的行数
        print(result)
        # 查看新增数据的主键
        print(cursor.lastrowid)
4.1.3.4 添加多条数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """添加多条数据"""
        sql = "insert into student (name, sex, class, age, description) VALUES (%s, %s, %s, %s, %s)"
        args = [
            ("王晓红1号", 1, 301, 17, "这家伙很懒，一句话都没有留下."),
            ("王晓红2号", 1, 301, 17, "这家伙很懒，一句话都没有留下."),
            ("王晓红3号", 1, 301, 17, "这家伙很懒，一句话都没有留下."),
            ("王晓红4号", 1, 301, 17, "这家伙很懒，一句话都没有留下."),
        ]
        # 批量执行SQL语句
        result = cursor.executemany(sql, args)
        # cursor是默认把单个DML语句作为事务进行包装执行的，所以需要在执行SQL语句，手动提交事务
        conn.commit()
        # 查看受影响的行数
        print(result)
        # 查看新增数据的主键
        print(cursor.lastrowid)
4.1.3.5 查询数据
4.1.3.5.1 查询一条数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """查询一条数据"""
        sql = "select id,name,age,sex from student where id=%(id)s"
        args = {"id": 1}

        cursor.execute(sql, args)
        row = cursor.fetchone()
        print(row)
4.1.3.5.2 查询多条数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """查询多条数据"""
        sql = "select id,name,age,sex from student where class=%(class)s"
        args = {"class": 301}
        cursor.execute(sql, args)

        # cursor的游标会记录每次读取的状态，所以如果前面有执行了fetch方法读取数据，则后续再次调用fetch会在上一次读取数据的基础往后读取

        # 可以使用fetchmany 从结果集_rows中读取指定数量的结果
        for row in cursor.fetchmany(3):
            print(row)
        print(">>>>>>> ")

        # 可以使用fetchall一次性从结果集_rows中读取全部结果
        for row in cursor.fetchall():
            print(row)
4.1.3.6 更新数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """更新数据，可以更新一条，也可以更新多条，主要看设置的where条件"""
        # 先读取要更新的数据【当然，现在是学习阶段，所以也可以不读取，直接更新】
        sql = "select id,name,age,sex from student where name=%(name)s"
        args = {"name": "小明2号"}
        cursor.execute(sql, args)
        row = cursor.fetchone()  # 举例修改1条
        sql = f"update student set name=%(name)s where id=%(id)s"
        args = {"name": "小明同学", "id": row["id"]}
        result = cursor.execute(sql, args)
        conn.commit()
        print(result)
4.1.3.7 删除数据
import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """删除数据"""
        sql = f"delete from  student where id=%(id)s"
        args = {"id": 113}
        result = cursor.execute(sql, args)
        conn.commit()
        print(result)

练习：

1. 在python中操作pymysql，获取 301班所有女生的信息[id,name,sex,age]
2. 在python中操作pymysql，获取黄老师的学生信息以及学生的课程成绩
3. 基于python操作pymysql实现一个商品信息管理系统[终端版本]，在以前基于文件存储数据的基础上，现在改成数据库存储商品信息。

4.1.4 事务处理

mysql本身是支持事务的，所以在Pymysql使用过程中，当游标cursor建立时就自动开始了一个隐形的事务管理了。

数据准备

drop table if exists users;

create table users(
  id int auto_increment primary key,
  name varchar(50),
  money decimal(8,2)
);

insert into users values(1, '小明',1000);
insert into users values(2, '小红',1000);
insert into users values(3, '小白',1000);

-- update users set money=money-200 where name = '小明';
-- update users set money=money+200 where name = '小红';

演示代码：

import pymysql.cursors

with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """
        事务处理
        只要小明有钱，并且小红的钱低于1600，就继续转钱
        """
        conn.begin()
        sql1 = "select money from users where name = '小明'"
        cursor.execute(sql1)
        res1 = cursor.fetchone()
        if res1["money"] > 0:
            sql2 = "update users set money=money-200 where name = '小明'"
            cursor.execute(sql2)

        sql3 = "select money from users where name = '小红'"
        cursor.execute(sql3)
        res2 = cursor.fetchone()
        if res1["money"] > 0 and res2["money"] < 1600:
            sql4 = "update users set money=money+200 where name = '小红'"
            cursor.execute(sql4)
            conn.commit()
            print("转账成功")
        else:
            conn.rollback()
            print("转账失败，事务回滚")

事务多点回滚

import pymysql.cursors
with pymysql.connect(
        user="root",
        password="123",
        host="127.0.0.1",
        port=3306,
        database="students",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor  # 设置pymysql针对查询结果的返回值为字典格式，默认是元组格式
    ) as conn:
    with conn.cursor() as cursor:
        """
        事务处理
        只要小明有钱，并且小红的钱低于1600，就继续转钱
        """
        conn.begin()
        # 把小明的money减去200
        sql = "update users set money=money-200 where name='小明'"
        cursor.execute(sql)

        sql = "select * from users"
        cursor.execute(sql)
        print(cursor.fetchall())

        sql = "SAVEPOINT s1"
        cursor.execute(sql)
       # -- 给小红分100
        sql = "update users set money=money+100 where name='小红'"
        cursor.execute(sql)

        sql = "select * from users"
        cursor.execute(sql)
        print(cursor.fetchall())

        sql = "SAVEPOINT s2"
        cursor.execute(sql)
       # -- 给小白分100
        sql = "update users set money=money+100 where name='小白'"
        cursor.execute(sql)

        sql = "select * from users"
        cursor.execute(sql)
        print(cursor.fetchall())

        sql = "SAVEPOINT s3"
        cursor.execute(sql)

        sql = "ROLLBACK TO s1"  # 表示从s1事务保存点，到当前一行的中间所有DML操作全部失效
        cursor.execute(sql)

        sql = "select * from users"
        cursor.execute(sql)
        print(cursor.fetchall())

        conn.commit()

4.1.5 基于面向对象封装工具类

在工作中，手写SQL语句基于execute执行，这种方式，性能是最好的！但是往往开发中，如果我们编写大量固定的SQL语句，也会导致程序的维护成本提升，同时将来如果应用程序要切换底层数据库的话，将会带来巨大的兼容问题。

import pymysql.cursors


class DB(object):
    """数据库工具类"""
    connected = False  # 数据库连接状态，False表示没有连接或连接失败
    __conn = None  # 数据库连接对象

    class DBError(Exception):
        """数据库异常基类"""
        pass

    class DBConfigError(DBError):
        """数据库配置异常类"""
        pass

    class ConnectionError(DBError):
        """数据库连接异常类"""
        pass

    class ExecuteError(DBError):
        """数据库执行异常类"""
        pass

    def __init__(self, conf):
        if type(conf) is not dict:
            raise self.DBConfigError("错误: 数据库连接参数必须是字典类型！")

        for key in ["host", "user", "password", "database"]:
            if key not in conf.keys():
                raise self.DBConfigError(f"错误: 数据库连接缺少'{key}'参数")

        if not conf.get("port", None):
            conf["port"] = 3306

        if 'charset' not in conf.keys():
            conf["charset"] = "utf8mb4"

        try:
            self.__conn = pymysql.connect(
                host=conf['host'],
                port=conf['port'],
                user=conf['user'],
                password=conf['password'],
                database=conf['database'],
                charset=conf['charset'],
                cursorclass=pymysql.cursors.DictCursor
            )
            self.connected = True
        except pymysql.Error as e:
            raise self.ConnectionError(f"数据库连接失败: {e}")

        self.sql = ""  # 记录最后一次执行的SQL语句

    def __enter__(self):
        return self

    def __exit__(self, *exc_info):
        del exc_info
        self.close()

    def __del__(self):
        self.close()

    def close(self):
        """
        关闭数据库连接
        :return:
        """
        try:
            self.__conn.close()
        except pymysql.Error as e:
            pass

    def insert(self, table, **kwargs):
        """
        添加一条数据
        :param table: 表名
        :param kwargs: 字典格式数据[字段名与字段值]
        :return: 新增数据的ID
        """
        fields = kwargs.keys()
        fields_params = [f'%({item})s' for item in fields]
        self.sql = f"INSERT INTO {table} ({', '.join(fields)}) VALUES ({', '.join(fields_params)})"
        with self.__conn.cursor() as cursor:
            try:
                cursor.execute(self.sql, kwargs)
                self.__conn.commit()
                return cursor.lastrowid
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def insert_many(self, table, data):
        """
        添加多条数据
        :param table: 表名
        :param data: 添加数据列表
        :return: 成功添加的数据数量
        """
        if type(data) not in [tuple, list]:
            raise self.ExecuteError("错误：添加多条数据，参数data格式必须是元组或字典!")
        if len(data) < 1:
            raise self.ExecuteError("错误，待添加必须至少1条！")

        fields = data[0].keys()
        fields_params = [f'%({item})s' for item in fields]
        self.sql = f"INSERT INTO {table} ({', '.join(fields)}) VALUES ({', '.join(fields_params)})"
        with self.__conn.cursor() as cursor:
            try:
                result = cursor.executemany(self.sql, data)
                self.__conn.commit()
                return result
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def get_one(self, sql):
        """
        查询一条数据
        :param sql: 原生DQL
        :return: 查询的第一条数据结果[字典格式]
        """
        with self.__conn.cursor() as cursor:
            try:
                self.sql = sql
                cursor.execute(self.sql)
                return cursor.fetchone()
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def get_all(self, sql):
        """
        查询多条数据
        :param sql: 原生DQL
        :return: 列表结构的所有查询结果
        """
        with self.__conn.cursor() as cursor:
            try:
                self.sql = sql
                cursor.execute(self.sql)
                return cursor.fetchall()
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def update(self, table, condition=None, **kwargs):
        """
        更新数据
        :param table: 表名
        :param condition: 更新条件，原生SQL语句
        :param kwargs: 字段参数
        :return: 受影响行数
        """
        fields = ', '.join([f"{item}=%({item})s" for item in kwargs.keys()])
        self.sql = f"UPDATE {table} SET {fields} WHERE {condition}"
        print(self.sql)
        with self.__conn.cursor() as cursor:
            try:
                result = cursor.execute(self.sql, kwargs)
                self.__conn.commit()
                return result
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def delete(self, table, condition=None):
        """
        删除数据
        :param table: 表名
        :param condition: 删除条件，原生SQL语句
        :return: 受影响行数
        """
        self.sql = f"DELETE FROM {table} WHERE {condition}"
        print(self.sql)
        with self.__conn.cursor() as cursor:
            try:
                result = cursor.execute(self.sql)
                self.__conn.commit()
                return result
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def count(self, table, condition=None):
        """
        统计数据表中符合条件的总数量
        :param table: 表名
        :param condition: 过滤条件，原生SQL语句
        :return:
        """
        where_sql = ""
        if condition:
            where_sql = f"WHERE {condition}"

        self.sql = f"SELECT count(id) as c FROM {table} {where_sql}"
        with self.__conn.cursor() as cursor:
            try:
                cursor.execute(self.sql)
                return cursor.fetchone()["c"]
            except pymysql.Error as e:
                raise self.ExecuteError(e)

    def truncate(self, table):
        """
        重置表
        :param table: 表名
        :return:
        """
        self.sql = f"TRUNCATE TABLE {table}"
        print(self.sql)
        with self.__conn.cursor() as cursor:
            try:
                result = cursor.execute(self.sql)
                self.__conn.commit()
                return result
            except pymysql.Error as e:
                raise self.ExecuteError(e)


if __name__ == '__main__':
    config = {
        "host": "127.0.0.1",
        "port": 3306,
        "user": "root",
        "password": "123",
        "database": "students",
    }

    with DB(config) as db:
        # """添加一条数据"""
        # data = {
        #     "name": "李小白",
        #     "sex": 1,
        #     "class": 306,
        #     "age": 18,
        #     "description": "不想说话~"
        # }
        # res = db.insert("student", **data)
        # print(res)


        # """添加多条数据"""
        # data = [
        #     {
        #         "name": "李大白",
        #         "sex": 1,
        #         "class": 302,
        #         "age": 18,
        #         "description": "1-不想说话~"
        #     },
        #     {
        #         "name": "李中白",
        #         "sex": 1,
        #         "class": 307,
        #         "age": 18,
        #         "description": "2-不想说话~"
        #     },
        # ]
        # res = db.insert_many("student", data)
        # print(res)

        # """查询一条数据"""
        # result = db.get_one("select id,name from student")
        # print(result)

        # """查询多条数据"""
        # result = db.get_all("select id,name from student")
        # print(result)

        """更新数据"""
        # res = db.update("student", "id>=115", name="不白", age=16, description="就是说一句话....")
        # print(res)

        """删除数据"""
        # res = db.delete("student", "id in (115,116)")
        # print(res)

        """查询数据总数"""
        # res = db.count("student")
        # print(res)
        # res = db.count("student", "class=301")
        # print(res)

        """重置表状态"""
        db.truncate("goods")

注意：我们一般不会在代码层面，对数据表、数据库进行创建或删除，否则存在相当大的安全隐患。

4.1.6 异步操作MySQL

针对异步操作MySQL，我们直接使用第三方封装号的开源模块：aiomysql。aiomysql实际上就是pymysql的异步封装，所以针对数据操作，在使用过程中，基本所有的方法名与参数都是pymysql是类似的

安装aiomysql

pip install aiomysql

快速使用，代码：

import asyncio, aiomysql

settings = {
    "host":"127.0.0.1",
    "port": 3306,
    "database": "students",
    "password": "123",
    "user": "root",
}


async def main():
    # 基于连接池连接数据库
    pool = await aiomysql.create_pool(
        host=settings["host"],
        port=settings["port"],
        user=settings["user"],
        password=settings["password"],
        db=settings["database"],
        charset="utf8mb4",
        cursorclass=aiomysql.cursors.DictCursor,
        minsize=5,   # 最小连接数，默认在初始化创建5个数据库连接对象在连接池中
        maxsize=20,  # 最多连接数，当数据库并发下，最多创建20个数据库连接对象在连接池中
        echo=True,   # 设置打印内部执行的SQL语句
    )

    async with pool.acquire() as conn:
        """基于互斥锁，从连接池中获取一个数据库连接对象"""
        async with conn.cursor() as cursor:
            """创建一个游标"""
            # 异步执行SQL语句
            await cursor.execute("SELECT * from student")
            # 异步获取结果
            # fetchmany(3)  # 获取指定数量
            # fetchone      # 获取一条
            # fetchall      # 获取所有
            data = await cursor.fetchmany(3)
            data = await cursor.fetchall()
            print(data)

    pool.close()
    await pool.wait_closed()

if __name__ == '__main__':
    asyncio.run(main())

如果觉得aiomysql对于数据库的操作比较底层，想要更加方便的操作数据库，则可以类似上面DB数据库工具类，对aiomysql也进行一次异步封装即可。

4.2 SQLAlchemy

SQLAlchemy 是 Python 著名开源的 ORM 工具包（也叫框架）。通过 ORM，开发者可以用面向对象的方式来操作数据库，不再需要编写 SQL 语句,支持绝大部分流行的关系型数据库。

官方文档：https://www.sqlalchemy.org/

4.2.1 ORM

4.2.1.1 基本概念

ORM是对象关系映射器（Object Relational Mapper）的简写，是一种对数据库底层的SQL语句进行封装，然后对外提供面向对象操作的一种数据库操作方式。

O是Object，也就类对象的意思。

R是Relational，译作联系或关系，也就是关系型数据库中数据表的意思。

M是Mapper，是映射的意思，表示类对象和数据表的映射关系。

ORM框架会帮我们把类对象和数据表进行了一对一的映射，让我们可以通过类对象提供的属性或方法就可以操作对应的数据表。

ORM框架还可以根据我们设计的类自动帮我们生成数据表，省去了我们自己建表的过程。这个操作一般叫数据迁移（migrate）

我们后面的学习过程中，要开发项目一般都选择在项目内嵌ORM框架，这样就可以不需要直接编写SQL语句进行数据库操作，而是通过定义模型类对象，操作模型类对象即可完成对数据库中数据的增删改查和数据表的创建删除等操作。

image-20220610101828521

4.2.1.2 优缺点

优点：

  • 数据模型类都在一个地方定义，容易更新和维护，也利于代码复用。
  • ORM 有现成的工具，很多功能都可以自动完成，比如数据消除、预处理、事务等等。
  • 使用了ORM以后迫使开发者在开发项目时采用MVC架构，ORM 就是天然的 Model（M模型），最终使代码组织更清晰，更加容易维护。
  • 基于ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
  • 新手对于复杂业务容易写出性能不好的原生SQL，但有了ORM不必编写复杂的SQL语句, 只需要通过操作模型对象即可同步修改数据表中的数据.
  • 开发中应用ORM将来如果要切换底层数据库，只需要切换ORM底层对接数据库的驱动类和配置信息即可，例如从mysql数据库切换成postgreSQL数据库，则把底层驱动(mysqldb或pymysql) 切换成 psycopg2，然后把连接配置修改即可。开发代码基本不需要调整与修改。

缺点：

  • ORM库不是轻量级模块工具，初学者需要花很多精力学习和使用ORM模块，甚至不同的ORM框架，会存在不同操作代码。

  • 对于复杂的数据业务查询，ORM表达起来比原生SQL语句要更加困难和复杂，例如子查询，事务，连表查询。

  • ORM操作数据库的性能要比使用原生的SQL语句差。

因为直接使用pymysql或mysqldb数据库驱动，只需发送SQL语句即可，而ORM不仅要发送，还要在发送前组装拼接SQL语句，里面使用了大量的正则以及字符串拼接，所以性能低下。

  • ORM 抽象掉了底层数据库，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL。

针对这种情况，可以使用pymysql或mysqlDB另外编写SQl语句操作即可，用了ORM并不表示当前项目不能使用别的数据库操作模块了。

模块安装，终端执行命令：

pip install -U SQLAlchemy
# 注意，使用ORM务必保证已经安装了ORM底层所需要的数据库驱动模块，如pymysql或mysqlDB

# 如果SQLAlchemy底层使用pymysql连接数据库，则安装如下
# pip install pymysql
# 如果SQLAlchemy底层使用MySQLdb连接数据库，则安装如下
# pip install mysqlclient

4.2.2 快速入门

4.2.2.1 初始化配置

db.py，代码：

# from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# from sqlalchemy import Column, Integer, String, Boolean, Numeric, Text  # 字段、整型
from sqlalchemy import *


# 1. 创建数据库驱动（引擎）
engine = create_engine(
    # 连接数据库的URL
    # url="驱动名称://账户:密码@IP地址:端口/数据库名?charset=utf8mb4",  # 如果底层驱动是pymysql
    # url="mysql+pymysql://root:123@127.0.0.1:3306/students?charset=utf8mb4",  # 如果底层驱动是pymysql
    url="mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4",  # 如果底层驱动是MySQLdb
    echo=True,  # 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
    pool_size=10,  # 连接池的数据库连接数量，默认为5个，设置为0时表示连接无限制
    max_overflow=30,    # 连接池的数据库连接最大数量，默认为10个
    pool_recycle=60*30  # 设置时间以限制数据库连接多久没使用则自动断开（指代max_overflow-pool_size），单位：秒
)

# 基于底层数据库驱动建立数据库连接会话，相当于cursor游标
DbSession = sessionmaker(bind=engine)
session = DbSession()
# 模型类对象的基类，内部提供了数据库的基本操作以及共同方法
Model = declarative_base()
4.2.2.2 数据迁移

基于上面初始化模块，创建模型，如果模型绑定的数据表不存在，则自动新建数据表。数据表存在，则不会新建。这个操作一般称之为数据迁移（Migrate）。

代码：

import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    # 1. 把当前模型与数据库中指定的表名进行关联
    __tablename__ = "tb_student"

    # 2. 绑定字段信息
    # 模型属性 = db.Column(数据类型, 字段约束)
    # primary_key=True 设置当前字段为整型，主键，SQLAlchemy会自动设置auto_increment为自增
    id = db.Column(db.Integer, primary_key=True)
    # db.String(20) 设置当前字段为字符串，varchar(20)
    name = db.Column(db.String(20))
    # db.Boolean 设置当前字段为布尔类型，本质上在数据库中是 0/1
    # default=True 设置当前字段的默认值
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    # 当前字段名如果是python关键字，则需要给第一个参数则字段的别名使用
    # SmallInteger = SMALLINT
    classes = db.Column("class", db.SMALLINT)
    # Text 表示设置当前字段为文本格式，因为文本与字符串varchar在python都是字符串，所以此处可以兼容
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    # DateTime 设置字段为日期时间类型
    # 注意：如果设置当前日期时间为默认值，不能在now加上小括号
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)


if __name__ == '__main__':
    # 如果没有提前声明模型中的数据表，则可以采用以下代码生成新的数据表，这个操作叫数据迁移
    # 如果数据库中已经声明了有数据表，则不会继续生成新的数据表
    db.Model.metadata.create_all(db.engine)

在实际开发中，因为中大型的IT企业，开发团队相对比较完整，所以会存在DBA工程师，所以有些公司是不使用数据迁移的，因为在我们编写服务端代码之前，DBA就已经设计好整个项目的数据表结构了。当然，也有些DBA比较坑，所以我们也要对这种数据迁移要了解，以防万一。同时，我们不能依赖于ORM提供的数据迁移功能，因为在数据库操作层面，最终的核心还是SQL语句。

4.2.2.3 字段类型

官方文档：https://docs.sqlalchemy.org/en/14/core/type_basics.html#generic-types

ORM模型提供字段类型 对应的python中数据类型 描述
Integer int 普通整数，一般是32位
SmallInteger int 取值范围小的整数，一般是16位
BigInteger int 不限制精度的整数，替代integer
Float float 浮点数
Numeric decimal.Decimal 普通数值，一般是32位
String str 变长字符串
Text str 变长字符串，对较长或不限长度的字符串做了优化
Unicode unicode 变长Unicode字符串
UnicodeText unicode 变长Unicode字符串，对较长或不限长度的字符串做了优化
Boolean bool 布尔值
DateTime datetime.datetime 日期和时间
Date datetime.date 日期
Time datetime.time 时间
LargeBinary bytes 二进制文件内容
Enum enum.Enum 枚举类型，相当于django的choices，但是功能没有choices那么强大
4.2.2.4 字段约束
选项名 说明
primary_key 如果为True，代表当前数据表的主键
unique 如果为True，为这列创建唯一 索引，代表这列不允许出现重复的值
index 如果为True，为这列创建普通索引，提高查询效率
nullable 如果为True，允许有空值，如果为False，不允许有空值
default 为这列定义默认值
4.2.2.5 数据操作
4.2.2.5.1 添加一条数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)


if __name__ == '__main__':
    """添加一条数据"""
    student = Student(
        name="小明1号",
        classes="305",
        sex=True,
        age=18,
        description="滚出去..",
    )
    db.session.add(student)  # 相当于 pymysql的execute
    db.session.commit()      # 相当于 事务提交 commit
4.2.2.5.2 添加多条数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)


if __name__ == '__main__':
    """添加多条数据"""
    student1 = Student(name="小明1号", classes="302", sex=True, age=18, description="滚出去..")
    student2 = Student(name="小明2号", classes="303", sex=True, age=18, description="滚出去..")
    student3 = Student(name="小明3号", classes="304", sex=True, age=18, description="滚出去..")
    student4 = Student(name="小明4号", classes="305", sex=True, age=18, description="滚出去..")

    db.session.add_all([student1, student2,student3,student4])  # 相当于 pymysql的executemany
    db.session.commit()      # 相当于 事务提交 commit
4.2.2.5.3 查询一条数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    # def __str__(self):
    #     return f"<{self.__class__.__name__} {self.name}>"

    def __repr__(self):
        """
        当实例对象被使用print打印时，自动执行此处当前，
        当前__repr__使用与上面__str__一致，返回值必须时字符串格式，否则报错！！！
        """
        return f"<{self.__class__.__name__} {self.name}>"


if __name__ == '__main__':
    """查询一条数据"""

    """
    get 用于根据主键值获取一条，如果查不到数据，则返回None，查到结果则会被ORM底层使用当前模型类来进行实例化成模型对象
    get 可以接收1个或多个主键参数，只能作为主键值
    """
    # get(4) 相当于 where id=4;
    student = db.session.query(Student).get(4)  # 如果查询的是联合主键 写法： get((5,10)) 或 get({"id": 5, "version_id": 10})
    print(student)
    # <__main__.Student object at 0x7f4161c69520> <class '__main__.Student'>


    """
    使用first获取查询结果集的第一个结果
    first 不能接收任何参数，所以一般配合filter或者filter_by 来进行使用的
    """
    student = db.session.query(Student).first()

    # 获取属性值
    if student:
        print(f"id={student.id}, name={student.name}, age={student.age}")
4.2.2.5.4 查询多条数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"


if __name__ == '__main__':
    """查询多条数据"""
    student_list = db.session.query(Student).all()
    print(student_list)

    # 基于循环输出每一个模型对象中的属性
    for student in student_list:
        print(f"id={student.id}, name={student.name}, classes={student.classes}")
4.2.2.5.5 过滤条件查询
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        return {
            "id": self.id,
            "name": self.name
        }


if __name__ == '__main__':
     """模型对象转字典"""
    # student_list = db.session.query(Student).all()
    # print([student.todict() for student in student_list])

    """过滤条件查询"""
    """
    filter_by - 精确查询
    filter_by支持值相等=号操作，不能使用大于、小于或不等于的操作一律不能使用
    """
    # # 单个字段条件
    # students = db.session.query(Student).filter_by(name="小明1号").all()
    # print(students)

    # # 多个and条件
    # students = db.session.query(Student).filter_by(sex=1, age=18).all()
    # print(students)

    """
    filter - 匹配查询
    支持所有的运算符表达式，比filter精确查询要更强大
    注意：条件表达式中的字段名必须写上模型类名
    filter中的判断相等必须使用==2个等号
    """
    # # 获取查询结果集的所有数据，列表
    # students = db.session.query(Student).filter(Student.age > 17).all()
    # print(students) # [<Student 小明1号>, <Student 小明1号>, <Student 小明3号>, <Student 小明4号>]
    #
    # # 获取查询结果集的第一条数据，模型对象
    # students = db.session.query(Student).filter(Student.age < 18).first()
    # print(students) # <Student 小明1号>

    """in运算符"""
    students = db.session.query(Student).filter(Student.id.in_([1, 3, 4])).all()
    print(students) # [<Student 小明1号>, <Student 小明1号>, <Student 小明2号>]

    """多条件表达式"""
    """多个or条件"""
    # from sqlalchemy import or_
    # # 查询302或303班的学生
    # students = db.session.query(Student).filter(or_(Student.classes==303, Student.classes==302)).all()
    # print(students) # [<Student 小明1号>, <Student 小明2号>]

    """多个and条件"""
    # students = db.session.query(Student).filter(Student.age==18, Student.sex==1).all()
    # print(students) # [<Student 小明1号>, <Student 小明3号>]

    # from sqlalchemy import and_
    # students = db.session.query(Student).filter(and_(Student.age == 18, Student.sex == 1)).all()
    # print(students) # [<Student 小明1号>, <Student 小明3号>]

    """and_主要用于与or_一起使用的"""
    # 查询305的18岁男生 或者 305班的17岁女生
    from sqlalchemy import and_, or_
    # students = db.session.query(Student).filter(
    #     or_(
    #         and_(Student.classes==305, Student.age==18, Student.sex==1),
    #         and_(Student.classes==305, Student.age==17, Student.sex==2),
    #     )
    # ).all()

    students = db.session.query(Student).filter(
        and_(
            Student.classes == 305,
            or_(
                and_(Student.age == 18, Student.sex == 1),
                and_(Student.age == 17, Student.sex == 2)
            )
        )
    ).all()

    print(students) # [<Student 小明1号>, <Student 小明4号>]
4.2.2.5.6 更新数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        return {
            "id": self.id,
            "name": self.name
        }


if __name__ == '__main__':
    """更改数据"""
    # 查询要更改的数据[目的为了让ORM实现表记录与模型对象的映射]
    student = db.session.query(Student).get(6)
    student.age = 16
    student.classes = 301
    db.session.commit()
4.2.2.5.7 删除数据
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        return {
            "id": self.id,
            "name": self.name
        }


if __name__ == '__main__':
    """更改数据"""
    # 查询要删除的数据[目的为了让ORM实现表记录与模型对象的映射]
    student = db.session.query(Student).get(6)
    db.session.delete(student)
    db.session.commit()
4.2.2.5.8 其他操作
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        return {
            "id": self.id,
            "name": self.name,
            "classes": self.classes,
            "age": self.age,
        }


if __name__ == '__main__':
    """限制结果数量"""
    # student_list = db.session.query(Student).limit(3).all()
    # print(student_list)

    # """结果排序"""
    # student_list = db.session.query(Student).order_by(Student.classes.asc(), Student.age.desc()).all()
    # print([student.todict() for student in student_list])

    # # 事务操作
    # db.session.begin()
    # db.session.commit()
    # db.session.rollback()
4.2.2.5.9 原生SQL操作

针对开发中一些复杂的业务，往往使用ORM提供的方法操作会极其复杂或者书写比较困难，或根本提供对应的方法，此时我们可以考虑直接写原生SQL。

import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        return {
            "id": self.id,
            "name": self.name,
            "classes": self.classes,
            "age": self.age,
        }


if __name__ == '__main__':
    """DQL-读取数据"""
    # # 返回游标对象
    # cursor = db.session.execute("select * from tb_student")
    # # # 获取一条结果,mappings方法表示把结果从元组转换成字典
    # student = cursor.mappings().fetchone()
    # print(student)

    # # 获取指定数量结果
    # student_list = cursor.mappings().fetchmany(2)
    # print(student_list)

    # # 获取所有结果
    # student_list = cursor.mappings().fetchall(cursor=)
    # print(student_list)


    """DML-写入数据"""
    sql = "insert into tb_student(name, class, age, sex, description) values(:name, :class, :age, :sex, :description)"
    data = {
        "class": "305",
        "age": 19,
        "name": "xiaohong",
        "sex": 0,
        "description": ".....",
    }

    cursor = db.session.execute(sql, params=data)
    db.session.commit()
    print(cursor.lastrowid)  # 获取最后添加的主键ID
4.2.2.5.10 模型对象转字典
import db
from datetime import datetime


# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

if __name__ == '__main__':
    student = db.session.query(Student).get(1)
    # 不仅会返回当前模型的字段属性，
    # 还会返回当前对象与数据表的映射关系
    # 如果有使用了外键，还会记录表与表之间的关联关系
    print(student.__dict__)

4.2.3 异步操作sqlalchemy

官方文档：https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

4.2.3.1 初始化
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import *

# 1. 创建数据库驱动（引擎）
engine = create_async_engine(
    # 连接数据库的URL
    # url="驱动名称://账户:密码@IP地址:端口/数据库名?charset=utf8mb4",  # 如果底层驱动是greenlet
    url="mysql+aiomysql://root:123@127.0.0.1:3306/students?charset=utf8mb4",  # 如果底层驱动是aiomysql
    # url="mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4",  # 如果底层驱动是MySQLdb
    echo=True,  # 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
    pool_size=10,  # 连接池的数据库连接数量，默认为5个，设置为0时表示连接无限制
    max_overflow=30,    # 连接池的数据库连接最大数量，默认为10个
    pool_recycle=60*30  # 设置时间以限制数据库连接多久没使用则自动断开（指代max_overflow-pool_size），单位：秒
)

# 基于底层数据库驱动建立数据库连接会话，相当于cursor游标
async_session = sessionmaker(
    engine, expire_on_commit=False, class_=AsyncSession
)

# 模型类对象的基类，内部提供了数据库的基本操作以及共同方法
Model = declarative_base()
4.2.3.2 基本使用
import asyncio
import async_db as db
from datetime import datetime

# 1. 创建一个与数据库对应的模型类对象
class Student(db.Model):
    """学生表模型"""
    __tablename__ = "async_student"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

async def main():
    # 异步数据迁移
    async with db.engine.begin() as conn:
        # 删除当前程序中所有的模型对应的数据表
        # await conn.run_sync(Model.metadata.drop_all)

        # 创建当前程序中所有的模型的数据表，如果数据表不存在的情况下
        await conn.run_sync(db.Model.metadata.create_all)

    # 开启会话
    async with db.async_session() as session:
        # 开启事务
        async with session.begin():
            """DQL - 查询数据"""
            # # 拼接SQL语句
            sql = db.select(Student).filter(Student.classes == 305).order_by(Student.id)
            print(sql)
            # 异步执行SQL语句
            student = await session.execute(sql)
            # # 获取一个结果
            # print(student.first())
            # 获取多个结果
            # print(student.mappings().all())

            """DML - 写入数据"""
            # student1 = Student(name="小明1号", classes="302", sex=True, age=18, description="滚出去..")
            # student2 = Student(name="小明2号", classes="303", sex=True, age=18, description="滚出去..")
            # student3 = Student(name="小明3号", classes="304", sex=True, age=18, description="滚出去..")
            # student4 = Student(name="小明4号", classes="305", sex=True, age=18, description="滚出去..")
            # # 添加一条数据
            # session.add(student1)
            # # 添加多条数据
            # session.add_all([student1, student2, student3,student4])

            # 异步提交事务
            await session.commit()

if __name__ == '__main__':
    # asyncio.run(main())
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())