MySQL的高级部分解析

1. MySQL的事务

(1)存储引擎的介绍

   介绍：当客户端发送一条SQL语句给服务器时，服务器端通过缓存、语法检查、校验通过之后，然后会通过调用底层的一些软件组织，去从数据库中查询数据，然后将查询到的结果集返回给客户端，而这些底层的软件组织就是存储引擎。

   MySQL的存储引擎：

     - MySQL的核心就是存储引擎，MySQL可以设置多种不同的存储引擎，不同的存储引擎在索引、存储、以及锁的策略上是不同的。

     - Mysql5.5之前，使用的是myisam存储引擎，支持全文搜索，不支持事务。

     - Mysql5.5以后，使用的是innodb存储引擎，支持事务以及行级锁

(2)MySQL事务的介绍

   介绍：事务是一个操作序列，这些操作要么都做，要么都不做，是一个不能分割的工作单位。在两条或两条以上的SQL语句才能完成的业务时，才需要用事务，因为事务时同步原则，效率比较低。

   事务的ACID特性：

     - 原子性：放在同一事务的一组操作时不可分割的

     - 一致性：在事务的执行前后，整体的状态是不变的

     - 隔离性：事务之间是独立存在的，两个不同事务之间互不影响

     - 持久性：事务执行之后，将会永久的影响到数据库。

#例：一个事务操作

BEGIN;

update t_account set money=money+100 where id =1;

update t_account set money=money-100 where id =2;

COMMIT;

#一个回滚操作

BEGIN;

update t_account set money=money+100 where id =1;

update t_account set money=money-100 where id =2;

COMMIT;

注意：MySQL数据库，dml操作采用的是自动提交

#查看自动提交

show variables like 'autocommit';

#修改自动提交

set autocommit=0;

(3)MySQL事务并发时产生的问题

  脏读：在一个事务的执行范围内，读到了另一事务未提交的数据。

  解决：读已提交，一个数据库只能读到另一个事务提交后的数据。(Oracle默认的事务隔离级别)

  不可重复读：一个事务，在只读范围内，被另一事务修改并提交事务，导致多次读取的数据不一致的问题。

  解决：可重复读(MySQL默认的事务隔离级别)

  虚读：一个事务的只读范围内，被另一个事务删除或者添加数据，导致多次读取的数据不一致的问题。

  解决：串行化：解决所有问题，但是速度十分缓慢，不能使用并发事务。

  注意：查看事务的隔离级别：select @@tx_isolation;

2. MySQL的存储程序

(1)MySQL的存储程序的介绍

   描述：运行与服务器端的程序。

   优点：简化开发，执行效率比较高(在服务器端以通过校验，可直接使用)

   缺点：服务器端保存这些存储程序需要占用磁盘空间;数据迁移时，需要将这些存储程序进行迁移;调试和编写程序在服务器端都不方便

   存储程序的分类：存储过程、存储函数、触发器

   注意：存储程序不能使用事务

(2)存储过程

  介绍：存储过程是在服务器端的一段可执行的代码块。

例：

#修改结束符标志

delimiter //

#创建存储过程

create procedure pro_book()

begin

#sql

select * from book;

select * from book where bid=3;

end //

#运行

call pro_book()

#参数的传入

delimiter //

create procedure pro_book02(num int)

begin

select * from book where bid=num;

end ; //

--调用

call pro_book02(3)

#传出参数

delimiter //

create procedure pro_book03(num int,out v_name varchar(10))

begin

select bname into v_name from book where bid=num;

end ; //

--调用,这里的@v_name是一个用户变量

call pro_book03(1,@v_name);

select @v_name;

#传入传出参数

delimiter //

create procedure pro_book04(num int)

begin

select bid into num from book where bid=num;

end ; //

--调用

set @v_id=3; --给用户变量赋值

call pro_book04(@v_id);

select @v_id;

控制流程语句

#if语句

delimiter //

create procedure if_test(score int)

begin

-- 定义局部变量

declare myLevel varchar(20);

if score>80 then

set myLevel='A';

elseif score >60 then

set myLevel='B';

else

set myLevel='C';

end if;

select myLevel;

end; //

-- 调用

call if_test(70);

#while循环

delimiter //

create procedure while_test()

begin

declare i int ;

declare sum int ;

set i=1;

set sum =0;

while i<=100 do

set sum=sum+i;

set i=i+1;

end while ;

select sum;

end ;//

call while_test()

#loop循环

delimiter //

create procedure loop_test()

begin

declare i int ;

declare sum int ;

set i=1;

set sum =0;

-- 起别名

lip:loop

if i>100 then

-- 离开loop循环

leave lip ;

end if ;

set sum=sum+i;

set i=i+1;

end loop ;

select sum;

end ;//

call loop_test()

#repeat循环

delimiter //

create procedure repeat_test()

begin

declare i int ;

declare sum int ;

set i=1;

set sum =0;

repeat

set sum=sum+i;

set i=i+1;

-- 不要加分号

until i>100

end repeat ;

select sum;

end ;//

call loop_test()

(3)存储函数

  存储在服务器端，有返回值，函数可以作为SQL的一部分进行调用。

**例**：

delimiter //

create function func_01(num int)

-- 返回值类型

returns varchar(20)

deterministic

begin

declare v_name varchar(20);

select bname into v_name from book where bid =num ;

return v_name;

end ; //

set @v_name=func_01(3);

select @v_name;

-- 作为SQL的一部分调用

select * from book where bname=func_01(3);

函数和存储过程的区别：

   - 存储过程有三种参数模式(in、out、inout)实现数据的输入输出，而函数是通过返回值进行数据传递。

   - 关键字不同

   - 存储过程可以作为独立个体执行，函数只能作为SQL的一部分执行。

(4)触发器

   触发器，存储在服务器端，由事件调用，不能传参。

   事件类型：增、删、改

   语法：

create trigger 触发器名

触发时机(after|before) event(update|delete|insert)

on 需要设置触发器的表名 for each row (设置为行级触发器)

begin

一组sql

end;

例：

delimiter //;

-- 创建一个触发器

create trigger tri_test

after delete

-- 设置为行级别的触发器

on book for each row

begin

insert into book values(old.id,'悲惨数据','zzy');

end;//

注意：在触发器中有两个对象：old、new，old表示删除数据时那条原数据记录，

new表示修改和插入数据时，那条新数据记录。

3. MySQL的表的设计

(1)数据库的三大范式：

   - 1NF：所有字段都是原子性的，不可分割的。

   - 2NF：非主键字段必须与主键相关(每一张表只描述一类事物)，而不能与主键部分相关(在联合主键时有效)

   - 3NF：非主键字段必须与主键相关(每一张表只描述一类事物)，而不能与主键部分相关(在联合主键时有效)

(2)表的关系：

一一对应

#以人和×××为例

人表：

CREATE TABLE `t_people` (

`id` int(11) NOT NULL,

`name` varchar(50) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

)

×××表：

create table t_idcard(

card_number varchar(18) primary key,

create_date date,

p_id int unique,

foreign key (p_id) REFERENCES t_people(id)

)

注意：设计方法：想办法让外键字段同时拥有唯一约束，外键字段在任意的表中都可以

一对多：

以部门和员工表为例：

create table t_emp(

eid int PRIMARY KEY,

ename varchar(50) not null,

job varchar(50),

deptno int ,

foreign key (deptno) REFERENCES t_dept(deptno)

)

部门表：

create table t_dept(

deptno int primary key,

deptname varchar(50)

)

注意：设计方法：只需要在多的那个表中增加一个外键约束

多对多：

设计方法：需要找一张中间表，转化成两个一对多的关系

(3)数据库的优化：

SQL的优化

在查询时一般不使用 *，因为在查询记录时，一般使用(*),他会将*转换为列名，然后在查询(耗时)

使用 not null /null 对索引进行搜索，会导致索引失效

索引列中使用函数，也会导致索引失效

索引列中进行计算，也会导致索引失效

索引列不要使用not|!=|<>

尽量不要使用or,使用union

索引列中使用like，也会导致索引失效

exists 和 in的使用选择

exists先执行主查询：如果主查询过滤的比较多，则使用exists

in先执行子查询：如果是子查询的过滤比较多，则使用in。