文章目录
前言
生活中有时会出现灵魂三问:我是谁?我在哪?我在做什么?特别的喝醉酒的第二天,完全不记得昨天发生了什么。而在数据库操作中也会出现这种灵魂拷问,我用的是哪个用户,为什么会没有权限?我操作的是哪个数据库,刚刚不会把线上正式服务器数据删了吧?
上面描述的问题常常出现在切换数据库处理问题的时候,通过一个客户端连接到Mysql数据库服务器,操作数据库1,然后切换再操作数据库2,这时如果中间有人打扰,很容易忘记刚刚操作的是哪个数据库,或者中途处理个其他紧急的事情,回来连操作的用户都忘了,这时就需要一些基础信息的查询命令,帮助你来恢复记忆。
数据库基础信息查询
数据库的基础信息涉及到方方面面,这里只列举几个常用的查询命令,用来回答上面的灵魂拷问,其他命令还有很多,用到了再总结吧。
查询当前操作的用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.07 sec)
查询当前操作的数据库
mysql> select database();
+------------+
| database() |
+------------+
| sqltest2 |
+------------+
1 row in set (0.09 sec)
查询当前数据库端口
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.07 sec)
查询当前数据库版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.06 sec)
数据库结构信息查询
这里的结构我指的是DDL
中定义的那些元素,比如表、存储过程等,有一些常用的查询命令,要是一段时间不使用还是会忘记,比如查询一个数据库中的存储过程,每次查询时都要上网搜一下,所以今天总结在一起方便查找。
查询当前数据库中的所有表
mysql> show tables;
+--------------------+
| Tables_in_sqltest2 |
+--------------------+
| a |
| b |
| c |
| d |
| m |
| p |
| tb_test |
| tb_with_index |
+--------------------+
14 rows in set (0.11 sec)
查询创建表的sql语句
mysql> show create table a;
+-------+----------------------------------------+
| Table | Create Table |
+-------+----------------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------+
1 row in set (0.11 sec)
查询指定表中的所有字段
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.12 sec)
查询当前数据库中的所有存储过程
这个命令我得吐槽一下,为什么不能像查询当前数据库的中所有表一样,搞个show procedures;
命令,非得通过where子句指定数据库呢,具体的原因还不知道,等我弄明白了再回来补充。
mysql> show procedure status where db='sqltest2';
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sqltest2 | fill_slow_query_test | PROCEDURE | root@localhost | 2019-03-25 11:14:01 | 2019-03-25 11:14:01 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| sqltest2 | fill_tb_without_index | PROCEDURE | root@localhost | 2019-03-18 09:53:32 | 2019-03-18 09:53:32 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| sqltest2 | fill_tb_with_index | PROCEDURE | root@localhost | 2019-03-18 09:53:33 | 2019-03-18 09:53:33 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.17 sec)
查询创建存储过程的sql语句
mysql> show create procedure fill_tb_with_index;
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| fill_tb_with_index | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_tb_with_index`()
BEGIN
DECLARE i int default 1;
WHILE i <= 100000 do
insert into tb_with_index values(i, i, i);
set i = i + 1;
END WHILE;
END | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
1 row in set (0.09 sec)
查询指定表上的索引
mysql> show index from tb_with_index;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_with_index | 1 | id_index | 1 | id | A | 100035 | NULL | NULL | YES | BTREE | | |
| tb_with_index | 1 | num_index | 1 | num | A | 98715 | NULL | NULL | YES | BTREE | | |
| tb_with_index | 1 | money_index | 1 | money | A | 100035 | NULL | NULL | YES | BTREE | | |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)
查询当前用户连接的权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
查询指定用户连接的权限
mysql> show grants for 'guest';
+---------------------------------------------------------------------------------------------------------------+
| Grants for guest@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'guest'@'%' IDENTIFIED BY PASSWORD '*6C8DE74065898C44C21EF74D67A834C5256BFA1C' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
总结
- 以上总结的查询语句都是我经常用到,相比Mysql所有查询语句来说简直是冰山一角,总结到一起主要是方便日后查找,同时也希望给他人带来帮助
- 查看这些语句会发现,有些是
select
开头,有些是show
开头,实际上很多show
开头的都是对information_schema
数据库数据的封装 information_schema
数据库是Mysql系统自带的数据库,记录了整个数据库实例上所有数据结构信息,更像是记录数据库的数据库,包含表结构、字符集,权限等太多的信息,有机会后续找时间聊聊这个数据库,在此就不展开了- 正因为很多
show
开头的都是对information_schema
数据库数据的封装,所以这些查询语句基本都可以通过在information_schema
数据库查询得到,比如show procedure status where db='sqltest2';
就可以改写成select routine_name from information_schema.routines where routine_schema='sqltest2';
- 此时此刻,我正在距离天安门500多米的现场等待阅兵仪式的开始,一边学习一边为祖国庆生的感觉真好!
(注:500多米多了5公里,现场是卧室床前的电视机旁,这么近的距离不知道一会能不能看见接受检阅的飞机o(* ̄︶ ̄*)o)