蛤蟆笔记MySQL学习通过SHOW获取数据库元数据

1、35.蛤蟆笔记 MySQL学习一一通过SHOW获取数据库元数据MYSQL提供的SHOW语句，可以显示许多类型的元数据。列出可访问数据库mysql> show databases;+| Database|+| in formatio n_schema | mysql| performa nce_schema | sampdb| sys|+5 rows in set (0.00 sec)显示数据库的创建语句mysql> show create database sampdb;+| Database | Create Database|+| sampdb | CREATE DATABAS

2、E 'sampdb' /*!40100 DEFAULT CHARACTER SET lati n1 */ |+1 row in set (0.00 sec)列出默认数据库或给定数据库里的表mysql> show tables;+| Tables_ in _sampdb |+|absenee| grade_eve nt| log_partitio n | member| mytbl| n amelist| preside nt| score| stude nt|I t|+ 10 rows in set (0.00 sec) mysql> show tables fro

3、m mysql;+| Tables_in_mysql|+| columns_priv| db| engin e_cost| event| func| gen eral_log| gtid_executed| help_category| help_keyword| help_relati on| help_topic| innodb_in dex_stats| inno db_table_stats| n db_b inlog_in dex| plugi n| proc| procs_priv| proxies_priv| server_cost| servers| slave_master_

4、i nfo| slave_relay_lognfo| slave_worker_i nfo| slow_log| tables_priv| time_z one| time_z on e_leap_sec ond| time_z one_n ame| time_z on e_tra nsiti on| time_z on e_tra nsiti on _type | user| 31 rows in set (0.00 sec)显示表的创建语句mysql> show create table mytbl;+Table|Table|Create|+| mytbl | CREATE TABL

5、E 'mytbl' ('k' mediumint(8) unsigned NOT NULL,'j' char(10) NOT NULL,PRIMARY KEY ('k'),UNIQUE KEY 'j' ('j')ENGINES nn oDB DEFAULT CHARSET=lati n1 |+1 row in set (0.00 sec)显示表里的列或索引信息mysql> show colu mns from mytbl;+一+一一+-一+-+| Field | Type| Null | Ke

6、y | Default | Extra |+一+一一+-一+-+| k| mediumi nt(8) un sig ned | 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| mediumi nt(8) un sig ned | NO| PRI | NULL| j| char(10)| NO| UNI

7、 | NULL|+一+一一+-一+-+2 rows in set (0.00 sec)mysql> explai n mytbl;+一+一一+-一+-+| Field | Type| Null | Key | Default | Extra |+一+一一+-一+-+| k| mediumi nt(8) un sig ned | NO| PRI | NULL| j| char(10)| NO| UNI | NULL|+一+一一+-一+-+2 rows in set (0.00 sec)显示索引：mysql> show index from mytbl;+| Table | Non_u

8、nique | Key_name | Seq_inndex | Column_name | Collation | Cardinality |Sub_part | Packed | Null | In dex_type | Comme nt | In dex_comme nt |+Imytbl I0 IPRIMARY I1 I kI A0INULL I NULLIIBTREEIIIImytbl I0 IjI1 I jI A0INULL I NULLIIBTREEIII+-+-+-+-+2 rows in set (0.00 sec)显示默认数据库或给定数据库里的表描述 信息mysql>

9、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 |+-+| absenee16384 |15:58:19 | NULLI| grade_event163

10、84 |15:58:19 | NULLI| log_partitio n | Inn oDB |I0 I|NULL|NULL| member|InnoDB | 0 | | NULLInnoDB I0 II NULL10 I Dynamic0 I0I lati n1_swedish_ci I10 I Dynamic0 I0I lati n1_swedish_ci I10 I Dyn amic I98304 I0 II lati n1_swedish_ci IInnoDB I10 I DynamicNULL |NULL |NULL |27302016-02-24I2730 |2016-02-24|

11、98304NULL I 2016-03-13 17:25:57NULL I partitio ned III 102 I481 I49152 |0 I0 I0 I103 I2016-02-2415:58:19 | NULL1| NULLI lat in 1_swedish_ci INULL II1| mytbl| InnoDB |10 I DynamicI0 I0 I16384 |0 I16384 I0 INULL I2016-03-1418:49:43 | NULLI| NULLI lat in 1_swedish_ci INULL II1| namelistI MEMORY I10 I F

12、ixedI0 I105 I0 |11902695 |0 I0 INULL I2016-03-1418:43:42 | NULL1I NULLI lat in 1_swedish_ci INULL II1| preside ntI InnoDB I10 I DynamicI36 I455 I16384 |0 I0 I0 INULL I2016-02-2415:58:19 | NULL1I NULLI lat in 1_swedish_ci INULL II1| scoreI InnoDB I10 I DynamicI173 I94 I16384 |0 I16384 I0 INULL I2016-

13、02-2415:58:19 | NULLII NULLI lat in 1_swedish_ci INULL II1| studentI InnoDB I10 I DynamicI31 I528 I16384 |0 I0 I0 I32 I2016-02-2415:58:19 | NULL1I NULLI lat in 1_swedish_ci INULL II1I tI InnoDB I10 I DynamicI0 I0 I16384 |0 I0 I0 INULL I2016-03-1316:20:31 | NULLI NULLI lat in 1_swedish_ci INULL II|+-+ + + + + + +10 rows in set (0.00 sec)mysql> show tables like '_ytbl：+| Tables_in_sampdb (_ytbl) |+| mytbl|+ 1 row in set (0.