SQL

SQL：数仓中 INFORMATION_SCHEMA 应用汇总

INFORMATION_SCHEMA 用法

  • MySQL中有一个名为 INFORMATION_SCHEMA 的数据库，在该库中有一个 TABLES 表，这个表主要字段分别是：
    • TABLE_SCHEMA : 数据库名，
    • TABLE_NAME：表名，
    • ENGINE：所使用的存储引擎， -TABLES_ROWS：记录数，
    • DATA_LENGTH：数据大小，
    • INDEX_LENGTH：索引大小

查询数据库表基本信息

  • 查看数据库的表数量 
    select table_schema as 数据库名称, count(*) as 表数量
from INFORMATION_SCHEMA.TABLES
group by table_schema;

    sql-17

  • 查询指定数据库及其表数量 
    SELECT table_schema as 库名 , count(*) as 表数量
 FROM information_schema.TABLES  
 where table_schema = 'ods'

    sql-17

  • 查询某个表中有多少字段 
    SELECT COUNT(*) 字段数量
FROM information_schema. COLUMNS 
WHERE table_schema = 'ods' 
AND table_name = 'ods_oms_stock';
  • 查询某个数据库中所有表共有多少字段 
    SELECT COUNT(column_name) as 总字段量
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'ods';

  • 统计某数据库中每个表的数据量

    SELECT TABLE_NAME as 表名, 
(DATA_LENGTH/1024/1024) as 数据大小_兆 ,
(INDEX_LENGTH/1024/1024) as 索引数据大小_兆,
((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as 总数据大小_兆,
TABLE_ROWS as 总数据条数
FROM TABLES 
WHERE TABLE_SCHEMA = 'ods';

    sql-17

  • 查询某数据库总数据大小 
    SELECT (sum(DATA_LENGTH)/1024/1024) as 总数据量_单位M 
from `TABLES` 
WHERE TABLE_SCHEMA = 'ods';

    sql-17

  • 查询某数据库中每张表数据条数 
    select table_name as 表名 , table_rows as 数据条数
from tables 
where TABLE_SCHEMA = 'ods';
  • 查询某数据库内所有表总数据条数 
    SELECT sum(table_rows) as 总数据条数
from tables 
where TABLE_SCHEMA = 'ods'

查看表字段基本信息

  • 查询某个数据库中所有字段 
    SELECT column_name as 全部字段
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'ods';

    sql-17

  • 查询某数据库中所有表、字段、字段类型、注释等信息

    SELECT TABLE_NAME 表名, column_name 字段名, DATA_TYPE 数据类型, column_comment 注释
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'ods';

    sql-17

INFORMATION_SCHEMA.COLUMNS 查找指定字段

  • 通常情况下，我们可以通过以下命令，查看指定表包含哪些字段信息：
    • show columns from 表名；
    • describe 表名；
    • show create table 表名；

  • 但在数仓工作中，经常要查看哪些数据表包含指定字段，由于涉及的表比较多，不可能逐个的去查找表格，需要使用特殊方法 INFORMATION_SCHEMA.COLUMNS。

  • 查看全部的 库名 + 表名 + 字段名 
    select concat(table_schema,'.',table_name,'.',column_name) from INFORMATION_SCHEMA.COLUMNS;
  • 查看【字段名】包含指定字符串：
    • 精确查找： 
      select * from INFORMATION_SCHEMA.COLUMNS where column_name = '尺码';

      sql-17

    • 模糊匹配： 
      select * from INFORMATION_SCHEMA.COLUMNS where column_name rlike '尺';

      sql-17

  • 查看【注释】包含指定字符串：

    select * from INFORMATION_SCHEMA.COLUMNS where column_comment rlike '尺';

    sql-17

CATALOG
    FEATURED TAGS
    推荐系统 数据结构 用户画像 指标体系 数仓建模 用户运营 SQL 数据埋点 HBase Hive OLAP 数仓运维 BI FineBI Hadoop Spark Tableau 数仓 数据中台 数据治理 数据湖 数据集市 Airflow DataX DolphinScheduler Flume Kafka Maxwell Zookeeper 任务调度 Elasticsearch ETL 数据产品 数据库 MySQL Python Redis JS逆向 安卓逆向 爬虫 HTTP
    FRIENDS