MySQL常用命令详解

查看版本

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.21 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.21    |
+-----------+
1 row in set (0.00 sec)

查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

查看库

show databases

切换库

use test;

查看所有的表

show tables;

查看当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

创建表

create table MyClass( 
    id int(4) not null primary key auto_increment, 
    name char(20) not null, 
    sex int(4) not null default '0', 
    degree double(16,2) 
    );

删除表

drop table MyClass;

删除表中的数据

delete from MyClass where id=11;

修改字段名

alter table MyClass change sex gender char(1) not null;  

查看表结构

describe MyClass;
desc MyClass;
show columns from MyClass;

插入数据

insert into MyClass (id, name, gender, degree) values (10, 'Mark', 'M', 23.23);
insert into MyClass (id, name, gender, degree) values (11, 'Mark', 'M', 23.23),(12, 'White', 'F', 23.23),(13, 'Jerry', 'M', 99.23);
insert into MyClass  values (14, 'Mark', 'M', 23.23);                                  

更新表中的数据

mysql> update MyClass set name='Nicolas' where id=14;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from MyClass where id=14;
+----+---------+--------+--------+
| id | name    | gender | degree |
+----+---------+--------+--------+
| 14 | Nicolas | M      |  23.23 |
+----+---------+--------+--------+
1 row in set (0.00 sec)

在表中添加字段

mysql> alter table MyClass add passtest int(4) default '0';
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc MyClass;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(4)       | NO   | PRI | NULL    | auto_increment |
| name     | char(20)     | NO   |     | NULL    |                |
| gender   | char(1)      | NO   |     | NULL    |                |
| degree   | double(16,2) | YES  |     | NULL    |                |
| passtest | int(4)       | YES  |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改表名

mysql> rename table MyClass to my_class;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_class       |
| mytable        |
+----------------+
2 rows in set (0.00 sec)

查看数据

mysql> select * from MyClass;
+----+-------+--------+--------+
| id | name  | gender | degree |
+----+-------+--------+--------+
|  1 | Tom   | M      |  99.99 |
|  2 | Alise | F      |  60.23 |
|  3 | Marry | F      |  63.23 |
|  4 | Wilsh | M      |  79.23 |
| 10 | Mark  | M      |  23.23 |
| 11 | Mark  | M      |  23.23 |
| 12 | White | F      |  23.23 |
| 13 | Jerry | M      |  99.23 |
| 14 | Mark  | M      |  23.23 |
+----+-------+--------+--------+
9 rows in set (0.00 sec)

mysql> select * from  MyClass order by id;
+----+-------+--------+--------+
| id | name  | gender | degree |
+----+-------+--------+--------+
|  1 | Tom   | M      |  99.99 |
|  2 | Alise | F      |  60.23 |
|  3 | Marry | F      |  63.23 |
|  4 | Wilsh | M      |  79.23 |
| 10 | Mark  | M      |  23.23 |
| 11 | Mark  | M      |  23.23 |
| 12 | White | F      |  23.23 |
| 13 | Jerry | M      |  99.23 |
| 14 | Mark  | M      |  23.23 |
+----+-------+--------+--------+
9 rows in set (0.00 sec)

mysql> select * from  MyClass order by id limit 0,5;
+----+-------+--------+--------+
| id | name  | gender | degree |
+----+-------+--------+--------+
|  1 | Tom   | M      |  99.99 |
|  2 | Alise | F      |  60.23 |
|  3 | Marry | F      |  63.23 |
|  4 | Wilsh | M      |  79.23 |
| 10 | Mark  | M      |  23.23 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)

MySQL备份

FLUSH TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

    UNLOCK TABLES implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES. The commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table locks.

    Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK. 

FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables (see Section 6.4.1, “Selecting General Query and Slow Query Log Output Destinations”). 

FLUSH TABLES WITH READ LOCK;

#-A, --all-databases Dump all the databases. This will be same as --databases with all databases selected. 
#-x, --lock-all-tables Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.
mysqldump --all-databases --lock-all-tables > dbdump.db

UNLOCK TABLES;

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

# mysqldump -uroot -A -p > /tmp/mysqldump_$(date +%F).dmp
Enter password: 
# file /tmp/mysqldump_2016-10-11.dmp 
/tmp/mysqldump_2016-10-11.dmp: UTF-8 Unicode English text, with very long lines

MySQL恢复

1、先登录mysql控制台，然后执行source后面跟备份文件路径
2、mysql -uroot -p < /tmp/mysqldump_2016-10-11.dmp