MySQL必知必会3
创建和操纵表
创建表
输入
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不会提交
上一篇: js的try catch使用心得
下一篇: JQuery学习笔记(4)——ajax