35.笔记 MySQL学习——通过SHOW获取数据库元数据
MYSQL提供的SHOW语句,可以显示许多类型的元数据。
列出可访问数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampdb |
| sys |
+--------------------+
5 rows in set (0.00 sec)
显示数据库的创建语句
mysql> show create database sampdb;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| sampdb | CREATE DATABASE `sampdb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
列出默认数据库或给定数据库里的表
mysql> show tables;
+------------------+
| Tables_in_sampdb |
+------------------+
| absence |
| grade_event |
| log_partition |
| member |
| mytbl |
| namelist |
| president |
| score |
| student |
| t |
+------------------+
10 rows in set (0.00 sec)
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
显示表的创建语句
mysql> show create table mytbl;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytbl | CREATE TABLE `mytbl` (
`k`mediumint(8) unsigned NOT NULL,
`j`char(10) NOT NULL,
PRIMARY KEY (`k`),
UNIQUE KEY `j` (`j`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
显示表里的列或索引信息
mysql> show columns from mytbl;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |Extra |
+-------+-----------------------+------+-----+---------+-------+
| k | mediumint(8) unsigned | NO |PRI | NULL | |
| j | char(10) | NO | UNI | NULL | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc mytbl;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| k | mediumint(8) unsigned | NO |PRI | NULL | |
| j | char(10) | NO | UNI | NULL | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> explain mytbl;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |Extra |
+-------+-----------------------+------+-----+---------+-------+
| k | mediumint(8) unsigned | NO |PRI | NULL | |
| j | char(10) | NO | UNI | NULL | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
显示索引:
mysql> show index from mytbl;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name |Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null| Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytbl | 0 | PRIMARY | 1 | k | A | 0 | NULL | NULL | | BTREE | | |
| mytbl | 0 | j | 1 | j | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
显示默认数据库或给定数据库里的表描述信息
mysql> show table status;
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format |Rows | Avg_row_length | Data_length | Max_data_length | Index_length |Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment|
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| absence | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| grade_event | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | 7 | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| log_partition | InnoDB | 10 | Dynamic | 0 | 0 | 98304 | 0 | 98304 | 0 | NULL | 2016-03-13 17:25:57 |NULL | NULL | latin1_swedish_ci | NULL | partitioned | |
| member | InnoDB | 10 | Dynamic | 102 | 481 | 49152 | 0 | 0 | 0 | 103 | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| mytbl | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2016-03-14 18:49:43 | NULL | NULL | latin1_swedish_ci| NULL | | |
| namelist | MEMORY | 10 | Fixed | 0 | 105 | 0 | 11902695 | 0 | 0 | NULL | 2016-03-14 18:43:42 |NULL | NULL | latin1_swedish_ci | NULL | | |
| president | InnoDB | 10 | Dynamic | 36 | 455 | 16384 | 0 | 0 | 0 | NULL | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| score | InnoDB | 10 | Dynamic | 173 | 94 | 16384 | 0 | 16384 | 0 | NULL | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| student | InnoDB | 10 | Dynamic | 31 | 528 | 16384 | 0 | 0 | 0 | 32 | 2016-02-24 15:58:19 |NULL | NULL | latin1_swedish_ci | NULL | | |
| t | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2016-03-13 16:20:31 | NULL | NULL | latin1_swedish_ci| NULL | | |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
10 rows in set (0.00 sec)
mysql> show tables like '_ytbl';
+--------------------------+
| Tables_in_sampdb (_ytbl) |
+--------------------------+
| mytbl |
+--------------------------+
1 row in set (0.00 sec)
mysql> show columns from student where `Key` ='PRI';
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key |Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| student_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+------------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)