视图，触发器，事务，存储过程，函数与流程控制，索引

 

一、视图

1、什么是视图

   虚拟表：在硬盘中没有的，通过查询在内存中拼接的表

   视图：通过查询得到一张虚拟表，保存下来，下次可直接使用

 

2、为什么要用视图

   如果要频繁使用一张虚拟表，可以不用重复查询

 

3、如何用视图

create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;

 

4、删除视图

drop view teacher2course;

 

5、强调

1、在硬盘中，视图只有表结构文件(.frm)，没有表数据文件(.idb); 其在后台对应的是一条sql语句

2、视图通常是用于查询，尽量不要修改视图中的数据

 

二、触发器

1、触发器：在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

 

2、为何要用触发器？

   触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码

 

3、创建触发器语法

# 针对插入

create trigger tri_after_insert_t1 after insert on 表名 for each row  #after

begin  #begin end 在sql中用于标志同一执行级别的代码；相当于python中的缩进

sql代码。。。

end

 

create trigger tri_before_insert_t2 before insert on 表名 for each row  #before

begin

    sql代码。。。

end

 

# 针对删除

create trigger tri_after_delete_t1 after delete on 表名 for each row

begin

    sql代码。。。

end

 

create trigger tri_before_delete_t2 before delete on 表名 for each row

begin

    sql代码。。。

end

 

# 针对修改

create trigger tri_after_update_t1 after update on 表名 for each row

begin

    sql代码。。。

end

 

create trigger tri_before_update_t2 before update on 表名 for each row

begin

    sql代码。。。

end

 

#删除触发器

drop trigger tri_after_insert_cmd;

 

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 $$   #修改sql中的默认分隔符，从；改成$$

create trigger tri_after_insert_cmd after insert on cmd for each row   #触发器名称要尽可能详细如例

begin

    if NEW.success = 'no' then   # NEW为mysql对新插入记录的封装名； 如果不用mysql的触发器，可以在自己的应用程序中加if判断

        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);

    end if;    # mysql中固定用法

end $$         # $$表一行语句终结

delimiter ;    # 将sql中的默认分隔符改回；

 

三、事务（重要）

1、什么是事务

   开启一个事务可以包含一些sql语句，这些sql语句要么同时成功；要么一个都别想成功，称之为事务的原子性

 

2、事务的作用：转账等，屏蔽因网络传输部分失效而带来的影响

 

3、如何用 （以下为运行逻辑，非代码）

create table user(

id int primary key auto_increment,

name char(32),

balance int

);

 

insert into user(name,balance)

values

('wsb',1000),

('egon',1000),

('ysb',1000);

 

try:

    update user set balance=900 where name='wsb'; #买支付100元

    update user set balance=1010 where name='egon'; #中介拿走10元

    update user set balance=1090 where name='ysb'; #卖家拿到90元

except 异常:

    rollback;   #如果异常，回滚到前一个状态，即balance都为1000

else:

commit;   #如果无异常，修改数据库（硬盘上的数据）

 

四、存储过程（重要）

#在mysql中函数是不能单独使用的，必须放在sql语句中使用；但存储过程是可以单独使用的

1、存储过程：存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

 

2、三种开发模型

    a、（如果不考虑非技术因素，各方面效率最高的选择；但考虑到实际情况，一般不被选择）

        应用程序：只需要开发应用程序的逻辑

        mysql：编写好存储过程，以供应用程序调用

        优点：开发效率，执行效率都高

        缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差

 

    b、（与c的思路一致，会被使用）

        应用程序：除了开发应用程序的逻辑，还需要编写原生sql

        mysql：

        优点：比方式1，扩展性高(非技术性的)

        缺点：

            1、开发效率，执行效率都不如方式1

            2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题

 

    c、（考虑到非技术因素，最常见的选择）

        应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM（object relationship matching）

        mysql：

        优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处

        缺点：执行效率连方式2都比不过

 

3、创建存储过程

delimiter $$

create procedure p1(   #创建存储过程p1

    in m int,    #mysql中参数必须先规定类型和用途（in，out，inout）

    in n int,

    out res int  #res用于接收返回值

)

begin

    select tname from teacher where tid > m and tid < n;

    set res=0;

end $$

delimiter;

 

4、如何用存储过程

a、直接在mysql中调用

set @res=10   #mysql中变量的定义要用@abc的形式

call p1(2,4,@res);#调用p1存储过程

 

select @res;  #查看结果

 

b、在python程序中调用

import pymysql

 

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    charset='utf8',

    database='db42'

)

 

cursor=conn.cursor(pymysql.cursors.DictCursor)

cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10  #pymysql帮助对传入变量进行以上变形

print(cursor.fetchall())

cursor.execute('select @_p1_2;')  #查看返回值，确认执行结果

print(cursor.fetchone())

 

cursor.close()

conn.close()

 

5、事务的使用 （事务+存储过程）

delimiter //

create PROCEDURE p5(

    OUT p_return_code tinyint

)

BEGIN

    DECLARE exit handler for sqlexception  #如果出现错误，执行

    BEGIN

        -- ERROR

        set p_return_code = 1;

        rollback;

    END;

 

    DECLARE exit handler for sqlwarning    #如果出现警告，执行

    BEGIN

        -- WARNING

        set p_return_code = 2;

        rollback;

    END;

 

    START TRANSACTION;                     #事务的应用

        update user set balance=900 where id =1;

        update user123 set balance=1010 where id = 2;

        update user set balance=1090 where id =3;

    COMMIT;

 

    -- SUCCESS

    set p_return_code = 0; #0代表执行成功

 

END //

delimiter ;

 

#在python中调用存储过程

import pymysql

 

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    charset='utf8',

    database='db44'

)

 

cursor=conn.cursor(pymysql.cursors.DictCursor)

cursor.callproc('p6',(100,)) #@_p5_0 = 100

cursor.execute('select @_p6_0')

print(cursor.fetchone())

 

cursor.close()

conn.close()

 

五、函数

1、强调：mysql内置的函数只能在sql语句中使用

mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

 

2、补充

a、select * from s1 \G   #表字段太多（字段行显示不全）时，用\G将表竖着显示出来： row1 哪些字段： 对应的内容，row2。。。

b、视图、触发器、事务、存储过程、函数、流程控制皆是在库下面建立

 

六、流程控制 （if，while，case）

#case

select

case

when name = 'egon' then

    name

when name = 'alex' then

    concat(name,'_BIGSB')

else

    concat(name,'_SB')

end

 

from emp;

 

七、索引

1、为什么要用索引

   对于一个应用来说，对数据库的读写比例基本上是10:1,即读多写少

   而且对于写来说极少出现性能问题，大多数性能问题都是慢查询

   提到加速查询，就必须用到索引

 

2、什么是索引

   索引就相当于书的目录，是mysql中一种专门的数据结构，称为key（primary key，unique，index key）

   索引的本质原理就是通过不断地缩小查询范围，来降低io次数从而提升查询性能

   强调：一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据（同一种方式）

 

3、索引的影响（先有数据，后有索引；索引，亦占硬盘空间）

   a、在表中有大量数据的前提下，创建索引速度会很慢

   b、在索引创建完毕后，对表的查询性能会大幅度提升，但是写性能会降低

 

4、B+树：只有叶子节点才存放真实数据，其他的（根节点、枝节点）都是虚拟数据

 

   a、一次IO走一个block块（磁盘块）

   b、浅蓝-block块； 深蓝-数据项； 黄-指针

   c、为降低IO次数-》降低树的高度-》数据项越小越好（block块大小一定的情况下，数据项越小，存放的数据量最多，占用的树叶少）-》采用占空间少的做索引（比如id）

 

5、索引的最左匹配特性（从左到右一次匹配）

g、应该对哪些字段做索引：

1、应该对数据量小的字段做索引（数据量小，比对查询的快）

2、应该对区分度高的字段做索引

3、索引字段不要参与运算

 

6、聚集索引（primary key）：组织数据时就按照此索引组织

   特点：叶子节点存放的一整条数据的对应的关系（聚集在一起）

 

7、辅助索引（unique，index）

   特点：如果是按照这个字段创建的索引，那么叶子节点存放的是：{名字：名字所在那条记录的主键的值（名字与其在聚集索引中存放的数据的绑定关系）}

根据这个关系再去聚集索引里面找对应的值。

        innodb的索引存放在表数据里（.idb）

    a、覆盖索引：只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

    select name from user where name='egon';

    b、回表操作

    select age from user where name='egon';

 

8、补充： 权限管理

   a、授权

   grant all on *.* to 'egon' @'192.168.12.%' identified by '123';   #all指的是授予grant以外的全部权限

   flush privileges; #立即刷新权限

   exist

   b、删除权限

   revoke select on db1.* from 'egon'@'%';

 

9、其他：

   a、对区别度低的数据不要建索引，且索引不是越多越好

   b、注意范围问题，或者说条件不明确的问题 （条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like）

   c、mysql中对 a and b and c。。的形式，会先从有索引或区分度高的数据入手开始查询，以减少查询范围

   d、mysql中对 a or b or c。。的形式，会从左至右依次查询

   e、索引列不要参与计算，如果非要运算 a\12=3 ===> a=3\12

   f、联合索引的原则：

     区别度高、范围小的放左边，区别度低、范围大的放右边，按从左到右的顺序查询

     （email, name, gender, id）：可以搜(email)、(email,name, gender)等（含email），不能搜（id）等。