• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • SHOW语句的扩展

    SHOW语句的某些扩展伴随以下实现INFORMATION_SCHEMA

    • SHOW可用于获取有关其INFORMATION_SCHEMA自身结构的信息。
    • 多个SHOW语句接受一个WHERE子句,该子句在指定要显示的行时提供了更大的灵活性。

    INFORMATION_SCHEMA是一个信息数据库,因此其名称包含在的输出中SHOW DATABASES。同样,SHOW TABLES可用于INFORMATION_SCHEMA获取其表的列表:

    mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
    +---------------------------------------	+
    | Tables_in_INFORMATION_SCHEMA	|
    +---------------------------------------	+
    | CHARACTER_SETS	|
    | COLLATIONS	|
    | COLLATION_CHARACTER_SET_APPLICABILITY	|
    | COLUMNS	|
    | COLUMN_PRIVILEGES	|
    | ENGINES	|
    | EVENTS	|
    | FILES	|
    | KEY_COLUMN_USAGE	|
    | PARTITIONS	|
    | PLUGINS	|
    | PROCESSLIST	|
    | REFERENTIAL_CONSTRAINTS	|
    | ROUTINES	|
    | SCHEMATA	|
    | SCHEMA_PRIVILEGES	|
    | STATISTICS	|
    | TABLES	|
    | TABLE_CONSTRAINTS	|
    | TABLE_PRIVILEGES	|
    | TRIGGERS	|
    | USER_PRIVILEGES	|
    | VIEWS	|
    +---------------------------------------	+
    

    SHOW COLUMNSDESCRIBE可以显示有关各个INFORMATION_SCHEMA表中列的信息。

    SHOW接受LIKE用来限制显示行的子句的语句还允许一个WHERE子句,该子句指定所选行必须满足的更一般的条件:

    SHOW CHARACTER SET
    SHOW COLLATION
    SHOW COLUMNS
    SHOW DATABASES
    SHOW FUNCTION STATUS
    SHOW INDEX
    SHOW OPEN TABLES
    SHOW PROCEDURE STATUS
    SHOW STATUS
    SHOW TABLE STATUS
    SHOW TABLES
    SHOW TRIGGERS
    SHOW VARIABLES
    

    WHERE子句(如果存在)将根据该SHOW语句显示的列名进行评估。例如,该SHOW CHARACTER SET语句产生以下输出列:

    mysql> SHOW CHARACTER SET;
    +----------	+-----------------------------	+---------------------	+--------	+
    | Charset	| Description	| Default collation	| Maxlen	|
    +----------	+-----------------------------	+---------------------	+--------	+
    | big5	| Big5 Traditional Chinese	| big5_chinese_ci	|      2	|
    | dec8	| DEC West European	| dec8_swedish_ci	|      1	|
    | cp850	| DOS West European	| cp850_general_ci	|      1	|
    | hp8	| HP West European	| hp8_english_ci	|      1	|
    | koi8r	| KOI8-R Relcom Russian	| koi8r_general_ci	|      1	|
    | latin1	| cp1252 West European	| latin1_swedish_ci	|      1	|
    | latin2	| ISO 8859-2 Central European	| latin2_general_ci	|      1	|
    ...
    

    要将WHERE子句与一起使用SHOW CHARACTER SET,您将引用这些列名称。例如,以下语句显示有关字符集的信息,其默认归类包含字符串'japanese'

    mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
    +---------	+---------------------------	+---------------------	+--------	+
    | Charset	| Description	| Default collation	| Maxlen	|
    +---------	+---------------------------	+---------------------	+--------	+
    | ujis	| EUC-JP Japanese	| ujis_japanese_ci	|      3	|
    | sjis	| Shift-JIS Japanese	| sjis_japanese_ci	|      2	|
    | cp932	| SJIS for Windows Japanese	| cp932_japanese_ci	|      2	|
    | eucjpms	| UJIS for Windows Japanese	| eucjpms_japanese_ci	|      3	|
    +---------	+---------------------------	+---------------------	+--------	+
    

    此语句显示多字节字符集:

    mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
    +---------	+---------------------------	+---------------------	+--------	+
    | Charset	| Description	| Default collation	| Maxlen	|
    +---------	+---------------------------	+---------------------	+--------	+
    | big5	| Big5 Traditional Chinese	| big5_chinese_ci	|      2	|
    | ujis	| EUC-JP Japanese	| ujis_japanese_ci	|      3	|
    | sjis	| Shift-JIS Japanese	| sjis_japanese_ci	|      2	|
    | euckr	| EUC-KR Korean	| euckr_korean_ci	|      2	|
    | gb2312	| GB2312 Simplified Chinese	| gb2312_chinese_ci	|      2	|
    | gbk	| GBK Simplified Chinese	| gbk_chinese_ci	|      2	|
    | utf8	| UTF-8 Unicode	| utf8_general_ci	|      3	|
    | ucs2	| UCS-2 Unicode	| ucs2_general_ci	|      2	|
    | cp932	| SJIS for Windows Japanese	| cp932_japanese_ci	|      2	|
    | eucjpms	| UJIS for Windows Japanese	| eucjpms_japanese_ci	|      3	|
    +---------	+---------------------------	+---------------------	+--------	+