读书笔记--SQL必知必会18--视图
18.1 视图
视图是虚拟的表，只包含使用时动态检索数据的查询。
也就是说作为视图，它不包含任何列和数据，包含的是一个查询。
18.1.1 为什么使用视图
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的一部分而不是整个表
- 保护数据：可以赋予访问表的特定部分的权限
- 可返回与底层表不同格式和表示的数据
如果应用大量或复杂的视图，极可能影响性能，应该先进行测试再应用视图。
18.1.2 视图的规则和限制
- 视图名必须唯一，与其他的视图和表不同
- 视图数目没有限制
- 可以嵌套
- 视图不能索引，也不能有关联的触发器或默认值
不同DBMS对视图语法和表示的定义不同。
例如，有些DBMS会限制视图嵌套的级数、禁止在视图中使用ORDER BY子句、设置视图为只读等等。
18.2 创建视图
使用CREATE VIEW语句来创建视图。
使用DROP VIEW语句来删除视图。
覆盖或更新视图，必须先删除视图，然后再重新创建。
18.2.1 利用视图简化复杂的联结
建议创建不绑定特定数据的视图，增强可重用性。
MariaDB [sqlbzbh]> SHOW TABLES;
+-------------------+
| Tables_in_sqlbzbh |
+-------------------+
| Customers |
| OrderItems |
| Orders |
| Products |
| Vendors |
+-------------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> CREATE VIEW ProductCustomers AS
-> SELECT cust_name, cust_contact, prod_id
-> FROM Customers, Orders, OrderItems
-> WHERE Customers.cust_id = Orders.cust_id
-> AND OrderItems.order_num = Orders.order_num;
Query OK, 0 rows affected (0.01 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SHOW TABLES;
+-------------------+
| Tables_in_sqlbzbh |
+-------------------+
| Customers |
| OrderItems |
| Orders |
| ProductCustomers |
| Products |
| Vendors |
+-------------------+
6 rows in set (0.00 sec)
MariaDB [sqlbzbh]> SELECT cust_name, cust_contact
-> FROM ProductCustomers
-> WHERE prod_id = 'RGAN01';
+---------------+--------------------+
| cust_name | cust_contact |
+---------------+--------------------+
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
+---------------+--------------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
18.2.2 用视图重新格式化检索出的数据
MariaDB [sqlbzbh]> CREATE VIEW VendorLocations AS
-> SELECT Concat(vend_name, '---', vend_country) AS vend_title FROM Vendors;
Query OK, 0 rows affected (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT * FROM VendorLocations;
+-------------------------+
| vend_title |
+-------------------------+
| Bear Emporium---USA |
| Bears R Us---USA |
| Doll House Inc.---USA |
| Fun and Games---England |
| Furball Inc.---USA |
| Jouets et ours---France |
+-------------------------+
6 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
18.2.3 用视图过滤不想要的数据
WHERE子句适用于视图。
MariaDB [sqlbzbh]> CREATE VIEW CustomerEMailList AS
-> SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.01 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT * FROM CustomerEMailList;
+------------+--------------+-----------------------+
| cust_id | cust_name | cust_email |
+------------+--------------+-----------------------+
| 1000000001 | Village Toys | sales@villagetoys.com |
| 1000000003 | Fun4All | jjones@fun4all.com |
| 1000000004 | Fun4All | dstephens@fun4all.com |
+------------+--------------+-----------------------+
3 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
18.2.4 使用视图与计算字段
MariaDB [sqlbzbh]> CREATE VIEW OrderItemsExpanded AS
-> SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems;
Query OK, 0 rows affected (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
| 20008 | RGAN01 | 5 | 4.99 | 24.95 |
| 20008 | BR03 | 5 | 11.99 | 59.95 |
| 20008 | BNBG01 | 10 | 3.49 | 34.90 |
| 20008 | BNBG02 | 10 | 3.49 | 34.90 |
| 20008 | BNBG03 | 10 | 3.49 | 34.90 |
+-----------+---------+----------+------------+----------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>