MySQL SQL

予早 2025-09-06 14:38:54
Categories： Tags：

https://dev.mysql.com/doc/refman/8.4/en/language-structure.html

https://dev.mysql.com/doc/refman/8.4/en/charset.html

https://dev.mysql.com/doc/refman/8.4/en/data-types.html

https://dev.mysql.com/doc/refman/8.4/en/functions.html

https://dev.mysql.com/doc/refman/8.4/en/sql-statements.html

语法

引号

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

mysql中引号的用法（反引号``，单引号''，双引号""）
单引号：我们在例子中的条件值周围使用的是单引号。SQL 使用单引号来环绕文本值。如果是数值，请不要使用引号。

反引号：它是为了区分MYSQL的保留字与普通字符而引入的符号。

注意划重点：有MYSQL保留字作为字段的，必须加上反引号来区分！！！

所谓的保留字就是select database insert 这一类数据库的sql指令，当我们不得已要拿他们来做表名和字段名的时候 我们必须要加反引号来避免编译器把这部分认为是保留字而产生错误。

当然，在上面的例子中，book并不是保留字，这么加反引号只是作一个保险，这也是一个良好的sql建表习惯。

务必要记住：保留字既不能作为表名，也不能作为字段名，如果非要这么操作，请记住要增加反引号！
双引号的用法和单引号有所类似，大多数数据库都支持单引号和双引号的互换，即varchar类型的变量既可以用单引号来囊括，也可以用双引号。

当然了，一边单引号，一边双引号是不被允许的。

编解码

https://dev.mysql.com/doc/refman/8.4/en/charset.html

mysql collate，https://www.cnblogs.com/qcloud1001/p/10033364.html

数据类型

数据类型

数值类型

分为精确数字类型和近似数据类型。

精确数字类型：TINYINT、SMALLINT、MEDIUMINT、INTEGER、BIGINT

近似数据类型：FLOAT、DOUBLE、DECIMAL

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

[!TIP]

  1. 能用无符号就用无符号数字，既增加约束又增加有效范围。
  2. 枚举状态或分类（例如，1-待处理，2-成功，3-失败）优先选用无符号 TINYINT，组合状态或分类（例如，11213141，表示11类下的21子类下的31子类下的41子类）按需使用无符号 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
  3. 年份数据优先选用SMALLINT。
  4. 主键ID优先选用 BIGINT，避免自增ID用尽或者足够长以兼容分布式ID。
  5. 财务数据优先选用 DECIMAL，或者使用BIGINT和固定倍率常量（分、毫、厘）将数据扩大为整数。

mysql tinyint

Mysql tinyint(1)与tinyint(4)的区别

 CREATE TABLE `pre_demo` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
   `unsigned_t` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
   `signed_t` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
   `t1` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
   `t2` tinyint(2) unsigned zerofill NOT NULL DEFAULT '00',
   `t3` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
   `t4` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
   `t5` tinyint(5) unsigned zerofill NOT NULL DEFAULT '00000',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 INSERT INTO pre_demo VALUES(NULL,8,8,8,8,8,8,8);
 INSERT INTO pre_demo VALUES(NULL,123,123,123,123,123,123,123);
 
 tinyint占一个字节
 tinyint(1) 与 tinyint(4) 能够存储的数据范围是一样的，都是 0 到 255（无符号的）。区别在于，当使用 zerofill 时，查询结果显示的长度可能不同。
 zerofill 和 指定长度配合使用，可用于统一数据的显示长度，比如在数据库层面快速生成统一长度的流水号。
 一般情况下，无须刻意指定整型数据类型的长度。使用 MySQL 的默认长度tinyint(4) 即可。

日期时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

时间日期类型

DATE、TIME、DATETIME、TIMESTAMP、YEAR

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 不携带时区信息，无时区转换
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 不携带时区信息，有时区转换，时间以会话时区被转换为UTC时区时间存储，查询时自动转为会话时区时间

[!TIP]

  • DATE：存储日期数据（例如，生日）
  • TIME：存储时间数据（例如，视频时长、工单处理耗时、任务执行时长），但不推荐使用该类型，而是使用整数+应用层格式化替代。
  • YEAR：存储年份数据（例如，报表年份），但不推荐使用该类型，而是用 SMALLINT 代替。
  • DATETIME：存储日期时间数据，不携带时区信息
  • TIMESTAMP：存储时间戳数据，不携带时区信息，但会有时区转换，但不推荐使用该类型，而是使用 DATETIME + 应用层 UTC 格式化替代

各时间类型转换

DATE
DATETIME
TIMESTAMP

SELECT FROM_UNIXTIME(1447430881);
SELECT FROM_UNIXTIME(1447430881) + 0;
SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');

NOW([fsp])

返回当前日期和时间，该值以会话时区表示。在不同计算上下文中以不同格式返回，如果给定fsp参数以指定从0到6的小数秒精度，则返回值包括该数字中的小数秒部分：

STR_TO_DATE(str,format)

DATE_FORMAT()函数的逆函数，参数为一个代表时间的字符串str，以及一个格式化字符串。该函数返回值取决于时间字符串str中包含的具体内容：

服务器扫描时间字符串str，试图将格式与之匹配。格式字符串可以包含以%开头的文字字符和格式说明符。格式中的文字字符必须与字符串中的文字匹配。格式中的格式说明符必须与字符串的日期或时间部分匹配。

UNIX_TIMESTAMP([date])

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');

字符串类型

CHAR、VARCHAR

TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

[!TIP]

  • CHAR：适用于存储定长字符串（例如，MD5），或者长度相对均衡且需频繁查询的字符串（例如，国际手机区号）。
  • VARCHAR：适用于存储变长字符串（例如，产品名称、邮箱），
  • TINYTEXT：不使用该类型，使用 VARCHAR 代替。
  • TEXT：存储短文章（例如，电子邮件正文、新闻正文）
  • MEDIUMTEXT：存储长文章（例如，专业论文）
  • LONGTEXT：存储书籍（）

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

二进制大对象(BLOBS BinaryLargeOBjects)

JSON

CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

返回指定字段

select id,json_extract(json_value,'$.deptName') as deptName from dept;

包含某个对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

SELECT JSON_KEYS(json_value) FROM dept

https://blog.csdn.net/asd529735325/article/details/107205214

json字段上的索引

https://blog.csdn.net/BREATH57/article/details/129320951

运算符

函数

函数

聚合函数

COUNT，SUM，AVG，MIN，MAX

注意：上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值，聚合函数不能出现在WHERE子句中

COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

整除

5 div 2 = 2

取余

5 mod 2 = 1

四舍五入

round(1.5) = 2

窗口函数

https://blog.csdn.net/master_chenchen/article/details/140029827

数值函数

日期和时间函数

流程控制函数

SQL 语句

SQL，Structured Query Language

SQL 国际标准

标准版本 发布年份 里程碑定位 主要内容
SQL-86 1986 首个国际标准 • 基本 DML（SELECT / INSERT / UPDATE / DELETE）
SQL-89 1989 小步完善 • ORDER BY、GROUP BY 标准化
SQL-92 1992 “SQL-2”经典基线 • 四级别合规（Entry / Transitional / Intermediate / Full）
SQL:1999 1999 OLAP 与对象关系元年 • 公共表表达式 WITH [RECURSIVE]（MySQL 8.0 支持）
SQL:2003 2003 窗口函数 & XML 时代 • **窗口函数 OVER (PARTITION BY … ORDER BY …)**（MySQL 8.0 全套支持）
SQL:2006 2006 XML 强化 • XQuery 1.0 / XPath 2.0 深度集成
SQL:2008 2008 分析增强 • 增强 MERGE（支持 DELETE 分支）
SQL:2011 2011 时态数据 • 系统版本表（PERIOD FOR SYSTEM_TIME）
SQL:2016 2016 JSON & 行模式识别 • JSON 数据类型与 SQL/JSON 路径函数（MySQL 8.0 支持 JSON_TABLE、JSON_VALUE 等）
SQL:2019 2019 多维数组 & 图 • SQL/MDA 多维数组类型
SQL:2023 2023 最新基线 • SQL/PGQ 正式版（图查询语法）

[!NOTE]

MySQL 并非直接对标某版本规范，而是”努力与 SQL 标准兼容，但保留自有扩展“，

MySQL 从来没有 100 % 通过某个版本的 ANSI/ISO SQL 标准认证，官方文档的表述一直是 “继续努力与 SQL 标准兼容，但保留自己的扩展”。换句话说，它属于“以 SQL-92/SQL:1999 为基底，再叠加大量自有语法和特性”的实现。

MySQL 8.0 ≈ SQL-92 全兼容 + SQL:1999~SQL:2003 大部分特性 + SQL:2016 少量 JSON 扩展

SQL，Structured Query Language

数据定义语言DDL（Data Definition Language），CREATE,DROP,ALTER，其中包括表结构，视图和索引，操作数据库

数据查询语言DQL（Data Query Language）SELECT，以select关键字。各种简单查询，连接查询等 都属于DQL，查询数据

数据操纵语言DML（Data Manipulation Language）INSERT,UPDATE,DELETE，修改数据

数据控制语言DCL（Data Control Language）GRANT,REVOKE,COMMIT,ROLLBACK，安全控制

事务控制语言 TCL（Transaction Control Language） COMMIT, ROLLBACK, SAVEPOINT

MySQL 允许 SQL 最大长度

https://blog.csdn.net/s3088529551/article/details/120531194

# 默认为 67108864 字节，即 64 MB
SHOW VARIABLES LIKE 'max_allowed_packet';

in并不会限制元素多少，受限制的是packet的大小

select * from user_info where id in (1, 2, 3, ..., 1000000);

注释

注释

单行注释

多行注释

MySQL中的注释有三种：

  1. # 注释内容
  2. – 注释内容
  3. /* 注释内容*/

另外，在导出的SQL文件中，也会看到类似如下内容的注释：

1 CREATE DATABASE `blog` /*!40100 DEFAULT CHARACTER SET latin1 */;

其中的 /!40100 …/ 这部分注释会被MySQL执行，表示服务端版本号大于4.1.00时会被执行

8.29 21.11.23

数据库管理语句

SHOW 语句

-- 显示所有数据库
SHOW DATABASES;
-- 显示所有表名
SHOW TABLES;
-- 显示所有支持的数据库引擎
SHOW ENGINES;

show [session | global] variables;
show [session | global] variables like '...';

SET 语句

为变量赋值

MySQL 中有三种变量，分别为系统变量、用户自定义变量、局部变量。

系统变量

系统变量为 MySQL 预定义变量，只可通过 SET 语句修改值。对系统变量的读写有两个作用范围，分别为 session 和 global，不指定生效范围时默认为 session 范围：

-- 查看
show [session | global] variables;
show [session | global] variables like '...';

-- 赋值
set [session | global] 系统变量名 = 值;
set @@[session | global] 系统变量名 = 值;


set session autocommit = 1;
select @@global.autocommit;

用户自定义变量

用户自定义变量无需声明并由用户使用 SET 直接赋值，其仅在用户 session 中生效。

--  用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL

-- 赋值
set @var_name = 'xxx';
select @var_name = 'xxx';
select 字段名 into @var_name from 表名; 

set @mygender := '男',@myhobby := 'java';

-- 使用
select @var_name;

局部变量

局部变量是在存储过程等中由 declare 声明的变量，可通过 SET 语句修改值，其仅在所属存储过程中生效。

-- 声明
-- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
declare 变量名 变量类型 [default ...]

-- 赋值
set 变量名 = 值;
select 字段名 into 变量名 from 表名; 


-- 示例
create procedure p2() 
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student; 
    select stu_count; 
end; 

call p2();

DDL

参数

IN、OUT、INOUT

IN，输入参数，默认

OUT，输出参数

INOUT，输入输出参数

CREATE PROCEDURE 存储过程名称([ IN/OUT/INOUT 参数名 参数类型 ]) 
BEGIN
    -- SQL语句 
END;


create procedure p4(in score int, out result varchar(10)) 
begin
    if score >= 85 then 
        set result := '优秀'; 
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格'; 
    end if; 
end; 
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明 
call p4(18, @result); 
select @result;

DML

SELECT 语句

单表查询

create table employee(
    name varchar(20),
    age number(4),
    city varchar(20)
);

insert into employee(name,age,city) values(‘zhangsan’,30,’beijing’);
drop table employee;
update employee set age=50 where name=‘zhangsan’;
delete from employee where name=‘zhangsan’;


select distinct age from employee;
select name，age，city from employee where name like ‘B%’; # 在employee表选择name以B开头的列name、age、city
select max(salary),dept from employee group by dept;
select dept,avg(salary) from employee group by dept having avg(salary)>2000;
select name,age,city,salary from employee where dept=‘sales’ order by salary ASC; # ASC 升序（默认）、DESC 降序
select name,age from employee where city in(‘beijing’,’chengdou’);
select name,city from employee where age between 30 and 40;
select round(salary),name from employee;

多表查询

交叉连接，CROSS JOIN

1、 隐式交叉连接（没有CROSS JOIN）

SELECT * FROM 表1,表2

结果为笛卡尔乘积

2、显式交叉连接

SELECT * FROM 表1 CROSS JOIN 表2

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

如果写条件ON / WHERE 结果和内连接一样

内连接，INNER JOIN

SELECT * FROM 表1 INNER JOIN 表2 ON 条件

SELECT * FROM 表1 INNER JOIN 表2 WHERE 条件

典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集部分

不写条件会造成查询笛卡尔乘积

三表连接

 **SELECT** **S.studentName AS 姓名,SU.subjectName AS 课程,R.studentResult AS 成绩**

 **FROM** **student AS S**

 **INNER JOIN** **`result` AS R** **ON** **(S.`studentNo` = R.`studentNo`)**

 **INNER JOIN** **`subject` AS SU** **ON** **(SU.subjectNo=R.subjectNo);**

外连接，OUTER JOIN

LEFT JOIN、RIGHT JOIN、FULL JOIN

1、SELECT * FROM 表1 LEFT JOIN 表2 ON 条件

左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2、SELECT * FROM 表1 RIGHT JOIN 表2 ON 条件

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

3、SELECT * FROM 表1 FULL JOIN 表2 ON 条件

完全外连接（FULL OUTER JOIN），但MySQL不支持这种形式。

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。

三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
说明：左表就是在“（LEFT JOIN）”关键字左边的表。右表当然就是右边的了。

SELECT 语句子句执行顺序

SELECT 从 FROM 开始执行，每一步执行完毕生成一个虚拟表作为下一步的输入。

1.  FROM <left_table> 
2.  <join_type> JOIN <right_table> ON <join_condition>
3.  WHERE <where_condition>
4.  GROUP BY <group_by_list>
5.  WITH {CUBE | ROLLUP}
6.  HAVING <having_condition>
7.  SELECT
8.  DISTINCT
9.  ORDER BY <order_by_list>
10. LIMIT OFFSET
  1. 指定查询的表，若有多个表会生成笛卡尔积，此时生成虚拟表vt1
  2. 根据 ON 条件 JOIN 表，此时生成虚拟表vt2（若有多个JOIN则重复本步骤，生成新的虚拟表vt2）
  3. WHERE 筛选数据，此时生成虚拟表vt3
  4. group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。
  5. 应用cube或者rollup选项，为vt5生成超组，生成vt6.
  6. 应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。
  7. 处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.
  8. 应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。
  9. 应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。
  10. 应用top选项。此时才返回结果给请求者即用户。

WHERE和HAVING有什么区别？

参考答案

WHERE是一个约束声明，使用WHERE约束来自数据库的数据，WHERE是在结果返回之前起作用的，WHERE中不能使用聚合函数。（聚合函数使用的前提是结果集返回）

HAVING是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在HAVING中可以使用聚合函数。另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。

从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

select * from table_name where 1 = 1

where 1=1用于简化动态条件拼接。

不使用where 1 = 1简化：

sql = "select * from table"

query = ""

if age.strip():  # age 可以用 age_text 变量
    query += f"Age='{age}'"

if address.strip():  # address 可以用 address_text 变量
    if query:  # 不是第一个条件就加 and
        query += " and "
    query += f"Address='{address}'"

if query:  # 有额外条件就拼到 where 后
    query += " where " + query

print(query)

使用where 1 = 1简化：

sql = "select * from table where 1 = 1"

query = ""

if age.strip():  # age 可以用 age_text 变量
    query += f"and Age='{age}'"

if address.strip():  # address 可以用 address_text 变量
    query += f"and Address='{address}'"

print(query)

select最后一个字段后不能有逗号

select a, b, c, from table

元组比较

select * from tb where (id, name) in (select id, name from tb2 where xxx in ('1', '2', '3'))

tb1别名使用后原有名字table1不能使用

select tb1.id, table1.name from table1 as tb1

联表查询两种限制条件方式

SELECT
    t.id,t.name,a.project_id
FROM
    micrisevice AS t
    LEFT JOIN project_mirservie AS a ON a.micrservice_id = t.id 
    WHERE t.STATUS = 0 AND a.project_id = 4 OR a.id is NULL

SELECT
    t.id,t.name,a.project_id
FROM
    micrisevice AS t
    LEFT JOIN project_mirservie AS a ON a.micrservice_id = t.id 
    AND a.project_id = 4 
    OR a.id IS NULL 
WHERE
    t.STATUS = 0

在MySQL中，DUAL 是一个特殊的虚拟表，它不存储任何数据，但可以用来执行一些不需要访问实际数据表的操作。DUAL 通常用于返回一个单一的值，比如使用 SELECT 语句与 NOW() 或 VERSION() 函数来获取当前的日期和时间或MySQL服务器的版本。在早期的SQL标准中，所有的SELECT语句都需要从一个表中选择数据。因此，为了执行不需要从表中选择数据的操作，MySQL引入了DUAL虚拟表。不过，从SQL:2003标准开始，已经不再需要FROM DUAL。

select VERSION();
select VERSION() from DUAL;

UNION

联合查询，union

select 字段列表 from 表A union select 字段列表 from 表B;

MySQL UNION 操作符用于连接两个以上的 SELECT 语句

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

复合结构

BEGIN … END

[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN … END 语法用于编写复合语句，这类语句可出现在存储程序（存储过程和函数、触发器以及事件）中。复合语句可以包含多条语句，这些语句由 BEGIN 和 END 关键字包裹。statement_list 表示一条或多条语句的列表，每条语句都以分号（;）作为语句分隔符结尾。statement_list

DECLARE

DECLARE 用户声明存储程序中的

流程控制语句

if

if 条件1 then
    ...
elseif 条件2 then
    ...
else
    ...
end if;
-- 在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有

case

两种语法

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时， 执行statement_list2， 否则就执行 statement_list 
CASE case_value 
    WHEN when_value1 THEN statement_list1 
    [ WHEN when_value2 THEN statement_list2] 
    ... 
    [ ELSE statement_list ]
END CASE;

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成 立时，执行statement_list2， 否则就执行 statement_list 
CASE
    WHEN search_condition1 THEN statement_list1 
    [WHEN search_condition2 THEN statement_list2] 
    ... 
    [ELSE statement_list]
END CASE;

while

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑 
WHILE 条件 DO 
    SQL逻辑... 
END WHILE;

repeat

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环 
-- do while
REPEAT
    SQL逻辑... 
    UNTIL 条件 
END REPEAT;

loop

[begin_label:] LOOP 
    SQL逻辑... 
END LOOP [end_label];

leave、iterate

LEAVE label; -- 退出指定标记的循环体，break
ITERATE label; -- 直接进入下一次循环，continue

fi

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

case when

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

MySQL ifnull()函数

游标

游标是一种特殊的数据类型，游标变量是一种特殊的局部变量

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
-- 打开游标
OPEN 游标名称 ;
-- 获取记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
-- 关闭游标
CLOSE 游标名称 ;

事务

-- 显式开启事务
start transaction; -- begin; 等价
-- 提交
commit;
-- 回滚
rollback;

-- 查看事务隔离级别
select @@transaction_isolation;

SQL 语句执行全流程

我们知道MySQL其实是分成server层和存储引擎层两部分，每当执行一个查询时，server层负责生成执行计划，即选取即将使用的索引以及对应的扫描区间。我们这里以InnoDB为例，针对每一个扫描区间，都会：

[!IMPORTANT]

MySQL 5.6 及以前查询缓存机制默认启用，5.7 中查询缓存机制默认禁用，8.x 及以后查询缓存机制被移除。

  1. 客户端与 Server 端连接管理器建立连接
  2. 解析器对 SQL 进行词法分析、语法分析，生成解析树
  3. 优化器对 SQL 进行优化，生成执行计划
  4. 执行器调用存储引擎层 API 执行 SQL
    1. 通过二级索引或者直接根据聚簇索引定位记录
    2. 对记录加锁，行锁、意向锁
    3. 将记录原数据写入 undo log
    4. 在 buffer pool 中修改页数据
    5. 生成 redo log
  5. Binary Log Group Commit，Server 层
    1. 将事务产生的逻辑事件写入 binlog cache
    2. flush：把各线程 binlog cache 写入 binlog 文件 buffer，并给每个事务分配 逻辑序号 logical timestamp
    3. sync：根据 sync_binlog 策略调用 fsync() 把 binlog 文件 buffer 刷盘
    4. commit：通知存储引擎层提交事务
  6. 引擎层提交事务
    1. 把事务在 redo log buffer 中的内容 按 LSN 顺序 追加到 log buffer 尾部
    2. 如果 innodb_flush_log_at_trx_commit = 1，立即调用 log_write_up_to() 把 buffer fsync 到 redo log 文件
    3. 把事务状态改为 COMMITTED，并记录 binlog filename/position 到系统表空间事务系统段（trx_sys）
    4. 释放行锁、清理 read view 等
  7. 后台异步程序
    1. 把 Buffer Pool 中已修改的页刷盘
    2. 更新 checkpoint LSN —— 保证崩溃恢复时 redo 日志不会过长

Undo 先于 Redo（内存），Redo 先于 Binlog（落盘顺序：Binlog fsync → Redo fsync）。

工具语句

EXPLAIN

EXPLAIN

https://dev.mysql.com/doc/refman/8.4/en/explain-output.html

EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息，即执行计划。EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。

EXPLAIN会为SELECT语句中使用的每个表返回一行信息。它在输出中按照MySQL处理该语句时读取表的顺序列出这些表。这意味着MySQL从第一个表中读取一行，然后在第二个表中找到匹配的行，接着在第三个表中寻找，以此类推。当所有表都处理完毕后，MySQL会输出选定的列，并回溯表列表，直到找到一个有更多匹配行的表。然后从该表中读取下一行，并继续处理下一个表。

EXPLAIN 输出列

select
  t2.`id`,
  t2.`real_name`
from
  `tb_sys_user_tenant` t1
  join `tb_sys_user` t2 on t1.`user_id` = t2.`id`
where
  t1.`tenant_id` in (14, 17, 44, 81, 102, 114, 119, 178)
  and t1.`is_delete` = 0
  and t2.`is_delete` = 0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 11 10.00 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 gatherone_advertiser.t1.user_id 1 10.00 Using where
Column 列 JSON Name JSON 名称 Meaning 意义
id select_id SELECT 标识符
select_type None SELECT 类型
table table_name 输出行的表
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys possible_keys 可能选择的索引
key key 实际选择的索引
key_len key_length 所选键的长度
ref ref 列与索引的比较。ref列显示为了从表中选择行，哪些列或常量会与key列中命名的索引进行比较。
rows rows 待检查行的估计数量。行 rows列表示MySQL认为执行查询必须检查的行数。
filtered filtered 按表条件筛选的行百分比。过滤列表示表条件过滤的表行的估计百分比。最大值为100，这意味着没有发生行过滤。从100开始递减的值表示过滤量增加。行显示检查的估计行数，行×过滤显示与下表连接的行数。例如，如果行为1000，过滤为50.00（50%），则要与下表连接的行数为1000×50%=500。
Extra None 附加信息

[!NOTE]

JSON 特性中为 NULL 的部分不会显示在 JSON 格式的 EXPLAIN 输出中。

Join Type，连接类型

最佳类型到最差类型：

最左前缀匹配原则是指SQL中用到的字段可以匹配某个索引的最左前缀，SQL中字段顺序任意。

explain select * from staff where leader = 2326 and did = 4106;

EXPLAIN Extra 信息

using filesort

https://dev.mysql.com/doc/refman/8.4/en/order-by-optimization.html

1. 核心含义

Using filesort 是 MySQL 执行计划（EXPLAIN）中 Extra 列 的关键提示，对应的 JSON 格式属性为 using_filesort，表示 MySQL 无法直接通过索引实现排序，必须额外执行一次 “文件排序” 操作才能按需求返回有序结果。

2. 触发逻辑（为什么会出现）

当查询包含 ORDER BY（或隐式需要排序的逻辑，如 GROUP BY 需排序分组），但 MySQL 无法利用索引的有序性直接获取结果时，就会触发该操作，具体步骤如下：

  1. 筛选行：先根据查询的 JOIN 类型和 WHERE 条件，筛选出所有符合条件的行；
  2. 暂存排序信息：为这些符合条件的行，存储 “排序键”（即 ORDER BY 指定的列值）和 “行指针”（指向原表中该行的位置）；
  3. 额外排序：对暂存的 “排序键” 进行排序；
  4. 按序取行：根据排序后的 “排序键” 和 “行指针”，从原表中按顺序取出最终结果。

3. 关键影响

4. 示例场景

比如查询 SELECT name FROM user WHERE age > 20 ORDER BY create_time;：

using index

这段内容核心围绕 MySQL 中的覆盖索引（Covering Index） 机制展开，是查询性能优化的关键概念，以下从核心逻辑、特殊场景、查看方式三方面简要解析：

1. 核心：覆盖索引的定义与适用场景

2. 特殊场景：InnoDB 的聚集索引例外

3. 如何查看：覆盖索引的 EXPLAIN 格式支持

无论使用哪种 EXPLAIN 格式，都能看到查询是否使用了覆盖索引：

Using index condition

https://dev.mysql.com/doc/refman/8.4/en/index-condition-pushdown-optimization.html

核心是联合索引中间部分有范围匹配会使得后续部分无法基于索引查询

这段内容核心解析的是 MySQL 中的 “索引条件下推（Index Condition Pushdown, ICP）” 优化机制，本质是通过 “提前用索引过滤数据” 减少不必要的全表行读取，提升查询效率，具体可拆解为 3 个关键逻辑：

1. 核心动作：“先查索引、再判是否读全表”

2. 核心价值：“延迟读全表，减少 IO 开销”

全表行的读取需要访问磁盘（或内存中的完整表数据块），属于 “高开销操作”；而索引元组通常体积更小、访问更快。
ICP 通过 “用索引先过滤”，把原本需要读取的全表行数大幅减少 —— 比如查询 WHERE age > 30 AND name LIKE '张%'，若索引包含 age 和 name，ICP 会先在索引层筛选出 “age>30 且 name 以张开头” 的记录，只读取这些记录的全表行，避免无效的全表行 IO。

3. 适用场景与本质

Using index for skip scan

Using index for skip scan（使用索引进行跳过扫描）解析

“Using index for skip scan” 是 MySQL 的EXPLAIN输出中 “Extra” 列的一个值（对应 JSON 格式输出的using_index_for_skip_scan属性），核心作用是标识查询执行时采用了 “跳过扫描（Skip Scan）” 这一索引访问方法，以更高效地从索引中获取目标数据。

核心逻辑：“跳过” 部分索引键，减少扫描范围

跳过扫描（Skip Scan）是 MySQL 针对复合索引的一种优化访问策略，适用于查询需要按复合索引中 “非前缀列” 过滤数据的场景。
通常情况下，复合索引需遵循 “最左前缀原则”—— 只有使用索引的前 N 个列（前缀）过滤时，才能高效命中索引；而跳过扫描打破了这一限制：它会先定位到复合索引中某一 “目标列” 的不同取值，跳过该列相同取值对应的其他前缀列数据，直接扫描目标列对应的数据范围，从而减少不必要的索引扫描量，提升查询效率。

适用场景（本质）

当查询满足以下条件时，MySQL 可能触发 “跳过扫描”：

  1. 使用复合索引，且查询过滤 / 排序依赖索引中的 “非最左前缀列”（例如复合索引为(a, b, c)，查询需按b或c过滤）；
  2. 索引中 “目标列” 的不同取值数量较少（即基数低），此时 “跳过相同取值” 的收益更高；
  3. 查询无需访问表的实际数据（仅通过索引即可获取所需列，本质也是一种 “覆盖索引” 的延伸场景）。

价值：平衡复合索引的灵活性与效率

复合索引的 “最左前缀原则” 虽高效，但限制了非前缀列的使用场景；而 “跳过扫描” 通过优化索引扫描逻辑，让非前缀列也能借助复合索引高效查询，既避免了为非前缀列单独建索引的开销，又提升了此类查询的执行速度。

场景

假设某张商品表 products 包含字段 category（商品分类）、brand（品牌）、price（价格），且表上创建了复合索引 idx_category_brand_price (category, brand, price)。现在需要查询 “所有分类下，品牌为‘A’且价格大于 100 的商品”，对应的 SQL 语句为：
SELECT category, brand, price FROM products WHERE brand = 'A' AND price > 100;

由于查询的过滤条件 brand 是复合索引的非最左前缀列（索引首列是 category），若 MySQL 采用常规索引扫描，需遍历整个索引才能筛选出 brand = 'A' 的记录；而当触发 “跳过扫描（Skip Scan）” 时，优化器会先识别出索引中 category 的不同取值（如 “家电”“服装”“食品” 等），然后针对每个 category 取值，直接 “跳过” 该分类下 brand 不等于 ‘A’ 的索引数据，仅扫描该分类下 brand = 'A' 且 price > 100 的索引元组。

此时执行 EXPLAIN 分析该查询，其 Extra 列会显示 Using index for skip scan（JSON 格式输出中 using_index_for_skip_scan 属性为 true），表明查询通过跳过扫描访问方法，利用复合索引 idx_category_brand_price 高效定位到目标数据，无需遍历完整索引或回表读取数据（因查询列 category、brand、price 均在索引中，属于覆盖索引场景）。

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)

https://dev.mysql.com/doc/refman/8.4/en/hash-joins.html

Using join buffer 系列（含 Block Nested Loop、Batched Key Access、hash join）是 MySQL 执行 多表连接 时的优化策略标识，核心逻辑是：将 “前序表”（已处理的表）的部分数据暂存到 “连接缓冲区”（join buffer），再用缓冲区中的数据与 “当前表” 匹配连接，避免频繁直接访问磁盘表，减少 I/O 开销。

其中，括号内的不同关键词对应具体的连接算法，JSON 格式输出中统一通过 using_join_buffer 属性显示算法类型（只能是三者之一）。

三种具体算法差异

三种算法的核心区别在于 “缓冲区数据与当前表匹配的方式”，适配不同场景：

算法类型 核心逻辑 适用场景
Block Nested Loop（BNL） 块嵌套环（BNL 把前序表数据分批存入缓冲区，每批数据与当前表的所有行逐行比对（嵌套循环）。 无合适索引、表数据量较小的连接场景；避免全表扫描时频繁读取前序表磁盘数据。
Batched Key Access（BKA） 批量密钥访问（BKA） 缓冲区暂存前序表的 “索引键”，批量用这些键到当前表的索引中查找匹配行（批量查索引）。 当前表有可用索引，且需减少索引查找的次数（批量操作比单次查找更高效）。
hash join（哈希连接） 散 列 用前序表数据在缓冲区构建 “哈希表”，再扫描当前表，用当前表数据匹配哈希表。 大表连接场景，尤其当两表均无合适索引时，哈希匹配比嵌套循环更快。

关键补充说明

  1. 缓冲区作用：join buffer 是内存区域，暂存前序表数据后，无需反复从磁盘读取前序表，仅需用缓冲区数据与当前表交互，降低磁盘 I/O。
  2. EXPLAIN 中的位置意义：该标识出现在某张表的 Extra 列中，说明 “当前表” 正在使用前序表（EXPLAIN 输出中该行的上一行表）的缓冲区数据进行连接。
  3. 并非 “越优越好”：出现该标识通常意味着连接未完全依赖索引（若有完美索引，可能无需缓冲区），需结合业务判断是否需优化索引（如给当前表加合适的连接索引，减少缓冲区依赖）。

Using MRR

https://dev.mysql.com/doc/refman/8.4/en/mrr-optimization.html

Using MRR（多范围读取优化）解析

1. 核心含义

Using MRR 是 MySQL EXPLAIN 输出中 Extra 列的一种取值，代表查询执行时采用了 Multi-Range Read（多范围读取）优化策略，目的是通过优化数据读取顺序，减少磁盘 I/O 开销，提升查询效率。在 JSON 格式的 EXPLAIN 输出中，该信息会包含在 message 属性里。

2. 优化逻辑（为什么需要 MRR）

MySQL 中，索引查询（如范围查询、多值匹配）可能会先通过索引获取到一批 行标识符（如 InnoDB 的主键 ID），再根据这些标识符去读取实际的表数据（即 “回表”）。

3. 适用场景

通常在以下查询场景中会触发 MRR 优化：

4. 关键价值

MRR 的核心作用是 将 “随机 I/O” 转化为 “顺序 I/O”—— 磁盘对顺序数据的读取效率远高于随机读取，因此能显著优化需要大量回表的查询性能，尤其在数据量较大、磁盘 I/O 成为瓶颈时效果更明显。

Using sort_union(…), Using union(…), Using intersect(…)

https://dev.mysql.com/doc/refman/8.4/en/index-merge-optimization.html

这三个值均属于 MySQL 中 Index Merge（索引合并）优化 的具体实现算法，仅在 EXPLAIN 输出的 join type 为 index_merge 时出现，核心作用是通过合并多个单列索引的扫描结果，提升查询效率（避免全表扫描或依赖低效的复合索引），具体解析如下：

1. 核心共性

三者均针对 单个表上的多个单列索引 场景：当查询的 WHERE 条件中包含对同一表多个列的过滤（如 WHERE col1 = 1 OR col2 = 2 或 WHERE col1 = 1 AND col2 = 2），且每个列都有独立索引时，MySQL 会选择 “合并多个索引的扫描结果” 来获取数据，而非仅用一个索引或全表扫描。

2. 各算法差异

（1）Using union(…) （1）使用联合

（2）Using intersect(…) （2）使用 interse

（3）Using sort_union(…)

3. 核心价值与注意事项

Using temporary

“Using temporary（使用临时表）” 解析

在 MySQL 的 EXPLAIN 分析结果中，“Using temporary” 是 Extra 列的关键提示，对应的 JSON 格式输出属性为 using_temporary_table（值为 true 时表示触发），核心含义是：MySQL 为了执行当前查询，必须创建一张临时表来暂存中间结果，才能最终返回目标数据。

1. 核心触发场景（最典型情况）

最常见的触发原因是 查询中 GROUP BY 和 ORDER BY 子句指定的列不一致。
例如：

-- GROUP BY 按“用户ID”分组，ORDER BY 按“订单金额”排序
SELECT user_id, SUM(order_amount) 
FROM orders 
GROUP BY user_id 
ORDER BY order_amount;

此时 MySQL 需先通过 GROUP BY user_id 计算每个用户的订单总金额（结果暂存），再根据 order_amount 排序 —— 由于 “分组列” 和 “排序列” 不同，无法直接通过分组结果排序，必须创建临时表存储分组后的中间数据，再对临时表执行排序操作。

2. 其他常见触发场景

除了 GROUP BY 与 ORDER BY 列不一致，以下情况也可能触发 “Using temporary”：

3. 对查询性能的影响

临时表的创建和销毁会消耗额外的 CPU、内存和磁盘 I/O（若临时表数据量大，会从内存写入磁盘），通常会导致查询变慢，是性能优化中需重点规避的情况之一（与 “Using filesort” 并称两大常见性能痛点）。

4. 优化方向

核心思路是避免 MySQL 依赖临时表暂存数据，常见手段：

Using where

这段内容围绕 MySQL 中 EXPLAIN 输出里 Using where 相关信息展开，核心是解释其含义、查询优化提示及 JSON 格式下的对应表现，具体解析如下：

1. “Using where” 的核心含义 1.“使用

Using where 是 EXPLAIN 输出中 Extra 列的常见值，本质是说明 MySQL 正在使用 WHERE 子句过滤数据，作用有两个：

2. 关键优化提示：何时需警惕查询问题

当 EXPLAIN 结果同时满足以下两个条件时，可能意味着查询存在优化空间（除非你明确需要读取表中所有行）：

这说明 MySQL 在扫描全表 / 全索引后，没有通过 WHERE 子句过滤数据 —— 要么是忘了加 WHERE 条件，要么是 WHERE 条件无法生效（比如条件列没索引、条件逻辑无效），会导致无效数据被扫描 / 传输，影响查询效率。

3. JSON 格式输出的特殊对应规则

在 EXPLAIN FORMAT=JSON（JSON 格式的执行计划）中，没有与 Using where 完全对应的字段；取而代之的是 attached_condition 属性，该属性会直接存储当前查询中实际使用的 WHERE 条件内容（比如 attached_condition: "t1.id > 100"），本质与 Using where 要传递的 “使用了 WHERE 过滤” 逻辑一致，只是表现形式不同。

SQL 练习

从某个表中导出部分数据

新建表tmp_export，使用insert和select将指定数据写入，然后导出tmp_export表

 insert into tmp_export(a, b, c) (select a, b, c from table_a where a = 'xxx');

导出数据到文件

 SELECT a, b, a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

使用带条件的mysqldump

 mysqldump -u root -p bbscs6 BBSCS_USERINFO –where "id > 123" > test.sql

一步一步优化SQL

https://blog.csdn.net/m0_53022813/article/details/124372805

https://blog.csdn.net/weigeshikebi/article/details/80214965

https://www.cnblogs.com/kangxinxin/p/11585935.html

https://blog.csdn.net/qq_29380733/article/details/86291961

创建用户

'canal'@'%'
'user'@'host'

显示数据库

show databases;

创建数据库

create database if not exists sqlexercise charset utf8mb4 collate utf8mb4_general_ci;

查看当前使用数据库

select database();

使用sqlexercise数据库

use sqlexercise;

显示数据库创建信息

show create database sqlexercise;

创建部门表、职员表、职位表、工资表

-- 部门表
drop table sqlexercise.department;
create table if not exists department(
did numeric(4),
dname varchar(20),
dlocation varchar(20),
primary key(did)
);


-- 职员表
drop table sqlexercise.staff;
create table if not exists staff(
id int auto_increment,
sid int(4),
sname varchar(20),
sex numeric(1) not null comment '0:未知 1:男 2:女',
did numeric(4),
leader numeric(4),
hiredate datetime,
primary key(id),
UNIQUE  INDEX  uidx_sid(sid  ASC),
INDEX  idx_sname(sname  ASC),
INDEX  idx_did_leader(did,leader)
);

-- 职员备注表
drop table sqlexercise.staff_note;
create table if not exists staff_note(
id int auto_increment,
sid int(4),
note json,
primary key(id),
UNIQUE  INDEX  uidx_sid(sid  ASC),
);
/*
{
  "lucky_day": "星期五",
  "birthstone": "蓝宝石",
  "sports": {
    "team_sports": ["足球", "篮球"],
    "individual_sports": ["游泳", "跑步"]
  },
  "interests": {
    "arts": ["绘画", "雕塑"],
    "music": ["古典音乐", "爵士乐"],
    "literature": ["小说", "诗歌"],
    "travel_destinations": ["巴黎", "东京"],
    "cuisines": ["意大利菜", "日本料理"],
    "technology": ["人工智能", "虚拟现实"]
  },
  "aversions": {
    "places": ["拥挤的市场", "嘈杂的夜店"],
    "foods": ["榴莲", "蓝纹奶酪"]
  }
}
*/

-- 职位表
drop table sqlexercise.job;
create table if not exists job(
jid numeric(4),
jname varchar(20),
propost varchar(20),
lowsalary numeric(7,2),
highsalary numeric(7,2),
primary key(jid)
);

-- 工资表
drop table sqlexercise.salary;
create table if not exists salary(
sid numeric(4),
jid numeric(4),
basesalary numeric(7,2),
allowance numeric(7,2),
primary key(sid,jid)
);

插入数据

# 以下j
# 部门表数据
insert into sqlexercise.department value(4101,'行政部','北京');
insert into sqlexercise.department value(4102,'人事部','北京');
insert into sqlexercise.department value(4103,'财务部','北京');
insert into sqlexercise.department value(4104,'产品部','上海');
insert into sqlexercise.department value(4105,'运营部','上海');
insert into sqlexercise.department value(4106,'研发部','南京');

# 职员表数据
insert into sqlexercise.staff value(1,2301,'李乐天',1,4101,null,'2000-04-10 17:27:25'); -- 行政总监
insert into sqlexercise.staff value(2,2302,'汪慕蕊',2,4102,null,'2001-05-08 08:12:32'); -- 人事总监
insert into sqlexercise.staff value(3,2303,'龙芳蔼',2,4103,null,'2005-09-12 09:00:23'); -- 财务总监
insert into sqlexercise.staff value(4,2304,'宁明志',1,4104,null,'2000-02-28 08:53:01'); -- 产品总监
insert into sqlexercise.staff value(5,2305,'庄诗珊',2,4105,null,'2007-11-18 16:42:23'); -- 运营总监
insert into sqlexercise.staff value(6,2306,'边英豪',1,4106,null,'2002-03-28 08:10:59'); -- 研发总监

insert into sqlexercise.staff value(7,2307,'相昂然',1,4101,2301,'2004-02-11 08:12:24'); -- 行政经理
insert into sqlexercise.staff value(8,2308,'谭青木',2,4101,2307,'2003-09-30 16:00:05'); -- 行政专员
insert into sqlexercise.staff value(9,2309,'魏宇文',1,4101,2307,'2012-11-05 11:23:01'); -- 行政专员

insert into sqlexercise.staff value(10,2310,'温夏烟',2,4102,2302,'2001-02-14 08:45:21'); -- 人事经理
insert into sqlexercise.staff value(11,2311,'冀莺莺',2,4102,2310,'2012-03-24 16:56:34'); -- 人事主管
insert into sqlexercise.staff value(12,2312,'步星宇',1,4102,2310,'2006-02-23 08:36:20'); -- 人事主管
insert into sqlexercise.staff value(13,2313,'聂景龙',1,4102,2311,'2022-01-13 08:59:59'); -- 人事专员
insert into sqlexercise.staff value(14,2314,'石弘博',1,4102,2311,'2013-04-09 10:34:48'); -- 人事专员
insert into sqlexercise.staff value(15,2315,'晏瑾瑶',2,4102,2312,'2014-01-31 17:12:12'); -- 人事专员
insert into sqlexercise.staff value(16,2316,'叶曼安',2,4102,2312,'2018-07-29 08:56:02'); -- 人事专员

insert into sqlexercise.staff value(17,2317,'孔珍丽',2,4103,2303,'2002-02-12 09:42:31'); -- 财务经理
insert into sqlexercise.staff value(18,2318,'潘博',1,4103,2317,'2004-07-21 08:42:32'); -- 财务专员
insert into sqlexercise.staff value(19,2319,'戴仑',1,4103,2317,'2010-03-06 15:36:00'); -- 财务专员

insert into sqlexercise.staff value(20,2320,'阎浩气',1,4104,2304,'2009-02-11 10:24:14'); -- 产品经理
insert into sqlexercise.staff value(21,2321,'王学义',1,4104,2320,'2001-08-19 14:23:46'); -- 产品专员
insert into sqlexercise.staff value(22,2322,'吕修贤',1,4104,2320,'2002-05-31 10:48:59'); -- 产品专员

insert into sqlexercise.staff value(23,2323,'钟沈静',2,4105,2305,'2006-02-15 11:36:41'); -- 运营经理
insert into sqlexercise.staff value(24,2324,'林梦泽',2,4105,2323,'2016-01-12 11:00:01'); -- 运营专员
insert into sqlexercise.staff value(25,2325,'龙良平',1,4105,2323,'2015-07-12 08:05:03'); -- 运营专员

insert into sqlexercise.staff value(26,2326,'范德润',1,4106,2306,'2009-02-11 15:36:56'); -- 架构师
insert into sqlexercise.staff value(27,2327,'曾修平',1,4106,2326,'2005-04-06 09:23:00'); -- 开发工程师
insert into sqlexercise.staff value(28,2328,'薛依霜',2,4106,2326,'2016-03-17 08:52:01'); -- 测试工程师
insert into sqlexercise.staff value(29,2329,'范兴昌',1,4106,2326,'2021-02-28 08:45:20'); -- 运维工程师
insert into sqlexercise.staff value(30,2330,'谷永寿',1,4106,2326,'2016-03-03 09:00:09'); -- DBA

# 职位表数据
insert into sqlexercise.job value(1101,'行政总监','总监',12000.00,30000.00); -- 行政总监
insert into sqlexercise.job value(1102,'人事总监','总监',12000.00,30000.00); -- 人事总监
insert into sqlexercise.job value(1103,'财务总监','总监',12000.00,30000.00); -- 财务总监
insert into sqlexercise.job value(1104,'产品总监','总监',15000.00,35000.00); -- 产品总监
insert into sqlexercise.job value(1105,'运营总监','总监',15000.00,35000.00); -- 运营总监
insert into sqlexercise.job value(1106,'研发总监','总监',20000.00,40000.00); -- 研发总监

insert into sqlexercise.job value(1107,'行政经理','经理',6000.00,15000.00); -- 行政经理
insert into sqlexercise.job value(1108,'行政专员','专员',3000.00,8000.00); -- 行政专员

insert into sqlexercise.job value(1109,'人事经理','经理',6000.00,15000.00); -- 人事经理
insert into sqlexercise.job value(1110,'人事主管','主管',6000.00,15000.00); -- 人事主管
insert into sqlexercise.job value(1111,'人事专员','专员',3000.00,8000.00); -- 人事专员

insert into sqlexercise.job value(1112,'财务经理','经理',6000.00,15000.00); -- 财务经理
insert into sqlexercise.job value(1113,'财务专员','专员',3000.00,8000.00); -- 财务专员

insert into sqlexercise.job value(1114,'产品经理','经理',8000.00,20000.00); -- 产品经理
insert into sqlexercise.job value(1115,'产品专员','专员',5000.00,10000.00); -- 产品专员

insert into sqlexercise.job value(1116,'运营经理','经理',8000.00,20000.00); -- 运营经理
insert into sqlexercise.job value(1117,'运营专员','专员',5000.00,10000.00); -- 运营专员

insert into sqlexercise.job value(1118,'架构师','经理',10000.00,30000.00); -- 架构师
insert into sqlexercise.job value(1119,'开发工程师','专员',8000.00,20000.00); -- 开发工程师
insert into sqlexercise.job value(1120,'测试工程师','专员',8000.00,20000.00); -- 测试工程师
insert into sqlexercise.job value(1121,'运维工程师','专员',8000.00,20000.00); -- 运维工程师
insert into sqlexercise.job value(1122,'DBA','专员',8000.00,20000.00); -- DBA

# 职薪表数据
insert into sqlexercise.salary value(2301,1101,25000.00,1000.00);
insert into sqlexercise.salary value(2302,1102,25000.00,2000.00);
insert into sqlexercise.salary value(2303,1103,25000.00,1000.00);
insert into sqlexercise.salary value(2304,1104,27000.00,1000.00);
insert into sqlexercise.salary value(2305,1105,27000.00,1000.00);
insert into sqlexercise.salary value(2306,1106,30000.00,1000.00);

insert into sqlexercise.salary value(2307,1107,10000.00,null);
insert into sqlexercise.salary value(2308,1108,6000.00,null);
insert into sqlexercise.salary value(2309,1108,7000.00,null);

insert into sqlexercise.salary value(2310,1109,11000.00,null);
insert into sqlexercise.salary value(2311,1110,10000.00,null);
insert into sqlexercise.salary value(2312,1110,11000.00,null);
insert into sqlexercise.salary value(2313,1111,6000.00,400.00);
insert into sqlexercise.salary value(2314,1111,7000.00,500.00);
insert into sqlexercise.salary value(2315,1111,6500.00,500.00);
insert into sqlexercise.salary value(2316,1111,8000.00,600.00);

insert into sqlexercise.salary value(2317,1112,12000.00,null);
insert into sqlexercise.salary value(2318,1113,6500.00,null);
insert into sqlexercise.salary value(2319,1113,6500.00,null);

insert into sqlexercise.salary value(2320,1114,13000.00,null);
insert into sqlexercise.salary value(2321,1115,8000.00,null);
insert into sqlexercise.salary value(2322,1115,7000.00,null);

insert into sqlexercise.salary value(2323,1116,14000.00,null);
insert into sqlexercise.salary value(2324,1117,6000.00,null);
insert into sqlexercise.salary value(2325,1117,8000.00,null);

insert into sqlexercise.salary value(2326,1118,22000.00,500.00);
insert into sqlexercise.salary value(2327,1119,15000.00,300.00);
insert into sqlexercise.salary value(2328,1120,17000.00,300.00);
insert into sqlexercise.salary value(2329,1121,15000.00,300.00);
insert into sqlexercise.salary value(2330,1122,16000.00,300.00);

source

清空表

delete from table_name

下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释：
全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

-- 复制表结构和表数据
create table django_migrations_backup select * from django_migrations;

-- 复制表结构
create table django_migrations_backup select * from django_migrations where 1<>1;

-- 表重命名
alter table table1 rename as table2;

-- 修改列id的类型为int unsigned
alter table table1 modify id int unsigned;

-- 修改列id的名字为sid，而且把属性修改为int unsigned
alter table table1 change id sid int unsigned;

　19、使用optimize table
　　对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查询性能。具有可变长的表都存在磁盘碎片问题，这个问题对blob数据类型更为突出，因为其尺寸变化非常大。可以通过使用optimize table来整理碎片，保证数据库性能不下降，优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表，然后使用转存后的文件并重新建数据表；

-- 进程列表
show processlist;

-- 变量
show variables;

-- 查看某数据库的所有表
show tables from sqlexercise;

-- 列出表各列信息
show full columns from sqlexercise.django_migrations_backup;

-- 列出表的索引
show index from table;

-- 列出数据库的表状态
show table status from sqlexercise;

-- 显示表的创建语句
show create table sqlexercise.django_migrations_backup;

-- 显示当前时间
show now();

select password('root');

show variables like 'log_error'; -- 查找错误日志文件路径 

show variables like 'general_log_file'; -- 查找日志文件路径

show variables like 'slow_query_log_file'; -- 慢查询日志文件路径 

show variables like 'log_%'; -- 查看日志状态


describe sqlexercise.user

create database if not exists edu_explore charset utf8mb4 collate utf8mb4_general_ci;

create table edu_student(
id bigint unsigned primary key auto_increment COMMENT 'id',
sno VARCHAR(20) NOT NULL COMMENT '学号',
name VARCHAR(25) COMMENT '姓名',
gender tinyint COMMENT '性别',
age tinyint COMMENT '年龄',
gmt_create datetime COMMENT '创建时间',
gmt_modified datetime COMMENT '修改时间'
);

create table edu_course(
id bigint unsigned primary key auto_increment COMMENT 'id',
cno VARCHAR(20) NOT NULL COMMENT '课程号',
name VARCHAR(25) COMMENT '课程名',
gmt_create datetime COMMENT '创建时间',
gmt_modified datetime COMMENT '修改时间'
);

create table edu_sc(
id bigint unsigned primary key auto_increment COMMENT 'id',
sno VARCHAR(20) NOT NULL COMMENT '学号',
cno VARCHAR(20) NOT NULL COMMENT '课程号',
gmt_create datetime COMMENT '创建时间',
gmt_modified datetime COMMENT '修改时间'
);

# 仅直接配置主键索引
# 性别用int
# 年龄用int

CREATE TABLE `auth_user` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`uid` varchar(3) NOT NULL COMMENT '用户ID',
-- `uid` bigint unsigned NOT NULL COMMENT '用户ID',
`identity_type` tinyint unsigned NOT NULL DEFAULT 1 COMMENT '1用户名 2手机号 3邮箱 4QQ 5微信 6新浪微博',
`identifier` varchar(50) NOT NULL COMMENT '身份标识，手机号、邮箱、用户名或第三方应用的唯一标识',
`certificate` varchar(20) NOT NULL COMMENT '身份凭证',
`gmt_create` datetime COMMENT '创建时间',
`gmt_modified` datetime COMMENT '上次修改时间',
PRIMARY KEY `pk_id` (`id`),
UNIQUE KEY `uk_uid_identity_type` (`uid`,`identity_type`)
-- KEY `idx_uid` (`uid`)
) COMMENT='用户授权表';

销售额

OVER (PARTITION BY)的作用：

OVER (PARTITION BY)通常与其他聚合函数一起使用，如SUM(), AVG(), COUNT(), MAX()和MIN()等。它允许你对数据进行分区（基于某个列的值），然后在每个分区内独立地应用聚合函数。这样，每个行都会保留，并且会添加一个或多个聚合计算的列，这些列是基于每个分区的聚合结果。

GROUP BY与OVER (PARTITION BY)的区别：

  1. 数据聚合：GROUP BY用于将数据分组，并为每个组返回一行聚合结果（如总和、平均值等）。而OVER (PARTITION BY)则在保留所有原始数据行的同时，为每个行添加聚合计算的列。
  2. 执行顺序：GROUP BY在SELECT之后、ORDER BY之前执行，而PARTITION BY是在所有这些关键字之后执行的，可以简单理解为在执行完SELECT之后，在所得结果集之上进行PARTITION BY分组。
  3. 结果集影响：使用GROUP BY后，结果集中的行数会减少，因为每个组只返回一行。而使用OVER (PARTITION BY)后，结果集中的行数不变，每个行都会保留，并添加聚合计算的列。

简单说，partition就是用来实现窗口函数的

示例说明：

假设有一个员工表employee_tbl，包含员工的姓名、日期和登录次数，我们想要计算每个员工的总登录次数，同时保留每个员工的每次登录记录。

使用GROUP BY的示例：

sql

SELECT name, COUNT(*) as total_signins FROM employee_tbl GROUP BY name;

这个查询会返回每个员工的总登录次数，但不会保留每次登录的记录。

使用OVER (PARTITION BY)的示例：

sql

SELECT name, date, signin, SUM(signin) OVER (PARTITION BY name) as total_signins FROM employee_tbl;

这个查询会为每个员工的每次登录记录添加一个total_signins列，显示该员工的总登录次数，同时保留每次登录的记录。

CREATE TABLE tb_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT,
    sid INT,
    sale_date DATE,
    price DECIMAL(10, 2)
);

INSERT INTO tb_order (order_id, sid, sale_date, price) VALUES
(1, 101, '2024-01-01', 250.50),
(2, 102, '2024-01-02', 150.75),
(3, 103, '2024-01-03', 375.00),
(4, 104, '2024-01-04', 120.25),
(5, 105, '2024-01-05', 450.00),
(6, 106, '2024-01-06', 230.45),
(7, 107, '2024-01-07', 550.99),
(8, 108, '2024-01-08', 320.00),
(9, 109, '2024-01-09', 199.99),
(10, 110, '2024-01-10', 299.50),
(11, 111, '2024-01-11', 399.99),
(12, 112, '2024-01-12', 249.99),
(13, 113, '2024-01-13', 599.99),
(14, 114, '2024-01-14', 175.00),
(15, 115, '2024-01-15', 450.00),
(16, 116, '2024-01-16', 225.50),
(17, 117, '2024-01-17', 350.00),
(18, 118, '2024-01-18', 275.75),
(19, 119, '2024-01-19', 399.99),
(20, 120, '2024-01-20', 499.99),
(21, 121, '2024-01-21', 125.00),
(22, 122, '2024-01-22', 299.99),
(23, 123, '2024-01-23', 399.99),
(24, 124, '2024-01-24', 249.99),
(25, 125, '2024-01-25', 599.99),
(26, 126, '2024-01-26', 175.00),
(27, 127, '2024-01-27', 450.00),
(28, 128, '2024-01-28', 225.50),
(29, 129, '2024-01-29', 350.00),
(30, 130, '2024-01-30', 275.75);

窗口函数（Window Functions）在SQL中除了进行分区聚合计算外，还有很多其他的用途。以下是一些常见的窗口函数用途：

  1. 计算运行总数（Running Totals）：

    • 使用窗口函数可以计算累积的总和或计数，例如，计算每个月的累积销售额。
    SELECT order_date, sales_amount,
      SUM(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

  2. 计算移动平均值（Moving Averages）：

    • 窗口函数可以用来计算一段时间内的平均值，比如过去7天的平均销售额。
    SELECT order_date, sales_amount,
      AVG(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

  3. 排名和位置函数：

    • 使用RANK(), DENSE_RANK(), ROW_NUMBER()等函数，可以对结果集进行排名。
    SELECT name, score,
      RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

  4. 比较不同行的数据：

    • 窗口函数可以比较同一查询中的不同行，比如计算每个员工相对于团队平均销售额的百分比。
    SELECT name, sales_amount,
      (sales_amount / SUM(sales_amount) OVER ()) * 100 AS percentage_of_total
FROM employees;

  5. 数据的差分计算：

    • 计算当前行与前一行或后一行的差值，比如计算销售额的月环比增长。
    SELECT order_date, sales_amount,
      LAG(sales_amount, 1) OVER (ORDER BY order_date) AS previous_month_sales,
      (sales_amount - LAG(sales_amount, 1) OVER (ORDER BY order_date)) / LAG(sales_amount, 1) OVER (ORDER BY order_date) * 100 AS month_over_month_growth
FROM sales;

  6. 数据的领先和滞后计算：

    • 使用LEAD()和LAG()函数可以访问当前行的前几行或后几行的数据。
    SELECT order_date, sales_amount,
      LEAD(sales_amount, 1) OVER (ORDER BY order_date) AS next_month_sales
FROM sales;

  7. 数据的聚合和去重：

    • 窗口函数可以用于聚合数据，同时去除重复的行，比如计算每个组的第一条记录。
    SELECT name, MIN(order_date) OVER (PARTITION BY name) AS first_order_date
FROM orders;

  8. 计算累积最大值或最小值：

    • 计算累积的最大值或最小值，比如计算每个员工的累积最大销售额。
    SELECT name, sales_date, sales_amount,
      MAX(sales_amount) OVER (PARTITION BY name ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_max
FROM sales;

窗口函数的强大之处在于它们可以在不改变原始数据行的情况下，为每行添加额外的计算列，这使得它们在数据分析和报告中非常有用。

存储对象

存储对象，stored object

特性 存储函数 存储过程
返回值 必须返回单个值 可以返回多个结果集或参数
SQL中使用 可以 不可以
事务处理 有限支持 完整支持
参数 只有IN参数 IN、OUT、INOUT参数

存储过程

创建

DELIMITER $$ -- 将结束符从分号改为 $$
CREATE PROCEDURE sp_add_one(IN  p_num INT, OUT p_result INT) -- 创建一个存储过程
BEGIN
    SET p_result = p_num + 1; -- 核心逻辑。在存储过程内部，每个SQL语句后面都可以跟一个分号，因为现在的结束符是 $$
END $$ -- 表示存储过程的定义结束，$$ 是新的结束符
DELIMITER ;  -- 恢复默认结束符

[!TIP]

在SQL中，语句默认结束符是分号（;），DELIMITER是一个命令行工具，用于改变SQL语句的结束符。

当定义存储过程、函数或触发器时，由于存储对象内部可能包含多个以分号为结束符的SQL语句，故存储对象需要一个其他结束符来使得SQL解析器识别存储对象在此结束而并非在某一个中间 SQL 语句结束。

简而言之，DELIMITER用于辅助识别语法边界，使得 SQL 程序更好地组织结构。

查看

-- 查看创建语句
SHOW CREATE PROCEDURE `sp_add_one`;
-- 查看详细信息
SELECT * FROM information_schema.routines WHERE routine_name = 'sp_add_one';

调用

SET @result := 0;
CALL sp_add_one(5, @result); -- 调用
SELECT @result AS plus_one;

删除

DROP PROCEDURE IF EXISTS `sp_add_one`;

存储函数

创建

DELIMITER $$
CREATE FUNCTION MOCK_PHONE_86(p VARCHAR(11)) RETURNS VARCHAR(11) DETERMINISTIC
BEGIN
    RETURN CONCAT(LEFT(p, 3), '****', RIGHT(p, 4));
END $$
DELIMITER ;

[!TIP]

  • 存储函数有确定性（DETERMINISTIC）和非确定性（NOT DETERMINISTIC）之分，MySQL 存储函数默认为非确定性。
  • 只要函数结果完全由入参决定，可指定 DETERMINISTIC，MySQL 会基于环境优化性能。
  • 只要函数内部出现随机、时间、会话变量、表数据变动等，就必须写 NOT DETERMINISTIC，避免被缓存影响结果以及 bInlog 同步无法重做数据。

查看

-- 查看创建语句
SHOW CREATE FUNCTION `MOCK_PHONE_86`;
-- 查看详细信息
SELECT * FROM information_schema.routines WHERE routine_name = 'MOCK_PHONE_86';

调用

SELECT MOCK_PHONE_86 ("13666666666") as phone;

删除

DROP FUNCTION IF EXISTS `MOCK_PHONE_86`;

触发器

触发器类型 OLD NEW
insert 无 new表示将要或者已经新增的数据
update old表示修改之前的数据 new表示将要或已经修改后的数据
delete old表示将要或者已经删除的数据 无

假定有一个场景，用户表tb_user中数据被删除时，将数据写入到tb_user_deleted表，并将updated_time更新为删除时间。

表结构准备

-- tb_user表
CREATE TABLE tb_user (
  `id` BIGINT PRIMARY KEY AUTO_INCREMENT, 
  `name` VARCHAR (50), 
  `email` VARCHAR (100), 
  `age` INT, 
  `created_time` DATETIME, 
  `updated_time` DATETIME
);

-- tb_user_deleted 表（与原表完全一致）
CREATE TABLE `tb_user_deleted` LIKE `tb_user`;

创建

DELIMITER $$
CREATE TRIGGER trg_user_after_delete 
AFTER DELETE ON tb_user 
FOR EACH ROW -- 行级触发器
BEGIN
  -- 读取 OLD 插入 tb_user_deleted
  INSERT INTO tb_user_deleted
  VALUES
  (OLD.id, OLD.NAME, OLD.email, OLD.age, OLD.created_time, OLD.updated_time);
  -- 把 tb_user_deleted 中这条记录的 updated_time 改成当前时间
  UPDATE tb_user_deleted
  SET updated_time = NOW()
  WHERE
    id = OLD.id;
END $$
DELIMITER ;

查看

SHOW CREATE TRIGGER `trg_user_after_delete`;

触发

-- 插入一条测试数据
INSERT INTO tb_user(name, email, age, created_time, updated_time)
VALUES ('Alice', 'alice@example.com', 18, NOW(), NOW());

-- 查看
SELECT * FROM tb_user;
-- 结果：1	Alice	alice@example.com	18	2025-09-01 15:41:21	2025-09-01 15:41:21

-- 删除这条记录
DELETE FROM tb_user WHERE id = 1;

-- 查看日志表
SELECT * FROM tb_user_deleted;
-- 结果：1	Alice	alice@example.com	18	2025-09-01 15:41:21	2025-09-01 15:43:30

删除

DROP TRIGGER IF EXISTS `trg_user_after_delete`;

视图

数据库中的视图分为普通视图和物化视图。普通视图为纯逻辑表，其由若干物理表提供数据，查询数据或者修改数据时会从物理表查询或者修改，物化视图为具有一定物理数据的视图，其是在普通视图基础上进行空间换时间优化而来。MySQL 仅支持普通视图。

普通视图有利于多表查询，不利于插入、更新、删除

# 创建
create [or replace] view 视图名称 [列名列表] as select语句 [with [cascaded | local] check option]
# 修改，使用create or replace也可以
alter view [or replace] view 视图名称 [列名列表] as select语句 [with [cascaded | local] check option]

show create view 视图名称; # 查看创建视图语句
# 基于视图对数据进行操作，查询
select * from 视图名称 ...;
drop view if exists stu_v_1;

对视图的插入和修改条件见：https://dev.mysql.com/doc/refman/8.4/en/view-updatability.html

Memcached
MySQL 程序