mysql自定义函数索引_mysql 之索引视图事务存储过程触发器自定义函数

索引

建立索引

约束都会自动建立索引

select * from stu where id = 6;

select * from stu where address = '河南郑州';

create index addrindex on stu(address asc);

使用索引

select * from stu where address = '河南郑州';

事务

A B 50

转账

1) A-50完成

2) B+50完成

3) 事务提交commit

视图

简化查询

存储过程

procedure 过程

-- 建立存储过程

delimiter $$

create procedure sss()

begin

select id,name from stu;

end$$

delimiter ;

-- 执行存储过程

call sss;

-- 删除存储过程

drop procedure sss;

触发器

stu 学生成绩表

插入的时候，如果这个学生不及格，不但在此表插入

而且还要将此学生信息插入补考表

stubak 补考表

create database bydb;

use bydb;

create table stu(

id int unsigned auto_increment,

name varchar(30),

score tinyint unsigned,

primary key(id)

);

create table stubak like stu;

-- 建立触发器 会自动使用两个临时表 NEW old

delimiter //

create trigger t1 after insert on stu for each row

begin

if NEW.score <60 or NEW.score is null then

insert into stubak values(NEW.id,NEW.name,NEW.score);

end if;

end //

delimiter ;

insert into stu values(null,'jack',20),(null,'andy',90),(null,'lisi',null);

select * from stu;

select * from stubak;

delete from stu where id = 3;

delimiter //

create trigger t2 after update on stu for each row

begin

-- NEW.score 新成绩 OLD.score 旧成绩

if NEW.score>=60 then

delete from stubak where id = NEW.id;

else

replace into stubak values(NEW.id,NEW.name,NEW.score);

end if;

end //

delimiter ;

delimiter //

create trigger t3 after delete on stu for each row

begin

delete from stubak where id = OLD.id;

end //

delimiter ;

函数

select rand();

编写一个函数实现根据时间来文字提示

2020-7-1 17：54 ：01 刚刚

2020-7-1 17：35 ：01 xx分钟前

DELIMITER //

CREATE FUNCTION getTime(i DATETIME)

RETURNS VARCHAR(50) DETERMINISTIC

BEGIN

DECLARE str VARCHAR(50) DEFAULT DATE_FORMAT(i,'%Y-%m-%d %H:%i:%s');

DECLARE tlong INT UNSIGNED DEFAULT UNIX_TIMESTAMP(i);

DECLARE slong INT UNSIGNED DEFAULT UNIX_TIMESTAMP();

IF ROUND((slong-tlong)/3600)>=1 THEN

SET str = CONCAT(ROUND((slong-tlong)/3600),'小时前');

ELSEIF ROUND((slong-tlong)/60)>=1 THEN

SET str = CONCAT(ROUND((slong-tlong)/60),'分钟前');

ELSE

SET str = '刚刚';

END IF;

RETURN str;

END //

DELIMITER ;

SELECT *FROM te;

SELECT xv,姓名,score,getTime(shijian) FROM te;