(十五)MySQL命令大全：以后再也不用担心忘记SQL该怎么写啦~-六虎

导言

本文为社区首发签约文章，14天内制止转载，14天后未获授权制止转载，侵权必究！

信任我们在编写SQL时一定有一个困扰，便是分明记住数据库中有个指令/函数，可以完结自己需求的功用，但偏偏不记住哪个指令该怎么写了，这时只能靠盲目的去百度，以此来寻找自己需求的指令。

时刻是最厉害的兵器，少年定会白首，鲜花亦会凋谢，沧海会演化桑田，高山也会化作平原。

而我们每一位开发者，作为人类也不例外，无法抵御时刻的流逝，其记忆力会跟着时刻逐渐推移不断下降，而MySQL中的指令/函数那么多，我们也并不能完全记住，所以关于前面的那种状况，在实践开发中也属常事，所以本章则会将一些常用的SQL指令/函数悉数罗列出来，今后当需求用到时只需回来查找即可。

其实在编撰《JVM成神路》这个专栏的时候，也曾出过一篇类似于的文章，名为《JVM参数大全》，其间首要罗列了JVM通用参数、内存各区域的调整参数、GC废物收回的相关参数、功用监控与调优等参数，本章则归于它的姊妹篇，但差异在于：主角从JVM换成了MySQL。

当我们今后需求运用某条指令/函数时，可以很好的利用这篇指令大全来辅佐您，方法有两种：

①按下Ctrl+F查找快捷键，查找关键词用于定位相应的指令。
②本文会以功用对一切指令进行分类，经过右侧的文章目录可按功用快捷调整指令方位。

当然，如若本文对你有些许帮助，那请不要忘了点赞支撑一下噢~

一、根底操作与库指令

首先来介绍一些关于MySQL根底操作的指令，以及操作数据库相关的指令，MySQL中的一切指令默许是以;分好完毕的，因而在履行时一定要记住带上分号，不然MySQL会以为你这条指令还未完毕，会持续等候你的指令输入，如下：

1.1、MySQL根底操作指令

net start mysql：Windows体系发动MySQL服务。
安装目录/mysql start：Linux体系发动MySQL服务。
- shutdown：后边的start换成这个，表明封闭MySQL服务。
- restart：换成restart表明重启MySQL服务。
ps -ef | grep mysql：Linux检查MySQL后台进程的指令。
kill -9 MySQL进程ID：强杀MySQL服务的指令。
mysql -h地址 -p端口 -u账号 -p：客户端衔接MySQL服务（需求二次输入暗码）。
show status;：检查MySQL运转状况。
SHOW VARIABLES like %xxx%;：检查指定的体系变量。
show processlist;：检查当时库中正在运转的一切客户端衔接/作业线程。
show status like "Threads%";：检查当时数据库的作业线程体系。
help data types;：检查当时版别MySQL支撑的一切数据类型。
help xxx：检查MySQL的帮助信息。
quit：退出当时数据库衔接。

1.2、MySQL库相关的指令

show databases;：检查现在MySQL中具有的一切库。
show engines;：检查当时数据库支撑的一切存储引擎。
use 库名;：运用/进入指定的某个数据库。
show status;：检查当时数据库的状况信息。
show grants;：检查当时衔接的权限信息。
show errors;：检查当时库中记载的过错信息。
show warnings：检查当时库抛出的一切警告信息。
show create database 库名;：检查创立某个库的SQL详细信息。
show create table 表名;：检查创立某张表的SQL详细信息。
show tables;：检查一个库中的一切表。
desc 表名;：检查一张表的字段结构。除开这种方法还有几种方法：
- describe 表名;：检查一张表的字段结构。
- show columns from 表名;：检查一张表的字段结构。
- explain 表名;：检查一张表的字段结构。
create database 库名;：新建一个数据库，后边还可以指定编码格局和排序规矩。
drop database 库名;：删去一个数据库。
ALTER DATABASE 库名 DEFAULT CHARACTER SET 编码格局 DEFAULT COLLATE 排序规矩：修正数据库的编码格局、排序规矩。

1.3、MySQL表相关的指令

关于MySQL表相关的指令，首先来聊一聊创立表的SQL指令，如下：

CREATE TABLE `库名`.`表名`  (
    字段称号1 数据类型(精度约束) [字段选项],
    字段称号2 数据类型(精度约束) [字段选项]
) [表选项];

关于表中的每个字段，都需求用,分割，但最终一个字段后边无需跟,逗号，一同创立表时，关于每个字段都有多个字段选项，关于一张表而言也有多个表选项，下面一同来看看。

字段选项（可以不写，不选运用默许值）：
- NULL：表明该字段可以为空。
- NOT NULL：表明改字段不答应为空。
- DEFAULT 默许值：刺进数据时若未对该字段赋值，则运用这个默许值。
- AUTO_INCREMENT：是否将该字段声明为一个自增列。
- PRIMARY KEY：将当时字段声明为表的主键。
- UNIQUE KEY：为当时字段设置仅有约束，表明不答应重复。
- CHARACTER SET 编码格局：指定该字段的编码格局，如utf8。
- COLLATE 排序规矩：指定该字段的排序规矩（非数值类型生效）。
- COMMENT 字段描绘：为当时字段增加补白信息，类似于代码中的注释。
表选项（可以不写，不选运用默许值）：
- ENGINE = 存储引擎称号：指定表的存储引擎，如InnoDB、MyISAM等。
- CHARACTER SET = 编码格局：指定表的编码格局，未指定运用库的编码格局。
- COLLATE = 排序规矩：指定表的排序规矩，未指定则运用库的排序规矩。
- ROW_FORMAT = 格局：指定存储行数据的格局，如Compact、Redundant、Dynamic....。
- AUTO_INCREMENT = n：设置自增列的步长，默以为1。
- DATA DIRECTORY = 目录：指定表文件的存储途径。
- INDEX DIRECTORY = 目录：指定索引文件的存储途径。
- PARTITION BY ...：表分区选项，后续讲《MySQL表分区》再细聊。
- COMMENT 表描绘：表的注释信息，可以在这儿增加一张表的补白。

整体看下来会发现选项还蛮多，下面贴个比方感受一下：

-- 在 db_zhuzi 库下创立一张名为 zz_user 的用户表
CREATE TABLE `db_zhuzi`.`zz_user`  (
    -- 用户ID字段：int类型、不答应为空、设为自增列、声明为主键
    `user_id` int(8) NOT NULL AUTO_INCREMENT PRIMARY "i_p_id" COMMENT '用户ID',
    -- 用户称号字段：字符串类型、运转为空、默许值为“新用户”
    `user_name` varchar(255)  NULL DEFAULT "新用户" COMMENT '用户名'
)
-- 存储引擎为InnoDB、编码格局为utf-8、字符排序规矩为utf8_general_ci、行格局为Compact
ENGINE = InnoDB 
CHARACTER SET = utf8 
COLLATE = utf8_general_ci 
ROW_FORMAT = Compact;

上述代码块中就贴出了一个创立表的比方，我们在创立表时可依据需求自行挑选需求的字段选项、表选项。

接下来一同来看看其他关于表操作的SQL指令，但关于增修正查的指令会放在后边讲。

show table status like 'zz_users'\G;：纵排输出一张表的状况信息。
alter table 表名表选项;：修正一张表的结构，如alter table xxx engine=MyISAM。
rename table 表名 to 新表名;：修正一张表的表名。
alter table 表名字段操作;：修正一张表的字段结构，操作如下：
- add column 字段名数据类型：向已有的表结构增加一个字段。
- add primary key(字段名)：将某个字段声明为主键。
- add foreing key 外键字段表名.字段名：将一个字段设置为另一张表的外键。
- add unique 索引名(字段名)：为一个字段创立仅有索引。
- add index 索引名(字段名)：为一个字段创立一般索引。
- drop column 字段名：在已有的表结构中删去一个字段。
- modify column 字段名字段选项：修正一个字段的字段选项。
- change column 字段名新字段名：修正一个字段的字段称号。
- drop primary key：移除表中的主键。
- drop index 索引名：删去表中的一个索引。
- drop foreing key 外键：删去表中的一个外键。
drop table if exists 表名：假如一张表存在，则删去对应的表。
truncate table 表名：清空一张表的一切数据。
create table 表名 like 要仿制的表名：仿制一张表的结构，然后创立一张新表。
create table 表名 as select * from 要仿制的表名：一同仿制表结构和数据创立新表。

1.4、表的剖析、检查、修正与优化操作

MySQL本身供给了一系列关于表的剖析、检查与优化指令：

①剖析表：剖析表中键的散布，如主键、仅有键、外键等是否合理。
②检查表：检查表以及表的数据文件是否存在过错。
③修正表：当一个表的数据或结构文件损坏时，可以修正表结构（仅支撑MyISAM表）。
④优化表：消除delete、update句子履行时造成的空间糟蹋。

剖析表

语法如下：

analyze [local | no_write_to_binlog] table 表名1;

其间的可选参数local、no_write_to_binlog代表是否将本条SQL记载进bin-log日志，默许状况下是记载的，加上这两个参数中的其间一个后则不会记载，履行作用如下：

假如Msg_text显现的是OK，则代表这张表的键不存在问题，存在问题的状况我这边就不模拟了，后边举例聊。

检查表

语法如下：

check table 表名1,表名2... [检查选项];

剖析、检查、优化、修正的指令都支撑一同操作多张表，不同的表之间只需用,逗号隔开即可。检查指令有多个可选项，如下：

quick：不扫描行数据，不检查链接过错，仅检查表结构是否有问题。
fast：只检查表运用完结后，是否正确封闭了表文件的FD文件描绘符。
changed：从上述检查过的方位开始，只检查被更改的表数据。
medium：检查行数据，搜集每一行数据的键值（主键、外键…），并核算校验和，验证数据是否正确。
extended：对每行数据的一切字段值进行检查，检查完结后可确保数据100%正确。

先来看看履行成果吧，如下：

这回的成果呈现了些许不同，Msg_text中呈现了一个Error信息，提示我们检查的zz_u表不存在，而关于一张存在的zz_users表，则回来OK，表明没有任何问题。

当然，这儿关于其他的检查选项就不做测验了，我们可以自行试验，比方把表的结构文件或数据文件，在本地翻开手动删去前面的一点点数据，然后再履行检查指令，其实你也可以观察到，提示“数据不完好”的信息（但需求先中止运转MySQL，而且用本地表测验，不要用线上表瞎搞）。

修正表

语法如下：

repair [local | no_write_to_binlog] table 表名 [quick] [extended] [use_frm];

值得一提的是，修正表的指令不支撑InnoDB引擎，仅支撑MyISAM、CSV、引擎，比方依据InnoDB引擎的表履行修正指令时，提示如下：

上述Msg_text信息翻译过来的意思是：挑选的表其引擎并不支撑修正指令。

InnoDB引擎其实也有修正机制，可以在my.ini/my.conf文件中加一行配置：[mysqld]innodb_force_recovery = 1，这样在发动时会强制康复InnoDB的数据。

上述这个修正机制默许是不敞开的，因为InnoDB不需求这个康复机制，究竟之前在《引擎篇》中聊过：InnoDB有完善的业务和耐久化机制，客户端提交的业务都会耐久化到磁盘，除非你人为损坏InnoDB的数据文件，不然根本上不会呈现InnoDB数据损坏的状况。

优化表

语法如下：

optimize [local | no_write_to_binlog] table 表名;

这儿值得一提的是：此优化非彼优化，并不意味着你的表存在功用问题，履行后它会主动调优，而是指铲除老数据，履行作用如下：

还记住之前在《MVCC机制》中聊过的躲藏字段嘛？其实删去一条数据本质上并不会立马从磁盘移除，而是会先改掉躲藏的删去标识位，履行这条优化指令后，MySQL会将一些现已delete过的数据彻底从磁盘删去，然后开释这些“废弃数据”占用的空间。

上面的履行成果显现：“现在表的数据现已是最新的了”，这是啥原因呢？因为我这张表中压根没有数据，哈哈哈，没有刺进过数据，天然也不会有删去数据的动作，因而就会呈现这个提示。

OK~，到这儿关于剖析表、检查表、修正表以及优化表就现已介绍清楚啦！其实这几个功用，在mysqlcheck东西中也有供给。

1.5、MySQL忘记暗码怎么办？

到这儿，关于一些MySQL根底指令、库表指令就打住了，最终再来讲一个比较有用的知识点：MySQL忘记暗码怎么办？关于这种状况其实也非常常见，哪忘记时该怎么处理呢？可以重置暗码！

①先停掉MySQL的后台服务：

Windows体系请履行：net stop mysql
Linux体系请履行：安装目录/mysql shutdown（kill强杀进程也可以）

②进入到MySQL安装目录下的bin文件夹内，履行mysqld --skip-grant-tables去掉衔接认证。

③因为上面关掉了衔接认证，接着输入mysql敲下回车，进入mysql终端指令行。

④输入use mysql;，进入MySQL本身的体系数据库，然后输入show tables;检查一切表。

⑤查询MySQL中注册的一切用户：select user,host,password from user;。

⑥运用update句子，更改root超级办理员的账号暗码，如下：

update user set password=password('123') where user="root" and host="localhost";

因为MySQL本身会用一张用户表来存储一切已创立的账号信息，衔接时的效验基准也是来自于该表中的数据，因而在这儿修正暗码后，再用新暗码登录即可！

假如不是root账号的暗码忘记了，则可以直接登录root账号修正其他用户的暗码，假如是root账号则依照上述流程操作。

完结之后可以用mysql -uroot -p123衔接一下，测验暗码是否被重置。

二、增修正查句子

2.1、根本的增修正查句子

刺进数据

增修正查俗称为CRUD，这也是MySQL运转之后履行次数最多的一类SQL句子，一同也是每位开发者写的最多的SQL句子，接下来则说说这块的句子，首先上台的是我们的几位老同伴，即insert、delete、update、select...这类一般SQL句子。

insert into 表名(字段名...) values(字段值...);：向指定的表中刺进一条数据。
insert into 表名(字段名...) values(字段值...),(...)...;：向表中刺进多条数据。
insert into 表名 set 字段名=字段值,...;：刺进一条数据，但只刺进某个字段的值。

假如要刺进一条完好的数据，字段名可以用*替代一切字段，除开上述两种刺进数据的根本方法外，还有几种批量刺进的方法，如下：

-- 运用insert句子批量刺进另一张表中查询的数据
insert into 表名(字段名...) select 字段名... from 表名...;
-- 运用replace句子来完结批量刺进
replace into 表名(字段名1,字段名2...) values(字段值....),(字段值...),...;

上述批量刺进数据的方法中，还可以经过replace关键字来完结刺进，它与insert有啥差异呢？答案在于它可以完结批量更新，运用replace关键字来刺进数据的表有必要要有主键，MySQL会依据主键值来决定新增或修正数据，当批量刺进的数据中，主键字段值在表中不存在时，则会向表中刺进一条相应的数据，而当刺进数据中的主键值存在时，则会运用新数据覆盖原有的老数据。

删去数据

delete from 表名;：删去一张表的一切数据。
delete from 表名 where 条件;：依据条件删去一条或多条数据。
truncate table 表名：清空一张表的一切数据。

修正数据

update 表名 set 字段名=字段值,...;：修正表中一切记载的数据。
update 表名 set 字段名=字段值,... where 条件;：依据条件修正一条或多条记载的数据。
replace 表名(字段名1,...) values(字段值...),...;：批量修正对应主键记载的数据。

查询数据

select * from 表名;：查询一张表的一切数据。
select * from 表名 where 条件;：依据条件查询表中相应的数据。
select 字段1,字段2... from 表名 where 条件;：依据条件查询表中相应数据的指定字段。
select 函数(字段) from 表名;：对查询后的成果集，进行某个函数的特别处理。

上述三种是最根本的查询方法，接着来看一些高档查询语法，如下：

-- 为查询出来的字段取别号
select 字段1 as 别号,... from 表名 where 条件;
select 字段1 别号,... from 表名;
-- 为查询出的表取别号
select * from 表名 as 别号;
-- 以多条件查询数据
select * from 表名 where 字段1=值1 and 字段2=值2 and ...; -- 一切条件都符合时才匹配
select * from 表名 where 字段1=值1 or 字段2=值2 or ...; -- 符合恣意条件的数据都会回来
-- =符号，可以依据状况换为>、<、>=、<=、!=、between and、is null、not is null这些
-- 对查询后的成果集运用函数处理
select 函数(字段) from 表名 where 条件;
-- 对查询条件运用函数处理
select * from 表名 where 函数(条件);
-- 含糊查询
select * from 表名 where 字段 like "%字符"; -- 查询字段值以指定字符完毕的一切记载
select * from 表名 where 字段 like "字符%"; -- 查询字段值以指定字符最初的一切记载
select * from 表名 where 字段 like "%字符%"; -- 查询字段值包括指定字符的一切记载
-- 依照多值查询对应行记载
select * from 表名 where 字段 in (值1，值2,...);
-- 依照多值查询相反的行记载
select * from 表名 where 字段 not in (值1，值2,...);
-- 依据多个字段做多值查询
select * from 表名 where (字段1,字段2...) in ((值1，值2,...),(...),...);
-- 只需求查询成果中的前N条数据
select * from 表名 limit N;
-- 回来查询成果中 N~M 区间的数据
select * from 表名 limit N,M;
-- 联合多条SQL句子查询（union all表明不去重，union表明对查询成果去重）
select * from 表名 where 条件
union all 
select * from 表名 where 条件;

当然，关于MySQL中支撑的函数稍后再展开聊，下面再聊聊一些其他的高档查询语法，如分组、过滤、子查询、相关查询等。

分组过滤、数据排序

写SQL句子时，有些需求往往无法经过最根本的查询句子来完结，因而就需求用到一些高档的查询语法，例如分组、过滤、排序等操作，接着先聊聊这个，语法如下：

-- 依据一个字段进行排序查询
select * from 表名 order by 字段名 asc; -- 按字段值正序回来成果集
select * from 表名 order by 字段名 desc; -- 按字段值倒序回来成果集
select * from 表名 order by 字段1 asc,字段2 desc; -- 依照多字段进行排序查询
-- 依据字段进行分组
select * from 表名 group by 字段1,字段2....;
-- 依据分组查询后的成果做条件过滤
select * from 表名 group by 字段1 having 条件;

实践上group by、having这些句子，更多的要合作一些聚合函数运用，如min()、max()、count()、sum()、avg()....，这样才干更符合业务需求，但关于聚合函数后边再介绍，先简略说说where、having的差异：

这两个关键字都是用来做条件过滤的，但where优先级会比group by高，因而当分组后需求再做条件过滤时，就无法运用where来做筛选，而having便是用来对分组后的成果做条件过滤的。查询句子中的各类关键字履行优先级为：from → where → select → group by → having → order by。

子查询

子查询也可以理解成是查询嵌套，是指一种由多条SQL句子组成的查询句子，语法如下：

-- 依据一条SQL句子的查询成果进一步做查询
select * from (select * from 表名 where 条件) as 别号 where 条件;
-- 将一条SQL句子的查询成果作为条件持续查询（只适用于子查询回来单值的状况）
select * from 表名 where 字段名 = (select 字段名 from 表名 where 条件);
-- 将一条SQL句子的查询成果作为条件持续查询（适用于子查询回来多值的状况）
select * from 表名 where 字段名 exists (select 字段名 from 表名 where 条件);
-- 上述的exists可以换为not exists，表明查询不包括相应条件的数据
-- 将一条SQL句子的多个查询成果，作为条件与多个字段进行规模查询
select * from 表名 where (字段1,字段2...) in (select 字段1,字段2... from 表名);

在上述子查询语法中，exists的作用和in大致相同，只不过not in时会触发全表扫描，而not exists仍旧可以走索引查询，因而通常状况下尽量运用not exists替代not in来查询数据。

三、MySQL数据库函数

MySQL中供给了丰富的函数支撑，包括数学函数、字符串函数、日期和时刻函数、条件判别函数、体系信息函数、加密函数、格局化函数等，经过这些函数，一方面可以简化业务的代码量，另一方面还能更好的完结各类特别业务需求，下来一同来聊聊MySQL支撑的函数。

3.1、数学函数

数学函数是MySQL中最常用的一类函数，首要用来处理一切数值类型的字段值，一同来看看。

abs(X)：回来X的绝对值，如传进-1，则回来1。
ln(X)：回来X的天然相对数。
log(X,Y)：回来以X的以Y为底的对数。
log10(X):回来以X基数为10的对数。
bin(X)：回来X的二进制值。
oct(X)：回来X的八进制值。
hex(X)：回来X的十六进制值。
mod(X,Y)：回来X除以Y的余数。
ceil(X) | ceiling(X)：回来不小于X的最小整数，如传入1.23，则回来2。
round(X)：回来X四舍五入的整数。
floop(X)：回来X向下取整后的值，如传入2.34，会回来2。
greatest(X1,X2....,Xn)：回来集合中的最大整数值。
least(X1,X2....,Xn)：回来集合中的最小整数值。
rand(N)：回来一个0~N``0~1之间的随机小数（不传参默许回来0~1之间的随机小数）。
sign(X)：传入正数，回来1；传入负数，回来-1；传入0，回来0。
pow(X,Y) | power(X,Y)：回来X的Y次方值。
pi()：回来四舍五入后的圆周率，3.141593。
sin(X)：回来X的正弦值。
asin(X)：回来X的横竖弦值。
cos(X)：回来X的余弦值。
acos(X)：回来X的反余弦值。
tan(X)：回来X的正切值。
atan(X)：回来X的横竖切值。
cot(X)：回来X的余切值。
radians(x)：回来x由角度转化为弧度的值。
degrees(x)：回来x由弧度转化为角度的值。
sqrt(X)：回来X的平方根。
exp(e,X)：回来e的x乘方的值。
truncate(X,N)：回来小数X保留N位精准度的小数。
format(x,y)：将x格局化位以逗号隔开的数字列表，y是成果的小数位数。
inet_aton(ip)：将IP地址以数字的方法展现。
inet_ntoa(number)：显现数字代表的IP地址。
......

3.2、字符串函数

ascii(C)：回来字符C的ASCII码。
length(S)：回来字符串的占位空间，传入“竹子爱熊猫”，回来15，一个汉字占位3字节。
bit_length(S)：回来字符串的比特长度。
concat(S1,S2,...)：合并传入的多个字符串。
concat_wa(sep,S1,S2...)：合并传入的多个字符串，每个字符串之间用sep距离。
position(str,s) | locate(str,s)：回来s在str中第一次呈现的方位，没有则回来0。
find_in_set(S,list)：回来字符串S在list列表中的方位。
insert(S1,start,end,S2)：运用S2字符串替换掉S1字符串中start~end的内容。
lcase(S) | lower(S)：将传入的字符串中一切大写字母转换为小写。
ucase(S) | upper(S)：将传入的字符串中一切小写字母转换为大写。
left(S,index)：从左面开始截取字符串S的index个字符。
right(S,index)：从右侧开始截取字符串S的index个字符。
trim(S)：删去字符S左右两侧的空格。
rtrim(S)：删去字符S右侧的空格。
replace(S,old,new)：运用new新字符替换掉S字符串中的old字符。
repeat(str,count)：将str字符串重复count次后回来。
substring(S,index,N)：截取S字符串，从index方位开始，回来长度为N的字符串。
reverse(S)：将传入的字符串反转，即传入Java，回来avaJ。
quote(str)：用反斜杠转移str中的英文单引号。
strcmp(S1,S2)：比较两个字符是否相同。
lpad(str,len,s)：对str字符串左面填充len个s字符。
rpad(str,len,s)：对str字符串右边填充len个s字符。
......

3.3、日期和时刻函数

curdate() | current_date()：回来当时体系的日期，如2022-10-21。
curtime() | current_time()：回来当时体系的时刻，如17:30:52。
now() | sysdate()：回来当时体系的日期时刻，如2022-10-21 17:30:59。
unix_timestamp()：获取一个数值类型的unix时刻戳，如1666348711。
from_unixtime()：将unix_timestamp()获取的数值时刻戳，格局化成日期格局。
month(date)：获取date中的月份。
year(date)：获取date中的年份。
hour(date)：获取date中的小时。
minute(date)：获取date中的分钟。
second(date)：获取date中的秒数。
monthname(date)：回来date中月份的英文称号。
dayname(date)：获取日期date是星期几，如Friday。
dayofweek(date)：获取date坐落一周的索引方位，周日是1、周一是2…周六是7。
week(date)：获取date是本年的第多少周。
quarter(date)：获取date坐落一年中的哪个季度（1~4）。
dayofyear(date)：获取date是本年的第多少天。
dayofmonth(date)：获取date是本月的第多少天。
time_to_sec(time)：将传入的时刻time转换为秒数，比方"01:00:00" = 3600s。
date_add(date,interval 时刻单位) | adddate(...)：将date与给定的时刻按单位相加。
date_sub(date,interval 时刻单位) | subdate(...)：将date与给定的时刻按单位相减。
addtime(date,time)：将date加上指定的时刻，如addtime(now(),"01:01:01")。
subtime(date,time)：将date减去指定的时刻。
datediff(date1,date2)：核算两个日期之间的距离天数。
last_day(date)：获取date日期这个月的最终一天。
date_format(date,format)：将一个日期格局化成指定格局，format可选项如下：
- %a：作业日的英文缩写（Sun~Sat）。
- %b：月份的英文缩写（Jan~Dec）。
- %c：月份的数字格局（1~12）。
- %M：月份的英文全称（January~December）。
- %D：带有英文后缀的数字月份（1th、2st、3nd....）。
- %d：一个月内的天数，双数方法（01、02、03....31）。
- %e：一个月内的天数，奇数方法（1、2、3、4....31）。
- %f：奇妙（000000~999999）。
- %H：一天内的小时，24小时的周期（00、01、02...23）。
- %h | %I：一天内的小时，12小时的周期（01、02、03...12）。
- %i：一小时内的分钟（00~59）。
- %j：一年中的天数（001~366）。
- %k：以24小时制显现时刻（00~23）。
- %l：以12小时制显现时刻（01~12）。
- %m：月份的数字方法，双数方法（01~12）。
- %p：一天内的时刻段（上午AM、下午PM）。
- %r：12小时制的时刻（12:01:09 AM）。
- %S | %s：秒数，双数方法（00~59）。
- %T：24小时制的时刻（23:18:22）。
- %U：一年内的周（00~53）。
time_format(time,format)：将一个时刻格局化成指定格局。
str_to_date(str,format)：将日期字符串，格局化成指定格局。
timestampdiff(unit,start,end)：核算两个日期之间距离的详细时刻，unit是单位：
- year：年。
- quarter：季度。
- month：月。
- week：周。
- day：天。
- hour：小时。
- minute：分钟。
- second：秒数。
- microsecond：奇妙。
weekday(date)：回来date坐落一周内的索引（0是周一…6是周日）。

3.4、聚合函数

聚合函数一般是会结合select、group by having筛选数据运用。

max(字段名)：查询指定字段值中的最大值。
min(字段名)：查询指定字段值中的最小值。
count(字段名)：计算查询成果中的行数。
sum(字段名)：求和指定字段的一切值。
avg(字段名)：对指定字段的一切值，求出平均值。
group_concat(字段名)：回来指定字段一切值组合成的成果，如下：
distinct(字段名)：关于查询成果中的指定的字段去重。

这儿略微介绍一个日常业务中碰到次数较多的需求：

select * from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 黑熊      | 男       | 8888     | 2022-09-17 23:48:29 |
|       8 | 猫熊      | 女       | 8888     | 2022-09-27 17:22:29 |
|       9 | 棕熊      | 男       | 0369     | 2022-10-17 23:48:29 |
+---------+-----------+----------+----------+---------------------+
-- 依据性别字段分组，然后显现各组中的一切ID
select 
    convert(
        group_concat(user_id order by user_id asc separator ",") 
    using utf8) as "分组计算" 
from `zz_users` group by user_sex;
+-------------+
| 分组计算    |
+-------------+
| 1,8         |
| 2,3,4,9     |
+-------------+

上述利用了group_concat()、group by完结了依照一个字段分组后，显现对应分组的一切ID。

3.5、操控流程函数

if(expr,r1,r2)：expr是表达式，假如建立回来r1，不然回来r2。
ifnull(v,r)：假如v不为null则回来v，不然回来r。
nullif(v1,v2)：假如v1 == v2，则回来null，假如不相等则回来V1。

-- if的用例
select if(user_id > 3,"√","") from zz_users;
-- ifnull的用例
select ifnull(user_id,"") from zz_users;
-- case语法1：
case <表达式>
   when <值1> then <操作>
   when <值2> then <操作>
   ...
   else <操作>
end;
-- 用例：判别当时时刻是星期几
select case weekday(now()) 
	when 0 then '星期一' 
	when 1 then '星期二' 
	when 2 then '星期三' 
	when 3 then '周四' 
	when 4 then '星期五' 
	when 5 then '星期六'
	else '星期天'
end as "今天是星期几？";
-- case语法2：
case
    when <条件1> then <指令>
    when <条件2> then <指令>
    ...
    else commands
end;
-- 用例：判别今天是星期几
select case
	when weekday(now()) = 0 then '星期一' 
	when weekday(now()) = 1 then '星期二' 
	when weekday(now()) = 2 then '星期三' 
	when weekday(now()) = 3 then '周四' 
	when weekday(now()) = 4 then '星期五' 
	when weekday(now()) = 5 then '星期六'
	else '星期天'
end as "今天是星期几？";

简略聊一下CASE语法，第一种语法就类似于Java中的switch，而第二种语法就类似于多重if，经过CASE语法可以让SQL更加灵敏，完结类似于存储进程的作业。

3.6、加密函数

password(str)：将str字符串以数据库暗码的方法加密，一般用在设置DB用户暗码上。
md5(str)：对str字符串以MD5不可逆算法方法加密。
encode(str,key)：经过key密钥对str字符串进行加密（对称加密算法）。
decode(str,key)：经过key密钥对str字符串进行解密。
aes_encrypt(str,key)：经过key密钥对str字符串，以AES算法进行加密。
aes_decrypt(str,key)：经过key密钥对str字符串，以AES算法进行解密。
sha(str)：核算str字符串的散列算法校验值。
encrypt(str,salt)：运用salt盐值对str字符串进行加密。
decrypt(str,salt)：运用salt盐值对str字符串进行解密。

3.7、体系函数

version()：查询当时数据库的版别。
connection_id()：回来当时数据库衔接的ID。
database() | schema()：回来当时衔接坐落哪个数据库，即use进入的库。
user()：查询当时的登录的一切用户信息。
system_user()：回来当时登录的一切体系用户信息。
session_user()：查询一切衔接的用户信息。
current_user()：查询当时衔接的用户信息。
charset(str)：回来当时数据库的编码格局。
collation(str)：回来当时数据库的字符排序规矩。
benchmark(count,expr)：将expr表达式重复运转count次。
found_rows()：回来最终一个select查询句子检索的数据总行数。
cast(v as 类型)：将v转换为指定的数据类型。

3.8、……

除开上述这些函数之外，其实在MySQL还有许多许多的函数，但现在几乎现已将一切常用的函数悉数列出来了，因而关于其他偏冷门一些的函数就不再介绍。当然，就算你需求的某个功用在MySQL中没有供给函数支撑，你也可以经过create function的方法自界说存储函数，其逻辑与上篇讲到的《MySQL存储进程》大致相同。

四、MySQL支撑的数据类型

这儿所谓的数据类型，也便是只在创立表时可以挑选的列字段类型，在MySQL中其实可以经过：

help data types;：检查当时版别支撑的一切数据类型。如下(MySQL5.1版别)：

总体可分为数值类型、字符/串类型、时刻/日期类型、其他类型四种，下面一同来聊聊吧。

4.1、数值类型

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。

4.2、字符串类型

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个字节。

一般在为列指定数据类型时，都会varchar(255)这样写，其实中心的这个数字约束的并不是字节长度，而是字符数量，比方varchar(255)，表明该列最大能存储255个字符。

4.3、时刻/日期类型

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

4.4、其他类型

json：MySQL5.7版别引进的，在此之前只能用字符串类型来存储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数据。
- .....：还有一些其他json类型的函数，这儿不再阐明，一般json类型用的较少。
enum(选项1,选项2...选项n)：新增数据时只能从已有的选项中挑选一个并刺进。
set(选项1,选项2...选项n)：新增数据时可以从已有的选项中挑选多个并刺进。
eometry、point、linestring、polygon：空间类型（触摸不多）。

略微解释一下enum、set类型，这两种类型就类似于平时的单选框和多选框，有必要从已有的选项中挑选，两者的差异在于：enum枚举类型只能挑选一个选项，而set集合类型可以挑选多个选项（其有用的比较少，多数状况下都是直接在客户端中处理）。

OK~，简略了解MySQL支撑的数据类型后，接着再来看看其他一些常用的SQL指令。

五、索引相关的指令

-- 创立一个一般索引（方法①）
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 fulltext index 索引名 ON 表名(列名);
-- 创立一个前缀索引
create index 索引名 ON 表名 (列名(索引键长度));
-- 创立一个空间索引
alter table 表名 add spatial key 索引名(列名);
-- 创立一个联合索引
create index 索引名 ON 表名 (列名1(索引键长度),列名2,...列名n);

上面将MySQL中创立各类索引的多种方法都列出来了，接着再聊聊索引检查、运用与办理的指令。

-- 检查一张表上的一切索引
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 条件....;

六、业务与锁相关的指令

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;：达观锁方法履行。

七、存储进程、存储函数与触发器

-- 创立一个存储进程
DELIMITER $
CREATE 
    PROCEDURE 存储进程称号(回来类型 参数名1 参数类型1, ....)
    [ ...... ]
BEGIN
    -- 详细组成存储进程的SQL句子....
END $
DELIMITER ;
-- 创立一个存储函数
DELIMITER $
CREATE 
    FUNCTION 存储函数称号(参数名1 参数类型1, ....)
    RETURNS 数据类型
[NOT] DETERMINISTIC statements
BEGIN
    -- 详细组成存储函数的SQL句子....
END $
DELIMITER ;
-- 创立一个触发器
CREATE TRIGGER 触发器称号
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW
    -- 触发器的逻辑（代码块）;
-- ------------- 用户变量与局部变量 ---------------
-- 界说、修正用户变量
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 值1 THEN
        -- 分支操作1....
    WHEN 值2 THEN
        -- 分支操作2....
    .....
    ELSE
        -- 分支操作n....
END CASE;
-- 第二种语法
CASE
    WHEN 条件判别1 THEN
        -- 分支操作1....
    WHEN 条件判别2 THEN
        -- 分支操作2....
    .....
    ELSE
        -- 分支操作n....
END CASE;
-- 循环：LOOP、WHILE、REPEAT
-- loop循环
循环称号:LOOP
    -- 循环体....
END LOOP 循环称号;
-- while循环
【循环称号】:WHILE 循环条件 DO
    -- 循环体....
END WHILE 【循环称号】;
-- repeat循环
【循环称号】:REPEAT
    -- 循环体....
UNTIL 完毕循环的条件判别
END REPEAT 【循环称号】;
-- 循环跳转
LEAVE 【循环称号】; -- 完毕某个循环体
ITERATE 【循环称号】; -- 跳出某个循环体，持续下次循环
-- ------------- 存储进程的游标 ---------------
-- ①声明（创立）游标
DECLARE 游标称号 CURSOR FOR select ...;
-- ②翻开游标
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 触发器称号;：删去某个指定的触发器。

当然，如若你对这块感兴趣，详细的教程可参阅上篇：《MySQL存储进程与触发器》。

八、MySQL用户与权限办理

create user 用户名@'IP' identified by 暗码;：创立一个新用户。
drop user 用户名@'IP';：删去某个用户。
set password = password(新暗码);：为当时用户设置新暗码。
set password for 用户名 = password(新暗码);：为指定用户设置新暗码（需求权限）。
alter user 用户名@'IP' identified by 新暗码;：运用root账号修正暗码。
mysqladmin -u用户名 -p旧暗码 password 新暗码;：运用mysqladmin东西更改用户暗码。
rename user 原用户名 to 新用户名;：对某个用户重命名。
show grants;：检查当时用户具有的权限。
show grants for 用户名;：检查指定用户具有的权限。
grant 权限1,权限2... on 库名.表名 to 用户名;：为指定用户颁发权限。
- 权限可选项：
  - insert：刺进表数据的权限。
  - delete：删去表数据的权限。
  - update：修正表数据的权限。
  - select：查询表数据的权限。
  - alter：修正表结构的alter权限。
  - alter routine：修正子程序（存储进程、函数、触发器）的alter权限。
  - create：创立表的create权限。
  - create routine：创立存储进程、存储函数、触发器的权限。
  - create temporary tables：创立暂时表的权限。
  - create user：创立/删去/重命名/授权用户的权限。
  - create view：创立视图的权限。
  - drop：删去表的权限。
  - execute：履行存储进程的权限。
  - file：导出、导入表数据的权限。
  - index：创立和删去索引的权限。
  - lock tables：获取表锁的权限。
  - process：查询作业线程的权限。
  - references：这个在MySQL中没有。
  - reload：请空表的权限。
  - replication clinet：获取主节点、从节点地址的权限。
  - replication slave：仿制主节点数据的权限。
  - show databases：检查一切数据库的权限。
  - show view：检查一切视图的权限。
  - shutdown：封闭数据库服务的权限。
  - super：修正主节点信息的权限。
  - all privileges：一切权限。
- usage：不颁发这些权限。其他权限悉数颁发。
- grant option：颁发这些权限，其他权限悉数不颁发。
- 权限规模可选项：
  - *.*：大局权限，表明该用户可对一切库、一切表进行增修正查操作。
  - 库名.*：单库权限，表明该用户可对指定库下的一切表进行增修正查操作。
  - 库名.表名：单表权限，表明该用户可对指定表进行增修正查操作。
revoke 权限1,权限2... on 库名.表名 from 用户名;：撤销指定用户的指定权限。
revoke all privileges from 用户名 with grant option;：撤销一个用户的一切权限。
flush privileges;：改写权限。
select user,password,host from mysql.user;：查询当时库中的一切用户信息。
MySQL8.0版别后推出的暗码办理机制：
- set persist default_password_lifetime=90;：设置一切用户的暗码在90天后失效。
- create user 用户@IP password expire interval 90 day;：创立用户时设置失效时刻。
- alter user 用户名@IP password expire interval 90 day;：设置指定用户暗码失效。
- alter user 用户名@IP password expire never;：设置指定用户的暗码永不失效。
- alter user 用户名@IP password expire default;：运用默许的暗码失效战略。上述给出了一系列的用户办理和权限办理的指令，最终略微提一下创立用户时的注意事项：

-- 创立一个名为 zhuzi 的用户
create user 'zhuzi'@'196.xxx.xxx.xxx' identified by "123456";

在创立用户时需求在用户称号后边跟一个IP地址，这个IP的作用是用来约束登录用户的机器，假如指定为详细IP，则表明只能由该IP的机器登录该用户，假如写%表明恣意设备都能运用该用户名登录衔接。

一同也最终提一嘴，MySQL关于一切的用户信息，都会放在自带的mysql库的user表中存储，因而也可以对表履行insert、delete、update、select操作，来完结办理用户的功用。

九、MySQL视图与暂时表

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 暂时表名;：清空某张暂时表的数据。

MySQL的暂时表本质上是一种特别的视图，被称为不可更新的视图，也便是暂时表只支撑查询数据，不支撑增修正操作，因而也可以经过创立视图的方法创立暂时表，在创立句子中加入temporary关键字即可，不指定默以为undedined，意思是主动挑选视图结构，一般为merge结构，表明创立一个支撑增修正查的视图。

十、数据的导出、导入与备份、还原

数据库的备份其实本质上便是指经过导出数据的方法，或许复制表文件的方法来制造数据的副本，数据康复/还原便是指在数据库毛病、反常、过错的状况下，经过导入原本的数据副本，将数据康复到正常状况，下面来介绍MySQL中供给的相关指令。

-- --------运用 mysqldump 东西做数据的逻辑备份（导出的是sql句子）-----------
-- 导出MySQL中悉数的库数据（运用--all-databases 或许 -A 参数）
mysqldump -uroot -p暗码 --all-databases > 备份文件名.sql
-- 导出MySQL中一部分的库数据（运用--databases 或许 -B 参数）
mysqldump -uroot -p暗码 --databases > 备份文件名.sql
-- 导出MySQL单库中的一部分表数据
mysqldump –u 用户名 –h主机名 –p暗码 库名[表名1,表名2...]> 备份文件名.sql
-- 导出MySQL单表的部分数据（运用 --where 参数）
mysqldump -u用户名 -p 库名 表名 --where="条件" > 备份文件名.sql
-- 扫除某些表，导出库中其他的一切数据（运用 --ignore-table 参数）
mysqldump -u用户名 -p 库名 --ignore-table=表名1,表名2... > 备份文件名.sql
-- 只导出表的结构（运用 --no-data 或许 -d 选项）
mysqldump -u用户名 -p 库名 --no-data > 备份文件名.sql
-- 只导出表的数据（运用 --no-create-info 或许 -t 选项）
mysqldump -u用户名 -p 库名 --no-create-info > 备份文件名.sql
-- 导出包括存储进程、函数的库数据（运用--routines 或许 -R选项）
mysqldump -u用户名 -p -R --databases 库名 > 备份文件名.sql
-- 导出包括事情（触发器）的库数据（运用 --events 或许 -E选项）
mysqldump -u用户名 -p -E --databases 库名 > 备份文件名.sql
-- --------运用 mysql 东西来康复备份的数据（导入xx.sql文件履行）-----------
-- 康复库等级的数据（包括了建库句子的状况下运用）
mysql -u用户名 -p < xxx.sql
-- 康复库中表等级的数据
mysql -u用户名 -p 库名 < xxx.sql
-- ----------以物理方法备份数据（导出的是表数据） ------------
-- 检查数据库导出数据的途径（假如没有则需在`my.ini/my.conf`中配置）
show variables like '%secure_file_priv%';
-- 导出一张表的数据为txt文件（运用 select ... into outfile 句子）
select * from 表名 into outfile "备份文件名.txt";
-- 导出一张表的数据为txt文件（运用 mysql 东西）
mysql -u用户名 -p --execute="select ...;" 库名 > "数据寄存目录/xxx.txt"
-- 导出一张表的结构和数据为sql、txt文件（运用 mysqldump -T 的方法）
mysqldump -u用户名 -p -T "数据寄存目录" 库名 文件名
-- 导出一张表的数据为txt文件，以竖排方法存储（运用 mysql –veritcal 的方法）
mysql -u用户名 -p -veritcal --execute="select ...;" 库名 > "数据寄存目录/xxx.txt"
-- 导出一张表的数据为xml文件（运用 mysql -xml 的方法）
mysql -u用户名 -p -xml --execute="select ...;" 库名 > "数据寄存目录/xxx.xml"
-- -----------经过物理数据文件康复数据----------------
-- 运用load data infile 的方法导入.txt 物理数据
load data infile "数据目录/xxx.txt" into table 库名.表名;
-- 运用 mysqlimport 东西导入xxx.txt物理数据
mysqlimport -u用户名 -p 库名 '数据寄存目录/xxx.txt' 
    --fields-terminatedby=',' 
    --fields-optionally-enclosed-by='\"'
-- 运用 mysqldump 东西搬迁数据
mysqldump –h 地址1 –u用户名 –p暗码 –-all-databases | mysql –h地址2 –u用户名 –p暗码

上述列出了一系列数据导出导入、备份康复、搬迁等指令，这些都是MySQL本身就支撑的方法，但这些自带的指令或东西，在一些状况下往往没有那么灵敏、便利，因而在实践状况下，可以适当结合第三方东西来完结，比方：

较大的数据需求做物理备份时，可以经过xtrabackup备份东西来完结。
MySQL5.5版别之前的MyISAM表，可以经过MySQLhotcopy东西做逻辑备份（速度最快）。
不同版别的MySQL可以运用XtraBackup备份东西来做数据搬迁。
MySQL、Oracle之间可以经过MySQL Migration Toolkit东西来做数据搬迁。
MySQL、SQL Server之间可以经过MyODBC东西来做数据搬迁。

当然，不管是MySQL本身供给的东西也好，亦或是第三方供给的东西也罢，因为本身就写死了逻辑，因而在有些场景下仍旧存在局限性，因而有时我们也需求写主动化脚本，以此来完结一些特别的需求。

十一、表分区相关的指令

-- 创立规模分区
create table `表名`(
	`xxx` xxx not null,
	 ....
) 
partition by range(xxx)(
	partition 分区名1 values less than (规模) data directory = "/xxx/xxx/xxx",
	partition 分区名2 values less than (规模) data directory = "/xxx/xxx/xxx",
	......
);
-- 创立枚举分区
create table `表名`(
	`xxx` xxx not null,
	 ....
)
partition by list(xxx)(
	partition 分区名1 values in (枚举值1,枚举值2...),
	partition 分区名2 values in (枚举值),
	......
);
-- 创立惯例哈希分区
create table `表名`(
	`xxx` xxx not null,
	 ....
)
partition by hash(xxx)
partitions 分区数量;
-- 创立线性哈希分区
create table `表名`(
	`xxx` xxx not null,
	 ....
)
partition by linear hash(xxx)
partitions 分区数量;
-- 创立Key键分区
create table `表名`(
	`xxx` xxx not null,
	 ....
)
partition by key(xxx)
partitions 分区数量;
-- 创立Sub子分区
create table `表名`(
	`xxx` xxx not null,
	 ....
) 
partition by range(父分区键)
subpartition by hash(子分区键)(
    partition 分区名1 values less than (规模1)(
        subpartition 子分区名1,
        subpartition 子分区名2,
        ......
    ),
    partition 分区名2 values less than (规模2)(
        subpartition 子分区名1,
        subpartition 子分区名2,
        ......
    ),
    ......
);
-- 查询一张表各个分区的数据量
select 
    partition_name as "分区称号",table_rows as "数据行数"
from 
    information_schema.partitions 
where 
    table_name = '表名';
-- 查询一张表父子分区的数据量
select 
    partition_name as "父分区称号",
		subpartition_name as "子分区称号",
		table_rows as "子分区行数"
from 
    information_schema.partitions 
where 
    table_name = '表名';
-- 查询MySQL中一切表分区的信息
select * from information_schema.partitions;
-- 查询一张表某个分区中的一切数据
select * from 表名 partition (分区名);
-- 关于一张已存在的表增加分区
alter table 表名 reorganize partition 分区名 into (
	partition 分区名1 values less than (规模) data directory = "/xxx/xxx/xxx",
	partition 分区名2 values less than (规模) data directory = "/xxx/xxx/xxx",
	......
);
-- 将多个分区合并成一个分区
alter table 表明 reorganize partition 分区名1,分区名2... into (
    partition 新分区名 values less than (规模)
);
-- 清空一个分区中的一切数据
alter table 表名 truncate partition 分区名;
-- 删去一个表的指定分区
alter table 表名 drop partition 分区名;
-- 重建一张表的分区
alter table 表名 rebuild partition 分区名;
-- 剖析一个表分区
alter table 表名 analyze partition 分区名;
-- 优化一个表分区
alter table 表名 optimize partition 分区名;
-- 检查一个表分区
alter table 表名 check partition 分区名;
-- 修正一个表分区
alter table 表名 repair partition 分区名;
-- 削减hash、key分区方法的 n 个分区
alter table 表名 coalesce partition n;
-- 将一张表的分区切换到另一张表
alter table 表名1 exchange partition 分区名 with table 表名2;
-- 移除一张表的一切分区
alter table 表名 remove partitioning;

十二、MySQL、InnoDB、MyISAM的参数

“参数，也被称之为MySQL的体系变量，这些变量是影响MySQL运转的关键，对每个参数做出不同调整，都有或许直接影响到线上数据库的功用，详细的完好体系参数可参阅《MySQL官网文档-体系变量》，官方供给的体系参数数量大致如下：

经过xpath的方法提取数据，大约可以得知MySQL体系变量大约一千个上下，因而这儿就不做过多的详细介绍，简略介绍一些较为常用的即可，其他详细的可参阅前面给出的官网链接。

但是要注意，虽说MySQL中有一千多个对外露出的体系参数，但并不是一切的参数都可以让用户调整，MySQL的体系参数分为了三类：
一类是由MySQL自己保护的参数，这类参数用户无法调整。
第二类是以配置文件的方法加载的参数，这类参数有必要在MySQL停机的状况下才干更改。
第三类是运转时的体系参数，这类是可以由用户去做动态调整的。

我们需求关心的重点便是第三类参数，那怎么观察这类参数呢？方法如下：

show global variables;：检查大局一切用户等级可以看到的体系变量。
show session variables; | show variables;：检查当时会话的一切体系变量。
show variables like '%关键字%';：运用含糊查询查找某个体系变量。

MySQL5.1与MySQL8.0版别的履行成果如下：

可以很明显的从成果中得知：MySQL5.1版别中存在278个体系变量，MySQL8.0版别中存在557个体系变量，这仅仅只是社区版，而在商业版的MySQL中，其体系参数会更多，下面调出一些重点来聊一聊。

max_connections：MySQL的最大衔接数，超出后新到来的衔接会堵塞或被回绝。
version：当时数据库的版别。
ft_min_word_len：运用MyISAM引擎的表中，全文索引最小查找长度。
ft_max_word_len：运用MyISAM引擎的表中，全文索引最大查找长度。
ft_query_expansion_limit：MyISAM中运用with query expansion查找的最大匹配数。
innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小查找长度。
innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大查找长度。
optimizer_switch：MySQL躲藏参数的开关。
skip_scan：是否敞开索引跳跃扫描机制。
innodb_page_size：InnoDB引擎数据页的巨细。
tx_isolation：业务的阻隔等级。
autocommit：业务主动提交机制。
innodb_autoinc_lock_mode：刺进意向锁的作业方法。
innodb_lock_wait_timeout：InnoDB锁冲突时，堵塞的超时时刻。
innodb_deadlock_detect：是否敞开InnoDB死锁检测机制。
innodb_max_undo_log_size：本地磁盘文件中，Undo-log的最大值，默许1GB。
innodb_rollback_segments：指定回滚段的数量，默以为1个。
innodb_undo_directory：指定Undo-log的寄存目录，默许放在.ibdata文件中。
innodb_undo_logs：指定回滚段的数量，默以为128个，也便是之前的innodb_rollback_segments。
innodb_undo_tablespaces：指定Undo-log分红几个文件来存储，有必要敞开innodb_undo_directory参数。
back_log：回滚日志的最大回撤长度（一条数据的最长版别链长度）。
innodb_undo_log_truncate：是否敞开Undo-log的紧缩功用，即日志文件超越一半时主动紧缩，默许封闭。
innodb_flush_log_at_trx_commit：设置redo_log_buffer的刷盘战略，默许每次提交业务都刷盘。
innodb_log_group_home_dir：指定redo-log日志文件的保存途径，默以为./。
innodb_log_buffer_size：指定redo_log_buffer缓冲区的巨细，默以为16MB。
innodb_log_files_in_group：指定redo日志的磁盘文件个数，默以为2个。
innodb_log_file_size：指定redo日志的每个磁盘文件的巨细约束，默以为48MB。
innodb_log_write_ahead_size：设置checkpoint刷盘机制每次落盘动作的巨细。
innodb_log_compressed_pages：是否对Redo日志敞开页紧缩机制，默许ON。
innodb_log_checksums：Redo日志完好性效验机制，默许敞开。
log_bin：是否敞开bin-log日志，默许ON敞开，表明会记载变更DB的操作。
log_bin_basename：设置bin-log日志的存储目录和文件名前缀，默以为./bin.0000x。
log_bin_index：设置bin-log索引文件的存储方位，因为本地有多个日志文件，需求用索引来确认现在该操作的日志文件。
binlog_format：指定bin-log日志记载的存储方法，可选Statment、Row、Mixed。
max_binlog_size：设置bin-log本地单个文件的最大约束，最多只能调整到1GB。
binlog_cache_size：设置为每条线程的作业内存，分配多大的bin-log缓冲区。
sync_binlog：操控bin-log日志的刷盘频率。
binlog_do_db：设置后，只会搜集指定库的bin-log日志，默许一切库都会记载。
log-error：error-log过错日志的保存途径和名字。
slow_query_log：设置是否敞开慢查询日志，默许OFF封闭。
slow_query_log_file：指定慢查询日志的存储目录及文件名。
general_log：是否敞开查询日志，默许OFF封闭。
general_log_file：指定查询日志的存储途径和文件名。
innodb_buffer_pool_size：InnoDB缓冲区的巨细。
innodb_adaptive_hash_index：是否敞开InnoDB的自适应哈希索引机制。
innodb_compression_level：调整紧缩的等级，可控规模在1~9，越高紧缩作用越好，但紧缩速度也越慢。
innodb_compression_failure_threshold_pct：当紧缩失败的数据页超出该份额时，会加入数据填充来减小失败率，为0表明制止填充。
innodb_compression_pad_pct_max：一个数据页中最大答应填充多少份额的空白数据。
innodb_log_compressed_pages：操控是否对redo-log日志的数据也敞开紧缩机制。
innodb_cmp_per_index_enabled：是否对索引文件敞开紧缩机制。
character_set_client：客户端的字符编码格局。
character_set_connection：数据库衔接的字符编码格局。
character_set_database：数据库的字符编码格局。
character_set_results：回来的成果集的编码格局。
character_set_server：MySQL-Server的字符编码格局。
character_set_system：体系的字符编码格局。
collation_database：数据库的字符排序规矩。
......：剩下的就不再列出来了，我们可依据查询出的变量名，去官网文档查询释义即可。

十三、MySQL常见的过错码

程序Bug、过错、反常…..，这些词汇天生与每位程序员挂钩，当一个程序呈现问题时，用户第一时刻想到的是这个渠道的程序员不可，而身为开发者逃不开一点是：又得背锅和加班解决问题！

而MySQL作为整个体系的后方大本营，天然也无法避免会呈现过错与反常，在MySQL内部其实界说了一系列过错码，当运转进程中产生过错，或履行句子时呈现反常时，一般状况下都会向客户端回来过错码以及过错信息。

但往往许多时候有些过错我们没见过，所以面临问题时难免有些束手无策，也正因为如此，本节将罗列MySQL中常见的过错码，当你碰到问题时可以直接经过过错码在此查找，以此来定位问题呈现的原因，以此进一步推导出问题的解决方案。

MySQL的过错信息由ErrorCode、SQLState、ErrorInfo三部分组成，即过错码、SQL状况、过错信息三部分组成，如下：

ERROR 1045 (28000): Access denied for user 'zhuzi'@'localhost' (using password: YES)

其间1045归于过错状况码，28000归于SQL状况，后边跟着的则是详细的过错信息，不过MySQL内部大致界说了两三千个过错码，其过错码的界说坐落include/mysqld_error.h、include/mysqld_ername.h文件中，而SQLState的界说则坐落include/sql_state.h文件中，一切的过错信息列表则坐落share/errmsg.txt文件中，因而我们感兴趣的可自行编译MySQL源码检查。

OK~，因为本章篇幅过长，就不再罗列细致的过错码概况了，究竟当呈现过错时，百度、谷歌才是最好的挑选，究竟从上面不只仅能找到过错码的含义，一同还能找到过错码的解决方案，但为了文章的完好性，再贴一位大佬的→《过错码大全》←的收拾：

通篇下来共计16.07W字，里边关于MySQL中90%+的过错码都罗列出来了，但我大致看了一些，有些过错码的描绘并不恰当，应该是直接依据share/errmsg.txt文件中的过错信息直接翻译过来的，我们有兴趣可以看看~

十四、MySQL指令大全总结

到这儿停止，关于MySQL中大部分常用的指令根本上都现已罗列出来啦！今后如若忘记某个函数称号、某条句子的语法等等，都可以直接在本章中快捷查找，但本篇仅仅只写出了根本的语法，实践数据库开发中往往还需求结合业务，来编写更为杂乱的SQL句子~。本章的首要目的是当成《MySQL指令手册》，本质上没有太多可以学习的知识点，但它却非常有用，可以协助我们的日常开发。

(十五)MySQL命令大全：以后再也不用担心忘记SQL该怎么写啦~

导言

一、根底操作与库指令

1.1、MySQL根底操作指令

1.2、MySQL库相关的指令

1.3、MySQL表相关的指令

1.4、表的剖析、检查、修正与优化操作

剖析表

检查表

修正表

优化表

1.5、MySQL忘记暗码怎么办？

二、增修正查句子

2.1、根本的增修正查句子

刺进数据

删去数据

修正数据

查询数据

分组过滤、数据排序

子查询

相关查询

三、MySQL数据库函数

3.1、数学函数

3.2、字符串函数

3.3、日期和时刻函数

3.4、聚合函数

3.5、操控流程函数

3.6、加密函数

3.7、体系函数

3.8、……

四、MySQL支撑的数据类型

4.1、数值类型

4.2、字符串类型

4.3、时刻/日期类型

4.4、其他类型

五、索引相关的指令

六、业务与锁相关的指令

七、存储进程、存储函数与触发器

八、MySQL用户与权限办理

九、MySQL视图与暂时表

十、数据的导出、导入与备份、还原

十一、表分区相关的指令

十二、MySQL、InnoDB、MyISAM的参数

十三、MySQL常见的过错码

十四、MySQL指令大全总结

相关文章

8 个关于提高你 Flutter 移动程序安全性的建议

如何设计一个高效的分布式日志服务平台

炼丹！训练 stable diffusion 来生成LoRA定制模型

Flutter版本从1.7.8到3.7.9的升级之旅

作者信息