【MySQL】开发篇

存储引擎

 存储引擎是存储数据，建立索引，更新查询数据等技术的实现方式。存储引擎是基于表的，不是基于库的。MySQL存在多种存储引擎，可根据需要使用相应引擎，或者编写存储引擎。

创建表时如果不指定存储引擎，会使用默认存储引擎，默认存储引擎为InnoDB。

InnoDB

InnoDB的特点是支持事务，外键和行级锁。

InnoDB存储表和索引由两种方式：

（1）使用共享表空间存储，这种方式创建的表结构保存在.frm文件中。

（2）使用多表空间存储，这种方式创建的表的结构保存在.frm文件中，但每个表的数据和索引单独保存在.ibd文件中。

MyISAM

不支持事务和外键，其优势是访问速度快。

 MEMORY

MEMORY存储引擎将表的数据存放在内存中，每个表在磁盘上会有一个.frm的磁盘文件，该文件只存储表的结构，不存储数据，数据都在内存中，这样有利于提高表的效率。MEMORY类型表访问非常快，但数据存放在内存中，并且默认使用hash索引，表中数据易丢失。

MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，MERGE表本身不存储数据，对MERGE类型的表可以进行查询，更新，删除操作，实际上就是对内部的MyISAM表进行操作。

 索引

索引是帮助MySQL高效获取数据的数据结构。

索引的优势：

（1）提高数据检索效率，降低数据的io成本。

（2）通过索引列的数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的劣势：

（1）索引实际也是一张表，也要占用空间

（2）索引在大大提高了查询效率的同时也降低了更新表的速度。

索引结构：

BTREE结构，B+TREE结构，InnoDB中的B+TREE结构。

索引分类：

（1）单值索引：一个索引只包含单个列

（2）唯一索引：索引列的值必须唯一，但允许由多个空值

（3）复合索引：一个索引包含多个列

（4）主键索引：创建表时对主键自动创建

 索引语法

可以在创建表的时候创建索引，也可以后面随时增加索引。

创建索引：

 查看索引：

删除索引：

视图

概述

视图是一种虚拟存在的表，在数据库中并不实际存在，行和列数据均来自定义试图的查询中使用的表，并且是在使用视图时动态生成的。视图就是一条select语句执行后返回的结果集。我们在创建视图时，主要工作就落在创建这条SQL查询语句上。

视图优势在于：简单，安全和数据独立。

视图语句

创建视图：

CREATE VIEW 视图名 AS 查询语句...

 修改视图：

方法一：

alter view 视图名 AS  查询语句;

方法二：

alter view 视图名 AS  查询语句;

删除视图：

drop view 视图名，视图名，...

查看视图：

show tables;//查看视图和表的列表
show create view 视图名;//查询创建视图时的语句
select *from 视图名；//查看视图内容

存储过程和函数

概述

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数有返回值，存储过程没有。

存储过程和函数操作

创建存储过程

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;

调用存储过程

call procedure_name();

查询存储过程

-- 查看test数据库中的所有存储过程
select name from mysql.proc where db='test';

-- 查看存储过程的状态信息
show procedure status;

-- 查看存储过程的创建语句
show create procedure 存储过程名;

删除存储过程

drop procedure 存储过程名;

存储过程语法

变量

变量声明：declare

declare var_name type [default value]

例子：

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 声明变量,赋默认值为0
	select num+10;
	
end ;

call test2();			-- 调用存储过程

变量赋值：set 可以赋值为常量或表达式

set var_name=expr[,var_name=expr]...

例子：

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 给num变量赋值
	select num;
	
end ;

call test3();

也可以通过select...into...赋值：

例子：

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;
	select num;
end ;

call test4();

if条件判断

语法结构：

if 条件1 then 执行1
[elseif 条件2 then 执行2]...
[else 执行3]
end if;

例子：

CREATE PROCEDURE test5 ()
begin
	
	declare id int default 1;			
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
	select class_name;
end ;

call test5();

传递参数

语法结构：

create procedure procedure_name([in/out/inout] 参数名  参数类型)


in：该参数可以作为输入，也就是需要调用方传入值 , 默认
out：该参数作为输出，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数

in举例：

-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begin
	
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
	select class_name;
end ;

call test6(3);

out举例：
 

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
end ;


call test7(1,@class_name);	-- 创建会话变量		

select @class_name;		-- 引用会话变量

@xxx：代表定义一个会话变量，整个会话都可以使用，当会话关闭（连接断开）时销毁
@@xxx：代表定义一个系统变量，永久生效。

case语句

语法结构：

 while循环

语法结构：

//先判定条件，如果条件为true 则执行逻辑，否则不执行逻辑
while 条件 do
  sql逻辑
end while;

repeat循环

语法结构：

//先执行一次逻辑，然后判定条件是否满足，如果满足，则退出，不满足则继续下一次逻辑执行
repeat
  sql逻辑
  until 条件
end repeat;

loop循环

语法结构：

//loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，会出现死循环。loop可以搭配以下两个语句使用：
（1）leave：退出循环
（2）iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

[begin_label:]loop
     sql逻辑
end loop[end_label];

leave label;//退出指定标记的循环体
iterate label;//直接进入下一次循环

例子：
 

CREATE PROCEDURE test12 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;	
	sum:loop							-- 定义循环标识
		set total=total+count;
		set count=count-1;
		
		if count < 1 then
			leave sum;					-- 跳出循环
		end if;
	end loop sum;						-- 标识循环结束
	select total;
	
end ;

call test12(10);

游标

游标是用来存储查询结果集的数据类型。

语法如下：

//声明游标
declare 游标名称 cursor for 查询语句;


//打开游标
open 游标名称;


//获取游标记录
fetch 游标名称 into 变量[,变量];


//关闭游标
close 游标名称;

例子：

CREATE PROCEDURE test13 ()		-- 默认是输入(in)参数
begin
	
	declare id int(11);
	declare `name` varchar(20);
	declare class_id int(11);
	-- 定义游标结束标识符
	declare has_data int default 1;
	
	declare stu_result cursor for select * from student;
	-- 监测游标结束
	declare exit handler for not FOUND set has_data=0;//声明异常处理的语句
	
	-- 打开游标
	open stu_result;
	
	repeat 
		fetch stu_result into id,`name`,class_id;
		
		select concat('id: ',id,';name: ',`name`,';class_id',class_id);
		until has_data=0		-- 退出条件,注意不要打分号
	end repeat;
	
	-- 关闭游标
	close stu_result;
	
end ;

call test13();

定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用declare语句如下：

declare 处理方式 handler for 错误类型 处理语句；


//处理方式：
continue：遇到错误不处理，继续执行
exit：遇到错误马上退出
undo：遇到错误后撤回之前的操作，MySQL中暂不支持


//错误类型：
SQLSTATE ：字符串错误码，表示长度为 5 的 sqlstate_value 类型的错误代码
MySQL_error_code ：匹配数值类型错误代码
SQLWARNING ：表示匹配所有以 01 开头的数值SQLSTATE错误码
NOT FOUND : 表示匹配所有以 02 开头的数值SQLSTATE错误码
SQLEXCEPTION:表示匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE的错误代码

存储函数

语法结构：

create function 函数名([参数列表])
returns type
begin
....
end;

例子：

触发器

概述

触发器是与表有关的数据库对象，指在insert、update、delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库确保数据的完整性，日志记录，数据校验等操作。

使用别名old和new来引用触发器中发生变化的记录内容。

 触发器操作

创建触发器

create trigger 触发器名称
before/after insert/update/delete
on 表名
[for each row] //行触发器
begin
触发器内容
end;

例子：

//insert触发器

create trigger tri_insert_student
after insert 
on student
for each row 
update class set count=count+1 where class.id=new.class_id;

//delete触发器
create trigger tri_delete_student 
after delete 
on student 
for each row 
update class set count=count-1 where old.class_id=class.id;

//多条语句的触发器
                                                           
create trigger tri_delete_student
after delete
on student 
for each row 
BEGIN                                    
     Delete from grade where id = OLD.id;  #删除成绩表中的记录                                                        
     update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录   
END; #分号要有                                                                         

删除触发器

drop trigger [schema名] 触发器名

如果没有指定schema，默认为当前数据库

查看触发器

show triggers

事务

概述

事务是一组操作的集合，他是一个不可分割的工作单位。

 

并发事务问题

脏读：一个事务读到另一个事务还没提交的数据

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，有发现这行数据已经存在

事务的隔离级别

事务的隔离级别主要有四种 read uncommitted（读未提交），read committed（读已提交）,repeatable read（可重复读），seriatizable（串行化）。