0. 相关概念
~ DB / RDB - relational database
~ DBMS / RDBMS - relational database management system
~ DBA - database administrator
~ DBS - database system
~ SQL / NoSQL / NewSQL - structured query language / not only SQL
- KV数据库 - Redis / DynamoDB - 性能极好 - 游戏开发 / IoT应用 / 实时分析
- 文档数据库 - MongoDB / DynamoDB / CouchDB - 价值低体量大 / 扩展能力 - 内容管理 / 日志处理 / 实时协作同步
- 列族数据库 - Cassandra / HBase - 大数据场景下提数性能好 / 实时查询
- 图数据库 - Neo4j / MemGraph - Cypher - 社交网络 / 推荐系统 / 流数据处理
- 时序数据库 - InfluxDB / Prometheus / DolphinDB - 时间序列分析 - 金融 / 监控
- 搜索引擎 - ElasticSearch / Solr - 倒序索引 - 全文检索
1. 关系型数据库的特征
~ 理论基础 - 关系代数（集合论、一阶逻辑、关系运算）
~ 具体表象 - 用二维表组织数据
- 表 - 实体（entity）- 关系（relation）
- 行 - 记录（record）- 元组（tuple）
- 列 - 字段（field）- 属性（attribute）
- 主键（primary key）- 能够唯一标识一条记录的字段
- 外键（foreign key）- 外来的主键（其他表的主键或唯一性字段）
- 行数 - 势（cardinality）
- 列数 - 度（degree）
- 数据取值范围 - 域（domain）
~ 编程语言 - 结构化查询语言 - SQL
- 数据定义语言 - DDL - create / drop / alter / rename / truncate
- 数据操作语言 - DML - insert / delete / update / select
- 数据控制语言 - DCL - grant to / revoke from
- 事务控制语言 - TCL - start transaction / commit / rollback
2. 关系型数据库相关产品
~ Oracle - PL/SQL
~ MySQL - Hive - HQL
~ IBM DB2
~ SQLServer - T-SQL
~ PostgreSQL
~ MariaDB
-------------
~ Snowflake
~ Greenplum
~ Teradata
3. MySQL相关知识
~ 性能好、成本低、可靠性高
~ MySQL的SQL方言跟大数据平台的HiveSQL相似度最高
~ 安装和启动
- Windows环境 - MySQL Installer - Windows服务 - MySQL80
- macOS环境 - MySQL Server / MySQL Workbench - 系统设置 - MySQL
- Linux环境 - CentOS / Redhat - 包管理工具RPM
~ 连接MySQL服务器
- 命令行客户端 - mysql -h localhost -u root -p
- 图形化客户端
~ Workbench - 功能比较完备，界面不够漂亮 - 官方工具
~ Navicat - 付费工具，界面漂亮，功能比较强大 - PremiumSoft
~ SQLyog - 付费/社区，功能比较强大，界面一般 - Webyog
~ DataGrip - 支持多种数据库，智能代码编辑器，资源占用高 - JetBrains
4. MySQL命令
~ show - 显示相关信息
- 显示所有数据库：show databases;
- 显示所有二维表：show tables;
- 显示所有字符集：show charset;
- 显示所有排序规则：show collation;
- 显示存储引擎：show engines;
~ help - 获取帮助信息
~ system - 调用系统命令
- 清屏：system clear; / system cls;
- 查看文件：system ls; / system dir;
~ source - 执行SQL脚本
~ edit - 打开文本编辑器
~ quit - 退出客户端
5. 使用DDL建库建表
~ 创建数据库
create database school default charset utf8mb4;
~ 删除数据库
drop database school;
~ 切换上下文
use school;
~ 创建二维表
create table students
(
stu_id int not null,
stu_name varchar(20) not null,
stu_sex boolean not null default 1,
stu_birth date not null,
primary key (stu_id)
) engine innodb;
~ 显示表结构
desc students;
~ 删除二维表
drop table students;
~ 修改表添加列
alter table students add column stu_tel char(11) not null;
~ 修改表删除列
alter table students drop column stu_tel;
~ 修改表修改列
alter table students modify column stu_sex char(1) not null default '男';
alter table students change column stu_sex stu_gender char(1) not null default '女';
~ 修改表添加约束
alter table students add constraint ck_students_gender check (stu_gender = '男' or stu_gender = '女');
~ 修改表删除约束
alter table students drop constraint ck_students_gender;
~ 修改表名
alter table students rename to tb_student;
6. SQL数据类型
help 'data types';
help 'decimal';
help 'varchar';
https://devdocs.io/mariadb/
~ 整数 - tinyint / smallint / mediumint / int（integer） / bigint
1字节 2字节 3字节 4字节 8字节
smallint - 取值范围从 -32768 到 32767
smallint unsigned - 只能表示0和正数 - 取值范围从 0 到 65535
----------------------------------------------------------------
int / integer - 取值范围从 -2^31 到 2^31-1
int unsigned - 无符号整数 - 只能表示0和正数 - 取值范围从 0 到 2^32-1
int(4) zerofill - 12 --> 0012 / 123 ---> 0123 / 1234 ---> 1234
----------------------------------------------------------------
bigint - 取值范围从 -2^63 到 2^63-1
bigint unsigned - 只能表示0和正数 - 取值范围从 0 到 2^64-1
~ 小数 - float / double / numeric / decimal / dec
decimal(M,D) / numeric(M,D) - M位有效数字，小数点后面有D位有效数字
最大值：M - 65, D - 30
默认值：M - 10, D - 0
经验：不要使用float和double两种类型！！！
~ 字节串 - binary / varbinary - 二进制数据
~ 字符串 - char / varchar
char(M) / charater(M) - 定长字符串 - 255
varchar(M) / character varying(M) - 变长字符串 - 65535
注意：如果使用utf8mb4编码 ---> varchar(16383)
经验：不要把大文本或大二进制数据放到数据库二维表中，比较好的做法是保存文件的资源路径
~ 日期和时间 - date / time / datetime / timestamp
date - 日期 - 1998-05-08
time - 时间 - 14:25:39
datetime - 日期时间 1998-05-08 14:25:39
经验：尽量不使用timestamp类型，避免出现2038问题！！！
~ 其他类型
boolean - tinyint - 1 / 0
enum / set - 有限选项 - enum('男', '女') / enum('东', '南', '西', '北')
longtext / longblob - 文本大对象（CLOB）/ 二进制大对象（BLOB）- 4G
json - JSON数组 / JSON对象 - [1, 2, 3] / {'name': '骆昊', 'age': 43} - 4G
7. 表关系的建立
~ 实体关系图（ER图）- 设计表以及表关系
- 实体 - 矩形框
- 属性 - 椭圆框
- 关系 - 菱形框
~ 建模工具 - 正向工程 / 反向工程
- PowerDesigner - 概念模型图 ---> 物理模型图 ---> SQL方言
- ER/Studio
- Enterprise Architect
- Visual Paradigm
~ 一对一 - 多对一的特例，可以将任何一方视为多添加外键列，再给外键列加唯一约束即可
~ 一对多/多对一 - 在多的一方添加外键列，用于维系多对一关系（考虑性能可以不添加外键约束）
~ 多对多 - 创建中间表，将多对多关系变成两个多对一关系（经典做法）
8. 写数据操作
~ 需要考虑数据的完整性（data integrity）
- 实体完整性 - 每条记录都是独一无二的 - 主键约束 / 唯一约束 - duplicate entry
- 参照完整性 / 引用完整性 - 从表的数据要参照主表的数据 - 外键约束 - foreign key constraint fails
- 域完整性 - 不能写入无效的数据 - 数据类型 / 长度限制 / 非空约束 / 默认值约束 / 检查约束
~ 插入数据 - insert
insert into 表名
(字段1, 字段2, ...)
values
(值1, 值2, ...);
insert into 表名
(字段1, 字段2, ...)
values
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...);
~ 删除数据 - delete
delete
from 表名
where 条件;
~ 更新数据 - update
update 表名
set 字段1 = 值1
, 字段2 = 值2
, ...
where 条件;
9. 读数据操作 - 业务提数
~ 投影 - select子句
- 别名 - as
- 去重 - distinct
~ 数据来源 - from子句
- 表连接 - 连接事实表和维度表
~ cross join - 交叉连接 - 笛卡尔积
~ inner join - 内连接 - on - 连表条件
~ natural join - 自然连接 - 同名列
~ left join - 左外连接 - on - 连表条件 - 左表不满足连表条件的记录也能取到
~ right join - 右外连接 - on - 连表条件 - 右表不满足连表条件的记录也能取到
~ full join - 全外连接 - MySQL不支持全外连接
~ 分组前筛选 - where子句
- 算术运算 - +、-、*、/
- 比较运算 - =、<>、<、>、<=、>=
- 范围运算 - between ... and ...
- 逻辑运算 - and、or、not
- 模糊查询 - like、regexp
- 空值运算 - is null / is not null
- 其他运算 - all、any、in、not in
- 函数运算 - trim / char_length / substring / timestampdiff
~ 分组 - group by子句 - 给每个组做统计
- 聚合函数
~ sum - 求和
~ avg - 算术平均
~ max - 最大值
~ min - 最小值
~ count / count(distinct) - 计数
~ variance / var_pop / var_samp - 方差
~ stddev / stddev_pop / stddev_samp - 标准差
~ 分组后筛选 - having子句 - 可以写聚合函数
~ 排序 - order by子句
- 升序 - asc（默认）
- 降序 - desc
~ 分页 - limit / offset 子句 - MySQL方言
10. 窗口函数 - 数据分析函数（OLAP函数） - 开窗打标
~ 函数() over ()
~ 函数() over (order by ...)
~ 函数() over (partition by ... order by ...)
~ 函数() over (order by ... rows between ... and ...)
~ 函数() over (order by ... range between ... and ...)
- preceding / following / unbounded
- current row
- 定义窗口 - window w as (...) - 实现对窗口的复用
~ 有哪些函数可以在窗口函数语法中使用
- 聚合函数 - sum() / avg() / count() / max() / min() / stddev() / variance()
- 排名函数 - rank() / dense_rank() / row_number() / percent_rank()
- 取数函数 - lag() / lead() / first_value() / last_value() / nth_value()
11. 使用DCL授予和召回权限
~ 创建用户
create user 'guest'@'%' identified by 'Guest.618';
~ 授予权限
grant insert, delete, update, select on hrs.* to 'guest'@'%';
~ 召回权限
revoke insert, delete, update on hrs.* from 'guest'@'%';
~ 创建角色
create role data_analyst;
~ 设置角色权限
grant select, show view on hrs.* to data_analyst;
~ 通过角色授予权限
grant data_analyst to 'guest'@'%';
~ 修改密码
alter user 'guest'@'%' identified by '123456';
~ 密码过期
alter user 'guest'@'%' password expire;
~ 修改用户名和主机
rename user 'guest'@'%' to 'my_guest'@'192.168.100.%';
~ 删除用户
drop user if exists 'guest'@'%';
12. 使用TCL实现事务管理
~ 事务：把多个对数据的操作视为不可分割的原子性操作，要么全做，要么全不做
~ ACID特性：
- 原子性 - Atomicity
- 一致性 - Consistency
- 隔离性 - Isolation
- 持久性 - Duration
~ 并发数据访问的五类问题 - 锁机制 - 保护数据
- 脏读
- 不可重复读
- 幻读
- 第一类丢失更新
- 第二类丢失更新
~ 事务隔离级别 - 通过设置事务隔离级别让数据库选择适当的锁来保护数据
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
~ 查看事务隔离级别
show variables like 'transaction_isolation';
~ 修改事务隔离级别
set session transaction isolation level read committed;
set global transaction isolation level read committed;
~ 开启事务环境
start transaction;
~ 提交事务
commit;
~ 回滚事务
rollback;
13. Python程序接入MySQL数据库
pip install pymysql cryptography
~ 创建连接 - pymysql.connect(host, port, user, password, database, charset) - Connection
~ 获取游标 - conn.cursor() - Cursor
~ 执行SQL - cursor.execute() / cursor.executemany() - int
~ 两种情况：
- 写数据：提交或回滚 - conn.commit() / conn.rollback()
- 读数据：通过游标抓取 - cursor.fetchone() / cursor.fetchall() / cursor.fetchmany(size)
~ 关闭连接 - conn.close()
14. 视图 - view - 查询的快照 - 命名查询
~ 作用：
- 实现对数据的预处理 - 数据分析师
- 隐藏表结构
- 将访问权限控制到列
- 实现对查询的复用
~ 限制：
- 视图可以嵌套 - 基于视图创建视图
- 视图中的排序会被查询的排序覆盖
- 视图无法使用索引，不会激发触发器
~ 创建或替换视图
create or replace view view_name as select ...;
~ 删除视图
drop view if exists view_name;
15. 函数 - function - 封装功能上相对独立且会被重复使用的代码
~ 获取帮助
- help 'functions'; / ? functions;
- help 'window functions'; / ? window functions;
- help 'first_value'; / ? first_value;
~ MySQL内置函数
- 聚合函数
~ 描述性统计信息 - sum() / avg() / count() / max() / min() / var_pop() / var_samp() / stddev_pop() / stddev_samp()
~ 其他 - group_concat() / json_arrayagg() / json_objectagg()
- 数值函数
~ 绝对值和符号函数 - abs() / sign()
~ 取整和四舍五入 - ceiling() / floor() / round() / truncate()
~ 三角和反三角函数 - sin() / cos() / tan() / cot() / asin() / acos() / atan()
~ 指数和对数 - pow() / exp() / log() / log10() / log2()
~ 随机数 - rand()
~ 弧度和角度 - degrees() / radians()
~ 平方根 - sqrt()
- 字符串函数
~ 长度 - char_length() / length()
~ 拼接 - concat()
~ 大小写 - lower() / upper()
~ 取子串 - left() / right() / substring()
~ 替换 - replace()
~ 修剪 - trim() / ltrim() / rtrim()
~ 定位 - instr() / locate()
~ 填充 - lpad() / rpad()
~ BASE64编解码 - from_base64() / to_base64()
~ 反转 - reverse()
- 日期时间函数
~ 获取日期和时间 - current_timestamp() / current_date() / current_time()
~ 获取部分信息 - date() / time() / year() / quarter() / month() / day() / weekday() / hour() / minute() / second()
~ 计算时间差 - datediff() / timestampdiff()
~ 添加间隔 - date_add() / date_sub()
~ 格式化日期时间 - date_format()
- 流程控制函数
~ if() / ifnull() / coalesce() / nullif()
- 窗口函数
~ 排名 - rank() / dense_rank() / row_number() / percent_rank()
~ 取数 - lag() / lead() / first_value() / last_value() / nth_value()
- 其他函数
~ 类型转换 - cast() / convert()
~ 信息获取 - current_role() / current_user() / database() / last_insert_id() / version() / user()
~ 全局唯一标识符 - uuid() / uuid_to_bin() / bin_to_uuid()
~ UDF - 用户自定义函数
- 关键词 - create function
- 函数名(参数列表) 返回类型
- 修饰符
~ deterministic- 指示函数具有确定性 - 输入相同输出相同，有利于优化查询和缓存
~ no sql - 函数不会访问或修改数据库中的数据 - 提高执行效率和安全性
~ reads sql data - 函数可能会读取但不会修改数据库中的数据
- 函数执行体 - begin ... end
~ 创建函数
delimiter $$
create function func_name(param_name data_type, ...)
returns data_type deterministic
begin
declare ...;
... ... ...;
return ...;
end $$
delimiter ;
~ 删除函数
drop function if exists func_name;
~ 调用函数
select func_name(param1, param2);
16.（存储）过程 - stored procedure - 一组预定义的可以重复使用的SQL语句集合
~ 过程的作用
- 封装和代码复用
- 减少交互次数，提升性能
- 事务控制和安全性
- 减少网络开销
~ 创建过程
delimiter $$
create procedure procedure_name(param1 data_type, ...)
begin
declare ...;
... ... ...;
end $$
delimiter ;
~ 调用过程
call procedure_name(x, y, z);
~ 删除过程
drop procedure if exists procedure_name;
17. 索引 - 类似于书的目录 - 加速查询
~ 执行计划 - explain
- select_type
- table
- type - 查询类型
~ const - 常量级查询 - 性能最好
~ eq_ref - 唯一索引扫描 - 性能很好
~ ref - 非唯一索引扫描 - 性能比较好
~ range - 索引范围扫描 - 性能还行
~ index - 索引全扫描 - 性能很差
~ ALL - 全表扫描 - 性能最差
- possible_keys - 可能用到的索引
- key - 实际用到的索引
- key_len - 索引的长度
- rows - 预估扫描的行数 - 行数越少查询效率越高
- extra - 额外的信息
~ 索引相关概念 - InnoDB - B+树
- B+树是一个多叉树，层级很少（3-4层），所有它的I/O性能特别的好
- 聚集索引 - 索引组织表 - 数据就根据主键排序放在索引中 - 只有一个
- 非聚集索引 - 二级索引 - 额外创建一棵树来加速数据检索 - 回表
- 前缀索引/复合索引
- 索引覆盖查询和回表
~ index covered query - 要投影的字段已经被索引覆盖到，此时不需要回表，性能非常好
~ 回表 - 投影的字段没有被索引完全覆盖到，需要重新通过主键定位到数据，性能会受到影响
- 函数索引
- 不可见索引
- 索引设计要点
~ 注意索引的最左匹配原则，避免失效的情况
~ 在作为查询条件的字段上建索引（覆盖到where子句）
~ 避免额外排序（索引覆盖到order by子句的字段）
~ 避免回表（让索引覆盖到查询要投影的列 - 复合索引）
~ 索引不是越多越好 - 索引加速了查询但是让增删改变得更慢 - 索引更新问题
~ 创建索引
create index index_name on table_name (field_name1, field_name2, ...);
~ 删除索引
drop index index_name on table_name;
alter table table_name drop index index_name;
~ 设置索引可见性（MySQL8新特性）
alter table table_name alter index index_name invisible;
alter table table_name alter index index_name visible;
18. 其他相关知识
~ MySQL三种类型的变量
- 系统变量 - transaction_isolation / max_connections
- 用户变量
~ set @x = 10;
~ select @x := 10;
- 局部变量 - 通过declare进行声明
~ 数据备份
- 导出数据
~ 导出到文本文件 - select ... into outfile '...';
~ Python脚本代码 - CSV / Excel
~ 图形化客户端工具
- 导入数据
~ load data infile ...;
~ Python脚本代码 - CSV / Excel
~ 图形化客户端工具
- 导出SQL
~ mysqldump -u root -p school > school1.sql
~ mysqldump -u root -p --databases school > school2.sql
- 导入SQL
~ mysql -u root -p school < school1.sql
~ mysql -u root -p < school2.sql
- 停机冷备份 - 停机 - 复制数据文件
- 联机热备份
~ Percona XtraBackup
~ MySQL Enterprise Backup
~ 临时表
- create temporary table ...;
~ 虚拟列和计算列
- alter table tb_emp add column ann_sal int as (sal * 14 + comm * 12);
~ 触发器（trigger） - 千万不要用
- 可能存在行级锁升级为表锁的风险，严重影响性能（难道不担心锁表的风险吗）
- 可能存在诸多隐式操作，在不知情的情况下修改数据或者提交回滚事务，维护成本高
~ 表分区 - 加速查询 - RANGE分区 / LIST分区 / HASH分区
~ 主从复制 - 读写分离
~ 分表分库 - 数据切片 - 数据库中间件 - Vitess / ShardingSphere / Cobar / Mycat / Gaea
~ JOIN连接算法
- 嵌套循环连接 - nested loop join - OLTP业务 - （驱动表）数据量小，语句简单，存在索引
~ 驱动表 - 可以通过explain进行查看
- 左外连接时，左表是驱动表；
- 右外连接时，右表是驱动表；
- 内连接时，哪个表的数据更少，哪个表就是驱动表
~ OLTP - online transaction processing - 业务查询（开发） - 效率高、延迟低
- 哈希连接 - hash join - OLAP业务 - 数据量大，关联表多，语句复杂，连接条件无索引
~ OLAP - online analysis processing - 分析查询（数据分析师）- 允许高延迟
~ CTE - Common Table Expression - 公共表表达式
- 使用场景 - 需要在同一查询中多次引用相同的子查询的结果
- 具体语法
with cte_name (column1, column2, ...) as (
select ... from ...
)
select ...
from cte_name
where ...;
- 递归查询
with recursive Q as (
select eno, ename, mgr
from tb_emp where eno = 7800
union all
select P.eno, P.ename, P.mgr
from tb_emp as P inner join Q on P.mgr = Q.eno
)
select * from Q;
~ SQL注射攻击 - SQL Injection
~ 范式理论和反范式设计
- 范式级别（规范程度）越高，数据冗余越低，数据操作异常越少
- 1NF - 列不可分割（单值列）- JSON类型显然是违背了1NF的设计
- 2NF - 满足1NF + 非主键列必须完全依赖于主键列
- 3NF - 满足2NF + 消除传递依赖
- 4NF - 满足3NF + 消除非平凡多值依赖
- 反范式 - 违反范式理论 - 增加冗余数据或违反范式规则来达到以下目标
~ 提高查询性能
~ 简化数据操作
~ 空间换取时间
~ 建表必备的三个字段 - xxx_id / xxx_created / xxx_updated
~ 慢查询日志
- show variables like '%slow_query%';
- set global slow_query_log = on;
- set global long_query_time = 3;
~ insert相关
- 如果有多个连接向同一个表插入数据，使用insert delayed可以获得更好的性能
- 将查询结果插入到表中 - insert into table_name ... select ...;
~ SQL查询提示
- use index - 建议使用指定的索引
- ignore index - 建议忽略掉指定的索引
- force index - 强制使用指定的索引数据库和SQL完整笔记
本节15936字2025-04-27 10:35:57
