MySQL数据库常用情景命令行记录

MySQL管理常用

登录命令

mysql -u [username] -h [host] -p # 回车后输入密码
# -u 即用户名，-h 即数据库IP地址，-p即密码
# 也可以省略-h默认为localhost

登录时的各个参数含义

1. -D [database] 或 --database=db_name # 设置打开特定数据库
2. -h [host_name] 或 --host=host_name # 设置服务器地址
3. -p [xxxxx] 或 --password=xxxxx # 密码
4. -P [port] 或 --port=port_val # 设置端口号
5. -u [username] 或 --user=username # 设置登录账户
6. –prompt [\h] # 修改提示符比如mysql>改成localhost，进入数据库后可以使用 prompt [提示符] 命令修改回来。在啊下边参数后面依旧可以添加其他符号，以命令prompt \u@\h-\d> 为例最后提示符为变为:root@localhost-testdb>
   • \D # 完整日期；
   • \d # 当前数据库；
   • \h # 服务器名称；
   • \u # 当前用户；
7. –delimiter [分隔符] # 修改分隔符，即每条SQL语句的标志
   • delimiter $,则会将分隔符设置为$结尾

注意： prompt和delimiter命令都是一次会话有效的，下次会话无效。

创建用户和授权

有时候并不需要root用户那么高的权限，因此会使用到新建的用户并定制其权限Grant。

# 创建用户，方式一：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
# 此时的用户没有任何的对数据库的增删改查的权限
# 授权给该用户 
GRANT ALL[insert,select,update...] ON dbName.tbName TO ‘username‘@’host’;
# 增加权限后该用户只能在允许的权限中操作
# 创建用户，方式二：（一步到位）
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON dbName.tbName[*.*]
TO ‘username‘@‘host’
IDENTIFIED BY ‘password’;

删除用户和撤权

对应创建用户和授权必然就存在删除用户和撤销权利Revoke
以root进入数据库后执行如下命令：

REVOKE ALL[select,insert...] ON dbName.tbName FROM 'username'@'host';
# 只有某个用户名默认删除‘oneadmin’@‘localhost’;
DROP user ‘username’@‘host’;

DROP不会将删除用户的创建的所有数据库删除掉，在drop前可以先自己删除已经建立的数据库，否则只能使用root删除

设置与更改用户密码

有时需要设置或修改用户的密码只需要一下命令：

# 可以使用root或本username账号下执行
SET PASSWORD FOR 'username'@'host'=PASSWORD('newpassword');

数据库编码方式

• 刚创建数据库时设置编码方式：

# CHARACTER SET:指定数据库采用的字符集,utf8不能写成utf-8
# COLLATE:指定数据库字符集的排序规则,utf8的默认排序规则为utf8_general_ci（通过show character set可以查看）

CREATE DATABASE db 
CHARACTER SET utf8 
COLLATE utf8_general_ci;

• 创建数据库后修改编码方式：

-- 修改数据库编码
ALTER DATABASE db CHARACTER SET GBK COLLATE gbk_chinese_ci;

ALTER DATABASE db CHARACTER SET utf8 COLLATE utf8_general_ci;

• 创建表时，设置表、字段编码

CREATE TABLE tb(
id int(10) auto_increment,
user_name varchar(60) CHARACTER SET GBK COLLATE gbk_chinese_ci,
email varchar(60),
PRIMARY KEY(id)
)CHARACTER SET utf8 COLLATE utf8_general_ci;

• 表创建好后，设置表编码、字段编码

-- 修改表编码
ALTER TABLE tb CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 修改字段编码
ALTER TABLE tb MODIFY email VARCHAR(60) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看SQL执行时间

# 查看上一条执行时间，如果没有开启功能需要设置
show profiles;
# 查看profiling 是否是on状态
show variables like 'profiling';
# 开启该功能和关闭该功能
set profiling=1;
set profiling=0;

其他数据库管理的命令

• USE [数据库名];
• SHOW DATABASES;// 显示所有数据库
• select database();// 显示当前选中数据库
• SHOW TABLES;// 显示选择的数据中的所有表
• SHOW CHARACTER SET;// 查看所有的字符编码
• SHOW CREATE TABLE[DATABASE] tbName[dbName]// 查看表或数据库的具体创建语句
• SHOW COLUMNS FROM [数据表];
  等同于
  DESC [数据表]
• SHOW INDEX FROM [数据表];
• SHOW TABLE STATUS FROM db_name [LIKE ‘pattern’] \G: // 该命令将输出Mysql数据库管理系统的性能及统计信息。
• DROP DATABASE [数据库名];// 删除某个数据库
• DROP TABLE [数据表名];// 删除某个表

参考：

【1】菜鸟教程MySQL
【2】MySQL创建用户与授权
【3】创建数据库时,设置数据库的编码方式