MySQL学习(3)---MySQL常用命令

ps：此随笔基于mysql 5.7.*版本。

准备

net start mysql
启动MySQL服务
net stop mysql
关闭MySQL服务
mysql [-h<IP地址>] [-p<端口>] -u<用户名> -p<密码>
连接MySQL
exit/quit
退出MySQL

注：MySQL的提示符是：mysql> 。MySQL命令结束使用“;”或是“\G”。

已知root账户密码进行登录

格式：mysql [-h地址] [-p端口] -u用户名 -p密码

省略不写地址或端口则自动使用默认。(地址：localhost；端口：3306)

两种方式进行登录。方式1：

QQ截图20220824134457

方式2：

QQ截图20220824134844

忘记root账户密码进行登录(修改root密码)

以管理员身份打开一个cmd。
检查MySQL服务是否启动，若已启动则先将其停止服务，输入命令：net stop mysql，回车。
输入命令：E:，回车，再次输入：cd MySQL\MySQL Server 5.7\bin，切换至mysql安装目录的bin目录下。输入命令：mysqld --defaults-file="E:\MySQL\MySQL_Data\my.ini" --console --skip-grant-tables，回车。

QQ截图20220824144141

该命令通过跳过用户权限安全检查，开启MySQL服务，这样连接MySQL时，可以不用输入密码。此时已经开启MySQL服务，保留这个cmd窗口。

注：这里笔者修改了MySQL配置文件my.ini中basedir和datadir各自的路径。

QQ截图20220824140821

同样使用管理员身份，另起一个cmd窗口，尝试连接MySQL。输入命令：mysql -uroot -p，回车，随后出现：Enter password:，这里不用输入密码，直接回车，出现登录成功的信息。

QQ截图20220824144349

执行命令来重置root账户密码：update mysql.user set authentication_string=password('新密码') where user='root';，回车。
刷新权限。输入命令：flush privileges;，回车。
然后退出，重新登录。输入命令：quit或exit，回车。
此时关闭之前的cmd窗口。执行命令来启动MySQL服务：net start mysql，回车。
再次尝试连接mysql。输入命令：mysql -uroot -p，回车。出现输入密码提示，输入新密码即可登录。

QQ截图20220824144818

至此，忘记root账户密码问题解决。

数据库操作

show databases;
查看所有的数据库，等同于select schema_name from information_schema\G;（注：\G以纵向报表的形式输出结果，有利于阅读）
create database <数据库名>;
创建数据库
use <数据库名>;
使用该数据库
select database();/status;
查看当前使用的数据库
drop database <数据库名>;
删除数据库
alter database 数据库名 character set <字符编码>;（注：修改完数据库字符集，需要重启MySQ数据库）
修改数据库字符集

表操作

注：操作表之前应使用“use <数据库名>;”进行连接。

show tables [from <按数据库名>];
查看指定数据库中包含的表
desc/describe <表名>;
显示当前数据表的结构
show create table <表名>;
查看建表时的SQL语句
create table <表名> (<字段名1> <类型1> [字段约束] [,...<字段名n> <类型n> [字段约束]]);
创建表
create table <表名> like <已有表名>;
创建与当前表相同结构的新表
rename table <旧表名> to <新表名>;
重命名数据表
alter table <表名> add <新字段名1> <字段类型> [default] [默认值] [字段约束] [first|after] [已有字段] [,...add <新字段名n> <字段类型> [default] [默认值] [字段约束] [first|after] [已有字段]];（注：不加[first|after]的位置说明时，默认添加新字段到最后一列。若添加first位置说明，则在第一列添加新字段；若添加after位置说明，则在指定字段的下一列进行添加）
增加一列/多列字段
alter table <表名> change <旧字段名> <新字段名> <新字段类型> [新约束];
修改字段名称
alter table <表名> modify <字段名> <新类型> [新约束] [first|after] [已有字段];（注：若不加新约束，则会将建表时的约束清空，主键、外键和唯一约束除外）
修改字段类型及约束
alter table <表名> engine=innodb|myisam|memory|archivem
修改数据库引擎
alter table <表名> add primary key(字段名);
增加主键
alter table <表名> drop primary key;
删除主键
alter table <表名> add constraint <外键约束名> foreign key(字段名) references <主表>(主键)
增加外键
alter table <表名> drop foreign key(外键约束名);
删除外键
alter table <表名> drop index <字段名>;
删除唯一约束
alter table <表名> drop column <字段名1/列名1> [,...drop column <字段名n/列名n>]
删除单个/多个列
drop table <表名>;
删除表
insert into <表名> [(<字段名1> [,...<字段名n>])] values (值1[,...值n]) [,...(值1[,...值n])];
向表中插入一条/多条数据
delete from <表名> where <表达式>;
删除表中满足条件的数据
delete from <表名>;/truncate table <表名>;
删除表中所有数据（注：前者会逐条进行删除，速度较慢；后者直接删除表中所有的行，速度快）
update <表名> set <字段名1=新值1> [,...字段名n=新值n] where <表达式> [order by ASC|DESC] [limit ...]
修改表中一条/多条数据
select * from <表名>
查询表中所有数据
select [distinct] <字段名1> [,...字段名n] from <表名1> [,...表名n] where <表达式> [group by <字段>] [having <表达式>] [order by ASC|DESC] [limit [偏移量,] <行记录数>]
查询表中满足指定条件的数据

用户和权限管理

create user '用户名'@'IP地址|域名|%|localhost' [identified by '密码'];（注：%代表通配符，允许客户端以任何IP访问，localhost代表只能通过本机进行登录。如果既有字母，又有数字，则不进行匹配（防止恶意访问））
单独创建用户
grant <具体权限|all privileges> on <[数据库名.] 表名> to '用户名'@'IP地址|域名|%|localhost' [identified by '密码'] [with grant option];（注：具体权限包括select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file权限中的一种或多种。all privileges代表所有权限。with grant option表示用户可以将自己的权限分配给其他用户）
授权（存在用户则直接授权，否则创建该用户并授权）
select user();
查看当前登录用户
show grants;
查看当前用户权限
show grants for '用户名'@'IP地址|域名|%|localhost';
查看某个用户的权限
rename user '旧用户名'@'IP地址|域名|%|localhost' to '新用户名'@'与前者相同';
重命名用户
upadte mysql.user set authentication_string=pasword('新密码') where user='用户名' and host='IP地址|域名|%|localhost';
修改用户密码
flush privileges;
刷新权限
revoke <具体权限|all privileges> from <[数据库名.] 表名> to '用户名'@'IP地址|域名|%|localhost';
撤销用户权限
drop user '用户名'@'IP地址|域名|%|localhost';
删除用户

注：每当调整权限之后需要使用flush privileges;命令刷新更改的权限设置

查询数据库运行状态

show status like '%Aborted_connects%';
查询由于客户端尝试连接，而被MySQL拒绝的连接次数
show status like '%Threads_connected%';
查询当前打开的连接数量
show status like 'Qcache%';
查询缓存情况
show variables like 'log_bin';
查询是否启用binlog日志

备份与恢复

mysqldump [-h<IP地址>] -u<用户名> -p<密码> <数据库名> > <保存路径/文件名.sql>
备份单个数据库
mysqldump [-h<IP地址>] -u<用户名> -p<密码> <数据库名> <表名1> [,...表名n] > <保存路径/文件名.sql>
备份单个数据库中的一个/多个数据表
mysqldump [-h<IP地址>] -u<用户名> -p<密码> --databases <数据库名1> [,...数据库名n] > <保存路径/文件名.sql>
同时备份多个数据库
mysqldump [-h<IP地址>] -u<用户名> -p<密码> --all-databases > <保存路径/文件名.sql>
备份所有数据库
mysqldump [-h<IP地址>] -u<用户名> -p<密码> [数据库名] < <保存路径/文件名.sql>（注：如果备份文件中包含创建数据库语句，则不需要指定数据库名；否则需要指定数据库名）
还原单个数据库
mysqldump [-h<IP地址>] -u<用户名> -p<密码> [数据库名表名1,...表名n] < <保存路径/文件名.sql>
还原单个数据库中的一个/多个数据表
mysqldump [-h<IP地址>] -u<用户名> -p<密码> --databases [数据库名1,...数据库名n] < <保存路径/文件名.sql>
还原多个数据库
mysqldump [-h<IP地址>] -u<用户名> -p<密码> --all-databases < <保存路径/文件名.sql>
还原所有数据库