MySQL12 - MySQL命令大全

SQL命令大全

一：基础操作与库命令

1：MySQL基础操作命令

1.1：服务的启动，关闭和重启

• windows -> net start/shutdown/restart mysql

• linux -> 安装目录/mysql start/shutdown/restart

1.2：Linux查看Mysql后台进程命令

ps -ef | grep mysql

1.3：强杀Mysql服务的命令

kill -9 [MySQL的pid]

1.4：客户端连接mysql服务

mysql -h 地址，-p 端口 -u 用户名 -p

🎉 --> 下面的命令是进入mysql客户端后的命令

• 查看MySQL客户端的运行状态：show status
  
• 查看指定的系统变量：show variables like %xxx%

• 查看当前库中正在运行的所有客户端连接/工作线程：show processList
  
• 查看当前数据库的工作线程系统：show status like "Threads%"

• 查看MySQL的帮助信息：help xxx，例如：help 'select'就可以看到select语法的完整用法表述

• 退出当前数据库连接：quit

2：库相关命令

2.1：查看目前MySQL拥有的所有的库

show databases; // 查看所有的mysql所有的库

2.2：查看当前数据库支持的所有存储引擎

show engines; // 产看当前的数据库支持的所有的存储引擎

2.3：指定进入指定数据库

user [database_name]

2.4：查看当前数据库的状态信息

show status;

2.5：查看当前连接的权限信息

show grants

2.6：查看当前库中记录的错误信息

show errors;

2.7：查看当前库抛出的所有警告信息

show warnings;

2.8：查看创建某个库的SQL详细信息

show create database database_name;

2.9：查看一个库的所有的表

show tables;

2.10：新建一个数据库

-- 后面还可以指定编码格式和排序规则
create database 库名

2.11：删除一个数据库

drop database 库名

2.12：修改数据库的编码格式、排序规则

alter database 库名 default character set 编码格式 default collate 排序规则

3：表相关命令

3.1：创建数据表

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 ...：表分区选项
  • COMMENT 表描述：表的注释信息，可以在这里添加一张表的备注。

-- 在 spring 库下创建一张名为 user 的用户表
create table `springboot`.`user`  (
    -- 用户ID字段：int类型、不允许为空、设为自增列、声明为主键
    `user_id` int(8) not null auto_increment primary "i_p_id" commit '用户ID',
    -- 用户名称字段：字符串类型、运行为空、默认值为“新用户”
    `user_name` varchar(255)  null default "新用户" commit '用户名'
)
-- 存储引擎为InnoDB、编码格式为utf-8、字符排序规则为utf8_general_ci、行格式为Compact
engine = InnoDB 
character set = utf8 
collate = utf8_general_ci 
row_format = Compact;

3.2：查看一张表的字段结构

# 下面四种结构都可以查看表的字段结构
desc 表名
describe 表名
show columns from 表名
explain 表名

3.3：纵排输出一张表的状态信息

show table status like 'table_name';

3.4：修改一张表的结构

alter table 表名 表选项

3.5：修改一张表的表名

rename table [old_table_name] to [new_table_name]

3.6：修改一张表的字段结构(重)

alter table [table_name] [option];

字段操作如下：

操作语句	含义
add column 字段名称 字段的数据类型	向已有的表结构添加一个字段
add primary key(字段名称)	将某个字段声明为主键
add foreign key 外键字段 表名.字段名称	将一个字段设置为另一张表的外键
add unique index 索引名称(字段名称)	为一个字段创建唯一索引
add index 索引名称(字段名称)	为一个字段创建一个普通索引
modify column 字段名称 字段选项	修改一个字段的字段选项
change cloumn 字段名称 新的字段名称	修改一个字段的字段名称
drop cloumn 字段名称	在已有的表结构中删除一个字段
drop primary key	移除表中的主键
drop index 索引名称	删除表中的一个索引
drop freign key 外键	删除表中的一个外键

3.7：删除表

drop table if exists 表名

3.8：清空表数据

truncate table 表名

3.9：复制一张表的结构，然后创建一张新表

create table 表名 like 要复制的表名

3.10：同时复制表结构和数据创建新表

create table 表名 as select * from 要复制的表名

3.11：分析表

analyze [local | no_write_to_binlog] table 表名1;

其中的可选参数local、no_write_to_binlog代表是否将本条SQL记录进bin-log日志

默认情况下是记录的，加上这两个参数中的其中一个后则不会记录

如果Msg_text显示的是OK，则代表这张表的键不存在问题。其他状态是可能存在问题，后面详解

3.12：检查表

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

分析、检查、优化、修复的命令都支持同时操作多张表，不同的表之间只需用,逗号隔开即可。检查命令有多个可选项，如下：

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

3.13：修复表

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

⚠️ 修复表不支持InnoDB引擎，仅支持MyISAM、CSV

InnoDB怎么办？

InnoDB引擎其实也有修复机制

可以在my.ini/my.conf文件中加一行配置：[mysqld]innodb_force_recovery = 1

这样在启动时会强制恢复InnoDB的数据。

上述这个修复机制默认是不开启的，因为InnoDB不需要这个恢复机制，InnoDB有完善的事务和持久化机制，客户端提交的事务都会持久化到磁盘

除非你人为损坏InnoDB的数据文件，否则基本上不会出现InnoDB数据损坏的情况。

3.14：优化表

optimize [local | no_write_to_binlog] table 表名;

🎉 这个优化不是指性能优化，而是指清除老数据

这个就和MVCC中的隐藏字段中的delete_flag有关了

执行这条优化命令后，MySQL会将一些已经delete过的数据彻底从磁盘删除，从而释放这些“废弃数据”占用的空间。

3.15：忘记密码怎么办

1. 先停掉MySQL的后台服务
   • Windows：net stop mysql
   • Linux：安装目录/mysql shutdown（kill强杀进程也可以）
2. 进入到MySQL安装目录下的bin文件夹内，执行mysqld --skip-grant-tables去掉连接认证。
3. 接着输入mysql敲下回车，进入mysql终端命令行。
4. 输入use mysql;，进入MySQL自身的系统数据库，然后输入show tables;查看所有表。
5. 查询MySQL中注册的所有用户：select user,host,password from user;。
6. 使用update语句，更改root超级管理员的账号密码，如下：

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

因为MySQL连接时的效验基准也是来自于该表中的数据，因此在这里修改密码后，再用新密码登录即可！

🎉 如果不是root账号的密码忘记了，则可以直接登录root账号修改其他用户的密码，如果是root账号则按照上述流程操作。

🎉 完成之后可以用mysql -u root -p 123连接一下，测试密码是否被重置。

二：CURD相关命令

1：插入数据相关

1.1：向指定的表中插入一条数据

insert into 表名(字段名1, 字段名2...) values(字段值1, 字段值2...)

1.2：向指定的表中插入多条数据

insert into 表名(字段名1, 字段名2...) values(字段值1, 字段值2...), (字段值1, 字段值2...), ...

🎉 如果要插入一条完整的数据，字段名可以用*代替所有字段，不过还是推荐写

1.3：插入一条数据，但只插入某个字段的值

insert into 表名 set 字段名 = 字段值, 字段名 = 字段值

1.4：其他批量插入

-- 使用insert语句批量插入另一张表中查询的数据
insert into 表名(字段名...) select 字段名... from 表名...;

-- 使用replace语句来实现批量插入
-- 当批量插入的数据中主键字段值在表中不存在时，则会向表中插入一条相应的数据
-- 当插入数据中的主键值存在时，则会使用新数据覆盖原有的老数据
replace into 表名(字段名1,字段名2...) values(字段值....),(字段值...),...;

2：删除数据相关

2.1：删除一张表的所有数据

delete from 表名

2.2：根据条件删除一条或多条数据

delete from 表名 where 条件

2.3：清空一张表的所有数据

truncate table 表名

3：修改数据相关

3.1：修改表中所有记录的数据

update table_name set 字段名 = 字段值，字段名 = 字段值，...;

3.2：根据条件修改一条或多条记录的数据

update 表名 set 字段名 = 字段值，字段名 = 字段值，... where 条件;

3.3：批量修改对应主键记录的数据

replace 表名(字段名1,...) values(字段值...),...;

4：基本查询相关

4.1：基本查询

select * from table_name;
select * from table_name where xx;
select field1, field2... from table_name where xx;

4.2：对查询后的结果集，进行某个函数的特殊处理

select 函数(字段) from table_name where xx;

4.3：别名设置

select 字段1 as 别名,... from 表名 where 条件;
select 字段1 别名,... from 表名;

select * from 表名 as 别名;

4.4：多条件查询数据

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这些

4.5：对查询条件使用函数处理

select * from 表名 where 函数(条件);

4.6：模糊查询

select * from 表名 where 字段 like "%字符";  -- 查询字段值以指定字符结尾的所有记录
select * from 表名 where 字段 like "字符%";  -- 查询字段值以指定字符开头的所有记录
select * from 表名 where 字段 like "%字符%"; -- 查询字段值包含指定字符的所有记录

4.7：多值查询相关

-- 按照多值查询对应行记录
select * from 表名 where 字段 in (值1，值2,...);
-- 按照多值查询相反的行记录
select * from 表名 where 字段 not in (值1，值2,...);

4.8：查询指定范围的数据

-- 只需要查询结果中的前N条数据
select * from 表名 limit N;

-- 返回查询结果中 N~M 区间的数据
select * from 表名 limit N,M;

4.9：联合多条查询

-- 联合多条SQL语句查询（union all表示不去重，union表示对查询结果去重）
select * from 表名 where 条件
union all 
select * from 表名 where 条件;

5：高级查询

5.1：数据排序

-- 基于一个字段进行排序查询
select * from 表名 order by 字段名 asc;          -- 按字段值正序返回结果集
select * from 表名 order by 字段名 desc;         -- 按字段值倒序返回结果集
select * from 表名 order by 字段1 asc,字段2 desc; -- 按照多字段进行排序查询

5.2：分组过滤

-- 基于字段进行分组
select * from 表名 group by 字段1,字段2....;

-- 基于分组查询后的结果做条件过滤having
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。

5.3：子查询

-- 基于一条SQL语句的查询结果进一步做查询
select * from (select * from 表名 where 条件) as 别名 where 条件;

-- 将一条SQL语句的查询结果作为条件继续查询（只适用于子查询返回单值的情况）
select * from 表名 where 字段名 = (select 字段名 from 表名 where 条件);


-- 将一条SQL语句的查询结果作为条件继续查询（适用于子查询返回多值的情况）
-- exists是一个用于检查子查询结果是否存在的操作符。它返回一个布尔值，如果子查询返回结果，则返回1（真），否则返回0（假）
select * from 表名 where exists (select 字段名 from 表名 where 条件);
-- 上述的exists可以换为not exists，表示查询不包含相应条件的数据，例如查找存在订单的顾客
select * from Customers  
where exists (select * from Orders where Orders.customer_id = Customers.customer_id);


-- 将一条SQL语句的多个查询结果，作为条件与多个字段进行范围查询
select * from 表名 where (字段1,字段2...) in (select 字段1,字段2... from 表名);

exists & in

在上述子查询语法中，exists的作用和in大致相同，只不过not in时会触发全表扫描，而not exists依旧可以走索引查询

因此通常情况下尽量使用not exists代替not in来查询数据

5.4：关联查询 - 交叉连接

默认把前一张表的每一行数据与后一张表的所有数据做关联查询

select * from 表1,表2...; -- 这种方式默认采用交叉连接的方式
select * from 表1 cross join 表2; -- 显式声明采用交叉连接的方式

5.5：关联查询 - 内连接

只返回两张表条件都匹配的数据

-- 隐式的内连接写法
select * from 表1,表2... where 表1.字段 = 表2.字段 ...; 
-- 等值内连接
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 = 别名2.字段;
-- 不等式内连接
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 < 别名2.字段;

5.6：关联查询 - 外连接

• 左外连接：左表为主，右表为次，无论左表在右表是否匹配，都返回左表数据，缺失的右表数据显示NULL
• 右外连接：和左连接相反，右表为主，左表为次，永远返回右表的所有数据
• 全外连接：两张表没有主次之分，每次查询都会返回两张表的所有数据，不匹配的显示NULL【MySQL不支持，需要用union all】

-- 左外连接
select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

-- 右外连接
select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

-- 全外连接
select * from 表1 left join 表2 on 表1.字段 = 表2.字段
union all
select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

多表联查

-- 继续拼接查询两张以上的表
select * from 表1 left join 表2 on 表1.字段 = 表2.字段 left join 表3 on 表2.字段 = 表3.字段;

-- 通过隐式连接的方式，查询两张以上的表
select * from 表1,表2,表3... where 表1.字段 = 表2.字段 and 表1.字段 = 表3.字段...;

-- 通过子查询的方式，查询两张以上的表
select * from (
    表1 as 别名1 
    left join 表2 as 别名2 
    on 别名1.字段 = 别名2.字段
) 
left join 表3 as 别名3 
on 别名1.字段 = 别名3.字段;

三：库函数

1：数学函数

主要用来处理所有数值类型的字段值

函数名称	含义
abs(x)	返回x的绝对值
ln(x)	返回x的自然相对数【以e为底】
log(x, y)	返回以x为底，y的对数
log10(x)	返回以10为底，x的对数
bin(x)	返回x的二进制值
oct(x)	返回x的八进制值
hex(x)	返回x的16进制值
mod(x, y)	返回x % y
ceil(x) or ceiling(x)	返回大于等于x的最小整数
floor(x)	返回小于等于x的最大整数
round(x)	返回x的四舍五入值
greatest(x1, x2, x3…)	返回集合中的最大整数
least(x1, x2, x3…)	返回集合中的最小整数
rand(N)	返回0-N之间的随机小数，不传是0-1之间随机小数
sign(x)	传入正数，返回1，传入0，返回0， 传入负数，返回-1
pow(x, y) or power(x, y)	返回x^y
pi()	返回π的后六位3.14159
sin(x)/asin(x)/cos(x)/acos(x)	三角函数
tan(x)/atan(x)/cot(x)	三角函数
radians(x) / degrees(x)	角度->弧度、弧度->角度
sqrt(x)	返回x的平方根
exp(x)	返回e^x
truncate(x, N)	返回小数x返回N位精确度的小数
format(x, y)	将x格式化位以逗号隔开的数字列表，y是结果的小数位数。
inet_aton(x_ip)	将x_ip地址用数字的形式展现
inet_ntoa(number)	显示数字代表的ip地址

2：字符串函数

函数名称	含义
acsii(x)	返回字符x的ACSII码
length(x)	返回字符串x的占位空间，一个汉字占三位
bit_length(x)	返回字符串的比特长度
concat(x1, x2…)	合并传入的多个字符串
position(str, s)	返回s在str中第一次出现的位置，没有为0
find_in_set(s, list)	返回字符串s在list中的位置
insert(s1, start, end, s2)	将s1的start-end位置换成s2
lcase(x) / lower(x)	将传入的字符串中所有大写字母转换为小写
ucase(x) / upper(x)	将传入的字符串中所有小写字母转换为大写
left(s, index)	从左侧开始截取字符串s的index个字符
right(s, index)	从右侧开始截取字符串s的index个字符
trim(x)	删除字符s左右两侧的空格。
rtrim(x)	删除字符s右侧的空格。
replace(s, old, new)	使用new新字符替换掉s字符串中的old字符。
repeat(str, count)	将字符串str重复count次后返回
reverse(s)	反转字符串s
quote(s)	用反斜杠转义s中的英文单引号。
strcmp(s1, s2)	比较两个字符串是否相同
lpad/rpad(str, len, s)	对str左/右边填充len个s字符

3：日期和时间函数

函数名称	含义
curdate() or current_date()	返回当前的系统日期
curtime() or current_time()	返回当前的系统时间
now() or sysdate()	返回当前的系统日期时间
unix_timestamp()	获取一个数值类型的unix时间戳
from_unixtime()	将数值时间戳，格式化成日期格式
year(d)/month(d)/week(d)/hour(d)/minute(d)/second(d)	获得时间d的各个部分
monthname(d)	返回d中的英文名称
dayname(d)	获取日期d是星期几
dayofyear(d)/dayofmonth(d)/dayofweek(d)	获取d位于一年/月/周的索引的位置
quarter(d)	d位于一年种的哪一个季度(1-4)
time_to_sec(time)	将传入的时间time转成秒数
date_add()/date_sub	将date和给定的时间相加减
addtime(date,time)/subtime(date,time)	将date加/减指定时间
datediff(date1, date2)	计算两个日期之间的间隔天数
last_day(date)	获取date所在月的最后一天
date_format(date,format)	日期格式化成为指定的形式
time_format(time,format)	时间格式化成为指定的形式
str_to_date(str,format)	将日期字符串，格式化成指定格式
timestampdiff(unit,start,end)	计算两个日期之间间隔的具体时间
weekday(date)	返回date位于一周内的索引（0是周一)

🎉 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）。

🎉 timestampdiff(unit,start,end)中unit可选单位如下：

• year：年。
• quarter：季度。
• month：月。
• week：周。
• day：天。
• hour：小时。
• minute：分钟。
• second：秒数。
• microsecond：微妙。

4：聚合函数

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

函数名称	含义
max(字段名)/min(字段名)	查询指定字段值中的最大/小值
count(字段名)	统计查询结果中的行数
sum(字段名)	求和指定字段的所有值
avg(字段名)	对指定字段的所有值，求出平均值
distinct(字段名)	对于查询结果中的指定的字段去重
group_concat(字段名)	返回指定字段所有值组合成的结果

5：控制流程函数

函数名称	含义
if(expr,r1,r2)	expr是表达式，如果成立返回r1，否则返回r2
ifnull(v, r)	如果v不为null则返回v，否则返回r
nullif(v1, v2)	如果v1 == v2，返回null，否则返回v1

6：加密函数

函数名称	含义
password(str)	将str字符串以数据库密码的形式加密，一般用在设置DB用户密码上。
md5(str)	对str字符串以MD5不可逆算法模式加密
encode(str, key)/decode(str, key)	对称加密/解密，key是秘钥
aes_encrypt(str, key) aes_decrypt(str, key)	用AES算法加密和解密，key是秘钥
sha(str)	计算str字符串的散列算法校验值。
encrypt(str,salt)/decrypt(str,salt)	用盐值对字符串加密解密

7：系统函数

函数名称	含义
version()	当前数据库的版本
connection_id()	返回当前数据库连接的id
database() | schema()	返回当前连接位于哪个数据库
user()	查询当前的登录的所有用户信息
system_user()/session_user()/current_user()	系统用户，连接用户，当前连接用户
charset(str)	返回当前数据库的编码格式
collation(str)	返回当前数据库的字符排序规则
benchmark(count,expr)	将expr表达式重复运行count次
found_rows()	返回最后一个select查询语句检索的数据总行数
cast(v as 类型)	将v转换为指定的数据类型

四：数据类型

这里所谓的数据类型，也就是只在创建表时可以选择的列字段类型，可以通过：help data types;：查看当前版本支持的所有数据类型。

总体可分为数值类型、字符/串类型、时间/日期类型、其他类型四种

1：数值类型

类型名称	类型说明
tinyint	小整数类型,1Byte,-128~127
smallint	中整数类型，2Bytes, -32768~32767
mediumint	中大整数类型，3Bytes
int | integer	常用整数类型，4Bytes, -2147483548~2147483647
bigint	超大整数类型，8Bytes
float	单精度浮点数，4Bytes
double	双精度浮点数，8Bytes
decimal(m, d)	数类型，占位和取值范围都依赖m、d值决定 m是小数点后面的精度，d是小数点前面的标度
bit(m)	存储位值，取值范围1-64

2：字符串类型

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

🎉 一般在为列指定数据类型时，都会varchar(255)这样写，其实中间的这个数字限制的并不是字节长度，而是字符数量

3：时间/日期类型

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

4：其他类型

类型名称	说明
json	存储json数据，需要通过函数辅助使用
enum(选项1,选项2…选项n)	新增数据时只能从已有的选项中选择一个并插入
set(选项1,选项2…选项n)	新增数据时可以从已有的选项中选择多个并插入

🎉 enum、set类型，这两种类型就类似于平时的单选框和多选框，必须从已有的选项中选择

🎉 enum、set的区别在于：enum枚举类型只能选择一个选项，而set集合类型可以选择多个选项

五：索引相关的命令

具体使用在索引文章中均以涉及

1：创建普通索引

# ================= 创建普通索引 =================
-- tips: 后续其他类型的索引都可以通过这三种方式创建
-- 创建一个普通索引（方式①）
create index 索引名 ON 表名 (列名(索引键长度) [ASC|DESC]);
create key 索引名 ON 表名 (列名(索引键长度) [ASC|DESC]);

-- 创建一个普通索引（方式②）
alter table 表名 add index 索引名(列名(索引键长度) [ASC|DESC]);

-- 创建一个普通索引（方式③）
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  index [索引名称] (列名(长度))  
);

2：创建指定类型的索引

-- 创建一个唯一索引
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);

3：索引查看、使用与管理

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

六：事务与锁相关命令

在事务文章中均以提及

1：事务开启，提交和回滚

命令名称	含义
start transaction;/begin;/begin work;	开启一个事务
commit;	提交事务
rollback;	回滚事务

2：事务保存点相关

命令名称	含义
savepoint 事务点名称;	添加一个事务保存点
rollback to 事务点名称;	回滚到指定的事务保存点
release savepoint 事务点名称;	删除一个事务保存点

3：事务隔离级别相关

命令名称	含义
select @@tx_isolation; show variables like ‘%tx_isolation%’;	查询事务的隔离级别
set @@tx_isolation = “隔离级别”;	设置当前会话的隔离级别
set global transaction isolation level 级别;	设置全局的事务的隔离级别

4：自动提交相关

命令名称	含义
show variables like ‘autocommit’;	查看自动提交事务机制是否开启
set @@autocommit = `0	1

5：锁相关

命令名称	含义
select … for share;	手动获取共享锁
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;	乐观锁

七：用户与权限管理

🎉 在下面可以看到用户名后面跟了一个@IP，这个IP的作用是用来限制登录用户的机器

• 如果指定为具体IP，则表示只能由该IP的机器登录该用户

create user 'cui'@'192.168.xxx.xxx' identified by '123456';

• 如果写%表示任意设备都能使用该用户名登录连接。

1：用户信息相关

命令	含义
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 新用户名;	对某个用户重命名

2：权限相关

命令	含义
show grants	查看当前用户所有的权限
show grants for 用户名	查看指定用户拥有的权限
grant 权限1,权限2… on 库名.表名 to 用户名;	为指定用户授予权限
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;	使用默认的密码失效策略

权限可选项

usage：不授予这些权限。其他权限全部授予。

grant option：授予这些权限，其他权限全部不授予。

• 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：所有权限。

权限范围可选项：

• *.*：全局权限，表示该用户可对所有库、所有表进行增删改查操作。
• 库名.*：单库权限，表示该用户可对指定库下的所有表进行增删改查操作。
• 库名.表名：单表权限，表示该用户可对指定表进行增删改查操作。

八：视图与临时表

1：视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。

但是，视图并不在数据库中以存储的数据值集形式存在。

行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成

命令	含义
create view 视图名 as select …;	对查询出的结果集合建立一个指定名称的视图
select * from 视图名	基于某个已经创建的视图查询数据
show create view 视图名;	查看某个已存在的视图其详细信息
desc 视图名	查看某个视图的字段结构
alter view 视图名(字段1,…) as select 字段1…;	修改某个视图的字段为查询字段
drop view 视图名	删除某个视图

2：临时表

在MySQL中，临时表是一种用于存储临时数据的表，它只在当前会话中存在，会话结束后会自动删除。

临时表对于存储中间结果、临时计算或者需要频繁创建和删除的数据非常有用。

创建临时表后，你可以像使用普通表一样对它进行操作，例如插入数据、更新数据等。

临时表仅在当前会话中可见，会话结束后会自动删除。

⚠️ 临时表是在数据库服务器上创建的，而不是在客户端应用程序中创建的，在关闭连接或会话后，临时表将自动删除，而不需要手动删除。

命令	含义
create temporary table 表名(…);	创建一张临时表（方式1）
truncate table 临时表名	清空某张临时表的数据

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

数据库的备份其实本质上就是指通过导出数据的形式，或者拷贝表文件的方式来制作数据的副本

数据恢复/还原即是指在数据库故障、异常、错误的情况下，通过导入原本的数据副本，将数据恢复到正常状态

1：数据的导出

1.1：mysqldump工具备份

# --------------- 使用 mysqldump 工具做数据的逻辑备份（导出的是sql语句） -----

-- 导出MySQL中全部的库数据（使用--all-databases 或者 -A 参数）
mysqldump -uroot -p密码 -A > 备份文件名.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

1.2：物理备份

-- 查看数据库导出数据的路径（如果没有则需在`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"

2：数据恢复

2.1：mysql方式恢复

# 使用 mysql 工具来恢复备份的数据（导入xx.sql文件执行）
# 恢复库级别的数据（包含了建库语句的情况下使用）
mysql -u 用户名 -p < xxx.sql

# 恢复库中表级别的数据
mysql -u 用户名 -p 库名 < xxx.sql

2.2：物理方式恢复

# 通过物理数据文件恢复数据
# 使用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密码

十：表分区相关的命令(了解即可，很少用)

表分区是一种数据库优化技术，用于提高查询性能和管理大型表。

通过将表划分为多个较小的逻辑部分，称为分区，可以更有效地处理数据并加速查询。

表分区的主要优点包括：

1. 性能提升：将表划分为多个分区，可以减少查询需要扫描的数据量，从而加快查询速度。
2. 管理便利：对于大型表，分区可以使得数据的插入、更新和删除操作更加高效，并且可以独立地管理和维护每个分区，减轻系统负载。
3. 故障恢复：分区使得数据备份和恢复更为容易。如果某个分区出现故障，只需恢复该分区的数据，而不需要对整个表进行恢复。

表分区可以在大多数数据库管理系统中实现，如MySQL、Oracle、Microsoft SQL Server等。

在创建表时，可以使用分区关键字将表划分为多个分区，每个分区可以包含不同的物理存储和索引。

此外，还可以对分区进行合并、拆分、移动等操作，以满足不同的需求。

⚠️ 表分区虽然可以提高查询性能和管理大型表，但也会增加系统的复杂性。因此，在进行表分区时，需要根据实际情况进行权衡和优化。

⚠️ 在MySQL 8.0中，唯一支持分区的存储引擎是InnoDB和NDB。不支持分区的存储引擎不能使用分区

-- 创建范围分区
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的错误信息由ErrorCode、SQLState、ErrorInfo三部分组成，即错误码、SQL状态、错误信息三部分组成，如下：

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

1045属于错误状态码; 28000属于SQL状态；后面跟着的则是具体的错误信息

至于所有的错误码，实在太多了，直接大佬笔记地址：https://www.cnblogs.com/cuianbing/p/16260796.html