MySql操作命令大全

来源：互联网发布：js 清理cookie 编辑：程序博客网时间：2024/06/08 06:11

【注】

所有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 'databaseshow databasesshow 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_aaaEnter password: [root@fts-dtsrv1 /]#

将back_aaa恢复至ccc数据库

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

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

0 0