读书笔记--SQL必知必会18--视图

读书笔记--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]>