mysql——触发器、视图、索引——前期整理笔记00

今天药忘吃喽~ 2023-10-02 08:48 16阅读 0赞

一、触发器
    
    触发器是由事件来出发某个动作。这些事件包括insert语句、update语句和delete语句。
    
    当数据库系统执行这些事件时，就会激活触发器执行相应得动作。
    
    触发器是有insert、update、delete等事件来触发某种特定动作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。
    
    这样做可以保证某些操作之间的一致性.
    
    
    1、创建只有一个执行语句的触发器
    
    语法格式：
    
    
             create trigger 触发器名 before|after 触发事件
    
             on 表名 for each row 执行语句
    
    
    触发器名参数指要创建的触发器的名字；
    
    before和after参数指定了触发器执行的时间；
    
       before指在触发事件之前执行触发语句；
    
       after指在触发事件之后执行触发语句；
    
    触发事件指触发的条件，其中包括insert、update、delete；
    
    表名指触发事件操作的表的名称；
    
    for each row表示任何一条记录上的操作满足触发条件都会触发该触发器；
    
    执行语句指触发器被触发后执行的程序；
    
    
    举例：
    
    create trigger dept_trig1 before insert
    
    on department for each row
    
    inert into trigger_time values(now());
    
    注释：当向department表中执行insert操作时，数据库系统都会在insert语句执行之前向trigger_time表中插入当前时间；
    
    
    2、创建有多个执行语句的触发器
    
    
    语法格式：
    
    
             create trigger 触发器名 before|after 触发事件
    
             on 表名 for each row 
    
    
             begin
    
                    执行语句列表
    
             end
    
    
    
    
    执行语句列表表示需要执行的多个执行语句的内容，不同执行语句之间用分好隔开；
    
    
    注意：
    
    一般情况下，MYSQL默认的是以“;”作为结束执行语句。在创建触发器的过程中，需要用到“;”为了解决这个问题可以用delimiter语句
    
    如“delimiter &&”可以将结束符号变为“&&”。当触发器创建完成之后，可以用命令“delimiter ;”来将结束符号变为“;”。
    
    
    举例：
    
    
    delimiter &&
    
    create trigger dept_trig2 aftrer delete
    
    on department for each row
    
    begin
           insert into trigger_time values('21:01:01');
           insert into trigger_time values('22:01:01');
    end
    
    &&
    
    delimiter;
    
    
    注意：在MYSQL中，一个表在相同的触发时间，只能创建一个触发器；
    
    
    3、查看触发器
    
    (1)show triggers语句查看触发器信息
    
    语法格式：show triggers;
    
    注释：其结果显示了所有触发器的基本信息，但是该语句无法查询指定的触发器信息，只能查询所有的触发器信息，适合触发器较少时使用
    
    
    
    (2)在triggers表中查看触发器信息
    
    在MYSQL中，所有触发器的定义都存储在information_schema数据库下的triggers表中，
    
    查询triggers表，可以查看到数据库中所有触发器的详细信息；
    
    语法格式：select * from information_schema.triggers;
    
    
    同时该方法可以查询指定触发器的详细信息。
    
    语法格式：select * from information_schema.triggers where trigger_name='触发器名';
    
    
    
    4、触发器的使用
    
    before在触发事件之前执行，after在触发事件之后执行；
    
    注意：触发器中不能包含start transaction、commit或者rollback等关键词，也不能包含call语句。
    
    
    
    5、删除触发器
    
    语法格式：drop trigger 触发器名;
    
    
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    
    create trigger 触发器名  before| after 触发事件
    on 表名 for each row
    
    执行语句；
    
    
    
    delimiter &&
    create trigger 触发器名  before| after 触发事件
    on 表名 for each row
      begin
            执行语句列表
      end
      &&
    delimiter;
    
    
    触发事件是指触发条件，包括insert、update、delete;
    
    表名指触发事件操作的表的名称；
    
    
    =====================================================================================================================
    =====================================================================================================================
    
    create table employee ( num int(50),
                            d_id int(50),
                            name varchar(50),
                            age int(50),
                            sex varchar(50),
                            homeadd varchar(50)
                           );
    
    insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
    insert into employee values(2,1001,'lisi',24,'nv','hunan');
    insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
    insert into employee values(4,1004,'aric',15,'nan','yingguo');
    
    select * from employee;
    
    
    
    create table department ( d_id int(50),
                              d_name varchar(50),
                              functione varchar(50),
                              address varchar(50)
                            );
    
    insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
    insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
    insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
    
    select * from department;
    
    ==========================================================================================
    
    select * from employee;
    
    select * from department;
    
    ==========================================================================================
    
    
    create table trigger_time ( exec_time varchar(50)
                              );
    
    
    select now();
    
    
    
    create  trigger dept_trig1 before insert on department for each row insert into trigger_time values ( now() );
    
    
    
    delete from department where d_id = 1003;
    
    
    select * from department;
    
    
    select * from trigger_time;
    
    insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
    
    
    select * from trigger_time;
    
    =================================================================================================================
    
    select * from employee;
    
    
    select * from department;
    
    create table trigger_time1 ( exec_time varchar(50)
                              );
    
    select now();
    
    select * from trigger_time1;
    
    
    
    delimiter &&
    create  trigger dept_trig2 after delete on department for each row 
    begin
          insert into trigger_time1 values ( now() );
          insert into trigger_time1 values ( now() );
    end 
    &&
    delimiter;
    
    select * from trigger_time1;
    
    delete from department where d_id = 1003;
    
    select * from trigger_time1;
    
    =================================================================================
    
    =================================================================================
    查看触发器
    
    1、查看数据库中所有触发器的信息：
    
    show triggers;
    
    
    2、在triggers表中查看触发器信息
    
    mysql中所有触发器的定义都存在information_schema数据库下的triggers表中，查询triggers表，可以查询数据库中所有触发器的详细信息
    
    select * from information_schema.triggers;   /*查询所有*/
    
    
    select * from information_schema.triggers where trigger_name = 'dept_trig1';     /*单个指定查询*/
    
    
    注意：在激活触发器时，对触发器中的执行语句存在一些限制。而且触发器有问题，会阻止程序向下执行，而且数据不能回滚。
    
    
    3、删除触发器
    
    drop trigger 触发器名；
    
    
    
    select * from information_schema.triggers;
    
    
    drop trigger dept_trig1;
    
    drop trigger dept_trig2;
    
    ================================================================================================================================
    ================================================================================================================================
    ================================================================================================================================
    ================================================================================================================================
    ================================================================================================================================
    ================================================================================================================================
    ================================================================================================================================
    
    二、视图
    
    
    视图是一种虚拟的表，是从数据库中的一个或者多个表中导出来的表。
    
    视图还可以从已经存在的视图的基础上定义。
    
    数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据存放在原来的表中。
    
    使用视图查询数据时，数据库会从原来的表中取出对应的数据，因此，视图中的数据是依赖于原来的表中的数据。
    
    一旦原表中的数据发生改变，则显示在视图中的数据也会发生改变。
    
    
    
    视图的作用：视图是在原有的表或者视图的基础上重新定义的虚拟表，这样可以从原有的表中选取对用户有用的信息。
    
    
    1、创建视图
    
    视图可以建立在一张表上，也可以建立在多张表上
    
    语法格式：
               create [ algorithm = { undefined | merge | temptable} ]
    
                      view 视图名 [(属性清单)]
    
                      as select 语句
    
                      [ with { cascaded | loocal } check option ];
    
    
    algorithm是可选参数，表示视图选择的算法
    
    
    
    视图名参数表示需要创建的视图的名称；
    
    属性清单参数是可选参数，其指定了视图中各个属性的名词；默认情况下，与select语句中查询的属性相同；
    
    select语句参数是一个完整的查询语句，表示从某个表中查询出满足条件的记录，并将这些记录导入视图中；
    
    with check option是可选参数，表示更新视图时，要保证在该视图的权限范围内；
    
    
    
    cascaded是可选参数，表示更新视图时，要满足所有相关视图和表的条件，该参数为默认值；
    
    local表示更新视图时，只要满足该视图本身的定义条件即可；
    
    
    (1)在单表上创建视图
    
    举例1： create view department_view1 as select * from department
    
    查询department_view1视图的表结构：desc department_view1;
    
    注释：视图department_view1表的属性与department表的属性完全一样，因为在未指定定义视图的属性列表的情况下，
    
          视图的属性名与select语句查询的属性名相同。
    
    
    举例2：create view department_view2(name,fuction,location) as select d_name,fuction,address from department;
    
    视图department_view2的属性列名分别为：name,fuction,location。因为在创建视图时，指定了属性列表。
    
    视图的属性名与属性列表中的属性名相同。
    
    
    
    (2)在多表上创建视图
    
    
    举例：create algorithm=merge view worker_view1 (name,department,sex,age,address) 
    
          as select name,department.d_name,sex,2009-birthday,address
    
          from worker,department where worker.d_id=departmnet.d_id
    
          with local check option;
    
    
    
    
    2、查看视图
    
    (1)describe语句查看视图基本信息
    
    语法格式:describe 视图名;         可以简写为：desc 视图名;
    
    此语句可以简单了解视图中的各个字段的简单的信息
    
    
    (2)show table status语句查看视图基本信息
    
    语法格式：show table status like '视图名';
    
    like表示后面匹配的是字符串；
    
    视图名参数指需要查看的视图的名称，需要用单引号引起了；
    
    
    
    (3)show create view语句查看视图详细信息
    
    语法格式：show create view 视图名;
    
    
    (4)在views表中查看视图详细信息
    
    在MYSQL中，所有视图的定义都存储在information_schema数据库下的views表中。
    
    查询views表，可以查看到数据库中所有视图的详细信息。
    
    语法格式：select *  from information_schema.views;
    
    
    
    
    
    
    
    3、修改视图
    
    修改视图是指修改数据库中已经存在的表的定义。
    
    当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间的一致；
    
    (1)create or replace view语句修改视图
    
    语法格式：
               create or replace [ algorithm = { undefined | merge | temptable} ]
    
                          view 视图名 [(属性清单)]
    
                          as select 语句
    
                         [ with { cascaded | loocal } check option ];
    
    
    
    (2)alter语句修改视图
    
    
    语法格式：
               alter [ algorithm = { undefined | merge | temptable} ]
    
                      view 视图名 [(属性清单)]
    
                      as select 语句
    
                      [ with { cascaded | loocal } check option ];
    
    
    4、更新视图
    
    ***************************************************
    
    
    5、删除视图
    
    
    删除视图是指删除数据库中已经存在的视图的定义；删除视图时，只能删除视图的定义，不会删除数据
    
    语法格式：drop view [if exists] 视图名列表;
    
    if exists参数指判断视图是否存在，如果存在则执行，否则不执行，
    
    视图名列表参数表示要删除的视图的名称列表，各个视图名称之间用逗号隔开；
    
    ====================================================================================================================================
    
    create table employee ( num int(50),
                            d_id int(50),
                            name varchar(50),
                            age int(50),
                            sex varchar(50),
                            homeadd varchar(50)
                           );
    
    insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
    insert into employee values(2,1001,'lisi',24,'nv','hunan');
    insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
    insert into employee values(4,1004,'aric',15,'nan','yingguo');
    
    select * from employee;
    
    create table department ( d_id int(50),
                              d_name varchar(50),
                              functione varchar(50),
                              address varchar(50)
                            );
    
    insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
    insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
    insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
    
    select * from department;
    
    
    ===================================================================================================
    select * from employee;
    
    select * from department;
    
    
    /*在单表上创建视图*/
    
    create view department_view1 as select * from department;
    
    desc department_view1;
    
    select * from department_view1;
    
    select d_id,d_name,functione from department_view1;
    
    ==============================================================
    
    create view department_view2(name2,functione2,location2) as select d_name,functione,address from department;
    
    desc department_view2;
    
    select * from department_view2;
    
    ===============================================================================
    在多表上创建视图
    
    create view department_view3(d_id,name,age,sex,homeadd,bmmc,gongn,bangonglouceng) as 
    select employee.d_id,employee.name,employee.age,employee.sex,employee.homeadd,department.d_name,department.functione,department.address 
    from employee,department
    where employee.d_id = department.d_id;
    
    select * from department_view3;
    
    describe department_view3;
    
    ================================================================================================================================
    
    查看视图
    
    describe 视图名称；
    
    或者
    
    desc 视图名称；
    ===================================================================
    
    查看视图基本信息
    
    show table status like '视图名';
    
    
    show table status like 'department_view1';
    
    =======================================================
    
    查看视图详细信息
    
    show create view 视图名;
    
    show create view department_view1;
    
    ==============================================================
    
    在views表中查看视图详细信息
    
    select * from information_schema.views;
    
    
    ======================================================================
    修改视图
    
    使用 create or replace 语句，后面的语法格式都一样。
    
    在视图已经存在的情况下，可以对视图进行修改；
    
    在视图不存在的情况下，可以创建视图
    
    
    
    select * from department_view2;
    
    create or replace view department_view2(id,name2,functione2,location2) as select d_id,d_name,functione,address from department;
    
    desc department_view2;
    
    select * from department_view2;
    
    
    
    使用alter语句也可以修改视图，后面其它语法格式一样；
    
    
    select * from department_view2;
    
    alter view department_view2(functione2,location2) as select functione,address from department;
    
    desc department_view2;
    
    select * from department_view2;
    
    ========================================================================================================
    
    更新视图，即更新原始表格中的数据，但是只能在权利范围之内，一般不建议使用。
    
    
    ======================================================================================================
    
    删除视图
    
    drop view [if exists] 视图名列表 [ restrict| cascade ]
    
    
    if exists 指判断视图是否存在，如果存在则执行，不存在，则不需要执行；
    
    视图名称列表 即表示要删除的视图的名称的列表，各个视图名称之间用逗号隔开；
    
    
    drop view department_view1,department_view2,department_view3;
    
    
    
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    =====================================================================================================================
    
    一、索引
    
    
    
    索引由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度。
    
    索引是创建在表上面的，是对数据表中一列或者多列的值进行排序的一种结构。
    
    
    通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。
    
    
    索引优点：提高检索数据的速度；
    
    索引缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间，
    
              增加、删除、修改数据时，要动态的维护索引，造成数据的维护速度降低了。
    
    
    注意：索引可以提高查询速度，但是会影响插入记录的速度。向有索引的表中插入记录时，数据库会按照索引进行排序，这样就降低了插入记录的速度，
    
           插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后再插入数据。插入完成后，再创建索引。
    
    
    
    索引的分类
    
    
    1、普通索引
    
    
    2、唯一性索引
    
    
    3、全文索引
    
    
    4、单列索引
    
    
    5、多列索引
    
    
    6、空间索引
    
    
    ==================================================================================================
    索引的设计原则
    
    1、选择唯一性索引
    
    2、为经常需要排序、分组和联合操作的字段建立索引
    
    3、为常作为查询条件的字段建立索引
    
    4、限制索引的数目
    
    5、尽量使用数据量少的索引
    
    6、尽量使用前缀索引
    
    7、删除不再使用或者很少使用的索引
    
    ===========================================================================================================
    
    创建索引
    
    一、创建表的时候创建索引
    
    语法格式如下：
    
    create table 表名 (  属性名 数据类型 [完整性约束条件],
                         属性名 数据类型 [完整性约束条件],
                         ……
                         属性名 数据类型
                         [unique | fulltext | spatial] index | key
                           [ 别名 ] ( 属性名1 [(长度)]) [ asc | desc ] )
                       );
    
    unique是可选参数，表示索引为唯一性索引；
    
    fulltext是可选参数，表示索引为全文索引；
    
    spatial是可选参数，表示索引为空间索引；
    
    index 和 key参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；
    
    “别名”是可选参数，用来给创建的索引取的新的名称；
    
    “属性1”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；
    
    “长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
    
    “asc”和“desc”都是可选参数，“asc”表示升序排序，“desc”表示降序排序；
    
    
    1、创建普通索引
    
    创建一个普通索引，不需要加任何unique、fulltext、spatial参数。
    
    示例：
    create table index1 ( id int,
                          name varchar(20),
                          sex varchar(20),
                          index index1_id (id)
                         );
    
    show create table index1;
    
    explain select * from index1 where id = 1; /*possible_keys:index1_id;kye:index1_id ;说明索引被引用了*/
    
    
    
    2、创建唯一性索引
    
    创建唯一性索引时，需要使用unique参数进行约束。
    
    示例：创建一个表名为index2的表，表中的id字段上建立名为index2_id的唯一性索引，且以升序的形式排列。
    
    create table index2 ( id int,
                          name varchar(20),
                          sex varchar(20),
                          unique index index2_id (id asc)
                         );
    
    show create table index2;
    
    
    
    3、创建全文索引
    
    全文索引，只能创建在char、varchar、text类型的字段上，而且只有myisam存储引擎支持全文索引。
    
    create table index3 ( id  int,
                          info varchar(20),
                          fulltext index index3_info ( info )
                         )engine=myisam;
    
    show create table index3;
    
    4、创建单列索引
    
    单列索引是在表的某一个字段上创建索引
    
    subject字段的长度为20，而index4_st索引的长度只有10，这样做的目的是为了提高查询速度。
    
    对于字符型的数据，可以不用查询全部信息，而只查询前面的若干字符信息。
    
    
    create table index4 ( id  int,
                          subject varchar(30),
                          index index4_st ( subject(10) )
                         );
    
    
    show create table index4;
    
    
    5、创建多列索引
    
    创建多列索引是在表的多个字段上创建一个索引
    
    create table index5 ( id  int,
                          name varchar(30),
                          sex varchar(4),
                          index index5_ns ( name,sex )
                         );
    
    
    show create table index5;
    
    可以看出，name和sex字段上已经建立了一个名为index5_ns的单列索引。
    
    多列索引里，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用；
    
    如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
    
    
    6、创建空间索引
    
    
    创建空间索引时，必须使用spatial参数来设置。而且，创建空间索引时，表的存储引擎必须是myisam类型。而且，索引字段必须有非空约束。
    
    
    create table index6 ( id int,
                          space geometry not null,
                          spatial index index6_sp ( space )
                        )engine=myisam;
    
    show create table index6;
    
    
    注意：space字段必须是非空的，而且数据类型是geometry类型。这个类型是空间数据类型。空间数据类型包括：geometry、point、linestring、polygon类型等
    
    
    =======================================================================================================================================
    
    二、在已经存在的表上创建索引
    
    
    在已经存在的表中，可以直接为表上的一个或几个字段创建索引。格式如下：
    
    
    create [unique | fulltext | spatial] index 索引名
                           
    on 表名 ( 属性名 [(长度)]) [ asc | desc ] );
    
    
    
    unique是可选参数，表示索引为唯一性索引；
    
    fulltext是可选参数，表示索引为全文索引；
    
    spatial是可选参数，表示索引为空间索引；
    
    index 用来指定字段为索引的；
    
    “索引名”参数是给创建的索引取的新名称；
    
    “表名”是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；
    
    “属性名”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；
    
    “长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
    
    “asc”和“desc”都是可选参数，“asc”表示升序排序，“desc”表示降序排序；
    
    
    1、创建普通索引
    
    
    create table example0 ( id int,
                            name varchar(20),
                            sex varchar(40)
                           );
    
    show create table example0;
    
    
    create index index7_id on example0( id );
    
    
    2、创建唯一性索引
    
    create unique index index8_id on index8( course_id );
    
    
    3、创建全文索引
    
    create fulltext index index9_info on index9( info );
    
    4、创建单列索引
    
    create index index10_addr on index10 ( address(4) );
    
    5、创建多列索引
    
    create index index11_na on index11 ( name , address );
    
    6、创建空间索引
    
    create spatial index index12_line on index12( line );
    
    
    ==================================================================================================
    
    三、用alter table语句来创建索引
    
    
    在已经存在的表上，可以通过alter table语句直接为表上的一个或几个字段创建索引
    
    语法格式如下：
    
    
    alter table 表名 add [unique | fulltext | spatial] index 索引名 ( 属性名 [(长度)]) [ asc | desc ] );
    
    
    1、创建普通索引
    
    alter table example0 add index index13_name ( name );
    
    
    2、创建唯一性索引
    
    alter table index14 add unique index index14_id ( course_id );
    
    
    3、创建全文索引
    
    alter table index15 add fulltext index index15_info( info );
    
    
    4、创建单列索引
    
    
    alter table index16 add index index16_addr( address(4) );
    
    
    5、创建多列索引
    
    alter table index17 add index index17_na( name,address );
    
    
    6、创建空间索引
    
    alter table index18 add spatial index index18_line( line );
    
    
    ====================================================================================================================
    
    删除索引
    
    指将表中已经存在的索引删除掉。
    
    语法格式如下：
    
    drop index 索引名 on 表名;
    
    ========================================================================================================================