不要额外加糖

Appearance

MySQL ​

基础 ​

MySQL概述 ​

关系型数据库 ​

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

特点：

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

SQL ​

SQL通用语法 ​

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

SQL 分类 ​

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象(数据库，表，字段)
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中的表的记录
DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库访问权限

DDL ​

数据库操作

查询

mysql
# 查询所有数据库
SHOW DATABASES;

# 查询当前数据库
SELECT DATABASE();

创建

mysql
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLIATE 排序规则];

删除

mysql
DROP DATABASE [IF EXISTS] 数据库名;

使用

mysql
USE 数据库名;

表操作-查询

查询当前数据库所有表

mysql
SHOW TABLES;

查询表结构

mysql
DESC 表名;

查询指定表的建表语句

mysql
SHOW CREATE TABLE 表名;

表操作-创建

mysql
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],
    ...
    字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

数据类型

类型大小有符号无符号描述
TINYINT1 byte(-128, 127)(0, 255)小整数值
SMALLINT2 byte(-32768， 32767)(0, 65535)大整数值
MEDIUINT3 byte(-8388608， 8388607)(0, 4294967295)大整数值
INT(INTEGER)4 byte(-2^63, 2^63-1)(0, 2^64-1)大整数值
BIGINT8 byte极大整数值
FLOAT4 byte单精度浮点数
DOUBLE8 byte双精度浮点数
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)
类型大小描述
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过255个字符的二进制数据
TINYTEXT0-255 bytes短文本字符串
BLOB0-65535 bytes二进制形式的长文本数据
TEXT0-65535 bytes长文本数据
MEDIUBLOB0-16777215 bytes二进制形式的中等长度文本数据
MEDIUTEXT0-16777215 bytes中等长度文本数据
LONGBLOB0-4294967295 bytes二进制形式的极大文本数据
LONGTEXT0-4294967295 bytes极大文本数据
类型大小范围格式描述
DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值
TIME3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间
YEAR11901 至 2155YYYY年份值
DATETIME81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳

表操作

添加字段

mysql
ALTER 表名 ADD 字段名 类型(长度) [comment 注释] [约束]

修改数据类型

mysql
ALTER 表名 MODIFT 字段名 新数据类型(长度);

修改字段名和字段类型

mysql
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [comment '注释'] [约束];

删除字段

mysql
ALTER TABLE 表名 DROP 字段名;

修改表名

mysql
ALTER TABLE 表名 RENAME TO 新表名;

删除表

mysql
DROP TABLE [IF EXISTS] 表名;

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

mysql
TRUNCATE TABLE 表名;

DML ​

添加数据

  1. 给指定字段添加数据

    mysql
    INSERT INTO 表名 (字段1, 字段2...) VALUES (值1, 值2...);

  2. 给全部字段添加数据

    mysql
    INSERT INTO VALUES (值1, 值2...);

  3. 批量添加数据

    mysql
    INSERT INTO 表名 (字段1, 字段2...) VALUES (值1, 值2...),(值1, 值2...);
    mysql
    INSERT INTO 表名 VALUES (值1, 值2...),(值1, 值2...);

修改数据

mysql
UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2,... [WHERE 条件];

删除数据

mysql
DELETE FROM 表名 [WHERE 条件];

DQL ​

语法

mysql
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
c 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

基本查询

  1. 查询多个字段

    mysql
    SELECT 字段1, 字段2, ... FROM 表名;
SELECT * FROM 表名；

  2. 设置别名

    mysql
    SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... 表名;

  3. 去重

    mysql
    SELECT DISTINCT 字段列表 FROM 表名;

条件查询

  1. 语法

    mysql
    SELECT 字段列表 FROM 表名 WHERE 条件列表;

  2. 条件

    比较运算符功能
    >大于
    >=大于等于
    <小于
    <=小于等于
    =等于
    <> 或 !=不等于
    BETWEEN...AND...在某个范围之间(含最大、最小值)
    IN(...)在in之后的列表中的值，多选一
    LIKE 占位符模糊匹配(_匹配单个字符，%匹配任意个字符)
    IS NULL是 null
    逻辑运算符功能
    AND 或 &&并且(多个条件同时成立)
    OR 或 ||或者(多个条件满足一个)
    NOT 或 ！非，不是

聚合函数

  1. 介绍：

    将一列数据作为整体，进行纵向计算。

  2. 常见的聚合函数：

    函数功能
    count统计数量
    max最大值
    min最小值
    avg平均值
    sum求和

  3. 语法

    mysql
    SELECT 聚合函数(字段列表) FROM 表名;

分组查询

  1. 语法

    mysql
    SELECT 字段列表 FROM 表名 [where 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

  2. where 和 having 去区别：

    1. 执行时机不同：where 是分组之前进行过滤，不满足条件不参与分组；而 having 是分组之后对结果进行过滤。
    2. 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

分组查询中的字段列表，使用某个符号进行分隔

排序查询

  1. 语法

    mysql
    SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

  2. 排序方式

    1. ASC：升序
    2. DESC：降序

分页查询

  1. 语法

    mysql
    SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

  2. 注意

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

DCL ​

用户管理

  1. 查询用户

    mysql
    USE MYSQL;
SELECT * FROM user;

  2. 创建用户

    mysql
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  3. 修改用户密码

    mysql
    ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

  4. 删除用户

    mysql
    DROP USER '用户名'@'主机名';

权限管理

  1. 常用权限

    权限功能
    ALL, ALL PRIVILEGES所有权限
    SELECT查询数据
    INSERT插入数据
    UPDATE修改数据
    DELETE删除数据
    ALTER修改表
    DROP删除数据库/表/视图
    CREATE创建数据库/表

  2. 权限控制

    查询权限

    mysql
    SHOW GRANTS FOR '用户名'@'主机名';

    授予权限

    mysql
    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

    撤销权限

    mysql
    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数 ​

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数 ​

函数功能
CONCAT(S1,S2,...Sn)字符串拼接
LOWER(str)将str 转为小写
UPPER(str)将str 转为大写
LPAD(str,n,pad)左填充,用字符串pad对str的左边进行填充，达到n个字符
RPAD(str,n,pad)右填充,用字符串pad对str的右边进行填充，达到n个字符
TRIM(str)去掉字符串前后空格
SUBSTRING(str,start,len)字符串截取

数值函数 ​

函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x,y)返回x/y的模
RAND()返回0~1内的随机数
ROUND(x,y)求参数x的四舍五入值，保留y位小数

日期函数 ​

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date, INTERVAL expr unit)返回一个日期/时间值加上一个时间间隔
DATEDIFF(date1, date2)返回两个时间之间的天数

DATE_ADD()用于指定日期添加时间间隔的函数。 它的语法如下: DATE_ADD(date, INTERVAL expr unit)

date是需要增加（减少）时间的日期， expr是需要增加（减少）的时间数量， unit是需要增加（减少）的时间单位（如天、小时、分钟等）。

流程控制函数 ​

函数功能
IF(value, t, f)如果value为true，则返回t，否则返回f
IFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] ... ESLE [default] END如果val1为true，返回res1，...否则返回默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [dafault] END如果expr的值等于val1，返回res1, ...否则返回默认值

约束 ​

  1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  2. 目的：保证数据库中的数据正确、有效性和完整性。
  3. 分类：
约束描述关键字
非空约束限制字段的数据不能为nullNOT NULL
唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE
主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KTY
默认约束保存数据时，如果未指定该字段的值，则采用默认约束DEFAULT
检查约束保证字段值满足某一个条件CHECK
外键约束用来让两张表的数据之间建立连接，保证数据一致性和完整性FOREIGN KEY

外键

  1. 添加外键
mysql
CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主列表名)
);

ALERT TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主列表名);
  1. 删除外键
mysql
ALERT TABLE 表名 DROP FOREIGN KEY 外键名称;

多表查询 ​

概述 ​

项目开发中，在进行数据库结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表之间也存在着各种联系。

  • 一对多
  • 多对多
  • 一对一

概述：指从多张表中查询数据

笛卡儿积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

分类 ​

  1. 连接查询
    • 内连接：相当于查询A、B交集部分数据
    • 外连接：
      • 左外连接：查询左表的所有数据，以及两张表交集部分数据
      • 右外连接：查询右表的所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名
  2. 子查询

内连接

mysql
# 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件;

# 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

外连接

mysql
# 左外连接 查询表1数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

# 右外连接 查询表2数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

自连接

mysql
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;

联合查询

对于联合查询的多张表，列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并，union 会对合并之后的数据去重

mysql
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

自查询

概念：SQL 语句中嵌套 SELECT 语句，成为嵌套查询，又称子查询

  1. 标量子查询(子查询结果为单值)
  2. 列子查询(结果为一列)
  3. 行子查询(结果为一行)
  4. 表子查询(结果为多行多列)

标量子查询

mysql
# 常见操作符 = <> > >= < <=
SELECT 字段列表 FROM 表A WHERE (SELECT 字段 FROM 表B WHERE 条件);

列子查询

mysql
# 常见操作符 IN、 NOT IN、ANY、SOME、ALL

行子查询

mysql
# 常见操作符 =、<>、IN、NOT IN、

表子查询

mysql
# 常见操作符 IN

事务 ​

事务操作 ​

mysql
# 方法一
# 事务自动提交
select @@autocommit;

# 事务设置
set @@autocommit = 1;

# 提交事务
commit;

# 回滚事务
rollback;
mysql
# 方法二
# 开启事务
start transaction 或 begin;

# 提交事务
commit;

# 回滚事务
rollback;

事务四大特性 ​

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

事务并发问题 ​

  • 脏读：一个事务读到另一个事务还没有提交的数据。
  • 不可重复读：一个事务先后读取同一条数据，但两次读取的数据不同，称之为不可重复读。
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据存在，好像出现了幻影。

事务隔离级别 ​

隔离级别脏读不可重复读幻读
Read uncommitted✔✔✔
Read committed✘✔✔
Repeatable（默认）✘✘✔
Serializable✘✘✘
mysql
# 查看隔离级别
select @@transaction_isolation;

# 设置隔离级别
set [session | global] transaction isolation level { 隔离级别 }

进阶 ​

存储引擎 ​

MySQL 体系结构 ​

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

存储引擎简介 ​

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎事基于表的，而并不是基于库的，所以存储引擎可也呗成为表引擎。

创建表时选择存储引擎

mysql
CREATE TABLE 表名(
	...	
) ENGINE=INNODB;

查看当前数据库支持的存储引擎

mysql
SHOW ENGINES;

存储引擎特点 ​

  • InnoDB
    • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎
    • 特点
      • DML操作遵从ACID模型，支持事务
      • 行级锁，提高并发访问性能
      • 支持外键约束，保证数据的完整性和正确性
    • 文件：xxx.ibd，xxx表示表名，InnoDB引擎的每张表都会对应这样的一个表空间，存储该表的表结构、数据和索引
  • MyISAM
    • 介绍：MyISAM是MySQL在早期的默认存储引擎
    • 特点
      • 不支持事务，不支持外键
      • 支持表锁，不支持行锁
      • 访问速度快
    • 文件
      • xxx.sdi：存储表结构信息
      • xxx.MYD：存储数据
      • xxx.MYI：存储索引
  • Memory
    • 介绍：Memory引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能讲这些表作为临时表或缓存使用。
    • 特点
      • 内存存放
      • hashsuoy
    • 文件：xxx.sdi：存储表结构信息

存储引擎选择 ​

  1. InnoDB：是Mysql默认的存储引擎，支持事务，外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB村粗引擎比较合适。
  2. MyISAM：如果应用事宜读操作和插入操作为主，只有很少的更新和删除，并且对事务的完整性、并发性要求不高
  3. Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存，Memory的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引 ​

索引的帮助MySQL 高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点 ​

  • 优势
    • 提高数据检索效率，降低数据库的IO成本
    • 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。
  • 劣势
    • 索引列也是要占用空间的
    • 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT，UPDATE，DELETE，效率降低。

索引结构 ​

索引结构描述
B+Tree索引最常见的索引类型，大部分引擎都支持B+索引
Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
Full-text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式

索引分类 ​

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特点数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT
分类含义特点
聚焦索引将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引将数据与索引分开存储，索引结构的叶子节点关联的是对应主键可以存在多个

索引语法 ​

创建索引

mysql
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name);

查看索引

mysql
SHOW INDEX FROM table_name;

删除索引

mysql
DROP INDEX index_name ON table_name;

SQL性能分析 ​

  • SQL执行频率

    MySQL 客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。

    mysql
    SHOW GLOBAL STATUS LIKE 'Com_______'

  • 慢查询日志

    慢查询日志记录了所有执行时间超过指定参数(long_query_time, 单位秒，默认10秒)的所有SQL语句的日志

    MySQL默认没有开启，需要自行开启。

  • profile详情

    show profile 能够在SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profile参数，能够看到当前MySQL是profile操作：

    mysql
    SELECT @@have_profile;

SHOW PROFILES；

  • explain执行计划

    EXPLAIN或者 EDSC命令获取 MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

    mysql
    EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

Released under the MIT License.

Copyright © 2023-present Bin