mysql常用命令整理

说明：下面用到的语法是参考官方mysql5.7 en manual文档，本机的运行环境为centos7和mysql5.7.26

修改数据库

语法：

ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...

ALTER {DATABASE | SCHEMA} db_name
 UPGRADE DATA DIRECTORY NAME

alter_specification:
 [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name

代码：

# 创建示例数据库db_demo
mysql> create database if not exists db_demo default charset=utf8 collate=utf8_general_ci; 

# 显示db_demo数据库创建信息
mysql> show create database db_demo; 
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| db_demo  | CREATE DATABASE `db_demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

# 查找以utf8开头的字符集
mysql> show character set like 'utf8%'; 
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

# 查找以utf8_general_ci开头的排序规则
mysql> show collation like 'utf8_general_ci%';
+-----------------+---------+----+---------+----------+---------+
| Collation       | Charset | Id | Default | Compiled | Sortlen |
+-----------------+---------+----+---------+----------+---------+
| utf8_general_ci | utf8    | 33 | Yes     | Yes      |       1 |
+-----------------+---------+----+---------+----------+---------+
1 row in set (0.00 sec)

# 将数据库db_demo的字符集修改为utf8mb4
mysql> alter database db_demo character set = utf8mb4;
Query OK, 1 row affected (0.00 sec)

# 查看修改后的数据库db_demo的结构
mysql> show create database db_demo;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| db_demo  | CREATE DATABASE `db_demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

# 将db_demo的排序规则修改为utf8_general_ci，当前为utf8mb4_general_ci
mysql> alter database db_demo collate = utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

# db_demo的排序规则被修改为utf8_general_ci之后，默认字符集也会自动被修改为utf8
mysql> show create database db_demo;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| db_demo  | CREATE DATABASE `db_demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

/* 注意：目前修改数据库的操作仅支持修改字符集与排序规则，'ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME' 一般不会用到 */

SET NAMES用法

语法：

SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT}

该语句会设置三个会话系统变量character_set_client, character_set_connection和character_set_results的字符集，设置character_set_connection同时也会设置collation_connection的排序规则

character_set_client - 当客户端连接到服务器时客户端用此字符集向服务器发送请求

character_set_connection - 该字符集用于在没有字符集介绍人时的指定的文字和从数字转换的字符串

character_set_results - 使用该字符集向客户端返回查询结果

# 查询相关的字符集系统变量
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

# 设置character_set_client, character_set_connection和character_set_results
# 这三个字符集为gbk
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

# 上面设置完之后，character_set_client, character_set_connection和character_set_results
# 这三个字符集都已经被修改，重启mysql服务之后会自动失效
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

根据其他表或查询结果来创建新表

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 { LIKE old_tbl_name | (LIKE old_tbl_name) }

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 [(create_definition,...)]
 [table_options]
 [partition_options]
 [IGNORE | REPLACE]
 [AS] SELECT ... (Some valid select or union statement)

临时表只存在于当前的会话，一旦当前会话被关闭，临时表也会被自动删除

# 创建一个空的新表，它基于原表的定义，其中列，索引的定义，表存储格式的版本与原表相同
# 当一个LOCK TABLES语句有效时不能执行语句CREATE TABLE或CREATE TABLE ... LIKE
# 新表会保留原表产生的列信息
# CREATE TABLE ... LIKE不会保留原表中任何指定的DATA DIRECTORY或INDEX DIRECTORY表选项和外键的定义
# 如果原表是一个临时表，CREATE TABLE ... LIKE不会保留TEMPORARY
# 创建一个临时表用CREATE TEMPORARY TABLE ... LIKE
CREATE TABLE new_tbl LIKE orig_tbl;

# 创建表t1
mysql> create table t1(
    -> id int unsigned not null primary key auto_increment comment '主键, 自增',
    -> name varchar(20) not null default '' comment '姓名',
    -> key idx_name(name)
    -> ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)

# 基于t1表创建表t2
mysql> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

# 显示t1表结构
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 显示t2表结构
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

/* 通过对比看出t1与t2的表结构完全相同 */

当表中存在外键的定义时，再使用CREATE TABLE ... LIKE看一下执行结果

# 创建表t3，t3有外键定义
mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `t1_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '表t1的主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `fk_t1_id` (`t1_id`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 基于t3创建表t4
mysql> create table t4 like t3;
Query OK, 0 rows affected (0.02 sec)

# 显示t4的表结构
mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `t1_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '表t1的主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `fk_t1_id` (`t1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

/* 通过上面t3与t4的表结构定义可以看出，使用CREATE TABLE ... LIKE创建表t4，t4可以创建成功，
   但是t4舍弃掉了t3中的外键的定义，所以在有外键定义的表中使用CREATE TABLE ... LIKE需要格外注意。 */

# 从原表创建新表，同时会将原表的数据插入到新表中
CREATE TABLE new_tbl [IGNORE | REPLACE] [AS] SELECT * FROM orig_tbl;

# t1表结构
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# t1表数据
mysql> select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
+----+-----------+
1 row in set (0.00 sec)

# 基于t1表创建表t6
mysql> create table t6(
    -> id int unsigned not null auto_increment primary key comment '主键, 自增',
    -> age tinyint unsigned not null default 0 comment '年龄',
    -> key idx_age(age)
    -> ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci 
    -> select id as t1_id, name from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

# t6表结构
mysql> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `t1_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# t6表数据
mysql> select * from t6;
+----+-----+-------+-----------+
| id | age | t1_id | name      |
+----+-----+-------+-----------+
|  1 |   0 |     1 | 孙悟空    |
+----+-----+-------+-----------+
1 row in set (0.01 sec)

/* 经过对比t1和t6的表结构，可以看出通过CREATE TABLE ... SELECT创建的表t6，原表t1的索引被丢弃，
   同时原表t1的列被追加到新表t6的右侧并且不会重叠，原表t1的数据也被插入到t6中。 */

# 基于t1创建空表t7
mysql> create table t7 like t1;
Query OK, 0 rows affected (0.02 sec)

# 表t7结构
mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 由上面知道表t1中有一条记录id为1，t1的auto_increment是2
# t7基于t1创建，此时t7的auto_increment是否也是2？
# 通过插入一条记录来看一下数据
mysql> insert into t7 values(null, '沙僧');
Query OK, 1 row affected (0.00 sec)

# 虽然t7是基于t1创建的，t7的auto_increment属性仍然是从0开始
mysql> select * from t7;
+----+--------+
| id | name   |
+----+--------+
|  1 | 沙僧   |
+----+--------+
1 row in set (0.00 sec)

# 用CREATE TABLE ... SELECT基于t1创建表t8
mysql> create table t8 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 与t1对比，t8的id不再自增，主键索引和普通索引都不存在
mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show keys from t8;
Empty set (0.00 sec)

# 根据t1创建表t10
mysql> create table t10(id int(10) unsigned NOT NULL primary key auto_increment COMMENT '主键, 自增') select * from t1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

# t10表结构，看出id的定义被覆盖
mysql> show create table t10\G
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# t10表的数据
mysql> select * from t10;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
+----+-----------+
1 row in set (0.00 sec)

/* 由此看出在用CREATE TABLE ... SELECT创建新表时，可以在CREATE TABLE后面重新定义列属性
   这些列可以与SELECT中表的列的名称相同或者不同，若相同会覆盖SELECT后的表中列的定义，若不
   同SELECT后表的列会自动追加到新表的右端。 */

# 创建表t11，基于t1创建
mysql> create table t11 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

# t11表结构，注意列id的定义(此时不再自增，不是主键，默认值是0)
mysql> show create table t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 一条记录
mysql> select * from t11;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
+----+-----------+
1 row in set (0.00 sec)

# 再插入一条记录
mysql> insert into t11 values(1, '猪八戒');
Query OK, 1 row affected (0.01 sec)

# 现在是两条记录
mysql> select * from t11;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  1 | 猪八戒    |
+----+-----------+
2 rows in set (0.00 sec)

# 基于t11创建t12报错，原因是因为原表t11中有两个相同的id，而新表的id是主键必须唯一，再插入到新表时出错
mysql> create table t12(`id` int(10) unsigned NOT NULL primary key auto_increment COMMENT '主键, 自增') select * from t11;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

# 解决方案使用ignore或replace
# 使用ignore时，不会报错正常执行，不过会有一个隐藏的警告
mysql> create table t12(`id` int(10) unsigned NOT NULL primary key auto_increment COMMENT '主键, 自增') ignore select * from t11;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 1

# 显示警告
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

# 在使用ignore时，对于多条重复记录，会默认使用第一条记录，剩下的会被忽略，同时会产生一个警告
# 所以下面会显示第一条记录
mysql> select * from t12;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
+----+-----------+
1 row in set (0.00 sec)

# 使用replace时，不会有警告，对于多条重复记录，会使用最后一条记录，把之前的覆盖掉
mysql> create table t13(`id` int(10) unsigned NOT NULL primary key auto_increment COMMENT '主键, 自增') replace select * from t11;
Query OK, 3 rows affected (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 0

# 会显示最后一条重复记录
mysql> select * from t13;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 猪八戒    |
+----+-----------+
1 row in set (0.00 sec)

/* 由此可见用ignore或replace都会舍弃重复记录，所不同的是是使用第一条还是最后一条重复记录，
   还有ignore是有警告产生的 */

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name
 [PARTITION (partition_name [, partition_name] ...)]
 [(col_name [, col_name] ...)]
 SELECT ...
 [ON DUPLICATE KEY UPDATE assignment_list]

value:
 {expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...

# t1的结构
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# t1的数据
mysql> select * from t1;
+----+--------------+
| id | name         |
+----+--------------+
|  4 | 元始天尊     |
|  1 | 孙悟空       |
|  3 | 小白龙       |
|  2 | 观音菩萨     |
+----+--------------+
4 rows in set (0.00 sec)

# 基于t1创建t16
mysql> create table t16 like t1;
Query OK, 0 rows affected (0.05 sec)

# t16的结构与t1相同
mysql> show create table t16\G
*************************** 1. row ***************************
       Table: t16
Create Table: CREATE TABLE `t16` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键, 自增',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 此时t16是一个空表，将t1的数据拷贝到t16
mysql> insert into t16 select * from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 可以看出t1与t16的数据一样
mysql> select * from t16;
+----+--------------+
| id | name         |
+----+--------------+
|  4 | 元始天尊     |
|  1 | 孙悟空       |
|  3 | 小白龙       |
|  2 | 观音菩萨     |
+----+--------------+
4 rows in set (0.00 sec)

# 由上面知道，t1和t16的数据相同
# 再执行一次插入看一下会有什么效果？
# 提示重复错误
mysql> insert into t16 select * from t1;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

# 使用ignore再执行一次，可以正常执行不会报错，但是会产生警告
mysql> insert ignore into t16 select * from t1;
Query OK, 0 rows affected, 4 warnings (0.01 sec)
Records: 4  Duplicates: 4  Warnings: 4

# 警告内容
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '3' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |
+---------+------+---------------------------------------+
4 rows in set (0.00 sec)

# 使用INSERT ... ON DUPLICATE KEY UPDATE看下效果
mysql> insert into t16 select * from t1 on duplicate key update t16.id=t16.id*5;
Query OK, 8 rows affected (0.01 sec)
Records: 4  Duplicates: 4  Warnings: 0

# 下面显示的是t16现在的数据，可以看到id值得变化，将重复的id进行计算
# 上面的插入语句等价于：
# update t16 set id=id*5 where id in(1, 2, 3, 4)
mysql> select * from t16;
+----+--------------+
| id | name         |
+----+--------------+
| 20 | 元始天尊     |
|  5 | 孙悟空       |
| 15 | 小白龙       |
| 10 | 观音菩萨     |
+----+--------------+
4 rows in set (0.00 sec)

# 还有一种情况，当t1与t17的id相同，但是name不同的情况，像这种情况该怎样处理？
# 如果用ignore或者on duplicate key update，虽然不会报错，但会造成数据丢失，把
# id相同，但是name不同的记录会被舍弃掉，有没有一种解决方法，可以进行合并又不会造成
# 数据丢失，下面看一下这个操作
# 下面是两个表现在的数据
mysql> select * from t17;
+----+--------------+
| id | name         |
+----+--------------+
|  4 | 土地公公     |
|  3 | 太上老君     |
|  2 | 女儿国       |
|  1 | 蜘蛛精       |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+----+--------------+
| id | name         |
+----+--------------+
|  4 | 元始天尊     |
|  1 | 孙悟空       |
|  3 | 小白龙       |
|  2 | 观音菩萨     |
+----+--------------+
4 rows in set (0.00 sec)

# 在t17后面加上指定的列名name，同时select后也必须是name，这样只会插入name字段，id不会被插入
mysql> insert into t17 (`name`) select `name` from t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t17;
+----+--------------+
| id | name         |
+----+--------------+
|  5 | 元始天尊     |
|  4 | 土地公公     |
|  3 | 太上老君     |
|  2 | 女儿国       |
|  6 | 孙悟空       |
|  7 | 小白龙       |
|  1 | 蜘蛛精       |
|  8 | 观音菩萨     |
+----+--------------+
8 rows in set (0.00 sec)

备份与还原命令

# 备份指定数据库(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql databasename

# 备份指定数据库中的指定的表(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql databasename tablename

# 只备份数据库中表的结构(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql -d databasename

# 只备份数据库中表的数据，没有表结构信息(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql -t databasename

# 只备份数据库中指定表的结构(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql -d databasename tablename

# 只备份数据库中指定表的数据(不需要登录)
mysqldump -uroot -p -r /tmp/backupfile.sql -t databasename tablename

# 用source命令还原(需要登录)
mysql> source /tmp/backupfile.sql;

分配权限命令

# 给来自124.133.55.150的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123456。
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to 'joe'@124.133.55.150 identified by '123456';
mysql>flush privileges;

# 给来自124.133.55.150的用户joe分配可对数据库vtdc所有表进行所有操作的权限，并设定口令为123456。
mysql>grant all privileges on vtdc.* to 'joe'@124.133.55.150 identified by '123456';
mysql>flush privileges;

# 给来自124.133.55.150的用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123456。
mysql>grant all privileges on *.* to 'joe'@124.133.55.150 identified by '123456';
mysql>flush privileges;