06-查看视图

选择背景色：黄橙洋红淡粉水蓝草绿白色选择字体：宋体黑体微软雅黑楷体选择字体大小：小中大特恢复默认

Previous 上一篇本节目录 Next 下一篇

从MySQL 5.1版本开始，使用SHOW TABLES命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOW VIEWS命令。

mysql> use sakila

Database changed

mysql> show tables;

+----------------------------+

| Tables_in_sakila |

+----------------------------+

……

| staff |

| staff_list |

| store |

+----------------------------+

26 rows in set (0.00 sec)

同样，在使用SHOW TABLE STATUS命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。所以，可以通过下面的命令显示视图的信息：

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

下面演示的是查看staff_list视图信息的操作：

mysql> show table status like 'staff_list' \G

1. row

Name: staff_list

Engine: NULL

Version: NULL

Row_format: NULL

Rows: NULL

Avg_row_length: NULL

Data_length: NULL

Max_data_length: NULL

Index_length: NULL

Data_free: NULL

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: NULL

Checksum: NULL

Create_options: NULL

Comment: VIEW

1 row in set (0.01 sec)

如果需要查询某个视图的定义，可以使用SHOW CREATE VIEW命令进行查看：

mysql> show create view staff_list \G

** 1. row ****

View: staff_list

Create View: CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW 'staff_list' AS select 's'.'staffid' AS 'ID',concat ('s'.'first name',_utf8' ','s'.'lastname') AS 'name','a'.'address' AS 'address','a'.'postal code' AS 'zip code','a'.'phone' AS 'phone','city'.'city' AS 'city','country'. 'country' AS 'country','s''.'store_id' AS 'SID' from ((('staff' 's' join 'address' 'a' on(('s'.'address_id'= 'a'.'address_id'))) join 'city' on(('a'.'city_id' = 'city'.'city_id'))) join 'country' on(('city'.'country_id' = 'country'.'country_id')))

1 row in set (0.00 sec)

最后，通过查看系统表information_schema.views也可以查看视图的相关信息：

mysql> select * from views where table_name = 'staff_list' \G

1. row

TABLE_CATALOG: NULL

TABLE_SCHEMA: sakila

TABLE_NAME: staff_list

VIEW_DEFINITION: select 's'.'staff_id' AS 'ID',concat('s'.'firstname', utf8' ','s'.'last_name') AS 'name','a'.'address' AS 'address','a'.'postal_code' AS 'zip code','a'.'phone' AS 'phone','sakila'.'city'.'city' AS 'city','sakila'. 'country'. 'country' AS 'country','s'.'store_id' AS 'SID' from ((('sakila'.'staff' 's' join 'sakila'.'address' 'a' on(('s'.'address_id' = 'a'.'address_id'))) join 'sakila'.'city' on(('a'.'city_id' ='sakila'.'city'.'city_id'))) join 'sakila'.'country' on(('sakila'. 'city'.'country_id' ='sakila'.'country'.'country_id')))

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

1 row in set (0.00 sec)

Previous 上一篇本节目录 Next 下一篇