MySQL高效编程--学习笔记
第1部分 MySQL基础篇1、对于企业而言，选择MySQL数据库的两大原因：1）MySQL是开源关系型数据库产品，使用普及率高；2）性能出色，运行速度块。MySQL有免费和收费两种类型的产品。
2、mysql登录：cd 打开mysql的bin目录，执行“mysql -u root -p”回车，然后要求输入密码Enter password，正确输入密码后，回车，即可登录成功！
http://img.blog.csdn.net/20131115220505218
3、常用数据库查询命令：
show databases; 查看数据库服务器上的全部数据库
create database test; 创建一个名称为test的数据库
use test;使用test数据库
show tables;查看当前数据库中所有表
创建表：
create table 表名（
属性名数据类型列选项,
……
）
其中，列选项可以为AUTO_INCREMENT（自增），DEFAULT '默认值'，INDEX（定义为索引）,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 NULL（是否为空）、LIKE（指定目标一致）、 BETWEEN（指定范围）、 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.主键
右外连接：
[*] select 列名1 from 表1
[*] RIGHT OUTER JOIN 表2
[*] ON 表1.外键=表2.主键
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。
http://img.blog.csdn.net/20131121090320750
17、事务处理的机制简单的说就是留下更新日志，数据库会根据这些日志信息，在必要时将就数据取回，或在发生错误是将数据恢复到原先的状态。与事务处理相关的日志可以分为两个类型：UNDO和REDO。
=====>>索引
18、索引就是表中所有记录的搜索引导，索引的经过排序的。有索引后，先查找索引再根据索引查找相应的记录。
19、大多数数据库中用B树（平衡树）的结果来保存索引的。
20、创建索引：create index 索引名 on 表名（列名,……）;
查看表中所有的索引信息：show index from 表名 [\G]；
删除索引：drop index 索引名 on 表名；
确认索引的使用情况（优劣）：explain 调查对象 select 语句；
如：explain select * from employee where name='wang' \G;
复合索引：create index> 唯一索引：create unique indexidx_employee_name on employee(name);
索引名建议的形式开头。
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 语句 ；
删除视图：drop view 视图名;
查看视图所有列信息：show fields from 视图名;
视图名建议的形式开头。
25、不能进行插入/更新/删除操作的场合：
①视图列中含有统计函数
②视图定义使用了GROUP BY/HAVING语句，DISTINCT语句，UNION语句的情况
注：HAVING是对group by设置查询条件；distinct是要求查询结果没有重复项。
③视图定义时使用了子查询
④跨越多个表进行数据的变更操作
26、创建视图时使用命令，将不能插入或更新不符合视图的检索条件的数据。
如：
view plaincopyhttps://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] create view v_product300up
[*] as
[*] select * from product where price >=300
[*] with check option;
此时，变更操作要求price大于或等于300才能进行，否则就会出现的错误信息。
=====>>存储过程
27、存储过程是数据库中保存的一系列SQL命令的集合，这些SQL命令通常并非简单的组合在一起，还可以使用各种条件判断、循环控制等，来实现简单的SQL命令不能实现的负责功能。
28、使用存储过程可以有这些好处：
①提高执行性能
②可减轻网络负担
③可防止对表的直接访问，提高安全性
④可将数据库的处理黑匣子化
29、定义存储过程：
ceate procedure 存储过程名（参数的种类1 参数1 参数类型1 [,参数的种类2，参数2，参数类型2…]）
begin
处理内容
end
注：存储过程的参数可以分为输入参数（接受调用方的数据），输出参数（向调用方返回处理结果）。参数的种类可以是IN、OUT、INOUT其中之一。存储过程名前加上的开头。
如：
[*] 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...elseif...else...end 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
②向调用返回结果值
③存储函数给定的前缀
35、查看全部已创建的存储函数：show function status;
查看存储函数的信息：show create function 函数名 [\G]；
存储函数调用：select 函数名(参数);
如：
[*] delimiter //
[*] create function fn_factorial(
[*] f_num int
[*] )returns int
[*] begin
[*] declare f_result int default 1;--定义局部变量并初始化为1
[*] while f_num>1 do
[*] set f_result=f_result*f_num;
[*] set f_num=f_num-1;
[*] end while;
[*] retrun f_result;
[*] delimiter ;
调用存储函数：
select fn_factorial(5), fn_factorial(0);
======>>触发器
36、触发器是在操作数据库时，执行一个动作，而触发了另一个动作。如删除表1的一条记录，在表2中插入一条日志记录。
37、创建触发器：
create trigger 触发器名 发生时刻 事件名
on 表名 for each row
begin
任意系列SQL语句
end
说明：①触发器不是直接与运行的，而是针对具体表的操作事时被调用的；
②决定触发器运行时刻具体是指发生在insert、update、delete等操作的前还是后，用before或after表示。
③for each row 表示以行为单位执行的，为固定值。
确认已创建完成的触发器列表和触发器的信息：show trigger [\G];
删除触发器：drop trigger 触发器名；
[*] delimiter //
[*] create trigger trg_customer_history after delete(
[*] on customer for each row
[*] begin
[*] insert into customer_history(mid,name,birth,sex,updated)
[*] values(OLD.mid,OLD.name,OLD.birth,OLD.sex,NOW());
[*] end;
[*] delimiter ;
=====>>游标
38、游标就是对select语句取出的结果进行一件一件处理的功能。游标可以将多个查询记录进行一件一件的单独的处理。
[*] delimiter //
[*] create proceduce sp_cursor(
[*] OUT p_result TEXT
[*] )
[*] begin
[*] --定义标志变量flag(判断是否所有记录都被取出)
[*] declare flag bit default 0;
[*] --定义存储当前行的部署名的变量tmp
[*] declare tmp varchar(20);
[*] --定义游标取出游标中所有记录时的处理
[*] declare cur cursor for select distinct depart from employee;
[*] --定义取出游标中所有记录是的处理
[*] declare continue handler for not foundset flag=1;
[*] --打开游标
[*] open cur;
[*] --从游标中一行行取出数据
[*] while flag!=1 do
[*] fetch cur into tmp;--将当前行中的内容保存到本地变量中
[*] if flag!=1 then
[*] --将变量tmp以逗号分隔的字符串的形式保存到输出参数p_result中
[*] set p_result=concat_ws(',',p_result);
[*] end if
[*] end while; --标志变量为1后结束循环
[*] --关闭游标
[*] colse cur;
[*] end
[*] delimiter ;
调用游标：call sp_cursor(@p_result);
本文出自http://blog.csdn.net/jbjwpzyl3611421/article/details/17760987
页:
[1]