sql重点知识学习笔记（1）

天落枫

墨衍会员 · AI 创作全网分发码龄10年

关注

原创最新推荐文章于 2026-04-16 09:23:33 发布 1k 阅读 AI 写同款· GEO 优化 ›

SQL快速入门（基本用法）基本语句用法阅读详情

笔记由极客时间摘录得到

sql基础

基础中的基础

sql标准有两个：
sql92和sql99

sql按功能分为4个部分：
ddl：数据定义语言——负责，增删改数据库表
dml：数据操作语言——负责，增删改数据项
dcl：数据控制语言——负责，定义访问权限
dql：数据查询语言——负责，查询

eg：

SELECT name, hp_max FROM heros WHERE role_main = '战士'

sql书写和命名规则：

表名、表别名、字段名、字段别名等都小写；
SQL 保留字、函数名、绑定变量等都大写。
此外在数据表的字段名推荐采用下划线命名，比如 role_main 这种。
SQL 语句必须以分号结尾。

常用数据库：
MySQL
SQL Server
PostgreSQL
DB2
MongoDB

数据库分类：
键值对型：Redis，查找速度快，无法过滤，需要遍历
文档型：MongoDB，文档作为信息基本单位
全文索引型：Elasticsearch，Splunk，Solr，核心原理是“倒排索引”
关系型行存储型：Oracle，MySQL，SQL Server
关系型列存储型：Hadop，降低系统I/O，用于分布式文件系统，但功能有限
图形型：利用实体和对象映射到节点和边得关系，解决复杂关系问题

在这里插入图片描述

列式存储降低I/O：行式存储是把一行的数据都串起来进行存储，然后再存储下一行。
同样，列式存储是把一列的数据都串起来进行存储，然后再存储下一列。
这样做的好处，就是相邻的数据的数据类型是一样的，因此也更容易压缩。压缩之后就自然降低了IO

sql执行过程

oracle

在这里插入图片描述

语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
权限检查：看用户是否具备访问该数据的权限。
共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。
优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

那软解析和硬解析又该怎么理解呢？
在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash值在库缓存（Library Cache）中查找，如果存在 SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。
如果没有找到 SQL 语句和执行计划，Oracle就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

注意：为了提升 SQL 的执行效率，我们应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。
普通查询：
SQL> select * from player where player_id = 10001;
绑定变量：
SQL> select * from player where player_id = :player_id;
这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询 10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

注意：因此我们可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量。但是这种方式也有缺点，使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

MySQL

架构：C/S
服务端：mysqld
在这里插入图片描述

连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层：对 SQL 语句进行查询处理；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

其中sql层：
在这里插入图片描述

查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器。这一部分中，MySQL 和 Oracle 执行 SQL 的原理是一样的。

MySQL特点，可以选择存储引擎，特点：

InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用Memory 存储引擎。
NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

查看MySQL执行时的资源使用情况
设置是否开启资源监测：

mysql> select @@profiling;

profiling=0关闭
profiling=1开启

开启资源监测后，执行一个sql语句，然后执行下面语句可查看上一条资源信息：

mysql> show profile;

查询当前会话所有profiles：

mysql> show profiles;

在这里插入图片描述
如果需要查看指定的资源信息：

mysql> show profile for query 2;

在 8.0 版本之后，MySQL 不再支持缓存的查询，原因我在上文已经说过。一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了 SQL 的查询时间。

查询当前数据库版本：

select version();

DDL的注意点

在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。需要注意的是，在执行 DDL 的时候，不需要 COMMIT，就可以完成执行任务。

新建表

分析一段标准的DDL：

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `height` float(3, 2) NULL DEFAULT 0.00,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

varchar(255)：可变长度字符串，最长255，单位是字节，一个字母一个字节，一个中文两个字节
nvarchar(255)：单位是字符，unicode，统一两个字节
语句结束符为；分号
NOT NULL：空值约束
auto_increment：主键自增
反引号：能避免与MySQL保留字冲突
CHARACTER SET utf8：字符编码
COLLATE utf8_general_ci：排序规则
UNIQUE INDEX：唯一索引
NORMAL INDEX：普通索引
DEFAULT：默认值
CHECK：取值范围约束：CHECK(height>=0 AND height<3)

排序规则：
utf8_general_ci：对大小写不敏感
utf8_bin：大小写敏感

注意：InnoDB，它是 MySQL5.5 版本之后默认的存储引擎

修改表

eg:

# 加字段
ALTER TABLE player ADD (age int(11));
# 改名
ALTER TABLE player RENAME COLUMN age to player_age;
# 改属性
ALTER TABLE player MODIFY (player_age float(3,1));
# 删字段
ALTER TABLE player DROP COLUMN player_age;

数据表设计原则：

表数量尽可能少
数据表字段尽可能少
联合主键字段尽可能少
主键和外键尽可能多

查询表

eg：

# 查询所有
SELECT * FROM heros;
# 查询特定列，起别名
SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros;
# 查询常数
SELECT '王者荣耀' as platform, name FROM heros;
# 同上
SELECT 123 as platform, name FROM heros;
# 去重
SELECT DISTINCT attack_range FROM heros;
# 多字段合一去重
SELECT DISTINCT attack_range, name FROM heros

注意：

DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错。
DISTINCT 其实是对后面所有列名的组合进行去重。

order by语句

可以跟多个列名，排列时先按照第一个排，然后依次比较
ASC 增（默认），DESC 降
比价规则，如果为文本，需要看数据库设置的规则，（如果再创建字段时设置binary属性，则区分大小写）
order by通常位于select语句最后

SELECT name, hp_max FROM heros ORDER BY hp_max DESC ;

SELECT name, hp_max FROM heros ORDER BY mp_max, hp_max DESC;

# MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字
SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5;
# SQL Server 和 Access使用Top关键字
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC;
# DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;
# Oracle使用rownum关键字，注意：这条语句执行逻辑是：取5条数据→排序，想要逻辑一样，使用下面方法
SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC;
# 使用子查询
SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5;

select执行顺序

# 书写顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

# 执行顺序
# FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

注意：在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。

select *使用限制

使用*会加重数据库负担，但是和将列名全部列出来效率差不多。生产上不推荐使用select *

count 1和count *

在MySQL innoDB中：count (*) 和count(1) 都是对所有结果进行count本质没有区别，复杂度O(N)，全表扫描（循环计数）
在MyISAM中：由于有meta信息存了row_count值，所以复杂度O(1)

另外，InnoDB中，count (*) 和count(1) 统计行数，尽量采用二级索引，因为主键索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于查找具体的行来说，采用主键索引效率更高。

优化总结：
1、一般情况下：COUNT(*) = COUNT(1) > COUNT(字段)
所以尽量使用COUNT(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
2、如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

条件过滤

条件过滤主要使用where子句

比较运算符

在这里插入图片描述
注意： Access 不支持（!=），不等于应该使用（<>）。在 MySQL 中，不支持（!>）（!<）

逻辑运算符

在这里插入图片描述
注意：当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符，如需改变优先级，使用括号即可

eg：

SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

注意：对于日期，有可能是字符串格式，建议用DATE函数来转换成日期格式再比较

通配符

通配符的关键字：LIKE

%：匹配任意个字符
?：匹配单个字符

注意：在 Access 中使用?来代替_，而且在 DB2 中是不支持通配符_的，因此你需要在使用的时候查阅相关的 DBMS 文档。

不过在实际操作过程中，我还是建议你尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配。即使你对 LIKE 检索的字段进行了索引，索引的价值也可能会失效。
如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用LIKE '%太%'或LIKE '%太’的时候就会对全表进行扫描。如果使用LIKE ‘太%’，同时检索的字段进行了索引的时候，则不会进行全表扫描

保持高效率的一个很重要的原因，就是要避免全表扫描，所以我们会考虑在 WHERE 及 ORDER BY 涉及到的列上增加索引。

为什么要在order by上加上索引有利于排序避免全表扫描？

关于ORDER BY字段是否增加索引：在MySQL中，支持两种排序方式：FileSort和Index排序。Index排序的效率更高，
Index排序：索引可以保证数据的有序性，因此不需要再进行排序。
FileSort排序：一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低。

所以使用ORDER BY子句时，应该尽量使用Index排序，避免使用FileSort排序。当然具体优化器是否采用索引进行排序，你可以使用explain来进行执行计划的查看。

优化建议：

SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，ORDER BY子句避免使用FileSort排序。
当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。
尽量Using Index完成ORDER BY排序。
如果WHERE和ORDER BY相同列就使用单索引列；如果不同使用联合索引。
无法Using Index时，对FileSort方式进行调优。

避免索引失效的手段：

不要在WHERE子句后面对字段做函数处理，同时也避免对索引字段进行数据类型转换
避免在索引字段上使用<>，!=，以及对字段进行NULL判断（包括 IS NULL, IS NOT NULL）
在索引字段后，慎用IN和NOT IN，如果是连续的数值，可以考虑用BETWEEN进行替换
因为在WHERE子句中，如果对索引字段进行了函数处理，或者使用了<>,!=或NULL判断等，都会造成索引失效。

sql函数

内置函数：算术函数、字符串函数、日期函数、转换函数

eg：

# 算术函数
SELECT ABS(-2) #运行结果为 2。
SELECT MOD(101,3) #运行结果 2。
SELECT ROUND(37.25,1) #运行结果 37.3。
# 字符串函数
SELECT CONCAT('abc', 123) #运行结果为 abc123。
SELECT LENGTH('你好') #运行结果为 6。
SELECT CHAR_LENGTH('你好') #运行结果为 2。
SELECT LOWER('ABC') #运行结果为 abc。
SELECT UPPER('abc') #运行结果 ABC。
SELECT REPLACE('fabcd', 'abc', 123) #运行结果为 f123d。
SELECT SUBSTRING('fabcd', 1,3) #运行结果为 fab。
# 日期函数
SELECT CURRENT_DATE() #运行结果为 2019-04-03。
SELECT CURRENT_TIME() #运行结果为 21:26:34。
SELECT CURRENT_TIMESTAMP() #运行结果为 2019-04-03 21:26:34。
SELECT EXTRACT(YEAR FROM '2019-04-03') #运行结果为 2019。
SELECT DATE('2019-04-01 12:00:05') #运行结果为 2019-04-01。
# 转换
SELECT CAST(123.123 AS INT) #运行结果会报错。
SELECT CAST(123.123 AS DECIMAL(8,2)) #运行结果为 123.12。
SELECT COALESCE(null,1,2) #运行结果为 1。

日期函数：
在这里插入图片描述
注意：DATE 日期格式必须是yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较，我会在后面的例子中讲具体的原因。

转换函数：
在这里插入图片描述
CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

使用sql会出现的问题：大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

sql聚合函数

聚合函数共5个：
在这里插入图片描述
eg：


SELECT COUNT(*) FROM heros WHERE hp_max > 6000

SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000

SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros

SELECT COUNT(*), role_main FROM heros GROUP BY role_main

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

注意：
count(字段)：会忽略null
count(*)：如果是*则不会
group by：按照多字段分组，则会罗列所有组合情况进行分组

HAVING和WHERE区别：WHERE 是用于数据行，而 HAVING 则作用于分组。HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

子查询

子查询分为关联子查询和非关联子查询

非关联子查询：子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。

关联子查询：子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。

eg：

# 非关联子查询
SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player);
# 关联子查询：因为每次表 a 中的 team_id 可能是不同的，所以是关联子查询。如果是非关联子查询，那么从句计算的结果是固定的才可以
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

特点：子查询结果固定=非关联子查询

EXIST子查询

特点是：where exist + 子查询
eg：

SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

集合比较子查询类型

在这里插入图片描述
eg：

SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002);

SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team;

IN和EXIST区别和使用场景

总结：大 IN 小；小 EXIST 大；

连接查询

SQL92 中的 5 种连接方式，它们分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接。

笛卡尔积：交叉连接CROSS JOIN，全连接
等值连接：where后判定等值
非等值连接：where后逻辑判断：大于，小于，不等于，之间
外连接：主表全显示，从表有对应就显示（sql92使用+标记主表）：left join和right join
自连接：自己和自己连接查询，一般会有优化，效率高
自然连接：sql99中，natural join，将连接表主键字段等值比较

eg：

SELECT * FROM player, team where player.team_id = team.team_id(+);

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id;
# 自连接，不好解释
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height
# natural join，相当于：WHERE player.team_id = team.team_id。
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team

注意：当使用表别名后就不能使用原表名

ON条件

eg：SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id;

USING条件

指定字段的等值连接：
eg:SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);
相当于：ON player.team_id = team.team_id

注意：MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。

连接查询标准格式

建议采用如下格式进行连接查询：

SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

一些注意事项

不是所有数据库都支持全外连接
oracle表没有别名as，直接跟别名即可
sqlite只有左连接
连接表数量过多会降低查询效率
连接时一定要加条件
自连接比子查询效率高

视图的作用

视图是虚拟表，本身不具有数据。非长期存放，连接关闭后临时表自动释放。

可以简化复杂的sql查询，可以直接重用无需考虑细节。可以嵌套创建。

视图的优点：安全性，简单清晰

创建视图 create view

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

修改视图 alter view

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

删除视图 drop view

DROP VIEW view_name

注意：SQLite 不支持视图的修改，仅支持只读视图，也就是说你只能使用 CREATE VIEW 和 DROP VIEW，如果想要修改视图，就需要先 DROP 然后再 CREATE

利用视图进行格式化

 CREATE VIEW player_team
 AS SELECT CONCAT(player_name, '(' , team.team_name , ')') 
 AS player_team FROM player 
 JOIN team WHERE player.team_id = team.team_id

存储过程

类似于函数，可以接收参数并执行的一系列语句，返回结果

创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

注意：和视图一样，我们可以删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要更新存储过程，我们需要使用 ALTER PROCEDURE。

eg：

# 1+2+...+n
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END

调用：CALL add_num(50)

注意：因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER，新的结束符可以用（//）或者（$$）。如果你用的是 MySQL，那么上面这段代码，应该写成下面这样：
eg：


DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

入参修饰符：
在这里插入图片描述
eg：

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

调用：

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

注意：当参数为out时，参数名前必须加@

流程控制

BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。

DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。

SET：赋值语句，用于对变量进行赋值。

SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

CASE：CASE 语句用于多条件的分支判断
eg：

CASE 
  WHEN expression1 THEN ...
  WHEN expression2 THEN ...
  ...
    ELSE 
    --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。

REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

存储过程的特点：首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。还有一点，存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。
缺点也很明显：可移植性差，调试困难，版本更新容易出问题，不适合高并发

收录于