数据库复习（四）

数据库复习（四）
1、视图：create view 创建视图
    定义一个名称：
    a、定义对应关系，视图--不是真实的表，只能查数据，不能删除数据
    temp1 <---> select * from score where course_id in (1,2)
    b、调用temp1 :仅仅是把之前一张表的语句拿过来执行
    select * from score where course_id in(1,2)
    c、执行时才获取数据
    d、创建的视图也保存在数据库中：使用的时候当表用，也可以进行拼接关联
2、存储过程
    （创建存储过程类似函数但不是函数）
    create procedure c1
    sql语句
    #mysql客户端
    call c1()#执行存储过程
    a、可写复杂逻辑
    b、参数 in out inout
    c、结果集：select ...
    d、myaql的终止符换成$$（myaql默认以；为终止符），用完之后再修改回；为终止符
3、动态sql
    a、sql是字符串
    b、字符串格式化 ？ execute proc using @p1
4、触发器
    a、为某一个表：
        insert 前后
        update 前后
        delete 前后
    b、OLD，NEW
    c、删除多行，插入多行，更新多行
    #删除之前，将删除的值添加到另外一张表
5、事务 innodb：start transaction 开启一个事务
                commit 与commit之间的操作执行时出现问题时就会事务回滚
6、函数有两类：
    （1）mysql内置函数，定义函数处理数据
    （2）自定义函数
    注：sql不允许返回值，不允许return返回
7、索引：两类功能，约束和加速查找
    约束：
        主键
        外键
        唯一
        普通
        组合
    加速查找
    列索引，创建文件，索引列数据全部拿出
    以二叉树的形式存储
    B-tree索引
    哈希索引（一般用不到因为innodb不支持）
    索引种类：
    普通索引 加速查找
    唯一索引 加速查找，约束列数据不能重复，null
    主键索引 加速查找，约束列数据不能重复，不能null
    组合索引 多列可以创建一个索引文件
    （1）普通索引
        create index 索引名称 on 表（列名）
    （2）唯一索引
        create unique index 索引名 on 表（列名）
        删除索引 drop unique index 索引名 on 表（列名）
    （3）主键索引（功能强大，多）
        不能为空，不能重复
        alter table 表名 add primary key（列名）；
        alter table 表名 drop primary key
        alter table 表名 modify 列名 int，drop primary key；
    （4）组合索引：将n个列组合成一个索引
        普通组合索引：
            无约束
        联合唯一索引：
            由于，两列数据同时不相同，才能插入，不然报错
        查找时遵循一个规则：最左匹配
        name，pwd
        select * from tb1 where name = 'eric' #会走索引
        select * from tb1 where name = 'eric' and pwd = '123' #会走索引
        select * from tb1 where pwd = '123' #不会走索引
        name，pwd，email
        select * from tb1 where name = 'eric' #会走索引
        select * from tb1 where pwd = '123' #不会走索引
        select * from tb1 where email = 'ericc' #不会走索引
        select * from tb1 where name = 'ericc' and pwd = '123' #会走索引
8、覆盖索引
    select * from tb where nid = 1
    #先去索引中找
    #再去数据表中找
    select nid from tb where nid < 10
    #先去索引中找
    --情况应用上索引，并且不用去数据表中操作，覆盖索引0
    --只需要在索引中就能获取到数据时
9、合并索引
    nid     name（单独索引）   email（单独索引）   pwd
    select * from tb where name = 'eric'
    select * from tb where email= 'eric@.com'
    select * from tb where name ='eric' and email='eric@.com'

    nid     name（组合索引）   email（合并索引）   pwd
    #最左前缀
    select * from tb where name = 'eric'
    select * from tb where email= 'eric@.com'  #####无法满足#####
    select * from tb where name ='eric' and email='eric@.com'
10、执行计划 - 相对比较准确的表达出当前SQL运行状况
    是否走索引，不走索引
    explain SQL语句
    （1）explain SQL语句
        type：ALL    - 全数据扫描
        type：index  - 全索引扫描
        但效率都不高
    （2）limit
        select * from tb where email= 'eric@.com'
        select * from tb where email= 'eric@.com' limit 1；
    ----SQL： ALL、index，都是有优化的余地的----
    （3）range对索引列进行范围查找 select * from tb1 where name < 'eric';
        between and
        in
        > >= < <= 操作
        注意：!= 和 > 符号
    （4）如何命中索引
        注：用like的额时候%在前面不走索引
            转船也不会走索引
            数据类型不一致也不会走索引
            != 和 > 也不走索引
    （5）指定列前几个字符创建索引
         text，blob必须要执行长度