《MySQL 必知必会》C21-C24

第二十一章 创建和操纵表

1 创建表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理，表的两种创建方式：
1，使用具有交互式创建和管理表的工具
2，表可以直接用MySQL语句操纵

为了用程序创建表，可使使用SQL的creat table语句，在使用交互式工具时，实际上使用的是MySQL语句，但是这些语句不是由用户编写的，界面工具会自动执行相应的MySQL语句

(1) 表创建基础

使用creat table语句时，必须给出下列信息：
1，新表的名字，在creat table后给出
2，表列的名字和定义，用逗号分隔

示例：

create table customers
(
	cust_id		int			not null	auto_increment,
	cust_name	char(50)	not null,
	cust_add	char(50)	null,
	cust_city	char(50)	null,
	cust_state	char(5)		null,
	cust_zip	char(10)	null,
	cust_coun	char(50)	null,
	cust_cont	char(50)	null,
	cust_email	char(255)	null,
	primary key (cust_id)
) engine = innodb;

注：
1，在创建表时，指定的表名必须不存在否则将出错，如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它
2，如果想在一个表不存在时创建它，应该在表名后给出 if not exists

(2) 使用null值

null值就是没有值或缺值，允许null值的列也允许在插入时不给出该列的值，不允许null值的列不接受该列没有值的行，即在插入或更新时该列必须有值

每个表列，要么是null列要么是not null列，这种状态在创建表时由表的定义规范

create table orders
(
	order_num	int			not null	auto_increment,
	order_date	datetime	not null,
	cust_id		int			not null,
	primary key (order_num)
) engine = innodb;

上述的示例如果试图插入没有列的值，将返回错误且插入失败

null为默认设定，如果不指定not null，则认为指定的是null

理解null：不要将null值和空串混淆，null值是没有值，它不是空串，空串是一个有效的值，它不是无值，null值用关键字null而不是空串指定

(3) 主键

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

单列作为主键：

primary key (cust_id)

多列作为主键：

primary key (order_num, order_item)

主键中只能使用不允许null值的列，允许null值的列不能作为唯一标识

(4) 使用auto_increment

auto_increment用于确定下一个要使用的值

cust_id		int		not null	auto_increment,

auto_increment告诉MySQL，本列每当增加一行时自动增量，每次执行一个insert操作时，MySQL自动对该列增量，给该列赋予下一个可用的值，这样每个行分配一个唯一的cust_id，从而作为主键值

每个表只允许一个auto_increment列，且它必须被索引(如通过使它成为主键)

(5) 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的是默认值，默认值用creat 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;

quantity列包含订单中没想物品的个数，给该列的描述添加文本default 1，指示MySQL，在未给出数量的情况下使用数量1

注：
1，与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量
2，许多数据库开发人员使用默认值而不是null列，特别是对用于计算或数据分组的列更是如此

(6) 引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎，在使用create table语句时，该引擎具体创建表，而在使用select语句或进行其它数据处理时，该引擎在内部处理你的请求，多数时此引擎都隐藏在DBMS内

MySQL与其它DBMS不一样，它具有多种引擎，它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行create table 和select等命令

发行多种引擎，是因为它们各自具有不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性

如果省略engine = 语句，则会使用默认引擎(很可能是MyISAM)，但并不是所有语句都默认使用它

需要了解的几个引擎:
InnoDB：是一个可靠的事物处理引擎，它不支持全文本搜索

MyISAM：一个性能极高的引擎，它支持全文本搜索，但不支持事物处理

MEMORY：在功能上等同于MyISAM，但由于数据存储在内存，而不是磁盘中，所以速度很快(特别适用于临时表)

MyISAM由于其性能和特性可能是最受欢迎的引擎，但如果需要可靠的事物处理，可以使用InnoDB

数据库中的各个表可以使用不同的引擎，但混用引擎有一个大问题，外键不能跨引擎，即使用一个引擎的表不能具有使用不同引擎的表的外键

2 更新表

为了更新表定义，可以使用alter table语句，但是理想状态下，当表中存储了大量数据后，此表就不应该再被更新，在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动

使用alter table更改表的结构，，必须给出如下定义：
1，在alter table之后给出要更改的表名
2，所做更改的列表

示例，给表添加一个列：

alter table vendors
add vend_phone char(20);

这条语句为vendors语句增加了一个名为vend_phone的列，必须确定其数据类型

删除表中的一个列：

alter table vendors
drop column vend_phone;

alter table的一种常见用途是定义外键

复杂的表结构更改一般需要手动过程删除，涉及以下步骤：
1，用新的列布局创建一个表
2，使用insert select语句从旧表中复制数据到新表
3，校验包含所需数据的新表
4，重命名旧表
5，用旧表原来的名字重新命名表
6，根据需要，重新创建触发器，存储过程，索引和外键

小心使用 alter table：在使用alter table前最好创建一完整的备份，数据库表的更改不能撤销

3 删除表

使用drop table可以删除一个表：

drop table customers;

这条语句删除customers表，删除表没有确定，也不能撤销，执行这条语句将永远的删除表

4 重命名表

使用rename table语句可以重命名一个表：

rename table customers to cust1;

也可以对多个表重命名

rename table cust1 to cust,
			 stu to studet,
			 orders to ord;

第二十二章 使用视图

1 视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询

从3个表中使用select语句检索数据：

select cust_name, cust_contact
from customers, orders, orderitems
where customers.cust_id = order,cust_id,
  and orderitems.order_num = orders.order_num,
  and prod_id = 'tnt2';

现在假如可以把整个查询包装成一个名为productcustomers的虚拟表，可以轻松的检索出所需要的数据

select cust_name, cust_contact
from productcustomers
where prod_id = 'tnt2';

这就是视图的作用，productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或者数据，它包含的是一个SQL查询

(1) 使用视图的原因

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

在视图创建之后，可以用与表基本相同的方式利用它们，可以对视图执行select操作，过滤和排序数据，将视图联结到其它视图或表，甚至能添加和更新数据

重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，它们返回的数据是从其它表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据

性能问题：视图并不包含数据，每次使用试图时，都必须处理查询执行时所需的任一个检索，如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害

(2) 视图的规则和限制

创建和使用视图的规则和限制：
1，与表一样，视图必须唯一命名
2，对于对于可以创建的视图数目没有限制
3，为了管理视图，必须具有足够的访问权限，这些限制通常由数据库管理人员授予
4，视图可以嵌套，即可以用从它其视图中检索数据的查询来构造一个视图
5，order by可以用在视图中，但如果从该视图检索数据的select语句中也含有order by，那么视图中的order by会被覆盖
6，视图不能索引，也不能有关联的触发器和默认值
7，视图可以和表一起使用，如编写一条联结表和视图的select语句

2 使用视图

create view						创建视图
show create view viewname;		查看创建视图的语句
drop view viewname;				删除视图

更新视图时，可以先drop再create
也可以直接用create or replace view，如果要更新的视图不存在，第二条语句会创建一个新视图，如果存在要更新的视图，第二条语句会替换原有视图

(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的视图，它联结了3个表，以返回已订购了任意产品的所有客户的列表
如果使用select * from productcustomers，将列出订购了任意产品的客户

为检索订购了产品tnt2的客户，可以如下使用视图：

select cust_name, cust_contact
from productcustomers
where prod_id = 'tnt2';

这条语句通过where子句从视图中检索特定数据，在MySQL处理此查询时，它将指定的where子句添加到视图查询中的已有where子句中，以便正确地过滤数据

(2) 用视图重新格式化检索出的数据

视图的另一个常见用途是重新格式化检索出的数据

下面的select语句在单个组合计算列中返回供应商名和位置：

现在假如经常需要这个格式的结果，不必每次在需要时进行联结，创建一个视图，每次需要时使用它即可

(3) 用视图过滤不想要的数据

视图对于普通的where子句也很有用，如定义一个customeremaillist视图，它过滤没有电子邮件地址的客户：

(4) 使用视图与计算字段

视图对于简化计算字段的使用特别有用，如下面的select语句，它检索某个特定订单的中的物品，计算每种物品的中价值：

select prod_id, quantity, item_price
		quantity*item_price as expanded_price
from orderitems
where order_num = 20005;

将其转换成一个视图：

视图易于创建，而且很好使用，正确使用视图可以极大地简化复杂的数据处理，让后续的操作一劳永逸

(5) 更新视图

通常，视图是可更新的，即对它们使用insert，update和delete，更新一个视图将更新其基表，视图本身没有数据，如果你对视图增加或者删除行，实际上是对其基表增加或删除行

但并非所有的视图都是可更新的，视图中如果有以下的操作，则不能进行视图的更新：
1，分组
2，联结
3，子查询
4，并
5，聚集函数
6，distinct
7，导出或者计算列

一般应该将视图用于检索，而不用于更新，视图的主要作用就是用于对数据的检索

第二十三章 使用存储过程

1 存储过程

迄今为止，使用的大多数MySQL语句都是针对一个或多个表的单条语句，并非所有的操作都这么简单，经常会有一个完整的操作需要多条语句才能完成

如以下的情形：
1，为了处理订单，需要核对以保证库存中有相应的物品
2，库存中没有的物品需要订购，这需要与供应商进行某交互
3，关于哪些物品入库和哪些物品退订，需要通知相应的客户

执行上述复杂的问题需要针对多个表的多条MySQL语句，此外需要执行的具体语句及其次序也不是固定的，它们可能会根据哪些物品在库存中哪些不在而变化

为了处理这些问题，可以单独地编写每条语句，并根据结果有条件地执行另外的语句，每次需要这个处理时都必须做这些工作

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

2 使用存储过程的原因

使用存储过程的理由：
1，通过把处理封装在容易使用的单元中，简化复杂的操作
2，由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果所有的开发人员和应用程序使用同一存储过程，则所使用的代码都是相同的，这一点的延伸就是为了防止错误，需要执行的步骤越多，出现问题的可能性就越大，防止错误保证了数据的一致性
3，简化对变动的管理，如果表名，列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至都不知道这些变化

这一点的延伸就是安全性，通过存储过程限制对基础数据的访问减少了数据出错的机会

4，提高性能，因为使用存储过程比使用单独的SQL语句要快
5，存在一些只能在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

即存储过程有3个主要的好处，即简单，安全，高性能

MySQL将编写存储过程的安全和访问与执行存储过程的安全与访问区分开来，即使你不能编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程

3 使用存储过程

(1) 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为call，call接受存储过程的名字以及需要传递给它的任意参数

示例：

call productpricing(@pricelow,
					@pricehigh,
					@priceaverage);

其中执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格，存储过程可以显示结果，也可以不显示结果

(2) 创建存储过程

一个返回产品平均价格的存储过程：

create procedure productpicing()
begin
	select avg(prod_price) as priceaverage
	from products;
end;

此存储过程名为productpricing，
用create procedure productpricing()定义，如果存储过程接受参数，则需要将它们在 () 中列出来，没有参数()也不能少，begin和end用来限定存储过程体，存储过程本身只是一个简单的select语句

在MySQL处理这段代码时，它创建一个新的存储过程productpricing，没有返回数据，因为这段代码并未调用存储过程，这里只是为了以后使用而创建

存储过程中也存在 ; MySQL将 ;作为语句分隔符，如果命令实用程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误
解决办法是临时更改历命令行实用程序的语句分隔符：

delimiter //

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
	from products;
end //

其中delimiter(分隔符) // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的end定义为end // 而不是end; 这样存储过程内的 ; 仍然保持不动，并且正确地传递给数据库引擎，最后恢复为原来的语句分隔符

使用该存储过程：

存储过程实际上是一种函数，所以存储过程名后必须有 ()

注：使用delimiter // 后，所有的结束语句后除了加上 ; 外还需要加 // 标志着SQL语句的结束

(3) 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除

删除命令:

drop procedure productpricing;

这条语句删除刚创建的存储过程，只给出存储过程名，删除不需要()

如果指定的存储过程不存在，则drop procedure将产生一个错误，当该过程存在想删除它时，可使用drop prodecure if exists

(4) 使用参数

productpricing只是一个简单的存储过程，它简单地显示select语句的结果，一般，存储过程并不显示结果，而是把结果返回给你指定的变量

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

productpricing的带参存储过程：

此存储过程接受3个参数，每个参数必须具有指定的类型，关键字out指出相应的参数用来从存储过程传出一个值(返回给调用者)，MySQL支持in(传递给存储过程)，out(从存储过程中传出)，inout(对存储过程传入和传出)类型的参数，存储过程的代码位于begin和end语句中，它们是一系列select语句，用来检索值，然后保存到相应的变量(通过into关键字)

调用该存储过程：

使用in和out参数，ordertotal接受订单号并返回该订单：

onumber定义为in，因为订单号被传入存储过程，ototal定位为out，因为要从存储过程返回值，select语句使用这两个参数，where子句使用onumber选择正确的行，into使用ototal存储计算出来的合计

调用该存储过程：

(5) 建立智能存储过程

迄今为止所有的存储过程基本上都是封装MySQL简单的select语句，虽然它们都是有效的存储过程例子，但它们所能完成的工作直接用这些被封装的语句就能完成，只有在存储过程内包含业务规则和智能处理时，它们的威力才能真正显现出来

考虑这个例子，需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客，为此需要考虑：
1，获得合计结果
2，把营业税有条件地添加到合计值
3，返回合计

存储过程的完整工作如下：

--name:ordertotal
--parameters: onumber = order number
--				taxable = 0 if not taxable, 1 if taxable
--				ototal = order total variable

create procedure ordertotal(
	in onumber int,
	in taxable boolean,
	out ototal decimal(8,2);
) comment 'obtain order total, optionally adding tax'

begin
--declare variable for total
declare total decimal(8,2);
--declare tax percentage
declare taxrate int default 6;

--get the order total
select sun(item_price*quantity)
from orderitems
where order_num = onumber
into total;

--is this taxable?
if taxable then
--yes,so add taxrate to the total
select total + (total/100*taxrate) into total;
end if;
--and finally, save to out variable
select total into ototal;
end; 

(6) 检查存储过程

为了显示用来创建一个存储过程的create语句，
使用show create procedure语句

show create procedure ordertotal;

为了获得包括何时，由谁创建等详细信息的存储过程列表
使用show procedure status语句，
show procedure status会列出所有存储过程，为了限制其输出，可使用like指定一个过滤模式：

show procedure status like 'ordertotal';

第二十四章 使用游标

1 游标

MySQL检索操作返回的一组称为结果集的行，这组返回的行都是与SQL语句匹配的行。使用简单地select语句没法得到第一行，下一行或前十行，也不存在每次一行地处理所有行的简单方法

有时需要在检索出来的数据中前进或后退一行或多行，这就是使用游标的原因，游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被改语句检索出来的结果集，在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或者更改

MySQL的游标只能用于存储过程

2 使用游标

使用游标的几个明确步骤：
1，在能够使用游标前，必须定义它，这个过程实际上没有检索数据，它只是定义要使用的select语句
2，一旦定义后，必须打开游标以供使用，这个过程用前面定义的select语句把数据实际检索出来
3，对于填有数据的游标，根据需要取出(检索)各行
4，在结束游标使用时，必须关闭游标

在定义游标后，可根据需要频繁地打开和关闭游标，在游标打开后，根据需要频繁地执行取操作

(1) 创建游标

游标用declare语句创建，declare命名游标，并定义相应的select语句，根据需要带where和其它子句

定义名为ordernumbers的游标，使用了可检索所有订单的select语句：

create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders;
end;

这个存储过程没有实际做什么事，declare语句用来定义和命名游标，这里为ordernumbers，存储过程处理完成后，游标就消失(游标局限于存储过程)

(2) 打开和关闭游标

定义一个游标后，可以打开它，游标用open cursor语句打开

open ordernumbers;

在处理open语句时执行查询，存储检索出的数据以供浏览和滚动

游标处理完成后，使用如下语句关闭

close ordernumbers;

close释放游标所使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭

在一个游标关闭后，如果没有重新打开，则不能使用它，但是声明过的游标不需要再次声明，用open打开它就可以了

如果没有明确用close关闭游标，MySQL将会到达end语句时自动关闭它

create procedure processorders()
begin

	--declare the cursor
	declare ordernumbers cursor
	for
	select order_num from orders;
	
	--open the cursor
	open ordernumbers;
	
	--close the cursor
	close ordernumbers;

end;

上述的存储过程声明，打开，关闭一个游标

(3) 使用游标数据

在一个游标被打开后，可以使用fetch语句分别访问它的每一行，fetch指定检索什么数据(所需的列)，检索出来的数据存储在什么地方，它还向前移动游标中的内部行指针，使下一条fetch语句检索下一行

从游标中检索单个行(第一行)：

create procedure processorders()

begin
	
	--declare local variables
	declare o int;

	--declare the cursor
	declare ordernumbers cursor
	for
	select order_num from orders;

	--open the cursor
	open ordernumbers;

	--get order number
	fetch ordernumbers into o;

	--close the cursor
	close ordernumbers;

end;

其中fetch用来检索当前行的order_num列(将自动从第一行开始)到一个名为o的局部变量中，对检索出的数据不做处理

循环检索数据，从第一行到最后一行：

create procedure processorders()

begin
	
	--declare local variables
	declare done boolean default 0;
	declare o int;

	--declare the cursor
	declare ordernumbers cursor
	for
	select order_num from orders;

	--declare countinue handler
	declare continue handler for sqlstate '02000' set done = 1;

	--open the cursor
	open ordernumbers;

	--loop through all rows
	repeat

		--get order number
		fetch ordernumbers into o;
		
	--end of loop
	until done end repeat;

	--close the cursor
	close ordernumbers;

end;

调用上述的存储过程，将定义几个变量和一个continue handler，定义并打开一个游标，重复读取所有行，然后关闭游标

这个例子中的fetch在repeat中，因此它反复执行知道done为真(由until done end repeat 规定)，为了使它起作用，用一个default 0定义done

使用declare continue handler for sqlstate ‘02000’ set done = 1;这条语句来控制done的值，这条语句定义了一个continue handler，它是在条件出现时被执行的代码，这里，它指出sqlstate ’02000‘出现时，set done = 1，sqlstate ’02000‘是一个未找到条件，当repeat由于没有更多的行循环而不能继续时，这个条件被实现

declare语句次序：用declare语句定义的局部变量必须定义任意游标或句柄之前定义，而句柄必须在游标之后定义，不遵从此规定将产生错误

对取出的数据进行实际处理：

create procedure processorders()

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 exits ordertotals
		(order_num int,
		total decimal(8,2)
		);
	
	open ordernumbers;

	repeat
		
		fetch ordernumbers into o;
		
		call ordertotal(0,1,t);
		
		insert into ordertotals(order_num, total)
		values(o,t);
		
	until done end repeat;
		
	close ordernumbers;

end;

此存储过程在运行中创建了一个新表ordertotals，这个表将保存存储过程生成的结果，fetch取出每个order_num，然后call执行另一个存储过程来就按每个订单的带税的合计，最后用insert保存每个订单的单号和合计

此存储过程不返回数据，但它能够创建和填充另一个表，可以查看：

select *
from ordertotals;