typora-copy-images-to: imgs

1. 第一章 MySQL常用函数(理解)

1.1. 1 字符串函数

函数 描述 实例
CHAR_LENGTH(s) 返回字符串 s 的字符数 SELECT CHAR_LENGTH('itheima') AS '长度';
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串 SELECT CONCAT('I','love','you');
LOWER(s) 将字符串 s 的所有字母变成小写字母 SELECT LOWER('ITHEIMA');
UPPER(s) 将字符串转换为大写 SELECT UPPER("itheima");
SUBSTR(s, start,length) 从字符串 s 的 start 位置(从1开始)截取长度为 length 的子字符串 SELECT SUBSTR("itheima",1,2);
TRIM(s) 去掉字符串 s 开始和结尾处的空格 SELECT TRIM(' itheima ')
-- 统计字符串的长度
select char_length('sunzhenhua');-- 10
-- 字符串的拼接
select concat('sun','zhen','hua');-- sunzhenhua
select concat('id=',id,':name=',name,':price_id=',price_id) from fruit;
-- 字符串转小写
select lower('SunZhenHua');-- sunzhenhua
-- 字符串转大写
select upper('SunZhenHua');-- SUNZHENHUA
-- 截取字符串 substr(var,start,lenth):说明var表示要截取的字符串,start:表示
-- 开始截取的文职,end表示:截取的长度
select substr('sunzhenhua',4,4);-- Zhen
-- 去除字符串两端的空格
select trim('    sun  zhen     hua     ');-- sun  zhen     hua

1.2. 2 数字函数

函数 描述 实例
RAND() 返回 0 到 1 的随机数 SELECT RAND();
ROUND(小数 , 小数点后保留小数位数) 四舍五入保留几位小数 SELECT ROUND(3.1415926,2) ;
TRUNCATE(小数 , 小数点后保留小数位数) 不会四舍五入保留几位小数 SELECT TRUNCATE(3.1415926,3);
LEAST(expr1, expr2, expr3, ...) 返回列表中的最小值 SELECT LEAST(13, 14, 521, 74, 1)
GREATEST(expr1, expr2,expr3, ...) 返回列表中的最大值 SELECT GREATEST(13, 14, 521, 74, 1)

1.3. 3 日期函数

函数名 描述 实例
NOW() 和 SYSDATE() 返回系统的当前日期和时间 SELECT NOW(); 或 SELECT SYSDATE();
CURDATE() 返回当前日期 SELECT CURDATE();
CURTIME() 返回当前系统时间 SELECT CURTIME();
YEAR(d) 返回d的中的年份 SELECT YEAR(NOW());
MONTH(d) 返回d的中的月份 SELECT MONTH(NOW());
DAY(d) 返回d中的日 SELECT DAY(NOW());
-- 获取当前日期时间
select now();
select sysdate();
-- 获取当前日期
select curdate();
-- 获取当前的时间
select curtime();
-- 获取输入日期对应的年
select year(now());-- 2020
select year('1998-03-03');-- 1998
select year('1998/03/03');-- 1998
-- 获取输入日期对应的月
select month('1998/03/03');-- 3
select month(now());-- 11
-- 获取输入日期对应的天
select day(now());-- 2

1.4. 4 高级函数

函数名 描述 实例
CURRENT_USER() 返回当前用户 SELECT CURRENT_USER();
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。 SELECT IFNULL(null,'Hello Word')
ISNULL(expression)** 判断表达式是否为 NULL,为空则为1,不为空则为0 SELECT ISNULL(NULL);
-- 查看当前用户
select current_user();
-- ifnull(v1,v2):当v1为null时,反馈v2,否则返回v1
select id,ifnull(price,0.0) as price from price;
-- isnull(exp):表示当exp为null时,结果为1,否则0
select isnull(null);-- 1
select isnull(12);;-- 0
-- 查询价格为null的数据有哪些?
select * from price where isnull(price);-- 4/null

2. 第二章 事务(掌握)

2.1. 1.事务的概念

​ 在实际的业务开发中,有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行,要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败,就进行事务的回滚,所有的SQL语句全部执行失败。

简而言之,事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败

事务作用:保证在一个事务中多次操作数据库表中数据时,要么全都成功,要么全都失败。

2.1.1. 1.1 事务的应用场景

​ 1.转账业务:比如a给b转账,那么a扣款和b加款两部操作做要么都成功,要么都失败;

​ 2.比如:学生管理系统中,要删除一个学生,那么需要删除学生的基本资料同时,也要删除和该学生相关的选课、成绩等信息;

image-20201017153201116

2.2. 2、手动提交事务

MYSQL中可以有两种方式进行事务的操作:

  1. 手动提交事务:先开启,再提交
  2. 自动提交事务(默认的):即执行一条sql语句提交一次事务。

    事务有关的SQL语句:

SQL语句 描述
start transaction; 开启手动控制事务
commit; 提交事务
rollback; 回滚事务

数据准备:

# 创建账号表
create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);

说明:

需求1:a给b转账100元,演示提交事务和回滚事务

-- 需求1:a给b转账100元,演示提交事务和回滚事务
-- 1)手动开启事务
start transaction;
-- 2)转账
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3)事务提交
commit;

-- 演示回滚
-- 1)手动开启事务
start transaction;
-- 2)转账
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3)事务回滚
rollback;

2.3. 3、自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,执行完毕自动提交事务,MySQL默认开始自动提交事务。

1.通过以下命令可以查看当前autocommit模式:

show variables like '%commit%';

2.设置自动提交的参数为OFF:

set autocommit = 0;  -- 0:OFF  1:ON

小结:

1.如何开启事务?

-- 数据库默认自动开启事务
-- 手动开启事务
start transaction;

2.如何提交事务?

commit;

3.如何回滚事务?

rollback;

2.4. 4、事务的回滚点和执行原理

2.4.1. 什么是回滚点

上面的操作,如果回滚,直接回滚到事务开始前。有时我们只需要回滚到中间的某个位置,就可以设置回滚点。

2.4.2. 语句

回滚点的操作语句 语句
设置回滚点 savepoint 名字
回到回滚点 rollback to 名字

练习:

1. 将数据还原到1000
2. 开启事务
3. 让a用户先减100块
4. 设置回滚点:savepoint itcast; 
5. 然后让a用户再次减100块
6. 回到回滚点:rollback to itcast
7. 最后commit提交事务
-- 1)手动开启事务
start transaction;
-- 2)a扣款100
update account set money=money-100 where name='a';
-- 设置回滚点
savepoint heima;
-- 3)b入账100
update account set money=money+100 where name='b';
-- 事务提交
-- commit;
-- 回滚到heima回滚点
rollback to heima;
-- 提交
commit;

2.4.3. 事务原理

说明:

1.客户端与服务端建立连接后,数据库维护一个临时的事务日志文件;
2.如果数据没有开启手动事务提交,那么数据直接写入到数据库中;
3.如果数据库开启事务手动提交,首先我们的事务组内的数据会先写入这个临时的事务日志文件中
4.当这个事务使用commit命令提交时,会将临时事务日志文件中的事务数据写入数据库;
5.当事务进行rollback回滚时,会清除这个事务日志文件下的事务数据
6.如果在事务连接过程中且未提交的时候,连接断开,那么也会清除事务日志文件中的数据;

2.5. 5、事务的四大特性(ACID)(面试)

数据库的事务必须具备ACID特性,ACID是指 Atomicity(原子性)、Consistensy(一致性)、Isolation(隔离性)和Durability(持久性)的英文缩写。

2.5.1. 1、隔离性(Isolation)

多个用户并发的访问数据库时,一个用户的事务不能被其他用户的事务干扰,多个并发的事务之间要相互隔离。

举例:

image-20201017171056636

2.5.2. 2、持久性(Durability)

指一个事务一旦被提交,它对数据库的改变将是永久性的,哪怕数据库发生异常,重启之后数据依然存在。

举例:

image-20201017171523160

2.5.3. 3、原子性(Atomicity)

原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

2.5.4. 4、一致性(Consistency)

一致性是指数据处于一种语义上的有意义且正确的状态

事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。

事务的成功与失败,最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。

image-20201017172153074

小结:

面试问题:说一下事务的4大特性?

1)原子性
   事务的一组逻辑单元不能再分割,要么都执行,要么都不执行;
2)隔离性
   在事务并发环境下,多个事务之间彼此隔离,不受影响;
3)持久性
   事务一旦提交,对数据的修改时永久性的,哪怕数据库发生异常,也不会丢失数据;
4)一致性
   首先是逻辑上的概念,事务执行前后要从一个执行性的状态到另一个一致性的状态;

事务相关的指令:

命令 作用
start transaction; 手动开启事务
commit; 提交事务
rollback; 回滚事务
savepoint 回滚点的名称; 设置事务回滚点
rollback to 回滚点的名称; 回滚到回滚点

2.6. 6、事务的并发访问引发的三个问题(面试)

事务在操作时的理想状态:多个事务之间互不影响,如果隔离级别设置不当就可能引发并发访问问题。

并发访问的问题 含义
脏读 一个事务读取到了另一个事务中尚未提交的数据。最严重,杜绝发生。
不可重复读 一个事务中多次读取的数据内容不一致,要求的是一个事务中多次读取时数据是不一致的,这是事务update时引发的问题
幻读(虚读) 一个事务内读取到了别的事务插入或者删除的数据,导致前后读取记录行数不同。这是insert或delete时引发的问题

2.6.1. 1.脏读:

指一个事务读取了另外一个事务未提交的数据。(非常危险)

说明:脏读强调一个事务读取了未提交的事务的数据;

时序图:

时间 session1 session2
T1 start transaction; start transaction;
T2 原来数据:a=1000;b=1000
update:a=900;b=1100
T3 select:a=900;b=1100
此时session1读取了session2未提交的数据,这种现象叫做脏读
T4 rollback;最糟糕的情况

2.6.2. 2.不可重复读:

在一个事务内多次读取表中的数据,多次读取的内容不同。

​ 说明:不可重复读就是一个事务读取了其他事务提交前后的数据,造成两次读取不一致的情况,多发生在事务update操作时;

时序图:

时间 session1 session2
T1 start transaction; start transaction;
T2 select:a=1000;b=1000;
T3 原来数据:a=1000;b=1000 update:a=900;b=1100
commit;提交事务
T4 再次查询:select:a=900;b=1100;
此时读取的数据与上一次读取的数据内容不一致,这种现象叫做不可重复读

说明:发生不可重复读的前提是1)事务多次读取相同条件下的数据2)另一个事务修改(update)完毕后事务进行了提交操作;

2.6.3. 3.幻读(虚读)

一个事务内读取到了别的事务插入或者删除的数据,导致前后读取记录行数不同,多发生在delete或insert时;

时序图:

时间 session1 session2
T1 start transaction; start transaction;
T2 count(*);-- 2
T3 insert into ...(插入1条记录)
commit;事务提交
count(*)=3
T4 再次统计:count()=3
此时事务1前后读取的记录数不一致的现象叫做*虚读

小结:

能够理解事务并发访问的三个问题:

1)脏读
  一个事务读取了另一个事务未提交的数据(开发中避免)
2)不可重复读
   一个事务多次读取一组数据,前后的内容不一致的现象叫做不可重复读;(强调:内容,update3)幻读(虚读)
   一个事务多次读取一组数据的记录数,前后的记录数不一致的现象叫做幻读;(强调:记录数,delete,insert)

2.7. 7、事务的隔离级别

2.7.1. 1.介绍

MySQL数据库规范规定了4种隔离级别,用于解决上述出现的事务并发问题;

级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
1 读未提交 read uncommitted
2 读已提交 read committed Oracle和SQL Server
3 可重复读 repeatable read MySQL
4 串行化 serializable

说明:其实三个问题,开发中最严重的问题就是脏读,这个问题一定要避免,而关于不可重复读和虚读其实只是感官上的错误,并不是逻辑上的错误。就是数据的时效性,所以这种问题并不属于很严重的错误。如果对于数据的时效性要求不是很高的情况下,我们是可以接受不可重复读和虚读的情况发生的。

2.7.2. 2、安全和性能对比

安全: 串行化>可重复读>读已提交>读未提交

性能: 串行化<可重复读<读已提交<读未提交

小结:

面试:数据库有哪些隔离级别?

数据库有哪些隔离级别?
1)读未提交    脏读 不可重复读 幻读
2)读已提交    不可重复读 幻读
3)可重复度    幻读
4)串行化

2.8. 8、脏读的演示(不建议课下练习,课上听懂即可)

2.8.1. 8.1 查询和设置隔离级别

show variables like '%isolation%';
-- 或
select @@tx_isolation;

2.8.2. 8.2 设置事务隔离级别

set global transaction isolation level 隔离级别;
-- 如:
set global transaction isolation level read uncommitted;

注意:设置后只有重新连接数据库才能看到隔离级别的变化;

2.8.3. 8.3 脏读的演示

脏读:一个事务读取到了另一个事务中尚未提交的数据。

操作流程:设置mysql读未提交的隔离级别,同时开启两个事务,事务A执行更新操作且提交前,事务B执行查看操作,此时事务A再将事务回滚,那么事务B读取的数据就是错误的数据.

前提参数设置:set global binlog_format=MIXED;(该命令设置完毕后,依旧需要重新连接数据库)

演示时序图:

1604287467185

说明:sqlyog开启两个窗口,同时开启事务,一个窗口执行更新操作,另一个窗口查询,最后事务回滚,前后数据存在差别;

如何解决脏读的问题?

说明: 需要将全局的隔离级别进行提升(read committed),然后开启两个sqlyog窗口演示,演示完毕恢复隔离级别即可;

1)提高隔离级别 读已提交
set global transaction isolation level read committed;
2)刷新session(重新连接)

1604288042796

小结:

1.查看全局事务隔离级别命令?

select @@tx_isolation;

2.设置全局隔离级别?

set global transaction isolation level 事务隔离级别;

3.如何解决脏读?

调高隔离级别 >=read committed;

3. 第三章 MySQL性能(理解)

3.1.1. 2.1 分析-数据库查询效率低下

要提高操作数据库的性能,有如下两种方式:

1.硬优化:就是软优化之后性能还很低,只能采取硬优化,最后的步骤了,就是公司花钱购买服务器。在硬件上进行优化。

2.软优化: 在操作和设计数据库方面上进行优化(重点)(表结构和sql语句)

3.1.2. 2.2 分析-执行次数比较多的语句

1.执行次数比较多的语句分类

* 1)查询密集型

* 2)修改密集型  es solor

2.查询累计插入和返回数据条数,即查看当前数据库属于查询密集型还是修改密集型

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';

1604289042745

3.1.3. 2.3 查看-sql语句的执行效率

准备千万级别数据:

-- 1. 准备表
CREATE TABLE user(
    id INT,
    username VARCHAR(32),
    password VARCHAR(32),
    sex VARCHAR(6),
    email VARCHAR(50)
);
-- 2. 创建存储过程,实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法,插入千万条数据之后,在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO user VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
    COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号
-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;
-- 4. 调用存储过程
CALL auto_insert();

说明:上述sql大概运行10分钟左右,课上演示,不建议课下练习!

需求:查询id是22的用户,测试查询耗时

select * from user where id=22; -- 5s

4. 第四章 索引(掌握)

4.1. 3.1 什么是索引

Mysql官方对索引的定义为:索引(index)是帮助Mysql高效获取数据的数据结构。

可以得到索引的本质:索引就是数据结构;

4.2. 3.2 MySQL索引分类

* 主键(约束)索引
        主键约束+提高查询效率

* 唯一(约束)索引
        唯一约束+提高查询效率

* 普通索引
        仅提高查询效率

* 组合(联合)索引
        多个字段组成索引

* 全文索引
        solr、es

* hash索引
        根据key-value 效率非常高
         等值查询,不适合范围查询

4.3. 3.3 MySQL索引语法

3.3.1 创建索引 【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

说明:

​ 1.如果在同一张表中创建多个索引,要保证索引名是不能重复的

​ 2.上述创建索引的方式比较麻烦,还需要指定索引名

3.采用上述方式不能添加主键索引

【准备的创建表的SQL语句】

-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

练习:

需求1:给name字段设置普通索引
    create  index name_idx on student(name);
需求2:给telephone字段设置唯一索引
    create unique index name_idx on student(telephone);

3.3.2 在已有表的字段上修改表时指定【了解】

-- 添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名:primary

-- 添加唯一索引(除了NULL外,NULL可能会出现多次)
alter table 表名 add unique(字段); -- 默认索引名:字段名

-- 添加普通索引,索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名:字段名

【准备的创建表的SQL语句】

-- 创建学生表
CREATE TABLE student2(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

练习:

需求1:1.指定id为主键索引
    alter table student2 add primary key(id);
需求2:指定name为普通索引
    alter table student2 add index(name);
需求3:指定telephone为唯一索引
    alter table student2 add unique(telephone);

3.3.3 创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

3.3.4 查看索引

show index from 表名;

练习:【查看student3表的索引信息】

show index from student3;

3.3.5 删除索引

-- 两种方式
-- 方式1:直接删除
drop index 索引名 on 表名;
-- 方式2:-- 修改表时删除
alter table 表名 drop index 索引名;

练习:

练习1:删除student表的name普通索引
    drop index name_idx on student;
练习2:删除student表的telephone唯一索引
     alter table student drop index telephone_idx

4.4. 3.4 千万表记录索引效果演示

【1.先来测试没有索引情况下查询】

-- 1.指定id查询
select * from user where id = 8888888;
-- 2.指定username精准查询
select * from user  where username = 'jack1234567';

需求1:给id和name添加索引,其中id为主键索引,name为普通索引

-- 指定id为主键索引
ALTER TABLE USER ADD PRIMARY KEY(id);
-- 指定username为普通索引
ALTER TABLE USER ADD INDEX(username);

说明:千万数据下添加索引非常耗时,大概需要10分钟左右;

测试添加索引后查询执行效率情况;

select * from user where id=22;-- 5.422s
select * from user2 where id=22;-- 0.00s

4.5. 3.5 索引的优缺点

4.5.1. 1.优势

​ 1) 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。 ​ 2) 索引底层就是排序,通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

4.5.2. 2.劣势

  • 在数据库建立过程中,需花费较多的时间去建立并维护索引,特别是随着数据总量的增加,所花费的时间将不断递增(在海量数据前提下,创建索引成本高)。
  • 在数据库中创建的索引需要占用一定的物理存储空间,这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间(会额外占用磁盘空间)。
  • 在对表中的数据进行修改时,例如对其进行增加、删除或者是修改操作时,索引还需要进行动态的维护,这给数据库的维护速度带来了一定的麻烦(维护成本高)。

4.6. 3.6 索引创建原则(面试)

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。

1. 字段内容可识别度不能低于70%,字段内数据唯一值的个数不能低于70%
    例如:一个表数据只有50行,那么性别和年龄哪个字段适合创建索引,明显是年龄,因为年龄的唯一值个数比较多,性别只有两个选项。

2. 经常使用where条件搜索的字段,例如user表的id name等字段。

3. 经常使用表连接的字段(内连接、外连接),可以加快连接的速度。

4. 经常排序的字段 order by,因为索引已经是排过序的,这样一来可以利用索引的排序,加快排序查 询速度。

5.空间原则(字段占用空间越小也好)

* 注意: 索引并不是越多越好,因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引,添加记录、更新、修改时,也需要更新索引,会间接影响数据库的效率。

4.7. 3.7 索引的数据结构【了解】

我们知道索引是帮助MySQL高效获取排好序数据结构

以磁盘顺序迭代查询与二叉树查找为例:

如果没有使用索引查询数据,比如查询col1=6,需要经过6次io;

如何较少磁盘io次数?

如果读取col2=89,那么需要3次io;

4.7.1. 3.7.2 索引的数据结构

数据结构学习网站(美国旧金山大学)

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

1.二叉查找树

左边的子节点比父节点小,右边的子节点比父节点大;

1604299840372

2.红黑树

平衡二叉树(左旋、右旋、变色);

说明:存储千万数据,树高大约为23;

1604300068791

3.BTree

如何将树的高度压低?

将每个节点存储的数据尽量多一些!

多路平衡搜索树;

说明:数据库一个节点16KB,每个节点的元素由索引(8字节),指针域(6个字节),数据组成(比如1kb),存储千万数据树高接近6;

如果存储的数据占1kb,那么每个节点存储的元素个数是15个元素,那么如果存储1000w数据树高多少?6

思考:如何进一步降低树的高度及优化查询?

​ 1)数据查询的过程中,有一些数据data的加载时没有意义且浪费磁盘io;

​ 2)如果要进行范围查询,那么需要反复从根节点读取数据;

4.B+Tree

1)B+tree将树分为叶子节点和非叶子节点,其中非叶子节点只存储索引+指针,不存储数据,而叶子节点存储索引+指针域+数据;

如果一个B+tree的树高时3的话,那么非叶子节点2层,叶子节点1层;

​ 非叶子节点两层:1170个元素,如果是两层,那么元素数量:11701170=*1,368,900

​ 叶子节点:因为包含索引+指针域+数据 -----16*1.24/(8+6+1025)=15

总共:1,368,900*15=20,533,500

优化BTree(非叶子节点:索引+指针、叶子节点:索引+数据【地址】);

说明:b+tree通过非叶子节点不存储数据,进一步降低了树的高度;

​ 通过叶子节点之间维护了指针双向指向,提高了区间的访问能力;

存储2千w数据,树高维护在3左右!

小结:

B+Tree好处:

​ 1)降低树的高度

​ 2) 叶子节点按照索引排好序,支持范围查找,速度会很快。

​ 3)还有一点是mysql将根节点都加载到内存中,每张表有一个根节点,大小是16KB.那么这样的好处,按照上述如果是千万条数据,那么只有2次磁盘IO.这就是为什么我们加完索引之后瞬间查到数据的原因了。

最终结论:

通过将树的每个节点存储的元素增大来降低树的高度;

4.7.2. 3.7.3 MySQL中的B+Tree

-- 查看mysql索引节点大小
show global status like 'innodb_page_size'; -- 16kb

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能

5. 第五章 视图(掌握)

数据准备:

说明:创建国家和城市表。
1.  1个国家有多个城市,一个城市只属于一个国家
2.  国家和城市是:1对多
-- 1个国家有多个城市,一个城市只属于一个国家
-- 国家和城市是:1对多
CREATE TABLE country (
    country_id int(11) PRIMARY KEY AUTO_INCREMENT,
    country_name varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE city (
    city_id int(11) PRIMARY KEY AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int(11) NOT NULL,
    constraint ref_country_fk foreign key(country_id) references country(country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country values(1,'China');
insert into country values(2,'America');
insert into country values(3,'Japan');
insert into country values(4,'UK');


insert into city values(1,'西安',1);
insert into city values(2,'NewYork',2);
insert into city values(3,'北京',1);
insert into city values(4,'上海',1);

5.1. 4.1 视图介绍

5.1.1. 1.概述

​ 1.视图(View)是一种虚拟存在的表,行和列的数据来源于定义视图的查询中使用的表,并且是在使用视图时动态生成的。

​ 2.视图和普通表一样使用,但是视图并不存储数据。通俗的讲,视图就是一条SELECT语句执行后返回的结果集。

5.1.2. 2.应用场景

1)多个地方用到同样的查询结果

2)该查询结果使用的sql语句较复杂

创建视图语法格式:

create view 视图名
as
查询语句;

需求:查看所有国家和对应城市的信息

 create view country_view as select country.*,city.city_id,city.city_name from city,country where city.country_id=country.country_id;

5.1.3. 3.视图和表的区别

     使用方式      占用物理空间

视图    完全相同     视图中的数据不保存,仅仅保存的是sql逻辑,磁盘上只有表结构,没有数据

表     完全相同      占用

5.1.4. 4.视图的好处

* 1、sql语句提高重用性,效率高
* 2、和表实现了分离,提高了安全性,使用视图的用户只能访问他们被允许查询的结果集

5.2. 4.2 修改视图

方式1-创建过程覆盖旧的视图,达到修改的操作

create or replace view 视图名
as
查询语句;
-- 说明:如果存在该视图名就是修改视图,如果不存在该视图名则创建视图。

需求:修改上述视图city_country_view,select语句变为查询城市名是上海的城市信息和所属国家信息

create or replace view country_view as select country.*,city.city_id,city.city_name from city,country where city.country_id=country.country_id
 and country.country_id=1;

方式2:使用alter修改

alter view 视图名
as
查询语句;

需求:修改上述视图city_country_view,select语句变为查询城市名是北京的城市信息和所属国家信息

 alter view country_view2 as select country.*,city.city_id,city.city_name from city,country where city.country_id=country.country_id
 and country.country_id=2;

5.3. 4.3 查看视图

查看视图指令与table指令格式一致.

show tables;
desc country_view;
show create table country_view;

5.4. 4.4删除视图

drop view 视图名,视图名,.....;

需求:删除 country_view视图

drop view country_view;

小结:

视图应用场景?

1)复杂的sql查询功能,为了提高代码的复用性,可以将查询封装成视图;
2)保护数据安全(向上层应用暴露表中仅仅满足条件的数据)

创建语句格式?

create view 视图名称 as select_sql;

6. 第六章 触发器(了解)

6.1. 5.1 触发器概述

​ 1.触发器主要是通过事件进行触发而被执行的,我们可以在修改数据的前后绑定事件,触发执行定义的SQL;

​ 2.触发器是与表有关的数据库对象,指在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句集合。

​ 3.使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。

说明:OLD表示操作前的对象,NEW 表示操作之后的对象。

触发器类型 NEW 和 OLD的使用
INSERT 型触发器 NEW 表示将要或者已经新增的数据,插入之前没有数据,所以不能使用OLD
UPDATE 型触发器 OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据,删除之后没有数据了,所以不能使用NEW

6.2. 5.2 创建触发器

-- 语法
create trigger trigger_name
before/after insert/update/delete
on tbl_name
for each row   -- 行级触发器
    trigger_stmt ;
说明:
    1)before/after位置称为触发时机,一个触发器只能选择一个
    2)insert/update/delete位置称为触发事件,一个触发器只能选择一个
    3for each row称为行级触发器,触发器绑定实质是表中的所有行,因此当每一行发生指定改变的时候,就会触发触发器。

数据准备:

-- 创建员工表,员工信息
create table emp(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int,
    salary int
);
insert into emp(id,name,age,salary) values(null, '金毛狮王',40,2500);
insert into emp(id,name,age,salary) values(null, '蝙蝠王',38,3100);
-- 创建一张日志表,存放日志信息
create table emp_logs(
    id int(11) primary key auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的ID,emp表数据的id',
    operate_params varchar(500) comment '操作参数,插入emp中的数据'
)engine=innodb default charset=utf8;

需求1:创建 insert 型触发器,完成插入数据时的日志记录

需求2:创建 update 型触发器,完成更新数据时的日志记录 **

create trigger update_trigger
after update
on emp
for each row
   insert into emp_logs values(null,'update',now(),old.id,concat('old:{',old.id,old.name,old.age,old.salary,
'} new:{',new.id,new.name,new.age,new.salary,'}'));

需求3:创建delete 行的触发器 , 完成删除数据时的日志记录

6.3. 5.3 查看触发器

可使用指令或者可视化工具查看触发器;

SHOW TRIGGERS;

6.4. 5.4 删除触发器

drop trigger trigger_name ;

总结:

1)常用函数
  trim():去除字符串前后空格
  concat(str1,str2,...):字符串拼接
  substr(str,start,length);截取字符串指定索引下的长度
  round(num,保留小数位);四舍五入
  truncate(num,保留小数位);不做四舍五入
  now()/sysdate():获取当前日期时间
  curdate();获取当前日期
  day(now());获取输入值对应的天
  curtime();获取当前的时间
  year()/month()
  ifnull(v1,v2)/isnull(v1)
 2)事务
   4大特性:
     原子性:事务的一组逻辑单元,不可分割,要么都发生,要么都不发生;
     一致性:事务中数据在事务前后从一个一致性状态到另一个一致性状态;
     持久性:事务一旦提交,对数据的修改时永久性的;
     隔离性:事务在并发环境下,事务与事务之间彼此隔离,不受影响;
 3)事务的并发问题:
    脏读:一个事务读取了另一个事务尚未提交的数据;
    不可重复读:一个事务多次读取数据时,内容不一致的现象(update);
    幻读(虚读):一个事务多次读取记录时,记录数不一致的现象(insert/delete);
4)mysql事务隔离级别
  读未提交 ----》脏读,不可重复读,幻读
  读已提交 ----》不可重复读,幻读
  可重复读 ----》幻读
   串行化
5)索引分类
  主键索引:提高查询效率+唯一+非空约束
  唯一索引:unique,唯一约束+底层维护一个唯一索引
  普通索引:index 提高查询效率
  联合索引:(联合主键索引,联合唯一索引,联合普通索引)
  全文索引
  hash索引
  空间索引
6)索引创建语法
create index 索引名称 on 表明(字段,....)
create unique index 索引名称 on 表明(字段,....)
alter table 表名称 add primary key(字段,....);
7)mysql innodb b+tree
  多路平衡搜索树
  特点:
    1)非叶子节点只存储索引和指针域
    2)叶子节点存储索引,数据,指针域
    3)innodb加载数据按照页加载,16kb,为了提高区间访问能力,维护了一个双向的链表结构;
8)视图
   视图与表的区别:
     视图:不存储数据,只存储sql查询语句+表结构
     表:存储数据+表结构;
   语法:
     create view 视图名称 as  select语句;
   视图作用:
      1)提高了代码的复用性
      2)提高了安全性;
9)触发器
   事件驱动
   语法:
   create trigger 触发器名称
   before/after insert/delete/update
   on 触发器要绑定的表名
   for each row
       sql(new,old);

作业:

练习课上所讲内容,理解事务,索引,视图,触发器的作用和应用场景

results matching ""

    No results matching ""