MySQL⾼效编程
第1部分 MySQL基础篇
1、对于企业⽽⾔，选择MySQL数据库的两⼤原因：1）MySQL是开源关系型数据库产品，使⽤普及率⾼；2）性能出⾊，运⾏速度块。MySQL有免费和收费两种类型的产品。
2、mysql登录：cd 打开mysql的bin⽬录，执⾏“mysql -u root -p”回车，然后要求输⼊密码Enter password，正确输⼊密码后，回车，即可登录成功！
3、常⽤数据库查询命令：
show databases; 查看数据库服务器上的全部数据库
create database test; 创建⼀个名称为test的数据库
use test；使⽤test数据库
show tables；查看当前数据库中所有表
创建表：
create table 表名（
属性名数据类型列选项,
……
）
其中，列选项可以为AUTO_INCREMENT（⾃增），DEFAULT '默认值'，INDEX（定义为索引）,[NOT]NULL（允许/禁⽌
NULL），PRIMARY KEY（定义为主键），UNIQUE（定义为唯⼀性），CHECK（定义可以输⼊的值的范围/选项）。
alter table 表名 modify 列名数据类型；修改表中列的定义
alter table 表名 add 列名数据类型；追加新列
alter table 表名 drop 列名；删除列
DESC 表名；显⽰表的结构
DROP TABLE 表名；删除表
Insert into 表名(列名1，列名2,...) values (数据1，数据2,...)；向表⾥插⼊数据
Select 列名1，列名2,... from 表名[条件表达式等]；显⽰表中的数据
Update 表名 set 列名1=值1，列名1=值1，.... where 条件表达式；更新标志的记录
Delete from 表名 where 条件表达式；删除表中的记录
4、数据检索
①推荐明确指定列名：使⽤[*]来⽆条件地获得所有列数据，第⼀，获取到不需要的列就会浪费很多内
存；第⼆，获得的数据会按表定义的列的顺序，如果修改了表列的顺序，对应的程序也必须进⾏修改。
②条件检索：必须使⽤WHERE语句。⽐较运算符有：=、<、>、<=、>=、<>（不等于）、IS[NOT] NULL（是否为空）、[NOT]LIKE（指定⽬标⼀致）、[NOT] BETWEEN（指定范围）、[NOT] IN（在指定候补值内）。
③模糊检索：select name from customer where name like '李%';
④多条件组合查询：使⽤and(并)、or（或）连接。理论运算符的优先顺序：NOT->AND->OR。
⑤结果排序：使⽤order by进⾏数据排序，可以指定ASC（升序）或DESC（降序），默认是ASC。
如：select name,age from customer where age>21 order by age DESC;
⑥取得指定数间的记录：LIMIT；
limit 2：从起始位置开始取出两件；
limit 1,2：从1开始取出两件。
⑦数据分组：GROUP BY，通常GROUP BY语句与统计函数⼀起使⽤。主要统计函数：AVG(列名)平均值、COUNT(列名)件数、MAX(列名)最⼤值、MIN(列名)最⼩值、SUM(列名)合计值。
⑧列的别名：select sex,COUNT(mid) as cnt from customer group by sex；其中as可以省略。
5、运算符与数据库函数
①运算符：+、-、*、/、DIV（除法返回结果的整数部分）、%（求余）、AND、OR。
②部分数据库函数
LENGTH函数：返回字符串的字节数
FLOOR/GEILING/TRUNCATE函数：⽤于⼩数四舍五⼊处理
DATE_ADD函数：对⽇期date进⾏指定值的加算处理
NOW()当前时间、CURDATE()当前⽇期、CURTIME()当前时刻、EXTRACT(YEAR_MONTH '2013-11-20 21:02:00')从给定的⽇期/时刻中取得任意元素（如年、⽉等）
CASE函数：条件判断
select name
case sex
when 0 then '男'
when 1 then '⼥'
else 'OTH'
end as sex
from customer;
6、多表连接查询
①内链接：返回结果集合中仅是符合查询条件和连接条件的记录；
select m.name,o.oid
from member m
inner join ordercord o
on m.id=o.mid
（where/order by等语句）
②外连接：返回的查询结果集中不仅是符合连接条件的⾏，⽽且还包括左表（左外连接）或右表（右外连接）中所有数据⾏；
左外连接：
select 列名1 from 表1
LEFT OUTER JOIN 表2
ON 表1.外键=表2.主键[WHERE/ORDER BY语句等]
右外连接：
select 列名1 from 表1
RIGHT OUTER JOIN 表2
ON 表1.外键=表2.主键[WHERE/ORDER BY语句等]
3个或3个以上表间连接，从内往外⼀层层的连接。
③⼦查询：先返回⼦查询的结果集，再返回主查询结果集。
第2部分 MySQL⾼级应⽤篇
7、MySQL的功能可以分为两部分：外层部分主要完成与客户端的连接以及事前调查SQL语句的内容的功能；⽽内层部分就是所谓的存储引擎部分，它负责接受外层的数据操作指⽰，完成实际的数据输⼊输出以及⽂件操作⼯作。MySQL提供的存储引擎有：MyISAM（默认的⾼速引擎，不⽀持事务处理）、InnoDB（⽀持⾏锁定以及事务处理，⽐MyISAM的处理速度稍慢）、ISAM等。
8、查看表中使⽤的引擎：show create table 表名 [\G];
修改表使⽤的引擎：alter table 表名 engine=新引擎;
其中，\G在MySQL监视框中显⽰出信息有序。
====>>事务
9、事务是指作为单个业务逻辑⼯作单元执⾏的⼀系列操作，要么全部成功，要么全部失败。MySQL提供的3个重要事务处理命令：BEGIN（声明事务处理开始）、COMMIT（提交整个事务）、ROLLBACK（回滚到事务开始的状态）。
10、MySQL默认⾃动提交事务。当然也可以通过SET AUTOCOMMIT=0；将⾃动提交功能置为OFF；也可以通过SET AUTOCOMMIT=1；将⾃动提交功能置为ON。
11、部分回滚：
定义保存点：savepoint 保存点名；
回滚到指定的保存点：rollback to savepoint 保存点名；
12、事务处理的利⽤范围，以下⼏条SQL命令，执⾏后将被⾃动提交，是在事务处理可以利⽤的范围之外：DROP DATABASE；DROP TABLE；DROP；ALTER TABLE；
13、多⽤户同时操作同⼀个数据库时，需要对其进⾏并发控制，MySQL通过锁来实现。锁分为共享锁和排他锁，共享锁是当⽤户参照数据时，将对象数据变为只读形式的锁定；排他锁是使⽤INSERT/UPDATE/DELETE命令对数据进⾏更新时，其他进程（或事务）⼀律不能对读取该数据。
14、锁定对象的⼤⼩，通常被称为锁定的粒度。⽀持的粒度随着数据库不同⽽有所差异，⼀般有3种锁定粒度：记录（⾏）、表、数据库；
15、锁定的粒度越⼩，运⾏性越⾼，但并不是越⼩的粒度越好，锁定的数⽬越多，消耗的服务器资源也越多。数据库中⾏单位粒度的锁定⼤量发⽣时，数据库有将这些锁定的粒度⾃动向上提升的机制，通常称为锁定提升。MySQL只⽀持⾏/表粒度，不⽀持锁定提升功能。
16、事务处理的隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZEABLE。
17、事务处理的机制简单的说就是留下更新⽇志，数据库会根据这些⽇志信息，在必要时将就数据取回，或在发⽣错误是将数据恢复到原先的状态。与事务处理相关的⽇志可以分为两个类型：UNDO和REDO。
=====>>索引
18、索引就是表中所有记录的搜索引导，索引的经过排序的。有索引后，先查索引再根据索引查相应的记录。
19、⼤多数数据库中⽤B树（平衡树）的结果来保存索引的。
20、创建索引：create [unique] index 索引名 on 表名（列名,……）;
查看表中所有的索引信息：show index from 表名 [\G]；
删除索引：drop index 索引名 on 表名；
确认索引的使⽤情况（优劣）：explain 调查对象 select 语句；
如：explain select * from employee where name='wang' \G;
复合索引：create index idx_employee_name on employee(name,sex);
唯⼀索引：create unique index idx_employee_name on employee(name);
索引名建议[idx_]的形式开头。
21、不适合使⽤索引的场合：
①模糊查询要求后⽅⼀致或部分⼀致检索；如：select * from employee where name like '%w%'或'%w'；是不适合的。
②使⽤了IS NOT NULL、<>⽐较运算符的场合；如：select * from employee where name is not null；是不适合的。
③对列使⽤了运算/函数的场合；
④复合索引的第⼀列没被包含在where条件语句中的场合。如：select * from employee where name='wang' or age='1'；是不适合的。
======>>视图
22、视图是伪表，视图本事不包含如何数据的，将多个物理表的数据通过视图动态地组织在⼀起，⽤户可以像使⽤普通物理表那样使⽤它。
23、视图具有以下特性：①可以公开表中特定的⾏和列；②简化复杂的SQL查询；③可以限制可插⼊/更新的值范围。
24、创建视图：create view 视图名(列名,...) as select 语句 [with check point]；
删除视图：drop view 视图名;
查看视图所有列信息：show fields from 视图名;
视图名建议[v_]的形式开头。
25、不能进⾏插⼊/更新/删除操作的场合：
①视图列中含有统计函数
②视图定义使⽤了GROUP BY/HAVING语句，DISTINCT语句，UNION语句的情况
注：HAVING是对group by设置查询条件；distinct是要求查询结果没有重复项。
③视图定义时使⽤了⼦查询
④跨越多个表进⾏数据的变更操作
26、创建视图时使⽤[with check option]命令，将不能插⼊或更新不符合视图的检索条件的数据。
如：
create view v_product300up
as
select * from product where price >=300
with check option;
此时，变更操作要求price⼤于或等于300才能进⾏，否则就会出现[CHECK OPTION failed……]的错误信息。
=====>>存储过程
27、存储过程是数据库中保存的⼀系列SQL命令的集合，这些SQL命令通常并⾮简单的组合在⼀起，还可以使⽤各种条件判断、循环控制等，来实现简单的SQL命令不能实现的负责功能。
28、使⽤存储过程可以有这些好处：
①提⾼执⾏性能
②可减轻⽹络负担
③可防⽌对表的直接访问，提⾼安全性
④可将数据库的处理⿊匣⼦化
29、定义存储过程：
ceate procedure 存储过程名（参数的种类1 参数1 参数类型1 [，参数的种类2，参数2，参数类型2…]）
begin
处理内容
end
注：存储过程的参数可以分为输⼊参数（接受调⽤⽅的数据），输出参数（向调⽤⽅返回处理结果）。参数的种类可以是IN、OUT、INOUT 其中之⼀。存储过程名前加上[sp_]的开头。
如：
delimiter //
create procedure sp_search_customer(IN p_name varchar(20) )
begin
if p_name is null or p_name='' then
select * from customer;
else
select * from customer where name like p_name;
end if;
end
delimiter ;
注：上⾯delimiter命令改变分隔符为//，因为在存储过中使⽤到";"。
30、存储过程中可使⽤的控制语句：
①if...d if
②case when 值1 then 执⾏命令 when 值2 then 执⾏命令 else 执⾏名 end case
③循环控制（后置判断）：repeat 直⾄条件表达式为true时才执⾏的命令 until 条件表达式 end repeat
④循环控制（前置判断）：while 条件表达式 do 系列命令 end while
31、查看存储过程是否存在：show procedure status [\G];
查看存储过程信息：show create procedure 存储过程名;
删除存储过程：drop procedure 存储过程名;
执⾏存储过程名：call 存储过程名（参数,……）；
32、定义存储过程可以声明局部变量：declare 变量名数据类型[初始值]；
赋值给变量：set 变量名=值；
=====>>存储函数
33、存储函数是保存在数据库中的函数（Function）。所谓函数就是按照事先决定的规则进⾏处理，然后将结果返回的单功能机制。
34、定义存储函数：
create function 函数名（
参数1 数据类型1
[，数据2 数据类型2...]
)returns 返回值类型
begin
任意系列SQL语句
return 返回值；
end
注：①参数只有输⼊型IN
mysql删除重复的数据保留一条
②向调⽤返回结果值
③存储函数给定[fn_]的前缀
35、查看全部已创建的存储函数：show function status;
查看存储函数的信息：show create function 函数名 [\G]；
存储函数调⽤：select 函数名(参数);
如：
发表评论