MySQL必知必会3
创建和操纵表
创建表
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。输入
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;分析
表名紧跟在CREATE TABLE 关键字后面 ，实际的表定义括在圆括号中。各列之间用逗号分隔。这个表由9列组成。每列的定义以列名开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定，这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束
NULL值
NULL值就是没有值或缺值，允许值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行
更新表
ALTER TABLE vendors
ADD vend_phone char(20);分析
这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型
删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;定义外键
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);删除表
DROP TABLE customers1;重命名表
RENAME TABLE customers2 TO customers;使用视图
视图
需要MySQL5 MySQL5添加了对视图的支持，本章的内容适用于MySQL5及以后的版本
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
理解视图
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'; 此查询用来检索订购了某个特定产品的客户，任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品的相同数据，必须修改最后的WHERE子句
现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据
输入
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';这就是视图的作用。productcustomers是一个视图，作为视图，它不包含表中应该有的任何或数据，它包含的是一个SQL查询
视图的规则和限制
与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名字)
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限，这些限制通常由数据库管理人员授予
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句也含有ORDER BY,那么该视图中的ORDER BY将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句
使用视图
视图用CREATE VIEW 语句创建
使用SHOW CREATE VIEW viewname; 来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname;
更新视图时，可以先用DROP再用CREATE ，也可以直接用CREATE OR REPLACE VIEW ，如果想要更新的视图不存在，则第二条语句会创建一个视图，如果要更新的视图存在，则第二条语句会替换原有视图
利用视图简化复杂的联结
输入
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 = order.order_num;分析
这条语句创建了一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户列表，如果执行
SELECT * FROM productcustomers，将列出订购了任意产品的客户
用视图重新格式化检索出的数据
视图的另一种常见用途是重新格式化检索出的数据。下面的SELECT语句在单个组合计算列中返回供应商名和位置
输入
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;+------------------------+
| vend_title |
+------------------------+
| ACME(USA) |
| Anvils R Us(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
| LT Supplies(USA) |
+------------------------+现在加入经常需要这个格式的结果，不必再每次需要时执行联结，创建一个视图，每次需要时使用它即可，
输入
CREATE VIEW vendorlocations AS
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;分析
这条语句使用与以前的SELECT语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据，可以如下进行
SELECT * FROM vendorlocations;使用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;分析
显然，在发送电子邮件到邮件列表时，需要排除没有电子邮件地址的用户。这里的WHERE子句过滤了cust_email列中具有NULL值的行，
现在，可以像使用其他表一样使用视图customeremaillist
SELECT * FROM customeremaillist;使用视图与计算字段
输入
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;为检索订单2005的详细内容
输入
SELECT * FROM orderitemsexpanded
WHERE order_num=20005;更新视图
通常，视图是可更新的(INSERT、UPDATE和DELETE),更新一个视图将更新基表
如果视图定义了以下操作，则不能进行视图更新
分组(使用GROUP BY和HAVING);
联结；
子查询；
并；
聚集函数(Min()、Count()、Sum()等)；
DISTINCT;
导出(计算)列
换句话说，本章许多例子中的视图都是不可以更新的，这听上去好像是一个严重的限制，但实际上不是，因为视图主要用于数据检索
使用存储过程
需要MySQL5 MySQL5添加了对存储过程的支持，因此，本章内容适用于MySQL5及以后的版本
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可以将其视为批文件，虽然他们的作用不限于批处理
使用存储过程
使用存储过程需要指定如何执行他们，存储过程的执行远比其定义更经常遇到，因此，我们将从执行存储过程开始介绍，然后在介绍创建和使用存储过程
执行存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给他的任意参数
CALL productpricing(
@pricelow,
@pricehigh,
@priceaverage;
)分析
其中，执行名为productpricing的存储过程，他计算并返回产品的最低、最高和平均价格
创建存储过程
输入
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;分析
此储存过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，他们将在()中列举出来。此存储过程没有参数但后跟的括号仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句
那么，如何使用这个存储过程
输入
CALL productpricing();+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+分析
CALL productpricing();执行刚创建的存储国产过程并显示返回的结果，因为存储过程实际上是一种函数，所以存储过程名后需要有()符号
删除存储过程
DROP PROCEDURE productprincing；使用参数
输入
DELIMITER //
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price)
INTO pl
FROM products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END //
DELIMITER ;
分析
此存储过程接受3个参数：pl存储产品最低价格.....
每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)，MySQL支持IN(传递给存储过程),OUT(从存储过程传出),INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内
调用存储过程
CALL productpricing(
@pricelow,
@pricehigh,
@priceaverage
);分析
由于存储过程要求3个参数，因此必须正好传递三个参数，在调用时，这条语句并不显示任何数据
为了显示检索的产品平均价格，如下
SELECT @priceaverage；使用游标
MySQL检索操作返回一组称为结果集的行，这组返回行都是与SQL语句相匹配的行，使用简单的SELECT语句，例如，没有办法得到第一行，下一行或前十行，也不存在每次一行地处理所有行的简单方法
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因
游标是一个存储在MySQL服务器上的数据库查询，他不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据
使用游标
在能够使用游标前，必须声明它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来
对于填有数据的游标，根据需要去除各行
在结束游标使用时，必须关闭游标
在声明游标后，可根据需要频繁地打开和关闭游标，在游标打开后，可根据需要频繁地执行取操作
创建游标
游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。例如，下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
DELIMITER //
CREATE PROCEDURE processorders3()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END //
DELIMITER ;
分析
这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers.存储过程处理完成后，游标就消失。
打开和关闭游标
OPEN ordernumbers;在处理OPEN语句时执行查询，存储检索出来的数据以供浏览和滚动
游标处理完后，应当使用如下语句关闭游标
CLOSE ordernumbers;输入
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
CLOSE ordernumbers;
END //
DELIMITER ;
分析
这个存储过程声明、打开、和关闭一个游标。但对检索出的数据声明也没有做
使用游标数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索声明数据，检索出来的数据存储在什么地方。他还向前移动游标中的内部指针，使下一条FETCH语句检索下一行
输入
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;
END //
DELIMITER ;分析
其中FETCH用来检索当前行的order_num列从第一行开始到一个名为o的局部声明变量中，对检索出的数据不做任何处理
输入
DELIMITER //
CREATE PROCEDURE processorders7()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
UNTIL done END REPEAT;
CLOSE ordernumbers;
END //
DELIMITER ;与前一个列子一样，这个列子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个列子不一样的是，这个列子中的FETCH是在REPEAT内，因此他反复执行直到done为真(有UNTIL done END REPEAT;规定) 。为使他起作用，用一个DEFAULT 0(假，不结束)定义变量done。那么,done这样才能在结束时被设置为真呢?答案是用以下语句
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
这条语句定义了一个CONTINUE HANDLER,她是在条件出现时被执行的代码。这里，他指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到的条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件
如果调用这个存储过程，他将定义几个变量和一个CONTINUE HANDLER ，定义并打开一个游标，重复读取所有行，然后关闭游标
输入
DELIMITER //
CREATE PROCEDURE processorders9()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8,2));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o,1,t);
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END //
DELIMITER ;管理事务处理
事务处理(transaction processiong)可以用来维护数据库的完整性，他保证成批的MySQL操作要么完全执行，要么完全不执行
控制事务处理
使用ROLLBACK
MySQL的ROLLBACK命令用来回退MySQL语句，
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertorals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;分析
这个列子从显示ordertotals表的内容开始，首先执行一条SELECT以显示该表不为空。然后开始一个事务处理，用一条DELETE语句删除ordertotals中所有的表。另一条SELECT语句验证ordertotals确实为null，这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为null
使用COMMIT
一般的MySQL语句都是直接针对数据库表执行和编写的，这就是所谓的隐含提交(implicit commit)，即提交操作是自动进行的
但是，在事务处理块中，提交不会隐含的进行。为进行明确的提交，使用COMMIT语句
输入
START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;分析
在这个例子中，从系统中完全删除订单20010.因为涉及更新两个数据库表orders和orderitems,所以使用事务处理块来保证订单不被部分删除，最后的COMMIT语句仅在不出错是写出更改，如果第一条DELETE起作用，但第二条失败，则DELETE不会提交
