MySQL 常用命令

启动 / 关闭 MySQL 服务

以管理员身份运行cmd、也可以在任务管理器中开启/关闭服务

C:\Windows\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。


C:\Windows\system32>net stop mysql57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

进入 MySQL

C:\Users\bxm09>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

退出 MySQL

下面的三个命令都可以

mysql> exit;
mysql> quit;
mysql> \q;

Bye

修改MySQL提示符

mysql> prompt bxm>
PROMPT set to 'bxm>'
bxm>

mysql> prompt \D
PROMPT set to '\D'
Wed Apr 26 14:18:39 2017

查看 MySQL 版本信息

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)

Connection id:          11
Current database:
Current user:           ODBC@
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.18-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 41 min 32 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 100  Flush tables: 1  Open tables: 95  Queries per second avg: 0.009
--------------

显示服务器当前版本

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.00 sec)

显示服务器当前时间

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-04-26 14:26:07 |
+---------------------+
1 row in set (0.00 sec)

显示当前用户

mysql> SELECT USER();
+--------+
| USER() |
+--------+
| ODBC@  |
+--------+
1 row in set (0.00 sec)

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

mysql> CREATE DATABASE demo1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS demo1;
Query OK, 1 row affected, 1 warning (0.00 sec)

查看警告信息

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1007 | Can't create database 'demo1'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

显示数据库列表

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo1              |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

显示表

mysql> SHOW TABLES;
+---------------------+
| Tables_in_shopimooc |
+---------------------+
| imooc_admin         |
| imooc_album         |
| imooc_cate          |
| imooc_pro           |
| imooc_user          |
+---------------------+
5 rows in set (0.00 sec)

修改数据库

ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;

mysql> ALTER DATABASE demo1 CHARACTER SET gbk;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE demo1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| demo1    | CREATE DATABASE `demo1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

mysql> DROP DATABASE demo1;
Query OK, 0 rows affected (0.02 sec)