MySQL 创建视图

妖狐艹你老母 2024-04-18 01:03 74阅读 0赞

MariaDB \[test\]> CREATE TABLE t (qty INT, price INT);  
Query OK, 0 rows affected (0.15 sec)  
  
MariaDB \[test\]> INSERT INTO t VALUES(3, 50);  
Query OK, 1 row affected (0.01 sec)  
  
\--创建视图  
  
MariaDB \[test\]> CREATE VIEW v AS SELECT qty, price, qty\*price AS value FROM t;  
Query OK, 0 rows affected (0.09 sec)  
  
MariaDB \[test\]> SELECT \* FROM v;  
\+------+-------+-------+  
| qty  | price | value |  
\+------+-------+-------+  
|    3 |    50 |   150 |  
\+------+-------+-------+  
1 row in set (0.00 sec)  
  
MariaDB \[test\]> explain select \* from v;  
\+------+-------------+-------+------+---------------+------+---------+------+------+-------+  
| id   | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra |  
\+------+-------------+-------+------+---------------+------+---------+------+------+-------+  
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |  
\+------+-------------+-------+------+---------------+------+---------+------+------+-------+  
1 row in set (0.00 sec)

\--查看视图状态  
  
MariaDB \[test\]> show table status from test like 'v'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
           Name: v  
         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)   
  
\--查看创建语句  
  
MariaDB \[test\]> show create view v\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                View: v  
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=\`root\`@\`localhost\` SQL SECURITY DEFINER VIEW \`v\` AS select \`t\`.\`qty\` AS \`qty\`,\`t\`.\`price\` AS \`price\`,(\`t\`.\`qty\` \* \`t\`.\`price\`) AS \`value\` from \`t\`  
character\_set\_client: utf8  
collation\_connection: utf8\_general\_ci   
  
MariaDB \[test\]> select \* from information\_schema.views where table\_name = 'v'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       TABLE\_CATALOG: def  
        TABLE\_SCHEMA: test  
          TABLE\_NAME: v  
     VIEW\_DEFINITION: select \`test\`.\`t\`.\`qty\` AS \`qty\`,\`test\`.\`t\`.\`price\` AS \`price\`,(\`test\`.\`t\`.\`qty\` \* \`test\`.\`t\`.\`price\`) AS \`value\` from \`test\`.\`t\`  
        CHECK\_OPTION: NONE  
        IS\_UPDATABLE: YES  
             DEFINER: root@localhost  
       SECURITY\_TYPE: DEFINER  
CHARACTER\_SET\_CLIENT: utf8  
COLLATION\_CONNECTION: utf8\_general\_ci  
           ALGORITHM: UNDEFINED  
1 row in set (0.02 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121331/