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

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

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

<!--more-->

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcrm           |
+--------------------+
4 rows in set (0.00 sec)

 

 

以下是information_schema数据库中的表.

mysql> use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

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

以下的语句可以查出超过1000行数据的表

mysql> select concat(table_schema,'.',table_name) as table_name,table_rows
    -> from information_schema.tables where table_rows > 1000
    -> order by table_rows desc;

+----------------------------------+------------+
| table_name                       | table_rows |
+----------------------------------+------------+
| bugs.series_data                 |      52778 |
| bugs.bugs_activity               |      26436 |
| bugs.longdescs                   |      21473 |
| bugs.email_setting               |       5370 |
| bugs.attachments                 |       4714 |
| bugs.attach_data                 |       4651 |
| bugs.cc                          |       4031 |
| bugs.bugs                        |       2190 |
| bugs.namedqueries_link_in_footer |       1228 |
+----------------------------------+------------+
9 rows in set (0.04 sec)

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

This example gives a list of all the tables without primary key.

SELECT CONCAT(t.table_name,".",t.table_schema) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
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个最大表

mysql> SELECT concat(table_schema,'.',table_name) table_name,
    -> concat(round(data_length/(1024*1024),2),'M') data_length
    -> FROM information_schema.TABLES
    -> ORDER BY data_length DESC LIMIT 5;

+--------------------+-------------+
| table_name         | data_length |
+--------------------+-------------+
| bugs.attach_data   | 706.89M     |
| bugs.longdescs     | 3.45M       |
| bugs.bugs_activity | 1.45M       |
| bugs.series_data   | 0.75M       |
| bugs.attachments   | 0.51M       |
+--------------------+-------------+
5 rows in set (0.05 sec)