MySQL不仅用于表数据操纵,而且还可以用来执行数据库和表的所有操作,包括表本身的创建和处理.
一般有两种创建表的方法:
为了用程序创建表,可使用SQL的CREATE TABLE语句.值得注意的是,在使用交互式工具时,实际上使用的是MySQL语句.但是,这些语句不是用户编写的,界面工具会自动生成并执行相应的MySQL语句
为利用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;
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;
主键值必须唯一.即,表中的每个行必须具有唯一的主键值.如果主键使用单个列,则它的值必须唯一.如果使用多个列,则这些列的组合值必须唯一.
主键可以在创建表时定义,或者在创建表之后定义.
主键和NULL值第1章介绍过,主键为其值唯一标识表中每个行的列.主键中只能使用不允许NULL值的列.允许NULL值的列不能作为唯一标识.
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语句.
如果在插入行时没有给出值,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;
MySQL与其他DBMS不一样,它具有多种引擎.它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行CREATE TABLE和SELECT等命令.
为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性.
当然,你完全可以忽略这些数据库引擎.如果省略ENGINE=语句,则使用默认引擎(很可能是InnoDB),多数SQL语句都会默认使用它.但并不是所有语句都默认使用它,这就是为什么ENGINE=语句很重要的原因.
以下是几个需要知道的引擎:
外键不能跨引擎混用引擎类型有一个大缺陷.外键不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键.
那么,你应该使用哪个引擎?这有赖于你需要什么样的特性.MyISAM由于其性能和特性可能是最受欢迎的引擎.但如果你不需要可靠的事务处理,可以使用其他引擎.
为了使用ALTER TABLE更改表结构,必须给出下面的信息:
ALTER TABLE的一种常见用途是定义外键.
复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
小心使用ALTER TABLE使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份).数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们.类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据.
例子:给表添加一个列ALTER TABLE vendors ADD vend_phone CHAR(20);
删除表中的一列ALTER TABLE vendors DROP COLUME vend_phone;
删除表(删除整个表而不是其内容)非常简单,使用DROP TABLE语句即可
删除表没有确认,也不能撤销,执行这条语句将永久删除该表.
例子:删除customers2表DROP TABLE customers2;
使用RENAME TABLE语句可以重命名一个表
例子:将表customers2重命名为customersRENAME TABLE customers2 TO customers;
对多个表进行重命名RENAME TABLE backup_customers TO customers,backup_vendors TO vendors,backup_products TO products;
视图是虚拟的表.与包含数据的表不一样,视图只包含使用时动态检索数据的查询.
视图的一些常见应用.
在视图创建之后,可以用与表基本相同的方式利用它们.可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据.
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施. 视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的. 在添加或更改这些表中的数据时,视图将返回改变过的数据.
性能问题因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索.如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害.因此,在部署使用了大量视图的应用前,应该进行测试.
关于视图创建和使用的一些最常见的规则和限制.
22.2 使用视图在理解什么是视图(以及管理它们的规则及约束)后,我们来看一下视图的创建.
视图的最常见的应用之一是隐藏复杂的SQL,这通常都会涉及联结.
视图极大地简化了复杂SQL语句的使用.利用视图,可一次性编写基础的SQL,然后根据需要多次使用.
创建可重用的视图 创建不受特定数据限制的视图是一种好办法.
检索订购了产品TNT2的客户SELECT cust_name, cust_contactFROM productcustomersWHERE prod_id='TNT2';
视图的另一常见用途是重新格式化检索出的数据.
例子:搜索结果SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')AS vend_titleFROM vendorsORDER BY vend_name;
创建视图CREATE VIEW vendorlocations ASSELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')AS vend_titleFROM vendorsORDER BY vend_name;
从视图中查询数据SELECT * FROM vendorlocations;
视图对于应用普通的WHERE子句也很有用.
HERE子句与WHERE子句如果从视图检索数据时使用了一条WHERE子句,则两组子句(一组在视图中,另一组是传递给视图的)将自动组合.
例子:创建视图CREATE VIEW customeremaillist ASSELECT cust_id, cust_name, cust_emailFROM customersWHERE cust_email IS NOT NULL;
从视图中查询数据SELECT * FROM customeremaillist;
视图非常容易创建,而且很好使用.正确使用,视图可极大地简化复杂的数据处理.
例子:检索某个特定订单中的物品,计算每种物品的总价格:SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_priceFROM orderitems WHERE order_num=20005;
创建视图create VIEW orderitemsexpanded ASSELECT prod_id, quantity, item_price, quantity*item_price AS expanded_priceFROM orderitems;
从视图中检索数据SELECT * FROM orderitemsexpanded WHERE order_num=20005;
迄今为止的所有视图都是和SELECT语句使用的.然而,视图的数据能否更新?答案视情况而定.通常,视图是可更新的(即,可以对它们使用INSERT、UPDATE和 DELETE).更新一个视图将更新其基表.如果你对视图增加或删除行,实际上是对其基表增加或删除行.但是,并非所有视图都是可更新的.基本上可以说,如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除).这实际上意味着,如果视图定义中有以下操作,则不能进行视图的更新:
将视图用于检索一般,应该将视图用于检索(SELECT语句)而不用于更新(INSERT、UPDATE和DELETE).
迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句.并非所有操作都这么简单,经常会有一个完整的操作需要多条语句才能完成.这时可以创建存储过程.存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合.可将其视为批文件,虽然它们的作用不仅限于批处理.
既然我们知道了什么是存储过程,那么为什么要使用它们呢?有许多理由,下面列出一些主要的理由.
换句话说,使用存储过程有3个主要的好处,即·简单、安全、高性能.· 显然,它们都很重要.
不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷.
不能编写存储过程?你依然可以使用 MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来.这是好事情.即使你不能(或不想)编写自己的存储过程,也仍然可以在适当的时候执行别的存储过程.
例子:CALL productpricing(@pricelow, @pricehigh, @priceaverage);
解决办法是临时更改命令行实用程序的语句分隔符://其中,DELIMITER //告诉命令行实用程序使用//作为新的语 句结束分隔符,可以看到标志存储过程结束的END定义为END //而不是END;.这样,存储过程体内的;仍然保持不动,并且 正确地传递给数据库引擎.最后,为恢复为原来的语句分隔符,可使用DELIMITER ;. 除\符号外,任何字符都可以用作语句分隔符.
例子:创建存储过程CREATE PROCEDURE productpricing()BEGINSELECT Avg(prod_price) AS priceaverage FROM products;END;调用存储过程CALL productpricing();
存储过程在创建之后,被保存在服务器上以供使用,直至被删除.删除命令从服务器中删除存储过程.
例子:DROP PROCEDURE productpricing;这条语句删除刚创建的存储过程.请注意没有使用后面的(),只给出存储过程名.
仅当存在时删除 如果指定的过程不存在,则DROPPROCEDURE将产生一个错误.当过程存在想删除它时(如果过程不存在也不产生错误)可使用DROP PROCEDURE IF EXISTS.
变量(variable)内存中一个特定的位置,用来临时存储数据.变量名 所有MySQL变量都必须以@开始.一般,存储过程并不显示结果,而是把结果返回给你指定的变量.
例子:CREATE PRODURE productpricing(OUT p1 DECIMAL(8,2),OUT ph DECIMAL(8,2),OUT pa DECIMAL(8,2))BEGINSELECT MIN(prod_price) INTO p1 FROM products;SELECT MAX(prod_price) INTO ph FROM products;SELECT Avg(prod_price) INTO pa FROM pruductslEND;
此存储过程接受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))BEGINSELECT Sum(item_price*quantity) FROM orderitemsWHERE order_num=onumber INTO ototal;END;
调用存储过程:CALL ordertotal(2005, @total);SELECT @total;
迄今为止使用的所有存储过程基本上都是封装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 totalDECLARE total DECIMAL(8,2);-- Declare tax percentageDECLARE taxrate INT DEFAULT 6;
-- Get the order totalSELECT Sum(item_price*quantity)FROM orderitemsWHERE order_num=onumberINTO total;
-- Is this taxable?IF taxable THEN-- Yes, so add taxrate to the totalSELECT total+(total/100*taxrate) INTO total;END IF;
END;
为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句
为了获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PROCEDURE STATUS.
例子:SHOW CREATE PRODURE ordertotal;
限制过程状态结果SHOWPROCEDURESTATUS列出所有存储过程.为限制其输出,可使用LIKE指定一个过滤模式如:SHOW PRODURE STATUS LIKE 'ordertotal';
游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集.在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据.
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改.
只能用于存储过程 不像多数DBMS,MySQL游标只能用于存储过程(和函数).
使用游标涉及几个明确的步骤.
游标用DECLARE语句创建.DECLARE命名游标,并定义 相应的SELECT语句,根据需要带WHERE和其他子句.
在定义游标之后,可以打开它.
例子:CREATE PRODURE processorders()BEGINDECLARE ordernumbers CURSORFORSELECT order_num FROM orders;END;
游标用OPEN CURSOR语句来打开
在处理OPEN语句时执行查询,存储检索出的数据以供浏览和滚动.
游标处理完成后,应当使用如下语句关闭游标: CLOSE CURSOR
CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭.
隐含关闭 如果你不明确关闭游标,MySQL将会在到达END语句时自动关闭它.
例子:CREATE PRODURE processorders()BEGIN-- Declare the cursorDECLARE ordernumbers CURSORFORSELECT order_num FROM orders;
在一个游标被打开后,可以使用FETCH语句分别访问它的每一行.FETCH指定检索什么数据(所需的列),检索出来的数据存储在什么地方.它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不重复读取同一行).
例子:从游标中检索单个行CREATE PRODURE processorders()BEGIN-- Declare local variablesDECLARE o INT;
END;
循环检索数据,从第一行到最后一行CREATE PRODURE processorders()BEGIN-- Declare local variablesDECLARE done BOOLEAN DEFAULT 0;DECLARE o INT;
END;
重复或循环?除这里使用的REPEAT语句外,MySQL还支持循环语句,它可用来重复执行代码,直到使用LEAVE语句手动退出为止.通常REPEAT语句的语法使它更适合于对游标进行循环.
CREATE PRODURE processorders()BEGIN-- Declare local variablesDECLARE done BOOLEAN DEFAULT 0;DECLARE o INT;DECLARE t DECIMAL(8,2);
END;
使用SELECT * FROM ordertotals;
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句):
在创建触发器时,需要给出4条信息:
保持每个数据库的触发器名唯一在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';
触发器不能更新或覆盖.为了修改一个触发器,必须先删除它, 然后再重新创建.
现在,删除触发器的语法应该很明显了.为了删除一个触发器,可使用DROP TRIGGER语句
例子:DROP TRIGGER newproduct;
INSERT触发器在INSERT语句执行之前或之后执行.需要知道以下几点:
BEFORE或AFTER?通常,将BEFORE用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据).本提示也适用于UPDATE触发器.
DELETE触发器在DELETE语句执行之前或之后执行.需要知道以下两点:
多语句触发器正如所见,触发器deleteorder使用BEGIN和END语句标记触发器体.这在此例子中并不是必需的,不过也没有害处.使用BEGIN END块的好处是触发器能容纳多条SQL语句(在BEGIN END块中一条挨着一条).
在任意订单被删除前将执行此触发器.它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_ orders的存档表中(为实际使用这个例子,你需要用与orders相同的列 创建一个名为archive_orders的表).
UPDATE触发器在UPDATE语句执行之前或之后执行.需要知道以下几点:
在结束本章之前,我们再介绍一些使用触发器时需要记住的重点.
26.1 事务处理事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行.
事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果.利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示).如果没有错误发生,整组语句提交给(写到)数据库表.如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态.
在使用事务和事务处理时,有几个关键词汇反复出现.下面是关于 事务处理需要知道的几个术语:
管理事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退.
MySQL使用下面的语句来标识事务的开始:START TRANSACTION
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语句显示该表不为空.
一般的MySQL语句都是直接针对数据库表执行和编写的.这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动进行的.
但是,在事务处理块中,提交不会隐含地进行.为进行明确的提交,使用COMMIT语句.
隐含事务关闭当COMMIT或ROLLBACK语句执行后,事务会自动关闭(将来的更改会隐含提交).
例子:START TRANCTIONDELETE FROM orderitems WHERE order_num=20010;DELETE FROM orders WHERE order_num=20010;COMMIT;
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理.但是,只是对简单的事务处理才能这样做,更复杂的事务处理可能需要部分提交或回退.
为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符.这样,如果需要回退,可以回退到某个占位符.
这些占位符称为保留点.为了创建占位符,可如下使用SAVEPOINT 语句: SAVEPOINT delete1;每个保留点都取标识它的唯一名字,以便在回退时,MySQL知道要 回退到何处.为了回退到本例给出的保留点,可如下进行:ROLLBACK TO delete1;
保留点越多越好可以在MySQL代码中设置任意多的保留点,越多越好.为什么呢?因为保留点越多,你就越能按自己的意愿灵活地进行回退.
释放保留点保留点在事务处理完成(执行一条ROLLBACK或 COMMIT)后自动释放.自MySQL 5以来,也可以用RELEASE SAVEPOINT明确地释放保留点.
正如所述,默认的MySQL行为是自动提交所有更改.换句话说,任何时候你执行一条MySQL语句,该语句实际上都是针对表执行的,而且所做的更改立即生效.为指示MySQL不自动提交更改,需要使用以下语句:SET autocommit=0;
autocommit标志决定是否自动提交更改,不管有没有COMMIT语句.
设置autocommit为0(假)指示MySQL不自动提交更改(直到autocommit被设置为真为止).
标志为连接专用 autocommit标志是针对每个连接而不是服务器的.
数据库表被用来存储和检索数据.不同的语言和字符集需要以不同的方式存储和检索.因此,MySQL需要适应不同的字符集(不同的字母 和字符),适应不同的排序和检索数据的方法.在讨论多种语言和字符集时,将会遇到以下重要术语:
在MySQL的正常数据库活动(SELECT、INSERT等)中,不需要操心太多的东西.使用何种字符集和校对的决定在服务器、数据库和表级进行.
MySQL支持众多的字符集.为查看所支持的字符集完整列表,使用以下语句:SHOW CHARACTER SET;这条语句显示所有可用的字符集以及每个字符集的描述和默认校对.
为了查看所支持校对的完整列表,使用以下语句:SHOW COLLATION;此语句显示所有可用的校对,以及它们适用的字符集.可以看到有的字符集具有不止一种校对.
通常系统管理在安装时定义一个默认的字符集和校对.此外,也可以在创建数据库时,指定默认的字符集和校对.为了确定所用的字符集和校对,可以使用以下语句:SHOW VARIABLES LIKE 'character%';SHOW VARIAVLES LIKE 'collation%';
实际上,字符集很少是服务器范围(甚至数据库范围)的设置.不同的表,甚至不同的列都可能需要不同的字符集,而且两者都 可以在创建表时指定.
一般,MySQL如下确定使用什么样的字符集和校对.
除了能指定字符集和校对的表范围外,MySQL还允许对每个列设置.
临时区分大小写上面的SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术.当然,反过来也是可以的.
SELECT的其他COLLATE子句除了这里看到的在ORDERBY子句中使用以外,COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等.
最后,值得注意的是,如果绝对需要,串可以在字符集之间进行转 换.为此,使用Cast()或Convert()函数.
例子:为了给表指定字符集和校对CREATE TABLE mytable(column1 INT,column2 VARCHAR(10))DEFAULT CHARACTER SET hebrewCOLLATE hebrew_general_ci;此语句创建一个包含两列的表,并且指定一个字符集和一个校 对顺序.
允许对每个列设置CREATE TABLE mytable(column1 INT,column2 VARCHAR(10),column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci)DEFAULT CHARACTER SET hebrewCOLLATE hebrew_general_ci;对整个表以及一个特定的列指定了CHARACTER SET和COLLATE.
查询字符集SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_ci;
MySQL服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权,既不能多也不能少.换句话说,用户不能对过多的数据具有过多的访问权.
防止无意的错误重要的是注意到,访问控制的目的不仅仅是防止用户的恶意企图.数据梦魇更为常见的是无意识错误的结果,如错打MySQL语句,在不合适的数据库中操作或其他一些用户错误.通过保证用户不能执行他们不应该执行的语句,访问控制有助于避免这些情况的发生.
MySQL用户账号和信息存储在名为mysql的MySQL数据库中.一般不需要直接访问mysql数据库和表,但有时需要直接访问.需要直接访问它的时机之一是在需要获得所有用户账号列表时.
例如：select user from user;
为了创建一个新用户账号,使用CREATE USER语句
指定散列口令IDENTIFIEDBY指定的口令为纯文本,MySQL将在保存到user表之前对其进行加密.为了作为散列值指定口令,使用IDENTIFIED BY PASSWORD.
使用GRANT或INSERTGRANT语句(稍后介绍)也可以创建用户账号,但一般来说CREATE USER是最清楚和最简单的句子.此外,也可以通过直接插入行到user表来增加用户,不过为安全起见,一般不建议这样做.MySQL用来存储用户账号信息的表(以及表模式等)极为重要,对它们的任何毁坏都可能严重地伤害到MySQL服务器.因此,相对于直接处理来说,最好是用标记和函数来处理这些表.
为重新命名一个用户账号,使用RENAME USER语句
MySQL5之前仅MySQL5或之后的版本支持RENAME USER.为了在以前的MySQL中重命名一个用户,可使用UPDATE直接更新user表.
RENAME USER ben TO bforta;
MySQL5之前自MySQL5以来,DROPUSER删除用户账号和所有相关的账号权限.在MySQL 5以前,DROP USER只能用来删除用户账号,不能删除相关的权限.因此,如果使用旧版本的MySQL,需要先用REVOKE删除与账号相关的权限,然后再用DROP USER删除账号.
例子:DROP USER bforta;
在创建用户账号后,必须接着分配访问权限.新创建的用户账号没有访问权限.它们能登录MySQL,但不能看到数据,不能执行任何数据库操作.为看到赋予用户账号的权限,使用SHOW GRANTS FOR username;
用户定义为user@hostMySQL的权限用用户名和主机名结合定义.如果不指定主机名,则使用默认的主机名%(授予用户访问权限而不管主机名).
为设置权限,使用GRANT语句.GRANT要求你至少给出以下信息:
GRANT的反操作为REVOKE,用它来撤销特定的权限.被撤销的访问权限必须存在,否则会出错.
GRANT和REVOKE可在几个层次上控制访问权限:
未来的授权在使用GRANT和REVOKE时,用户账号必须存在,但对所涉及的对象没有这个要求.这允许管理员在创建数据库和表之前设计和实现安全措施.这样做的副作用是,当某个数据库或表被删除时(用DROP语句),相关的访问权限仍然存在.而且,如果将来重新创建该数据库或表,这些权限仍然起作用.
简化多次授权可通过列出各权限并用逗号分隔,将多条 GRANT语句串在一起,如下所示:GRANT SELECT, INSERT ON crashcourse.* TO bforta;
权限---|---ALL|除GRANT OPTION外的所有权限ALTER|使用ALTER TABLEALTER ROUTINE|使用ALTER PROCEDURE和DROP PROCEDURECREATE|使用CREATE TABLECREATE ROUTINE|使用CREATE PROCEDURECREATE TEMPORARY TABLE|使用CREATE TEMPORARY TABLECREATE USER|使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGESCREATE VIEW|使用CREATE VIEWDELETE|使用DELETEDROP|使用DROP TABLEEXECUTE|使用CALL和存储过程FILE|使用SELECT INTO OUTFILE和LOAD DATA INFILEGRANT OPTION|使用GRANT和REVOKEINDEX|使用CREATE INDEX和DROP INDEXINSERT使用INSERTLOCK TABLES|使用LOCK TABLESPROCESS|使用SHOW FULL PROCESSLISTRELOAD|使用FLUSHREPLICATION CLIENT|服务器位置的访问REPLICATION SLAVE|由复制从属使用SELECT|使用SELECTSHOW DATABASES|使用SHOW DATABASESSHOW VIEW|使用SHOW CREATE VIEWSHUTDOWN|使用mysqladmin shutdown(用来关闭MySQL)SUPER|使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和SET GLOBAL.还允许mysqladmin调试登录UPDATE|使用UPDATEUSAGE|无访问权限
例子: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访问权限.
为了更改用户口令,可使用SET PASSWORD语句.新口令必须如下加密:SET PASSWORD FOR bforta=Password('123456');SET PASSWORD更新用户口令.新口令必须传递到Password()函数进行加密.设置自己的口令:SET PASSWORD=Password('123456')在不指定用户名时,SET PASSWORD更新当前登录用户的口令.
像所有数据一样,MySQL的数据也必须经常备份.由于MySQL数据库是基于磁盘的文件,普通的备份系统和例程就能备份MySQL的数据.但是,由于这些文件总是处于打开和使用状态,普通的文件副本备份不一定总是有效.
下面列出这个问题的可能解决方案.
首先刷新未写数据为了保证所有数据被写到磁盘(包括索引数据),可能需要在进行备份前使用FLUSH TABLES语句.
MySQL提供了一系列的语句,可以(应该)用来保证数据库正确和正常运行.
以下是你应该知道的一些语句.
例子:analyze table orders;check table orders, oderitems;repair table orders;optimize table orders;
服务器启动问题通常在对MySQL配置或服务器本身进行更改时出 现.MySQL在这个问题发生时报告错误,但由于多数MySQL服务器是作为系统进程或服务自动启动的,这些消息可能看不到.
在排除系统启动问题时,首先应该尽量用手动启动服务器.MySQL服务器自身通过在命令行上执行mysqld启动.
下面是几个重要的mysqld 命令行选项:
MySQL维护管理员依赖的一系列日志文件.主要的日志文件有以下几种.
在使用日志时,可用FLUSH LOGS语句来刷新和重新开始所有日志文件.
可以看出,下面的内容并不能完全决定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更新表中一行或多行.
数据类型是定义列中可以存储什么数据以及该数据 实际怎样存储的基本规则.数据类型用于以下目的.
最常用的数据类型是串数据类型.它们存储串,如名字、地址、电 话号码、邮政编码等.有两种基本的串类型,分别为定长串和变长串.定长串接受长度固定的字符串,其长度是在创建表时指定的.CHAR属于定长串类型.变长串存储可变长度的文本.有些变长数据类型具有最大的定长,而有些则是完全变长的.不管是哪种,只有指定的数据得到保存(额外的数据不保存)TEXT属于变长串类型.
既然变长数据类型这样灵活,为什么还要使用定长数据类型?回答是因为性能.MySQL处理定长列远比处理变长列快得多.此外,MySQL不允许对变长列(或一个列的可变部分)进行索引.这也会极大地影响性能.
串数据类型
使用引号 不管使用何种形式的串数据类型,串值都必须括在 引号内(通常单引号更好).
当数值不是数值时你可能会认为电话号码和邮政编码应该存储在数值字段中(数值字段只存储数值数据),但是,这样做却是不可取的.如果在数值字段中存储邮政编码01234,则保存的将是数值1234,实际上丢失了一位数字. 需要遵守的基本规则是:如果数值是计算(求和、平均等)中使用的数值,则应该存储在数值数据类型列中.如果作为字符串(可能只包含数字)使用,则应该保存在串数据类型列中.
数值数据类型存储数值.MySQL支持多种数值数据类型,每种存储的数值具有不同的取值范围.显然,支持的取值范围越大,所需存储空间越多.此外,有的数值数据类型支持使用十进制小数点(和小数),而有的则只支持整数.
有符号或无符号所有数值数据类型(除BIT和BOOLEAN外)都可以有符号或无符号.有符号数值列可以存储正或负的数值,无符号数值列只能存储正数.默认情况为有符号,但如果你知道自己不需要存储负值,可以使用UNSIGNED关键字,这样做将允许你存储两倍大小的值.
数值数据类型
不使用引号 与串不一样,数值不应该括在引号内.
存储货币数据类型 MySQL中没有专门存储货币的数据类型,一般情况下使用DECIMAL(8, 2)
二进制数据类型可存储任何数据(甚至包括二进制信息),如图像、多媒体、字处理文档等.