MySQL之常用SQL语句

　　1) 分表之后统计数据的总量

SELECT
        (a0.total + a1.total + a2.total + a3.total + a4.total + a5.total + a6.total + a7.total) total
    FROM
    (
        (
            SELECT
                count(*) AS total
            FROM
                wx_00
        ) a0,
        (
            SELECT
                count(*) AS total
            FROM
                wx_01
        ) a1,
        (
            SELECT
                count(*) AS total
            FROM
                wx_02
        ) a2,
        (
            SELECT
                count(*) AS total
            FROM
                wx_03
        ) a3,
        (
            SELECT
                count(*) AS total
            FROM
                wx_04
        ) a4,
        (
            SELECT
                count(*) AS total
            FROM
                wx_05
        ) a5,
        (
            SELECT
                count(*) AS total
            FROM
                wx_06
        ) a6,
        (
            SELECT
                count(*) AS total
            FROM
                wx_07
        ) a7
    );

 

　　2. 导出整个数据库或表中的数据字典

SELECT
    TABLE_NAME as '表名',
    COLUMN_NAME as '字段',
    COLUMN_TYPE as '数据类型',
  COLUMN_DEFAULT as '默认值',
  IS_NULLABLE as '是否为NULL', 
    COLUMN_COMMENT as '备注'
FROM
    information_schema. COLUMNS
WHERE
    TABLE_SCHEMA = 'jiaoni_zmt';

SELECT
    TABLE_NAME as '表名',
    COLUMN_NAME as '字段',
    COLUMN_TYPE as '数据类型',
  COLUMN_DEFAULT as '默认值',
  IS_NULLABLE as '是否为NULL', 
    COLUMN_COMMENT as '备注'
FROM
    information_schema. COLUMNS
WHERE
    TABLE_SCHEMA = 'jiaoni_zmt' AND TABLE_NAME ='app_member';