SHOW VIEWS
描述
SHOW VIEWS
语句返回指定数据库(可选)的所有视图。此外,可以使用可选的匹配模式过滤此语句的输出。如果未指定数据库,则从当前数据库返回视图。如果指定的数据库是全局临时视图数据库,我们将列出全局临时视图。请注意,无论给定哪个数据库,该命令也会列出本地临时视图。
语法
SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
参数
-
{ FROM
|
IN } database_name指定要列出其视图的数据库名称。
-
regex_pattern
指定用于过滤掉不需要的视图的正则表达式模式。
- 除
*
和|
字符外,该模式的工作方式与正则表达式相同。 *
单独匹配 0 个或多个字符,|
用于分隔多个不同的正则表达式,其中任何一个都可以匹配。- 在处理之前,输入模式中的前导和尾随空格将被删除。模式匹配不区分大小写。
- 除
示例
-- Create views in different databases, also create global/local temp views.
CREATE VIEW sam AS SELECT id, salary FROM employee WHERE name = 'sam';
CREATE VIEW sam1 AS SELECT id, salary FROM employee WHERE name = 'sam1';
CREATE VIEW suj AS SELECT id, salary FROM employee WHERE name = 'suj';
USE userdb;
CREATE VIEW user1 AS SELECT id, salary FROM default.employee WHERE name = 'user1';
CREATE VIEW user2 AS SELECT id, salary FROM default.employee WHERE name = 'user2';
USE default;
CREATE GLOBAL TEMP VIEW temp1 AS SELECT 1 AS col1;
CREATE TEMP VIEW temp2 AS SELECT 1 AS col1;
-- List all views in default database
SHOW VIEWS;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| default | sam | false |
| default | sam1 | false |
| default | suj | false |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views from userdb database
SHOW VIEWS FROM userdb;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| userdb | user1 | false |
| userdb | user2 | false |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views in global temp view database
SHOW VIEWS IN global_temp;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| global_temp | temp1 | true |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views from default database matching the pattern `sam*`
SHOW VIEWS FROM default LIKE 'sam*';
+-----------+------------+--------------+
| namespace | viewName | isTemporary |
+-----------+------------+--------------+
| default | sam | false |
| default | sam1 | false |
+-----------+------------+--------------+
-- List all views from the current database matching the pattern `sam|suj|temp*`
SHOW VIEWS LIKE 'sam|suj|temp*';
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| default | sam | false |
| default | suj | false |
| | temp2 | true |
+-------------+------------+--------------+