mysql高级

rose1919
115 阅读7分钟

外键约束

MySQL外键约束是一种用于确保表与表之间数据完整性的关系型约束。它建立在两个表之间，一般是在一个表中定义一个列作为另一个表的主键，那么这个列就称为“外键(column)，”联结两个表的方法通常是使用JOIN操作

CREATE TABLE users ( 
user_id INT PRIMARY KEY, 
user_name VARCHAR(50), 
user_email VARCHAR(50) ); 

CREATE TABLE orders ( 
id INT PRIMARY KEY, 
order_date DATETIME, 
order_id INT, 
FOREIGN KEY (order_id) REFERENCES users(user_id) 
);

在此示例中，orders表中的order_id列是一个外键，引用了users表中的user_id列。这将确保在orders表中插入数据时，order_id列只包含users表中已存在的用户ID。如果您尝试插入orders表中的用户ID不存在于users表中，则MySQL将拒绝此操作并返回错误。

使用ALTER TABLE语句来添加外键约束


ALTER TABLE orders
ADD CONSTRAINT fk_user_id
FOREIGN KEY (order_id) REFERENCES users(user_id);
#附表order参照主表users

删除外键如果不知道 使用show create table xxx查看外键

truncate和delete区别

RUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句
4、不能回滚
语法：truncate table xxx；

any和all用法

any、all关键字必须与一个比较操作符一起使用

any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

举个例子：

select s1 from t1 where s1 > any (select s1 from t2);

假设any后面的s1返回了三个值，那其实就等价于

select s1 from t1 where s1 > result1 or s1 > result2 or s2 > result3

即s1大于子查询中的任意一个，等同于s1大于子查询的最小值即可

而all的用法相当于把上述语句的‘or’缓冲‘and’

select s1 from t1 where s1 > result1 and s1 > result2 and s2 > result3

即a大于子查询中的每一个，等同于a大于子查询的最大值。

逻辑架构

1.逻辑架构剖析

1.1服务器处理客户端请求

image.png

image.png

1.2第一层：连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
(1)用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
(2)用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后 面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

1.3第二层：服务层

(1)SQL Interface: SQL接口
·接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface
·MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定 义函数等多种SQ语言接口

(2)Parser: 解析器
·在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构 传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
·在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ，并根据数据字 典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还 会对SQl查询进行语法上的优化，进行查询重写。

(3)Optimizer: 查询优化器
·SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划 。
·这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连 接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将 查询结果返回给用户。
·它使用“ 选取-投影-连接 ”策略进行查询。例如：

SELECT id,name FROM student WHERE gender = '女';

这个SELECT查询先根据WHERE语句进行 选取 ，而不是将表全部查询出来以后再进行gender过 滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过 滤，将这两个查询条件 连接 起来生成最终查询结果。

(4)Caches & Buffers： ·查询缓存组件 MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结 果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过 程了，直接将结果反馈给客户端。
·这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。 这个查询缓存可以在 不同客户端之间共享 。 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

tips:
如果我问你9+8×16-3×2×17的值是多少，你可能会用计算器去算一下，最终结果35。如果再问你一遍9+8×16- 3×2×17的值是多少，你还用再傻呵呵的再算一遍吗？我们刚刚已经算过了，直接说答案就好了

1.4第三层：引擎层
插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别 维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样 我们可以根据自己的实际需要进行选取。

MySQL 8.0.25默认支持的存储引擎如下：

image.png

1.4存储层
所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存 在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设 备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。

1.5总结

我们可以简化如下： image.png

简化为三层结构：

  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
  2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
avatar
文章
阅读
粉丝