MySQL 创建视图-蒲公英云

MySQL 创建视图

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]> 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/，如需转载，请注明出处，否则将追究法律责任。

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