Mysql数据库高级

数据库事务，事务的使用场景：

事务
1、是什么是事务？
事务就是一个完整的业务逻辑。
举例：
假如张三向李四转账100元。
1）张三账户扣100元
2）李四的账户加100元 以上操作就是一个最小工作单元，要么同时成功要么同时失败，不可拆分。
2、事务支持 只有 INSERT DELETE UPDATE 以上三种语句和事务有关系。
3、事务是如何实现同时成功或同时失败？
InnoDB存储引擎；提供一组用来记录事务性活动日志文件。
提交事务：
清空事务性活动日志文件，并彻底把数据持久化到表中。
回滚事务：
按文件进行回滚操作，并清空事务性活动日志文件。
4、如何提交事务，回滚事务？
提交事务： COMMIT 语句
回滚事务： ROLLBACK 语句 关闭默认提交 START TRANSACTION
5、事务包括4个特性
A：原子性 说明事务是最小工作单元，不可再分。
C：一致性 所有任务要求，在同一事务当中，所有操作必须同时成功，同时失败。
I：隔离性 A事务和B事务之间有一定的隔离。
D：持久性 事务结束的一个保障。
6、重点研究事务的隔离性 A和B事务之间有一道墙，墙可以很厚也可以很薄，墙越厚代表隔离级别越高。
查询当前事务隔离级别 SELECT @@tx_isolation
设置全局事务隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL + 事务隔离级别
事务的隔离级别：
1.读未提交：read uncommitted。（最低隔离级别） 什么叫读未提交： 事务A可以读取到事务B未提交的数据。 问题： 脏读现象 演示： /设置全局事务隔离级别/ SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /查询事务/ SELECT @@tx_isolation; /开启事务/ START TRANSACTION; /插入数据/ INSERT INTO user(u_name,u_sex,u_money) VALUE(‘zhangsan’,’1’,100); /回滚事务/ ROLLBACK; /提交事务/ COMMIT;
2.读已提交 READ COMMITTED （只能读到提交之后的数据） 解决的问题： 解决了脏读现象 存在的问题： 第一次查询数据是三条，第二次查询数据可能是四条。 不能重复读。 每一次读取的数据都是绝对真实的数据。 oracle数据库的默认隔离级别READ COMMITTED
3.可重复读 REPEATABLE READ 什么是可重复读？ 提交之后也读不到，永远读取的都是开启事务之前的数据。 mysql的默认事务级别 REPEATABLE READ 事务A开启之后，不管多久，每一次在数据A中读取的数据都是一致的。 即使事务B已经将数据修改，事务A读到的数据还是没有改变，这就叫 可重复读。 问题： 出现幻影 读取到的数据都是幻象，不够真实。
4.序列化/串行化：serializable 这种是最高隔离级别，效率低，但是解决了所有问题。 这种隔离级别表示，事务排队，不能并发。 优点： 每一次读取到的都真实数据。 缺点： 效率太低。

Mysql触发器：

触发器的概念：

触发器是一种特殊的存储过程，不同于存储过程，触发器是通过事件触发执行，而不是手动调用执行的。

什么事触事件？

触发器（trigger），是指表中内容发生改变（增、删、改）时，系统会自<br />    动触发并执行的事件。

作用：

1、可以在触发时，校验或转换数据，保证数据安全。<br />    2、触发器发生错误时，前面用户执行成功的操作会被回滚。

表结构：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`demo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `demo`;
/*Table structure for table `flowers` */
DROP TABLE IF EXISTS `flowers`;
CREATE TABLE `flowers` (
  `f_id` BIGINT(18) NOT NULL AUTO_INCREMENT COMMENT '花呗表id',
  `f_credit_points` INT(4) NOT NULL COMMENT '信用积分',
  `f_quota` INT(5) NOT NULL COMMENT '花呗额度',
  `f_mapping_user` BIGINT(18) NOT NULL COMMENT '用户id',
  PRIMARY KEY (`f_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `u_id` BIGINT(18) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_name` VARCHAR(15) NOT NULL COMMENT '用户名',
  `u_sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
  `u_createtime` DATETIME NOT NULL COMMENT '创建时间',
  `u_eff` INT(1) NOT NULL DEFAULT '1' COMMENT '有效',
  PRIMARY KEY (`u_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建触发器

DELIMITER $$
/*自定义结束符号*/
/*创建*/
CREATE
    /*TRIGGER 触发器*/
    /*库名+触发器名*/
    /*BEFORE/AFTER 在sql之前执行还是之后执行*/
    /*INSERT/UPDATE/DELETE触发条件*/
    TRIGGER `demo`.`userflowers` AFTER INSERT
    /*作用于哪个表*/
    ON `demo`.`user`
    /*每行都受影响，都执行，行级触发器*/
    FOR EACH ROW BEGIN
    /*触发执行的语句*/
    INSERT INTO `flowers`(`f_credit_points`,`f_quota`,`f_mapping_user`)
    VALUE(100,1000,new.u_id);
    END$$
DELIMITER ;

触发器条件使用if elseif else：

/*判断是否有效字段变为0*/
    IF new.`u_eff` = 0 THEN
        /*删除花呗*/
        DELETE FROM `flowers` WHERE `f_mapping_user` = new.u_id;
    ELSEIF  new.`u_eff` = 1 THEN
        /*添加花呗*/
        INSERT INTO `flowers`(`f_credit_points`,`f_quota`,`f_mapping_user`)
        VALUE(100,1000,new.u_id);
    ELSE 
        /*其它情况*/
    END IF;

变量的使用（INTO @变量名）

DELIMITER $$
USE `demo`$$
DROP TRIGGER /*!50032 IF EXISTS */ `userflowers`$$
CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `userflowers` AFTER INSERT ON `user` 
    FOR EACH ROW BEGIN
    /*触发执行的语句*/
    INSERT INTO `flowers`(`f_credit_points`,`f_quota`,`f_mapping_user`)
    VALUE(100,1000,new.u_id);
    /*
        用户变量：以@开始，形式为 @变量名 用户变量只针对当前客户端生效
        全局变量：定义方式 set GLOBAL 变量名，或者 set @@global.变量名
        对所有客户端生效，但是具有super权限才可以设置全局变量
    */
    SELECT COUNT(*) FROM USER INTO @num;
    UPDATE `statistics` SET `users` = @num WHERE s_id = 1;
    END;
$$
DELIMITER ;

Mysql数据库事件：

一、什么是事件？

一组SQL集，用来执行定时任务，跟触发器很像，都是被动执行的，
事件是因为时间到了触发执行，而触发器是因为某件事件（增删改）触发执行；

二、查看事件是否开启:SHOW VARIABLES LIKE ‘event_scheduler’;

开启事件:SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
关闭事件:SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

三、查看事件

查看当前所在数据库的事件:SHOW EVENTS; SHOW PROCESSLIST;
查看数据库中所有的事件:SELECT * FROM mysql.event;

四、事件的控制

临时关闭事件
ALTER EVENT 事件名 DISABLE;
打开
ALTER EVENT 事件名 ENABLE;
删除事件
DROP EVENT 事件名;

五、定时执行事件（sql）

DELIMITER $$
/*创建定时事件*/
CREATE EVENT `demo`.`comm` 
/*指定时间执行*/
ON SCHEDULE AT '2021-09-27 16:34:00' 
/*完成时不保留启用  执行完成之后删除事件*/
ON COMPLETION NOT PRESERVE ENABLE 
DO 
    BEGIN
        UPDATE `commodity` SET `c_number` = 1; 
    END$$
DELIMITER ;

六、循环执行事件(sql)

DELIMITER $$
/*创建事件*/
CREATE EVENT `demo`.`addmoeny` 
/*循环执行 间隔时间为1天*/
ON SCHEDULE EVERY '1' DAY/*SECOND(秒)*/
/*循环的开始时间*/
STARTS '2021-09-27 00:00:00' 
/*循环的结束时间*/
ENDS '2022-04-29 00:00:00' 
/*完成时不保留启用*/
ON COMPLETION NOT PRESERVE ENABLE 
/*开始*/
DO BEGIN
    UPDATE myuser SET `u_money` = `u_money` + 10;
/*结束*/
END$$
DELIMITER ;

Mysql数据库函数:

什么是函数？

函数类似于Java方法，可以传值，也有返回值，可以配合查询做一些复杂的计算。

表结构

CREATE DATABASE /*!32312 IF NOT EXISTS*/`demo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `demo`;
/*Table structure for table `login_record` */
DROP TABLE IF EXISTS `login_record`;
CREATE TABLE `login_record` (
  `lr_id` BIGINT(11) NOT NULL AUTO_INCREMENT COMMENT '登录记录表',
  `lr_users_id` BIGINT(11) NOT NULL COMMENT '用户id',
  `lr_time` DATETIME NOT NULL COMMENT '登录时间',
  PRIMARY KEY (`lr_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Table structure for table `users` */
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `u_id` BIGINT(18) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_name` VARCHAR(11) NOT NULL COMMENT '用户名',
  `u_money` INT(11) NOT NULL COMMENT '用户金额',
  `u_day` INT(11) NOT NULL DEFAULT '1' COMMENT '用户注册天数',
  `u_member` VARCHAR(30) DEFAULT NULL COMMENT '会员标识',
  PRIMARY KEY (`u_id`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

创建函数

ELIMITER $$
CREATE
    /*函数*/
    FUNCTION `demo`.`usermoney`(number INT)
    /*返回值类型*/
    RETURNS INT
    /*开始*/
    BEGIN
    /*返回*/
    RETURN (number * 100);    
    /*结束*/
    END$$
DELIMITER ;

用法

SELECT usermoney(20);
SELECT `u_id`,`u_name`,usermoney(`u_money`),`u_day` 
FROM `users`

普通用户练习

DELIMITER $$
/*判断值为空重新赋值*/
CREATE
    FUNCTION `demo`.`umenber`(menber VARCHAR(30))
    RETURNS VARCHAR(30)
    BEGIN
    /*创建一个变量*/
    DECLARE newmenber VARCHAR(30) DEFAULT "普通用户";
    /*如果不为空*/
    IF menber IS NOT NULL THEN
        SET newmenber = menber;
    END IF;
    RETURN newmenber;
    END$$
DELIMITER ;

20日 - 30日是活动日 每天有50的活动奖励（活动奖励每多登录一天，奖励上涨百分之50）

DELIMITER $$
CREATE
    FUNCTION `demo`.`reward`(uid BIGINT(18))
    RETURNS INT(11)
    BEGIN
    /*创建变量必须把所有的变量都放在最上面*/
    /*创建一个变量*/
    DECLARE money INT(11) DEFAULT 0;
    /*初始值*/
    DECLARE i INT(11) DEFAULT 1;
    SELECT COUNT(*) FROM `login_record` 
    WHERE `lr_users_id` = uid
    AND `lr_time` <= "2021-09-30"
    AND `lr_time` >= "2021-09-20"
    INTO @day;
    /*WHILE 循环*/
    WHILE i <= @day DO
        IF i = 1 THEN
            SET money = 50;
        ELSE
            SET money = money * 1.5;
        END IF;
        SET i=i+1;
        END WHILE;
    RETURN money;
    END$$
DELIMITER ;

常用函数

MySQL 数值型函数

MySQL 字符串函数

MySQL 日期和时间函数

MySQL 聚合函数

MySQL 流程控制函数

Mysql存储过程

是一种在数据库中存储复杂程序，以便外部调用的一种数据库对象
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接受参数。
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

• 存储过程可以用在数据检验，强制实行商业逻辑等。

  缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

• 存储过程的性能调校与撰写，受限于各种数据库系统。

  两数相加

  ``sql DELIMITER $$ CREATE /*步骤/过程*/ /*myadd方法名*/ PROCEDUREdemo.myadd`(IN a INT,IN b INT ,OUT c INT) BEGIN SET c = (a + b + 100) * 5; END$$ DELIMITER ;

```sql
/*调用存储过程*/
CALL `myadd`(1,2,@sum);
/*查看结果*/
SELECT @sum;

升级版本

DELIMITER $$
CREATE
    /*步骤/过程*/
    /*myadd方法名*/
    /*写存储过程的时候，一定要进行参数验证*/
    PROCEDURE `demo`.`myadd`(IN a INT,IN b INT ,OUT c INT)
    BEGIN
    IF a IS NULL THEN
        SET a = 0;
    END IF;
    IF b IS NULL THEN
        SET b = 0;
    END IF;
    SET c = (a + b + 100) * 5;
    END$$
DELIMITER ;

CASE的使用

DELIMITER $$
CREATE
    PROCEDURE `demo`.`comm`(IN id INT,OUT ucomm INT)
    BEGIN
    SELECT `u_year` FROM `users` WHERE `u_id` = id INTO @year;
    CASE @year
    WHEN 1 THEN 
        SET ucomm = 100;
    WHEN 2 THEN
        SET ucomm = 200;
    WHEN 3 THEN 
        SET ucomm = 400;
    ELSE
        SET ucomm = 1000;
    END CASE;
    END$$
DELIMITER ;

输入又输出

DELIMITER $$
CREATE
    PROCEDURE `demo`.`myinout`(INOUT number INT)
    BEGIN
    SET number = number + 100;
    END$$
DELIMITER ;

SET @number = 100;
CALL myinout(@number);
SELECT @number;

Mysql索引:

优点

• 索引大大减小了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机IO变成顺序IO

• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

  • 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

    缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

• 对于非常小的表，大部分情况下简单的全表扫描更高效；

  
  索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
  因此应该只为最经常查询和最经常排序的数据列建立索引。
  MySQL里同一个数据表里的索引总数限制为16个。
  

  索引的分类

  在一个表中，主键索引只能有一个，唯一索引可以有多个。

• 主键索引（PRIMARY KEY）

  • 唯一的标识，主键不可重复，只能有一个列作为主键
    
• 唯一索引（UNIQUE KEY）
  • 避免重读的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
    
• 常规索引（KEY / INDEX）
  • 默认的，通过index或key关键字来设置
    
• 全文索引（FULLTEXT）
  • 在特定的数据库引擎下采用，之前只有MYISAM有
    
  • 快速定位数据
    

基础语法
— 索引的使用
— 方式1：在创建表的时候给字段增加索引
— 方式2：表创建完毕后，增加索引

— 显示所有的索引信息
SHOW INDEX FROM student

— 增加一个全文索引：格式：索引类型 索引名（列名），主键索引只要有索引名就行了
ALTER TABLE school.student ADD FULLTEXT INDEX StudentName(StudentName)

— 删除索引
DROP INDEX 索引名 ON 表名

— EXPLAIN分析sql执行的情况
EXPLAIN SELECT FROM student; — 非全文索引
EXPLAIN SELECT FROM student WHERE MATCH(StudentName) AGAINST(‘郭’) — 通过全文索引进行查找

创建表结构

CREATE TABLE `demo`.`users`(  
  `u_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` VARCHAR(50) NOT NULL DEFAULT '用户昵称' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) NOT NULL COMMENT '电话号码',
  `sex` TINYINT(4) DEFAULT 0 COMMENT '0男1女',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT 0 COMMENT '年龄',
  `create_time` DATETIME COMMENT '创建时间',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`u_id`)
);

创建函数添加模拟数据

DELIMITER $$
CREATE
    FUNCTION `demo`.`mock_data`()
    RETURNS INT
    BEGIN
    -- 创建变量为i
    DECLARE i INT(11) DEFAULT 1;
    WHILE i < 1000000 DO
        -- 获取当前系统时间
        SELECT CURRENT_TIMESTAMP INTO @time;
        -- 插入语句
        -- CONCAT(a,b) 把a和b两个字符串拼接
        -- RAND() 随机数
        -- FLOOR() 向下取整
        -- UUID() 唯一ID
        INSERT INTO `users`(`name`,`email`,`phone`,`sex`,`password`,`age`,`create_time`,`update_time`)
        VALUE(
                CONCAT('用户',i),
                CONCAT(FLOOR((RAND()*9999)),i,'@qq.com'),
                CONCAT('13',FLOOR(RAND()*999999999)),
                FLOOR(RAND()*2),
                UUID(),
                FLOOR(RAND()*100),
                @time,
                @time
        );
        SET i = i + 1;
    END WHILE;
    RETURN i;
    END$$
DELIMITER ;

执行函数

SELECT `mock_data`();

执行语句查看耗时

SELECT * FROM `users` WHERE NAME = "用户99999";

执行语句查看耗时

SELECT * FROM `users` WHERE NAME = "用户99999";• 1.

创建索引

CREATE INDEX 索引名 ON 表(字段) 索引名起名规范 id表名字段名

CREATE INDEX id_users_name ON `users`(`name`);

再次执行语句查看耗时

SELECT * FROM `users` WHERE `phone` = "1330039060";