MySQL必知必会阅读笔记(21-30章)
第21章 创建和操纵表
21.1 创建表
MySQL不仅用于表数据操纵,而且还可以用来执行数据库和表的所有操作,包括表本身的创建和处理.
一般有两种创建表的方法:
- 使用具有交互式创建和管理表的工具(如第2章讨论的工具);
- 表也可以直接用MySQL语句操纵.
为了用程序创建表,可使用SQL的CREATE TABLE语句.值得注意的是,在使用交互式工具时,实际上使用的是MySQL语句.但是,这些语句不是用户编写的,界面工具会自动生成并执行相应的MySQL语句
21.1.1 表创建基础
为利用CREATE TABLE创建表,必须给出下列信息:
- 新表的名字,在关键字CREATE TABLE之后给出;
- 表列的名字和定义,用逗号分隔.
CREATE TABLE语句也可能会包括其他关键字或选项,但至少要包括表的 名字和列的细节.
语句格式化
CREATE TABLE语句就是语句格式化的一个很好的例子,它被安排在多个行上,其中的列定义进行了恰当的缩进,以便阅读和编辑.以何种缩进格式安排SQL语句没有规定,但我强烈推荐采用某种缩进格式.
处理现有的表
在创建新表时,指定的表名必须不存在,否则将出错.如果要防止意外覆盖已有的表,SQL要求首先手工删除该表(请参阅后面的小节),然后再重建它,而不是简单地用创建表语句覆盖它.
如果你仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS.这样做不检查已有表的模式是否与你打算创建的表模式相匹配.它只是查看表名是否存在,并且仅在表名不存在时创建它.
NULL和空串
理解NULL
不要把NULL值与空串相混淆.NULL值是没有值,它不是空串.如果指定''(两个单引号,其间没有字符),这在NOT NULL列中是允许的.空串是一个有效的值,它不是无值.NULL值用关键字NULL而不是空串指定.
例子:
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;
21.1.2 使用NULL值
NULL值就是没有值或缺值.允许NULL值的列也允许在插入行时不给出该列的值.
不允许NULL值的列不接受该列没有值的行,换句话说,在插入或更新行时,该列必须有值.
每个表列或者是NULL列,或者是NOT NULL列,这种状态在创建时由表的定义规定.
例子:
CREATE TABLE order(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
cust_id int NOT NULL,
PRIMARY KEY(order_num)
)ENGINE=InnoDB;
CREATE TABLE vendors(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL,
vend_address char(50) NULL,
vend_city char(50) NULL,
vend_state char(5) NULL,
vend_zip char(10) NULL,
vend_country char(50) NULL,
PRIMARY KEY(vend_id)
)ENGINE=InnoDB;
21.1.3 主键再介绍
主键值必须唯一.即,表中的每个行必须具有唯一的主键值.如果主键使用单个列,则它的值必须唯一.如果使用多个列,则这些列的组合值必须唯一.
主键可以在创建表时定义,或者在创建表之后定义.
主键和NULL值
第1章介绍过,主键为其值唯一标识表中每个行的列.主键中只能使用不允许NULL值的列.允许NULL值的列不能作为唯一标识.
21.1.4 使用AUTO_INCREMENT
AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量.每次执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值.这样给每个行分配一个唯一的cust_id,从而可以用作主键值.
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如,通过使它成为主键).
覆盖AUTO_INCREMENT
如果一个列被指定为AUTO_INCREMENT,则它需要使用特殊的值吗?你可以简单地在INSERT语句中指定一个值,只要它是唯一的(至今尚未使用过)即可,该值将被用来替代自动生成的值.后续的增量将开始使用该手工插入的值.
确定AUTO_INCREMENT值让MySQL生成(通过自动增量)主 键的一个缺点是你不知道这些值都是谁.
考虑这个场景:你正在增加一个新订单.这要求在orders表中创建一行,然后在orderitms表中对订购的每项物品创建一行.order_num在orderitems表中与订单细节一起存储.这就是为什么orders表和orderitems表为相互关联的表的原因.这显然要求你在插入orders行之后,插入orderitems行之前知道生成的order_num.
那么,如何在使用AUTO_INCREMENT列时获得这个值呢?可使用last_insert_id()函数获得这个值,如下所示:
SELECT last_insert_id()
此语句返回最后一个AUTO_INCREMENT值,然后可以将它用于后续的MySQL语句.
21.1.5 指定默认值
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值.默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定.
不允许函数 与大多数DBMS不一样,MySQL不允许使用函数作为默认值,它只支持常量.
使用默认值而不是NULL值 许多数据库开发人员使用默认值而不是NULL列,特别是对用于计算或数据分组的列更是如此.
例子:
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;
21.1.6 引擎类型
MySQL与其他DBMS不一样,它具有多种引擎.它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行CREATE TABLE和SELECT等命令.
为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性.
当然,你完全可以忽略这些数据库引擎.如果省略ENGINE=语句,则使用默认引擎(很可能是InnoDB),多数SQL语句都会默认使用它.但并不是所有语句都默认使用它,这就是为什么ENGINE=语句很重要的原因.
以下是几个需要知道的引擎:
- InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
- MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘) 中,速度很快(特别适合于临时表);
- MyISAM是一个性能极高的引擎,它支持全文本搜索, 但不支持事务处理.
外键不能跨引擎
混用引擎类型有一个大缺陷.外键不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键.
那么,你应该使用哪个引擎?这有赖于你需要什么样的特性.MyISAM由于其性能和特性可能是最受欢迎的引擎.但如果你不需要可靠的事务处理,可以使用其他引擎.
21.2 更新表
为更新表定义,可使用ALTER TABLE语句.但是,理想状态下,当表中存储数据以后,该表就不应该再被更新.在表的设计过程中需要花费大量时间来考虑,以便后期不对该表进行大的改动.
为了使用ALTER TABLE更改表结构,必须给出下面的信息:
- 在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错);
- 所做更改的列表.
ALTER TABLE的一种常见用途是定义外键.
复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
- 用新的列布局创建一个新表;
- 使用INSERT SELECT语句从旧表复制数据到新表.如果有必要,可使用转换函数和计算字段;
- 检验包含所需数据的新表;
- 重命名旧表(如果确定,可以删除它);
- 用旧表原来的名字重命名新表;
- 根据需要,重新创建触发器、存储过程、索引和外键.
小心使用ALTER TABLE
使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份).数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们.类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据.
例子:
给表添加一个列
ALTER TABLE vendors ADD vend_phone CHAR(20);
删除表中的一列
ALTER TABLE vendors DROP COLUME vend_phone;
21.3 删除表
删除表(删除整个表而不是其内容)非常简单,使用DROP TABLE语句即可
删除表没有确认,也不能撤销,执行这条语句将永久删除该表.
例子:
删除customers2表
DROP TABLE customers2;
21.4 重命名表
使用RENAME TABLE语句可以重命名一个表
例子:
将表customers2重命名为customers
RENAME TABLE customers2 TO customers;
对多个表进行重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
21.5 小结
第22章 使用视图
22.1 视图
视图是虚拟的表.与包含数据的表不一样,视图只包含使用时动态检索数据的查询.
例子:
SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id=order_cust_id AND orderitems.order_num=order.order_num AND proid='TNT2';
22.1.1 为什么使用视图
视图的一些常见应用.
- 重用SQL语句.
- 简化复杂的SQL操作.在编写查询后,可以方便地重用它而不必知道它的基本查询细节.
- 使用表的组成部分而不是整个表.
- 保护数据.可以给用户授予表的特定部分的访问权限而不是整个表的访问权限.
- 更改数据格式和表示.视图可返回与底层表的表示和格式不同的数据.
在视图创建之后,可以用与表基本相同的方式利用它们.可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据.
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施. 视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的. 在添加或更改这些表中的数据时,视图将返回改变过的数据.
性能问题
因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索.如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害.因此,在部署使用了大量视图的应用前,应该进行测试.
22.1.2 视图的规则和限制
关于视图创建和使用的一些最常见的规则和限制.
- 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相 同的名字).
- 对于可以创建的视图数目没有限制.
- 为了创建视图,必须具有足够的访问权限.这些限制通常由数据库管理人员授予.
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图.
- ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖.
- 视图不能索引,也不能有关联的触发器或默认值.
- 视图可以和表一起使用.例如,编写一条联结表和视图的SELECT语句.
22.2 使用视图
在理解什么是视图(以及管理它们的规则及约束)后,我们来看一下视图的创建.
- 视图用CREATE VIEW语句来创建.
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句.
- 用DROP删除视图,其语法为DROP VIEW viewname;.
- 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW.如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图.
22.2.1 利用视图简化复杂的联结
视图的最常见的应用之一是隐藏复杂的SQL,这通常都会涉及联结.
视图极大地简化了复杂SQL语句的使用.利用视图,可一次性编写基础的SQL,然后根据需要多次使用.
创建可重用的视图 创建不受特定数据限制的视图是一种好办法.
例子:
返回已订购了任意产品的所有客户的列表
CREATE VIEW productcusomers 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;
检索订购了产品TNT2的客户
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id='TNT2';
22.2.2 用视图重新格式化检索出的数据
视图的另一常见用途是重新格式化检索出的数据.
例子:
搜索结果
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
创建视图
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
从视图中查询数据
SELECT * FROM vendorlocations;
22.2.3 用视图过滤不想要的数据
视图对于应用普通的WHERE子句也很有用.
HERE子句与WHERE子句
如果从视图检索数据时使用了一条WHERE子句,则两组子句(一组在视图中,另一组是传递给视图的)将自动组合.
例子:
创建视图
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
从视图中查询数据
SELECT * FROM customeremaillist;
22.2.4 使用视图与计算字段
视图非常容易创建,而且很好使用.正确使用,视图可极大地简化复杂的数据处理.
例子:
检索某个特定订单中的物品,计算每种物品的总价格:
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems WHERE order_num=20005;
创建视图
create VIEW orderitemsexpanded AS
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;
从视图中检索数据
SELECT * FROM orderitemsexpanded WHERE order_num=20005;
22.2.5 更新视图
迄今为止的所有视图都是和SELECT语句使用的.然而,视图的数据能否更新?答案视情况而定.
通常,视图是可更新的(即,可以对它们使用INSERT、UPDATE和 DELETE).
更新一个视图将更新其基表.如果你对视图增加或删除行,实际上是对其基表增加或删除行.
但是,并非所有视图都是可更新的.基本上可以说,如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除).这实际上意味着,如果视图定义中有以下操作,则不能进行视图的更新:
- 分组(使用GROUP BY和HAVING);
- 联结;
- 子查询;
- 并;
- 聚集函数(Min()、Count()、Sum()等);
- DISTINCT;
- 导出(计算)列.
视图主要用于数据检索.
将视图用于检索
一般,应该将视图用于检索(SELECT语句)而不用于更新(INSERT、UPDATE和DELETE).
22.3 小结
第23章 使用存储过程
23.1 存储过程
迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句.并非所有操作都这么简单,经常会有一个完整的操作需要多条语句才能完成.
这时可以创建存储过程.存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合.可将其视为批文件,虽然它们的作用不仅限于批处理.
23.2 为什么要使用存储过程
既然我们知道了什么是存储过程,那么为什么要使用它们呢?有许多理由,下面列出一些主要的理由.
- 通过把处理封装在容易使用的单元中,简化复杂的操作.
- 由于不要求反复建立一系列处理步骤,这保证了数据的完整性. 如果所有开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的.这一点的延伸就是防止错误.需要执行的步骤越多,出错的可能性就越大.防止错误保证了数据的一致性.
- 简化对变动的管理.如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码.使用它的人员甚至不需要知道这些变化.这一点的延伸就是安全性.通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会.
- 提高性能.因为使用存储过程比使用单独的SQL语句要快.
- 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可 以使用它们来编写功能更强更灵活的代码
换句话说,使用存储过程有3个主要的好处,即·简单、安全、高性能.· 显然,它们都很重要.
不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷.
- 一般来说,存储过程的编写比基本SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验.
- 你可能没有创建存储过程的安全访问权限.许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程.
尽管有这些缺陷,存储过程还是非常有用的,并且应该尽可能地使用.
不能编写存储过程?
你依然可以使用 MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来.这是好事情.即使你不能(或不想)编写自己的存储过程,也仍然可以在适当的时候执行别的存储过程.
23.3 使用存储过程
23.3.1 执行存储过程
MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL.CALL接受存储过程的名字以及需要传递给它的任意参数.
例子:
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
解决办法是临时更改命令行实用程序的语句分隔符://
其中,DELIMITER //告诉命令行实用程序使用//作为新的语 句结束分隔符,可以看到标志存储过程结束的END定义为END //而不是END;.这样,存储过程体内的;仍然保持不动,并且 正确地传递给数据库引擎.最后,为恢复为原来的语句分隔符,可使用DELIMITER ;. 除\符号外,任何字符都可以用作语句分隔符.
23.3.2 创建存储过程
例子:
创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END;
调用存储过程
CALL productpricing();
23.3.3 删除存储过程
存储过程在创建之后,被保存在服务器上以供使用,直至被删除.
删除命令从服务器中删除存储过程.
例子:
DROP PROCEDURE productpricing;
这条语句删除刚创建的存储过程.请注意没有使用后面的(),只给出存储过程名.
仅当存在时删除 如果指定的过程不存在,则DROPPROCEDURE将产生一个错误.当过程存在想删除它时(如果过程不存在也不产生错误)可使用DROP PROCEDURE IF EXISTS.
23.3.4 使用参数
变量(variable)内存中一个特定的位置,用来临时存储数据.
变量名 所有MySQL变量都必须以@开始.
一般,存储过程并不显示结果,而是把结果返回给你指定的变量.
例子:
CREATE PRODURE productpricing(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price) INTO p1 FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM pruductsl
END;
此存储过程接受3个参数:pl存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格.每个参数必须具有指定的类型,这里使用十进制值.关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者).MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数.存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字).
调用存储过程
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
在调用时,这条语句并不显示任何数据.它返回以后可以显示(或在其他处理中使用)的变量.
显示检索出的变量价格
SELECT @pricelow, @pricehigh, @priceaverage;
使用OUT和IN参数创建存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAl(8,2)
)
BEGIN
SELECT Sum(item_price*quantity) FROM orderitems
WHERE order_num=onumber INTO ototal;
END;
调用存储过程:
CALL ordertotal(2005, @total);
SELECT @total;
23.3.5 建立智能存储过程
迄今为止使用的所有存储过程基本上都是封装MySQL简单的SELECT 语句.虽然它们全都是有效的存储过程例子,但它们所能完成的工作你 直接用这些被封装的语句就能完成.只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来.
COMMENT关键字
本例子中的存储过程在CREATEPROCEDURE语句中包含了一个COMMENT值.它不是必需的,但如果给出,将在SHOW PROCEDURE STATUS的结果中显示.
IF语句
这个例子给出了MySQL的IF语句的基本用法.IF语 句还支持ELSEIF和ELSE子句
例子:
创建复杂的存储过程
-- Name:ordertotal
-- Parameters:
-- onumber=order number
-- taxable=0 if not taxable, 1 if taxable
-- ototal=order total variable
CREATE PRODURE ordertotal(
IN onumber INT,
IN taxable BOOLEAM;
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 Sum(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;
23.3.6 检查存储过程
为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句
为了获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PROCEDURE STATUS.
例子:
SHOW CREATE PRODURE ordertotal;
限制过程状态结果
SHOWPROCEDURESTATUS列出所有存储过程.为限制其输出,可使用LIKE指定一个过滤模式
如:SHOW PRODURE STATUS LIKE 'ordertotal';
23.4 小结
第24章 使用游标
24.1 游标
游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集.在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据.
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改.
只能用于存储过程 不像多数DBMS,MySQL游标只能用于存储过程(和函数).
24.2 使用游标
使用游标涉及几个明确的步骤.
- 在能够使用游标前,必须声明(定义)它.这个过程实际上没有 检索数据,它只是定义要使用的SELECT语句.
- 一旦声明后,必须打开游标以供使用.这个过程用前面定义的 SELECT语句把数据实际检索出来.
- 对于填有数据的游标,根据需要取出(检索)各行.
- 在结束游标使用时,必须关闭游标.
在声明游标后,可根据需要频繁地打开和关闭游标.在游标打开后, 可根据需要频繁地执行取操作.
24.2.1 创建游标
游标用DECLARE语句创建.DECLARE命名游标,并定义 相应的SELECT语句,根据需要带WHERE和其他子句.
在定义游标之后,可以打开它.
例子:
CREATE PRODURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
24.2.2 打开和关闭游标
游标用OPEN CURSOR语句来打开
在处理OPEN语句时执行查询,存储检索出的数据以供浏览和滚动.
游标处理完成后,应当使用如下语句关闭游标: CLOSE CURSOR
CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭.
在一个游标关闭后,如果没有重新打开,则不能使用它.但是,使用声明过的游标不需要再次声明,用OPEN语句打开它就可以了
隐含关闭 如果你不明确关闭游标,MySQL将会在到达END语句时自动关闭它.
例子:
CREATE PRODURE 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;
这个存储过程声明、打开和关闭一个游标.但对检索出的数据什么也没做.
24.2.3 使用游标数据
在一个游标被打开后,可以使用FETCH语句分别访问它的每一行.FETCH指定检索什么数据(所需的列),检索出来的数据存储在什么地方.它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不重复读取同一行).
例子:
从游标中检索单个行
CREATE PRODURE 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 INTO o;
-- Close the cursor
CLOSE ordernumbers;
END;
循环检索数据,从第一行到最后一行
CREATE PRODURE 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 continue 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;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
这条语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码.这里,它指出当SQLSTATE '02000'出现时,SET done=1.SQLSTATE '02000'是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件.
重复或循环?
除这里使用的REPEAT语句外,MySQL还支持循环语句,它可用来重复执行代码,直到使用LEAVE语句手动退出为止.通常REPEAT语句的语法使它更适合于对游标进行循环.
CREATE PRODURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8,2));
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- Get the total for this order
CALL ordertotal(o, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total) VALUES(o,t);
-- End of loop
UNTIL done END REPEAR;
-- Close the cursor
CLOSE ordernumbers;
END;
使用
SELECT * FROM ordertotals;
24.3 小结
第25章 使用触发器
25.1 触发器
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句):
- DELETE;
- INSERT;
- UPDATE.
其他MySQL语句不支持触发器.
25.2 创建触发器
在创建触发器时,需要给出4条信息:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(DELETE、INSERT或UPDATE);
- 触发器何时执行(处理之前或之后).
保持每个数据库的触发器名唯一
在MySQL5中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一.这表示同一数据库中的两个表可具有相同名字的触发器.这在其他每个数据库触发器名必须唯一的DBMS中是不允许的,而且以后的MySQL版本很可能会使命名规则更为严格.因此,现在最好是在数据库范围内使用唯一的触发器名.
仅支持表 只有表才支持触发器,视图不支持(临时表也不支持).
触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器.因此,每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE的之前和之后).单一触发器不能与多个事件或多个表关联,所以,如果你需要一个对INSERT和UPDATE操作执行的触发器,则应该定义两个触发器.
触发器失败
如果BEFORE触发器失败,则MySQL将不执行请求的操作.此外,如果BEFORE触发器或语句本身失败,MySQL将不执行AFTER触发器(如果有的话).
例子:
CREATE TRIGGER newproduct AFTER ON products FOR EACH ROW SELECT 'Product added';
25.3 删除触发器
触发器不能更新或覆盖.为了修改一个触发器,必须先删除它, 然后再重新创建.
现在,删除触发器的语法应该很明显了.为了删除一个触发器,可使用DROP TRIGGER语句
例子:DROP TRIGGER newproduct;
25.4 使用触发器
INSERT触发器在INSERT语句执行之前或之后执行.需要知道以下几点:
- 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;
- 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
- 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值.
BEFORE或AFTER?
通常,将BEFORE用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据).本提示也适用于UPDATE触发器.
例子:
创建一个名为neworder的触发器,它按照AFTER INSERT ON orders执行.在插入一个新订单到orders表时,MySQL生 成一个新订单号并保存到order_num中.触发器从NEW. order_num取得 这个值并返回它.此触发器必须按照AFTER INSERT执行,因为在BEFORE INSERT语句执行之前,新order_num还没有生成.对于orders的每次插 入使用这个触发器将总是返回新的订单号.
CREATE TROGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT new.order_num;
25.4.2 DELETE触发器
DELETE触发器在DELETE语句执行之前或之后执行.需要知道以下两点:
- 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
- OLD中的值全都是只读的,不能更新.
多语句触发器
正如所见,触发器deleteorder使用BEGIN和END语句标记触发器体.这在此例子中并不是必需的,不过也没有害处.使用BEGIN END块的好处是触发器能容纳多条SQL语句(在BEGIN END块中一条挨着一条).
例子:
使用OLD保存将要被删除的行到一个存档表中:
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id)
END;
在任意订单被删除前将执行此触发器.它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_ orders的存档表中(为实际使用这个例子,你需要用与orders相同的列 创建一个名为archive_orders的表).
25.4.3 UPDATE触发器
UPDATE触发器在UPDATE语句执行之前或之后执行.需要知道以下几点:
- 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;
- 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
- OLD中的值全都是只读的,不能更新.
例子:
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
25.4.4 关于触发器的进一步介绍
在结束本章之前,我们再介绍一些使用触发器时需要记住的重点.
- 与其他DBMS相比,MySQL 5中支持的触发器相当初级.未来的MySQL版本中有一些改进和增强触发器支持的计划.
- 创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的.如果INSERT、UPDATE或DELETE语句能够执行,则相关的触发器也能执行.
- 应该用触发器来保证数据的一致性(大小写、格式等).在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明地进行,与客户机应用无关.
- 触发器的一种非常有意义的使用是创建审计跟踪.使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易.
- 遗憾的是,MySQL触发器中不支持CALL语句.这表示不能从触发器内调用存储过程.所需的存储过程代码需要复制到触发器内.
25.5 小结
第26章 管理事务处理
26.1 事务处理
事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行.
事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果.利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示).如果没有错误发生,整组语句提交给(写到)数据库表.如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态.
在使用事务和事务处理时,有几个关键词汇反复出现.下面是关于 事务处理需要知道的几个术语:
- 事务(transaction)指一组SQL语句;
- 回退(rollback)指撤销指定SQL语句的过程;
- 提交(commit)指将未存储的SQL语句结果写入数据库表;
- 保留点(savepoint)指事务处理中设置的临时占位符(place-holder),你可以对它发布回退(与回退整个事务处理不同).
26.2 控制事务处理
管理事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退.
MySQL使用下面的语句来标识事务的开始:START TRANSACTION
26.2.1 使用ROLLBACK
ROLLBACK命令用来回退(撤销)MySQL语句
ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后).
哪些语句可以回退?
事务处理用来管理INSERT、UPDATE和 DELETE语句.你不能回退SELECT语句.
不能回退CREATE或DROP操作.事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销.
例子:
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
首先执行一条SELECT以显示该表不为空.
然后开始一 个事务处理,用一条DELETE语句删除ordertotals中的所有行.
另一条SELECT语句验证ordertotals确实为空.
这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句,
最后一条SELECT语句显示该表不为空.
26.2.2 使用COMMIT
一般的MySQL语句都是直接针对数据库表执行和编写的.这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动进行的.
但是,在事务处理块中,提交不会隐含地进行.为进行明确的提交,使用COMMIT语句.
隐含事务关闭
当COMMIT或ROLLBACK语句执行后,事务会自动关闭(将来的更改会隐含提交).
例子:
START TRANCTION
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;
26.2.3 使用保留点
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理.但是,只是对简单的事务处理才能这样做,更复杂的事务处理可能需要部分提交或回退.
为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符.这样,如果需要回退,可以回退到某个占位符.
这些占位符称为保留点.为了创建占位符,可如下使用SAVEPOINT 语句: SAVEPOINT delete1;
每个保留点都取标识它的唯一名字,以便在回退时,MySQL知道要 回退到何处.
为了回退到本例给出的保留点,可如下进行:ROLLBACK TO delete1;
保留点越多越好
可以在MySQL代码中设置任意多的保留点,越多越好.为什么呢?因为保留点越多,你就越能按自己的意愿灵活地进行回退.
释放保留点
保留点在事务处理完成(执行一条ROLLBACK或 COMMIT)后自动释放.自MySQL 5以来,也可以用RELEASE SAVEPOINT明确地释放保留点.
26.2.4 更改默认的提交行为
正如所述,默认的MySQL行为是自动提交所有更改.换句话说,任何时候你执行一条MySQL语句,该语句实际上都是针对表执行的,而且所做的更改立即生效.为指示MySQL不自动提交更改,需要使用以下语句:SET autocommit=0;
autocommit标志决定是否自动提交更改,不管有没有COMMIT语句.
设置autocommit为0(假)指示MySQL不自动提交更改(直到autocommit被设置为真为止).
标志为连接专用 autocommit标志是针对每个连接而不是服务器的.
26.3 小结
第27章 全球化和本地化
27.1 字符集和校对顺序
数据库表被用来存储和检索数据.不同的语言和字符集需要以不同的方式存储和检索.
因此,MySQL需要适应不同的字符集(不同的字母 和字符),适应不同的排序和检索数据的方法.
在讨论多种语言和字符集时,将会遇到以下重要术语:
- 字符集为字母和符号的集合;
- 编码为某个字符集成员的内部表示;
- 校对为规定字符如何比较的指令.
在MySQL的正常数据库活动(SELECT、INSERT等)中,不需要操心太多的东西.使用何种字符集和校对的决定在服务器、数据库和表级进行.
27.2 使用字符集和校对顺序
MySQL支持众多的字符集.
为查看所支持的字符集完整列表,使用以下语句:SHOW CHARACTER SET;
这条语句显示所有可用的字符集以及每个字符集的描述和默认校对.
为了查看所支持校对的完整列表,使用以下语句:SHOW COLLATION;
此语句显示所有可用的校对,以及它们适用的字符集.可以看到有的字符集具有不止一种校对.
通常系统管理在安装时定义一个默认的字符集和校对.此外,也可以在创建数据库时,指定默认的字符集和校对.
为了确定所用的字符集和校对,可以使用以下语句:
SHOW VARIABLES LIKE 'character%';
SHOW VARIAVLES LIKE 'collation%';
实际上,字符集很少是服务器范围(甚至数据库范围)的设置.不同的表,甚至不同的列都可能需要不同的字符集,而且两者都 可以在创建表时指定.
一般,MySQL如下确定使用什么样的字符集和校对.
- 如果指定CHARACTER SET和COLLATE两者,则使用这些值.
- 如果只指定CHARACTER SET,则使用此字符集及其默认的校对(如SHOW CHARACTER SET的结果中所示).
- 如果既不指定CHARACTER SET,也不指定COLLATE,则使用数据库默认.
除了能指定字符集和校对的表范围外,MySQL还允许对每个列设置.
临时区分大小写
上面的SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术.当然,反过来也是可以的.
SELECT的其他COLLATE子句
除了这里看到的在ORDERBY子句中使用以外,COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等.
最后,值得注意的是,如果绝对需要,串可以在字符集之间进行转 换.为此,使用Cast()或Convert()函数.
例子:
为了给表指定字符集和校对
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
此语句创建一个包含两列的表,并且指定一个字符集和一个校 对顺序.
允许对每个列设置
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
对整个表以及一个特定的列指定了CHARACTER SET和COLLATE.
查询字符集
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_ci;
27.3 小结
第28章 安全管理
28.1 访问控制
MySQL服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权,既不能多也不能少.
换句话说,用户不能对过多的数据具有过多的访问权.
防止无意的错误
重要的是注意到,访问控制的目的不仅仅是防止用户的恶意企图.数据梦魇更为常见的是无意识错误的结果,如错打MySQL语句,在不合适的数据库中操作或其他一些用户错误.通过保证用户不能执行他们不应该执行的语句,访问控制有助于避免这些情况的发生.
不要使用root
应该严肃对待root登录的使用.仅在绝对需要时使用它(或许在你不能登录其他管理账号时使用).不应该在日常的MySQL操作中使用root.
28.2 管理用户
MySQL用户账号和信息存储在名为mysql的MySQL数据库中.一般不需要直接访问mysql数据库和表,但有时需要直接访问.需要直接访问它的时机之一是在需要获得所有用户账号列表时.
用多个客户机进行试验 试验对用户账号和权限进行更改的最好办法是打开多个数据库客户机(如mysql命令行实用程序的多个副本),一个作为管理登录,其他作为被测试的用户登录.
例如：select user from user;
28.2.1 创建用户账号
为了创建一个新用户账号,使用CREATE USER语句
指定散列口令
IDENTIFIEDBY指定的口令为纯文本,MySQL将在保存到user表之前对其进行加密.为了作为散列值指定口令,使用IDENTIFIED BY PASSWORD.
使用GRANT或INSERT
GRANT语句(稍后介绍)也可以创建用户账号,但一般来说CREATE USER是最清楚和最简单的句子.此外,也可以通过直接插入行到user表来增加用户,不过为安全起见,一般不建议这样做.MySQL用来存储用户账号信息的表(以及表模式等)极为重要,对它们的任何毁坏都可能严重地伤害到MySQL服务器.因此,相对于直接处理来说,最好是用标记和函数来处理这些表.
为重新命名一个用户账号,使用RENAME USER语句
MySQL5之前
仅MySQL5或之后的版本支持RENAME USER.为了在以前的MySQL中重命名一个用户,可使用UPDATE直接更新user表.
例子:
CREATE USER ben IDENTIFY BY 'p@wOrd'给出了 一个口令.如果你再次列出用户账号,将会在输出中看到新账号.
RENAME USER ben TO bforta;
28.2.2 删除用户账号
为了删除一个用户账号(以及相关的权限),使用DROP USER语句
MySQL5之前
自MySQL5以来,DROPUSER删除用户账号和所有相关的账号权限.在MySQL 5以前,DROP USER只能用来删除用户账号,不能删除相关的权限.因此,如果使用旧版本的MySQL,需要先用REVOKE删除与账号相关的权限,然后再用DROP USER删除账号.
例子:
DROP USER bforta;
28.2.3 设置访问权限
在创建用户账号后,必须接着分配访问权限.新创建的用户账号没有访问权限.它们能登录MySQL,但不能看到数据,不能执行任何数据库操作.
为看到赋予用户账号的权限,使用SHOW GRANTS FOR username;
用户定义为user@host
MySQL的权限用用户名和主机名结合定义.如果不指定主机名,则使用默认的主机名%(授予用户访问权限而不管主机名).
为设置权限,使用GRANT语句.
GRANT要求你至少给出以下信息:
- 要授予的权限;
- 被授予访问权限的数据库或表;
- 用户名.
每个GRANT添加(或更新)用户的一个权限.MySQL读取所有授权,并根据它们确定权限.
GRANT的反操作为REVOKE,用它来撤销特定的权限.
被撤销的访问权限必须存在,否则会出错.
GRANT和REVOKE可在几个层次上控制访问权限:
- 整个服务器,使用GRANT ALL和REVOKE ALL;
- 整个数据库,使用ON database.*;
- 特定的表,使用ON database.table;
- 特定的列;
- 特定的存储过程.
未来的授权
在使用GRANT和REVOKE时,用户账号必须存在,但对所涉及的对象没有这个要求.这允许管理员在创建数据库和表之前设计和实现安全措施.
这样做的副作用是,当某个数据库或表被删除时(用DROP语句),相关的访问权限仍然存在.而且,如果将来重新创建该数据库或表,这些权限仍然起作用.
简化多次授权
可通过列出各权限并用逗号分隔,将多条 GRANT语句串在一起,如下所示:
GRANT SELECT, INSERT ON crashcourse.* TO bforta;
权限
---|---
ALL|除GRANT OPTION外的所有权限
ALTER|使用ALTER TABLE
ALTER ROUTINE|使用ALTER PROCEDURE和DROP PROCEDURE
CREATE|使用CREATE TABLE
CREATE ROUTINE|使用CREATE PROCEDURE
CREATE TEMPORARY TABLE|使用CREATE TEMPORARY TABLE
CREATE USER|使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW|使用CREATE VIEW
DELETE|使用DELETE
DROP|使用DROP TABLE
EXECUTE|使用CALL和存储过程
FILE|使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION|使用GRANT和REVOKE
INDEX|使用CREATE INDEX和DROP INDEX
INSERT使用INSERT
LOCK TABLES|使用LOCK TABLES
PROCESS|使用SHOW FULL PROCESSLIST
RELOAD|使用FLUSH
REPLICATION CLIENT|服务器位置的访问
REPLICATION SLAVE|由复制从属使用
SELECT|使用SELECT
SHOW DATABASES|使用SHOW DATABASES
SHOW VIEW|使用SHOW CREATE VIEW
SHUTDOWN|使用mysqladmin shutdown(用来关闭MySQL)
SUPER|使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和SET GLOBAL.还允许mysqladmin调试登录
UPDATE|使用UPDATE
USAGE|无访问权限
例子:
SHOW GRANTS FOR bforta;
GRANT SELECT ON crashcourse.* TO bforta;
允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT.
查看一下权限
SHOW GRANTS FOR bforta;
REVOKE SELECT ON crashcourse.* TO bforta;
REVOKE语句取消刚赋予用户bforta的SELECT访问权限.
28.2.4 更改口令
为了更改用户口令,可使用SET PASSWORD语句.
新口令必须如下加密:SET PASSWORD FOR bforta=Password('123456');
SET PASSWORD更新用户口令.新口令必须传递到Password()函数进行加密.
设置自己的口令:SET PASSWORD=Password('123456')
在不指定用户名时,SET PASSWORD更新当前登录用户的口令.
28.3 小结
第29章 数据库维护
29.1 备份数据
像所有数据一样,MySQL的数据也必须经常备份.由于MySQL数据库是基于磁盘的文件,普通的备份系统和例程就能备份MySQL的数据.但是,由于这些文件总是处于打开和使用状态,普通的文件副本备份不一定总是有效.
下面列出这个问题的可能解决方案.
- 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件.在进行常规备份前这个实用程序应该正常运行,以便能正确地备份转储文件.
- 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序).
- 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件.这两条语句都接受将要创建的系统文件名,此系统文件必须不存在,否则会出错.数据可以用RESTORE TABLE来复原.
首先刷新未写数据
为了保证所有数据被写到磁盘(包括索引数据),可能需要在进行备份前使用FLUSH TABLES语句.
29.2 进行数据库维护
MySQL提供了一系列的语句,可以(应该)用来保证数据库正确和正常运行.
以下是你应该知道的一些语句.
- ANALYZE TABLE,用来检查表键是否正确.
- CHECK TABLE用来针对许多问题对表进行检查.在MyISAM表上还对索引进行检查.CHECK TABLE支持一系列的用于MyISAM表的方式. CHANGED检查自最后一次检查以来改动过的表.EXTENDED执行最彻底的检查,FAST只检查未正常关闭的表,MEDIUM检查所有被删除的链接并进行键检验,QUICK只进行快速扫描.
- 如果MyISAM表访问产生不正确和不一致的结果,可能需要用REPAIR TABLE来修复相应的表.这条语句不应该经常使用,如果需要经常使用,可能会有更大的问题要解决.
- 如果从一个表中删除大量数据,应该使用OPTIMIZE TABLE来收回所用的空间,从而优化表的性能.
例子:
analyze table orders;
check table orders, oderitems;
repair table orders;
optimize table orders;
29.3 诊断启动问题
服务器启动问题通常在对MySQL配置或服务器本身进行更改时出 现.MySQL在这个问题发生时报告错误,但由于多数MySQL服务器是作为系统进程或服务自动启动的,这些消息可能看不到.
在排除系统启动问题时,首先应该尽量用手动启动服务器.MySQL服务器自身通过在命令行上执行mysqld启动.
下面是几个重要的mysqld 命令行选项:
- --help显示帮助——一个选项列表;
- --safe-mode装载减去某些最佳配置的服务器;
- --verbose显示全文本消息(为获得更详细的帮助消息与--help联合使用);
- --version显示版本信息然后退出.
几个另外的命令行选项(与日志文件的使用有关)在下一节列出.
29.4 查看日志文件
MySQL维护管理员依赖的一系列日志文件.主要的日志文件有以下几种.
- 错误日志.它包含启动和关闭问题以及任意关键错误的细节.此日志通常名为hostname.err,位于data目录中.此日志名可用--log-error命令行选项更改.
- 查询日志.它记录所有MySQL活动,在诊断问题时非常有用.此日志文件可能会很快地变得非常大,因此不应该长期使用它.此日志通常名为hostname.log,位于data目录中.此名字可以用--log命令行选项更改.
- 二进制日志.它记录更新过数据(或者可能更新过数据)的所有语句.此日志通常名为hostname-bin,位于data目录内.此名字可以用--log-bin命令行选项更改.注意,这个日志文件是MySQL 5中添加的,以前的MySQL版本中使用的是更新日志.
- 缓慢查询日志.顾名思义,此日志记录执行缓慢的任何查询.这个日志在确定数据库何处需要优化很有用.此日志通常名为 hostname-slow.log,位于data目录中.此名字可以用--log-slow-queries命令行选项更改.
在使用日志时,可用FLUSH LOGS语句来刷新和重新开始所有日志文件.
29.5 小结
第30章 改善性能
30.1 改善性能
数据库管理员把他们生命中的相当一部份时间花在了调整、试验以改善DBMS性能之上.在诊断应用的滞缓现象和性能问题时,性能不良的数据库(以及数据库查询)通常是最常见的祸因.
可以看出,下面的内容并不能完全决定MySQL的性能.我们只是想回顾一下前面各章的重点,提供进行性能优化探讨和分析的一个出发点.
- 首先,MySQL(与所有DBMS一样)具有特定的硬件建议.在学习和研究MySQL时,使用任何旧的计算机作为服务器都可以.但对用于生产的服务器来说,应该坚持遵循这些硬件建议.
- 一般来说,关键的生产DBMS应该运行在自己的专用服务器上.
- MySQL是用一系列的默认设置预先配置的,从这些设置开始通常是很好的.但过一段时间后你可能需要调整内存分配、缓冲区大小等.(为查看当前设置,可使用SHOW VARIABLES;和SHOW STATUS;.)
- MySQL一个多用户多线程的DBMS,换言之,它经常同时执行多个任务.如果这些任务中的某一个执行缓慢,则所有请求都会执行缓慢.如果你遇到显著的性能不良,可使用SHOW PROCESSLIST显示所有活动进程(以及它们的线程ID和执行时间).你还可以用KILL命令终结某个特定的进程(使用这个命令需要作为管理员登录).
- 总是有不止一种方法编写同一条SELECT语句.应该试验联结、并、子查询等,找出最佳的方法.
- 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句.
- 一般来说,存储过程执行得比一条一条地执行其中的各条MySQL语句快.
- 应该总是使用正确的数据类型.
- 决不要检索比需求还要多的数据.换言之,不要用SELECT *(除非你真正需要每个列).
- 有的操作(包括INSERT)支持一个可选的DELAYED关键字,如果使用它,将把控制立即返回给调用程序,并且一旦有可能就实际执行该操作.
- 在导入数据时,应该关闭自动提交.你可能还想删除索引(包括FULLTEXT索引),然后在导入完成后再重建它们.
- 必须索引数据库表以改善数据检索的性能.确定索引什么不是一件微不足道的任务,需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句.如果一个简单的WHERE子句返回结果所花的时间太长,则可以断定其中使用的列(或几个列)就是需要索引的对象.
- 你的SELECT语句中有一系列复杂的OR条件吗?通过使用多条SELECT语句和连接它们的UNION语句,你能看到极大的性能改进.
- 索引改善数据检索的性能,但损害数据插入、删除和更新的性能.如果你有一些表,它们收集数据且不经常被搜索,则在有必要之前不要索引它们.(索引可根据需要添加和删除.)
- LIKE很慢.一般来说,最好是使用FULLTEXT而不是LIKE.
- 数据库是不断变化的实体.一组优化良好的表一会儿后可能就面目全非了.由于表的使用和内容的更改,理想的优化和配置也会改变.
- 最重要的规则就是,每条规则在某些条件下都会被打破.
30.2 小结
MySQL语句的语法
C.1 ALTERTABLE:ALTER TABLE用来更新已存在表的模式.
C.2 COMMIT:COMMIT用来将事务处理写到数据库.
C.3 CREATEINDEX:CREATE INDEX用于在一个或多个列上创建索引.
C.4 CREATEPROCEDURE:CREATE PROCEDURE用于创建存储过程.
C.5 CREATETABLE:CREATE TABLE用于创建新数据库表.
C.6 CREATEUSER:CREATE USER 用于向系统中添加新的用户账户.
C.7 CREATEVIEW:CREATE VIEW用来创建一个或多个表上的新视图.
C.8 DELETE:DELETE从表中删除一行或多行.
C.9 DROP:DROP永久地删除数据库对象(表、视图、索引等).
C.10 INSERT:INSERT给表增加一行.
C.11 INSERTSELECT:INSERT SELECT插入SELECT的结果到一个表.
C.12 ROLLBACK:ROLLBACK用于撤销一个事务处理块.
C.13 SAVEPOINT:SAVEPOINT为使用ROLLBACK语句设立保留点.
C.14 SELECT:SELECT用于从一个或多个表(视图)中检索数据.
C.15 STARTTRANSACTION:START TRANSACTION表示一个新的事务处理块的开始.
C.16 UPDATE:UPDATE更新表中一行或多行.
MySQL数据类型
数据类型是定义列中可以存储什么数据以及该数据 实际怎样存储的基本规则.
数据类型用于以下目的.
- 数据类型允许限制可存储在列中的数据.例如,数值数据类型列只能接受数值.
- 数据类型允许在内部更有效地存储数据.可以用一种比文本串更简洁的格式存储数值和日期时间值.
- 数据类型允许变换排序顺序.如果所有数据都作为串处理,则1位于10之前,而10又位于2之前(串以字典顺序排序,从左边开始比较,一次一个字符).作为数值数据类型,数值才能正确排序.
在设计表时,应该特别重视所用的数据类型.使用错误的数据类型 可能会严重地影响应用程序的功能和性能.更改包含数据的列不是一件 小事(而且这样做可能会导致数据丢失).
D.1 串数据类型
最常用的数据类型是串数据类型.它们存储串,如名字、地址、电 话号码、邮政编码等.有两种基本的串类型,分别为定长串和变长串.
定长串接受长度固定的字符串,其长度是在创建表时指定的.CHAR属于定长串类型.
变长串存储可变长度的文本.有些变长数据类型具有最大的定长,而有些则是完全变长的.不管是哪种,只有指定的数据得到保存(额外的数据不保存)TEXT属于变长串类型.
既然变长数据类型这样灵活,为什么还要使用定长数据类型?回答是因为性能.
MySQL处理定长列远比处理变长列快得多.此外,MySQL不允许对变长列(或一个列的可变部分)进行索引.这也会极大地影响性能.
串数据类型
| 数据类型 | 说明 |
|---|---|
| CHAR | 1~255个字符的定长串.它的长度必须在创建时指定,否则MySQL假定为CHAR(1) |
| ENUM | 接受最多64K个串组成的一个预定义集合的某个串 |
| LONGTEXT | 与TEXT相同,但最大长度为4GB |
| MEDIUMTEXT | 与TEXT相同,但最大长度为16K |
| SET | 接受最多64个串组成的一个预定义集合的零个或多个串 |
| TEXT | 最大长度为64K的变长文本 |
| TINYTEXT | 与TEXT相同,但最大长度为255字节 |
| VARCHAR | 长度可变,最多不超过255字节.如果在创建时指定为VARCHAR(n),则可存储0到n个字符的变长串(其中n≤255) |
使用引号 不管使用何种形式的串数据类型,串值都必须括在 引号内(通常单引号更好).
当数值不是数值时
你可能会认为电话号码和邮政编码应该存储在数值字段中(数值字段只存储数值数据),但是,这样做却是不可取的.如果在数值字段中存储邮政编码01234,则保存的将是数值1234,实际上丢失了一位数字. 需要遵守的基本规则是:如果数值是计算(求和、平均等)中使用的数值,则应该存储在数值数据类型列中.如果作为字符串(可能只包含数字)使用,则应该保存在串数据类型列中.
D.2 数值数据类型
数值数据类型存储数值.MySQL支持多种数值数据类型,每种存储的数值具有不同的取值范围.显然,支持的取值范围越大,所需存储空间越多.此外,有的数值数据类型支持使用十进制小数点(和小数),而有的则只支持整数.
有符号或无符号
所有数值数据类型(除BIT和BOOLEAN外)都可以有符号或无符号.有符号数值列可以存储正或负的数值,无符号数值列只能存储正数.默认情况为有符号,但如果你知道自己不需要存储负值,可以使用UNSIGNED关键字,这样做将允许你存储两倍大小的值.
数值数据类型
| 数据类型 | 说明 |
|---|---|
| BIT | 位字段,1~64位.(在MySQL 5之前,BIT在功能上等价于TINYINT |
| BIGINT | 整数值,支持92233720368547758089223372036854775807(如果是UNSIGNED,为018446744073709551615)的数 |
| BOOLEAN(或BOOL) | 布尔标志,或者为0或者为1,主要用于开/关(on/off)标志 |
| DECIMAL(或DEC) | 精度可变的浮点值 |
| DOUBLE | 双精度浮点值 |
| FLOAT | 单精度浮点值 |
| INT(或INTEGER) | 整数值,支持2147483648~2147483647(如果是UNSIGNED, 为0~4294967295)的数 |
| MEDIUMINT | 整数值,支持-83886088388607(如果是UNSIGNED,为016777215)的数 |
| REAL | 4字节的浮点值 |
| SMALLINT | 整数值,支持-3276832767(如果是UNSIGNED,为065535)的数 |
| TINYINT | 整数值,支持-128127(如果为UNSIGNED,为0255)的数 |
不使用引号 与串不一样,数值不应该括在引号内.
存储货币数据类型 MySQL中没有专门存储货币的数据类型,一般情况下使用DECIMAL(8, 2)
D.3 日期和时间数据类型
MySQL使用专门的数据类型来存储日期和时间值.
日期和时间数据类型
| 数据类型 | 说明 |
|---|---|
| DATE | 表示1000-01-01~9999-12-31的日期,格式为 YYYY-MM-DD |
| DATETIME | DATE和TIME的组合 |
| TIMESTAMP | 功能和DATETIME相同(但范围较小) |
| TIME | 格式为HH:MM:SS |
| YEAR | 用2位数字表示,范围是70(1970年)69(2069年),用4位数字表示,范围是1901年2155年 |
D.4 二进制数据类型
二进制数据类型可存储任何数据(甚至包括二进制信息),如图像、多媒体、字处理文档等.
二进制数据类型
| 数据类型 | 说明 |
|---|---|
| BLOB | Blob最大长度为64KB |
| MEDIUMBLOB | Blob最大长度为16MB |
| LONGBLOB | Blob最大长度为4GB |
| TINYBLOB | Blob最大长度为255字节 |
