MySQL高阶概念

1、存储过程和函数【了解】

• 基础概念

  • C/S架构：client客户端，server服务器端

• 存储过程是一段完成指定功能的SQL语句的集合

• 怎么实现一个存储过程或函数

  • 变量：用来保存数据的符号。 name='tester'，name='1234'

    • 在存储过程的外面或者内部

    • set  @变量名=值 ; 
      

    • 只能在存储过程的内部使用

    • declare  变量名  数据类型  [default   默认值 ] ;
      

      特点：

      • 所有的declare要挨着写，而且只能写在存储过程最前面；先定义，后使用
      • 使用这种变量不需要@符号，直接使用变量名即可

  • 存储过程怎么定义？【重点】

    • create  procedure   [if not exists]  <proname>（形参列表）
      begin
      	代码块 ;
      end
      

      • 形参列表： 定义存储过程或函数的时候，用来占位的变量名。全称：形式参数
      • 实参：在调用函数或存储过程的时候，实际上使用的数据或变量。
      • 语法：(in|out|inout 变量名 数据类型(宽度),...)
        • in: 表示数据通过这个变量传入存储过程里面
        • out：表示数据通过这个变量传出到存储过程外面
        • inout：表示数据可以通过这个变量传入和传出。

  • create procedure  demo1(in x int,in y int,out z int)
    begin
     set z=x+y;
    end
    
    #call demo1(1,2,@rlt);
    #select @rlt; # 打印@rlt变量
    #call demo1(3,2,@rlt);
    #select @rlt; # 打印@rlt变量
    # 实现除法运算，用户可以输入两个数，返回两个数的商。
    create procedure  demo2(in x int,in y int,out z float(10,2))
    begin
     if y=0 then
    		select '除数不能为0！';
     else
    		set z=x/y;
     end if;
    end
    
    
    
    #登录功能：用户可以输入用户名和密码，如果用户名是tester，密码是123456，提示用户登录成功，否则提示输入有误，重新登录。
    
    create procedure  login(in username varchar(20),in passwd varchar(8),out msg varchar(20))
    begin
    	if username='tester' and passwd='123456' then
    			set msg='登录成功！';
    	else
    			set msg='输入有误，请重新输入.';
    	end if;	
    end
    #登录功能：用户可以输入用户名和密码，如果用户名是tester，密码是123456，提示用户登录成功，
    #如果用户名错误，提示：用户名输入有误。密码错误，提示：密码输入有误。
    call login('tester1','123456',@rlt);
    select @rlt;
    
    create procedure  login_v2(in username varchar(20),in passwd  varchar(8),out msg varchar(20))
    begin
    	if username='tester'  then
    		  if passwd='123456'  then
    				set msg='登录成功';
    		  else
    				set msg='密码输入有误';
    			end if;
    	else
    		set msg='用户名输入有误';
    	end if;
    end
    #登录功能：用户可以输入用户名和密码，如果用户名是tester，密码是123456，提示用户登录成功，
    # 如果用户名错误，提示：用户名输入有误。密码错误，提示：密码输入有误。
    # 允许用户输入错误三次，三次错误之后，提示用户：登录操作将暂停10分钟。
    # 循环执行
    #
    -- REPEAT
    -- 	语句块;
    -- UNTIL 退出循环条件 END REPEAT;
    drop procedure  if exists  login_v3;
    create procedure  login_v3(in username varchar(20),in passwd varchar(8),out msg varchar(20))
    begin
    	declare  cnt  int  default 0; #定义计数器
    	repeat  # 开启循环
    		# 多分支判断结构
    		if username='tester'  and passwd='123456'  then
    				set msg='登录成功';
    		elseif passwd!='123456' then 
    				set msg='密码输入有误';
    		elseif username !='tester' then
    				set msg='用户名输入有误';
    		end if;
    		set cnt=cnt+1; # 计数器递增
    		set msg=concat('你还有',(3-cnt),'次机会。');
    	until cnt>3 end repeat;  # 计数器的值达到条件，则退出循环
    	select '错误超过三次，登录操作将锁定10分钟。';
    end
    
    call login_v3('tester','123456',@rlt);
    select @rlt;
    # 使用while改造登录的功能
    # 以下是while的写法
    WHILE cnt<=3 DO
    	# 多分支判断结构
    		if username='tester'  and passwd='123456'  then
    				set msg='登录成功';
    		elseif passwd!='123456' then 
    				set msg='密码输入有误';
    		elseif username !='tester' then
    				set msg='用户名输入有误';
    		end if;
    		set cnt=cnt+1; # 计数器递增
    		set msg=concat('你还有',(3-cnt),'次机会。');
    END WHILE;
    
    

  • 中途离开循环

    • iterate : 跳出当前的迭代，进入下一次迭代
    • leave ：结束当前循环

  • 函数定义

  • create  function  <funcname>(形参列表)  returns  数据类型
    begin
    	语句块;
    	return  变量;
    end
    

    特点：【重点】

    • 形参列表：没有in、out、inout这样区别。因为函数只有入参。
    • 语法： （x int,y int）returns int
    • 只有一个返回值。
    • select 不能在函数里面用了。
    • 函数的返回值是可以赋值给变量的

2、事务**【重点】**

• 什么是事务？

  • 由一系列操作步骤组成逻辑单元

• 解析

  • 多个步骤组成的
  • 逻辑单元：要么一起执行完成，要么一起失败。
    • 举例：
      • 银行转账：A向B转500元

• 事务的4大特性

  • 原子性：事务的步骤是不可拆分
  • 隔离性：事务与事务之间不允许互相干扰
    • InnoDB存储引擎提供事务的隔离级别有
      • READ UNCOMMITTED
      • READ COMMITTED
      • REPEATABLE READ
      • SERIALIZABLE
  • 一致性：事务提交前后的数据库的变化必须一致
  • 持久性：事务执行的效果是永久的

• 脏读

• 幻读

  隔离级别	脏读	不可重复读	幻读	第一类丢失更新	第二类丢失更新
  未提交读（Read uncommitted）	允许	允许	允许	不允许	允许
  已提交读（Read committed）	不允许	允许	允许	不允许	允许
  可重复读（Repeatable read）	不允许	不允许	允许	不允许	不允许
  可串行化（Serializable ）	不允许	不允许	不允许	不允许	不允许

3、视图【了解】

• 视图：是一个虚拟表
• 作用：用来存放查询语句返回的结果，提高查询效率，实现数据隔离。
• 操作：
  • 创建视图
  • 查看视图
  • 删除视图
  • 修改视图
• 注意事项
  • 单表查询产生视图可以进行DML操作，不推荐。
  • 多表查询产生的视图无法进行DML操作：增删改

4、索引【熟悉】

• 什么是索引
  • 是一种可以进行快速排序的一种数据结构
• MySQL索引的算法
  • hash表
  • B+tree
• type：【了解】
  • all： 当explain的结果中type的值为all的时候，表示没有使用索引，如果该语句的执行速度又比较慢，则表示可以进行索引优化。
    • 如果使用了索引，看到还是all，表示where的写法有错误，导致索引失效。
  • const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
• 导致索引失效的原因 【了解】

  • 导致索引失效的可能原因：

    1. 查询语句中使用OR关键字
    2. 查询语句中使用select *
    3. 复合索引，未遵循最左原则： 复合索引分开用的时候，最左边的字段有效，其他的字段不会索引
    4. like使用%或_开头
    5. 需要类型转换
    6. where中索引列有运算
    7. where中索引列使用了函数
    8. 如果mysql觉得全表扫描更快时(数据极少)
• 索引的作用究竟是什么？
  • 提高查询效率
• 索引的缺点
  • 会增加空间占用，因为索引也是要占磁盘空间
  • 会使得DML效率变低：因为每次变动索引需要重新排序
    • 读写分离：数据库做成同样的两个，一个用来查询，一个用来做DML