函数 存储过程 触发器

mysql函数
    聚合函数:可以对一组值进行计算，并返回单个值
        count()\sum()\avg()\max()\min()
    日期函数：
        系统当前日期： curdate()
        系统当前时间： curtime()
        系统当前日期时间： now()
        计算两个日期相隔的天数：datediff(日期1,日期2)
        计算指定日期加上几天之后的日期 ： adddate(日期,天数)
use 1910b_day04

create table emp(
    eid int primary key auto_increment,
    ename varchar(20),
    birthday datetime
)

#向表中插入数据，生日为系统当前时间
insert into emp(ename,birthday) values('刘洋',now())
insert into emp(ename,birthday) values('刘三5','2021-09-09 12:12:12')

select * from emp
#修改刘三的生日时当前时间：
update emp set birthday=now() where ename = '刘三'

查询出生超过500天的学生信息
查询当前日期之前的500天的具体日期，然后筛选
select * from emp where birthday < adddate(now(),-500)
查询每位学生出生的天数
select *,datediff(now(),birthday) from emp where datediff(now(),birthday)>500

#查询员工信息，展示学生名的长度
select *,char_length(ename) from emp

存储过程：
select * from emp limit 3,3;

create procedure pageQuery(in startIndex int,in pageSize int)
begin 
    select * from emp limit startIndex,pageSize;
end;
 
查询前3条：
call pageQuery(0,3)
每页展示3条，查询第3页：
call pageQuery(6,3)

#定义局部变量
create procedure cp01()
begin 
    #定义局部变量 语法： declare 变量名 数据类型 [default 默认值]
    declare count int default 10;
    #查看变量
    select count;
    #重新赋值
    set count = 20;
    #查看变量
    select count;
end;

call cp01()

查看所有存储过程： SHOW PROCEDURE STATUS

#定义用户变量 set @变量名=默认值
set @name = 'rose';

select @name;

测试in、out、inout区别
drop procedure cp02;
create procedure cp02(inout score int)
begin 
    #查看参数
    select score;
    #重新赋值
    set score=10;
    #查看参数
    select score;
end;

#定义用户变量
set @count = 1;
select @count;
call cp02(@count)

show tables;
触发器：
create table t_clazz(
    cid int primary key auto_increment,
    cname varchar(20)
);
insert into t_clazz(cname) values('A班'),('B班'),('C班')

create table t_student(
    sid int primary key auto_increment,
    sname varchar(20),
    cid int,
    foreign key(cid) references  t_clazz(cid)
);

insert into t_student values(0,'rose',1);
insert into t_student values(0,'rose2',2);
insert into t_student values(0,'rose3',2);
insert into t_student values(0,'rose4',2);
insert into t_student values(0,'rose5',1);
insert into t_student values(0,'rose6',3);
insert into t_student values(0,'rose7',3);
insert into t_student values(0,'rose8',3);
insert into t_student values(0,'rose9',1);
insert into t_student values(0,'rose10',1);

select * from t_clazz;
select * from t_student;
删除C班 班级：
delete from t_clazz where cid=3
因为有外键约束，也就是说这个班下有学生，不然删除，需要先操作下，保证该班没有学生
update  t_student set cid = null where cid = 3
再删该班数据： delete from t_clazz where cid=3

使用触发器完成删除班级数据，自动清理该班下学生
触发器语法： 
create trigger 触发器名 before|after 更新操作（insert|update|delete） on 表名  for each row 
begin 
    #指定执行的语句
end;

四要素：
监视地点： 表名  t_clazz
监视事件： 更新操作 （insert|update|delete） delete 
触发时间： before|after   删除班级表之前先清理学生信息before
触发事件： 监视的表中数据更改了    删除班级表语句

create trigger deleteClazzTrigger before delete on t_clazz  for each row 
begin 
    #指定执行的语句 old.字段名 获取该字段的值
    update  t_student set cid = null where cid = old.cid;
end;

触发器不需要调用，只需要根据监视的情况执行语句: 
delete from t_clazz where cid = 2;

定义触发器：添加一条学生信息后，向日志表中添加一条信息。
    日志内容content为：学生姓名 ，记录时间log_time：为当前时间(10分)

#创建日志表
create table log(
    id int primary key auto_increment,
    content varchar(20),
    log_time datetime
)

创建触发器
create trigger insertStudentTrigger after insert on t_student for each row 
begin 
    insert into log values(0,new.sname,now());  #old.字段 获取表中已有的字段的值 ，new.字段名，获取即将向表中插入或者修改的新的值
end;

select * from log;
#向学生表插入数据，会自动调用触发器，向日志表插入数据
insert into t_student values(0,'jack2',1);

表中数据发生更改时自动触发的特殊的存储过程

use 1910b_day04;
create table account(
    id int primary key auto_increment,    
    accountNo varchar(25)not null,
    balance decimal(11,2) not null
);

insert into account values(0,'张三',30000.99);
insert into account values(0,'李四',29000.88);
insert into account values(0,'王五',31000.99);

实现张三给李四转账：
#开启手动事务
begin;
#张三减1000 ： 
update account set balance=balance-1000 where accountNo='张三';
#李四+1000：
update account set balance=balance+1000 where accountNo='李四';
#正常提交
commit;
#或者异常回滚
rollback;

select * from account;

事务是为保障数据操作可靠而独立执行的逻辑单元
事务四个特性：原子性、一致性、隔离性、持久性
    
查看事务的状态： show variables like 'autocommit'  # on 打开状态
关闭自动提交： set autocommit='off'
打开自动提交： set autocommit=1

#张三减1000 ： 
update account set balance=balance-1000 where accountNo='张三';
#李四+1000：

update account set balance=balance+1000 where accountNo='李四';

#正常提交
commit;
#异常回滚
rollback;

select * from account;

##################################################
视图（ view ）是简化查询过程，提高数据库安全性的虚拟表
创建视图语法： 
create view 视图名 as
select 语句

创建视图view_account，实现通过视图view_account查询表account中的所有数据：

create view view_account as
select * from account

调用： select * from view_account
show tables;
创建视图 view_student，实现通过视图查询学生信息和班级名
create view view_student as
select t_student.*,cname from t_student left join t_clazz on t_student.cid=t_clazz.cid

调用： select * from view_student where sname='rose'

删除视图： drop view view_student

查看视图的结构： desc view_account

修改视图： alter view view_account as select id from account
有就修改，没有就创建：create or replace view view_student as select t_student.*,cname from t_student left join t_clazz on t_student.cid=t_clazz.cid

索引：提高数据检索效率的数据库对象
    分类：普通索引、唯一索引、全文索引、多列索引
创建表时设置索引
create table 表名(
    字段名 数据类型,
    index 约束名 (字段名) #普通索引
    ,unique index 约束名 (字段名) #唯一索引
    ,fulltext index 约束名 (字段名) # 全文索引，存储引擎为innodb的不支持，myisan支持 ，只能描述字符串类型的
    ,index 约束名(字段1,字段2)
)

create table table01(
    id int,
    name varchar(23),
    age int,
    content varchar(200),
    index index01 (id),
    unique index index02 (name),
    fulltext index index03 (content,name)
)engine=myisam

删除索引： DROP  INDEX  索引名字    ON   表名;   
    drop index index03 on table01;

desc table01
创建索引： CREATE  [unique|fulltext] INDEX  索引名 ON   表名   (属性名1  [(长度)]  );  
        create unique index index02 on table01(name)