mysql 视图报错1356,mysql视图初探 - osc_ky6f5kf1的个人空间 - OSCHINA - 中文开源技术交流社区...

mysql视图初探

官方例子如下，从官方的例子就可以看出来视图就是提供一种快捷查询。用视图来查询一些常用的结果。

mysql> help create view;

Name: 'CREATE VIEW'

Description:

Syntax:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view, or replaces an existing

view if the OR REPLACE clause is given. If the view does not exist,

CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does

exist, CREATE OR REPLACE VIEW replaces it.

For information about restrictions on view use, see

http://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html.

The select_statement is a SELECT statement that provides the definition

of the view. (Selecting from the view selects, in effect, using the

SELECT statement.) The select_statement can select from base tables or

other views.

...

mysql> CREATE TABLE t (qty INT, price INT);

mysql> INSERT INTO t VALUES(3, 50);

mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

mysql> SELECT * FROM v;

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

| qty | price | value |

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

| 3 | 50 | 150 |

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

官网例子使用

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| city |

| country |

| dept |

| emp |

| t1 |

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

5 rows in set (0.00 sec)

mysql> create table goodsview (num int,price int);

Query OK, 0 rows affected (0.25 sec)

mysql> insert into goodsview values (3,50),(5,60);

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from goodsview;

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

| num | price |

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

| 3 | 50 |

| 5 | 60 |

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

2 rows in set (0.00 sec)

mysql> create view gview as select num,price ,num*price as value from goodsview;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from gview;

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

| num | price | value |

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

| 3 | 50 | 150 |

| 5 | 60 | 300 |

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

2 rows in set (0.00 sec)

表名替换后查询view

表名替换后原有的view肯定不可用

mysql> alter table goodsview rename to goodviewtable;

Query OK, 0 rows affected (0.11 sec)

mysql> show create table goodviewtable;

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

| Table | Create Table |

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

| goodviewtable | CREATE TABLE `goodviewtable` (

`num` int(11) DEFAULT NULL,

`price` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

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

1 row in set (0.00 sec)

mysql> show create table gview;

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

| View | Create View | character_set_client | collation_connection |

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

| gview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `gview` AS select `goodsview`.`num` AS `num`,`goodsview`.`price` AS `price`,(`goodsview`.`num` * `goodsview`.`price`) AS `value` from `goodsview` | latin1 | latin1_swedish_ci |

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

1 row in set, 1 warning (0.00 sec)

mysql> select * from gview;

ERROR 1356 (HY000): View 'test.gview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql>

删除视图

mysql> drop view gview;

Query OK, 0 rows affected (0.03 sec)

mysql>