MySql高级-笔记

一 索引、事务、锁

1. 索引:相当于书的目录
   1. 优点:查询速度快
   2. 缺点:占用存储空间大,维护和创建有成本,存储方式是树结构
   3. 索引使用:

      create index myindex on table(name);//创建索引 名:myindex 表名:table 列名:name
      show index from table;//查询 table表所有创建的索引
      drop index myindex on table;//删除 table表的myindex索引
      explain select * from table where name = 'abc';//分析SQL语句是否使用索引

2. 事务:一个最小的不可再分的工作单元
   1. 4大特征:原子性(A)  一致性(C)  隔离性(I)   持久性(D)
   2. 4隔离级别: 读未提交   读已提交  可重复读   串行化
   3. 事务使用:

      start transaction;//开始事务
      commit;//提交事务
      rollback;//回滚事务
      

3. 锁:多线程并发访问某一资源触发机制
   1. 乐观锁:先拿数据,再锁定. 乐观心态
   2. 悲观锁:先锁定,再拿数据. 悲观心态
      1. 共享锁 允许其他进行读取数据 不允许修改
      2. 排它锁 不允许对改数据任何操作 等待释放再解除
   3. 锁的粒度: 行锁 < 页锁 < 表锁    页锁最为常用

二 存储过程

1. 存储过程:SQL的封装与重用
   1. 优点:安全,提高性能,降低程序与数据库的交流
   2. 缺点:开发维护成本高,耗内存,不易于调试
   3. 使用:

      create procedure mypro(in a int,in b int,out sum int)
      begin
          set sum = a+b;
      end;
      call mypro(1,2,@s);-- 调用存储过程 
      select @s;-- 显示过程输出结果
      show create procedure mypro;
      drop procedure mypro;

2. 流程控制语句 if 条件  case 条件  while 循环   repeat 循环  loop 循环
   
   1. if 条件

      if num<0 then
          select '负';
      elseif num=0 then
          select '都不是';
      else
          select '正';
      end if;　　　　

   2. case 条件

      case
          when num<0 then select '负';
          when num=0 then select '都不是';
          else select '正';
      end case;

   3. while 循环

      while num<10 do
          set num = num+1;
          set sum = sum+num;
      end while;

三 视图和函数

1. 视图:是一个虚拟表
   1. 优点:好用-把多张表封装成一个视图,安全-可以隐藏字段
   2. 缺点:性能差,不能对视图做修改操作
   3. 使用:

      create view v1 as select name,age from student;
      show create view v1;
      drop view v1;

2. 函数:类似于存储过程,将一些SQL封装一起执行
   1. 与存储过程区别
      1. 返回值不同：函数必须有返回值,且仅返回一个结果值；过程可以没有返回值,但是能返回结果集；
      2. 调用时的不同：函数使用 select 进行调用；过程使用 call 进行调用；
      3. 参数的不同：函数的参数都是 in 参数；过程可以传递 in\out\inout 参数
   2. 使用:

      create function myfun(a int) returns int
      begin
          return a;
      end;
      select myfun(1);

      　　

四 触发器和游标

1. 触发器:触发器是和表关联的特殊的存储过程
   1. 使用:

      create trigger mytg
      after insert on orders
      for each row
      begin
          update product set num = num-3 where pid=1;
      end;
      drop trigger if exists mytg;
      

五 MySQL技巧与优化

1. --行转列
   select 
       user_name 姓名,
       max(case when course = '数学' then score else 0 end ) 数学,
       max(case when course = '语文' then score else 0 end ) 语文,
       max(case when course = '英语' then score else 0 end ) 英语
   from test_tb_grade group by user_name;
   --exists 查询是否有值
   select exists (select * from product where pid >100);
   
   --union 合并结果集去重  union all 合并结果集
   
   --rank 排序
   select t.*,
   case  when @score = t.score then @rownum
         when @score := t.score then @rownum := @rownum + 1
   end as rank
   from test_tb_grade t,(select @rownum := 0,@score := null) a
   order by t.score desc
   
   --分组统计 with ROLLUP
   select user_name,course,sum(score) from test_tb_grade
   GROUP BY user_name,course with ROLLUP;

六 MySQL管理

1. 查看用户:

   use mysql;
   select * from USER;

2. 创建用户:

   create user fhs@'%' identified by '123';

3. 用户授权:

   grant all on oa716.* to fhs@'%';

4. 撤销权限:

   revoke all on oa716.* from fhs;

5. 删除用户:

   drop user fhs@'%';