系列文档参考 MYSQL系列-整体架构介绍
本文紧接上一章# MYSQL系列-常用命令（一启动、库、表）

本章主要列举MYSQL日常使用过程中用到的一些命令，主要用于总结和后续使用时可以快速查阅

索引

普通索引

创建普通索引|前缀索引 create index 索引名 ON 表名 (列名(索引键长度) [ASC|DESC]);
修改普通索引 alter table 表名 add index 索引名(列名(索引键长度) [ASC|DESC]);
直接建表创建

CREATE TABLE tableName( 
columnName1 INT(8) NOT NULL, 
columnName2 ...., ....., 
index [索引名称] (列名(长度)) 
);

唯一索引

创建唯一索引 create unique 索引名 ON 表名 (列名(索引键长度) [ASC|DESC]);
修改唯一索引 alter table 表名 add primary key 索引名(列名);
创建多列唯一索引 create unique index 索引名 ON 表名 (列名1(索引键长度),列名2,...列名n);

其他索引

创建一个全文索引 create fulltext index 索引名 ON 表名(列名);
创建一个空间索引 alter table 表名 add spatial key 索引名(列名);

索引管理查看

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

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

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

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

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

视图与临时表

create view 视图名 as select ...;：对查询出的结果集建立一个指定名称的视图。
select * from 视图名;：基于某个已经创建的视图查询数据。
show create view 视图名;：查看某个已存在的视图其详细信息。
desc 视图名;：查看某个视图的字段结构。
alter view 视图名(字段1,...) as select 字段1...;：修改某个视图的字段为查询字段。
drop view 视图名;：删除某个视图。
create temporary table 表名(....);：创建一张临时表（方式1）。
create temporary view 表名 as select ...;：创建一张临时表（方式2）。
truncate table 临时表名;：清空某张临时表的数据。

存储过程、函数、触发器

存储过程

创建一个存储过程

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

举例如下：

CREATE PROCEDURE GetOrderCountByUser(IN userId INT, OUT orderCount INT)
BEGIN
    SELECT COUNT(*) INTO orderCount FROM orders WHERE user_id = userId;
END;

//执行举例
SET @userId = 1;
SET @orderCount = 0;
CALL GetOrderCountByUser(@userId, @orderCount);
SELECT @orderCount;

存储操作命令
- SHOW PROCEDURE STATUS;：查看当前数据库中的所有存储过程。
- SHOW PROCEDURE STATUS WHERE db = '库名' AND NAME = '过程名';：查看指定库中的某个存储过程。
- SHOW CREATE PROCEDURE 存储过程名;：查看某个存储过程的源码。
- ALTER PROCEDURE 存储过程名称 ....：修改某个存储过程的特性。
- DROP PROCEDURE 存储过程名;：删除某个存储过程。

函数

创建一个存储函数

DELIMITER $
CREATE 
    FUNCTION 存储函数名称(参数名1 参数类型1, ....)
    RETURNS 数据类型
[NOT] DETERMINISTIC statements
BEGIN

举例如下：

CREATE FUNCTION AddNumbers(a INT, b INT) RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = a + b;
    RETURN sum;
END;
//执行
SELECT AddNumbers(2, 3) AS result;

执行命令
- SHOW FUNCTION STATUS;：查看当前数据库中的所有存储函数。
- SHOW CREATE FUNCTION 存储过程名;：查看某个存储函数的源码。
- ALTER FUNCTION 存储过程名称 ....：修改某个存储函数的特性。
- DROP FUNCTION 存储过程名;：删除某个存储函数。

触发器

创建一个触发器

CREATE TRIGGER 触发器名称 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 FOR EACH ROW

事务命令

事务相关

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;：乐观锁模式执行。

支持数据类型

数值

tinyint：小整数类型，占位1Bytes，取值范围-128~127。
smallint：中整数类型，占位2Bytes，取值范围-32768~32767。
mediumint：中大整数类型，占位3Bytes，取值范围-8388608~8388607。
int | integer：常用整数类型，占位4Bytes，取值范围-2147483548~2147483647。
bigint：超大整数类型，占位8Bytes，取值范围-9223372036854775808~9223372036854775807。
float：单精度浮点数类型，占位4Bytes，取值范围-3.4E+38 ~ 3.4E+38。
double：双精度浮点数类型，占位8Bytes，取值范围-1.7E-308～1.7E+308。
decimal(m,d)：小数类型，占位和取值范围都依赖m、d值决定，m是小数点后面的精度，d是小数点前面的标度。
bit(m)：存储位值，可存储m个比特位，取值范围是1~64。

字符串

char：定长字符串类型，存储空间0~255Bytes。
varchar：变长字符串类型，存储空间0~65535Bytes。
tinyblob：二进制短字符串类型，存储空间0~255Bytes。
tinytext：短文本字符串类型，存储空间0~255Bytes。
blob：二进制长字符串类型，存储空间0~65535Bytes。
text：长文本字符串类型，存储空间0~65535Bytes。
mediumblob：二进制大字符串类型，存储空间0~16777215Bytes。
mediumtext：大文本字符串类型，存储空间0~16777215Bytes。
longblob：二进制超大字符串类型，存储空间0~4294967295Bytes。
longtext：超大文本字符串类型，存储空间0~4294967295Bytes。
binary(m)：定长字符串类型，存储空间为M个字符。
varbinary(m)：定长字符串类型，存储空间为M个字符+1个字节。

日期

date：日期类型，占位3Bytes，格式为YYYY-MM-DD。
time：时间类型，占位3Bytes，格式为hh:mm:ss。
year：年份类型，占位1Bytes，格式为YYYY。
datetime：日期时间类型，占位8Bytes，格式为YYYY-MM-DD hh:mm:ss。
timestamp：时间戳类型，占位4Bytes，格式为YYYYMMDDhhmmss，最大可精确到微妙。

JSON

json_array(...)：存储一个json数组的数据。
json_array_insert(字段,'$[下标]',"值")：在指定的json数组下标位置上插入数据。
json_object(...)：存储一个json对象。
json_extract(字段,'$.键')：查询键为某个值的所有数据。
json_search(....)：通过值查询键。
json_keys(字段)：获取某个字段的所有json键。
json_set(字段,'$.键',"值")：更新某个键的json数据。
json_replace(...)：替换某个json中的数据。
json_remove(字段,'$.键')：删除某个json数据。

MYSQL系列-常用命令二（索引、视图、存储过程、事务、数据类型）

索引

普通索引

唯一索引

其他索引

索引管理查看

视图与临时表

存储过程、函数、触发器

存储过程

函数

触发器

事务命令

事务相关

设置事务级别

自动提交

锁相关

支持数据类型

数值

字符串

日期

JSON