二阶段day6

一致性

1. 脏读：未提交事务直接读取
2. 第一类丢失：提交增加数据结果未增加
3. 第二类丢失：提交减少数据结果未减少

查看事务隔离级别:

• show variables like ‘transaction_ isolation’;

修改事务隔离级别:

• set session transaction isolation level read uncommitted; (session也可以变成global)

隔离级别有四个：

• READ UNCOMMITED
• READ COMMITED
• REPEATABLE READ（MySQL默认）
• SERIALIZABLE

视图

创建视图

Create view view名 （各列别名）AS 查询语句

视图如果有别名，通过别名进行列的操作

# 删除视图
Drop view view名

#修改视图名
create or replace view view名 AS 新查询语句

#查询时
SELECT * FROM spider_sql.vw_movie;（其中vw_movie是我创建的视图）
1
2
3
4
5
6
7
8
9
10
11
12

通过视图创建视图：可以更新，视图更新员工表更新

视图什么情况不能更新;

1.视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表-起使用。

2.创建视图时可以使用order by子句，但如果从视图中检索数据时也使用了order by,那么该视图中原先的order by 会被覆盖。

3.视图无法使用索引，也不会激发触发器实际开发中因为性能等各方面的考虑，通常不建议使用触发器。

函数

常用字符串函数

-- 合并字符串
select concat('a','bb','ccc');

-- 从hello world的第2个字符开始寻找
select locate('o','hello,world',2);
1
2
3
4
5

常用的数值函数

常用的时间日期函数

用户自定义函数

任务：假设当内容不超过50字符直接显示，超过50字符就截断

-- 创建函数
-- 修改终止符号
delimiter $$ 
create function truncate_string
(
	content varchar(16383),
    max_length int unsigned
)returns varchar(16383) DETERMINISTIC -- 也可以写NO SQL
begin
	if char_length(content) > max_length then
		return concat(left(content,max_length),'...');
    else
		return content;
    end if;
end $$

-- 将终止符号修改回来
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

使用函数

select truncate_string('你好啊，朋友',3);-- '你好啊...'

select col_id
	 , col_name
     , truncate_string(col_intro,10)
from tb_college;
1
2
3
4
5
6

过程

假设：将部门编号为10的工资上涨200，部门编号为20的工资上涨500，部门编号为30的工资上涨300

delimiter $$;
-- 创建过程
create procedure sp_upgrade_salary()
begin
	-- 异常处理器
    declare flag boolean default 1;
	declare continue handler for sqlexception set flag = 0;
	start transaction;
	update tb_emp set sal = sal+200 where dno = 10;
	update tb_emp set sal = sal+500 where dno = 20;
	update tb_emp set sal = sal+300 where dno = 30;
    if flag then 
    commit;
    else
    rollback;
    end if;
end $$
delimiter ;
-- 调用过程
call sp_upgrade_salary();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

索引(index):优化查询

select ename from tb_emp where eno = 3211;-- type = const常量级查询
select ename from tb_emp where ename = '张无忌'; -- 全表查询
1
2

为什么用主键作为筛选条件查询性能最好的？

• 因为主键上默认会自动创建一个聚集索引

如果使用innodb引擎存储数据，那么索引是一个B+树的层次结构

但是一般查询都是名字查询，所以如何在名字上建一个索引？

1. 创建索引：

-- create index 索引名称 on 表 (字段);
-- 例如：
create index idx_emp_ename on tb_emp (ename);
1
2
3

结果如下：

首先了解，以上图的各项指标代表什么：

2. 删除索引：

-- 删除索引 
-- 方法1
drop index idx_emp_ename on tb_emp;
-- 方法2
alter table tb_emp drop index idx_emp_ename;
1
2
3
4
5

我们自己创建的索引是非聚集索引，非聚集索引的叶子放的是主键。

3. 前缀索引

4. 复合索引

-- 前缀索引
create index idx_emp_ename1 on tb_emp (ename(1));
-- 复合索引,遵循最左匹配原则
create index idx_emp_ename2 on tb_emp (ename,job);
1
2
3
4

5. 唯一索引

-- 如果姓名无重复，则可添加unique
create unique index idx_emp_ename on tb_emp (ename);
1
2