MySQL学习笔记7（创建和操纵表，使用视图，使用存储过程）

21创建表和操纵表

1创建表

示例（创建之前用到的表customers过程）：

CREATE TABLE customers

(

cust_id              int              NOT NULL AUTO_INCREAMENT,

 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;

表名紧跟在CREATE TABLE关键字后面。实际的表定义括在圆括号中。每列的定义以列名开始，后跟列的数据类型。主键用 PRIMARY KEY 指定。

创建新表时，指定的表名不能已经存在。可以在建表语句的表名之前加关键词 IF NOT EXISTS，当不存在时才创建该表。

主键列不允许NULL值出现。

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

AUTO_INCREATEMENT 是MySQL自动增量列。使用 SELECT last_insert_id() 来返回最后一个AUTO_INCREATMENT值。

对列指定默认值，如列定义时：‘quantity int NOT NULL DEFAULT 1，’。

上例指定引擎类型为InnoDB，关于常见引擎：

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

2更新表

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

ALTER TABLE vendors

ADD vend_phone char(20);

删除刚刚添加的列：

ALTER TABLE vendors

DROP COLUMN vend_phone;

ALTER TABLE 常见用途是定义外键，例如：

ALTER TABLE  表名 ADD CONSTRAINT  约束名称 FOREIGH KEY (列名)   REFERENCE 被引用的表名称（列名）；

小心使用ALTER TABLE ，数据库的更改不能撤销。

3删除&重命名

DROP TABLE 表名；

RENAME TABLE 原始表名 TO 新表名；

22使用视图

1视图

先对视图有一个简单认识：

SELECT cust_name,cust_contact

From customers,orders,orderitems

WHERE customers.cust_id=orders.cust_id

AND orderitems.order_num = orders.order_num

AND prod_id = 'TNT2';

上述语句返回的是购买了TNT2的客户的信息，那下次如果查询购买其他产品的客户的信息有需要输入这堆查询语句，为了简化操作，可以使用视图：

SELECT cust_name,cust_contact

From productcustomers

WHERE prod_id = 'TNT2';

现在只需要知道，productcustomers凝练了之前的部分语句，两次查询返回相同结果。

视图是一张虚拟表，本身不包含数据，它返回的数据是从其他表中检索出来的。嵌套视图会使性能下降。

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 customers .cust_id = orders.cust_id

AND orderitems.order_num = orders.order_num;

上述语句创建一个productcustomers视图，联结三个表，返回已订购任意产品的客户信息。如果检索其中订购TNT2的客户，方式如下：

2.2用视图重新格式化检索出的数据

第十小节里，SELECT语句组合计算列中返回供应商名和位置：

假如经常需要这个格式的结果，就创建一个视图：

2.3用视图过滤数据

2.4使用视图与计算字段

2.5视图更新

更新一个视图将更新其基表。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

一般视图用来检索(SELECT)而不用于更新（INSERT,UPDATE,DELETE）。

23存储过程

简单来说，存储过程就是为了以后的使用而保存的一条或多条MySQL语句集合。

1创建存储过程

使用命令行创建存储过程首先要临时更改命令行语句分隔符，如下所示：

原因是默认的MySQL语句分隔符为 ； ，mysql命令行分隔符也是 ； 。如果命令行解释存储过程自身内的 ； 字符，则使建立存储过程时出现错误。

然后，创建存储过程：

调用存储过程：

2删除存储过程

DROP PROCEDURE productpricing//

仅当存在时删除，DROP PROCEDURE IF EXISTS 。

3使用参数

变量：内存中一个特定的位置，用来临时存储数据。

可以指定变量的类型，MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入传出）类型的参数。

3.1OUT

创建存储过程：

调用存储过程，含有IN型参数：

返回检索数据：

3.2IN

创建存储过程：

调用函数，这里含有两个参数一个IN型，一个OUT型：

返回检索数据：

SHOW PROCEDURE STATUS 用来返回存储过程。