13.8.2 使用系统表分析数据库信息
除了可以使用DatabaseMetaData来分析底层数据库信息之外,如果已经确定应用程序所使用的数据库系统,则可以通过数据库的系统表来分析数据库信息。前面已经提到**,系统表又称为数据字典,数据字典的数据通常由数据库系统负责维护,用户通常只能查询数据字典,而不能修改数据字典的内容。
提示
几乎所有的数据库都会提供系统表供用户查询,用户可以通过查询系统表来获得数据库的相关信息。对于像MySQL和SQL Server这样的数据库,它们还提供一个系统数据库来存储这些系统表。系统表相当于视图**,用户只能查看系统表的数据,不能直接修改系统表中的数据.
MySQL数据库使用information_schema数据库来保存系统表,在该数据库里包含了大量系统表,常用系统表的简单介绍如下。
| 系统表 |
介绍 |
tables |
存放数据库里所有数据表的信息 |
schemata |
存放数据库里所有数据库(与MySQL的 Schema对应)的信息。 |
views |
存放数据库里所有视图的信息 |
columns |
存放数据库里所有列的信息 |
triggers |
存放数据库里所有触发器的信息 |
routines |
存放数据库里所有存储过程和函数的信息 |
key_column_usage |
存放数据库里所有具有约束的键信息。 |
table_constraints |
存放数据库里全部约束的表信息。 |
statistics |
存放数据库里全部索引的信息。 |
从这些系统表中取得的数据库信息会更加准确,例如,
- 若要查询当前
MySQL数据库中包含多少数据库及其详细信息,则可以查询schemata系统表;
- 如果需要查询指定数据库中的全部数据表,则可以查询
tables系统表;
- 如果需要查询指定数据表的全部数据列,就可以查询
columns系统表。
下面显示了通过系统表査询所有的数据库、 select_test数据库的全部数据表、 student_table表的所有数据列的SQL语句及执行效果.
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
34
35
36
37
38
39
| mysql> select * from schemata;
+--------------+-----------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-----------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | bishe | utf8 | utf8_general_ci | NULL |
| def | cimema_booking_system | utf8 | utf8_general_ci | NULL |
| def | hrm_db | utf8 | utf8_general_ci | NULL |
| def | moviebooking | utf8 | utf8_general_ci | NULL |
| def | mybatis | utf8mb4 | utf8mb4_general_ci | NULL |
| def | mysql | utf8 | utf8_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | select_test | utf8 | utf8_general_ci | NULL |
| def | ssm | utf8 | utf8_general_ci | NULL |
| def | test | utf8 | utf8_general_ci | NULL |
| def | test_engine | utf8 | utf8_general_ci | NULL |
+--------------+-----------------------+----------------------------+------------------------+----------+
12 rows in set
mysql> select * from tables where table_schema='select_test';
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | select_test | emp_table | BASE TABLE | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 16384 | 4194304 | 5 | 2019-05-28 15:36:44 | NULL | NULL | utf8_general_ci | NULL | | |
| def | select_test | img_table | BASE TABLE | InnoDB | 10 | Compact | 1 | 32768 | 32768 | 0 | 0 | 4194304 | 2 | 2019-07-05 13:16:38 | NULL | NULL | utf8_general_ci | NULL | | |
| def | select_test | jdbc_test | BASE TABLE | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 0 | 4194304 | 8 | 2019-07-04 14:03:45 | NULL | NULL | utf8_general_ci | NULL | | |
| def | select_test | student_table | BASE TABLE | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 16384 | 4194304 | 2208 | 2019-05-25 15:17:39 | NULL | NULL | utf8_general_ci | NULL | | |
| def | select_test | teacher_table | BASE TABLE | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 4194304 | 4 | 2019-05-25 15:17:38 | NULL | NULL | utf8_general_ci | NULL | | |
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
5 rows in set
mysql> select * from columns where table_name='student_table';
+---------------+--------------+---------------+--------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+--------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+---------------+--------------+---------------+--------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+--------------+------------+----------------+---------------------------------+----------------+
| def | select_test | student_table | student_id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | select_test | student_table | student_name | 2 | NULL | YES | varchar | 255 | 765 | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | |
| def | select_test | student_table | java_teacher | 3 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | int(11) | MUL | | select,insert,update,references | |
+---------------+--------------+---------------+--------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+--------------+------------+----------------+---------------------------------+----------------+
3 rows in set
|