查询 数据库的版本

  1. select version();

查询 当前连接的是哪一个数据库

select database();

获取 当前数据库所在的操作目录

show variables like 'datadir';

查询 某个数据库下的所有表名称

SELECT
    table_name
FROM
    information_schema. TABLES
WHERE
    table_schema = '表名称'
AND table_type = 'base table';

查询 指定数据库中指定表的所有字段名column_name

SELECT
    column_name
FROM
    information_schema. COLUMNS
WHERE
    table_schema = 'patrol'
AND table_name = '数据表名称'

查询 所有数据库，表所占磁盘空间大小

先 use information_schema;

SELECT
    concat(
        round(
            sum(DATA_LENGTH / 1024 / 1024),
            2
        ),
        'MB'
    ) AS SIZE
FROM
    TABLES;

查询 某个数据库 所占磁盘空间大小

SELECT
    concat(
        round(
            sum(DATA_LENGTH / 1024 / 1024),
            2
        ),
        'MB'
    ) AS SIZE
FROM
    TABLES
WHERE
    table_schema = '数据库名称';

查询 某个数据表的大小

SELECT
    concat(
        round(
            sum(DATA_LENGTH / 1024 / 1024),
            2
        ),
        'MB'
    ) AS SIZE
FROM
    TABLES
WHERE
    table_schema = '数据库名称'
AND table_name = '数据表名称';

查询 某个数据库索引的大小

SELECT
    CONCAT(
        ROUND(
            SUM(index_length) / (1024 * 1024),
            2
        ),
        ' MB'
    ) AS 'Index Size'
FROM
    TABLES
WHERE
    table_schema = '数据库名称';

查询 某个数据表索引的大小

SELECT
    CONCAT(
        ROUND(
            SUM(index_length) / (1024 * 1024),
            2
        ),
        ' MB'
    ) AS 'Total Index Size'
FROM
    TABLES WHERE table_schema = '数据库名称'
AND table_name = '数据表名称';

查询 某个数据库的占用大小的具体情况

SELECT
    CONCAT(
        table_schema,
        '.',
        table_name
    ) AS 'Table Name',
    CONCAT(
        ROUND(table_rows / 1000000, 4),
        'M'
    ) AS 'Number of Rows',
    CONCAT(
        ROUND(
            data_length / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Data Size',
    CONCAT(
        ROUND(
            index_length / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Index Size',
    CONCAT(
        ROUND(
            (data_length + index_length) / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Total'
FROM
    information_schema. TABLES
WHERE
    table_schema LIKE '数据表名称';

查询 某个DB下所有表使用的搜索引擎

SELECT
    table_name,
    table_type,
    ENGINE
FROM
    information_schema. TABLES
WHERE
    table_schema = 'e_ort'
ORDER BY
    table_name DESC;