注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

MySQL:常用 show 命令  

2014-12-02 15:05:50|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       常用 show 命令,持续更新。
   
--参考
http://dev.mysql.com/doc/refman/5.6/en/show.html

--显示数据库列表

root@localhost:(none)>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| francs             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)


--显示当前数据库中表

root@localhost:francs>show tables;
+------------------+
| Tables_in_francs |
+------------------+
| test_1           |
| test_2           |
+------------------+
2 rows in set (0.00 sec)


--查看表结构

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)


--查看表上的索引情况

root@localhost:francs>show index from francs.test_1\G;
*************************** 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

root@localhost:francs>show create table test_1\G
*************************** 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)


--查看表详细信息

root@localhost:francs>show table status\G
*************************** 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:


--查看指定表信息

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


--查看存储过程信息

root@localhost:francs>show procedure status\G
*************************** 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

root@localhost:francs>show create procedure pro_insert\G
*************************** 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)


--查看系统变量

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)

  评论这张
 
阅读(254)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016