自定义函数 function

自带的函数满足不了需求时,就可以自定义函数

create function 函数名 () 
returns 类型
函数体
create function StuNameById()
    returns varchar(45)
    return
    (SELECT name FROM tb_students_info WHERE id=1);
    //创建StuNameById存储函数,返回select查询结果。

select StuNameById();//查看函数运行结果。    
  • 如果需要修改自定义函数,需要先删除再重新创建。
drop function if exists 函数名;

存储过程procedure

我们之前学习的语句都是针对一个表或者几个表的单条sql语句。但是在实际的开发中,需要多条sql语句处理多个表才能满足需求。而存储过程就可以有效地完成这个数据库地操作。
一个存储过程是一个可编程的函数,在数据库中创建并保存,有sql语句和一些特殊的k控制结构组成。

存储过程创建、修改、删除

  • 创建

    create procedure <过程名> ( [过程参数[,…] ] ) <过程体>
    [过程参数[,…] ] 格式
    [ IN | OUT | INOUT ] <参数名> <类型>
    1. 支持in,out,inout三种类型的参数。
    2. 过程体在过程调用的时候必须执行的 SQL 语句,以关键字 BEGIN 开始,以关键字 END 结束,如果只有一条sql语句则可以省略。
    3. 存储过程中使用 DELIMITER 命令将结束命令修改为其他字符,而不是以分号结束。
      在mysql中执行mysql > DELIMITER ??后,任何命令、语句或程序的结束标志就换为两个问号“??”了,再用DELIMITER ;换回来。
      mysql> delimiter //
      mysql> create procedure ShowStuScore()
      -> begin
      -> SELECT * FROM tb_students_score;
      -> end //
      //创建一个不带参的存储过程
      mysql> delimiter ;
      mysql> call ShowStuScore();
      //使用call调用存储过程
      mysql> delimiter //
      mysql> CREATE procedure GetScoreByStu
      -> (IN name VARCHAR(30)) //接受一个输入参数
      -> BEGIN
      -> SELECT student_score FROM tb_students_score
      -> WHERE student_name=name;
      -> END //
      //创建带参的存储过程,指定了字符类型的输入参数name
      mysql> delimiter ;
      mysql> call GetScoreByStu('Green');
  • 修改
    如果需要修改存储过程的内容,需要删除后再重新创建。

    alter procedure 过程名 特征
  • 删除
    drop procedure IF EXISTS 过程名;

触发器 trigger

触发器是一个特殊的存储过程,但是不需要使用call来调用,只要定义一个事件发生就会被自动调用。比如

  • 增加一条学生记录时,会自动检查年龄是否符合范围要求。
  • 每当删除一条学生信息时,自动删除其成绩表上的对应记录。
  • 每当删除一条数据时,在数据库存档表中保留一个备份副本。

触发mysql触发器的操作有三种:insert,update,delete

  • insert
    可引用一个new的虚拟表来访问被插入的行.

  • update
    使用new访问update的值,使用old访问update前的值。old中值都是只读的。

  • delete
    使用old访问被删除的值

对于事务性表,如果触发程序失败,以及由此导致的整个语句失败,那么该语句所执行的所有更改将回滚;对于非事务性表,则不能执行此类回滚,即使语句失败,失败之前所做的任何更改依然有效。

每个表都有三个事件的前后触发处理,所以一个表最多支持6个触发事件

  • 查看已有的触发器
    show trigger;

触发器创建、修改、删除

  • 创建
    基本语法

    CREATE TRIGGER <触发器名> < BEFORE | AFTER >
    <INSERT | UPDATE | DELETE >
    ON <表名> FOR EACH Row<触发器主体>
    inser|update|delete用于指定触发器的种类,将新行插入表时触发|更改某一行数据时触发|从表中删除一行数据时触发
    before|after,表示触发器被触发的时刻,触发器在触发它的语句之前或之后被触发。比如说验证新数据是否满足条件就可以用before,而希望在触发语句执行之后再执行更多的变化则使用after。
    FOR EACH ROW,行级触发,受事件影响的每一行都要激活触发事件的处理
    create trigger SumOfSalary
    before insert on tb_emp    //定义了插入之前为触发事件
    for each row   //作用插入的每一行
    set @sum = @sum + NEW.salary;  //触发器主体  NEW值插入的值 @sum值初始为0
    //创建一个名为SumOfSalary 的触发器,当一行数据插入前,对salary字段值进行求和运算
    SET @sum=0;
    INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
    SELECT @sum; //@sum是mysql声明的变量,可以看下一小节看看声明变量的方法
  • mysql中声明变量的方法
    使用=或:=都可以为定义的变量赋值。

    set @num=1;
    或者
    set @num:=1;
  • 创建before类型触发器

    //建表语句
    CREATE TABLE `fisha_tb1` (
    `fisha_id` int(11) NOT NULL AUTO_INCREMENT,
    `fisha_title` varchar(100) NOT NULL,
    `fisha_author` varchar(40) NOT NULL,
    `salary` float DEFAULT NULL,
    PRIMARY KEY (`fisha_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
    //设置插入触发器
    create trigger sumOfSalary
    insert on fisha_tb1
    for each row
    set @sum = @sum + NEW.salary;
    //插入数据是触发显示
    set @sum = 0;
    insert into fisha_tb1(fisha_title,fisha_author,salary) values('salary1','test1',8000),('salary2','test2',25000);
    select @sum;  //结果33000
  • 创建after类型触发器

    CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
    //插入数据到tb_emp6表后,再把数据复制一份到tb_emp7表中,而且7表中的salary是插入值的两倍。    
  • 修改
    触发器不能被修改和覆盖,需要删除后再重创建。

  • 删除
    drop trigger 触发器名;

索引 index

索引是根据表中一列或若干列按照一定的顺序列值对应行之间的关系表。(也可以想象为id和所在行的关系,但索引表基本上是基于id建立的)
Mysql中通常使用顺序访问和索引访问两种方式对数据库表中的数据进行访问。

  • 顺序访问
    在表中实行全表扫描,从头到尾逐步遍历,从无序的数据中找到需要的数据。当查询的数据量较大时,这种方法比较耗费时间。

  • 索引访问
    通过遍历索引的方式直接访问表中记录行数据。这种方式需要提前在表中建立一个行的索引,访问时就可以通过索引来定位行位置,访问行的数据。
    索引存储了指定列数据值的指针,根据指定的排序顺序对这些指针排序。

索引的种类

在技术方面主要分为B-树索引和哈希索引

  • B-树索引
    其数据结构主要包含叶子节点,分支节点和根节点等几个组件。其中叶子节点包含的条目直接指向表里的数据行;分支节点包含条目直接指向分支节点或叶子节点;根节点一个B-树只有一个根节点,实际上是位于树的最顶端的分支节点。

  • 哈希索引
    也称为散列索引或 HASH 索引。
    把任意长度的输入通过散列算法换成固定长度的输出,输出的值就是散列值。MEMORY和HEAD存储引擎支持这类索引。
    mysql需要读取表中的索引列的值来参与散列计算,这个过程会比较耗费时间。
    HASH 索引只支持等值比较,如“=”“IN()”或“<=>”

而根据索引的具体用途,逻辑可以分为5个类型

  • 普通索引 - index
    是最基本的索引类型,唯一任务是加快对数据的访问速度,使用关键字index或key创建普通索引。

  • 唯一性索引 - unique
    不允许索引列中具有相同索引值得索引,此索引是为了避免数据出现重复,使用关键字unique创建。

  • 主键索引 - key
    也是一种唯一性索引,即不允许出现重复值或空值,而且每个表中只能有一个主键。可以建表时或建表后通过primary key指定。

  • 空间索引 - spatial
    主要用于处理地理空间类型geometry。

  • 全文索引 - fulltext
    只能在varchar或者txt类型的列值中使用,全文索引的存储引擎一定是myisam,而InnoDB没有全文索引,mysql的fulltext不支持中文,做分词搜索可以sphinx技术来处理中文

在实际使用过程中索引通常会被创建成单列索引和组合索引。单列索引也就是索引只包含原表的一个列;组合索引将原表多个列共同组成一个索引。

索引可以加快查询速度,提高mysql的处理性能,但是过多建立索引也会造成很多的问题

创建和动态维护索引要耗费时间,每一个索引还要占用一定的物理空间

索引创建、修改、删除

  • 创建索引
    创建索引有三种方法:

    • 专门在一个存在的表中创建索引:

      CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
    • 在创建表时创建索引:

      create table 表名(
      属性名 类型 约束条件,
      [索引类型] 索引名 (索引所属字段(长度))  [ASC|DESC]
      );
      CREATE table_name (
      属性名 类型 约束条件,
      [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY  [index_name] ( col_name  [length],…) [ASC | DESC]

      全文索引例子:

      CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY ,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT(title,body)
      ) ENGINE = MyIsam CHARSET =utf8;

      全文索引使用例子:

      select * from articles where match(title,body) against('hello') ;//hello需要索引的值
    • 在修改表时添加索引:
      alter table 表名 add [索引类型] 索引名(指定索引对应的字段名(索引长度)) [ASC|DESC]
      alter table fisha_tb1 add unique index uniqueAuthor (fisha_author);
      TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [index_name] ( col_name  [length],…) [ASC | DESC]

      例子:

      CREATE TABLE tb_stu_info
      -> (
      -> id INT NOT NULL,
      -> name CHAR(45) DEFAULT NULL,
      -> dept_id INT DEFAULT NULL,
      -> age INT DEFAULT NULL,
      -> height INT DEFAULT NULL,
      -> INDEX(height)
      -> );
      //创建一个表,为该表的height字段添加一般索引
      CREATE TABLE tb_stu_info2
      -> (
      -> id INT NOT NULL,
      -> name CHAR(45) DEFAULT NULL,
      -> dept_id INT DEFAULT NULL,
      -> age INT DEFAULT NULL,
      -> height INT DEFAULT NULL,
      -> UNIQUE INDEX(height)
      -> );
      //创建唯一性索引

      index | key 在用于指定字段为索引的,用户在选择时,只需要选择其中的一种即可。比如index(索引字段),key(索引字段)

  • 修改索引
    通过删除原索引后再新建一个索引的方式修改索引。

  • 删除索引

    • 使用drop index 语句

      DROP INDEX <索引名> ON <表名>
    • 使用alter table 语句
      ALTER TABLE 表名 [DROP PRIMARY KEY|DROP INDEX index_name|DROP FOREIGN KEY fk_symbol]
      //表示删除表中主键|删除指定索引|删除外键
  • 查看已创建的索引的情况
    SHOW INDEX FROM <表名> [ FROM <数据库名>]