MariaDB VIEW
(jlive)[crashcourse]>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;
+----------------+--------------+---------+
| cust_name
+----------------+--------------+---------+
| Coyote Inc.
| Coyote Inc.
| Coyote Inc.
| Coyote Inc.
| Wascals
| Yosemite Place | Y Sam
| The Fudds
| Coyote Inc.
| Coyote Inc.
| Coyote Inc.
| Coyote Inc.
+----------------+--------------+---------+
11 rows in set (0.00 sec)
(jlive)[crashcourse]>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.00 sec)
(jlive)[crashcourse]>DESC productcustomers;
+--------------+----------+------+-----+---------+-------+
| Field
+--------------+----------+------+-----+---------+-------+
| cust_name
| cust_contact | char(50) | YES
| prod_id
+--------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name
+----------------+--------------+
| Coyote Inc.
| Yosemite Place | Y Sam
+----------------+--------------+
2 rows in set (0.00 sec)
示例二：重新格式化查询到的数据
(jlive)[crashcourse]>SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title
+-------------------------+
| ACME (USA)
| Anvils R Us (USA)
| Furball Inc. (USA)
| Jet Set (England)
| Jouets Et Ours (France) |
| LT Supplies (USA)
+-------------------------+
6 rows in set (0.00 sec)
(jlive)[crashcourse]>CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
Query OK, 0 rows affected (0.00 sec)
(jlive)[crashcourse]>DESC vendorlocations;
+------------+--------------+------+-----+---------+-------+
| Field
+------------+--------------+------+-----+---------+-------+
| vend_title | varchar(103) | YES
+------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>SELECT * FROM vendorlocations;
+-------------------------+
| vend_title
+-------------------------+
| ACME (USA)
| Anvils R Us (USA)
| Furball Inc. (USA)
| Jet Set (England)
| Jouets Et Ours (France) |
| LT Supplies (USA)
+-------------------------+
6 rows in set (0.00 sec)
示例三：过滤不需要的数据
(jlive)[crashcourse]>SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;
+---------+----------------+---------------------+
| cust_id | cust_name
+---------+----------------+---------------------+
|
|
|
+---------+----------------+---------------------+
3 rows in set (0.00 sec)
(jlive)[crashcourse]>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.00 sec)
(jlive)[crashcourse]>DESC customeremaillist;
+------------+-----------+------+-----+---------+-------+
| Field
+------------+-----------+------+-----+---------+-------+
| cust_id
| cust_name
| cust_email | char(255) | YES
+------------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT * FROM customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name
+---------+----------------+---------------------+
|
|
|
+---------+----------------+---------------------+
3 rows in set (0.00 sec)
示例四：混杂计算
(jlive)[crashcourse]>SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|
|
|
|
|
|
|
|
|
|
|
+-----------+---------+----------+------------+----------------+
11 rows in set (0.00 sec)
(jlive)[crashcourse]>CREATE OR REPLACE 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)
(jlive)[crashcourse]>DESC orderitemsexpanded;
+----------------+---------------+------+-----+---------+-------+
| Field
+----------------+---------------+------+-----+---------+-------+
| order_num
| prod_id
| quantity
| item_price
| expanded_price | decimal(18,2) | NO
+----------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
(jlive)[crashcourse]>SELECT * FROM orderitemsexpanded WHERE order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|
|
|
|
+-----------+---------+----------+------------+----------------+
4 rows in set (0.00 sec)
删除VIEW
(jlive)[crashcourse]>DROP VIEW vendorlocations;
Query OK, 0 rows affected (0.16 sec)
