MySQL必知必会

一.使用高级联结
1.自联结
使用自联结而不用子查询, 通常自联结用来代替从相同表中检索数据时使用的子查询, 有时候自联结比处理子查询快得多
2.自然联结
3.外部联结(inner join, left outer join以及right outer join)
tip:
注意使用的联结类型, 一般使用内部联结
使用正确的联结条件
应该总提出联机条件(否则得出笛卡尔积)
一个联结可以包含多个表, 甚至每个联结可以采用不同的联结类型

二.全文本搜索
MyISAM支持全文本搜索, Innodb不支持
基于正则表达式的基于文本的搜索存在以下几个限制:
1)性能-通配符和正则表达式匹配通常要求MySQL尝试匹配表中的所有行, 随着行的增加搜索非常耗时
2)明确控制-使用通配符或者正则表达式很难明确控制匹配什么不匹配什么, 例如匹配第二个词的情况是建立在第一个词匹配的情况下
3)智能化的结果-不能按照匹配的程度进行排序输出, 还有一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行

1.进行全文本搜索
使用Match()和Against()执行全文搜索
其中Match()指定被搜索的列 , Against()指定使用的搜索表达式
eg:select note_text from productnotes
where Match(note_text) Against(‘rabbit’);

2.使用查询扩展
eg:select note_text from productnotes
where Match(note_text) Against(‘rabbit’ with query expansion);

3.布尔文本搜索(即使没有Full Text也可使用, 并且不按等级降序排序返回的)
eg:select note_text from productnotes
where Match(note_text) Against(‘rabbit -rope*’ in boolean mode);
匹配包含heavy但不包含以rope开始的词的行

三.存储过程
1.为什么要使用存储过程
1)通过把处理封装在容易使用使用的单元中, 简化复杂操作
2)不要求反复建立一系列处理步骤, 保证了数据的完整性
3)简化对变动的管理
4)提高性能

2.使用存储过程
eg: call productpricing(@pricelow,
@pricehigh,
@priceaverage)
执行名为productpricing的存储过程, 它计算并返回产品最低,最高和平均价格

3.创建存储过程
eg: create procedure productpricing()
begin
select Avg(prod_price) as priceaverage
from products;
end;

4.删除存储过程
eg: drop procedure productpricing;
tip:注意删除存储过程的时候productpricing后面没有()

四.使用游标
游标的作用:需要在检索出来的行前进或者后退一行或者多行, 这就是使用游标的原因
1.使用游标步骤
1)必须声明它, 这个过程没有检索数据, 只是定义要使用的select语句
2)一旦声明, 必须打开游标以供使用
3)对于填有数据的游标, 根据需要取出检索各行
4)在结束游标使用的时候, 必须关闭游标

2.创建游标
eg: create procedure processorders()
begin
declare ordernumbers CURSOR
for
select order_num from orders;
end;
这里只是简单的定义命名游标, 在存储过程处理完成之后, 游标就消失了

3.打开和关闭游标
用open cursor 语句来打开
输入 : open ordernumbers
分析 : 在处理open语句时执行查询, 存储检索出的数据以供浏览和滚动

用close ordernumbers 关闭游标
分析 : close释放游标使用的所有内部内存和资源, 因此不需要的时候需要关闭

4.使用游标数据
eg :
create procedure processorders()
begin
–declare local variable
declare o int;

--declare the cursor
declare ordernumbers cursor 
for
select order_num from oreders;

--open the cursor
open ordernumbers;

--get order number
fetch ordernumbers into o;

--close the cursor
close ordernumbers;
end;

分析 : 其中fetch用来检索当前行的order_num列到一个名为o的局部变量中, 对检索出来的数据不做任何处理

五.触发器
作用 : 需要某个语句在某个事件发生的时候自动执行
1.创建触发器
需要4个信息
1)唯一的触发器名
2)触发器关联的表
3)触发器应该响应的活动
4)触发器何时执行
eg: create trigger newproduct after insert on products
for each row select ‘Product added’;
分析: 这里的在每次插入products之后显示products added
tip : 只有表才支持触发器, 视图不支持触发器(临时表也不支持)
每个表每个时间只支持一个触发器, 因此每个表最多支持6个触发器(update, insert, delete的之前之后), 单一触发器不支持多表或者多事件关联

2.删除触发器
eg : drop trigger newproduct

3.使用触发器
1)Insert触发器
insert触发器在语句执行之前之后, 需要知道的
a.在insert触发器代码内, 可引用一个名为NEW的虚表访问插入的行
b.在before insert触发器中, NEW中的值也可以被更新
c.对于AUTO_INCREMENT, NEW在insert之前包含0, 执行之后包含新的自动生成值

2)delete触发器
delete触发器在使用之前需要知道以下两点
a.在delete触发器代码内, 你可以引用一个OLD的虚拟表来访问被删除的行
b.OLD中的值全部都是只读, 不能更新
如果before delete触发器执行失败, 则delete本身将被放弃

3)update触发器
使用之前需要知道的几点:
a.在update触发器代码中,你可以引用一个OLD的虚拟表访问以前的值, 引用一个NEW的虚拟表 访问更新的值
b.在before update触发器, NEW中的值可能被更新
c.OLD中的值全都是只读的, 不能更新

六, 管理事务
MyISAM不支持事务管理, Innodb支持事务管理
作用: 用于维护数据库的完整性, 保证成批的MySQL操作要么完全被执行, 要么完全不执行

1.控制事务管理
使用 start transaction标记事务开始
1)使用rollback
使用rollback来回退, 而且只有delete, update, insert这些语句, 回退select无意义, 也不能回退create, drop

2)使用commit
指明对事务明确的提交

3)使用保留点
简单事务才是直接rollback或者commit, 复杂事务可能需要部分撤退或者部分提交, 这时候就需要savepoint保留点(占位符)的存在了
eg :
savepoint delete1;
rollback to delete1;
分析 : 指定回退具体唯一的保留点
tip: 保留点在事务提交之后被自动释放, 也可以使用release savepoint明确释放保留点

4)更改默认的提交行为
eg : set autocommit=0;
分析 : 设置不自动提交
tip : 这里的不自动提交指的是连接而不是服务器

七. 数据库引擎
1.InnoDB是一个可靠的事务处理引擎, 但不支持全文本搜索
2.MEMORY在功能上等同于MyISAM, 当由于数据存储在内存中, 速度很快, 特别适合临时表
3.MyISAM是一个性能极高的引擎, 支持全文本搜索, 但不支持事务处理
tip : 外键不能跨引擎

八.关于数据库索引的种类
1.唯一索引, 唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓

2.主键索引, 数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问

3.聚集索引, 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引