查看视图有如下四种方式
- DESCRIBE
- SHOW TABLE STATUS LIKE ’视图名'
- SHOW CREATE VIEW 视图名
- 系统数据库information_schema 的views表中
1、DESCRIBE
— 能查看视图的列信息
mysql>
DESCRIBE v1;
+----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 18 | |
| salary | float(6,2) | NO | | 0.00 | |
| sex | enum('男','女','保密') | YES | | NULL | |
| depId | tinyint(3) unsigned | YES | | NULL | |
| pId | tinyint(3) unsigned | YES | | 1 | |
+----------+----------------------------+------+-----+---------+-------+
2、SHOW TABLE STATUS LIKE ''
— 这个命令用来查看表的信息,因为视图可以认为是一张虚拟表,也可以使用这个命令,但是信息大都为空
mysql> SHOW TABLE STATUS LIKE 'v2'\G;
*************************** 1. row ***************************
Name: v2
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.00 sec)
3、SHOW CREATE VIEW 视图名
— 一般查看视图信息用这个命令
mysql> SHOW CREATE VIEW v3\G;
*************************** 1. row ***************************
View: v3
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `e`.`id` AS `id`,`e`.`username` AS `name`,`d`.`depName` AS `depName` from (`employee` `e` join `department` `d` on((`d`.`id` = `e`.`depId`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)