数据库SQL语句执行知识

视图

1.什么是视图

        执行SQL语句得到的结果是一张虚拟表，我们如果要对该表做一些操作或需要频繁的使用到这张虚拟表时可以先将该虚拟表取个名字保存起来，保存后这张虚拟表就称之为“视图”

2.创建视图语法结构

'''create view 视图名 as SQL语句;'''
 
# 案例展示:拼接用户表和员工表保存成user_combine_ staff视图
 
create view user_combine_ staff as 
select * from user inner join staff on staff.sid = user.staff_id;

3.知识点总结

∙  视图中的数据不保存在硬盘中，只保存视图的表结构文件

∙  修改视图其最终就是对原表的数据进行修改所以视图通常用于数据查询，尽量不要修改视

     图中的数据

∙  如果在原表中新增或删除了数据则视图中的数据也会对应的发生变化

触发器

1.触发器的作用

        触发器的执行不是由程序员指定或手动执行，而是数据库在进行特定的操作时自动触发的，如对数据库表数据进行增、删、改(增前、增后、改前、改后、删前、删后)时

2.触发器语法结构

        Tips：在触发器内部的SQL语句中需要用到分号，而分号又是SQL语句默认的结束符，所以为了写完整的触发器代码需要临时修改SQL语句默认结束符：delimiter 

delimiter ;; # 将结束符改成;;避免冲突
 
create trigger 触发器名(before/after/insert/update/delete) on 表名 
for each row 
begin
    SQL语句
 
end;;
delimiter ; # 结束后得把结束符改回去
 
drop trigger 触发器名; # 删除触发器

3.知识点与注意点

∙  before :触发器在DML修改数据之前执行

∙  after    :触发器在DML修改数据之前执行

∙  insert  :触发器在DML插入数据之后执行

∙  update:触发器在DML更新数据之后执行

∙  delete  :触发器在DML删除数据之后执行

        DML是Data Manipulation Language的缩写，意思是数据操纵语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

存储过程

1.什么是存储过程

        存储过程是一组为了完成特定功能的SQL语句集，编译好后存储在数据库中，用户通过调用指定存储过程的名字来执行它 如果需要参数则带参数执行。

存储过程 类似于python中的函数。

2.语法结构

 
delimiter $$ -- 定义临时结束符
 
CREATE PROCEDURE 存储过程名(输入符 参数名 参数类型)
begin  -- 开始符
    SQL语句;
end $$ -- 结束符
delimiter ; -- 改回结束符
 
call 存储过程名  -- 调用
 
set @变量名  -- 变量赋值

3.知识总结

∙  show create procedure 存储过程名;        查看存储过程具体信息

∙  show procedure status;                           查看所有存储过程

∙  drop procedure 存储过程名;                    删除存储过程

∙  IN 输入参数：只能往存储过程中传入这个参数不能被返回出去

∙  OUT输出参数：表示这个参数可以被返回

∙  INOUT输入输出参数：这个参数可以传入也可以被返回出去

事务

1.理解事物的四大特性(ACID)

        事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。

∙  原子性：

        事务是一个完整的操作，当有任务完成了有的没完成时则整个操作失败必须都成功才能真正的完成两种情况：事务提交（commit）和事务回滚（rollback），如银行转账必须一个账户成功扣除一个账户成功到账

∙  一致性：

        事务按照预期的效果执行，数据达到预期的结果，如我给张三转50给李四转100那结果我的账户得扣150张三得加50李四得加100，如果数据不是这样就是没有到达一致性

∙  隔离性：

        事务的隔离性是当多个用户并发访问数据库时，数据库给每一个用户开启的事务都是独立的不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

∙  持久性：

        持久性的体现就是数据一旦提交之后，那么对于数据的改变就是永久的。如我们提交数据之后，我的账户就永久减少了150元，李四的账户就永久增加了100元。

2.具体案例理解

'''建用户账户表'''
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
'''插入数据'''
insert into user(name,balance)
values
('张三',1000),
('李四',1000),
('王五',1000);

# 修改数据之前先开启事务操作
start transaction;
 
# 修改操作
update user set balance=900 where name='张三'; #买支付100元
update user set balance=1010 where name='李四'; #中介拿走10元
update user set balance=1090 where name='王五'; #卖家拿到90元
 
# 回滚到上一个状态
rollback;
 
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
 
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
    update user set balance=900 where name='张三'; #买支付100元
    update user set balance=1010 where name='李四'; #中介拿走10元
    update user set balance=1090 where name='王五'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

3.事务处理高频关键字

∙  事务 (sransaction)

∙  提交 (commit)

∙  保留点(savepoint)

        为了让事务回退部分事务处理所以在事务处理块中合适的位置放置占位符 这样如果要回退到某个处理块时通过对应的占位符就可以   创建占位符：savepoint 占位符名

∙  回退 (rollback)

        用来回退到某个占位符    回退到占位符地址：rollback to 占位符名

小Tips：保留点在执行 rollback或commit之后自动释放

4.事务知识点拓展了解

4.1 隔离级别

        在SQL标准中定义了四种隔离级别，每一种级别规定了一个事务中所做的修改，InnoDB事务支持所有隔离级别 默认为（repeatable read：可重复读）

∙  未提交读(read uncommitted)：

        当一个事务处理了数据但没有提交时 别的事务也都是可以见的，事务可以读取未提交的数据 称为“脏读”

∙  提交读(read committed):

        一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，事务读取的是其他事务提交后的最新数据避免了“脏读” 具体使用行锁实现，这种级别也叫做“不可重复读”

∙  可重复读(repeatable read):

        采用该隔离级别时，数据库在读取的记录上加S锁，验证是否可以读取数据，直到事务提交，锁才能释放。其他用户可以读取数据，但是不能修改相同的数据。在采用可重复读隔离级别查询的过程中，需要对读取的记录加S锁，可以保证读取的记录是提交的，同时可以保证多次读取的记录是一致的，可以解决“脏读”不能解决“幻读”

∙ 可串行读 (serializable):

        强制事务串行执行，事务隔离最高级别但会使数据库的并发性能大幅下降，现实中很少使用

脏读：事务修改了数据但还未提交，其他事务读取到了这些未提交的数据

不可重复读：一个事务在执行时，另一个事务提交了新数据因此此事务前后两次读取到的数据结果会不一样

幻读：当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻觉，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题。

4.2 事务日志

        存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘

        事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
        事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

4.3 MVCC多版本并发控制

        MVCC全称(Multi-Version Concurrency Control)在MySQL和InnoDB中实现数据库并发性能，用更好的方式去处理读与写冲突，做到即使有读写冲突时做到不加锁非阻塞并发读。

◯  MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

◯​​  InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
        一个列保存了行的创建时间
        一个列保存了行的过期时间(或删除时间)   本质是系统版本号​​​​​  

ps：每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

内置函数

1.SQL语句常用内置函数

        MySQL中提供了数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等等

数学函数：abs()、mod(x,y)、sqrt()

字符串函数：length(str)、upper(str)、lower(str)、left(s,n)、right(s,n)

日期和时间函数：new()、date_format()、adddate()、addtime()、datediff()

条件判断函数：if()、ifnull()、nullif()

系统信息函数：version()、connection_id()

加密函数：md5(str)、sha1(str)、sha2(str)

2.内置函数使用案例

'''常见内置函数举例'''
# trim、ltrim、rtrim 移除指定字符
SELECT TRIM('  bar   ');
-> 'bar'
SELECT TRIM(LEADING 'x' FROM 'xxxxbarxxx');
-> 'barxxx'
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
# soundex 返回读音相似值对英文有效
SELECT soundex('Hello');
-> 'H400'
mysql> SELECT soundex('Quadratically');
-> 'Q36324'
'''
其实很多和python中的内置函数一样：
Lower、Upper ：大小写的转换
Left、Right：获取左右启始指定个数字符
当不知道函数具体使用时还可以获取帮助信息：hepl 函数名
'''

流程控制、循环控制

1.SQL流程控制之if条件语句

delimiter $$ # 设置临时结束符
create procedure proc_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 ;

2.SQL循环控制之while循环语句

delimiter $$ # 设置临时结束符
create procedure proc_while() # 创建存储过程
begin
    declare num int;
    set num = 0;
    while num < 10 do
        select
            num ;
        set num = num + 1;
    end while;
end $$
delimiter ;

3.知识点拓展补充

∙  SQL语句中的流程控制与python中的流程控制逻辑一样

∙  SQL语句的if语句中

        then：表示满足条件后 用法如python中if语句的 :

        elseif：表示或者满足这个条件 用法如python中的 elif

        end if：SQL语句中用if语句得用endif结束

∙  SQL语句的while语句中

        while：条件表达式

        do：执行循环体代码

        end while：结束while循环

索引与慢查询优化

1.理解MySQL索引

        数据库索引就想书的目录用来提高数据库表的数据访问速度，索引的使用通常使用B树和B+树（MySQL常用的索引就是B+树）

∙  主键索引(primary key)：在数据库里面，所有行数都会按照主键进行排序

∙  唯一索引(unique key)：约束作用（constraint)，规范数据的唯一性，但同时也在这个key上建立了一个index

∙  聚集索引(index)：只能加快数据查询 本身没有任何的额外限制

3.索引底层原理(树)

        B树也称B-树,它是一颗多路平衡查找树。我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉树