MySQL Life

MySql的视图操作及使用

时间:2017-11-24 21:31:33  作者:solgle  来源:solgle.com  查看:34  评论:0
内容摘要: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> 

 
 
标签:mysql视图 

solgle.com 版权所有,欢迎分享!!!

相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1