索引
建立索引
约束都会自动建立索引
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;
