分割线

函数

数据函数

作用于记录

-- 绝对值
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;
  • 逻辑图

    20210316182445
  • 例子

    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 高校获取数据的数据结构.

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

分类

  1. 主键索引 (Primary Key)

    • 最常见,唯一性(一个表只能有一个)
  2. 唯一索引 (Unique)

    • 保证某列中记录值唯一,同一表可以有多个唯一索引
  3. 常规索引 (Key/Index)

    • 默认的索引类型
  4. 全文索引 (FullText)

    • MyISAM/InnoDB 引擎下才能使用,对字段类型也有要求,适用于大型数据

添加&删除索引

方法一:创建表时
 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), '[email protected]', 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 索引