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 doset 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)deterministicbegin 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一组sqlend;

例：

delimiter //;-- 创建一个触发器create trigger tri_testafter delete -- 设置为行级别的触发器on book for each rowbegininsert 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。