MySQL常用命令或设置

参考手册: MySQL开发人员官网参考文档

一.MySQL命令行登录

mysql -h${主机名或主机IP} -P${端口} -u${用户名} -p ${数据库名};      # 回车之后再输入密码
mysql -h${主机名或主机IP} -P${端口} -u${用户名} -p${密码} ${数据库名};    # 不建议这种显示输入密码的方式

• 在本机登录, 可以省略"-h"参数;
• 使用默认端口, 可以省略"-P"参数;
• "${数据库名}"也可以省略, 进去之后使用"use ${数据库名}"选择数据库.

二.数据库操作

1.查看当前有哪些数据库

show databases;

2.切换数据库

use ${数据库名};

3.创建数据库

create database ${数据库名} character set ${编码方式};
-- "character set ${编码方式}"可以省略, 此时使用默认的编码方式, 建议手动指定编码方式, 不要省略.

三.表操作

1.查看当前数据库下有那些表

-- 查看schema下表清单
show tables;
show full tables from ${schema名};

-- 查看表详细信息
select * from information_schema.tables where table_schema = ‘${Schema名}‘ and table_name = ‘${表名}‘;

2.创建表

MySQL_V8.0建表官网参考文档

create table ${表名} {
    ${字段名1} ${字段类型及长度} ${null/not null} default ${默认值} common ‘${字段注释}‘,
    ${字段名2} ${字段类型及长度} ${null/not null} default ${默认值} common ‘${字段注释}‘,
    ...
    ${字段名n} ${字段类型及长度} ${null/not null} default ${默认值} common ‘${字段注释}‘
    primary key(${主键字段列表})
} ENGINE=${引擎类型}  DEFAULT CHARSET=${表编码};
-- For example:
create table zhoujl_test1{
    id int not null common ‘ID‘,
    name varchar(600) not null common ‘姓名‘,
    birthday date not null common ‘出生日期‘,
    sex varchar(1) not null common ‘性别‘,
    salary double(19,2) null default 0.0 common ‘收入‘,
    primary key(id)
} ENGINE=InnoDB  DEFAULT CHARSET=utf8;

3.修改表名

rename table ${原表名} to ${新表名};
ALTER TABLE t1 RENAME t2;

4.增加字段

alter table ${表名} add column ${字段1表达式}, add column ${字段2表达式}, add column ${字段n表达式};
For example:
CREATE TABLE t1 (c1 INT);
ALTER TABLE t1 ADD COLUMN c2 INT;

5.修改字段

alter table ${表名} modify column ${字段1表达式}, modify column ${字段2表达式}, modify column ${字段n表达式};
For example:
alter table t2 modify column c4 varchar(20), modify column c3 int;

6.字段重命名

alter table ${表名} change ${原字段名} ${新字段表达式};
For example:
alter table t2 change c3 c5 int;

7.删除字段

alter table ${表名} drop column ${字名1}, drop column ${字段2};
For example:
alter table t2 drop c5;

8.字段信息查看

show full columns from ${表名};
select * from information_schema.columns where table_schema = ‘${schema名}‘ and table_name = ‘${表名}‘;

9.查看没有主键的表

select table_schema, table_name
  from information_schema.tables
 where table_name not in (select distinct table_name
                            from information_schema.columns
                           where column_key = "PRI")
   AND table_schema not in
       (‘mysql‘, ‘information_schema‘, ‘sys‘, ‘performation_schema‘)
 and table_schema = ‘${schema名}‘;

四.MySQL字符集操作

1.查看编码

show variables like ‘character%‘;

2.临时设置编码

1.set names xxx

set names ${编码};

"set names xxx"一条命令相当于以下三条命令:

set character_set_client = xxx;
      set character_set_connection = xxx;
      set character_set_results = xxx;

3.元数据编码

1)修改表编码

alter table ${表名} default character set ${编码};

2)修改字段编码

alter table ${表名} change ${字段1} ${字段2} varchar(36) character set {编码};

3)转换表所有字段编码

alter table ${表名} convert to character set ${编码};

4)查看数据库编码格式

show variables like ‘character_set_database‘;

5)查看表编码格式

show create table ${表名};

6)创建/修改数据库时指定编码

create/alter database ${数据库名} character set utf8;

五.MySQL数据库备份与恢复

1.备份数据库成文件

mysqldump -h${IP地址} -P${端口号} -u${登录用户名} -p${登录密码} [-t/-d] ${数据库名} --add-drop-table --tables ${表名列表, 用空格分隔} > ${保存到的本地文件名}

• ${数据库名}参数前加"-t"表示只导数据,加"-d"表示只导结构,不加表示导结构与数据
• 举例: mysqldump -h127.0.0.1 -P3306 -uuser -puser dbName --add-drop-table > /data/bak/mysql/20200811/dbName.sql

2.将数据文件导入数据库

mysql -h${IP地址} -P${端口号} -u${登录用户名} -p${登录密码} ${数据库名} < ${数据文件}

• 举例: mysql -h127.0.0.1 -uuser1 ?-P3306 -puser1 ?dbName1 < /data/bak/mysql/20200811/dbName.sql

六.MySQL授权

S1.授权命令

grant ${权限类型} on ${数据库名}.${表名} to ${用户名} identified by ‘${登录密码}‘;

权限类型:
* all: 所有权限
* select/insert/update/delete: 查询/增加/修改/删除权限
* ...


举例: grant all on . to root identified by ‘Xxxxxxxx‘;

S2.使权限修改立即生效

flush privileges;

七.MySQL服务操作

• 启动:

systemctl start mysqld.service;

• 查看状态:

systemctl status mysqld.service;

• 重启:

systemctl restart mysqld.service;

• 停止:

systemctl stop mysqld.service;

八.MySQL查看数据存放位置

show global variables like "%datadir%";

九.MySQL设置登录跳过密码验证

1.linux系统下

在/etc/my.cnf文件中, [mysqld]下面新增skip-grant-tables,然后重启服务器.

十.MySQl设置传输包大小

MySQL执行插入或更新时, 当数据量过大时, 可能由于max_allowed_packet参数的限制导致执行失败.此时, 可以重新设置该参数的值.
max_allowed_packet默认值为1M.

1.查看max_allowed_packet当前值

show variables like ‘%max_allowed_packet%‘;

2.设置max_allowed_packet值大小

方法1.临时设置, 重启服务后会恢复默认值

set global max_allowed_packet = ${大小}

方法2.永久生效, 需要重启服务

• windows下:
  在my.ini文件中, 修改或增加max_allowed_packet = 30M, 数字根据需要设定.
• linux系统中:
  在/etc/my.cnf文件中, 修改或增加max_allowed_packet = 30M, 数字根据需要设定.

十一.MySQL查看正在执行的SQL进程

1.查看正在执行的SQL进程:

show processlist;

2.查出来之后, 可以使用下面的命令终止SQL进程:

kill ${进程ID}

十二.MySQL执行文件中的SQL

连接进入MySQL服务, 使用source ${文件名}执行. 末尾不能带分号.

十三.MySQL用户操作

1.修改密码

alter user ‘${用户名}‘@‘${IP地址}‘ identified by ‘${登录密码}‘;