🚀进修MySQL~(叁)

函数
事务
- ACID 原则
- 基本语法
索引

函数

数据函数

作用于记录

-- 绝对值
SELECT
  ABS(-8);

-- 向上取整
SELECT
  CEILING(9.4);

-- 向下取整
SELECT
  FLOOR(9.4);

-- 随机数,返回一个0-1之间的随机数
SELECT
  RAND();

-- 符号函数: 负数返回-1,正数返回1,0返回0
SELECT
  SIGN(0);

作用于字段

也叫 聚合函数(常用)

/*
  三种COUNT()用法及区别

  count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几;而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。

  1）在表没有主键时，count(1)比count(*)快
  2）有主键时，主键作为计算条件，count(主键)效率最高；
  3）若表格只有一个字段，则count(*)效率较高。
 */
SELECT
  COUNT(studentname) AS `学生人数`
FROM
  student;

SELECT
  COUNT(*) AS `学生人数`
FROM
  student;

SELECT
  COUNT(1) AS `学生人数`
FROM
  student;

-- 和
SELECT
  SUM(StudentResult) AS `总 和`
FROM
  result;

-- 平均
SELECT
  AVG(StudentResult) AS `平 均 分`
FROM
  result;

-- 最高
SELECT
  MAX(StudentResult) AS `最 高 分`
FROM
  result;

-- 最低
SELECT
  MIN(StudentResult) AS `最 低 分`
FROM
  result;

字符串函数

-- 返回字符串包含的字符数
SELECT
  CHAR_LENGTH('狂神说坚持就能成功');

-- 合并字符串,参数可以有多个
SELECT
  CONCAT('我', '爱', '程序');

-- 替换字符串,从某个位置开始替换某个长度
SELECT
INSERT('我爱编程helloworld', 2, 1, '超级热爱');

-- toLowerCase小写
SELECT
  LOWER('KuangShen');

-- toUpperCase大写
SELECT
  UPPER('KuangShen');

-- 从左边截取
SELECT
  LEFT('hello,world', 5);

-- 从右边截取
SELECT
  RIGHT('hello,world', 5);

-- 替换字符串
SELECT
  REPLACE('狂神说坚持就能成功', '坚持', '努力');

-- 截取字符串,开始和长度
SELECT
  SUBSTR('狂神说坚持就能成功', 4, 6);

-- 反转
SELECT
  REVERSE('狂神说坚持就能成功');

-- 查询姓周的同学,改成邹 (采用子查询)
SELECT
  *
FROM
  student
WHERE
  studentname =(
    SELECT
      REPLACE(studentname, '周', '邹')
    WHERE
      studentname LIKE '周%'
  );

时间函数

-- 获取当前日期(两种一样)
SELECT
  CURRENT_DATE();
SELECT
  CRUDATE();

-- 获取当前日期和时间(三种一样)
SELECT
  NOW();
SELECT
  LOCALTIME();
SELECT
  SYSDATE();

-- 获取年月日,时分秒
SELECT
  YEAR(NOW());
SELECT
  MONTH(NOW());
SELECT
  DAY(NOW());
SELECT
  HOUR(NOW());
SELECT
  MINUTE(NOW());
SELECT
  SECOND(NOW());

系统信息

-- 版本
SELECT
  VERSION();

-- 用户(MySQL的用户)
SELECT
  USER();
SELECT
  SYSTEM_USER();

分组

/*
  查询 不同课程 的平均分,最高分,最低分 (根据不同的课程进行分组)
  分组后面的筛选,使用 `HAVING`
  having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的
  */
SELECT
  subjectname,
  AVG(studentresult) AS `平 均 分`,
  MAX(StudentResult) AS `最 高 分`,
  MIN(StudentResult) AS `最 低 分`
FROM
  result AS r
  INNER JOIN `subject` AS s ON r.subjectno = s.subjectno
GROUP BY
  r.subjectno
HAVING
  `平 均 分` > 60;

MD5 加密

Message-Digest Algorithm 5（信息-摘要算法 5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法（摘要算法、哈希算法）.

初始化

-- 新建表
CREATE TABLE `testmd5` (
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入数据(明文密码)
INSERT INTO
  testmd5
VALUES
  (1, 'kuangshen', '123456'),(2, 'qinjiang', '456789');

加密

-- 加密全列
update
  testmd5
set
  pwd = md5(pwd);

-- 插入时加密
INSERT INTO
  testmd5
VALUES(4, 'kuangshen3', md5('123456'));

-- 检查账号密码是否对应
SELECT
  *
FROM
  testmd5
WHERE
  `name` = 'kuangshen'
  AND `pwd` = MD5('123456');

事务

一组遵循 ACID 原则的 SQL 指令

ACID 原则

原子性(Atomic)
- 同一批次的 SQL 指令,要么全通过,要么全取消,不存在中间态
一致性(Consist)
- 事务前后数据完整性一致
隔离性(Isolated)
- 多个用户并发执行多事务,会使其串行化执行,同一时间只能有一个请求作用于同一数据
- 有可能隔离失败导致一些问题(如下)
1. 脏读 : 一个事务读取了另一个没有提交的事务
2. 不可重复读 : 在同一个事务内，再次读取表中的数据，表发生了改变
3. 幻读(虚读) : 在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
持久性(Durable)
- 事务一旦提交成功,便不会被外界因素影响而导致回滚.

基本语法

/*
  MySQL中默认是自动提交,使用事务时应先关闭自动提交
  关闭自动提交模式(0),开启(1)
  */
SET autocommit = 0;

-- 开始一个事务,标记事务的起始点
START TRANSACTION;

-- 此处写业务代码

/*
  COMMIT; -- 提交一个事务给数据库
  ROLLBACK; -- 将事务回滚,数据回到本次事务的初始状态
  保存点(了解,不常用)
    SAVEPOINT `保 存 点 名 称`; -- 设置一个事务保存点
    ROLLBACK TO SAVEPOINT `保 存 点 名 称`; -- 回滚到保存点
    RELEASE SAVEPOINT `保 存 点 名 称`; -- 删除保存点
  */
COMMIT;

-- 还原MySQL数据库的自动提交
SET autocommit = 1;

逻辑图

例子

CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `shop`;

CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9, 2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO
  account (`name`, `cash`)
VALUES('A', 2000.00),('B', 10000.00);

-- 开始
SET
  autocommit = 0;

START TRANSACTION;

UPDATE
  account
SET
  cash = cash -500
WHERE
  `name` = 'A';

UPDATE
  account
SET
  cash = cash + 500
WHERE
  `name` = 'B';

COMMIT;

SET
  autocommit = 1;

索引

索引是帮助 MySQL 高校获取数据的数据结构.

提高查询,分组,全文搜索速度
表间参照&数据唯一性功能.

添加&删除索引

方法一：创建表时
　CREATE TABLE 表名 (
    字段名1 数据类型 [完整性约束条件…],
    字段名2 数据类型 [完整性约束条件…],
    索引类型 [索引名] (字段名[(长度)] [ASC |DESC])
  );

方法二：CREATE在已存在的表上创建索引
  CREATE 索引类型 索引名
  ON 表名 (字段名[(长度)] [ASC |DESC]) ;

方法三：ALTER TABLE在已存在的表上创建索引
  ALTER TABLE 表名
    ADD 索引类型 索引名 (字段名[(长度)] [ASC |DESC]) ;

-- 例子:增加全文索引
  ALTER TABLE `school`.`student`
    ADD FULLTEXT INDEX `studentname` (`studentName`);

#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;

#显示索引信息: SHOW INDEX FROM 表名;

EXPLAIN

/*
  EXPLAIN : 分析SQL语句执行性能
  常规索引
*/
EXPLAIN
SELECT *
FROM student
WHERE studentno = '1000';

/*
  使用全文索引
  全文搜索通过 MATCH() 函数完成。
  搜索字符串作为 against() 的参数被给定。对于表中的每个记录行，MATCH() 返回一个相关性值。即搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
*/
EXPLAIN
SELECT *
FROM student
WHERE MATCH(studentname) AGAINST('刘');

性能分析

建表

CREATE TABLE `app_user`
(
  `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name`        varchar(50)              DEFAULT '' COMMENT '用户昵称',
  `email`       varchar(50)         NOT NULL COMMENT '用户邮箱',
  `phone`       varchar(20)              DEFAULT '' COMMENT '手机号',
  `gender`      tinyint(4) unsigned      DEFAULT '0' COMMENT '性别（0:男；1：女）',
  `password`    varchar(100)        NOT NULL COMMENT '密码',
  `age`         tinyint(4)               DEFAULT '0' COMMENT '年龄',
  `create_time` datetime                 DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp           NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='app用户表';

生成随机数据

DELIMITER $$
CREATE FUNCTION mock_data()
  RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i < num
    DO
      INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
      VALUES (CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND() * (999999999 - 100000000) + 100000000)),
              FLOOR(RAND() * 2), UUID(), FLOOR(RAND() * 100));
      SET i = i + 1;
    END WHILE;
  RETURN i;
END;
SELECT mock_data();

上面的有可能无效,可以用下面的

{
  "schema": "study",
  "table": "app_user",
  "mockStartIndex": "auto",
  "mockCount": 100000,
  "mockValueReference": "http://mockjs.com/examples.html#DPD",
  "mock": {
    "id": {
      "type": "bigint",
      "value": "$mockIndex"
    },
    "name": {
      "type": "varchar",
      "value": "@string('lower',5)"
    },
    "email": {
      "type": "varchar",
      "value": "@string('lower',5)"
    },
    "phone": {
      "type": "varchar",
      "value": "@string('lower',5)"
    },
    "gender": {
      "type": "tinyint",
      "value": "@integer(0,1)"
    },
    "password": {
      "type": "varchar",
      "value": "@string('lower',5)"
    },
    "age": {
      "type": "tinyint",
      "value": "@integer(0,100)"
    },
    "create_time": {
      "type": "datetime",
      "value": "@now('yyyy-MM-dd HH:mm:ss')"
    },
    "update_time": {
      "type": "timestamp",
      "value": "@now('yyyy-MM-dd HH:mm:ss')"
    }
  }
}

性能测试

无索引下 select 耗时 & 搜索行数

study> SELECT
        *
      FROM
        app_user
      WHERE
          name = 'wkjvo'
[2021-03-17 12:06:29] 在 202 ms (execution: 72 ms, fetching: 130 ms) 中从 1 开始检索到1 行


rows: 99946

添加索引

CREATE INDEX idx_app_user_name ON app_user(name);

有索引的 select

study> SELECT
        *
      FROM
        app_user
      WHERE
          name = 'wkjvo'
[2021-03-17 12:12:11] 在 100 ms (execution: 6 ms, fetching: 94 ms) 中从 1 开始检索到1 行


rows: 1

索引规矩

无索引时是暴力遍历搜索,数据表越大越慢;有索引时是对特定数据结构索引,搜索速度快,但是在增删改时有额外性能开销.
1. 不要对经常变动的数据加索引
2. 小数据量的表建议不要加索引
3. 一般只在查找条件的字段索引
索引数据结构
1. hash 单个索引快,范围索引慢
2. Btree
不同的数据库引擎所支持的索引类型以及索引数据结构不同
- InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
- MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引

🚀进修MySQL~(叁)

函数

数据函数

作用于记录

作用于字段

字符串函数

时间函数

系统信息

分组

MD5 加密

事务

ACID 原则

基本语法

索引

分类

添加&删除索引

EXPLAIN

性能分析

索引规矩

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎
高情商：兼容性极强

函数

数据函数

作用于记录

作用于字段

字符串函数

时间函数

系统信息

分组

MD5 加密

事务

ACID 原则

基本语法

索引

分类

添加&删除索引

EXPLAIN

性能分析

索引规矩

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎高情商：兼容性极强

低情商：代码一坨屎
高情商：兼容性极强