1. 云图网首页
  2. 技术专区
  3. 数据库

MySQL：常用 Show 命令

• 数据库

常用 show 命令，持续更新。

参考

http://dev.mysql.com/doc/refman/5.6/en/show.html

显示数据库列表

1
2
3
4
5
6
7
8
9
10
11
 
root@localhost:(none)>show databases;  
+--------------------+  
|  Database |  
+--------------------+  
| information_schema |  
| francs |  
| mysql |  
| performance_schema |  
| test |  
+--------------------+  
5 rows in  set  (0.01 sec)

显示当前数据库中表

1
2
3
4
5
6
7
8
 
root@localhost:francs>show tables;  
+------------------+  
|  Tables_in_francs  |  
+------------------+  
| test_1 |  
| test_2 |  
+------------------+  
2 rows in  set  (0.00 sec)

查看表结构

1
2
3
4
5
6
7
8
 
root@localhost:francs>desc test_1;  
+-------+-------------+------+-----+---------+-------+  
|  Field  |  Type |  Null  |  Key  |  Default  |  Extra  |  
+-------+-------------+------+-----+---------+-------+  
| id |  int(11) | NO | PRI | NULL | |  
| name | varchar(32)  | YES | MUL | NULL | |  
+-------+-------------+------+-----+---------+-------+  
2 rows in  set  (0.00 sec)

查看表上的索引情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
root@localhost:francs>show index from francs.test_1G;  
*  1. row *  
 Table: test_1  
 Non_unique:  0  
 Key_name: PRIMARY  
Seq_in_index:  1  
 Column_name: id  
 Collation: A  
 Cardinality:  10231  
 Sub_part: NULL  
 Packed: NULL  
 Null:    
 Index_type: BTREE  
 Comment:    
Index_comment:    
*  2. row *  
 Table: test_1  
 Non_unique:  1  
 Key_name: idx_test_1_name  
Seq_in_index:  1  
 Column_name: name  
 Collation: A  
 Cardinality:  10231  
 Sub_part: NULL  
 Packed: NULL  
 Null: YES  
 Index_type: BTREE  
 Comment:    
Index_comment:    
2 rows in  set  (0.00 sec)  
  
ERROR:    
No query specified

查看建表 DDL

1
2
3
4
5
6
7
8
9
10
 
root@localhost:francs>show create table test_1G  
*  1. row *  
 Table: test_1  
Create  Table: CREATE TABLE `test_1`  (  
 `id`  int(11) NOT NULL,  
 `name` varchar(32) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_test_1_name`  (`name`) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
1 row in  set  (0.00 sec)

查看表详细信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
root@localhost:francs>show table statusG  
*  1. row *  
 Name: test_1  
 Engine:  InnoDB  
 Version:  10  
 Row_format:  Compact  
 Rows:  10231  
Avg_row_length:  35  
 Data_length:  360448  
Max_data_length:  0  
 Index_length:  0  
 Data_free:  0  
Auto_increment: NULL  
 Create_time:  2014-08-15  17:16:30  
 Update_time: NULL  
 Check_time: NULL  
 Collation: utf8_general_ci  
 Checksum: NULL  
Create_options:    
 Comment:

查看指定表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
francs@localhost:francs>show table status from francs like 'tbl_access_log'G  
*  1. row *  
 Name: tbl_access_log  
 Engine:  InnoDB  
 Version:  10  
 Row_format:  Compact  
 Rows:  6  
Avg_row_length:  10922  
 Data_length:  65536  
Max_data_length:  0  
 Index_length:  0  
 Data_free:  0  
Auto_increment: NULL  
 Create_time: NULL  
 Update_time: NULL  
 Check_time: NULL  
 Collation: utf8_general_ci  
 Checksum: NULL  
Create_options: partitioned  
 Comment:    
1 row in  set  (0.00 sec)  
  
ERROR:    
No query specified

查看存储过程信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
root@localhost:francs>show procedure statusG  
*  1. row *  
 Db: francs  
 Name: proc1  
 Type: PROCEDURE  
 Definer: francs@localhost  
 Modified:  2014-08-15  14:30:54  
 Created:  2014-08-15  14:30:54  
 Security_type: DEFINER  
 Comment:    
character_set_client: utf8  
collation_connection: utf8_general_ci  
 Database  Collation: utf8_general_ci  
*  2. row *  
 Db: francs  
 Name: pro_insert  
 Type: PROCEDURE  
 Definer: francs@localhost  
 Modified:  2014-08-15  14:41:22  
 Created:  2014-08-15  14:41:22  
 Security_type: DEFINER  
 Comment:    
character_set_client: utf8  
collation_connection: utf8_general_ci  
 Database  Collation: utf8_general_ci  
2 rows in  set  (0.00 sec)

查看存储过程 code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
root@localhost:francs>show create procedure pro_insertG  
*  1. row *  
 Procedure: pro_insert  
 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  
 Create  Procedure: CREATE DEFINER=`francs`@`localhost` PROCEDURE `pro_insert`()  
BEGIN   
 DECLARE i INT;   
SET i =  0;   
  
WHILE i <=  10000 DO  
insert into test_1(id, name ) values(i,CONCAT(ID,'_a'));   
SET i = i +  1;   
 END WHILE;   
END  
character_set_client: utf8  
collation_connection: utf8_general_ci  
 Database  Collation: utf8_general_ci  
1 row in  set  (0.00 sec)

查看系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
root@localhost:francs>show variables like 'log_%';    
+----------------------------------------+------------------------------+  
|  Variable_name |  Value |  
+----------------------------------------+------------------------------+  
| log_bin | OFF |  
| log_bin_basename | |  
| log_bin_index | |  
| log_bin_trust_function_creators | OFF |  
| log_bin_use_v1_row_events | OFF |  
| log_error |  /database/mysql/data/db1.err |  
| log_output | FILE |  
| log_queries_not_using_indexes | OFF |  
| log_slave_updates | OFF |  
| log_slow_admin_statements | OFF |  
| log_slow_slave_statements | OFF |  
| log_throttle_queries_not_using_indexes |  0 |  
| log_warnings |  1 |  
+----------------------------------------+------------------------------+  
13 rows in  set  (0.00 sec)

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/239598.html

赞 (0)
0
上一篇 2022年2月12日
下一篇 2022年2月12日

相关推荐

发表回复

登录后才能评论