07 表和视图

7.1 创建表

AUTO_INCREMENT

每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如,让它成为主键)

引擎类型

几种引擎:

  • InnoDB:支持事务处理,不支持全文本搜索

  • MyISAM:支持全文本搜索,不支持事务处理,性能高

  • MEMORY:功能同MyISAM,但数据存在内存中,速度快(适合临时表)

注意,外键不能跨引擎。

7.2 更新表 ALTER TABLE

ALTER TABLE要极为小心,应该在改动前进行一个完整的备份(包括数据和模式)。

添加一个列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除一个列

ALTER TABLE vendors
DROP COLUMN vend_phone;

添加外键

ALTER TABLE orders
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);

7.3 删除表

DROP TABLE customers;

7.4 重命名表

ALTER TABLE customers2 TO customers;

7.5 视图

概述

视图是虚拟的表。视图只包含使用时动态检索数据的查询。

由于视图本身不包含数据,它们返回的数据是从其他表中检索出来的。在每次使用视图时,都必须处理查询执行时所需的检索。所以在大量使用视图时,一定注意性能问题。

使用视图的规则和限制:

  • 视图可以嵌套

  • 视图不能索引,不能有关联的触发器或默认值

  • 视图可以和表一起使用

视图语句

  • CREATE VIEW 创建视图

  • SHOW CREATE VIEW viewname 查看创建视图的语句

  • DROP VIEW viewname 删除视图

  • CREATE OR REPLACE VIEW 更新视图

视图使用1-简化复杂的联结

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;

我们现在可以直接使用productcustomers这个视图

mysql> SELECT * FROM productcustomers;
+----------------+--------------+---------+
| cust_name | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc. | Y Lee | ANV01 |
| Coyote Inc. | Y Lee | ANV02 |
| Coyote Inc. | Y Lee | TNT2 |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | OL1 |
| Coyote Inc. | Y Lee | SLING |
| Coyote Inc. | Y Lee | ANV03 |
| Wascals | Jim Jones | JP2000 |
| Yosemite Place | Y Sam | TNT2 |
| E Fudd | E Fudd | FC |
+----------------+--------------+---------+

使用视图2-重新格式化检索出的数据

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
mysql> SELECT * FROM vendorlocations;
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+

使用视图3-过滤掉不想要的数据

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
mysql> SELECT * FROM customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name | cust_email |
+---------+----------------+---------------------+
| 10001 | Coyote Inc. | ylee@coyote.com |
| 10003 | Wascals | rabbit@wascally.com |
| 10004 | Yosemite Place | sam@yosemite.com |
+---------+----------------+---------------------+

使用视图4-简化计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;
mysql> SELECT * FROM orderitemsexpanded
-> WHERE order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
| 20005 | ANV01 | 10 | 5.99 | 59.90 |
| 20005 | ANV02 | 3 | 9.99 | 29.97 |
| 20005 | TNT2 | 5 | 10.00 | 50.00 |
| 20005 | FB | 1 | 10.00 | 10.00 |
+-----------+---------+----------+------------+----------------+

关于更新视图

更新(insert、update、delete)一个视图将更新其基表(因为视图本身不存储数据)。如果MySQL不能正确的确定被更新的基数据,则不允许更新。

一般的,应将视图用于检索(select),而不是更新(insert,update,delete)