视图
1.定义：SQL语句执行后产生的虚拟表 本身并不包含数据 为了方便后续使用就将虚拟表保存起来形成视图
2.作用：查询数据及后续方便使用 视图的大部分情况是保障数据的安全性 提高查询效率
3.保存视图语法：create view 视图名 as SQL语句
4.删除视图语法：drop view 视图名
注：在cmd终端中 mysql的表与视图字段没有实际的区分 如果保存太多视图可能会导致在数据修改时在视图中 修改导致报错而难以查询到原因 因此并不建议使用太多视图 并且尽量不要修改视图中的数据
触发器
1.定义：针对表数据的增、删、改自动触发的功能(增/删/改 前/后) 是一种特殊的存储过程
2.作用：协助应用在数据库端确保数据的完整性 日志记录 数据校验等操作
3.语法结构：
create trigger 触发器的名字 before/after insert/delete/update on 表名 for each row begin
SQL语句
end
注：触发器中SQL语句需要用到分号 但分号又是SQL语句中的结束符 所以在编写触发器语法时需要临时修改SQL语句中的结束符 触发器代码写完后记得将结束符修改回原来的结束符
修改结束符的语法结构：delimiter 修改成别的符号
4.案例
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
注：上述案例通过添加两张表 在一张表中创建一个判断字段 在一个表中添加数据时 在触发器中判断该条件 根据输入的内容而做不同的判断 将符合触发器条件的数据同时也保存到另一个表中(类似于筛选出需要单独记录的数据或者对特有字段进行日志记录 通过往一张表里添加数据 触发器让两张表之间建立连接关系)
存储过程
1.定义：编译并储存在数据库中的一段SQL语句集合 类似于python中的函数(数据库SQL语言层面的封装与重用)
2.作用：调用存储过程可以简化应用开发人员的很多工作 减少数据在数据库与应用服务上的传输 提高数据处理效率
3.语法结构：
delimiter 临时结束符 # 修改结束符 语法中存在分号
create procedure 名字(参数，参数)
begin
SQL语句
end 临时结束符
delimiter; # 修改回原来的分隔符
4.查看存储过程的具体信息语句：show create procedure 名字;
查看所有存储过程语句：show procedure statues;
删除存储过程语句：drop procedure 名字; # 删除后再查询报错
5.案例
delimiter $$
create procedure p1(
in m int, # in表示这个参数只能传入不能被返回
in n int,
out res int) # out表示这个参数可以被返回 inout 既可以传入也可以返回
begin
select * from t1 where id > m and id < n;
set res=0;
end $$
delimiter ;
# 针对res需要提前定义
set @res=10; # 定义
select @res; # 查看 10
call p1(1,3,@res); # 调用 查看输出的数据
select @res; # 查看 0
show create procedure p1; # 查看存储过程的具体信息
show procedure ststus; # 查看所有存储过程
drop procedure p1; # 删除存储过程
事务
1.定义：事务是一个最小的不可分割的工作单元 通常一个事务对应一个完整的业务
一个业务需要多个DML语句(insert、delete、update)共同完成 业务只和DML有关
2.事务是什么：多个操作同时进行 要么都成功 要么都失败(如给别人转账 要么你的钱减少 他的钱增多 要不就是都不变(存在错误钱被退回业务))
3.事务的四特性(ACID)
3.1 A原子性：事务是一个不可分割的单位 因此多个操作同时进行 要么全部成功 要么全部失败 在执行中发生错误会被回滚(Rollback)到事务开始的某个状态 就像没有发生过一样
3.2 C一致性：事务执行后 数据库的完整性不被破坏(其余三种特性的集合)
3.3 I隔离性：多个事务之间是相互隔离 互不干扰的 数据库允许多个并发事务同时对其进行读写修改能力 隔离性防止发生交叉而导致数据不一致
读未提交(Read uncommitted)
读提交(read committed)
可重复读(repeatable read)
串行化(serializable)
3.4 D持久性：一旦事务提交 对数据库的修改是永久的
4.事务之间相互影响的种类
刷脏：MySQL为了保证存储的效率 于是每次将要读写的文件事先存储在缓存池中 对于数据的操作是在缓存池中 而mysql将会定期刷新到磁盘中
4.1 脏读：一个事务读取另一个事务未提交的数据(读取的是未提交数据)
4.2 不可重复读：事务A先后读取同一个数据 但两次结果不一致(读取的是提交以后的结果-结果变了)
4.3 幻读：事务A按照某个条件进行先后两次查询 但查询结果条数不同(读取的是提交以后的数据 条数变了)
4.4 丢失更新
5.事务语法结构：
begin;
start transactton; # 开始一个事务
SQL语句;
rollback; # 事务回滚
commit; # 事务确认 确认后也可以执行 rollback但并不能影响结果
6.事务的隔离级别：在MySQL当中 只有InnoDB存储引擎是支持事务的
6.1 读未提交：一个事务可以读取到另一个事务未提交的修改(产生脏读、幻读、不可重复读问题)-几乎不用
6.2 读已提交：一个事务只能读取另一个事务已提交的修改(产生幻读、不可重复读问题)
6.3 可重复读：同一个事务多次读取相同的数据返回的结果一致(MySQL中默认的隔离级别)MySQL8之前版本的InnoDB可能存在幻读
6.4 串行化：事务串行执行 一次值允许一个事务进行操作(最安全 效率最低)
7.事务日志：提高事务效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录持久的在硬盘的事务日志中，而不用每次都将修改的数据本身持久到硬盘
事务日志采用的是追加方式因此在写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要随机在多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日吃持久之后内存中被修改的数据再后台慢慢刷回磁盘 目前大多数存储引擎都是这样实现的 通常称之为'预写式日志'修改数据需要写两次磁盘
8.扩展知识
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理 必须能在事务处理块中合适的位置放置占位符 如果需要回退就可以退回到某个占位符的位置(保留点)
创建占位符：savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
9.案例
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
MVCC多版本并发控制
1.MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
2.InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
3.案例
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username create_version delete_version
jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
username create_version delete_version
jason 1 2
jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
username create_version delete_version
jason01 2 3
注：
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
内置函数
1.移除指定字符：Trim、LTrim、RTcim
2.大小写转换:Upper、Lower
3.获取左右起始制定个数字符：Left、Right
4.返回读音相似值(对英文效果)：Soundex
5.日期格式：date_format(输出的时间格式)
在MySQL中表示时间格式尽量采用2022-11-11形式
adddate # 增加一个日期
addtime # 增加一个时间
datediff # 计算两个日期差值
注：help 函数名 # 查看帮助信息
6.案例
6.1 读音相似值
客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
6.2 日期
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m'); # 对时间分组并获个数
# 查询时间
select date(sub_time) from blog where Date(sub_time) = '2015-03-01';
select date(sub_time) from blog where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
流程控制
1.if条件语句-存储过程
delimiter $$
create procedure pro_if()
begin
declare i int default 0;
if i = 1 then
select 1;
elseif i = 2 then
select 2;
else
select 7;
end if;
end $$
delimiter ;
set @i=2; # 定义
call pro_if() # 调用输出结果
2.while循环-存储过程
delimiter $$
create procedure pro_while()
begin
declare num int;
set num = 0;
while num < 10 do
select num;
set num = num + 1;
end while;
end $$
delimiter ;
call pro_while() # 调用查看结果
索引
1.定义：数据库表的字段上添加 方便提高查询速度(类似于书的目录 更快的获得所需数据)
2.作用：让获取的数据更具有目的性，从而 提高数据库检索数据的性能
3.分类：在MySQL中也叫做'键' 是存储引擎用于快速找到记录的一种数结构 都可以加快查询的速度
primary key 加快查询速度每个表中有且只有一个且不能有空值
unique key 加快查询速度每个表中可以存在多个且可以为空
index key 加快查询速度
4.索引的优缺点
优点：索引的存在可以加快数据查询
缺点：会减慢数据的增删 并且建立索引会占用磁盘空间的索引文件
5.数据结构
5.1索引底层原理
1. B树(B-树)又叫二叉搜索树：只能有两个分支 所有节点存储一个关键字
优点：提高了查找顺序
缺点：每一个被查找项被查找的概率并不相等(首尾项)效率比较低
B+树：只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树：在树节点添加了通往其他节点的通道 减少查询次数
2.HASH索引
3.全文索引
4.R树索引
MySQL查询数据
1.explain语句
explain select name,countrycode from city where id=1;
2.扫描方式
2.1 全表扫描：没有索引、索引创建有问题、语句又问题时使用(explain语句结果all)
2.2 索引扫描
查看索引扫描类型语句：explain type(访问类型)
2.2.1 索引查询类型
1.index：遍历索引树(all遍历所有数据)
2.range：扫描开始与某一点 返回匹配值域的行(使用索引最低要求)
3.ref：使用唯一索引扫描或者唯一索引前缀扫描 返回匹配某个单独值得记录行
4.eq_ref：使用索引的唯一索引 对于每个索引键值 表中只有一条记录匹配
5.const、system：对查询的部分进行优化 并转化为一个常量时 使用这些类型访问
6.NULL：在优化过程中分解语句 执行时甚至不用访问表或索引
3.数据库索引的设计原则
3.1 选择唯一索引
3.2 经常需要排序、分组和联合操作的字段建立索引
3.3 常作为查询条件的字段建立索引
3.4 尽量使用前缀来索引
posted on
浙公网安备 33010602011771号