mysql8学习笔记10--create view

• Create view语句是指将某个查询数据的定义保留下来，以便随时

调用

• view本身不存储查询结果，只是一个定义

 

mysql> select * from orders_temp union all select * from orders_temp2;
+-----------+---------------------+---------+----+
| order_num | order_date          | cust_id | id |
+-----------+---------------------+---------+----+
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|         0 | 2015-10-08 00:00:00 |   10009 |  0 |
+-----------+---------------------+---------+----+
7 rows in set (0.00 sec)

mysql> create view v_order as select * from orders_temp union all select * from orders_temp2;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from v_order;
+-----------+---------------------+---------+----+
| order_num | order_date          | cust_id | id |
+-----------+---------------------+---------+----+
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|         0 | 2015-10-08 00:00:00 |   10009 |  0 |
+-----------+---------------------+---------+----+
7 rows in set (0.00 sec)

Or replace关键词表示当创建的视图已经存在时，执行替换命令

• Select_statement子句则是创建视图的select语句，可以是从表中查询数据，也可以从其他视图中查询数据

• 当视图被创建之后，则其定义就已经固定不会再改变，比如一个视图是由select *创建的，则后续对表增加的字段不会成为视图的一部分，而后续对表删除字段则会导致查询视图失败

• 创建的视图默认情况下是属于当前数据库的，当要创建到另外的数据库时则需要在视图名前面加上数据库名

mysql> show create view v_order;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View    | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                     | character_set_client | collation_connection |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_order | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_order` AS select `orders_temp`.`order_num` AS `order_num`,`orders_temp`.`order_date` AS `order_date`,`orders_temp`.`cust_id` AS `cust_id`,`orders_temp`.`id` AS `id` from `orders_temp` union all select `orders_temp2`.`order_num` AS `order_num`,`orders_temp2`.`order_date` AS `order_date`,`orders_temp2`.`cust_id` AS `cust_id`,`orders_temp2`.`id` AS `id` from `orders_temp2` | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> alter table orders_temp add column order_num2 int(10);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table orders_temp2 add column order_num2 int(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from orders_temp;
+-----------+---------------------+---------+----+------------+
| order_num | order_date          | cust_id | id | order_num2 |
+-----------+---------------------+---------+----+------------+
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |       NULL |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |       NULL |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |       NULL |
+-----------+---------------------+---------+----+------------+
3 rows in set (0.00 sec)

mysql> select * from orders_temp2;
+-----------+---------------------+---------+----+------------+
| order_num | order_date          | cust_id | id | order_num2 |
+-----------+---------------------+---------+----+------------+
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |       NULL |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |       NULL |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |       NULL |
|         0 | 2015-10-08 00:00:00 |   10009 |  0 |       NULL |
+-----------+---------------------+---------+----+------------+
4 rows in set (0.00 sec)

mysql> select * from v_order;
+-----------+---------------------+---------+----+
| order_num | order_date          | cust_id | id |
+-----------+---------------------+---------+----+
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|     20007 | 2005-09-30 00:00:00 |   10004 |  1 |
|     20008 | 2005-10-03 00:00:00 |   10005 |  2 |
|     20009 | 2005-10-08 00:00:00 |   10001 |  3 |
|         0 | 2015-10-08 00:00:00 |   10009 |  0 |
+-----------+---------------------+---------+----+
7 rows in set (0.00 sec)

mysql> 

mysql> alter table orders_temp2 drop column order_num;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from v_order;
ERROR 1356 (HY000): View 'company.v_order' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> 

mysql> create view v_prod as select prod_id,prod_price from products order by prod_price;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_prod;
+---------+------------+
| prod_id | prod_price |
+---------+------------+
| FC      |       2.50 |
| TNT1    |       2.50 |
| FU1     |       3.42 |
| SLING   |       4.49 |
| ANV01   |       5.99 |
| OL1     |       8.99 |
| ANV02   |       9.99 |
| FB      |      10.00 |
| TNT2    |      10.00 |
| DTNTR   |      13.00 |
| ANV03   |      14.99 |
| JP1000  |      35.00 |
| SAFE    |      50.00 |
| JP2000  |      55.00 |
+---------+------------+
14 rows in set (0.00 sec)

mysql> select * from v_prod order by prod_price desc;
+---------+------------+
| prod_id | prod_price |
+---------+------------+
| JP2000  |      55.00 |
| SAFE    |      50.00 |
| JP1000  |      35.00 |
| ANV03   |      14.99 |
| DTNTR   |      13.00 |
| FB      |      10.00 |
| TNT2    |      10.00 |
| ANV02   |       9.99 |
| OL1     |       8.99 |
| ANV01   |       5.99 |
| SLING   |       4.49 |
| FU1     |       3.42 |
| FC      |       2.50 |
| TNT1    |       2.50 |
+---------+------------+
14 rows in set (0.00 sec)

mysql>