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 用来返回存储过程。