mysqlshow 客户端
mysqlshow 客户端为各种形式的 SHOW 语句提供命令行界面,这些语句列出了数据库的名称、数据库中的表或者有关表列或者索引的信息。
一般语法:
shell> mysqlshow [options] [db_name [table_name [column_name]]]
mysqlshow 客户端的选项部分可以包括任何标准连接参数选项,例如 -host 或者 -user。
如果默认连接参数不合适,我们必须提供选项。
mysqlshow 还接受特定于其自身操作的选项。
使用 -help 选项调用 mysqlshow 以查看其选项的完整列表。
mysqlshow 执行的操作取决于提供的非选项参数的数量。
DESCRIBE 语句
DESCRIBE
DESCRIBE可以简写为DESC,如下;
mysql> DESCRIBE table_name; and mysql> DESC table_name;
下面的示例显示了 INFORMATION_SCHEMA 表信息
MySQL> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME | varchar(64) | NO | | | | | DEFAULT_COLLATE_NAME | varchar(64) | NO | | | | | DESCRIPTION | varchar(60) | NO | | | | | MAXLEN | boirnt(3) | NO | | 0 | | +----------------------+-------------+------+-----+---------+-------+
以下等效于上述 DESCRIBE/DESC 示例:
mysql> SHOW COLUMNS FROM table_name;
但是,虽然 SHOW COLUMNS 支持可选的 LIKE 和 WHERE 子句,但 DESCRIBE 不支持。
解释
当给定表名作为参数时,EXPLAIN 等效于 DESCRIBE:
mysql> EXPLAIN table_name;
使用 INFORMATION_SCHEMA 表创建 Shell 命令
我们可以使用 INFORMATION_SCHEMA 表来获取有关创建 shell 命令的信息。
使用 CONCAT 函数,我们可以组合字符串内容来创建可从命令行执行的 shell 脚本。
如下例所示,SQL 语句生成的输出仅转储 world_innodb 数据库中以单词“Country”开头的那些表。
mysql> SELECT CONCAT("mysqldump -uroot -p ", -> TABLE_SCHEMA," ", TABLE_NAME, " >> ", -> TABLE_SCHEMA,".bak.sql") -> FROM TABLES WHERE TABLE_NAME LIKE 'Country%';
- 产生以下 shell 命令:
shell> mysqldump -uroot -p world_innodb Country >> world_innodb.bak.sql shell> mysqldump -uroot -p world_innodb CountryLanguage >> world_innodb.bak.sql
下一步是将此输出存储在可以从 shell 命令行执行的批处理文件中。
这是通过添加子句 INTO OUTFILE 来完成的:
mysql> SELECT CONCAT("mysqldump -uroot -p ", -> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql") -> FROM TABLES WHERE TABLE_NAME LIKE 'Country%' -> INTO OUTFILE '/Country_Dump.sh';
然后可以从命令行执行此文件,该命令行运行幻灯片中显示的两个 mysqldump 命令:
shell> \tmp\Country_Dump.sh shell> \tmp\mysqldump -uroot -poracle world_innodb Country >> world_innodb.sql shell> \tmp\mysqldump -uroot -poracle world_innodb Country_Language >> world_innodb.sql
数据库是数据的结构化集合。
元数据是“关于数据的数据”。
使用以下方法,MySQL 提供对数据库服务器管理的数据库、表和其他对象的元数据的访问:
INFORMATION_SCHEMA :MySQL 服务器包含一个数据字典,该字典实现为名为 INFORMATION_SCHEMA 的数据库(架构),其中包括许多看似表的对象。
SHOW 语句:用于获取有关服务器统计信息、模式和模式对象的数据的专有语法:
- SHOW DATABASES 和 SHOW TABLES :返回数据库和表名的列表。
- SHOW COLUMNS :生成表中列的定义。
- 使用 SHOW 语句需要 SELECT 权限。
DESCRIBE :可用于检查表结构和列属性的 SQL 语句快捷方式。
mysqlshow :客户端程序,我们可以将其用作一些 SHOW 语句的命令行前端。
我们设置的参数确定要显示的信息,然后程序发出适当的 SHOW 语句并显示该语句的结果。
将 SELECT 与 INFORMATION_SCHEMA 一起使用
当我们使用 SELECT 语句从 INFORMATION_SCHEMA 表中检索元数据时,我们可以使用任何常用的 SELECT 功能。
我们可以使用 CREATE TABLE...SELECT 语句或者 INSERT...SELECT 语句将 INFORMATION_SCHEMA 查询的结果检索到另一个表中。
这使我们能够保存结果并稍后在其他语句中使用它们。
INFORMATION_SCHEMA:示例
mysql> SELECT TABLE_NAME, ENGINE -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'world_innodb'; mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE DATA_TYPE = 'set'; mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME -> FROM INFORMATION_SCHEMA.COLLATIONS -> WHERE IS_DEFAULT = 'Yes'; mysql> SELECT TABLE_SCHEMA, COUNT(*) -> FROM INFORMATION_SCHEMA.TABLES -> GROUP BY TABLE_SCHEMA; mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
上面的示例演示了如何利用 SELECT 的各种功能以不同方式从 INFORMATION_SCHEMA 表中提取信息:
- 显示用于给定数据库中的表的存储引擎
- 查找所有包含 SET 列的表
- 显示每个字符集的默认排序规则
- 显示每个数据库中的表数
- INFORMATION_SCHEMAtables 是只读的,不能用 INSERT、DELETE 或者 UPDATE 等语句修改。如果我们执行这些类型的语句以尝试更改 INFORMATION_SCHEMAtables 中的数据,则服务器会产生错误
使用 INFORMATION_SCHEMA 表创建 SQL 语句
使用 mysql 命令创建 SQL 语句。
使用 -e 选项输入 SELECT/CONCAT 语句:
shell> mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world_innodb';"
上述查询导致以下语句发送到标准输出:
CREATE TABLE world_innodb.City_backup LIKE world_innodb.City; CREATE TABLE world_innodb.Country_backup LIKE world_innodb.Country_backup; CREATE TABLE world_innodb.CountryLanguage_backup LIKE world_innodb.CountryLanguage_backup;
INFORMATION_SCHEMA 表创建可以从命令行执行的 SQL 语句。
幻灯片中的示例使用 mysql 命令执行一条语句,该语句对 world_innodb 数据库中的所有表进行精确复制。
此命令创建一个 SQL 输出,如果执行该输出,将基于 world_innodb 数据库中的表创建三个备份表。
添加管道符号( | ),然后执行 mysql 命令将这些 SQL 语句发送到要执行的 MySQL 服务器:
shell> mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle
INFORMATION_SCHEMA 表
要列出 INFORMATION_SCHEMA 数据库中的所有表,请使用以下查询:
mysql> SELECT TABLE_NAME -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'information_schema' -> ORDER BY TABLE_NAME; +---------------------------------------+ | TABLE_NAME | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | ... | USER_PRIVILEGES | | VIEWS | +---------------------------------------+
INFORMATION_SCHEMAtables 包含以下类型的信息:
表信息
- COLUMNS :表和视图中的列
- 引擎:存储引擎
- 模式:数据库
- 表:数据库中的表
- VIEWS :数据库中的视图
分区
- PARTITIONS : 表分区
- FILES :存储 MySQL NDB 磁盘数据表的文件
特权
- COLUMN_PRIVILEGES : MySQL 用户账户持有的列权限
- SCHEMA_PRIVILEGES : MySQL 用户帐户持有的数据库权限
- TABLE_PRIVILEGES : MySQL 用户账户持有的表权限
- USER_PRIVILEGES : MySQL 用户帐户持有的全局权限
字符集支持
- CHARACTER_SETS : 可用的字符集
- COLLATIONS : 每个字符集的排序规则
- COLLATION_CHARACTER_SET_APPLICABILITY :适用于特定字符集的排序规则
约束和索引
- KEY_COLUMN_USAGE : 键列的约束
- REFERENTIAL_CONSTRAINTS : 外键
- STATISTICS : 表索引
- TABLE_CONSTRAINTS :对表的约束
服务器设置和状态
- KEY_COLUMN_USAGE :约束
- GLOBAL_STATUS :所有 MySQL 连接的状态值
- GLOBAL_VARIABLES :用于到 MySQL 的新连接的值
- 插件:服务器插件
- ROCESSLIST :指示正在运行的线程
- SESSION_STATUS : 当前连接到 MySQL 的状态值
- SESSION_VARIABLES :对当前连接到 MySQL 有效的值
例程和相关信息
- 活动:预定活动
- ROUTINES:存储过程和函数
- TRIGGERS : 数据库中的触发器
- PARAMETERS : 存储过程和函数参数,以及存储函数
数据库
- INNODB_CMP 和 INNODB_CMP_RESET :与压缩 InnoDB 表相关的操作状态
- INNODB_CMPMEMand INNODB_CMPMEM_RESET :InnoDB 缓冲池中压缩页面的状态
- INNODB_LOCKS :InnoDB 事务请求并持有的每个锁
- INNODB_LOCK_WAITS :每个阻塞的 InnoDB 事务的一个或者多个行锁
- INNODB_TRX : 当前在 InnoDB 中执行的每个事务
- TABLESPACES : 活动表空间
INFORMATION_SCHEMA 数据库
INFORMATION_SCHEMA 数据库用作数据库元数据的中央存储库。
它是一个“虚拟数据库”,因为它不存储在磁盘的任何位置;但是,它像任何其他数据库一样包含表,并且可以像任何其他表一样使用 SELECT 访问其表的内容。
此外,SELECT 语句可用于获取 INFORMATION_SCHEMAtables
由于是虚拟数据库,MySQL通过读取表等对象元数据来构建查询结果。
当我们执行读取有关许多表的信息的查询时,MySQL 必须执行许多磁盘级操作,这可能会花费大量时间。
MySQL 支持的 SHOW 语句
除了 INFORMATION_SCHEMA 表,MySQL 还支持 SHOW 和 DESCRIBE 语句作为访问元数据的替代方法。
SHOW 和 DESCRIBE 语法不如使用 INFORMATION_SCHEMAqueries 灵活,但对于许多用途来说,SHOW 和 DESCRIBE 语法就足够了。
在这些情况下,使用此 MySQL 特定语法通常会更快、更容易。
SHOW 语句可以以多种形式使用,如下所示:
- SHOW DATABASES :列出可用数据库的名称
- SHOW TABLES :列出默认数据库中的表
- SHOW TABLES FROM [database_name] :列出指定数据库中的表
- SHOW COLUMNS FROM [table_name] :显示表的列结构
- SHOW INDEX FROM [table_name] : 显示表中索引和索引列的信息
- SHOW CHARACTER SET :显示可用字符集及其默认排序规则
- SHOW COLLATION : 显示每个字符集的排序规则
mysqlshow:示例
我们可以使用 mysqlshow 查询显示所有数据库或者特定数据库、表和/或者列的信息。
例如:
shell> mysqlshow -u -p +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | world_innodb | +--------------------+
shell> mysqlshow world_innodb shell> mysqlshow world_innodb City shell> mysqlshow world_innodb City CountryCode shell> mysqlshow "w%"
上面的例子演示了 mysqlshow 客户端的一些用法:
- 在没有参数的情况下,mysqlshow 显示类似于 SHOW DATABASES 的结果。
- 使用单个参数,mysqlshow 将其解释为数据库名称并显示类似于数据库的 SHOW TABLES 的结果。
- 使用两个参数,mysqlshow 将它们解释为数据库和表名,并显示类似于表的 SHOW FULL COLUMNS 的结果。
- 对于三个参数,输出与两个参数相同,除了 mysqlshow 将第三个参数作为列名并仅显示该列的 SHOW FULL COLUMNSoutput。
- 如果命令行上的最后一个参数包含特殊字符,mysqlshow 将该参数解释为模式并仅显示与该模式匹配的名称。特殊字符是 % 或者 * 以匹配任何字符序列,以及 _ 或者 ?匹配任何单个字符。此示例命令仅显示名称以 w 开头的那些数据库。
SHOW 语句:示例
一些常用的 SHOW 语句:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | world_innodb | +--------------------+
MySQL> SHOW TABLES; MySQL> SHOW TABLES FROM mysql; MySQL> SHOW TABLES FROM INFORMATION_SCHEMA; MySQL> SHOW COLUMNS FROM CountryLanguage; MySQL> SHOW FULL COLUMNS FROM CountryLanguage\G
其他 SHOW 语句示例
- 用 LIKE 和 WHERE 显示:
MySQL> SHOW DATABASES LIKE 'm%'; MySQL> SHOW COLUMNS FROM Country -> WHERE `Default` IS NULL;
- 其他 SHOW 语句:
MySQL> SHOW INDEX FROM City\G MySQL> SHOW CHARACTER SET; MySQL> SHOW COLLATION;