MySQL - 简介与架构

10.9k
MySQL / 基础篇
SQL

简介 ​

数据库 ​

数据库：DataBase，简称 DB，存储和管理数据的仓库

数据库的优势：

  • 可以持久化存储数据
  • 方便存储和管理数据
  • 使用了统一的方式操作数据库 SQL

数据库、数据表、数据的关系介绍：

  • 数据库

    • 用于存储和管理数据的仓库
    • 一个库中可以包含多个数据表

  • 数据表

    • 数据库最重要的组成部分之一
    • 由纵向的列和横向的行组成（类似 excel 表格）
    • 可以指定列名、数据类型、约束等
    • 一个表中可以存储多条数据

  • 数据：想要永久化存储的数据

参考视频：https://www.bilibili.com/video/BV1zJ411M7TB

参考专栏：https://time.geekbang.org/column/intro/139

参考书籍：https://book.douban.com/subject/35231266/

MySQL ​

MySQL 数据库是一个最流行的关系型数据库管理系统之一，关系型数据库是将数据保存在不同的数据表中，而且表与表之间可以有关联关系，提高了灵活性

缺点：数据存储在磁盘中，导致读写性能差，而且数据关系复杂，扩展性差

MySQL 所使用的 SQL 语句是用于访问数据库最常用的标准化语言

MySQL 配置：

  • MySQL 安装：https://www.jianshu.com/p/ba48f1e386f0

  • MySQL 配置：

    • 修改 MySQL 默认字符集：安装 MySQL 之后第一件事就是修改字符集编码

      sql
      vim /etc/mysql/my.cnf

添加如下内容：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8

    • 启动 MySQL 服务：

      shell
      systemctl start/restart mysql

    • 登录 MySQL：

      shell
      mysql -u root -p  敲回车，输入密码
初始密码查看：cat /var/log/mysqld.log
在root@localhost:   后面的就是初始密码

    • 查看默认字符集命令：

      sql
      SHOW VARIABLES LIKE 'char%';

    • 修改MySQL登录密码：

      sql
      set global validate_password_policy=0;
set global validate_password_length=1;
  
set password=password('密码');

    • 授予远程连接权限（MySQL 内输入）：

      sql
      -- 授权
grant all privileges on *.* to 'root' @'%' identified by '密码';
-- 刷新
flush privileges;

  • 修改 MySQL 绑定 IP：

    shell
    cd /etc/mysql/mysql.conf.d
sudo chmod 666 mysqld.cnf 
vim mysqld.cnf 
# bind-address = 127.0.0.1注释该行

  • 关闭 Linux 防火墙

    shell
    systemctl stop firewalld.service
# 放行3306端口

体系架构 ​

整体架构 ​

体系结构详解：

  • 第一层：网络连接层
    • 一些客户端和链接服务，包含本地 Socket 通信和大多数基于客户端/服务端工具实现的 TCP/IP 通信，主要完成一些类似于连接处理、授权认证、及相关的安全方案
    • 在该层上引入了连接池 Connection Pool 的概念，管理缓冲用户连接，线程处理等需要缓存的需求
    • 在该层上实现基于 SSL 的安全链接，服务器也会为安全接入的每个客户端验证它所具有的操作权限
  • 第二层：核心服务层
    • 查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，所有的内置函数（日期、数学、加密函数等）
      • Management Serveices & Utilities：系统管理和控制工具，备份、安全、复制、集群等
      • SQL Interface：接受用户的 SQL 命令，并且返回用户需要查询的结果
      • Parser：SQL 语句分析器
      • Optimizer：查询优化器
      • Caches & Buffers：查询缓存，服务器会查询内部的缓存，如果缓存空间足够大，可以在大量读操作的环境中提升系统性能
    • 所有跨存储引擎的功能在这一层实现，如存储过程、触发器、视图等
    • 在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询顺序，是否利用索引等， 最后生成相应的执行操作
    • MySQL 中服务器层不管理事务，事务是由存储引擎实现的
  • 第三层：存储引擎层
    • Pluggable Storage Engines：存储引擎接口，MySQL 区别于其他数据库的重要特点就是其存储引擎的架构模式是插件式的（存储引擎是基于表的，而不是数据库）
    • 存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信
    • 不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎
  • 第四层：系统文件层
    • 数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互
    • File System：文件系统，保存配置文件、数据文件、日志文件、错误文件、二进制文件等
image-20250319234059758

建立连接 ​

连接器 ​

池化技术：对于访问数据库来说，建立连接的代价是比较昂贵的，因为每个连接对应一个用来交互的线程，频繁的创建关闭连接比较耗费资源，有必要建立数据库连接池，以提高访问的性能

连接建立 TCP 以后需要做权限验证，验证成功后可以进行执行 SQL。如果这时管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有再新建的连接才会使用新的权限设置

MySQL 服务器可以同时和多个客户端进行交互，所以要保证每个连接会话的隔离性（事务机制部分详解）

整体的执行流程：

image-20250319234144374

权限信息 ​

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据

flush privileges 语句本身会用数据表（磁盘）的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下使用，这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以尽量不要使用这类语句

image-20250319234950483

连接状态 ​

客户端如果长时间没有操作，连接器就会自动断开，时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

为了减少连接的创建，推荐使用长连接，但是过多的长连接会造成 OOM，解决方案：

  • 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连

    sql
    KILL CONNECTION id

  • MySQL 5.7 版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

SHOW PROCESSLIST：查看当前 MySQL 在进行的线程，可以实时地查看 SQL 的执行情况，其中的 Command 列显示为 Sleep 的这一行，就表示现在系统里面有一个空闲连接

image-20250319234249865
参数含义
ID用户登录 mysql 时系统分配的 connection_id，可以使用函数 connection_id() 查看
User显示当前用户，如果不是 root，这个命令就只显示用户权限范围的 sql 语句
Host显示这个语句是从哪个 ip 的哪个端口上发的，可以用来跟踪出现问题语句的用户
db显示这个进程目前连接的是哪个数据库
Command显示当前连接的执行的命令，一般取值为休眠 Sleep、查询 Query、连接 Connect 等
Time显示这个状态持续的时间，单位是秒
State显示使用当前连接的 sql 语句的状态，以查询为例，需要经过 copying to tmp table、sorting result、sending data等状态才可以完成
Info显示执行的 sql 语句，是判断问题语句的一个重要依据

Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅只是返回给客户端，是处于执行器过程中的任意阶段。由于在 Sending data 状态下，MySQL 线程需要做大量磁盘读取操作，所以是整个查询中耗时最长的状态

执行流程 ​

查询缓存 ​

工作流程 ​

当执行完全相同的 SQL 语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存

查询过程：

  1. 客户端发送一条查询给服务器
  2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果（一般是 K-V 键值对），否则进入下一阶段
  3. 分析器进行 SQL 分析，再由优化器生成对应的执行计划
  4. 执行器根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
  5. 将结果返回给客户端

大多数情况下不建议使用查询缓存，因为查询缓存往往弊大于利

  • 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能费力地把结果存起来，还没使用就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低
  • 除非业务就是有一张静态表，很长时间才会更新一次，比如一个系统配置表，那这张表上的查询才适合使用查询缓存
缓存配置 ​

  1. 查看当前 MySQL 数据库是否支持查询缓存：

    sql
    SHOW VARIABLES LIKE 'have_query_cache';	-- YES

  2. 查看当前 MySQL 是否开启了查询缓存：

    sql
    SHOW VARIABLES LIKE 'query_cache_type';	-- OFF

    参数说明：

    • OFF 或 0：查询缓存功能关闭

    • ON 或 1：查询缓存功能打开，查询结果符合缓存条件即会缓存，否则不予缓存；可以显式指定 SQL_NO_CACHE 不予缓存

    • DEMAND 或 2：查询缓存功能按需进行，显式指定 SQL_CACHE 的 SELECT 语句才缓存，其它不予缓存

      sql
      SELECT SQL_CACHE id, name FROM customer; -- SQL_CACHE:查询结果可缓存
SELECT SQL_NO_CACHE id, name FROM customer;-- SQL_NO_CACHE:不使用查询缓存

  3. 查看查询缓存的占用大小：

    sql
    SHOW VARIABLES LIKE 'query_cache_size';-- 单位是字节 1048576 / 1024 = 1024 = 1KB

  4. 查看查询缓存的状态变量：

    sql
    SHOW STATUS LIKE 'Qcache%';
    image-20250319234326198
    参数含义
    Qcache_free_blocks查询缓存中的可用内存块数
    Qcache_free_memory查询缓存的可用内存量
    Qcache_hits查询缓存命中数
    Qcache_inserts添加到查询缓存的查询数
    Qcache_lowmen_prunes由于内存不足而从查询缓存中删除的查询数
    Qcache_not_cached非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
    Qcache_queries_in_cache查询缓存中注册的查询数
    Qcache_total_blocks查询缓存中的块总数

  5. 配置 my.cnf：

    sh
    sudo chmod 666 /etc/mysql/my.cnf
vim my.cnf
# mysqld中配置缓存
query_cache_type=1

    重启服务既可生效，执行 SQL 语句进行验证 ，执行一条比较耗时的 SQL 语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存

缓存失效 ​

查询缓存失效的情况：

  • SQL 语句不一致，要想命中查询缓存，查询的 SQL 语句必须一致，因为缓存中 key 是查询的语句，value 是查询结构

    sql
    select count(*) from tb_item;
Select count(*) from tb_item;	-- 不走缓存，首字母不一致

  • 当查询语句中有一些不确定查询时，则不会缓存，比如：now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()

    sql
    SELECT * FROM tb_item WHERE updatetime < NOW() LIMIT 1;
SELECT USER();
SELECT DATABASE();

  • 不使用任何表查询语句：

    sql
    SELECT 'A';

  • 查询 mysql、information_schema、performance_schema 等系统表时，不走查询缓存：

    sql
    SELECT * FROM information_schema.engines;

  • 在跨存储引擎的存储过程、触发器或存储函数的主体内执行的查询，缓存失效

  • 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除，包括使用 MERGE 映射到已更改表的表的查询，比如：INSERT、UPDATE、DELETE、ALTER TABLE、DROP TABLE、DROP DATABASE

分析器 ​

没有命中查询缓存，就开始了 SQL 的真正执行，分析器会对 SQL 语句做解析

sql
SELECT * FROM t WHERE id = 1;

解析器：处理语法和解析查询，生成一课对应的解析树

  • 先做词法分析，输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么代表什么。从输入的 select 这个关键字识别出来这是一个查询语句；把字符串 t 识别成 表名 t，把字符串 id 识别成列 id
  • 然后做语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果语句不对，就会收到 You have an error in your SQL syntax 的错误提醒

预处理器：进一步检查解析树的合法性，比如数据表和数据列是否存在、别名是否有歧义等

优化器 ​

成本分析 ​

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

  • 根据搜索条件找出所有可能的使用的索引
  • 成本分析，执行成本由 I/O 成本和 CPU 成本组成，计算全表扫描和使用不同索引执行 SQL 的代价
  • 找到一个最优的执行方案，用最小的代价去执行语句

在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少意味着访问磁盘的次数越少，消耗的 CPU 资源越少，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

统计数据 ​

MySQL 中保存着两种统计数据：

  • innodb_table_stats 存储了表的统计数据，每一条记录对应着一个表的统计数据
  • innodb_index_stats 存储了索引的统计数据，每一条记录对应着一个索引的一个统计项的数据

MySQL 在真正执行语句之前，并不能精确地知道满足条件的记录有多少条，只能根据统计信息来估算记录，统计信息就是索引的区分度，一个索引上不同的值的个数（比如性别只能是男女，就是 2 ），称之为基数（cardinality），基数越大说明区分度越好

通过采样统计来获取基数，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

在 MySQL 中，有两种存储统计数据的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

  • ON：表示统计信息会持久化存储（默认），采样页数 N 默认为 20，可以通过 innodb_stats_persistent_sample_pages 指定，页数越多统计的数据越准确，但消耗的资源更大
  • OFF：表示统计信息只存储在内存，采样页数 N 默认为 8，也可以通过系统变量设置（不推荐，每次重新计算浪费资源）

数据表是会持续更新的，两种统计信息的更新方式：

  • 设置 innodb_stats_auto_recalc 为 1，当发生变动的记录数量超过表大小的 10% 时，自动触发重新计算，不过是异步进行
  • 调用 ANALYZE TABLE t 手动更新统计信息，只对信息做重新统计（不是重建表），没有修改数据，这个过程中加了 MDL 读锁并且是同步进行，所以会暂时阻塞系统

EXPLAIN 执行计划在优化器阶段生成，如果 explain 的结果预估的 rows 值跟实际情况差距比较大，可以执行 analyze 命令重新修正信息

错选索引 ​

采样统计本身是估算数据，或者 SQL 语句中的字段选择有问题时，可能导致 MySQL 没有选择正确的执行索引

解决方法：

  • 采用 force index 强行选择一个索引

    sql
    SELECT * FROM user FORCE INDEX(name) WHERE NAME='klaus';

  • 可以考虑修改 SQL 语句，引导 MySQL 使用期望的索引

  • 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

执行器 ​

开始执行的时候，要先判断一下当前连接对表有没有执行查询的权限，如果没有就会返回没有权限的错误，在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。如果有权限，就打开表继续执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

引擎层 ​

Server 层和存储引擎层的交互是以记录为单位的，存储引擎会将单条记录返回给 Server 层做进一步处理，并不是直接返回所有的记录

工作流程：

  • 首先根据二级索引选择扫描范围，获取第一条符合二级索引条件的记录，进行回表查询，将聚簇索引的记录返回 Server 层，由 Server 判断记录是否符合要求
  • 然后在二级索引上继续扫描下一个符合条件的记录

推荐阅读：https://mp.weixin.qq.com/s/YZ-LckObephrP1f15mzHpA

终止流程 ​

终止语句 ​

终止线程中正在执行的语句：

sql
KILL QUERY thread_id

KILL 不是马上终止的意思，而是告诉执行线程这条语句已经不需要继续执行，可以开始执行停止的逻辑（类似于打断）。因为对表做增删改查操作，会在表上加 MDL 读锁，如果线程被 KILL 时就直接终止，那这个 MDL 读锁就没机会被释放了

命令 KILL QUERYthread_id_A 的执行流程：

  • 把 session A 的运行状态改成 THD::KILL_QUERY（将变量 killed 赋值为 THD::KILL_QUERY）
  • 给 session A 的执行线程发一个信号，让 session A 来处理这个 THD::KILL_QUERY 状态

会话处于等待状态（锁阻塞），必须满足是一个可以被唤醒的等待，必须有机会去判断线程的状态，如果不满足就会造成 KILL 失败

典型场景：innodb_thread_concurrency 为 2，代表并发线程上限数设置为 2

  • session A 执行事务，session B 执行事务，达到线程上限；此时 session C 执行事务会阻塞等待，session D 执行 kill query C 无效
  • C 的逻辑是每 10 毫秒判断是否可以进入 InnoDB 执行，如果不行就调用 nanosleep 函数进入 sleep 状态，没有去判断线程状态

补充：执行 Ctrl+C 的时候，是 MySQL 客户端另外启动一个连接，然后发送一个 KILL QUERY 命令

终止连接 ​

断开线程的连接：

sql
KILL CONNECTION id

断开连接后执行 SHOW PROCESSLIST 命令，如果这条语句的 Command 列显示 Killed，代表线程的状态是 KILL_CONNECTION，说明这个线程有语句正在执行，当前状态是停止语句执行中，终止逻辑耗时较长

  • 超大事务执行期间被 KILL，这时回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长
  • 大查询回滚，如果查询过程中生成了比较大的临时文件，删除临时文件可能需要等待 IO 资源，导致耗时较长
  • DDL 命令执行到最后阶段被 KILL，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久

总结：KILL CONNECTION 本质上只是把客户端的 SQL 连接断开，后面的终止流程还是要走 KILL QUERY

一个事务被 KILL 之后，持续处于回滚状态，不应该强行重启整个 MySQL 进程，应该等待事务自己执行完成，因为重启后依然继续做回滚操作的逻辑

常用工具 ​

mysql ​

mysql 不是指 mysql 服务，而是指 mysql 的客户端工具

sh
mysql [options] [database]
  • -u --user=name：指定用户名
  • -p --password[=name]：指定密码
  • -h --host=name：指定服务器IP或域名
  • -P --port=#：指定连接端口
  • -e --execute=name：执行SQL语句并退出，在控制台执行SQL语句，而不用连接到数据库执行

示例：

sh
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -uroot -p2143 db01 -e "select * from tb_book";

admin ​

mysqladmin 是一个执行管理操作的客户端程序，用来检查服务器的配置和当前状态、创建并删除数据库等

通过 mysqladmin --help 指令查看帮助文档

sh
mysqladmin -uroot -p2143 create 'test01';

binlog ​

服务器生成的日志文件以二进制格式保存，如果需要检查这些文本，就要使用 mysqlbinlog 日志管理工具

sh
mysqlbinlog [options]  log-files1 log-files2 ...

  • -d --database=name：指定数据库名称，只列出指定的数据库相关操作

  • -o --offset=#：忽略掉日志中的前 n 行命令。

  • -r --result-file=name：将输出的文本格式日志输出到指定文件。

  • -s --short-form：显示简单格式，省略掉一些信息。

  • –start-datatime=date1 --stop-datetime=date2：指定日期间隔内的所有日志

  • –start-position=pos1 --stop-position=pos2：指定位置间隔内的所有日志

dump ​

命令介绍 ​

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，及插入表的 SQL 语句

sh
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

连接选项：

  • -u --user=name：指定用户名
  • -p --password[=name]：指定密码
  • -h --host=name：指定服务器 IP 或域名
  • -P --port=#：指定连接端口

输出内容选项：

  • –add-drop-database：在每个数据库创建语句前加上 Drop database 语句
  • –add-drop-table：在每个表创建语句前加上 Drop table 语句 , 默认开启，不开启 (–skip-add-drop-table)
  • -n --no-create-db：不包含数据库的创建语句
  • -t --no-create-info：不包含数据表的创建语句
  • -d --no-data：不包含数据
  • -T, --tab=name：自动生成两个文件：一个 .sql 文件，创建表结构的语句；一个 .txt 文件，数据文件，相当于 select into outfile

示例：

sh
mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p2143 -T /tmp test city
数据备份 ​

命令行方式：

  • 备份命令：mysqldump -u root -p 数据库名称 > 文件保存路径
  • 恢复
    1. 登录MySQL数据库：mysql -u root p
    2. 删除已经备份的数据库
    3. 重新创建与备份数据库名称相同的数据库
    4. 使用该数据库
    5. 导入文件执行：source 备份文件全路径

更多方式参考：https://time.geekbang.org/column/article/81925

图形化界面：

  • 备份

    image-20250319234400695

  • 恢复

    image-20250319234436591

import ​

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件

sh
mysqlimport [options]  db_name  textfile1  [textfile2...]

示例：

sh
mysqlimport -uroot -p2143 test /tmp/city.txt

导入 sql 文件，可以使用 MySQL 中的 source 指令 :

sql
source 文件全路径

show ​

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引

sh
mysqlshow [options] [db_name [table_name [col_name]]]

  • –count：显示数据库及表的统计信息（数据库，表 均可以不指定）

  • -i：显示指定数据库或者指定表的状态信息

示例：

sh
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p1234 --count
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p1234 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p1234 test book --count

高级结构 ​

视图 ​

基本介绍 ​

视图概念：视图是一种虚拟存在的数据表，这个虚拟的表并不在数据库中实际存在

本质：将一条 SELECT 查询语句的结果封装到了一个虚拟表中，所以在创建视图的时候，工作重心要放在这条 SELECT 查询语句上

作用：将一些比较复杂的查询语句的结果，封装到一个虚拟表中，再有相同查询需求时，直接查询该虚拟表

优点：

  • 简单：使用视图的用户不需要关心表的结构、关联条件和筛选条件，因为虚拟表中已经是过滤好的结果集

  • 安全：使用视图的用户只能访问查询的结果集，对表的权限管理并不能限制到某个行某个列

  • 数据独立，一旦视图的结构确定，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

视图创建 ​

  • 创建视图

    sql
    CREATE [OR REPLACE] 
VIEW 视图名称 [(列名列表)] 
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION];

    WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件：

    • LOCAL：只要满足本视图的条件就可以更新
    • CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新， 默认值

  • 例如

    sql
    -- 数据准备 city
id	NAME	cid
1	深圳	 	1
2	上海		1
3	纽约		2
4	莫斯科	    3

-- 数据准备 country
id	NAME
1	中国
2	美国
3	俄罗斯

-- 创建city_country视图，保存城市和国家的信息(使用指定列名)
CREATE 
VIEW 
	city_country (city_id,city_name,country_name)
AS
    SELECT
        c1.id,
        c1.name,
        c2.name
    FROM
        city c1,
        country c2
    WHERE
        c1.cid=c2.id;

视图查询 ​

  • 查询所有数据表，视图也会查询出来

    sql
    SHOW TABLES;
SHOW TABLE STATUS [\G];

  • 查询视图

    sql
    SELECT * FROM 视图名称;

  • 查询某个视图创建

    sql
    SHOW CREATE VIEW 视图名称;

视图修改 ​

视图表数据修改，会自动修改源表中的数据，因为更新的是视图中的基表中的数据

  • 修改视图表中的数据

    sql
    UPDATE 视图名称 SET 列名 = 值 WHERE 条件;

  • 修改视图的结构

    sql
    ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(列名列表)] 
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION]

-- 将视图中的country_name修改为name
ALTER 
VIEW 
	city_country (city_id,city_name,name) 
AS
    SELECT
        c1.id,
        c1.name,
        c2.name
    FROM
        city c1,
        country c2
    WHERE
        c1.cid=c2.id;

视图删除 ​

  • 删除视图

    sql
    DROP VIEW 视图名称;

  • 如果存在则删除

    sql
    DROP VIEW IF EXISTS 视图名称;

存储过程 ​

基本介绍 ​

存储过程和函数：存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合

存储过程和函数的好处：

  • 提高代码的复用性
  • 减少数据在数据库和应用服务器之间的传输，提高传输效率
  • 减少代码层面的业务处理
  • 一次编译永久有效

存储过程和函数的区别：

  • 存储函数必须有返回值
  • 存储过程可以没有返回值

基本操作 ​

DELIMITER：

  • DELIMITER 关键字用来声明 sql 语句的分隔符，告诉 MySQL 该段命令已经结束

  • MySQL 语句默认的分隔符是分号，但是有时需要一条功能 sql 语句中包含分号，但是并不作为结束标识，这时使用 DELIMITER 来指定分隔符：

    sql
    DELIMITER 分隔符

存储过程的创建调用查看和删除：

  • 创建存储过程

    sql
    -- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

  • 调用存储过程

    sql
    CALL 存储过程名称(实际参数);

  • 查看存储过程

    sql
    SELECT * FROM mysql.proc WHERE db='数据库名称';

  • 删除存储过程

    sql
    DROP PROCEDURE [IF EXISTS] 存储过程名称;

练习：

  • 数据准备

    sql
    id	NAME	age		gender	score
1	张三		23		男		95
2	李四		24		男		98
3	王五		25		女		100
4	赵六		26		女		90

  • 创建 stu_group() 存储过程，封装分组查询总成绩，并按照总成绩升序排序的功能

    sql
    DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC; 
END$

DELIMITER ;

-- 调用存储过程
CALL stu_group();
-- 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;

存储语法 ​

变量使用 ​

存储过程是可以进行编程的，意味着可以使用变量、表达式、条件控制语句等，来完成比较复杂的功能

  • 定义变量：DECLARE 定义的是局部变量，只能用在 BEGIN END 范围之内

    sql
    DECLARE 变量名 数据类型 [DEFAULT 默认值];

  • 变量的赋值

    sql
    SET 变量名 = 变量值;
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

  • 数据准备：表 student

    sql
    id	NAME	age		gender	score
1	张三		23		男		95
2	李四		24		男		98
3	王五		25		女		100
4	赵六		26		女		90

  • 定义两个 int 变量，用于存储男女同学的总分数

    sql
    DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
	-- 定义两个变量
	DECLARE men,women INT;
	-- 查询男同学的总分数，为men赋值
	SELECT SUM(score) INTO men FROM student WHERE gender='男';
	-- 查询女同学的总分数，为women赋值
	SELECT SUM(score) INTO women FROM student WHERE gender='女';
	-- 使用变量
	SELECT men,women;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test3();
IF语句 ​

  • if 语句标准语法

    sql
    IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

  • 数据准备：表 student

    sql
    id	NAME	age		gender	score
1	张三		23		男		95
2	李四		24		男		98
3	王五		25		女		100
4	赵六		26		女		90

  • 根据总成绩判断：全班 380 分及以上学习优秀、320 ~ 380 学习良好、320 以下学习一般

    sql
    DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
	DECLARE total INT;							-- 定义总分数变量
	DECLARE description VARCHAR(10);			-- 定义分数描述变量
	SELECT SUM(score) INTO total FROM student; 	-- 为总分数变量赋值
	-- 判断总分数
	IF total >= 380 THEN
		SET description = '学习优秀';
	ELSEIF total >=320 AND total < 380 THEN
		SET description = '学习良好';
	ELSE
		SET description = '学习一般';
	END IF;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();
参数传递 ​

  • 参数传递的语法

    IN：代表输入参数，需要由调用者传递实际数据，默认的 OUT：代表输出参数，该参数可以作为返回值 INOUT：代表既可以作为输入参数，也可以作为输出参数

    sql
    DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$

DELIMITER ;

  • 输入总成绩变量，代表学生总成绩，输出分数描述变量，代表学生总成绩的描述

    sql
    DELIMITER $

CREATE PROCEDURE pro_test6(IN total INT, OUT description VARCHAR(10))
BEGIN
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = '学习优秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END IF;
END$

DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6(310,@description);
CALL pro_test6((SELECT SUM(score) FROM student), @description);
-- 查询总成绩描述
SELECT @description;

  • 查看参数方法

    • @变量名 : 用户会话变量，代表整个会话过程他都是有作用的，类似于全局变量
    • @@变量名 : 系统变量
CASE ​

  • 标准语法 1

    sql
    CASE 表达式
    WHEN 值1 THEN 执行sql语句1;
    [WHEN 值2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
END CASE;

  • 标准语法 2

    sql
    sCASE
    WHEN 判断条件1 THEN 执行sql语句1;
    [WHEN 判断条件2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
END CASE;

  • 演示

    sql
    DELIMITER $
CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- 定义变量
	DECLARE description VARCHAR(10);
	-- 使用case判断
	CASE
	WHEN total >= 380 THEN
		SET description = '学习优秀';
	WHEN total >= 320 AND total < 380 THEN
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END CASE;
	
	-- 查询分数描述信息
	SELECT description;
END$
DELIMITER ;
-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));
WHILE ​

  • while 循环语法

    sql
    WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

  • 计算 1~100 之间的偶数和

    sql
    DELIMITER $
CREATE PROCEDURE pro_test6()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		IF num % 2 = 0 THEN
			SET result = result + num;
		END IF;
		SET num = num + 1;
	END WHILE;
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;

-- 调用pro_test6存储过程
CALL pro_test6();
REPEAT ​

  • repeat 循环标准语法

    sql
    初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

  • 计算 1~10 之间的和

    sql
    DELIMITER $
CREATE PROCEDURE pro_test9()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- repeat循环
	REPEAT
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
		-- 停止循环
		UNTIL num > 10
	END REPEAT;
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;
-- 调用pro_test9存储过程
CALL pro_test9();
LOOP ​

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，如果不加退出循环的语句，那么就变成了死循环

  • loop 循环标准语法

    sql
    [循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

  • 计算 1~10 之间的和

    sql
    DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- loop循环
	l:LOOP
		-- 条件成立，停止循环
		IF num > 10 THEN
			LEAVE l;
		END IF;
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
	END LOOP l;
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();
游标 ​

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理

  • 游标可以遍历返回的多行结果，每次拿到一整行数据
  • 简单来说游标就类似于集合的迭代器遍历
  • MySQL 中的游标只能用在存储过程和函数中

游标的语法

  • 创建游标

    sql
    DECLARE 游标名称 CURSOR FOR 查询sql语句;

  • 打开游标

    sql
    OPEN 游标名称;

  • 使用游标获取数据

    sql
    FETCH 游标名称 INTO 变量名1,变量名2,...;

  • 关闭游标

    sql
    CLOSE 游标名称;

  • Mysql 通过一个 Error handler 声明来判断指针是否到尾部，并且必须和创建游标的 SQL 语句声明在一起：

    sql
    DECLARE EXIT HANDLER FOR NOT FOUND (do some action，一般是设置标志变量)

游标的基本使用

  • 数据准备：表 student

    sql
    id	NAME	age		gender	score
1	张三		23		男		95
2	李四		24		男		98
3	王五		25		女		100
4	赵六		26		女		90

  • 创建 stu_score 表

    sql
    CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

  • 将student表中所有的成绩保存到stu_score表中

    sql
    DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 定义标记变量
	DECLARE flag INT DEFAULT 0;
	
	-- 创建游标，查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- 游标结束后，将标记变量改为1  这两个必须声明在一起
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- 开启游标
	OPEN stu_result;
	-- 循环使用游标
	REPEAT
		-- 使用游标，遍历结果,拿到数据
		FETCH stu_result INTO s_score;
		-- 将数据保存到stu_score表中
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test12存储过程
CALL pro_test12();
-- 查询stu_score表
SELECT * FROM stu_score;

存储函数 ​

存储函数和存储过程是非常相似的，存储函数可以做的事情，存储过程也可以做到

存储函数有返回值，存储过程没有返回值（参数的 out 其实也相当于是返回数据了）

  • 创建存储函数

    sql
    DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称(参数 数据类型)
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$

DELIMITER ;

  • 调用存储函数，因为有返回值，所以使用 SELECT 调用

    sql
    SELECT 函数名称(实际参数);

  • 删除存储函数

    sql
    DROP FUNCTION 函数名称;

  • 定义存储函数，获取学生表中成绩大于95分的学生数量

    sql
    DELIMITER $
CREATE FUNCTION fun_test()
RETURN INT
BEGIN
	-- 定义统计变量
	DECLARE result INT;
	-- 查询成绩大于95分的学生数量，给统计变量赋值
	SELECT COUNT(score) INTO result FROM student WHERE score > 95;
	-- 返回统计结果
	SELECT result;
END
DELIMITER ;
-- 调用fun_test存储函数
SELECT fun_test();

触发器 ​

基本介绍 ​

触发器是与表有关的数据库对象，在 insert/update/delete 之前或之后触发并执行触发器中定义的 SQL 语句

  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的
  • 现在触发器还只支持行级触发，不支持语句级触发
触发器类型OLD的含义NEW的含义
INSERT 型触发器无 (因为插入前状态无数据)NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据无 (因为删除后状态无数据)

基本操作 ​

  • 创建触发器

    sql
    DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER  INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;

  • 查看触发器的状态、语法等信息

    sql
    SHOW TRIGGERS;

  • 删除触发器，如果没有指定 schema_name，默认为当前数据库

    sql
    DROP TRIGGER [schema_name.]trigger_name;

触发演示 ​

通过触发器记录账户表的数据变更日志。包含：增加、修改、删除

  • 数据准备

    sql
    -- 创建db9数据库
CREATE DATABASE db9;
-- 使用db9数据库
USE db9;
    sql
    -- 创建账户表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),					-- 姓名
	money DOUBLE						-- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',2000);
    sql
    -- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),				-- 操作类型 (insert update delete)
	operation_time DATETIME,			-- 操作时间
	operation_id INT,					-- 操作表的id
	operation_params VARCHAR(200)       -- 操作参数
);

  • 创建 INSERT 型触发器

    sql
    DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;
    sql
    -- 向account表添加记录
INSERT INTO account VALUES (NULL,'王五',3000);

-- 查询日志表
SELECT * FROM account_log;
/*
id	operation	operation_time		operation_id	operation_params
1	INSERT	   	2021-01-26 19:51:11		3	     插入后{id=3,name=王五money=2000}
*/

  • 创建 UPDATE 型触发器

    sql
    DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;
    sql
    -- 修改account表
UPDATE account SET money=3500 WHERE id=3;

-- 查询日志表
SELECT * FROM account_log;
/*
id	operation	operation_time		operation_id	  operation_params
2	UPDATE	   	2021-01-26 19:58:54		2		 更新前{id=2,name=李四money=1000}
												 更新后{id=2,name=李四money=200}
*/

  • 创建 DELETE 型触发器

    sql
    DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;
    sql
    -- 删除account表数据
DELETE FROM account WHERE id=3;

-- 查询日志表
SELECT * FROM account_log;
/*
id	operation	operation_time		operation_id	operation_params
3	DELETE		2021-01-26 20:02:48		3	    删除前{id=3,name=王五money=2000}
*/

范式 ​

第一范式 ​

建立科学的，规范的数据表就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式

**1NF：**数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性

基本表：

image-20250319234724584

第一范式表：

image-20250319234816785

第二范式 ​

**2NF：**在满足第一范式的基础上，非主属性完全依赖于主码（主关键字、主键），消除非主属性对主码的部分函数依赖。简而言之，表中的每一个字段 （所有列）都完全依赖于主键，记录的唯一性

作用：遵守第二范式减少数据冗余，通过主键区分相同数据。

  1. 函数依赖：A → B，如果通过 A 属性(属性组)的值，可以确定唯一 B 属性的值，则称 B 依赖于 A
    • 学号 → 姓名；(学号，课程名称) → 分数
  2. 完全函数依赖：A → B，如果A是一个属性组，则 B 属性值的确定需要依赖于 A 属性组的所有属性值
    • (学号，课程名称) → 分数
  3. 部分函数依赖：A → B，如果 A 是一个属性组，则 B 属性值的确定只需要依赖于 A 属性组的某些属性值
    • (学号，课程名称) → 姓名
  4. 传递函数依赖：A → B，B → C，如果通过A属性(属性组)的值，可以确定唯一 B 属性的值，在通过 B 属性(属性组)的值，可以确定唯一 C 属性的值，则称 C 传递函数依赖于 A
    • 学号 → 系名，系名 → 系主任
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
    • 该表中的码：(学号，课程名称)
    • 主属性：码属性组中的所有属性
    • 非主属性：除码属性组以外的属性
image-20250319234838513

第三范式 ​

**3NF：**在满足第二范式的基础上，表中的任何属性不依赖于其它非主属性，消除传递依赖。简而言之，非主键都直接依赖于主键，而不是通过其它的键来间接依赖于主键。

作用：可以通过主键 id 区分相同数据，修改数据的时候只需要修改一张表（方便修改），反之需要修改多表。

image-20250319234912807

总结 ​

image-20250319235102890
Q.E.D.
MySQL - 存储引擎与索引
Netty