MySql操作命令大全

【注】

• 所有MySql命令均以逗号或“\g”结尾，否则回车后认为命令未结束，等待输入
• 对数据库的增、删、改、查、建表语句即为常用的create table、insert等，本文不再介绍

mysql> show database
    -> show databases
    -> show databases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database
show databases
show databases' at line 1

显示mysql下所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hz                 |
| jkw                |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

切换数据库

mysql> use mysql;
Database changed

【注】

• mysql刚安装完有两个数据库：mysql和test。
• mysql库非常重要，它里面有mysql的系统信息。改密码和新增用户，实际上就是用这个库中的相关表进行操作。

显示数据库中所有表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

显示表结构

mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

建库

mysql> create databases aaa;

增加mysql用户

首先以root用户连接mysql，然后键入一下命令：
命令格式：grant 权限 on 数据库名.表名 to 用户名@登录主机 identified by "密码" 

【注】

• @后的“登录主机”为访问mysql的客户端主机名，”%”：代表任意客户端；localhost代表本地访问，即该用户不能远程访问该mysql

grant select,insert,delete,update on *.* to xuexuan@"%" identified by "123456";

【注】

• 允许xuexuan在任何一台电脑上连接mysql数据库并对数据进行增、删、改、查操作。
• 此用户十分危险！！！

grant select,delete,insert,update on aaa.* to xuexuan@localhost identified by "123456";

【注】

• xuexuan用户只能在本机访问mysql，无法远程访问
• 只能对aaa数据库中的表进行增、删、改、查操作

查看mysql用户

mysql> select host,user,password from user;
+----------------+---------------+-------------------------------------------+
| host           | user          | password                                  |
+----------------+---------------+-------------------------------------------+
| %              | root          | *DB469070DB0AD0CA0B93040D166D7FC4713D6961 |
| 127.0.0.1      | root          | *DB469070DB0AD0CA0B93040D166D7FC4713D6961 |
| %              | fwpt_2        | *7D8E52F5EC0847F5E58FE60EA0A28EA88F84666F |
| locahost       | dcs_mode      | *9EE7AB8BBB08C6EB745F27FD2C6E8B520B5069CA |
| fwpt_3         | username      | *86287D530862CCE88F244A8865DDDCF7B6DF18AB |
+----------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

备份与恢复

将上例创建的aaa库备份到文件back_aaa中，生成的back_aaa文件在当前所处路径下。

 格式：mysqldump -u 用户名 -p --opt 数据库名 > 备份文件名

[root@fts-dtsrv1 /]# mysqldump -u root -p --opt aaa > back_aaa
Enter password: 
[root@fts-dtsrv1 /]# 

将back_aaa恢复至ccc数据库

首先进入备份文件路径
格式：mysql -u root -p 数据库名 < 文件名

[root@fts-dtsrv1 /]# mysql -u root -p ccc < back_aaa