MySQL.VIEWS
时间：2021-07-01 10:21:17
帮助过：3人阅读
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;
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id
= ‘RGAN01‘;
+---------------+--------------------+
| cust_name
| cust_contact
|
+---------------+--------------------+
| Fun4All
| Denise L. Stephens
|
| The Toy Store
| Kim Howard
|
+---------------+--------------------+
SELECT CONCAT(vend_name,
‘ (‘, vend_country,
‘)‘)
-> FROM Vendors
ORDER BY vend_name;
+--------------------------------------------+
| CONCAT(vend_name,
‘ (‘, vend_country,
‘)‘)
|
+--------------------------------------------+
| Bear Emporium (USA)
|
| Bears R Us (USA)
|
| Doll House Inc. (USA)
|
| Fun
and Games (England)
|
| Furball Inc. (USA)
|
| Jouets et ours (France)
|
+--------------------------------------------+
CREATE VIEW VendorLocations
AS
-> SELECT CONCAT(vend_name,
‘ (‘, vend_country,
‘)‘)
-> AS vend_title
-> FROM Vendors;
Query OK, 0 rows affected (
0.02 sec)
CREATE VIEW CustomersEmailList
AS
-> SELECT cust_id, cust_name, cust_email
-> FROM Customers
-> WHERE cust_email
IS NOT NULL;
Query OK, 0 rows affected (
0.02 sec)
CREATE VIEW OrderItemsExpandedPrice
AS
-> SELECT order_num, prod_id, quantity, item_price, quantity
* item_price
AS expanded_price
-> FROM OrderItems;
Query OK, 0 rows affected (
0.01 sec)
SELECT * FROM OrderItemsExpandedPrice;
+-----------+---------+----------+------------+----------------+
| order_num
| prod_id
| quantity
| item_price
| expanded_price
|
+-----------+---------+----------+------------+----------------+
| 20005 | BR01
| 100 | 5.49 | 549.00 |
| 20005 | BR03
| 100 | 10.99 | 1099.00 |
| 20006 | BR01
| 20 | 5.99 | 119.80 |
| 20006 | BR02
| 10 | 8.99 | 89.90 |
| 20006 | BR03
| 10 | 11.99 | 119.90 |
| 20007 | BR03
| 50 | 11.49 | 574.50 |
| 20007 | BNBG01
| 100 | 2.99 | 299.00 |
| 20007 | BNBG02
| 100 | 2.99 | 299.00 |
| 20007 | BNBG03
| 100 | 2.99 | 299.00 |
| 20007 | RGAN01
| 50 | 4.49 | 224.50 |
| 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 |
| 20009 | BNBG01
| 250 | 2.49 | 622.50 |
| 20009 | BNBG02
| 250 | 2.49 | 622.50 |
| 20009 | BNBG03
| 250 | 2.49 | 622.50 |
+-----------+---------+----------+------------+----------------+
MySQL.VIEWS
标签：
