1、简介
现如今我们操作数据库，都是使用现成的数据库管理工具，比如Navicat、SQLyog等等。这些工具能够提供可视化操作界面，大大的提升了我们的操作效率，但是这样往往让我们忘却了很多数据库语句，比如建表语句、查询数据库信息等。这篇文章我们学习几个非常有用的语句，便于在没有可视化工具的时候给到你帮助。
2、正文
2.1 查看所有数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liziba |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.2 查看正在使用的数据库
此时未选择数据库，显示null
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
选择数据库liziba
mysql> USE liziba;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| liziba |
+------------+
1 row in set (0.00 sec)
2.3 查看当前数据库中的表
mysql> SHOW TABLES;
+------------------+
| Tables_in_liziba |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
2.4 查看表的列信息
mysql> SHOW COLUMNS FROM user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| sex | smallint(6) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
2.5 显示授权用户及其权限
查看所有用户的授权信息：
mysql> SHOW GRANTS;
查看具体用户的授权信息：
SHOW GRANTS FOR 'root'@'localhost';
2.6 查看服务器错误或警告信息
查看错误信息，这里我将ERRORS打成ERROES，服务器会产生一条错误日志
mysql> SHOW ERROES;
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 'ERROES' at line 1
mysql>
mysql>
mysql> SHOW ERRORS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'ERROES' at line 1 |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'WARRINGS' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 s
2.7 查看建库语句和建表语句
查看liziba数据库的建库语句
mysql> SHOW CREATE DATABASE liziba;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| liziba | CREATE DATABASE `liziba` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
查看user 表的建表语句，注意提前使用use xxx
mysql> SHOW CREATE TABLE user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) NOT NULL COMMENT '用户名',
`age` int(11) NOT NULL COMMENT '年龄',
`sex` smallint(6) NOT NULL COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
2.8 查看数据库端口
如果你忘记了端口，可以查看端口信息（前提是你能的登录上来，哈哈哈！）
mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
2.9 查看数据库索引大小
首先我们使用数据库information_schema，这个数据库中记录了表的相关信息
mysql> use information_schema;
Database changed
查看tables表列，可以看到记录的表相关信息属性，其中index_length列，记录了表的索引大小单位是B，table_schema列记录了当前表所属的数据库。
我们可以根据table_schema = 'liziba'，查看当前数据库下索引的大小
mysql> SELECT CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024), 4), 'mb') AS 'Database Index Size'
-> FROM tables
-> WHERE table_schema = 'liziba';
+---------------------+
| Database Index Size |
+---------------------+
| 0.0000mb |
+---------------------+
1 row in set (0.00 sec)
2.10 查看数据库大小
除了计算索引大小，我们也可以统计数据库的大小，只需要将表数据内存和索引数据内存加起来即可。
mysql> SELECT CONCAT(((ROUND(SUM(data_length), 4) + ROUND(SUM(index_length), 4)) / (1024 * 1024)), ' mb') AS 'Database Size'
-> FROM tables
-> WHERE table_schema = 'liziba';
+---------------+
| Database Size |
+---------------+
| 0.01562500 mb |
+---------------+
1 row in set (0.00 sec)
2.11 查看最大连接数
151 是默认的最大连接数
mysql> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
2.12 查看当前线程相关信息
注意这里使用STATUS，可以查看线程相关状态，VARIABLES查看的是配置参数。如下查询可以看到连接数、创建了多少线程、正在运行的线程等信息
mysql> SHOW STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 3 |
| Threads_created | 4 |
| Threads_running | 2 |
+-------------------+-------+
4 rows in set (0.00 sec)
2.13 查看文件存储路径
mysql> SHOW VARIABLES LIKE '%datadir%';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| datadir | E:\mysql-8.0.15-winx64\data\ |
+---------------+------------------------------+
1 row in set, 1 warning (0.00 sec)
2.14 查看数据库编码
查看数据库编码很有作用，我们有时候在同步数据的时候会因为编码不同导致数据出现异常，我们可以通过如下方式查看数据库的编码格式。
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | gbk_chinese_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set, 1 warning (0.00 sec)
2.15 帮助指令
可以使用HELP指令查看指令。
比如HELP SHOW;
mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
HELP STATUS;
mysql> HELP STATUS;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
FLUSH
SHOW
SHOW ENGINE
SHOW FUNCTION STATUS
SHOW MASTER STATUS
SHOW PROCEDURE STATUS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
