mysql学习笔记08

存储函数

格式

delimiter $$
create function 函数名 （参数可有可无）
returns 数据类型
begin 
  代码
  return 
end $$
delimiter;

使用实例

​

-- 创建存储函数-无参
​
-- 运行创建函数权限信任
set global log_bin_trust_function_creators=TRUE;
​
delimiter $$
create FUNCTION myfun1_emp()
returns int
begin 
            declare num int ;
            select  count(1) into num from emp;
            return num;
end $$
delimiter ;
-- 调用函数
select myfun1_emp();
drop FUNCTION myfun1_emp; -- 14 
​
​
​
-- 创建存储函数-有参
-- 传入员工编号，返回员工名字
delimiter $$
create function myfun2(id int) 
returns varchar(50)
begin 
        declare  name_emp varchar(50);
        select ename into name_emp from emp where empno = id;
        return name_emp ;
end $$
delimiter;
​
select myfun2(1002); 
-- 黛绮丝

触发器

概述

触发器是一种特殊的存储过程，触发器和存储过程一样完成特定功能，但是不需要调用，当对数据库的表进行DML操作时，回自动触发。

只有在insert，delete，update操作时才会触发触发器执行

触发器作用：日志记录，数据校验，为了保证数据完整性

使用old和new来引用触发器中发生变化的记录内容，支持行级触发，不支持语句触发

特性

​ 触发频率：针对每一行执行

​ 什么时候触发：在增删改前或者后

​ 触发器定义在表上，依附在表上

​ 什么条件回触发：增删改

格式

-- 创建只有一个执行语句的触发器
create tigger 触发器名字 after 触发事件
on 表名  for each row
执行语句；
​
-- 创建有多行执行语句的触发器
create tigger 触发器名字 after 触发事件
on 表名  for each row
begin
    执行语句；
end;

old和new的用法

语法：old.列名 或者 new.列名

在insert触发器中，只有new.name 指新插入的数据，没有old用法

在update触发器中，更新之前的数据为old,更新之后用new

在delete触发器中，只有old.name 指新插入的数据，没有new用法

触发器基本操作

-- 数据准备
create  database test07_trigger;
use test07_trigger;
​
-- 用户表
create table user(
uid int,
username varchar(50),
password varchar(50));
​
alter table user add constraint pk primary key(uid);
-- 用户信息操作日志
create table user_logs(
id int primary key auto_increment,
time timestamp,
log_text varchar(255)
);
​
-- 需求1：当user表添加一行数据，user_logs表添加一条日志记录
create trigger user_insert_trigger after insert 
on user for each row
     insert into user_logs values (null,NOW(),CONCAT('有新用户添加',new.uid,new.username,new.password));
-- 执行测试
insert into user values(2,'张三','123456');
​
​
​
-- 需求2：当user修改一行数据，user_logs表添加一条日志记录
delimiter $$
create trigger user_update_trigger after update
on user for each row
begin 
            insert into user_logs values(null,now(),CONCAT('有新用户修为',new.uid,new.username,new.password));
end $$
delimiter;
update user set uid=10 where password = '123456';
drop trigger user_update_trigger;
​
​
​
 -- 需求3：当user删除一行数据，user_logs表添加一条日志记录
 delimiter $$
 create trigger user_delete_trigger after delete
 on user for each row
 BEGIN
​
        insert into user_logs values(null,now(),concat('有用户删除',old.uid,old.username,old.password));
end $$
delimiter;
​
delete from user where uid=1;
drop trigger user_delete_trigger;

触发器操作drop和show

-- show triggers; 展示该数据库下所有触发器
-- drop trigger 触发器名字； -- 删除触发器

索引

介绍

索引是通过某种算法，构建出一个数据模型，用于快速寻找某一列中某行的数据，如果不要索引，查询就是从第一条记录开始读表，索引可以快速达到一个位置搜索数据。索引加快查找速度

分类

索引是存储引擎用来快速查找记录的数据结构

按照实现方式划分

​ 有Hash和B+Tree索引

按照功能划分

​ 单列索引

​ 普通索引

​ 唯一索引

​ 主键索引

​ 组合索引

​ 全文索引

​ 空间索引

单列索引

对表中的某一列建立索引

普通索引

NORMAL, B-Tree

对表中某一列建立索引，没有要求，可以重复，可以空值

格式

-- 数据准备
create database test08_suoying;
use test08_suoying;
-- 1.创建表时直接指定
create table student(
        sid int primary key ,
        card_id varchar(20),
        name varchar(20),
        gender varchar(20),
        age int,
        birth date,
        phone_num varchar(20),
        score double,
        
        
        index index_name (name) 
        -- 给name列创建索引
​
);
​
insert into student values (1,'2103','dc','男',21,'2002-12-7',15529163818,99);
​
​
-- 因为name 索引，所以用name 条件
select * from student  where name = 'dc';
​
​
-- 2.直接创建
create index indec_age on student(age);
​
-- 3.修改表结构时添加索引
alter table  student add index index_gender(gender);

​

查看索引

-- 查看数据库所有索引
select * from mysql.innodb_index_stats as a where  a.database_name = 'test08_suoying';  
-- 查看某一数据库索引
select * from mysql.innodb_index_stats as a where  a.database_name = 'test08_suoying' and a.table_name = 'student';
-- 查看集体一个表中索引
show index from student;

删除索引

-- drop 删除索引
drop index index_age on student;
-- alter 删除索引
alter table student drop index index_gender;

唯一索引

与普通索引类似，但是可以索引列必须唯一，允许空值

Create table student1 (
id int ,
age int ,
name varchar(20),
weight int,
length int,
    -- 1.创建表时直接指定;
unique index index_id(id)
);
​
​
​
-- 2.直接创建    
alter table student1 add unique index u_index(age);
​
​
​
-- 3.修改表结构时添加索引
​
create unique index index_name on student1(name) ;

主键索引

在创建主键时，默认自动创建

组合索引

create unique index index_name on student1(name) ;
​
​
Create table student2 (
id int ,
age int ,
name varchar(20),
weight int,
length int
);
create index index_con  on student2(id,name);
drop index index_con on student2;
​