SQL
概述
SQL(Structured Query Language)，是结构化查询语言，
可以用于访问和处理数据库，包括CRUD(增create,查read,改update,删delete)。
是一项国际统一的标准，但是大部分SQL数据库都有它们的专有扩展
基本概念
- 数据库(database) 保存有组织的数据的容器(通常是一个或一组文件)
- 数据表(table) 某种特定类型数据的结构化清单
- 模式(schema) 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式
- 列(column) 表中的一个字段。所有表都是由一个或多个列组成
- 行(row) 表中的一个记录
- 主键(primary key) 一列或一组列，其值能够唯一表示表中每一行
SQL语法结构
- 子句 语句和查询的组成部分
- 表达式 可以产生任何标量值，或由列和行组成的数据库表
- 谓词 给需要评估的SQL三值逻辑或者布尔逻辑指定条件并限制语句和查询的效果，或改变程序流程
- 语句 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、链接、会话或诊断。
数据类型
数值类型
- INT: 用于存储整数数值
- DECIMAL(precision,scale): 用于存储固定进度的数值，包括整数部分和小数部分
- FLOAT: 用于存储浮点数，支持较大的浮点数范围
字符串类型
- CHAR(n): 用于存储固定长度的字符串，n是最大字符数
- VARCHAR(n): 用于存储可变长度的字符串，最大长度为n
- TEXT: 用于存储大段文本数据
日期和时间类型
- DATE: 用于存储日期，格式为 YYYY-MM-DD
- TIME: 用于存储时间，格式为 HH:MM:SS
- DATETIME： 用于存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS
二进制类型
- BLOB: 用于存储二进制数据，比如图片，音频等
- BIT: 用于存储位数据
其他类型
- BOOL、BOOLEAN: 用于存储逻辑值
- ENUM: 用于存储枚举值，从一个预定义的列表中进行选择
SQL语法要点
- SQL语句不区分大小写，但是数据库表名、列名和值是否区分则依赖于具体的系统和配置
- 多条SQL语句必须以分号分隔
- 处理SQL语句时，所有空格都被忽略。SQL语句可以写成一行也可以分成多行
- 支持三种注释内容
## 注释1
– 注释2
/* 注释3 */ - 表名后面加变量名，作为别称
SQL语句分类
-
数据定义语言(DDL)
数据定义语言(Data Definition Language,DDL)是SQL语言集中负责数据结构定义与数据库对象定义的语言。
主要功能是 定义数据库对象
核心指令是CREATEALTERDROP -
数据操纵语言(DML)
数据操作语言(Data Manipulation Language,DML)是用于数据库操作，对数据库其中对象和数据运行访问工作的编程语句
主要功能是 访问数据，因此语法以读写数据库为主
核心指令是INSERTUPDATEDELETESELECT合称CRUD，即增删改查 -
事务控制语言(TCL)
事务控制语言(Transaction Control Language,TCL)用于管理数据库中的事务
核心指令是COMMITROLLBACK -
数据控制语言(DCL)
数据控制语言(Data Control Language,DCL)是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。
主要功能是 控制用户的访问权限，可控的权限有CONNECTSELECTINSERTUPDATEDELETEEXECUTEUSAGEREFERENCES
核心指令是GRANTREVOKE
增删改查
插入数据
INSERT INTO 语句用于向表中插入新纪录
-
插入完整的行
INSERT INTO table_name VALUES (value1,value2...); -
插入行的一部分至对应部分
INSERT INTO table_name(column1,column2...) VALUES (value1,value2...) -
插入查询出来的数据
INSERT INTO table_name(column1) SELECT name FROM table_name1;
解析：SELECT name FROM table_name2 从表2中获得name并插入到表1的column1列(相当于用SELECT语句取代了VALUE(value1))
更新数据
UPDATE 语句用于更新表中的记录
UPDATE table_name SET column1=value1,column2=value2 WHERE column3=value3;
说明：SET后面为需要更新的位置，WHERE后面为需要更新的行的特征
删除数据
DELETE 语句用于删除表中的记录
TRUNCATE TABLE 用于清除表也就是删除所有行(truncate:截断)
-
删除表中的指定数据
DELETE FROM table_name WHERE column1 = value; -
清空表中的数据
TRUNCATE TABLE table_name;
查询数据
SELECT 语句用于从数据库中查询数据
DISTINCT 用于返回为一不同的值。作用于列，也就是当所有列的值都相同时才相同(distinct:不同的)
LIMIT 限制返回的行数，有两个参数，第一个参数为起始行，从0开始，第二个参数为返回的总行数
ASC:升序(默认)
DESC:降序
-
查询单列
SELECT column1 FROM table_name; -
查询多列
SELECT column1,column2... FROM table_name; -
查询所有列
SELECT * FROM table_name; -
查询不同的值
SELECT DISTINCT column1 FROM table_name; -
限制查询结果
-- 返回前5行
SELECT * FROM table_name LIMIT 5;
SELECT * FROM table_name LIMIT 0,5;
-- 返回第3~5行(索引从0开始)
SELECT * FROM table_name LIMIT 2,3;
-- 解释从第二行开始，共3行
-- 第一个参数开始位置，第二个参数总行数
子查询
子查询是嵌套在较大查询中的SQL查询。子查询也称为内部查询或内部选择，而包含子查询的较大查询语句称为外部查询或外部选择
主要功能是 过滤数据、计算值、嵌套查询、子查询作为派生表
语法要点
-
子查询可以嵌套在
SELECTINSERTUPDATEDELETE语句或者另一个子查询中 -
子查询通常会在另一个
SELECT语句中的WHERE子句中添加 -
可以使用比较运算符(<,>或=)，也可以是多行运算符(
INANYALL) -
子查询必须被圆括号括起来
-
子查询在其父查询前执行，以便将子查询的结果传递给父查询
子查询的类型
- 标量子查询
返回单个值的子查询
SELECT column1 FROM table1
WHERE column1 = (SELECT MAX(column1) FROM table_name);
- 列子查询
返回一列值的子查询，通常用于含有IN或FROM子句
SELECT column1 FROM table_name
WHERE column1 IN (SELECT column2 FROM table2);
- 行子查询
返回一行值的子查询，通常用于比较整行数据或子查询作为派生表使用
SELECT * FROM table_name
WHERE (column1,column2) = (SELECT column1,column2 FROM table_name2);
WHERE
WHERE子句用于过滤记录，即缩小访问数据的范围
WHERE后跟一个返回true或false的条件
WHERE可以与SELECT、UPDATE、DELETE一起使用
可以用于WHERE子句的操作符
| 操作符 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于(在一些版本中可能是!=) |
| > | 等于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式 |
| IN | 指定针对某个列的多个可能值 |
运算符
IN 和 BETWEEN
- IN
作用是在指定的几个特定值中任选一个
SELECT * FROM table_name
WHERE column IN (value1,value2);
- BETWEEN
作用是选取介于某个范围内的值
SELECT * FROM table_name
WHERE column1 BETWEEN 3 AND 5;
AND,OR,NOT
-
AND,OR,NOT用于对过滤条件的逻辑处理
-
优先级 NOT > AND > OR
LIKE
- 使用在WHERE子句中，用于确定字符串是否匹配模式
- 只有字段是文本值才使用LIKE
- 通配符
- % 表示任意字符出现任意次数
- _ 表示任意字符出现一次
- [] 表示字符集中一个字符
- [^] 表示非字符集中一个字符
- 不可滥用通配符，通配符位于开头处匹配会非常慢
连接(JOIN)
将两个或多个表的行通过关联条件(通常是列的值相等)结合起来的操作
多表查询原理
将多个表通过笛卡尔乘积形成一个虚表，再根据条件筛选符合的数据
语法要点
-
连接多个表使用
JOIN关键字 -
关联条件使用
ON而不是WHERE -
JOIN保持基表(结构和书记)不变,而是使用虚拟表 -
链接可以替换子查询，且比子查询的效率更快
连接类型
- 内连接
又称等值连接，使用INNER JOIN关键字
用于查询来自两个或多个相关表的数据
语法:- 在FROM子句指定主表(table1)
- 在INNER JOIN子句和连接谓词中指定第二个表(table2)
- 只有谓词计算为TRUE的行才包含在结果集中
要点：
- 相同列会出现多次(因为是笛卡尔乘积)，需要用表明.列名区分
- 可以只查不在主表中的列
INNER JOIN子句将表 T1 的每一行与表 T2 的行进行比较，以查找满足连接谓词的所有行对。 如果连接谓词的计算结果为 TRUE ，则匹配的 T1 和 T2 行的列值将合并为一个新行并包含在结果集中。、右外连接(即右连接)、全外连接 - 左连接
LEFT JOIN 子句用于查询来自多个表的数据。
它返回左表中的所有行和右表中的匹配行。
如果在右表中找不到匹配的行，则使用 NULL 代替显示。
使用场景:保留左表所有内容，右表只需匹配内容
。 它对于查询分层数据或比较同一个表中的行很有用。
自联接使用内连接或左连接子句。 由于使用自联接的查询引用同一个表，因此表别名用于为查询中的表分配不同的名称
SELECT 列名
FROM 表名 AS 表别名1
JOIN 表名 AS 表别名2 ON 连接条件;
- 自然连接
自动查找两个表中相同列名的列，并将它们作为连接的条件
结果是两个表匹配的行的和，且删除重复的行(table1在前)
多列连接：如果两个表中有多个列具有相同列名，这些列都将被作为连接条件，从而形成多列连接。
性能可能不太好，因为要对所有的列进行匹配
SELECT *
FROM table1
NATURAL JOIN table2;
- 交叉连接
生成左表和右表的笛卡尔乘积，不会在连接的表之间建立关系
SELECT 列名
FROM
table1
CROSS JOIN table2;
组合(UNION)
用于合并多个SELECT查询的结果集
语法要点
- 会自动除去重复的行，如果需要保留使用UNION ALL
- 所有SELECT查询的列数需要相同，且对应的数据结构要相同
- 不会自动排序，如果要排序用ORDER BY(只能有一个且必须在最后)
- 列名可以不相同，但是位置顺序和数据结构要一致
- 合并后的列名由第一个SELECT确定
使用场景
在一个查询中从不同的表返回结构数据。
对一个表执行多个查询，按一个查询返回数据。
举例
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;
JOIN vs UNION
JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。
函数
不同数据库的函数往往各不相同，这里以MySQL为例
文本处理
-
LEFT(str,length)、RIGHT(str,length)
用途:中一个字符串的左(右)边提取指定长度的字符
要点:如果长度大于字符串长度则返回整个串
举例: 提取员工全名的前三个字符SELECT LEFT(full_name, 3) AS short_name FROM employees; -
LOWER(str)、UPPER(str)
用途:将字符转换成小(大)写
举例SELECT UPPER(column1) FROM table1; -
LTRIM(str,char)、RTRIM(str,char)
用法:去除字符串左(右)侧空格或指定字符char
举例SELECT LTRIM('$$$Price', '$') AS trimmed_string; -
LENGTH(string)
用途:计算字符串的长度
要点:使用时会获得额外的一列表示长度,需要指定列名SELECT product_name, LENGTH(product_name) AS name_length FROM products; -
SOUNDX()
用途:将一个字符串转成成描述其语言表示的字母数字模式
日期时间处理
数值处理
聚合(统计)
排序和分组
ORDER BY
用于对结果集进行排序
- ASC 升序(默认)
- DESC 降序
- 可以对多个列进行排序并指定不同的排序方式,顺序为从左到右
举例
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
GROUP BY
用于对结果集按照列进行分组，并可以对每个分组使用聚合函数
说明:
- 一般结果中的每个组会聚合成一行，同时附上统计数据
- 需要指定列名
- 多个列，列的顺序由SELECT指定，(两列都相同才作为一组)
SELECT column1, column2, ..., aggregate_function(column_name) AS column_name
FROM table_name
GROUP BY column1, column2, ...;
HAVING
用于对分组结果进行过滤，与WHERE类似，但是WHERE用于过滤行，HAVING用于过滤分组，通常与聚合函数一起使用
SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1
HAVING condition; --condition表示筛选条件
数据定义
约束项
-
PRIMARY KEY
主键，用于唯一标识表格中的每一行，每个表格只能有一个主键且值不可重发 -
NOT NULL
确保值非空 -
UNIQUE
确保列值唯一，但允许为空 -
DEFAULT
指定插入新行时，若为提供值则使用默认值 -
FOREIGN KEY
外键，用于创建表格间的关联，要确保参考另一表格(主表)的外键值必须在那个表格中存在
表格中的列在主表中对应列存在
使用场景- 建立表格间关联
- 维护数据唯一性
- 实现级联操作
- 限制数据插入和更新
举例
CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ... FOREIGN KEY (column_name) REFERENCES other_table(column_name) );外键约束可能会影响数据库的性能，特别是在大型数据库中频繁进行插入和更新操作时
-
CHECK
用于定义插入或更新列值的条件 -
AUTO_INCREMNET
在插入新行时，自动为列生成唯一的递增值，通常与主键一起使用
数据库(DATABASE)
- 创建数据库
CREATE DATABASE database1;
- 删除数据库
DROP DATABASE database1;
- 选择数据库
USE DATABASE database1;
数据表(TABLE)
-
创建数据表
- 普通创建
CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, column3 datatype constraints, -- column1是列名 -- datatype是数据类型 -- constraints是可选的约束项 );- 根据已有表进行创建
CREATE TABLE table_name AS SELECT * FROM table1;要点:
- 会复制原有的数据和结构
- 不会复制原始表的约束，索引，触发器等元数据信息
-
删除数据表
DROP TABLE table_name;
-
修改数据表
- 添加列
ALTER TABLE table_name ADD column1 datatype constraints;- 删除列
ALTER TABLE table_name DROP COLUMN column1;- 修改列
ALTER TABLE table_name MODIFY COLUMN column1 new_datatype将指定列名的列的数据类修修改为新的类型
- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
视图(VIEW)
定义
视图是基于结果集构建的可视化的虚拟表，本身不包含数据，也不能对其进行索引操作，对视图操作和对普通表操作类似。
作用
- 简化复杂的SQL操作，比如复杂的连接
- 只使用实际表的一部分数据
- 只给用户视图的访问权限，保证数据的安全
- 更改数据格式和表示
使用
- 创建视图
根据已有的表和条件进行创建。
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- 删除视图
DROP VIEW view_name;
- 查询视图
与正常查询数据表相同
SELECT *
FROM view_name;
- 更新视图
在某些情况下，可以更新视图。但并非所有类型的视图都可以更新。一般来说，只有满足一定条件的视图才能被更新，例如视图关联的列必须是唯一的、不能包含聚合函数等。
类型
- 虚拟视图:由SQL查询定义，不会在数据库中存储数据
- 物化视图:存储了视图的计算结果，后续查询时无需重复计算
索引
定义
用于提高检索速率的数据结构，类似于书籍的目录
作用
- 提高检索速率
- 加速排序
- 保持数据唯一性
要点
- 索引会增加写操作的开销
每次进行写时，索引都需要进行维护，导致写操作的性能下降 - 适当选择索引
选择常用于查询的列来建立索引，避免过度索引或不必要索引 - 定期维护索引
当数据增加和变化，索引可能会失效，定期重新构建索引可以保证其效率
使用
- 创建索引
CREATE INDEX index_name ON table_name(column1);
- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column1);
- 删除索引
ALTER TABLE table_name
DROP INDEX index_name;
事务处理
概念
- 事务是一组数据库操作，这些操作被视为一个逻辑单元，要么全部执行成功，要么全部失败回滚
- 事务通常由一系列SQL语句组成，例如插入、删除、更新等
特性
- 原子性(Atomicity) 事务中的所有操作要么全部执行成功，要么全部失败回滚，不存在部分执行的情况
- 一致性(Consistency) 事务执行前后，数据库的完整性约束不会被破坏，保持数据库的一致性
- 隔离性(Isolation) 多个事务并发执行时，每个事务的操作应该被隔离开，不会相互影响
- 持久性(Durablity) 一旦事务提交成功，其所作的修改应该永久的保存在数据库中，即使系统崩溃也不会丢失。
事务的控制
-
开始事务
START(BEGIN) TRANSACTION; -
提交事务
COMMIT;
提交后所作的修改永久保存 -
回滚事务
ROLLBACK;- 回滚未提交事务
若事务未提交，执行ROLLBACK将撤销该事务中所有的操作，并释放事务占用的内存。 - 回滚已提交事务
这样将撤销所有已提交的事务的操作，从而将数据库回滚到事务开始之前的状态 - 回滚到保留点
ROLLBACK TO point_name 可以回滚到特定的保留点
- 回滚未提交事务
-
创建保留点
SAVEPOINT point_name; -
释放保留点
RELEASE SAVEPOINT point_name;
事务的隔离等级
设置事务隔离等级
SET TRANSACTION ISOLATION LEVEL isolation_level;
- READ UNCOMMITTED
最低的隔离级别，允许一个事务读取另一个事务未提交的数据，可能导致脏读，不可重复读，幻读问题 - READ COMMITTED
保证一个事务不会读取另一个事务未提交的数据，但可能出现不可重复读 - REPEATABLE READ
保证一个事务在执行期间多次读取同一个数据时，数据不会发生变化，但可能出现幻读问题 - SERIALIZABLE(可串行化的)
最高的隔离级别，确保事务间彼此完全隔离，避免脏读、不可重复读、幻读的问题，但会降低并发性能。
并发中问题说明
-
脏读
脏读指一个事务读取了另一个事务尚未提交的数据。假设事务 A 修改了一行数据，但尚未提交，此时事务 B 读取了这个未提交的数据。如果事务 A 后来回滚了，那么事务 B 读取到的数据就是无效的，这就是脏读。 -
不可重复读
不可重复读指在一个事务中，同一查询在不同时间点返回了不同的结果。这可能是因为在事务执行期间，其他事务修改了查询的数据。例如，事务 A 读取了一行数据，然后事务 B 修改了该行数据并提交，此时事务 A 再次读取相同的行，但得到了不同的结果。 -
幻读
幻读指在一个事务中，同一查询在不同时间点返回了不同数量的行，这可能是由于在事务执行期间，其他事务插入了新的数据。例如，事务 A 查询一个范围内的数据行，然后事务 B 插入了一些新的数据行，并提交。接着事务 A 再次执行相同的查询，但这次返回的结果包含了之前不存在的行，就好像出现了幻觉一样。
权限控制
涉及到用户、权限、角色管理
用户管理
每个用户都有单独的权限
- 创建用户
CREATE USER user_name IDENTIFIED BY 'password';
用户名一般是username@hostname的形式,hostname表示允许该用户登录的主机，若%@表示允许任意主机登录
-
修改用户
- 修改密码
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';将指定用户的密码修改为new_password
- 修改用户名
RENAME USER 'old_username'@'hostname' TO 'new_username'@'hostname';- 修改主机
ALTER USER 'username'@'new_hostname' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 20;修改用户的登录主机，with语句能限制最大的查询次数
-
删除用户
DROP USER user_name;
-
查看用户
- SELECT语句
用户和权限信息存储在mysql系统数据库的表中，可以直接用SELECT检索
SELECT * FROM mysql.user WHERE User = 'username'- SHOW命令
可以用来查看特定用户的权限
SHOW GRANTS FOR 'username'@'hostname'; - SELECT语句
权限管理
- 授权权限
GRANT privilege_name ON database_name.table_name TO 'username'@'hostname';
- privilege_name 是要授予的权限，例如 SELECT, INSERT, UPDATE 等。
- database_name 是要授予权限的数据库名称。
- table_name 是要授予权限的表名称，如果要授予权限给整个数据库，则可以使用 * 通配符。
- ‘username’@‘hostname’ 是要授予权限的用户和主机。
- 撤销权限
REVOKE privilege_name ON database_name.table_name FROM 'username'@'hostname';
- privilege_name 是要撤销的权限，例如 SELECT, INSERT, UPDATE 等。
- database_name 是要撤销权限的数据库名称。
- table_name 是要撤销权限的表名称，如果要从整个数据库撤销权限，则可以使用 * 通配符。
- ‘username’@‘hostname’ 是要撤销权限的用户和主机。
- 查看权限
- SHOW GRANTS命令
SHOW GRANTS FOR 'username'@'hostname';
显示特定用户的权限
- SELECT语句直接查询user表
与前面用户查询类似
角色管理
- 创建角色
CREATE ROLE role_name;
- 角色授权
GRANT SELECT, INSERT ON database_name.table_name TO role_name;
将指定权限授予角色
- 分配角色
GRANT role_name TO 'username'@'hostname';
- 管理角色权限
与管理用户权限类似
存储过程
存储过程是一组预编译的SQL语句和逻辑，被存储在数据库中以便反复使用
创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
-- SQL语句和逻辑
END //
DELIMITER ;
说明:
- 参数是可选的，用于指定存储过程接受的输入参数，参数由名称和类型(IN、OUT、INOUT)组成，变量间用,分隔
- 为了避免MySQL中语句分隔符;与创建存储过程语句冲突，通常会将分隔符修改成//，内部正常用;
- 用BEGIN和END表示开始和结束
存储过程参数
可以接受输入参数，用于动态的生成结果
参数可以是IN、OUT、INOUT类型
调用存储过程
使用CALL语句调用存储过程，并传递参数(如果有)
可以将存储过程的结果存储在变量中或者直接使用其结果集
举例
CALL procedure_name(100);
说明：
- 若没有参数则只需要名称
- 若有参数则顺序必须与定义中的顺序一致
- 结果可以存在结果集中直接使用，也可以存在变量中
流程控制
可以使用IF、CASE、LOOP、WHILE、GOTO等进行流程控制
异常处理
可以用DECLARE…HANDLER语句定义异常处理程序
- 声明异常变量
首先需要使用DECLARE语句声明一个异常变量用于存储异常信息
DECLARE variable_name CONDITION FOR condition_name;
- 定义异常处理程序
使用DECLARE HANDLER语句定义异常处理程序，指定当某个异常发生时的处理逻辑。可以指定某类特定的异常，也可以使用通用的异常处理程序
DECLARE HANDLER FOR condition_value handler_statements;
- 抛出异常处理程序
通常使用SIGNAL语句主动抛出异常
可以指定异常的SQLSTATE、消息文本以及可选的错误代码
SIGNAL SQLSTATE 'sqlstate_value' SET MESSAGE_TEXT = 'message_text';
- 重新抛出异常
允许在异常处理程序中进一步处理异常情况
可选选择性的更改异常的SQLSTATE和消息文本
RESIGNAL
修改存储过程
使用ALTER PROCEDURE语句修改存储过程的定义
使用DROP PROCEDURE语句删除存储过程
DELIMITER //
CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
-- SQL语句和逻辑
END //
DELIMITER ;
说明:
相当于重新定义一个这个名字的存储过程，会直接覆盖原有的定义
存储过程的优势
- 提高性能 存储过程在数据库服务器上预编译和存储，减少了每次执行SQL语句的解析和编译开销
- 重用性 存储过程可以多次调用，提高了代码的重用性和可维护性
- 安全性 存储过程可以通过授权的方式进行权限管理，提高了数据库的安全性
游标
概念
游标是一种数据库对象，用于在存储过程或者函数中按行处理查询结果集。
一般用于交互式应用，用户可以对任意行进行浏览和修改
存储的数据是一整个结果集，但是可以逐行处理数据
不能跳转到指定行，也无法知道当前指向哪一行。
使用
- 声明游标
DECLARE cursor_name CURSOR FOR select_statement;
cursor_name 为指定的游标的名称
select_statement是查询语句(只能是SELECT语句不能是存储过程或者函数的调用)
- 打开游标
OPEN cursor_name;
- 获取游标数据
FETCH cursor_name INTO variable1, variable2, ...;
从游标中获取一行数据，并存储到变量中
- 循环处理游标数据
使用WHILE循环
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE var1 datatype;
DECLARE var2 datatype;
DECLARE cursor_name CURSOR FOR SELECT_statement;
OPEN cursor_name;
WHILE NOT done DO
FETCH cursor_name INTO var1, var2;
IF NOT done THEN
-- 在这里执行您的操作，例如输出或其他处理
END IF;
END WHILE;
CLOSE cursor_name;
使用LOOP循环
LOOP
FETCH cursor_name INTO variable_list;
-- 处理获取的数据逻辑
IF done THEN
LEAVE cursor_loop;
END IF;
END LOOP;
- 关闭游标
CLOSE cursor_name;
例子
DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
-- 创建接收游标数据的变量
DECLARE sid INT;
DECLARE sname VARCHAR(10);
-- 创建总数变量
DECLARE sage INT;
-- 创建结束标志变量 DECLARE done INT DEFAULT false;
-- 创建游标
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;
CLOSE cur;
SELECT total;
END $
DELIMITER ;
-- 调用存储过程
call getTotal();
触发器
概念
触发器是一种数据库对象，在特定的数据库操作(例如插入、更新或删除等)发生时自动执行一系列预定义的动作或操作。触发器通常与表相关联。
类型:
按处理级别：
- 行级触发器
当每一行受到影响时触发，可以监视和操作每一行的数据
- 语句级触发器
当整个SQL语句执行完毕后触发，不会监视或操作每一行数据，而是针对整个语句的执行进行操作。
按触发时间：
- BEFORE触发器
在响应的数据库操作之前执行
- AFTER触发器
在响应的数据库操作之后执行
作用
- 数据完整性的约束
触发器可以用来实施复杂的数据完整性规则，确保数据库中的数据符合特定的要求 - 审计跟踪
可以记录对数据库的更改，用于审计和追踪目的 - 自动化任务
执行自动化任务，如更新相关数据或通知相关用户
使用
- 创建触发器
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
-- 触发器操作
END //
DELIMITER ;
BEFORE/AFTER：指定触发器的触发时机。
INSERT/UPDATE/DELETE：指定触发器要响应的事件类型，可以是插入（INSERT）、更新（UPDATE）或删除（DELETE）操作。
ON table_name：指定触发器要关联的表名。
FOR EACH ROW：表示针对每一行数据执行触发器操作。这是行级触发器的特征，意味着每次操作影响到的行都会触发触发器。
BEGIN … END;：在 BEGIN 和 END 之间是触发器的操作部分，您可以在这里编写需要执行的SQL语句或存储过程调用等操作。
为了避免和触发器操作中的分隔符冲突，和创建存储过程类似，改为//
- 修改触发器
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
-- 触发器操作
END //
DELIMITER ;
与创建触发器类似，相当于用新的定义覆盖原有的定义。
- 查看触发器
SHOW TRIGGERS
- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;
NEW和OLD
-
NEW
NEW关键字引用的是触发器操作修改行的新值，对应INSERT包含新行的值，对应UPDATE包含更新后行的值 -
OLD
OLD关键字引用的是触发器操作导致将要更新或者删除的行的旧值
使用时只需要NEW.column_name / OLD.column_name引用即可
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
