mysql 对视图的操作_MySql的视图操作及使用

mysql> show tables;

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

| Tables_in_test |

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

| ai             |

| myai           |

| temp_ai        |

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

3 rows in set (0.00 sec)

mysql> create view v_myai as select * from myai;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_myai;

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

| id | city   | country |

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

|  1 | ???    | china   |

|  2 | ??     | china   |

|  3 | ??     | china   |

|  4 | ??     | china   |

|  5 | 成都   | china   |

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

5 rows in set (0.00 sec)

mysql>

mysql> show tables;

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

| Tables_in_test |

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

| ai             |

| myai           |

| temp_ai        |

| v_myai         |

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

4 rows in set (0.00 sec)

mysql> ---修改及重新创建视图

mysql> create or replace view  v_myai as select * from myai;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_myai;

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

| id | city   | country |

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

|  1 | ???    | china   |

|  2 | ??     | china   |

|  3 | ??     | china   |

|  4 | ??     | china   |

|  5 | 成都   | china   |

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

5 rows in set (0.03 sec)

mysql>

mysql> show tables;

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

| Tables_in_test |

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

| ai             |

| myai           |

| temp_ai        |

| v_myai         |

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

4 rows in set (0.00 sec)

mysql> show table status like '%v_%' \G;  --查看视图信息

*************************** 1. row ***************************

Name: v_myai

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)

ERROR:

No query specified

mysql>

--查看视图定义信息

mysql> show create view v_myai \G;

*************************** 1. row ***************************

View: v_myai

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_myai` AS select `myai`.`id` AS `id`,`myai`.`city` AS `city`,`myai`.`country` AS `country` from `myai`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> ---查看系统表views

mysql> select * from information_schema.views;

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

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                                                                          | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |

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

| NULL          | test         | v_myai     | select `test`.`myai`.`id` AS `id`,`test`.`myai`.`city` AS `city`,`test`.`myai`.`country` AS `country` from `test`.`myai` | NONE         | YES          | root@localhost | DEFINER       | utf8                 | utf8_general_ci      |

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

1 row in set (0.05 sec)

mysql>