25、MySQL的视图

好处：
1、简化sql语句，实现代码的重用，隐藏业务逻辑
2、保护数据
3、改变数据格式
案例1：创建视图

mysql> select cust_name, cust_contact from customers c, orders o, orderitems oi where c.cust_id = o.cust_id and o.order_num = oi.order_num and cust_contact = 'Y Sam';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Yosemite Place | Y Sam        |
+----------------+--------------+
1 row in set (0.08 sec)

创建视图

mysql> create view vcust as select cust_name, cust_contact from customers c, orders o, orderitems oi where c.cust_id = o.cust_id and o.order_num = oi.order_num;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from vcust;
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Coyote Inc.    | Y Lee        |
| Wascals        | Jim Jones    |
| Yosemite Place | Y Sam        |
| E Fudd         | E Fudd       |
+----------------+--------------+
11 rows in set (0.09 sec)

查看如何创建视图

mysql> show create view vcust;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vcust | CREATE ALGORITHM=UNDEFINED DEFINER=`ding`@`%` SQL SECURITY DEFINER VIEW `vcust` AS select `c`.`cust_name` AS `cust_name`,`c`.`cust_contact` AS `cust_contact` from ((`customers` `c` join `orders` `o`) join `orderitems` `oi`) where ((`c`.`cust_id` = `o`.`cust_id`) and (`o`.`order_num` = `oi`.`order_num`)) | utf8mb4              | utf8mb4_general_ci   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.09 sec)

删除视图

drop view vcust;

视图内容的格式化

mysql> create view v_vendor as select concat(ltrim(vend_name), ' (', vend_country, ')') from vendors;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from v_vendor;
+---------------------------------------------------+
| concat(ltrim(vend_name), ' (', vend_country, ')') |
+---------------------------------------------------+
| Anvils R Us (USA)                                 |
| LT Supplies (USA)                                 |
| ACME (USA)                                        |
| Furball Inc. (USA)                                |
| Jet Set (England)                                 |
| Jouets Et Ours (France)                           |
+---------------------------------------------------+
6 rows in set (0.07 sec)

视图过滤功能，同上
计算字段视图，非动态的计算字段

mysql> create view orderprice as select prod_id, quantity, item_price, (quantity * item_price) as totle from orderitems;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from orderprice;
+---------+----------+------------+---------+
| prod_id | quantity | item_price | totle   |
+---------+----------+------------+---------+
| ANV01   |       10 | 5.99       | 59.90   |
| ANV02   |        3 | 9.99       | 29.97   |
| TNT2    |        5 | 10.00      | 50.00   |
| FB      |        1 | 10.00      | 10.00   |
| JP2000  |        1 | 55.00      | 55.00   |
| TNT2    |      100 | 10.00      | 1000.00 |
| FC      |       50 | 2.50       | 125.00  |
| FB      |        1 | 10.00      | 10.00   |
| OL1     |        1 | 8.99       | 8.99    |
| SLING   |        1 | 4.49       | 4.49    |
| ANV03   |        1 | 14.99      | 14.99   |
+---------+----------+------------+---------+
11 rows in set (0.09 sec)

视图是虚拟表，速度相同
6、视图是否可以更新？ 可以，但有条件
不可以包含个group by 信息
不可以包含子查询
不可以包含max min avg sum
不可以包含计算字段
不可以包含distinct唯一值