数据库表和库的相关查询
1.查数据库中表数量
（红色标记的是常用到的重要的表结构信息表）
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_LOCK_WAITS |
| INNODB_SYS_TABLESTATS |
| INNODB_CMP |
| INNODB_METRICS |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_INDEXES |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_CMPMEM |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_BEING_DELETED |
| INNODB_SYS_TABLESPACES |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_TABLES |
| INNODB_BUFFER_POOL_STATS |
| INNODB_FT_CONFIG |
+---------------------------------------+
59 rows in set
（红色标记的表COLUMNS 、 SCHEMATA 、 TABLES 中COLUMNS 和TABLES 表的列太多不展示，看看SCHEMATA 表 ）
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 | ceshi | utf8 | utf8_general_ci | NULL |
| def | cms | utf8 | utf8_general_ci | NULL |
| def | mysql | utf8 | utf8_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
7 rows in set
（上表中我本地MySQL里的自己建的表出现了）。
查数据库中表数量：
mysql> select table_schema,count(*) from tables group by table_schema;
+--------------------+----------+
| table_schema | count(*) |
+--------------------+----------+
| ceshi | 6 |
| cms | 227 |
| information_schema | 59 |
| mysql | 29 |
| performance_schema | 52 |
+--------------------+----------+
7 rows in set
2.查数据库中所有表的信息
在navicat中新建查询，通过TABLES表来查，运行
select * from information_schema.tables where TABLE_SCHEMA='数据库名';
可以查看每张表对应的结构信息。
进一步，
3.查数据库中所有表名
在navicat中，
select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='数据库名';
4.查询（指定）数据库中哪些表有某个指定的字段
如：查哪些表有play_id字段
SELECT * FROM information_schema.columns WHERE column_name='player_id' -- AND TABLE_SCHEMA='数据库名';;
5.查询字段含有指定字符串的表：
如：查字段有含有"country"字符串（部分字符串）的表
SELECT * FROM information_schema.columns WHERE LOCATE("country",column_name)>0;
6.查库里的所有的空表
SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名' AND table_rows = 0 ORDER BY table_name ;
7.查看指定数据库中最近被更新的表
select TABLE_NAME,UPDATE_TIME from information_schema.TABLESwhere TABLE_SCHEMA='数据库名' ORDER BY UPDATE_TIME desc -- limit 1;
8.查看指定表最近被更新的时间
select TABLE_NAME,UPDATE_TIME from information_schema.TABLES where TABLE_SCHEMA='数据库名' and information_schema.TABLES.TABLE_NAME = '表名';
9.查看指定数据库中最近新建的表：
select TABLE_NAME,CREATE_TIME from information_schema.TABLES where TABLE_SCHEMA='数据库名' ORDER BY CREATE_TIME desc
10.查看MySQL数据库大小
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';
得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M。
11.查看数据库表基本信息。
select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = '数据库名' and information_schema.TABLES.TABLE_NAME = '表名';
