查看数据库元数据

查看数据库元数据

作者:LAMP小白  点击:2094  发布日期:2012-10-24 22:46:00  返回列表
查看库
SHOW DATABASES;

查看数据库的信息

mysql> SHOW CREATE DATABASE mio;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| mio | CREATE DATABASE `mio` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

列出表

SHOW TABLES;
SHOW TABLES FROM db_name;

查看数据表详情

SHOW CREATE TABLE tbl_name;

查看索引

mysql> SHOW INDEX FROM scoreG
*************************** 1. row ***************************
Table: score
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: event_id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: score
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: student_id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: score
Non_unique: 1
Key_name: student_id
Seq_in_index: 1
Column_name: student_id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
3 rows in set (0.00 sec)

查看字段信息

mysql> SHOW COLUMNS FROM score;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| student_id | int(10) unsigned | NO | PRI | NULL | |
| event_id | int(10) unsigned | NO | PRI | NULL | |
| score | int(11) | NO | | NULL | |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

查看表的描述性信息

mysql> SHOW TABLE STATUSG
*************************** 1. row ***************************
Name: absence
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2012-10-17 22:18:37
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: grade_event
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 6
Create_time: 2012-10-16 23:35:11
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: member
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 6
Create_time: 2012-10-24 21:15:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 4. row ***************************
Name: president
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6
Avg_row_length: 30
Data_length: 184
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2012-10-14 21:45:16
Update_time: 2012-10-17 22:39:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 5. row ***************************
Name: score
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 4194304
Auto_increment: NULL
Create_time: 2012-10-16 23:43:12
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 6. row ***************************
Name: student
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2012-10-14 21:52:51
Update_time: 2012-10-14 21:52:51
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
6 rows in set (0.00 sec)

从 INFORMATION_SCHEMA数据库获取元数据

mysql> SHOW TABLES IN INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
28 rows in set (0.00 sec)

SCHEMATA TABLES VIWS ROUTINES TRIGGERS EVENTS PARTITIONS COLUMNS
数据库 数据表 视图 存储过程 触发器 数据库里的事件 数据表分区和数据列信息
FILES 关于NDB硬盘数据文件的信息
TABLE_CONSTRAINS KEY_COLUMN_USAGE 关于数据库和数据列上的约束信息
STATISTICS 关于数据表索引特性信息
REFERENTIAL_CONSTRAINS 关于外键信息
CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY
关于所支持的字符集 每种字符集的排序方式 每种排序方式与他的字符集的映射关系
ENGINES PLUGINS 关于存储引擎和服务器插件信息
USER_PRIVILEGES SCHEMA_PRIVALEGES TABLE_PRIVILEGES COLUMN_PRIVILEGES
全局数据库 数据表 数据列的权限信息
GLOBAL_VARIABLES SESSION_VARIABLES GLOBAL_STATUS SESSION_STATUS
全局和会话系统变量和状态变量的值
PROCESSLIST 关于服务器内执行的线程信息


上一篇:数据表操作 下一篇:快递查询API
0