database

简介：数据库基础，包括SQL、函数、约束、多表查询、事务、存储引擎、索引和SQL优化等。

database

1. 目标

  • 熟练掌握基本的SQL语法、函数、约束、多表查询以及事务
  • 基本掌握存储引擎、索引、SQL优化、视图/存储过程/触发器、锁、InnoDB核心以及MySQL管理

2. 开始

2.1. MySQL的启动与停止

  • win+r services.msc
  • net start mysql80 / net stop mysql80 以管理员运行cmd

2.2. MySQL客户端连接

  • MySQL 8.0 Command Line Client
  • mysql [-h 127.0.0.1] [-P 3306] -u root -p

3. 2. SQL

3.1. SQL通用语法

  • SQL语句可以单行或多行，以分号结尾

  • SQL语句可以使用空格/缩进来增强语句的可读性

  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

  • 注释：

    • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）

    • 多行注释：/* 注释内容 */

3.2. SQL分类

  • DDL：定义数据库对象（数据库、表、字段）
  • DML：操作-对数据库中表的数据记录进行增删改操作
  • DQL：查询
  • DCL：控制-创建数据库用户、控制数据库的访问权限

3.3. DDL

3.3.1. 数据库操作

  • 查询

    • 查询所有数据库：SHOW DATABASES;

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

  • 创建：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

  • 删除：DROP DATABASE [IF EXISTS] 数据库名;

  • 使用：USE 数据库名;

3.3.2. 表操作

  • 查询

    • 查询当前数据库所有表：SHOW TABLES;

    • 查询表结构：DESC 表名;

    • 查询指定表的建表语句：SHOW CREATE TABLE 表名;

  • 创建

1
2
3
4
5
6
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	......
	字段N 字段N类型[COMMENT 字段N注释]
)[COMMENT 表注释];
  • 数据类型
    • 数值类型：
      • 字符串类型：char varchar
      • 日期时间类型：
      • DATE：YYYY-MM-DD
        • TIME：HH:MM:SS
        • YEAR：YYYY
        • DATETIME：YYYY-MM-DD HH:MM:SS
        • TIMESTAMP：YYYY-MM-DD HH:MM:SS
  • 修改
    • 添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
      • 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
      • 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
      • 删除字段：ALTER TABLE 表名 DROP 字段名;
      • 修改表名：ALTER TABLE 表名 RENAME TO 新表名;
  • 删除
    • 删除表：DROP TABLE [IF EXISTS] 表名;
      • 删除表并重新创建：TRUNCATE TABLE 表名;

3.4. DML

  • 添加

    • 给指定字段添加数据：INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,..);

    • 给全部字段添加数据：INSERT INTO 表名 VALUES (值1,值2,...);

    • 批量添加：

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

  • 修改

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

  • 删除

    • DELETE FROM 表名 [WHRER 条件];

3.5. DQL

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	字段列表
FROM
	表名列表
WHRER
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

3.5.1. 基本查询

  • 查询多个字段：

    • SELECT 字段1,字段2,字段3... FROM 表名;

    • SELECT * FROM 表名;

  • 设置别名：SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名; # AS可以省略 #

  • 去重：SELECT DISTINCT 字段列表 FROM 表名;

3.5.2. 条件查询

  • 语法：SELECT 字段列表 FROM 表名 WHRER 条件列表;
  • 条件：
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN ... AND ... 在某个范围之内(含最小、最大值)
IN(...) 在in之后的列表中的值，多选一
LIKE 占位符 模糊匹配('_'匹配单个字符，'%'匹配任意个字符)
IS NULL 是NULL
AND 或 && 多个条件同时成立
OR 或 || 多个条件任意一个成立
NOT 或 ！ 非，不是

3.5.3. 聚合函数

  • 聚合函数：将一列数据作为一个整体，进行纵向计算
  • 语法：SELECT 聚合函数(字段列表) FROM 表名;
  • 常见聚合函数：
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
  • null值不参与聚合运算

3.5.4. 分组查询

  • 语法：SELECT 字段列表 FROM 表名 [WHRER 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
  • WHERE与HAVING的区别：
    • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；
      • 判断条件不同：where不能对聚合函数进行判断，而having可以。
  • 执行顺序：where > 聚合函数 > having

3.5.5. 排序查询

  • 语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式2;
  • 排序方式：ASC-升序 / DESC-降序

3.5.6. 分页查询

  • 语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
  • 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
  • 分页查询不同数据库有不同的实现，MySQL中式LIMIT
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

3.6. DCL

3.6.1. 管理用户

  • 查询用户：USE mysql; SELECT * FROM user;
  • 创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 主机名可以用%通配
  • 修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
  • 删除用户：DROP USER '用户名'@'主机名';

3.6.2. 权限控制

  • 常用权限：
权限 说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
  • 查询权限：SHOW GRANTS FOR '用户名'@'主机名';
  • 授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  • 撤销权限：REVOKE 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

4. 函数

4.1. 字符串函数

函数 功能
CONCAT(S1,S2,...Sn) 字符串拼接
LOWER(str) 转为小写
UPPER(str) 转为大写
LPAD(str,n,pad) 左填充，填充到str长度为n
RPAD(str,n,pad) 右填充，填充到str长度为n
TRIM(str) 去掉首位空格
SUBSTRING(str,start,len) 连续子串，从1开始

4.2. 数值函数

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

4.3. 日期函数

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

4.4. 流程函数

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

5. 约束

5.1. 基础

  • 概念：约束是作用与表中字段上的规则，用于限制存储在表中的数据。保证数据库中数据的正确、有效性和完整性。
  • 分类
约束 描述 关键字
非空约束 该字段的数据不能为null NOT NULL
唯一约束 字段的所有数据唯一、不重复 UNIQUE
主键约束 一行数据唯一标识非空且唯一 PRIMARY KEY
默认约束 若未指定,则采用默认约束 DEFAULT
检查约束 保证字段值满足某一个条件 CHECK
外键约束 让两张表的建立连接 FOREIGN KEY

5.2. 外键约束

  • 添加外键

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

    • ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

  • 删除外键

    • ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

  • 删除/更新行为

行为 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION一致)
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为bull。(要求该外键允许取null)
SET DEFAULT 父表有变更时，子表将外键列设置成一个默认值。(Innode不支持)
  • 语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

6. 多表查询

6.1. 多表关系

  • 一对多（多对一）：在多的一方建立外键，指向一的一方的主键
  • 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键
  • 一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

6.2. 多表查询概述

多表查询分类：

  • 连接查询

    • 内连接：相当于查询A、B交集部分的数据

    • 外连接：

      • 左外连接：查询左表所有数据，以及两表交集部分数据

      • 右外连接：查询右表所有数据，以及两表交集部分数据

    • 自连接：当前表与自身的连接查询，自连接必须使用表别名

  • 子查询

6.3. 内连接

  • 概述：内连接查询两表交集部分的数据
  • 语法：
    • 隐式内连接：SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
      • 显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

6.4. 外连接

  • 概述：查询左表(右表)的所有数据 包含 交集部分的数据

  • 语法：

    • 左外连接：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

    • 右外连接：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

6.5. 自连接

  • 概述：自连接查询，可以是内连接查询，也可以是外连接查询。一定要取别名。
  • 语法：SELECT 字段列表 FROM 表A 别名A [INNER] JOIN 表A 别名B ON 连接条件...;

6.6. 联合查询

  • 概述：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
  • 语法：SELECT 字段列表 FROM 表A ...

​ UNION [ALL]

​ SELECT 字段列表 FROM 表B ...;

  • 联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
  • union all 会将全部数据直接合并在一起，union对去重。

6.7. 子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
  • 语法 ：SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2); 子查询外部可以是INSERT/UPDATE/DELETE/SELECT
  • 根据子查询结果分类：
    • 标量子查询(子查询结果为单个值)
      • 列子查询(子查询结果为一列)
      • 行子查询(子查询结果为一行)
      • 表子查询(子查询结果为多行多列)
  • 根据子查询位置分类：WHERE之后、FROM之后、SELECT之后
  • 常用操作：IN、SOME、ANY、ALL

7. 事务

7.1. 事务简介

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

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

7.2. 事务操作

  • 查看/设置事务提交方式
    • 查看：SELECT @@autocommit;
      • 设置：
        • 法一：SET @@autocommit=0; 0-手动/1-自动
        • 法二：START TRANSACTON 或 BEGIN;
  • 提交事务
    • COMMIT;
  • 回滚事务
    • ROLLBACK;

7.3. 事务四大特性(ACID)

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

7.4. 并发事务问题

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

7.5. 事务隔离级别

  • 事务隔离级别
隔离级别 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×
  • 查看事务隔离级别：SELECT @@TRANSACTION_ISOLATION;
  • 设置事务隔离级别：SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};

8. 存储引擎

8.1. MySQL体系结构

architecture
architecture2

8.2. 存储引擎简介

  • 在创建表时，指定存储引擎
1
2
3
4
5
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE=INNODB [COMMENT 表注释]；
  • 查看当前数据库支持的存储引擎：SHOW ENGINES;

8.3. 存储引擎特点

8.3.1. InnoDB

  • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后是默认的存储引擎

  • 特点：

    • DML操作遵循ACID模型，支持事务

    • 行级锁，提高并发访问性能

    • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性

  • 文件：

    • xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引
    • 参数：innodb_file_per_table

  • 逻辑存储结构

InnoDB逻辑结构

8.3.2. MyISAM

  • 介绍：MyISAM是MySQL早期的默认存储引擎

  • 特点：

    • 不支持事务，不支持外键

    • 支持表锁，不支持行锁

    • 访问速度快

  • 文件：

    • xxx.sdi：存储表结构信息

    • xxx.MYD：存储数据

    • xxx.MYI：存储索引

8.3.3. Memory

  • 介绍：Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

  • 特点：

    • 内存存放

    • hash索引（默认）

  • 文件：xxx.sdi：存储表结构信息

8.3.4. 存储引擎特点

特点 InnoDB MyISAM Memory
存储限制 64TB 有 有
事务安全 支持 - -
锁机制 行锁 表锁 表锁
B+tree索引 支持 支持 支持
Hash索引 - - 支持
全文索引 支持(5.6版本之后) 支持 -
空间使用 高 低 N/A
内存使用 高 低 中等
批量插入速度 低 高 高
支持外键 支持 - -

8.4. 存储引擎选择

  • InnoDB：应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作。
  • MyISAM：应用以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。
  • Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

9. 索引

9.1. Mysql安装(linux)

  • 安装：sudo apt install mysql-server
  • 验证：sudo systemctl status mysql
  • 启动 MySQL 服务器： sudo systemctl start mysql
  • 停止 MySQL 服务器： sudo systemctl stop mysql
  • 重启 MySQL 服务器： sudo systemctl restart mysql
  • 配置 MySQL 服务器自启动： sudo systemctl enable mysql
  • 连接：sudo mysql -u root -p
  • 修改密码：sudo mysqladmin -u root -p password

9.2. 索引概述

  • 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。
优势 劣势
提高数据检索的效率，降低数据库的IO成本 索引列占用空间
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，降低效率

9.3. 索引结构

  • 简介：MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持B+树索引
Hash索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引) 空间索引时MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene,Solr,ES
索引 InnoDB MyISAM Memory
B+Tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-tree 不支持 支持 不支持
Full-text 5.6后支持 支持 不支持

9.4. 索引分类

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

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引
  • 如果都没有，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

9.5. 索引语法

  • 创建索引：CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...);
  • 查看索引：SHOW INDEX FROM table_name;
  • 删除索引：DROP INDEX index_name ON table_name;

9.6. SQL性能分析

  • SQL执行频率：

    • show [session|global] status命令提供服务器状态信息

    • SHOW GLOBAL STATUS LIKE 'Com_'; 7个 ,查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

  • 慢查询日志：

    • 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf-linux c:Server 8.0的my.ini-win)中配置如下信息：

      1
      2
      3
      4
      # 开启MySQL慢日志查询开关
      slow_query_log=1
      # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
      long_query_time=2

    • 查看慢日志是否开启：SHOW VARIABLES LIKE 'SLOW_QUERY_LOG'

    • 查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

  • profile详情： SHOW PROFILES能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

    • 查看是否支持profile：SELECT @@having_profiling;

    • 默认profiling是关闭的，开启：SET profiling=1;

1
2
3
4
5
6
# 查看每一条SQL的耗时基本情况
SHOW PROFILES;
# 查看指定query_id的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY query_id;
# 查看指定query_id的SQL语句CPU的使用情况
SHOW PROFILE CPU FOR QUERY query_id;

9.7. 索引使用

9.8. 索引设计原则

10. SQL优化

11. 视图/存储过程/触发器

12. 锁

13. InnoDB引擎

14. MySQL管理

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

<br><br>本博客基于hexo框架的yilia主题<br>