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
中存储过程、存储函数与触发器的相关语法,接着再来聊聊管理的命令:
-
SHOW PROCEDURE STATUS;
:查看当前数据库中的所有存储过程。 -
SHOW PROCEDURE STATUS WHERE db = '库名' AND NAME = '过程名';
:查看指定库中的某个存储过程。 -
SHOW CREATE PROCEDURE 存储过程名;
:查看某个存储过程的源码。 -
ALTER PROCEDURE 存储过程名称 ....
:修改某个存储过程的特性。 -
DROP PROCEDURE 存储过程名;
:删除某个存储过程。 -
SHOW FUNCTION STATUS;
:查看当前数据库中的所有存储函数。 -
SHOW CREATE FUNCTION 存储过程名;
:查看某个存储函数的源码。 -
ALTER FUNCTION 存储过程名称 ....
:修改某个存储函数的特性。 -
DROP FUNCTION 存储过程名;
:删除某个存储函数。 -
SHOW TRIGGERS;
:查看当前数据库中定义的所有触发器。 -
SHOW CREATE TRIGGER 触发器名称;
:查看当前库中指定名称的触发器。 -
SELECT * FROM information_schema.TRIGGERS;
:查看MySQL
所有已定义的触发器。 -
DROP TRIGGER IF EXISTS 触发器名称;
:删除某个指定的触发器。