首页 / MYSQL / MySQL INFORMATION_SCHEMA 使用

MySQL INFORMATION_SCHEMA 使用

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL INFORMATION_SCHEMA 使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3760字，纯文字阅读大概需要6分钟。

内容图文

--  查看指定数据库的表信息

            SET
            @table_schema
            =
            ‘
            employees
            ‘
            ;

            SELECT
             
    table_name,
    table_type,
    engine,
    table_rows,
    avg_row_length,
    data_length,
    index_length,
    table_collation,
    create_time

            FROM
            
    information_schema.tables

            WHERE
            
    table_schema 
            =
            @table_schema
            ORDER
            BY table_name;

 

-- 查看会话连接信息

            SELECT
             
    THREAD_ID,
    name,
    type,
    PROCESSLIST_ID,
    PROCESSLIST_USER 
            AS
            user
            ,
    PROCESSLIST_HOST 
            AS
             host,
    PROCESSLIST_DB 
            AS
             db,
    PROCESSLIST_COMMAND 
            AS
             cmd,
    PROCESSLIST_TIME 
            AS
             time,
    PROCESSLIST_STATE 
            AS
             state,
    PROCESSLIST_INFO 
            AS
             info,
    CONNECTION_TYPE 
            AS
             type,
    THREAD_OS_ID 
            AS
             os_id

            FROM
            
    performance_schema.threads

            WHERE
            
    type 
            =
            ‘
            FOREGROUND
            ‘
            ORDER
            BY THREAD_ID;

 

-- CHARACTER_SETS 查看数据库支持的字符集

            SELECT 
            * 
            FROM 
            INFORMATION_SCHEMA.CHARACTER_SETS

            WHERE 
            CHARACTER_SET_NAME 
            LIKE
            ‘
            utf%
            ‘
            ;

SHOW 
            CHARACTER
            SET
            LIKE
            ‘
            utf%
            ‘;

 

-- COLLATIONS  字符序

            --
             用于指定数据集如何排序，以及字符串的比对规则
            SELECT
            *
            FROM
             INFORMATION_SCHEMA.COLLATIONS

            WHERE COLLATION_NAME LIKE‘utf%‘;

SHOW COLLATION LIKE‘utf%‘;

-- 查看表结构定义信息

            SELECT
             
    table_name,
    COLUMN_NAME,
    ordinal_position,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    column_type,
    column_key,
    character_set_name,
    collation_name

            FROM
            
    INFORMATION_SCHEMA.COLUMNS

            WHERE
            
    table_name 
            =
            ‘
            employees
            ‘
            AND table_schema =‘employees‘;

show columns from employees from employees;


desc employeees.employees;

 

-- 查看支持的引擎

            SELECT
            *
            FROM
             INFORMATION_SCHEMA.ENGINES;
show ENGINES;

 

-- 查看数据库的数据文件信息

            SELECT
            FILE_ID
            ,
    
            FILE_NAME
            ,
    FILE_TYPE,
    TABLESPACE_NAME,
    FREE_EXTENTS,
    TOTAL_EXTENTS,
    ((TOTAL_EXTENTS 
            - FREE_EXTENTS) * EXTENT_SIZE) /1024/1024AS MB_used,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    DATA_FREE,
    STATUS,
    ENGINE
FROM
    INFORMATION_SCHEMA.FILES;

 

-- 查看指定表的约束

            SELECT
             
    constraint_schema,
    table_name,
    constraint_name,
    column_name,
    ordinal_position,
    CONCAT(table_name,
            
            ‘
            .
            ‘
            ,
            column_name,
            
            ‘
             -> 
            ‘
            ,
            referenced_table_name,
            
            ‘
            .
            ‘
            ,
            referenced_column_name) 
            AS
             list_of_fks

            FROM
            
    information_schema.KEY_COLUMN_USAGE

            WHERE
            
    REFERENCED_TABLE_SCHEMA 
            =
            ‘
            employees
            ‘
            AND REFERENCED_TABLE_NAME ISNOTNULLORDERBY TABLE_NAME , COLUMN_NAME;

 

-- 查看指定分区表信息

            SELECT
             
    TABLE_SCHEMA,
    table_name,
    partition_name,
    subpartition_name sub_par,
    partition_ordinal_position par_position,
    partition_method method,
    partition_expression expression,
    partition_description description,
    table_rows

            FROM
            
    information_schema.PARTITIONS

            WHERE
            
    table_schema 
            =
            ‘
            test
            ‘
            AND table_name =‘t‘;

 

-- 查看支持的插件

            SELECT
            
  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
  PLUGIN_LIBRARY, PLUGIN_LICENSE

            FROM
             INFORMATION_SCHEMA.PLUGINS;

SHOW PLUGINS;

 

-- 查看数据库连接信息

            SELECT
            *
            FROM
             INFORMATION_SCHEMA.PROCESSLIST;

SHOW 
            FULL PROCESSLIST;

 

-- 查看数据库中的存储过程、函数等

            SELECT
             
    ROUTINE_SCHEMA,
    routine_name,
    ROUTINE_TYPE,
    data_type,
    routine_body,
    routine_definition,
    routine_comment

            FROM
            
    INFORMATION_SCHEMA.ROUTINES

            WHERE
            
    ROUTINE_TYPE 
            =
            ‘
            PROCEDURE
            ‘
            AND ROUTINE_SCHEMA="employees";

 

-- 查看存在的数据库及字符集信息

            SELECT
             
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME

            FROM
            
    INFORMATION_SCHEMA.SCHEMATA;

SHOW DATABASES;

 

-- 查看索引信息

            SELECT
             
    table_schema,
    table_name,
    index_name,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    index_type

            FROM
            
    INFORMATION_SCHEMA.
            STATISTICS
            WHERE
            
    table_name 
            =
            ‘
            employees
            ‘
            AND table_schema =‘employees‘;
SHOW INDEX FROM employees FROM employees;

 

-- 查看数据库大小

            SELECT
             
    table_schema 
            ‘
            database
            ‘
            ,
    CONCAT(
            ROUND(SUM(data_length + index_length) / (1024*1024),
                    2),
            ‘M‘) size
FROM
    information_schema.TABLES
WHERE
    ENGINE = (‘MyISAM‘||‘InnoDB‘)
GROUPBY table_schema;

 

-- 查看表大小　　

            SELECT
             
    CONCAT(table_schema, 
            ‘
            .
            ‘
            , table_name) table_name,
    CONCAT(
            ROUND(data_length / (1024*1024), 2),
            ‘M‘) data_length,
    CONCAT(ROUND(index_length / (1024*1024), 2),
            ‘M‘) index_length,
    CONCAT(ROUND(ROUND(data_length + index_length) / (1024*1024),
                    2),
            ‘M‘) total_size
FROM
    information_schema.TABLES
ORDERBY data_length DESC;

 

原文：http://www.cnblogs.com/zhenxing/p/5340592.html

内容总结

以上是互联网集市为您收集整理的MySQL INFORMATION_SCHEMA 使用全部内容，希望文章能够帮你解决MySQL INFORMATION_SCHEMA 使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1190056.html

来源：【匿名】