一、查询所有数据库
```
show databases;
或者
select DISTINCT(a.SCHEMA_NAME) from information_schema.SCHEMATA a;
```
二、查询所有表
<font size=4> 查询单库
use database_name;
show tables;
查询所有库
select a.TABLE_SCHEMA,a.TABLE_NAME from information_schema.TABLES a
三、查询所有表数据
1.查询所有表
```
SELECT CONCAT( ' select ', '"', a.TABLE_SCHEMA, '.', a.TABLE_NAME, '" as Table_Name', ', count(*) as Sum_Rows
from ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' union all'
)AS Statistic_Tables
FROM information_schema. TABLES a
UNION ALL
SELECT CONCAT( ' select "Table_Name","Sum_Rows" from dual' )
FROM DUAL
```
2.排除系统表
```
SELECT CONCAT( ' select ', '"', a.TABLE_SCHEMA, '.', a.TABLE_NAME, '" as Table_Name', ', count(*) as Sum_Rows
from ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' union all'
)AS Statistic_Tables
FROM information_schema. TABLES a
WHERE a.TABLE_SCHEMA NOT IN ( 'information_schema', 'mysql', 'performance_schema' )
UNION ALL
SELECT CONCAT( ' select "Table_Name","Sum_Rows" from dual' )
FROM DUAL
```
3.指定数据库
```
SELECT CONCAT( ' select ', '"', a.TABLE_SCHEMA, '.', a.TABLE_NAME, '" as Table_Name', ', count(*) as Sum_Rows
from ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' union all'
)AS Statistic_Tables
FROM information_schema. TABLES a
WHERE a.TABLE_SCHEMA IN ( 'name1', 'name2', 'name3' )
UNION ALL
SELECT CONCAT( ' select "Table_Name","Sum_Rows" from dual' )
FROM DUAL
```
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于