SQL，Structured Query Language

SQL 国际标准

[!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 范围：

• 在 session 中对系统变量的读写仅在该 session 生效，不影响其他 session，session 关闭后失效。
• 在 global 中对系统变量的读写在全局生效（所有session生效），影响其他所有 session，MySQL 实例关闭后失效。
• 可通过在MySQL配置文件中配置该系统变量实现持久化配置。

-- 查看
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

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 游标名称 ;

SQL 语句执行全流程

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）。