MySQL的存储函数、MySQL的触发器、MySQL的索引

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

-- 创建前要先信任子程序的创建者
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者

DELIMITER $$
CREATE FUNCTION my_funtion() -- 未传参
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 0;
SELECT COUNT(1) INTO num FROM emp;
RETURN num;
END $$
DELIMITER ;
-- 调用存储函数
SELECT my_funtion();

DELIMITER $$
CREATE FUNCTION my_funtion1(empno INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE NAME VARCHAR(20);
SELECT ename INTO NAME FROM emp WHERE emp.empno=empno;
RETURN NAME;
END $$
DELIMITER ;
-- 调用存储函数
SELECT my_funtion1(1003);

create trigger 触发器名 before|after 触发事件
on 表名 for each row 
执行语句;  -- 为触发器触发后记录信息的表

create trigger 触发器名 before|after  触发事件 
on 表名 for each row
begin
     执行语句列表
end;

-- 用户表
CREATE TABLE USER(
    uid INT PRIMARY KEY ,
    username VARCHAR(50) NOT NULL,
    PASSWORD VARCHAR(50) NOT NULL
);
-- 用户信息操作日志表
CREATE TABLE user_logs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    TIME TIMESTAMP,
    log_text VARCHAR(255)
);

CREATE TRIGGER my_trigger AFTER INSERT
ON USER FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),'有新数据添加')

INSERT INTO USER VALUES(1,'zhangsan','123456');

SELECT*FROM USER;

SELECT*FROM user_logs;

DELIMITER $$
CREATE TRIGGER my_trigger1 AFTER INSERT 
ON USER FOR EACH ROW
BEGIN 
INSERT INTO user_logs VALUES(NULL,NOW(),'有新数据添加1');
INSERT INTO user_logs VALUES(NULL,NOW(),'有新数据添加2');
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER my_trigger3
AFTER INSERT ON USER FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT('用户新添加了数据：',new.uid,';',new.username,';',new.password));
END $$
DELIMITER ;

show triggers;

-- -- drop trigger [if exists] trigger_name 
drop trigger if exists my_trigger;

create  table 表名(
   ...
   index index_name(字段名) 
);

CREATE TABLE student(
id INT,
NAME VARCHAR(20),
INDEX index_name(id,NAME) -- 可以指定多个字段做联合索引
);

create index 索引名 on 表名(字段名);

create index index_gender on student(gender);

alter table 表名 add index 索引名(字段名)

alter table student add index index_age(age);

select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名';

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'db1';

select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%';

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'db1' and a.table_name like '%student%';

show index from table_name;

show index from student;

drop index 索引名 on 表名 
或 
alter table 表名 drop index 索引名

DROP INDEX index_student_id ON student;
ALTER TABLE student DROP INDEX index_student_id;

create  table student2(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    phone_num varchar(20),
    score double,
    unique index_card_id(card_id) -- 给card_id列创建索引
);

create unique index 索引名 on 表名(列名)

CREATE UNIQUE INDEX index_student_id ON student(id);

alter table 表名 add unique [索引名] (列名)

ALTER TABLE student ADD UNIQUE index_student_id(id);

create index indexname on table_name(column1(length),column2(length));

-- 添加普通组合索引
CREATE INDEX index_student ON student(id,NAME);
ALTER TABLE student ADD INDEX index_student(id,NAME);
-- 添加唯一组合索引
CREATE UNIQUE INDEX index_student ON student(id,NAME);
ALTER TABLE student ADD UNIQUE index_student(id,NAME);
-- 删除组合索引
ALTER TABLE student DROP INDEX index_student;
DROP INDEX index_student ON student;

-- 为phone_num和name字段添加组合索引
create  unique index index_phone_name on student(phone_num,name);
-- 进行查询操作
select * from student where name = '张三'; 
select * from student where phone_num = '15100046637'; 
select * from student where phone_num = '15100046637' and name = '张三'; 
select * from student where name = '张三' and phone_num = '15100046637';

show variables like '%ft%';

insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02'); 
insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');

create table t_article (
     id int primary key auto_increment ,
     title varchar(255) ,
     content varchar(1000) ,
     writing_date date , 
     fulltext (content) -- 创建全文检索
);

alter table 表名 add fulltext 索引名(字段名)

alter table t_article add fulltext index_content(content)

create fulltext index 索引名 on 表名(字段名);

create fulltext index index_content on t_article(content);

match (col1,col2,...)  against(expr [search_modifier])

-- 没有结果 单词数需要大于等于3 （与前面的最小搜索长度和最大搜索长度的参数有关）
SELECT * FROM t_article WHERE MATCH(content) AGAINST('yo');
-- 有结果
SELECT * FROM t_article WHERE MATCH(content) AGAINST('you');

create table shop_info (
  id  int  primary key auto_increment comment 'id',
  shop_name varchar(64) not null comment '门店名称',
  geom_point geometry not null comment '经纬度’,
  spatial key geom_index(geom_point)
);