23.2. SHOW语句的扩展
优质
小牛编辑
126浏览
2023-12-01
某些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|
+---------------------------------------+
| SCHEMATA|
| TABLES|
| COLUMNS |
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| ROUTINES|
| STATISTICS |
| VIEWS |
| TRIGGERS|
| USER_PRIVILEGES|
| SCHEMA_PRIVILEGES |
| TABLE_PRIVILEGES|
| COLUMN_PRIVILEGES |
| TABLE_CONSTRAINTS |
| KEY_COLUMN_USAGE|
+---------------------------------------+
SHOW COLUMNS和DESCRIBE能够显示单独INFORMATION_SCHEMA表中的列信息。
扩展了一些SHOW语句,允许使用WHERE子句:
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
如果有WHERE子句的话,将根据SHOW语句显示的列名进行计算。例如,SHOW COLLATION语句可产生这些输出列:
例如,SHOW CHARACTER SET语句可产生这些输出列:
mysql> SHOW CHARACTER SET;
Charset | 描述 | 默认校对 | 最大长度 |
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 |
要想与SHOW CHARACTER SET一起使用WHERE子句,应引用这些列名称。例如,在下面的语句中,给出了用于默认校对且含有字符串“japanese”的字符集的信息:
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
描述
Charset | <td valign="top"默认校对 | 最大长度 | |
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 | 描述 | 默认校对 | 最大长度 |
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 |