MySql数据库信息information_schema的查询使用(转)

原文转自:http://solodu.iteye.com/blog/452968

从MySQL 5开始, 你可以看到多了一个系统数据库information_schema . information_schema 存贮了其他所有数据库的信息。让我们来看看几个使用这个数据库的例子：

<!--more-->

1. 取得关于 information_schema的基本信息

information_schema是一个虚拟数据库，并不物理存在，在select的时候，从其他数据库获取相应的信息。

Java代码

1. mysql> show databases;   
2. +--------------------+   
3. | Database           |   
4. +--------------------+   
5. | information_schema |   
6. | bugs               |   
7. | mysql              |   
8. | sugarcrm           |   
9. +--------------------+   
10. 4 rows in set (0.00 sec)  

 

 

以下是information_schema数据库中的表.

Java代码

1. mysql> use information_schema;   
2. mysql> show tables;   
3. +---------------------------------------+   
4. | Tables_in_information_schema          |   
5. +---------------------------------------+   
6. | CHARACTER_SETS                        |   
7. | COLLATIONS                            |   
8. | COLLATION_CHARACTER_SET_APPLICABILITY |   
9. | COLUMNS                               |   
10. | COLUMN_PRIVILEGES                     |   
11. | KEY_COLUMN_USAGE                      |   
12. | PROFILING                             |   
13. | ROUTINES                              |   
14. | SCHEMATA                              |   
15. | SCHEMA_PRIVILEGES                     |   
16. | STATISTICS                            |   
17. | TABLES                                |   
18. | TABLE_CONSTRAINTS                     |   
19. | TABLE_PRIVILEGES                      |   
20. | TRIGGERS                              |   
21. | USER_PRIVILEGES                       |   
22. | VIEWS                                 |   
23. +---------------------------------------+   
24. 17 rows in set (0.00 sec)  

2. 查询表中数据超过1000行的表

Java代码

1. 以下的语句可以查出超过1000行数据的表   
2.   
3. mysql> select concat(table_schema,'.',table_name) as table_name,table_rows   
4.     -> from information_schema.tables where table_rows > 1000  
5.     -> order by table_rows desc;   
6.   
7. +----------------------------------+------------+   
8. | table_name                       | table_rows |   
9. +----------------------------------+------------+   
10. | bugs.series_data                 |      52778 |   
11. | bugs.bugs_activity               |      26436 |   
12. | bugs.longdescs                   |      21473 |   
13. | bugs.email_setting               |       5370 |   
14. | bugs.attachments                 |       4714 |   
15. | bugs.attach_data                 |       4651 |   
16. | bugs.cc                          |       4031 |   
17. | bugs.bugs                        |       2190 |   
18. | bugs.namedqueries_link_in_footer |       1228 |   
19. +----------------------------------+------------+   
20. 9 rows in set (0.04 sec)  

3. 查询所有没有主键的表

Java代码

1. This example gives a list of all the tables without primary key.   
2.   
3. SELECT CONCAT(t.table_name,".",t.table_schema) as table_name   
4. FROM information_schema.TABLES t   
5. LEFT JOIN information_schema.TABLE_CONSTRAINTS tc   
6. ON t.table_schema = tc.table_schema   
7. AND t.table_name = tc.table_name   
8. AND tc.constraint_type = 'PRIMARY KEY'  
9. WHERE tc.constraint_name IS NULL   
10. AND t.table_type = 'BASE TABLE';  

4. 实现表的历史数据information_schema

Putting the MySQL information_schema to Use article implements a history database using the information schema. The first half of this article describes the requirements for the history database, and a generic design to implement it. The second half describes the stepwise construction of code-generator that creates the SQL to construct and load the history database. The code-generator is driven by the information schema and some features of the information schema are discussed in detail.

 

5. 查询5个最大表

Java代码

1. mysql> SELECT concat(table_schema,'.',table_name) table_name,   
2.     -> concat(round(data_length/(1024*1024),2),'M') data_length   
3.     -> FROM information_schema.TABLES   
4.     -> ORDER BY data_length DESC LIMIT 5;   
5.   
6. +--------------------+-------------+   
7. | table_name         | data_length |   
8. +--------------------+-------------+   
9. | bugs.attach_data   | 706.89M     |   
10. | bugs.longdescs     | 3.45M       |   
11. | bugs.bugs_activity | 1.45M       |   
12. | bugs.series_data   | 0.75M       |   
13. | bugs.attachments   | 0.51M       |   
14. +--------------------+-------------+   
15. 5 rows in set (0.05 sec)