11-2 数据库高级部分

待我称王封你为后i 2023-08-17 15:24 59阅读 0赞

*  1 视图
    
     *  1.1 什么是视图
     *  1.2 为什么使用视图
     *  1.3 使用方法
 *  2 触发器
    
     *  2.1 什么是触发器
        
         *  2.1.1 时间点
         *  2.1.2 事件
     *  2.2 有什么用
 *  3 事务(重点)
    
     *  3.1 什么是事务
     *  3.2 事务的特点
     *  3.3 如何使用
     *  3.4 修改隔离级别
 *  4 存储过程(优先掌握)
    
     *  4.1 什么是存储过程
     *  4.2 用来干什么
     *  4.3 三种数据处理方式
     *  4.4 使用
 *  5 函数
 *  6 备份与恢复
 *  7 流程控制(了解)
 *  8 正则匹配

# 1 视图 #

## 1.1 什么是视图 ##

本质上就是一个虚拟的表,看得见 但是实际不存在.

它是将一个查询语句的结果作为视图的数据进行展示.

## 1.2 为什么使用视图 ##

使用视图,可以帮助我们解决以下两个问题.

场景1:

我们希望某些查询语句只能看到某个表中的一部分记录,就可以使用视图

场景2:

简化sql语句的编写

## 1.3 使用方法 ##

# 语法:
    create [or replace] view vname as 查询语句;
    # or replace 表示:
    # 如果视图已经存在了,就替换里面的查询语句;
    create or replace view man_kind as select * from emp where sex='男';
    
    # 修改视图
    alter view vname as 新的语句;
    alter view man_kind as select * from emp where sex='女';
    
    # 删除视图
    drop view vname;
    
    # 查看视图结构
    desc vname;
    
    mysql> desc man_kind;
    +-------+-------------------+------+-----+---------+-------+
    | Field | Type              | Null | Key | Default | Extra |
    +-------+-------------------+------+-----+---------+-------+
    | id    | int(11)           | NO   |     | 0       |       |
    | name  | char(10)          | YES  |     | NULL    |       |
    | sex   | enum('男','女')   | YES  |     | NULL    |       |
    +-------+-------------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
    
    # 查看创建语句
    show create view vname;
    
    # 限制可以查看的记录
    create table salarys(id int,name char(10),money float);
    insert into salarys values(1,"张三丰",50000),(2,"张无忌",40000);
    # 创建视图  限制只能查看张无忌的工资
    create view zwj_view as select *from salarys where name = "张无忌";
    # 此处想要限制查看,用户在查询时,应该从视图查询数据.不能从工资表查询.
    
    
    
    # 简化sql语句的编写
    # 例如 可以将多个表之间的关系做成一个视图,方便后续查询.
    create table student(
      s_id int(3),
      name varchar(20), 
      math float,
      chinese float 
    );
    insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);
    
    create table stu_info(
      s_id int(3),
      class varchar(50),
      addr varchar(100)
    );
    insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');
    # 查询班级和学员的对应关系做成一个视图  
    create view class_info as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;
    
    select *from class_info;

注意: 修改视图,也会引起原表的变化.我们不能直接修改视图,视图仅用于查询数据.

# 2 触发器 #

## 2.1 什么是触发器 ##

触发器,是一段与某个表相关的sql语句,会在某个时间点满足某个条件后自动触发执行

其中有两个关键因素:①时间点 ② 事件

### 2.1.1 时间点 ###

事件发生前 before

事件发生后 after

### 2.1.2 事件 ###

update delete insert

触发器自动的包含两个对象,分别在不同的事件中可用.

old 对象 在 update 和delete 事件中可用

new 对象 在update 和insert 事件中可用

## 2.2 有什么用 ##

可以用于:当表的数据被修改时,自动记录一些数据,执行一些sql语句.

语法:

#                 事件名称  事件发生的时间   事件
    create trigger    t_name    t_time        t_event    on tbname for each row
    begin 
    # sql语句....;
    end;

案例:

#准备数据
    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
    );
    
    #需求： 当插入cmd表 的时候 如果执行状态时失败的 那么将信息插入到errlog中
    
    # 思路:
    # 创建一个触发器 叫做 cmd_insert
    # 触发器会在插入数据到cmd表后执行
    # 当插入的记录的success为no 时,自动插入记录到errlog中
    
    #实现:
    # 将结束符设置为 |.为什么要修改呢,因为mysql遇到分号自动就提交了,我们需要重定义 行结束符
    delimiter |
    create trigger cmd_insert after insert on cmd for each row
    begin
    if new.success = 'no' then   # 因为是insert事件,可以使用new对象
        insert into errlog values(null,new.cmd,new.sub_time); # sql语句需要;结尾
    end if; # 上面有if,一定要end if 结束
    end |  # 上面讲结束符设置为了|,此时sql语句结束
    # 结束时,需要还原之前的结束符
    delimiter ; # 注意空格
    
    
    # 上面说了创建触发器,怎么删除和查看触发器呢
    # 删除触发器
    drop trigger cmd_insert;
    
    # 查看所有的触发器
    show triggers;
    
    # 查看某个触发器的创建语句
    show create trigger t_name;

# 3 事务(重点) #

## 3.1 什么是事务 ##

事务就是一系列的sql语句的组合,是一个整体.

## 3.2 事务的特点 ##

事务有4个特点.

① 原子性

指的是 这个事务中的;sql语句是一个整体,不能拆分,要么都执行,要么都失败.不能只执行一部分.

② 一致性

事务执行结束后,表的关联关系一定是正确的,不能发生数据错乱的情况

③ 隔离性

事务之间相互隔离,即使操作了同一个表,数据也不会互相影响.

本质就是加锁.

④ 持久性

事务执行成功后,数据将永久保存,无法恢复.

## 3.3 如何使用 ##

在官方提供的cmd的mysql客户端下,事务是默认开启的,会将一条sql语句作为一个事务,自动提交.执行完成后,数据无法改变.

# 开启事务
    start transaction;
    # sql 语句...;
    # sql 语句...;
    rollback; # 回滚操作  就是撤销没有提交之前的所有的操作
    # sql 语句...
    commit; # 提交事务,一旦提交就持久化了

上述语法中,有回滚操作 和提交操作.  
**那么什么时候执行呢**  
当一个事务执行过程中,出现了异常情况时,此时应**执行回滚**操作  
当事务中所有语句都执行成功时,此时应该**执行提交**操作

# 回滚操作 可以回滚到最开始的状态,也可以回滚一部分此时我们可以使用保存点. 
    # 保存点可以使用savepoint来指定.
    
    # 保存点示例
    start transaction;
    update account set money = money - 100 where name = "一只穿云箭";
    savepoint a;
    update account set money = money - 100 where name = "一只穿云箭";
    savepoint b;
    update account set money = money - 100 where name = "一只穿云箭";
    savepoint c;
    
    select * from account;
    
    # 回滚至某个保存点 
    rollback to 保存点名称
    
    read committed

## 3.4 修改隔离级别 ##

隔离级别有以下几种:

1.  读未提交: 效率最高 数据不安全
2.  读已提交:  
    
3.  可重复读 默认
4.  串行 : 效率最低,数据安全

补充知识点:

设置系统内置变量 和 用户自定义的变量

修改全局的  
     set global transaction isolation level read committed;
     或者:
     set @@tx_isolation = "asasasasas-read";
     修改局部
     set session transaction isolation level read committed;
     
    
     @@系统内置变量
     @表示用户自定义的变量

# 4 存储过程(优先掌握) #

## 4.1 什么是存储过程 ##

存储过程是任意的sql语句的组合,被放到某一个存储过程中,类似于一个函数,它有一个函数名,有参数,有函数体.

## 4.2 用来干什么 ##

存储过程 其中可以包含任何的sql语句,逻辑处理,事务处理,所有的我们学过的sql语句都可以放到里面.

## 4.3 三种数据处理方式 ##

① 应用程序只关注业务逻辑,所有与数据相关的逻辑封装到mysql中

优点: 应用程序要处理的事情变少了,可以减少网路传输

缺点: 增加了人力成本,沟通成本,降低了整体的开发效率

② 应用程序既要处理业务逻辑,还要自己编写sql语句

优点: 降低了沟通成本,人力成本

缺点: 网路传输增加,sql语句的编写非常繁琐,易出错

③ 通过ORM框架,对象关系映射,自动生成sql语句并执行

优点: 不需要再编写sql语句,明显的提升开发效率

缺点: 不够灵活,应用程序开发者和数据库完全隔离了,可能导致仅关注上层开发,而不清楚底层原理.

## 4.4 使用 ##

# 语法:
    create procedure p_name(p_type p_name p_data_type)
    begin
    # sql...
    end
    
    # p_type  参数的类型  in输入  out输出 inout即可输入也可输出
    # p_name 参数的名字
    # p_data_type  参数的数据类型   如  int float
    
    
    # 注意: out参数必须是一个变量   不能是值
    
    
    # 案例:
    # 创建一个存储过程
    delimiter |
    create procedure add1(in a float,in b float,out c float)
    begin
    set c = a + b;
    end |
    delimiter ;
    
    # 调用
    set @res = 0  #设置初始值
    call add1(100,10,@res);
    
    # 删除
    drop procedure 名称;
    
    # 查看
    show create procedure 名称;
    
    # 查看全部 day41库下的所有过程
    select name from mysql.proc where db='day41' and type = 'PROCEDURE';
    
    # 创建一个过程,执行事务,并对异常进行捕获
    delimiter |
    create procedure transfer2(in aid int,in bid int,in m float,out res int)
    begin 
        declare exit handler for sqlexception
        begin
            # 异常处理代码
            set res = 99;
            rollback;
        end;
        
        start transaction;
        update account set money = money - m where id = aid;
        update account set money = moneys + m where id = bid;
        commit;
        set res = 1;
    end|
    delimiter ;

# 5 函数 #

mysql自带有几个函数.简单几个用过的,列出如下

current_date()  当前日期
    current_time() 当前时间
    now()  当前时间
    concat(s1,s2,...)  拼接字符串
    length(字符串)  计算长度
    md5(str)   字符串经过md5算法计算得到一个加密字符串
    password(str)    字符串经过加密计算得到一个密码字符串

自定义函数

create function func_name(参数 类型)
    函数体(不能有sql语句)
    returns 返回值的类 
    return 返回值
    
    
    # 示例
    delimiter |
    create function add2(a int,b int)
    returns int
    return a+b |
    delimiter ;
    
    
    # 查看创建语句
    show create function name;
    
    # 查看所有函数的状态
    show function status;
    
    # 查看某个库下所有函数
    select name from mysql.proc where db='库名称' and type ='FUNCTION';
    
    # 删除
    drop function name;

# 6 备份与恢复 #

# 备份
    # 第一个是数据库,后面的全是表名
    mysqldump -uroot -p111 day41 student teacher > data.sql
    
    # 备份多个数据库
    mysqldump -uroot -p --databases day41 day40 > data.sql
    # 指定 --databases 后导出的文件包含创建库的语句,而第一种方式不包含.
    
    
    # 备份所有的数据库,注意-的个数
    mysqldump -uroot -p111 --all-databases>all.sql
    
    # 自动备份
    Linux crontab 指令可以定时执行某一条指令
    
    # 恢复数据
    # 分为两种情况
    # 1. 没有登录mysql时 ,我们可以使用下面的命令执行还原操作
    mysql < 文件的路径
    # 2. 已经登录了mysql
    source 文件路径
    
    注意: 如果导出的sql中没有包含选择数据库的语句,需要手动加上,否则还原数据库的时候会失败.

# 7 流程控制(了解) #

主要是if ... then... ;end if;

delimiter | 
    create procedure showjishu()
    begin
    declare i int default 0;
    aloop: loop
    set i = i + 1;
    if i >= 101 then leave aloop;end if;
    if i % 2 = 0 then iterate aloop; end if;
    select i;
    end loop aloop;
    end |
    delimiter ;

# 8 正则匹配 #

语法

select * from tbname where  字段名 regexp '表达式';

注意事项:

在mysql中的正则匹配不能使用类似 \\w 这样的符号,所以我们需要找其他符号 来代替.

转载于:https://www.cnblogs.com/chenych/p/11227789.html