40、MySQL-查看视图

查看视图有如下四种方式

1. DESCRIBE
2. SHOW TABLE STATUS LIKE ’视图名'
3. SHOW CREATE VIEW 视图名
4. 系统数据库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)