基础：MySQL概述、SQL、函数、约束、多表查询、事务
进阶：存储引擎、索引、SQL优化、视图 / 存储过程 / 触发器、锁、InnoDB核心、MySQL管理
运维：日志、主从复制、分库分表、读写分离

（指令中的[]表示可选部分。大小写都可以。）

相关概念

Database, DB（数据库） 存储数据的仓库，数据是有组织地进行存储的
Database Management System, DBMS（数据库管理系统） 操纵和管理数据库的大型软件
Structured Query Language（SQL） 操作关系型数据库的编程语言，定义了一套操作相关型数据库统一标准

启动、停止、连接

（假设安装时注册到windows系统服务的服务名称为mysql80。）
启动：终端输入

1
net start mysql80

停止：终端输入

1
net stop mysql80

客户端连接：

  • 方式一：MySQL提供的客户端命令行工具：MySQL 8.0 Command Line Client。然后输入密码。
  • 系统自带的命令行输入（需配置Path环境变量）：
    1
    mysql [-h 127.0.0.1] [-p 3306] -u -root -p

数据库类型

关系型数据库（RDBMS）

建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

  1. 使用表存储数据，格式统一，便于维护。
  2. 使用SQL语言操作，标准统一，使用方便。

非关系型数据库

MySQL数据模型

img

MySQL图形化界面

  • Sqlyog
  • Navicat
  • DataGrip

SQL语法

SQL通用语法、SQL分类、DDL、DML、DQL、DCL

通用语法

  1. SQL语句可单行或多行书写，以分号结尾。
  2. SQL语句可使用空格 / 缩进来增强语句的可读性。
  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释：
    单行注释：– 或 #(MySQL特有)
    多行注释：/* */

SQL分类

Data Definition Language, DDL 数据定义语言，定义数据库对象（数据库、表、字段）
Data Manipulation Language, DML 数据操作语言，对数据库表中的数据进行增删改
Data Query Language 数据查询语言，查询数据库表中的数据
Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL(Data Definition Language, 数据定义语言, 表操作)

  • 查询
    查询所有数据库：
    1
    show databases;
    查询当前数据库：
    1
    select database();
  • 创建
    1
    create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]
  • 删除
    1
    drop database [if exists] 数据库名;
  • 使用
    1
    use 数据库名;

如果要设置字符集，建议设置为utf8mb4（支持4个字节），而不是utf8（支持3个字节）。因为在数据库当中有些数据库是占4个字节的。

DDL-表操作-创建

image-20230923010639621

DDL-表操作-查询

  • 查询当前数据库所有表：
    1
    show tables;
  • 查询表结构：
    1
    desc 表名;
  • 查询指定表的建表语句：
    1
    show create table 表名;

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

类型 大小 SIGNED UNSIGNED 描述
TINYINT 1 byte (-128, 127) (0, 255) 小整数值
SMALLINT 2 bytes (-32768, 32767) (0, 65535) 大整数值
MEDIUMINT 3 bytes (-8388608, 8388607) (0, 16777215) 大整数值
INT或INTEGER 4 bytes (-2147483648, 2147483647) (0, 4294967295) 大整数值
BIGINT 8 bytes (-2^63, 2^63-1) (0, 2^64-1) 极大整数值
FLOAT 4 bytes (-3.402823466 E+38, 3.402823466351 E+38) 0 和 (1.175494351 E-38, 3.402823466 E+38) 单精度浮点数值
DOUBLE 8 bytes (-1.7976931348623157 E+308, 1.7976931348623157 E+308) 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) 双精度浮点数值
DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 小数值（精确定点数）

DDL-表操作-修改

  • 添加字段

    1
    alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]

  • 修改数据类型

    1
    alter table 表名 modify 字段名 新增数据类型(长度);

  • 修改字段名和字段类型

    1
    alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]

  • 删除字段

    1
    alter table emp drop username;

  • 修改表名

    1
    alter table 表名 rename to 新表名;

  • 删除表

    1
    drop table [if exists] 表名;

  • 删除指定表，并重新创建该表

    1
    truncate table 表名;

    注意：在删除表时，表中的全部数据也会被删除。

总结

image-20230923232419424

DML(Data Manipulation Language, 数据操作语言)

  • 添加数据
    1
    insert
  • 修改数据
    1
    update
  • 删除数据
    1
    delete

DML-添加数据

  • 给指定字段添加数据

    1
    insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);

  • 给全部字段添加数据

    1
    insert into 表名 values (值1, 值2, ...);

  • 批量添加数据

    1
    insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
    1
    insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意：

  • 插入数据时，指定的字段顺序需要与值的顺序一一对应。
  • 字符串和日期型数据应该包含在引号中。
  • 插入的数据大小，应该在字段的规定范围内。

DML-修改数据

1
updata 表名 set 字段名1 = 值, 字段名2 = 值2, ... [where 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

1
delete from 表名 [where 条件]

注意：

  • DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据.
  • DELETE语句不能删除某一个字段的值(可以使用UPDATE)。

DQL (Data Query Language，数据查询语言)

用来查询数据库中表的记录。

DQL-语法

select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数

  • 基本查询
  • 条件查询 (WHERE)
  • 聚合函数 (count, max, min, avg, sum)
  • 分组查询 (GROUP BY)
  • 排序查询 (ORDER BY)
  • 分页查询 (LIMIT)

DQL-基本查询

image-20230924180118379

DQL-条件查询

image-20230925111909601

DQL-聚合函数

image-20231022011352859

注意：null值不参与所有聚合函数运算。

分组查询

image-20231022012133033

注意：

  • 执行顺序：where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

image-20231022013532803

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

注意：

  • 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DQL-执行顺序

image-20231022015538427

总结

image-20231022022349897

DCL(Data Control Language，数据控制语言)

DCL-管理用户

image-20231028075901011

DCL-权限控制

image-20231028075624834

image-20231028080432609

函数

字符串函数

image-20231028082137922

数值函数

image-20231028155023181

日期函数

image-20231028155230818

流程函数

image-20231028165427692

约束

image-20231028230447032

外键约束

image-20231029001039478

删除/更新行为

多表查询

概述

内连接

外连接

image-20231031010303178

内连接

image-20231031014229080

联合查询

image-20231101050144649

子查询

image-20231101050306241

标量子查询

image-20231101050704366

列子查询

image-20231101050617084

行子查询

image-20231101050822936

表子查询

image-20231101050937829

SQL之case when then用法

https://www.cnblogs.com/Richardzhu/p/3571670.html

事务

事务(Transactions)是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式地提交事务。

事务控制语句：

  • 开启事务 （两种写法均可）
    1
    start transaction;
    1
    begin;
  • 开启事务
    1
    commit;
  • 开启事务
    1
    rollback;

例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 开启事务
start transaction;

-- 删除部门
delete from tb_dept where id = 2;

-- 删除部门下的员工
delete from tb_emp where dept_id = 2;

-- 提交事务
commit;

-- 回滚事务
rollback;

select * from tb_dept;
select * from tb_emp;

事务的四大特性 (ACID)

  • 原子性 (Atomicity)
    事务是不可分割的最小单元，要么全部成功，要么全部失败
  • 一致性 (Consistency)
    事务完成时，必须使所有的数据都保持一致状态
  • 隔离性 (Isolation)
    数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性 (Durability)
    事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

索引

索引(index)是帮助数据库高效获取数据的数据结构。

无索引 -> 全表扫描，数据量越大，性能越低。

优点

  • 提高数据查询的效率，降低数据库的IO成本。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点

  • 索引会占用存储空间。
  • 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的B+Tree结构阻止的索引。

  • 思考：为什么MySQL不采用二叉搜索树或红黑树？
    因为在大数据量的情况下，层级深，检索速度会慢。

  • B+Tree (多路平衡搜索树)
    image-20231228170916077

语法

image-20231228171212856

注意：

  • 主键字段，在建表时，会自动创建主键索引。
  • 添加唯一约束时，数据库实际上会添加唯一索引。