mysql索引、事务、锁、存储过程、存储函数与触发器

最新推荐文章于 2024-08-21 16:45:54 发布

小绵羊与狮子的爱情故事

最新推荐文章于 2024-08-21 16:45:54 发布

阅读量1.5k

点赞数 36

分类专栏：数据库sql mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_39142108/article/details/141357123

版权

数据库sql 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

1、索引相关

-- 创建一个普通索引（方式①）
create index 索引名ON表名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引（方式②）
altertable表名add index 索引名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引（方式③）
CREATETABLE tableName(
  columnName1 INT(8)NOTNULL,
  columnName2 ....,
.....,
  index [索引名称](列名(长度))
);
-- 后续其他类型的索引都可以通过这三种方式创建


-- 创建一个唯一索引
createunique索引名ON表名(列名(索引键长度)[ASC|DESC]);

-- 创建一个主键索引
altertable表名addprimary key 索引名(列名);

-- 创建一个全文索引
create fulltext index 索引名ON表名(列名);

-- 创建一个前缀索引
create index 索引名ON表名(列名(索引键长度));

-- 创建一个空间索引
altertable表名add spatial key 索引名(列名);

-- 创建一个联合索引
create index 索引名ON表名(列名1(索引键长度),列名2,...列名n);

上面将MySQL中创建各类索引的多种方式都列出来了，接着再聊聊索引查看、使用与管理的命令。

-- 查看一张表上的所有索引
show index from表名;

-- 删除一张表上的某个索引
drop index 索引名on表名;

-- 强制指定一条SQL走某个索引查找数据
select*from表名 force index(索引名)where.....;

-- 使用全文索引（自然搜索模式）
select*from表名wherematch(索引列) against('关键字');
-- 使用全文索引（布尔搜索模式）
select*from表名wherematch(索引列) against('布尔表达式'inboolean mode);
-- 使用全文索引（拓展搜索模式）
select*from表名wherematch(索引列) against('关键字'with query expansion);

-- 分析一条SQL是否命中了索引
explain select*from表名where 条件....;

2、事务与锁

start transaction; | begin; | begin work;：开启一个事务。
commit;：提交一个事务。
rollback;：回滚一个事务。
savepoint 事务点名称;：添加一个事务点。
rollback to 事务点名称;：回滚到指定名称的事务点。
release savepoint 事务点名称;：删除一个事务点。
select @@tx_isolation;：查询事务隔离级别（方式一）。
show variables like '%tx_isolation%';：查询事务隔离级别（方式二）。
set transaction isolation level 级别：设置当前连接的事务隔离级别。
set @@tx_isolation = "隔离级别";：设置当前会话的事务隔离级别。
set global transaction isolation level 级别;：设置全局的事务隔离级别，选项如下：
- read uncommitted：读未提交级别。
- read committed：读已提交级别。
- repeatable-read：可重复读级别。
- serializable：序列化级别。
show variables like 'autocommit';：查看自动提交事务机制是否开启。
set @@autocommit = 0|1|ON|OFF;：开启或关闭事务的自动提交。
select ... lock in share mode;：手动获取共享锁执行SQL语句。
select ... for share;：MySQL8.0之后优化版的共享锁写法。
select ... for update;：手动获取排他锁执行。
lock tables 表名 read;：获取表级别的共享锁。
lock tables 表名 write;：获取表级别的排他锁。
show open tables where in_use > 0;：查看目前数据库中正在使用的表锁。
flush tables with read lock;：获取全局锁。
unlock tables;：释放已获取的表锁/全局锁。
update 表名 set version=version+1 ... where... and version=version;：乐观锁模式执行。

3 、存储过程、存储函数与触发器

-- 创建一个存储过程
DELIMITER $
CREATE
PROCEDURE存储过程名称(返回类型参数名1参数类型1,....)
[......]
BEGIN
-- 具体组成存储过程的SQL语句....
END $
DELIMITER ;

-- 创建一个存储函数
DELIMITER $
CREATE
FUNCTION存储函数名称(参数名1参数类型1,....)
RETURNS数据类型
[NOT]DETERMINISTIC statements
BEGIN
-- 具体组成存储函数的SQL语句....
END $
DELIMITER ;

-- 创建一个触发器
CREATETRIGGER触发器名称
{BEFORE | AFTER}{INSERT|UPDATE|DELETE}ON表名
FOREACHROW
-- 触发器的逻辑（代码块）;

-- ------------- 用户变量与局部变量 ---------------
-- 定义、修改用户变量
set@变量名称=变量值;
-- 查询用户变量
select@变量名称;

-- 定义局部变量
DECLARE变量名称数据类型default默认值;
-- 为局部变量赋值（方式1）
SET变量名=变量值;
-- 为局部变量赋值（方式2）
SET变量名:=变量值;
-- 为局部变量赋值（方式3）
select查询结果字段into变量名from表名;

-- ------------- 流程控制 ---------------
-- if、elseif、else条件分支语法
IF 条件判断THEN
-- 分支操作.....
ELSEIF 条件判断 THWN
-- 分支操作.....
ELSE
-- 分支操作.....
END IF

-- case分支判断语句
-- 第一种语法
CASE变量
WHEN值1THEN
-- 分支操作1....
WHEN值2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;

-- 第二种语法
CASE
WHEN条件判断1THEN
-- 分支操作1....
WHEN条件判断2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;

-- 循环：LOOP、WHILE、REPEAT
-- loop循环
循环名称:LOOP
-- 循环体....
END LOOP 循环名称;

-- while循环
【循环名称】:WHILE 循环条件 DO
-- 循环体....
END WHILE 【循环名称】;

-- repeat循环
【循环名称】:REPEAT
-- 循环体....
UNTIL 结束循环的条件判断
END REPEAT 【循环名称】;

-- 循环跳转
LEAVE 【循环名称】;-- 结束某个循环体
ITERATE 【循环名称】;-- 跳出某个循环体，继续下次循环

-- ------------- 存储过程的游标 ---------------
-- ①声明（创建）游标
DECLARE游标名称CURSORFORselect...;

-- ②打开游标
OPEN游标名称;

-- ③使用游标
FETCH游标名称INTO变量名称;

-- ④关闭游标
CLOSE 游标名称;

在上面列出了MySQL中存储过程、存储函数与触发器的相关语法，接着再来聊聊管理的命令：