bitscn.com
21．创建和操作表
21.1.创建表
create table创建表，必须给出下列信息：
1） 新表的名字，在关键字createtable之后
2） 表列的名字和定义，用逗号分隔
create table customers
(
cust_id int not null auto_increment,
cust_name char(50) notnull,
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,
primarykey (cust_id)
) engine = innodb;
有以下几点需要注意：
1）其中主键也可以用多个列组成，如orderitems表中在主键表示如下：
primary key(order_num,order_item)
主键只能使用不允许null值的列。
2）每个表只允许一个auto_increment列。可用selectlast_insert_id()获取最后一个auto_increment值。
3）用default指定默认值
4)引擎类型
innodb是一个可靠的事物处理引擎，它不支持全文本搜索
memory在功能上等同于myisam,但由于数据存储在内存中，速度很快（特适合于临时表）
myisam是一个性能极高的引擎，它支持全文本搜索，但不支持事物处理
引擎可以混用，但外键不能夸引擎。
21.2更新表
必须提供以下信息：
1）在alter table之后给出要更改的表名
2）所做更改的列表
alter table vendors add vend_phonechar(20);//增加一个新列
alter table vendors drop vend_phone;//删除一个列
定义外键：
alter table orderitems add constraintfk_orderitems_orders foreign key (order_num) reference orders(order_num);
21.3删除表
drop table customers;
21.4重命名表
rename table backup_customers to customers;
22.使用视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图不包含表中应该有的任何列或数据，它包含的是一个sql查询。视图仅仅用来查看存储在别处数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些彪中国的数据时，视图将返回改变过的数据。每次使用视图时，都必须处理查询执行时所需要的任一检索。
视图不能索引，也不能有关联的触发器或默认值。
使用视图：
1）视图用create view来创建
2）使用show create viewviewname;来查看创建视图的语句
3）用drop删除视图，其语法为dropview viewname;
4) 更新视图时，可以先用drop再用create,也可以直接用createor replace view。
22.1利用视图简化复杂的联结
mysql> create view productcustomers asselect cust_name,cust_contact,prod_id fro
m customers,orders,orderitems wherecustomers.cust_id = orders.cust_id and order
items.order_num = orders.order_num;
query ok, 0 rows affected (0.13 sec)
为检索订购产品tnt2的客户，可如下执行：
mysql> select cust_name,cust_contactfrom productcustomers where prod_id = 'tnt2
';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| coyote inc. | ylee |
| yosemite place | y sam |
+----------------+--------------+
2 rows in set (0.00 sec)
22.2用视图重新格式化检索出的数据
select * from productcustomers;
22.2视图的更新
有时，视图是可更新的（即可以对它们使用insert、update和delete），对视图增加或删除行，实际上是对其基表增加或删除行。
但是，如果视图定义中有以下操作，则不能进行更新：
1） 分组（使用group by和having）
2） 联结
3） 子查询
4） 并
5） 聚集函数
6） distinct
7） 导出（计算）列
23.使用存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条mysql语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
存储过程有3个主要的好处：简单、安全、高性能。
23.1执行存储过程
mysql称存储过程的执行为调用，因此mysql执行存储过程的语句为call。call接受存储过程的名字以及需要传递给它的任意参数。
call producpricing(@pricelow,@pricehigh,priceaverage);
其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。存储过程可以显示结果，也可以不显示结果。
23.2创建过程
create procedure productpricing()
begin
selectave(prod_price) as priceaverage
fromproducts;
end;
此存储过程名为productricing,用create procedure productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。begin和end语句用来限定存储过程体，过程体本事仅是一个简单的select语句。
在mysql处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
如果使用mysql命令行使用程序，由于默认的mysql语句结束符为；，则存储体内的；字符将不会最终成为储存过程的成分，这会导致语法错误。解决办法是使用新语句分隔符。delimiter //
create procedure productpricing()
begin
selectavg(prod_price) as priceaverage
fromproducts;
end//
delimiter;
其中delimiter//告诉命令行实用程序使用//作为新的语句结束符，可以看到表示存储过程结束的end定义end//而不是end;作为语句结束。最后使用delimiter ；恢复原来的语句结束符。然后调用结果如下：
mysql> call productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.30 sec)
query ok, 0 rows affected (0.30 sec)
23.2删除存储过程
drop procedure productpricing;//当不存在时报错
drop procedure productpricing if exists;//仅当存在时删除，不存在时不报错
23.3使用参数
一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
变量：内存中一个特定的位置，用来临时存储数据。所有mysql变量必须以@开始。
create procedure order（in onumberint, out ototal decimal(8,2)）
begin
selectsum(item_price*quantity)
fromorderitems
whereorder_num = onumber
intoototal;
end;
此存储过程接受两个参数，每个参数必须指定参数类型。关键字in指出相应参数用来传递给存储过程，out（从存储过程传出），inout（对存储过程传入和传出）。存储过程的代码位于begin和end语句内。
为调用这个存储过程，可使用如下语句：
call ordertotal(2005,@total);
为显示此合集，如下
select @total;
为得到另一个订单的合计信息，需要再次调用存储过程
call ordertotal(2009,@total);
select @total;
23.4 建立智能存储过程
只有在存储过程内包含业务规则和智能处理时，他们的威力彩真正显现出来。
create procedure ordertotal(
inonumber int,
intaxable boolean,
outototal decimal(8,2)
) comment 'obtain order total, optionallyadding tax'
begin
--declare variable for total
declare total decimal(8,2);
--declare tax percentage
declare taxrate int default 6;
--getthe order total
select sum(item_price*quantity)
from orderitems
where order_num = onumber
into total;
--is this taxable?
iftaxable then
--yes, so add taxrate to total
select total+(total/100*taxrate) into total;
endif;
--and finally,save to out variable
select total into ototal;
end；
这里commernt关键字不是必须的，但如果给出，将在show procedure statu的结果中显示。
mysql除了if语句,还指出elseif和else子句（elseif还须用then, else不使用）
调用并显示如下结果1:如下
mysql> call ordertotal(20005,0,@total);
query ok, 0 rows affected (0.05 sec)
mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
调用并显示如下结果2如下
mysql> call ordertotal(20005,1,@total);
query ok, 0 rows affected, 1 warning (0.00sec)
mysql> select @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)
23.5检查存储过程
为了显示用来创建一个存储过程的create语句，使用show create procedure语句：
show create procedure ordertotal;
为了获得包括何时，由谁创建等详细信息的存储过程列表，使用show procedure status,并且可用like指定一个过滤模式，例如
show procedure status like ‘ordertotal’;
24使用游标
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标是一个存储在mysql服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结构集。在存储了游标之后，应用程序可以根据需要滚动或浏览或更改其中的数据。
24.1使用游标
步骤：
1) 在使用有游标前，必须声明它。这个过程实际上没有检索数据，它只是ing医药使用的select语句
2) 一旦声明后，必须打开游标供使用。这个过程用前面定义的select语句把数据实际检索出来。
3) 对于填有数据的游标，根据需要取出各行
4) 在结束游标使用时，必须关闭游标
24.2创建游标和使用游标数据
在一个游标被打开后，可以使用fetch语句分别访问它的每一行，fetch指定检索什么数据，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条fetch语句检索下一行。
举例如下：
create procedure processorders()
begin
--declarelocal variables
declaredone boolean default 0;
declareo int;
declaret decimal(8,2);
--declarethe cursor
declareordernumbers cursor
for
selectorder_num from orders;
--declarecontinue handler
declarecontinue handler for sqlstate ‘02000’ set done = 1;
--createa table to store the results
createtable if not exists ordertotals
(order_numint, total decimal(8,2));
--openthe cursor
openordernumbers;
--loopthrough all rows
repeat
--getorder number
fetch ordernumbers into o;
--getthe total for this order
call ordertotal(o,1,t);
--insertorder and total into ordertotals
insertinto ordertotals(order_num,total)
values(o,t);
--endof loop
unitldone end repeat;
--closethe cursor;
closeordernumbers;
end;
在这个例子中，fetch在repeat内，因此它反复执行直到done为真（由until done end repeat;规定）。为使它起作用，用一个default 0定义变量done。然后用declare continue handler for sqlstate ‘02000’ set done =1;这条语句定义一个continuehandler,它是在条件出现时被执行的代码。这里，它指出当sqlstate ‘02000’出现时，set done=1。sqlstate ‘02000’是一个未找到条件，当repeat由于没有更多的行工循环而不能继续时，出现这个条件。这里还调用了另外一个存储过程call ordertotal(o,1,t);这是在前一章创建的存储过程，实现计算每个订单带税额合计。此存储过程不返回数据，但它能够创建和填充另一个表。
可以用以下语句执行存储过程和查看存储结果：
mysql> call processorders();
mysql> select * from ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20009 | 40.78 |
+-----------+---------+
6 rows in set (0.00 sec)
这样我们就得到了存储过程，游标，逐行处理以及存储过程调用其他存储工程的一个完整的工作样例。
25.使用触发器
25.1触发器
触发器是mysql响应一下任意语句而自动执行的一条mysql语句（或位于begin和end语句之间的一组语句）：
delete
insert
update
25.2创建触发器
创建触发器时，需要给出4条信息：
1) 唯一的触发器名；
2) 触发器关联的表
3) 触发器应该响应的活动（delete、insert或update）
4) 触发器何时执行(处理之前或之后)
只有表可以支持触发器，视图不支持（临时表也不支持）。
create trigger newproduct after insert onproducts for each row select 'd';
这里用create trigger创建一个名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了after insert，所以触发器将在insert语句成功后执行。这个触发器还制定了for each row，因此代码对每个插入行执行。在这个例子中，文本product added将对每个插入的行显示一次。ps.在我的mysql版本中，不能成功实现，会提示不能返回结果集not allowed to return a result set from a trigger错误。不知道是mysql版本问题还是mysql必知必会中的错误。
触发器按每个表每个时间每次的定义，每个表每个时间每次只能定义一个触发器，因此一个表最多支持6个触发器（insert,update和delete之前或之后）。
25.3删除触发器：
drop trigger newproduct;
25.4使用触发器
25.4.1 insert触发器
1)在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行；
2)在before insert触发器中，new中的值可以被更新（允许更改被插入的值）
3）对于auto_increment列，new在inserrt执行之前包含0，在执行之后包含新的自动生成的值。
create trigger neworder after insert onorders for each row select new.order_num;
25.4.2delete触发器
1）在deleye触发器代码内，你可以引用一个名为old的虚拟表，访问被删除的行
2）old中的值全部都是只读的，不能更新。
create trigger deleteorder before delete onorders
for each row
begin
insert into
archive_orderss(order_num,order_date,cust_id)
values(old.order_num,old.order_date,old.cust_id);
end;
25.5 update触发器
1)可以用old的虚拟表访问以前的值，也可以用名为new的虚拟表访问新更新的值
2）在beffore update触发器中，new中的值可能也被更新
3）old中的值全部都是只读的，不能更新
26.管理事务处理
事务处理可以用来维护数据库的完整性，它保证成批的mysql操作要么完全执行，要么完全不执行。
术语：
事务：指一组sql语句
回退：指撤销指定sql语句的过程
提交：指将未存储的sql语句结果写入数据库表
保留点：值事务处理中设置的临时占位符，你可以对它发布回退。
26.1控制事务处理
管理事务处理的关键在于将sql语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
start transaction//标记是事务开始
26.2使用rollback
rollback用来回退mysql语句
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
依次执行上面语句，可以看到删除的表格内容又被回退了。
rollback只能在一个事务处理内使用（在执行一条starttransaction命令之后）。
26.3使用commit
一般的mysql都是直接对数据库表执行和编写的，这是隐含提交。但在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用commit语句，如下所示：
start tranaction;
delete from orderitems where order_num =20010;
delete from orders where order_num = 20010;
commit;
如果第一条起作用，第二条失败，则事务不处理，也就是两条delete不会被提交。
26.4使用保留点
简单的rollback和commit语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才嫩这样做，更复杂的事务处理可能需要部分提交或回退。
为了支持回退部分事务处理，必须使用保留点，可如下使用savepoinr语句：
savepoint delete1;
每个保留点都表示它的唯一名字，以便回退时，mysql知道要回退到何处。
rollback to delete1;
26.5 更改默认的提交行为
为指示mysql不自动提交更改，需要使用以下语句:
set autocommit = 0;
27.全球化和本地化
createtable mytable
(
column1 int,
column2 varchar(0)
)deault character set hebrew cllatehebrew_general_ci;
这条语句创建一个表，并且制定一个字符集和一个校对顺序
show character set;//可以查看所支持的字符集完整列表
show collation; //查看所支持的校对完整列表
28．安全管理
管理用户
use mysql;
select user from user;
mysql数据库有一个名为user的表，它包含所有用户账号。user表有一个user列。
28.1创建用户账户
为创建新用户账户，使用create user语句
create user ben identified by ‘p@$$w0rd’;
创建一个用户名为ben的用户，口令为p@$$w0rd。
rename user ben to bforta;
28.2删除用户账号
drop user bforta;
28.3设置访问权限
mysql> show grants for bforta;
+-------------------------------------------------------------------------------
------------------------+
| grants for bforta@%
|
+-------------------------------------------------------------------------------
------------------------+
| grant usage on *.* to 'bforta'@'%'identified by password '*a6210e6c376aadb5a6
9274f8c3d15b788433a9eb' |
+-------------------------------------------------------------------------------
------------------------+
1 row in set (0.00 sec)
输出结果显示用户bforta有一个权限usage on *.*。usage表示没有权限，所以usage on *.*表示在任意数据库和人意表上对任何东西都没有权限。
使用grant设置权限，需要给出以下信息：
1）要授予的权限
2）被授予访问权限的数据库或表
3） 用户名
grant select on crashcourse.* to bforta;
赋予bforta在crashcourse.*拥有select权限。
在此显示权限如下：
show grants for bforta;
+-------------------------------------------------------------------------------
------------------------+
| grants for bforta@%
|
+-------------------------------------------------------------------------------
------------------------+
| grant usage on *.* to 'bforta'@'%'identified by password '*a6210e6c376aadb5a6
9274f8c3d15b788433a9eb' |
| grant select on `crashcourse`.* to'bforta'@'%'
|
+-------------------------------------------------------------------------------
------------------------+
2 rows in set (0.00 sec)
可以用revoke取消权限。
revoke select on crashcourse.* from bforta;
28.4更改口令
更新用户口令
set password for bforta = password(‘n3wp@$$w0rd’);
设置自己的口令
set passwor = password(‘n3w p@$$w0rd’);
第29章数据库维护
analyze table：用来检查表键是否正确
check table:用来针对许多问题对表进行检查
bitscn.com