mysql的学习笔记（阶段四）

-- 允许函数创建权限信任
set global log_bin_trust_function_creators = True;
create database mydb9_function;
drop function if exists myfunc1_emp;
use mydb9_function;
-- 创建存储函数(无参)
delimiter$$
create function myfunc1_emp() returns int
begin
  declare cnt int  default 0; -- 定义局部变量
  select count(*) into cnt from emp;
  return cnt;
end $$
delimiter;
--   调用存储函数
delect myfunc1_emp();
-- 创建一个有参存储函数
-- 需求，传入一个员工额编号，返回员工的名字
drop function if exists myfun2_emp;
delimiter $$
create function myfun2_emp(in_empno int) returns varchar(50)
begin
  declare out_ename varchar(50);
  select ename into out_name from emp where emp_no = in_empno;
  return out_ename;
end $$
delimiter;
select myfun2_emp(200);
-- 创建触发器
-- 先创建两张表
create database if not exists sdk
use  sdk;
drop table if exists user;
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)
);
--  需求 当user 表添加一行数据，则会自动在user_log 添加日志记录
-- 定义触发器
create trigger trigger_test1 after insert  -- 
on user for each row
insert into user_logs values(null,now(),'有新用户添加')
-- 在userb 表中添加数据，让触发器自动执行
insert into user values(1,'张三',123);
--  需求二 ： 当user 表数据被修改的时候，会自动在user_logs表中添加记录
delimiter $$
create trigger trigger_test2  before update
on user for each row
begin 
   insert into user_logs values(null,now(),'有用户信息被修改');
end $$
delimiter ;
update user set password = '8888' where uid =1;
-- 触发器相关的两个关键字
-- 不仅让触发器出发，还要获取具体更新的数据
-- new 可以找到新增加的数据
-- old 之前的数据
-- insert 类型的触发器
-- NEW
-- 定义触发器 trigger_test3
create trigger trigger_test3 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 (4,'赵六','123456');
--  update 类型的触发器
-- OLD(修改之前的数据)
create trigger trigger_test4 after update
on user for each row
insert into user_logs values(null,now(),concat_ws(','','"有用户信息修改，修改之前为：",OLD.uid,OLD.username,OLD.password))
update user set password = '999' where uid = 4;
-- delete 类型触发器
-- OLD
drop trigger if exists trigger_test5;
create trigger trigger_test6 after delete
on user for each row
insert into user_logs values(null,now,concat_ws(','',''有用户信息被删除，被删除信息为:',OLD.uid,OLD.username,OLD.
password));
delete from user where uid =4;
--  查看触发器
show triggers ;
  --  尽量少使用，并且不要对本表进行操作
--  索引(普通)
-- 1： 创建表的时候建立一个索引。
create database mydb5;
use mydb5;
create table student(
  sid int primary key,
  card_id varchar(20),
  name varchar(20),
  gender varchar(20),
  age int,
  brith date,
  phone_num varchar(20),
  score double,
  index index_name(name) -- 给name 列指定索引
);
select * from student where name = "张三"; -- 这样的话，会通过索引的方式去查询
--  2： 在创建表之后再进行添加索引
create index index_gender on student(gender);
-- 3:通过修改表结构
alter table student add index index_age(age);
--  查看索引
-- 1：查看数据库中所有的索引
select * from mysql.innodb_index_stats a where a.database_name='mydb5';
--  2：查看表中索引索引
select * from mysql.innodb_index_stats a where a.database_name = 'mydb5' and a.table_name like '%student';
-- 3：查看表中所有所有
show index from student;
-- 删除索引
-- 1:
drop index index_gender on student;
-- 2
alter table student drop index index_name;
-- 索引（唯一）
create table student2(
  sid int primary key,
  card_id varchar(20),
  name varchar(20),
  gender varchar(20),
  age int,
  brith date,
  phone_num varchar(20),
  score double,
  unique index_card_id(card_id) -- 创建表的时候创建
);
-- 创建表之后再进行创建
create unique index index_card_id on student2(card_id);
-- 修改表结构进行创建
alter table student2 add unique index_phone_num(phone_num)
--  组合索引
-- create index indexname on table_name(column1(length),column2(length));
create index index_phone_name on student(phone_num,name);
-- 创建唯一索引
create unique index index_phone_name on student(phone_num,name);
-- 需要注意索引遵循最左原则
-- 全文索引
-- fulltext
-- 只有字段的数据类型为char,varchar,text及其系列才可以建立全文索引
--  修改表结构创建全文索引
alter table t_article add fulltext index_content(content);
--  添加的方式，进行添加全文索引
create fulltext index index_content on t_article(content);
-- 在创建表的同时创建全文索引的话，效率是比较低的。不进行演示
-- 使用全文索引
show variables like '%ft%'; -- 可查看最小搜索长度和最大搜索长度
select * from t_article where match(content) against('you'); -- 查到的最小搜索长度为三，所以先从三个字符开始
-- 这种查询很类似模糊查询
select * from t_article where content like "%you%"; -- 但是这个速度效率是没有全文索引效率高的
-- 索引的原理，相关的算法
-- hash 算法
-- 更具相关hash算法来找出对应的值，但是不能进行范围查询。
-- 二叉树方式
--  也是不能在范围内进行有效的查找
-- 常用的索引的原理算法结构
-- BTREE树
-- 查看数据库引擎
show engines;
-- 查看创建表使用的命令
show create table user;
-- 创建表的时候指定数据引擎
create table stu2(id int,name varchar(20)) engine = myisam;
-- 在创建表之后进行对引擎修改
alter table stu1 engine = myisam;
-- 如果修改默认的存储引擎，可以在my.ini配置下进行修改
-- mysql 事务 Transaction 
-- 引擎支持 InnoDB
-- 保证成批的sql语句要么全部执行，要么全部不执行
--  在银行转账上就会有事务的体现
-- 比如张三给李四转账的时候，数据库会使张三的减少，李四的前增多，但是需要保证两条语句都需要执行
-- 不然的话就会出现交易问题。要么都执行，要么都不执行。
-- 开启事务
-- start transaction 或者begin
-- 演示事务操作
-- 先创建一个表
create database if not exists mydb12_transcation;
use mydb12_transcation;
create table account(
  id int primary key,
  name varchar(20),
  money double
);
insert into account values (1,"张三",1000);
insert into account values(2,"李四",2000);
-- id 为一的账号转账给id 为2 的账户
-- 设置mysql 事务为手动提交也就是先关闭自动提交
-- 先查询一下状态
select @@autocommit;
-- 设置为手动踢脚
set @@autocommit = 0;
-- 模拟账户转账
-- 开启事务
-- 模拟转账
begin;
update account set money = money-200 where id =1;
update account set money = money +200 where id =2;
-- 执行完之后提交事务
commit ;
-- 如果在转的过程中出现错误，可以进行回滚事务（必须在没有提交之前进行）
-- 如果已经提交，那么相当于数据已经落盘，将不能再进行提交。
rollback; --  会还原到以前的数据
-- 事务的隔离级别
-- 分别是读未提交，读已提交
-- 可重复读，以及可以进行串行化，或者是序列化
--  查看数据库的隔离级别
show variables like "%isolation%"; 
-- 在dos中可以进行明显的操作
--  对数据库密码进行一个修改(如果忘记最好进行一个修改)
use mysql;
alter user "root"@"localhost" identified with mysql_native_password by "123456";
show variables like 'mysql_native_password%';  -- 查看不到，因为已经进行加密