《MySQL必知必会》学习笔记——创建和操纵表、使用视图

第21章 创建和操纵表

1. 创建表

可以使用SQL的CREATE TABLE语句创建新的表。

1.1 表创建基础

使用CREATE TABLE语句创建新的表，必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出；
• 表列的名字和定义，用逗号分隔。

下面是一个例子：

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

每列的定义以列名开始，然后跟列的数据类型，最后是该列的值是否允许为NULL（每个列或者是NULL列，或者是NOT NULL列）。表的主键可以在创建表时用PRIMARY KEY关键字指定。

创建新表时，指定的表名必须不存在，否则将出错。如果仅想在一个表不存在时创建它，可以在表名后给出IF NOT EXISTS。

1.2 主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键值使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

之前的例子主键值使用的是单个列，现在给出一个多个列的例子：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

主键中只能使用不允许NULL值的列。

1.3 使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

一个列被指定为AUTO_INCREMENT，我们仍然可以在INSERT语句中为该列指定一个值，只要它是唯一的（至今尚未使用过）。该值将被用来替代自动生成的值。

使用AUTO_INCREMENT有一个缺点是，我们不知道这些自动生成的值是多少。但是有时我们需要使用这个值，我们可以通过last_insert_id()这个函数来获得这个值。如下所示：

SELECT last_insert_id();

此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

1.4 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。例如：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL   DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

MySQL不允许使用函数作为默认值，它只支持常量。

1.5 引擎类型

为不同的任务选择正确的引擎能获得良好的功能和灵活性。以下是几个需要知道的引擎：

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
• MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）；
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

注意，外键不能跨引擎。即，使用一个引擎的表不能引用具有使用不同引擎的表的外键。

2. 更新表

为更新表定义，可使用ALTER TABLE语句。值得注意的是，理想状态下，当表中存储数据以后，该表就不应该再被更新。

下面的例子给表添加一个列：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除刚刚添加的列，可以这样做：

ALTER TABLE vendors
DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是定义外键。下面是将orders表中的order_num列定义为orderitems表的order_num的外键。

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可以使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

3. 删除表

删除表（删除整个表而不是其内容）可以使用DROP TABLE语句：

DROP TABLE customers2;

4. 重命名表

使用RENAME TABLE可以重命名一个表：

RENAME TABLE customers2 TO customers,
             backup_vendors TO vendors;

第22章 使用视图

1. 视图

首先要明白，视图是虚拟的表，它包含的不是数据，而是根据需要检索数据的查询。可以理解为，视图是对一组SQL查询语句的封装。

1.1 为什么使用视图

下面是视图的一些常用应用：

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
• 使用表的组成部分而不是整个表。
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建后，可以用与表基本相同的方式利用它们。

使用视图时要注意性能问题。因为每次使用视图，都会处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

1.2 视图的规则和限制

下面是视图创建和使用的一些最常见的规则和限制：

• 视图必须唯一命名；
• 创建的视图数目没有限制；
• 创建视图需要足够的访问权限；
• 视图可以嵌套；
• ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句也含有ORDER BY语句，则视图中的ORDER BY将被覆盖；
• 视图不能索引，也不能有关联的触发器或默认值；
• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

2. 使用视图

下面是视图的一些基本使用：

• 视图用CREATE VIEW语句来创建。
• 使用SHOW CREATE VIEW viewname可以查看创建视图的语句。
• 用DROP删除视图，其语法为DROP VIEW viewname。
• 更新视图时，可以先用DROP再用CREATE，或者也可以直接用CREATE OR REPLACE VIEW。

下面给出一个视图的应用，其他的应用是类似的。

2.1 利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的SQL，比如简化复杂的联结。

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE cutomers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num;

这条语句创建了名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。

为检索订购了产品TNT2的客户，可如下进行：

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

通过创建了productcustomers视图，如果之后我们想检索其他客户订购的产品，则不再需要输出复杂的联结表的SQL语句，只需要一个SELECT和WHERE语句即可，非常方便。

2.2 更新视图

通常，视图是可更新的，但是限制非常大。而且，对视图的更新其实是对基表的更新，所以我们没必要去对视图进行更新，如果真的需要更新，直接对基表进行更新即可。

视图主要用于检索数据，而不用于更新。