mysql数据库各种应用_数据库 : Mysql - 日常应用

#登录MYSQL数据库

MYSQL-u root -p

#显示所有数据库

SHOW databases;

#显示当前数据库与应用程序间进行了多少条连接

SHOW processlist;

#使用某一数据库USE database;

#显示所有表

SHOW tables;

#表结构描述 describeDESC table;

#筛选表数据

#选择哪些列SELECTprod_name, prod_price

#从哪个表选FROMproducts

#选择的列做出什么筛选匹配WHERE vend_id = 1003 OR vend_id = 1004#根据什么顺序作出排序DESC 降序 ASC升序ORDER BYprod_price;WHERE--------------------------

vend_id IN(......)

vend_idIS NOT NULL

-------------------------------------

SELECTprod_id, prod_priceFROMproductsWHERE prod_name LIKE 'jet%'

ORDER BYprod_price;-------------------------------------

WHERE prod_name LIKE '_ ton anvil'

-------------------------------------/*你最好避免使用匹配符_ %，因为会有性能损失的问题，

尤其别在搜索的字符串前面使用。*/#正则表达式SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1000'

ORDER BYprod_name;---------------------------------------

#模糊查询常用SELECTprod_nameFROMproductsWHERE prod_name LIKE '1000'

ORDER BYprod_name;SELECTprod_nameFROMproductsWHERE prod_name REGEXP '.000'

ORDER BYprod_name;/*LIKE 匹配整个列。如果被匹配的文本在列值

中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用

通配符)。而 REGEXP 在列值内进行匹配,如果被匹配的文本在

列值中出现, REGEXP 将会找到它,相应的行将被返回。这是一

个非常重要的差别。*/#匹配两个串之一SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1000|2000'

ORDER BYprod_name;

#匹配几个字符之一SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[123] Ton'

ORDER BYprod_name;

#否定匹配,匹配这些字符除外的任何串SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[^123] ton'

ORDER BYprod_name;

#匹配范围可以被指定SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[1-9] ton'

ORDER BYprod_name;

#匹配特殊字符需要在其前面添加\\作为前'.'匹配0个或1个字符SELECTprod_nameFROMproductsWHERE prod_name REGEXP '\\.'

ORDER BYprod_name;--------------------------------------------------------------

#字符类/*[:alnum:] [a-zA-Z0-9]

[:alpha:] [a-zA-Z]

[:blank:] [\\t]

[:cntrl:] [ASCII 0-31和127]

[:digit:] [0-9]

[:graph:] 和[:print:]相同,但不包括空格

[:lower:] [a-z]

[:print:] 任意可打印字符

[:punct:] 既不在[:alnum]又不在[:cntrl:]的字符

[:space:] 包括空格在内的任意空白字符(\\f\\n\\r\\t\\v)

[:upper:] [A-Z]

[:xdigit:] 十六进制[a-fA-F0-9]*/

--------------------------------------------------------------

#重复元字符/** 0个或多个匹配

+ 1个或多个匹配(等于{1,})

? 0个或1个匹配(等于{0,1})

{n} 指定数目的匹配

{n,} 不少于指定数目的匹配

{n,m} 匹配数目的范围(m不超过255)*/

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '\\([0-9] sticks?\\)'

ORDER BYprod_name;SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[[:digit:]]{4}'

ORDER BYprod_name;------------------------------------------------------------------/*^ 文本的开始

$ 文本的结尾

[[:<:>

[[:>:]] 词的结尾*/

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '^[0-9\\.]'

ORDER BYprod_name;-----------------------------------------------------------------/*计算字段

一般数据库中存取的数据与应用程序所要求的数据格式并不一致，

所以需要进行格式转换，而计算字段就是应用于这一用途的字段。

计算字段是运行时在SELECT中创建出来的。*/#拼接--例：供应商 + 地址

SELECT Concat(vend_name, '(', vend_country, ')')FROMvendorsORDER BYvend_name;

#剪切空格RTrim

SELECT Concat(RTrim(vend_name), '(', vend_country, ')')FROMvendorsORDER BYvend_name;

#使用别名AS

SELECT Concat(RTrim(vend_name), '(', vend_country, ')') ASvend_titleFROMvendorsORDER BYvend_name;

#执行算术运算SELECTprod_id,

quantity,

item_price,

quantity*item_price ASexpanded_priceFROMorderitemsWHERE order_num = 20005;---------------------------------------------------

#函数

#函数能方便程序员编写代码，但是这样会影响代码的可移植性。/*1.文本处理函数

Left() 返回串左边的字符

Length() 返回串的长度

Locate() 找出串的一个子串

Lower() 将串转换为小写

LTrim() 去掉串左边的空格

Right() 返回串右边的字符

RTrim() 去掉串右边的空格

Soundex() 返回串的SOUNDEX值

SubString() 返回子串的字符

Upper() 将串转换为大写

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SOUNDEX 考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。

以下进行该函数的使用对比*/

SELECTcust_name,cust_contactFROMcustomersWHERE cust_contact = 'Y. Lie';SELECTcust_name,cust_contactFROMcustomersWHERE Soundex(cust_contact) = Soundex('Y. Lie');/*日期和时间处理函数

AddDate() 增加一个日期(天、周等)

AddTime() 增加一个时间(时、分等)

CurDate() 返回当前日期

CurTime() 返回当前时间

Date() 返回日期时间的日期部分

DateDiff() 计算两个日期之差

Date_Add() 高度灵活的日期运算函数

Date_Format() 返回一个格式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek() 对于一个日期,返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Month() 返回一个日期的月份部分

Now() 返回当前日期和时间

Second() 返回一个时间的秒部分

Time() 返回一个日期时间的时间部分

Year() 返回一个日期的年份部分*/

SELECTcust_id, order_numFROMordersWHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';/*数值处理

Abs() 返回一个数的绝对值

Cos() 返回一个角度的余弦

Exp() 返回一个数的指数值

Mod() 返回除操作的余数

Pi() 返回圆周率

Rand() 返回一个随机数

Sin() 返回一个角度的正弦

Sqrt() 返回一个数的平方根

Tan() 返回一个角度的正切*/

-------------------------------------------------------------

#聚集函数

#我们经常需要汇总数据而不用把它们实际检索出来。/*AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

DISTINCT 聚焦不同值， ALL 全部值进行计算*/

SELECT AVG(prod_price) ASavg_priceFROMproductsWHERE vend_id = 1003;SELECT COUNT(*) ASnum_custFROMcustomers;SELECT AVG(DISTINCT prod_price) ASavg_priceFROMproductsWHERE vend_id = 1003;SELECT COUNT(*) ASnum_items,MIN(prod_price) ASprice_min,MAX(prod_price) ASprice_max,AVG(prod_price) ASprice_avgFROMproducts;-------------------------------------------------------------

#数据分组/*数据分组由GROUP BY 语句引出，注意一点就是选择的字段如果不是

聚集函数则需要归在分组字段中

WITH ROLLUP关键字可以得到每个分组以及每个分组汇总级别

(针对每个分组)的值*/

SELECT vend_id, COUNT(*) ASnum_prodsFROMproductsGROUP BYvend_id;SELECT vend_id, AVG(prod_price) AS price_avg, COUNT(*)FROMproductsGROUP BYvend_id;SELECT vend_id, AVG(prod_price) AS price_avg, COUNT(*)FROMproductsGROUP BY vend_id WITHROLLUP;

#过滤分组SELECT cust_id, COUNT(*) ASordersFROMordersGROUP BYcust_idHAVING COUNT(*) >= 2;

#HAVING过滤的是分组后数据组，而WHERE过滤的是数据项SELECT vend_id, COUNT(*) ASnum_prodsFROMproductsWHERE prod_price >= 10

GROUP BYvend_idHAVING COUNT(*) >= 2;SELECT order_num, SUM(quantity*item_price) ASordertotalFROMorderitemsGROUP BYorder_numHAVING ordertotal >= 50

ORDER BYordertotal;

#SELECT子句顺序

#SELECT --FROM -- WHERE -- GROUP BY --

#--HAVING -- ORDER BY -- LIMIT

---------------------------------------------------------------

#子查询SELECTcust_idFROMordersWHERE order_num IN (SELECTorder_numFROMorderitemsWHERE prod_id = 'TNT2');SELECTcust_name,

cust_state,

(SELECT COUNT(*)FROMordersWHERE orders.cust_id = customers.cust_id) ASordersFROMcustomersORDER BYcust_name;-------------------------------------------------------------------/*联结

分解数据为多个表能更有效地存储,更方便地处理,并且具有更大的

可伸缩性。使用特殊的语法,可以联结多个表返回一组输出,

联结在运行时关联表中正确的行。

在联结两个表时,你实际上做的是将第一个表中的每一行

与第二个表中的每一行配对,然后由WHERE语句进行筛选。

两个表的行之间映射的结果又称笛卡尔积。*/

SELECTvend_name, prod_name, prod_priceFROMvendors, productsWHERE vendors.vend_id =products.vend_idORDER BYvend_name, prod_name;SELECTvend_name, prod_name, prod_priceFROMvendors, productsORDER BYvend_name, prod_name;/*目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的

相等测试。这种联结也称为内部联结。其实,对于这种联结可以使用稍

微不同的语法来明确指定联结的类型。*/#内部联结SELECTvend_name, prod_name, prod_priceFROM vendors INNER JOINproductsON vendors.vend_id =products.vend_id;

#联结多张表成一张大表SELECTprod_name, vend_name, prod_price, quantityFROMorderitems, products, vendorsWHERE products.vend_id =vendors.vend_idAND orderitems.prod_id =products.prod_idAND order_num = 20005;

#注意，联结表是性能低下的原因之一。-------------------------------------------------------------

#使用表别名SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')ASvend_titleFROMvendorsORDER BYvend_name;SELECTcust_name, cust_contactFROM customers AS c, orders AS o, orderitems ASoiWHERE c.cust_id =o.cust_idAND oi.order_num =o.order_numAND prod_id = 'TNT2';

#自联结/*当需要在一张表进行两次查询才能查找到正确的字段时，可以采用自联结。

如在一张商品表中寻找某样产品的产商下的所有产品。*/

SELECTprod_id, prod_nameFROMproductsWHERE vend_id = (SELECTvend_idFROMproductsWHERE prod_id = 'DTNTR');SELECTp1.prod_id, p1.prod_nameFROM products AS p1, products ASp2WHERE p1.vend_id =p2.vend_idAND p2.prod_id = 'DTNTR';

#外部联结/*联结没有匹配到的行*/

SELECTcustomers.cust_id, orders.order_numFROM customers LEFT OUTER JOINordersON customers.cust_id =orders.cust_id;SELECTcustomers.cust_id, orders.order_numFROM customers RIGHT OUTER JOINordersON orders.cust_id =customers.cust_id;

#使用带聚集函数的联结SELECTcustomers.cust_name,

customers.cust_id,COUNT(orders.order_num) ASnum_ordFROM customers INNER JOINordersON customers.cust_id =orders.cust_idGROUP BYcustomers.cust_id;SELECTcustomers.cust_name,

customers.cust_id,COUNT(orders.order_num) ASnum_ordFROM customers LEFT OUTER JOINordersON customers.cust_id =orders.cust_idGROUP BYcustomers.cust_id;

#组合查询/*多数SQL查询都只包含从一个或多个表中返回数据的单条 SELECT 语

句。MySQL也允许执行多个查询(多条 SELECT 语句)

,并将结果作为单个查询结果集返回。

这 些 组 合 查 询 通 常 称 为 并 ( union ) 或 复 合 查 询

(compound query).*/

SELECTvend_id, prod_id, prod_priceFROMproductsWHERE prod_price <= 5;SELECTvend_id, prod_id, prod_priceFROMproductsWHERE vend_id IN (1001, 1002);>>>>>>>>>>>>>>>>>>>>>

SELECTvend_id, prod_id, prod_priceFROMproductsWHERE prod_price <= 5

UNION

SELECTvend_id, prod_id, prod_priceFROMproductsWHERE vend_id IN (1001, 1002);#UNION默认会去除重复的行，如果想全部显示出来则使用SELECTvend_id, prod_id, prod_priceFROMproductsWHERE prod_price <= 5

UNION ALL

SELECTvend_id, prod_id, prod_priceFROMproductsWHERE vend_id IN (1001, 1002);--------------------------------------------------------------/*并非所有的引擎都支持全文本搜索，选择引擎时需注意。

LIKE通配符搜索和正则表达式查找存在几个问题：

性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表

中所有行(而且这些搜索极少使用表索引)。

因此,由于被搜索行数不断增加,这些搜索可能非常耗时。

明确控制——使用通配符和正则表达式匹配,很难(而且并不总

是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必

须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的

情况下才可以匹配或者才可以不匹配。

智能化的结果——虽然基于通配符和正则表达式的搜索提供了非

常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。

例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分

包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配

来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但

包含其他相关词的行。*/#在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理

#每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进

#行。

#为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改

#变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有

#的索引和重新索引。

#启动全文本搜索支持CREATE TABLEproductnotes

(

note_idINT NOT NULLAUTO_INCREMENT,

prod_idCHAR(10) NOT NULL,

note_data DATENOT NULL,

note_textTEXT NULL,PRIMARY KEY(note_id),

FULLTEXT(note_text),

) ENGINE=MyISM;

#这里FULLTEXT索引单个列。

#不要在导入数据的时候使用FULLTEXT索引，因为更新索引需要时间。

#应该首先导入所有数据，然后再修改表，定义FULLTEXT。

#在索引之后,使用两个函数 Match() 和 Against() 执行全文本搜索,

#其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。SELECTnote_textFROMproductnotesWHERE Match(note_text) Against('rabbit');

#全文本搜索会对查询结果进行排序。SELECTnote_text,

Match(note_text) Against('rabbit') ASrankFROMproductnotes;

#布尔文本搜索是全文本搜索的一种选择模式。------------------------------------------------------------------

#数据插入有以下几个方式

#插入完整的行INSERT INTOcustomersVALUES(NULL,'Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL);

#插入多行INSERT INTOcustomers(cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA'),

('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

#插入某些查询的结果INSERT INTOcustomers(cust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)SELECTcust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_countryFROMcustnew;/*提高整体性能：

数据库经常被多个客户访问,对处理什么请求以及用什么次序处理

进行管理是MySQL的任务。

INSERT操作可能很耗时(特别是有很多索引需要更新时),而且它可能

降低等待处理的 SELECT 语句的性能。

如果数据检索是最重要的(通常是这样),则你可以通过在

INSERT 和 INTO 之间添加关键字 LOW_PRIORITY ,指示MySQL

降低 INSERT 语句的优先级。*/

INSERT LOW_PRIORITY INTO

UPDATELOW_PRIORITYDELETELOW_PRIORITY

#更新操作UPDATEcustomersSET cust_name = 'The Fudds',

cust_email= 'elmer@fudd.com'

WHERE cust_id = 10004;

#删除操作DELETE FROMcustomersWHERE cust_id = 10006;

#删除一表后重建表TRUNCATE TABLE

-------------------------------------------------------------------

#创建表

#指定默认值，多个主键CREATE TABLEorderitems

(

order_numINT NOT NULL,

order_itemCHAR(10) NOT NULL,

quantityINT NOT NULL DEFAULT 1,

item_priceDECIMAL(8,2) NOT NULL,PRIMARY KEY(order_num, order_item)

) ENGINE=InnoDB;/*与其他DBMS一样, MySQL有一个具体管理和处理数据的内部引擎。

在你使用 CREATE TABLE 语句时,该引擎具体创建表,而在你使用 SELECT

语句或进行其他数据库处理时,该引擎在内部处理你的请求。多数时候,

此引擎都隐藏在DBMS内,不需要过多关注它。不同的是mysql打包了多种

引擎。

 InnoDB 是一个可靠的事务处理引擎(参见第26章)

,它不支持全文

本搜索;

 MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)

中,速度很快(特别适合于临时表)

;

 MyISAM 是一个性能极高的引擎,它支持全文本搜索(参见第18章)

,但不支持事务处理。*/#注意：

#混用引擎类型有一个大缺陷。外键不能跨引擎,即使用一

#个引擎的表不能引用具有使用不同引擎的表的外键。------------------------------------------------------------------

#更新表结构,通常修改表结构是错误的，因为可能影响多处接口。ALTER TABLEvendorsADD vend_phone CHAR(20);ALTER TABLEvendorsDROPCIOLUMN vend_phone;

#ALTER TABLE一种常见的用途就是定义外键。ALTER TABLEorderitemsADD CONSTRAINTfk_orderitems_ordersFOREIGN KEY (order_num) REFERENCESorders (order_num);

#删除表DROP TABLEcustomers;

#重命名

RENAMETABLE backup_customers TOcustomers,

backup_vendorsTOvendors,

backup_productsTOproducts;---------------------------------------------------------------------/*视图

视图是虚拟的表，本身并不包含任何列或数据，它包含的是SQL查询。

 重用SQL语句。

 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必

知道它的基本查询细节。

 使用表的组成部分而不是整个表。

 保护数据。可以给用户授予表的特定部分的访问权限而不是整个

表的访问权限。

 更改数据格式和表示。视图可返回与底层表的表示和格式不同的

数据。

简单地认为视图就是包装SQL查询语句的包，每次执行都会调用相应的

查询语句块。*/

CREATE VIEW productcustomers AS

SELECTcust_name, cust_contact, prod_idFROMcustomers, orders, orderitemsWHERE customers.cust_id =orders.cust_idAND orderitems.order_num =orders.order_num;SELECTcust_name, cust_contactFROMproductcustomersWHERE prod_id = 'TNT2';

#格式化检索出的数据SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country),')')ASvend_titleFROMvendorsORDER BYvend_name;CREATE VIEW vendorlocations AS

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country),')')ASvend_titleFROMvendorsORDER BYvend_name;

#用视图过滤不想要的视图CREATE VIEW customeremaillist AS

SELECTcust_id, cust_name, cust_emailFROMcustomersWHERE cust_email IS NOT NULL;

#使用视图与计算字段SELECTprod_id,

quantity,

item_price,

quantity*item_price ASexpanded_priceFROMorderitemsWHERE order_num = 20005;----------------------------------------------------------------

/*存储过程用于将SQL操作绑定成一个执行的最小子集，要么一起成功，

要么一起失败。*/#创建存储过程CREATE PROCEDUREproductpricing()BEGIN

SELECT Avg(prod_price) ASpriceaverageFROMproducts;END;

#如果命令行实用程序要解释存储过程自身内的 ; 字符,

#则它们最终不会成为存储过程的成分,这会使存储过程中的SQL出现句法

#错误。

#使用DELIMITER 来临时标记命令行实行程序的语句分隔符

DELIMITER//

CREATE PROCEDUREproductpricing()BEGIN

SELECT Avg(prod_price) ASpriceaverageFROMproducts;END //DELIMITER ;

#调用存储过程

CALL productpricing();

#删除储存过程DROP PROCEDUREproductpricing;

#带变量的存储过程

#一般存储过程是不返回结果的，而是把结果存储至变量之中。

#OUT参数用于存储结果

#IN参数用于传递参数CREATE PROCEDUREproductpricing(

OUT plDECIMAL(8,2),

OUT phDECIMAL(8,2),

OUT paDECIMAL(8,2)

)BEGIN

SELECT Min(prod_price)INTOplFROMproducts;SELECT Max(prod_price)INTOphFROMproducts;SELECT Avg(prod_price)INTOpaFROMproducts;END;

CALL productpricing(@pricelow,@pricehigh,@priceaverage);SELECT @pricelow,@pricehigh,@priceaverage;----------------------------------------------------------------

CREATE PROCEDUREordertotal(IN onumber INT,

OUT ototalDECIMAL(8,2)

)BEGIN

SELECT Sum(item_price *quantity)FROMorderitemsWHERE order_num =onumberINTOototal;END;

CALL ordertotal(20005, @total);----------------------------------------------------------------

#存储过程一般会添加业务规则

#下面一个存储过程展示了下面几个事物

#获取订单合计

#把营业税有条件加入合计中

#返回合计

DELIMITER//#Name : ordertotal

#Parameters: onumber= order number# taxable= 0 if not taxable, 1 iftaxable

# ototal= ordertotal variableCREATE PROCEDUREordertotal(IN onumber INT,INtaxable BOOLEAN,

OUT ototalDECIMAL(8,2)

)COMMENT'Obtain order total,optionally adding tax.'

BEGIN#Declare variable fortotalDECLARE total DECIMAL(8,2);

#Declaretax percentageDECLARE taxrate INT DEFAULT 6;

#Get theordertotalSELECT Sum(item_price*quantity)FROMorderitemsWHERE order_num =onumberINTOtotal;

#Isthis taxable?IF taxable THEN#Yes,soadd taxrate tothe totalSELECT total+(total/100*taxrate) INTOtotal;END IF;

#And finally,save toout variableSELECT total INTOototal;END //DELIMITER ;

CALL ordertotal(20005, 0, @total);SELECT @total;

CALL ordertotal(20006, 0, @total);SELECT @total;

#显示用来创建一个存储过程的CREATE语句

SHOWCREATE PROCEDUREordertotal;

#获取何时由谁创建等详细信息

SHOWPROCEDURE STATUS LIKE 'ordertotal';-----------------------------------------------------------------/*游标(cursor)是一个存储在Mysql服务器上的数据库查询，它不是一条

SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后,

应用程序可以根据需要滚动或浏览其中的数据。

注意，mysql的游标只能作用于存储过程(和函数)。*/

CREATE PROCEDUREprocessorders()BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROMorders;OPENordernumbers;CLOSEordernumbers;END;

#这个过程创建游标，打开和关闭游标CREATE PROCEDUREprocessorders()BEGIN

DECLARE o INT;

#定义游标DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROMorders;

#打开游标OPENordernumbers;

#抓取数据FETCH ordernumbers INTOo;

#关闭游标CLOSEordernumbers;END;

#以下是游标的实际应用：CREATE PROCEDUREprocessorders()BEGIN

DECLARE done BOOLEAN DEFAULT 0;DECLARE o INT;DECLARE t DECIMAL(8,2);DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROMorders;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;/*这条语句定义了一个 CONTINUE HANDLER ,它是在条件出现时被执行

的代码。这里,它指出当 SQLSTATE '02000' 出现时, SET done=1。

SQLSTATE'02000' 是一个未找到条件,当 REPEAT 由于没有更多的行

供循环不能继续时,出现这个条件。*/

CREATE TABLE IF NOT EXISTSordertotals

(order_numINT, total DECIMAL(8,2));OPENordernumbers;

REPEATFETCH ordernumbers INTOo;

CALL ordertotal(o,1, t);INSERT INTOordertotals(order_num, total)VALUES(o,t);

UNTIL doneENDREPEAT;CLOSEordernumbers;END;SELECT * FROMordertotals;/*输出结果

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20009 | 40.78 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20008 | 132.50 |

+-----------+---------+*/

/*触发器是Mysql响应以下任意语句而自动执行的一条Mysql语句或者是

位于BEGIN和END之间的一组语句。

DELETE

INSERT

UPDATE*/#创建触发器CREATE TRIGGER newproduct AFTER INSERT ONproductsFOR EACH ROW SELECT 'Product added' INTO @temp;

#当对products表进行插入时触发，对每一行插入后输出Product added.

#每个表最多支持6个触发器(每条INSERT,UPDATE和DELETE的之前和之后)。

#单一触发器不能与多个事件或多张表关联。

#删除触发器DROP TRIGGERnewproduct;/*INSERT触发器

 在 INSERT 触发器代码内,可引用一个名为 NEW 的虚拟表,访问被

插入的行;

 在 BEFORE INSERT 触发器中, NEW 中的值也可以被更新(允许更改

被插入的值)

;

 对于 AUTO_INCREMENT 列, NEW 在 INSERT 执行之前包含 0 ,在 INSERT

执行之后包含新的自动生成值。*/

CREATE TRIGGER neworder AFTER INSERT ONordersFOR EACH ROW SELECT NEW.order_num INTO @temp;

#插入数据至orders表后输出插入数据的order_num列属性。INSERT INTOorders(order_date, cust_id)VALUES(NOW(), 10001);/*DELETE触发器

 在 DELETE 触发器代码内,你可以引用一个名为 OLD 的虚拟表,访

问被删除的行;

 OLD 中的值全都是只读的,不能更新。*/#删除

#在旧订单数据删除前插入新表，保存数据。CREATE TRIGGER deleteorder BEFORE DELETE ONordersFOREACH ROWBEGIN

INSERT INTOarchive_orders(order_num, order_date, cust_id)VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);END;/*UPDATE触发器

 在 UPDATE 触发器代码中,你可以引用一个名为 OLD 的虚拟表访问

以前( UPDATE 语句前)的值,引用一个名为 NEW 的虚拟表访问新

更新的值;

 在 BEFORE UPDATE 触发器中, NEW 中的值可能也被更新(允许更改

将要用于 UPDATE 语句中的值);

 OLD 中的值全都是只读的,不能更新。*/#更改NEW的值CREATE TRIGGER updatevendor BEFORE UPDATE ONvendorsFOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);-------------------------------------------------------------------------/*事务管理——要么一起完成，要么一起失败；

理清几个概念

 事务( transaction )指一组SQL语句;

 回退( rollback )指撤销指定SQL语句的过程;

 提交( commit )指将未存储的SQL语句结果写入数据库表;

 保留点( savepoint )指事务处理中设置的临时占位符(place-

holder),你可以对它发布回退(与回退整个事务处理不同)。*/

SELECT * FROMordertotals;

STARTTRANSACTION;DELETE FROMordertotals;SELECT * FROMordertotals;ROLLBACK;SELECT * FROMordertotals;

#ROLLBACK只能在一个事务处理内使用，也就是在一条

#START TRANSACTION之后

#一般的MySQL语句都是直接针对数据库表执行和编写的。这就是

#所谓的隐含提交(implicitcommit),即提交(写或保存)操作是自动

#进行的。但在事务处理块中，提交需要进行明确的提交。

STARTTRANSACTION;DELETE FROM orderitems WHERE order_num = 20010;DELETE FROM orders WHERE order_num = 20010;COMMIT;

#复杂的事务处理可能需要部分提交或回退，可以用保留点来进行操作。

SAVEPOINT delete1;ROLLBACK TOdelete1;

RELEASE SAVEPOINT;

#让事务不自动提交SET autocommit = 0;---------------------------------------------------------------------/*全球化和本地化

 字符集为字母和符号的集合;

 编码为某个字符集成员的内部表示;

 校对为规定字符如何比较的指令*/#使用何种字符集和校对的决定在服务器、数据库和表级进行。

SHOWCHARACTER SET;

#查看所支持校对的完整列表,_ci不区分大小写,_cs区分大小写

SHOW COLLATION;

#表级

#为了给表指定字符集和校对,可使用带子句的CREATE TABLE#很少会有服务器和数据库级别的字符集和校对的设置。CREATE TABLEmytable(

columnn1INT,

columnn2VARCHAR(10)

)DEFAULT CHARACTER SEThebrew

COLLATE hebrew_general_ci;

#查看table的字符集和校对

SHOWTABLE STATUS FROM GoodtoKnowMysql LIKE '%order%';----------------------------------------------------------------------/*安全管理

访问控制——给用户提供他们所需的访问权,且仅提供他们所需的访问权。

Mysql Administrator 提供一个图形用户界面，可用来管理用户及

账号权限。

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

在现实世界

的日常工作中,决不能使用 root 。应该创建一系列的账号,有的用于管

理,有的供用户使用,有的供开发人员使用,等等。

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

#创建用户CREATE USER Rudolph_Browne IDENTIFIED BY '**********';

#重命名用户

RENAMEUSER Rudolph_Browne TOHarry;

#删除用户DROP USERHarry;

#设置访问权限

#查看用户权限

SHOW GRANTSFORRudolph_Browne;

#授予权限——GRANT

GRANT SELECT ON GoodtoKnowMysql.* TORudolph_Browne;

#允许用户在GoodtoKnowMysql数据库中的所有表进行SELECT操作；GRANT ALL ON GoodtoKnowMysql.* TORudolph_Browne;

#授予所有权限

#撤销权限REVOKE SELECT ON GoodtoKnowMysql.* FROMRudolph_Browne;/*GRANT 和 REVOKE 可在几个层次上控制访问权限:

 整个服务器,使用 GRANT ALL 和 REVOKE ALL;

 整个数据库,使用 ON database.*;

 特定的表,使用 ON database.table;

 特定的列;

 特定的存储过程。*/

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#权限表详情请参见手册或者Mysql必知必会 p213<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

#更改用户口令SET PASSWORD FOR Rudolph_Browne = Password('*********');

#更改自己口令SET PASSWORD = Password('**********');---------------------------------------------------------------------

#数据库维护/*备份数据

像所有数据一样,MySQL的数据也必须经常备份。由于MySQL数据

库是基于磁盘的文件,普通的备份系统和例程就能备份MySQL的数据。

但是,由于这些文件总是处于打开和使用状态,普通的文件副本备份不

一定总是有效。

下面列出这个问题的可能解决方案。

 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部

文件。在进行常规备份前这个实用程序应该正常运行,以便能正

确地备份转储文件。

 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据

(并非所有数据库引擎都支持这个实用程序)

。

 可以使用MySQL的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所

有数据到某个外部文件。这两条语句都接受将要创建的系统文件

名,此系统文件必须不存在,否则会出错。数据可以用 RESTORE

TABLE 来复原。*/#首先刷新未写数据 为了保证所有数据被写到磁盘(包括索引

#数据),可能需要在进行备份前使用 FLUSH TABLES 语句。

#导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

#导出数据库中指定表

mysqldump -u 用户名 -p 数据库名 --tables table_name1 table_name2 table_name3 > db_script.sql