3.MySQL高阶概念

qq_36227329

已于 2022-11-22 16:32:42 修改

阅读量164

点赞数

分类专栏： python测试开发笔记文章标签： mysql 数据库 java

于 2022-11-22 16:09:05 首次发布

本文链接：https://blog.csdn.net/qq_36227329/article/details/127984796

版权

python测试开发笔记专栏收录该内容

40 篇文章 1 订阅

订阅专栏

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

qq_36227329

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
3.MySQL高阶概念

变量：用来保存数据的符号。name=‘tester’，name=‘1234’存储过程是一段完成指定功能的SQL语句的集合。1、存储过程和函数【了解】怎么实现一个存储过程或函数。在存储过程的外面或者内部。只能在存储过程的内部使用。2、事务**【重点】**
复制链接

扫一扫

专栏目录