学习资料:

1. 练习: SQL Tryit
2. 教程:
   • 【哈工大】数据库系统 战德臣（全23讲）
   • 廖雪峰SQL教程
   • Lucidchart ERD教程

基本概念

数据库通识

数据库系统:

• 组成部分: 【以图书馆数据库系统为例】
  • 数据库(Database, DB): 相互有关联关系的表的集合
  • 数据库管理系统(Database Management System, DBMS): 管理数据库的一种系统软件，例如MySQL、Oracle。
  • 数据库应用(DataBase Application, DBAP): 例如图书采买管理程序、图书借阅管理程序。
  • 数据库管理员(DataBase Administrator, DBA)
  • 计算机基本系统

数据库管理系统:

• 功能:
  • 用户使用角度: 数据库语言
    • 分类:
      • 数据库定义: 定义数据库中Table的名称、标题(内含的属性名称及对该 属性的值的要求)等【通过数据定义语言(Data Definition Language, DDL)】
      • 数据库操纵: 向数据库的Table中增加/删除/更新数据及对数据进行查询、检索、统计等【通过广义的数据操纵语言(Data Manipulation Language, DML)】
      • 数据库控制: 控制数据库中数据的使用--哪些用户可以使用，哪些不可以【通过数据控制语言(Data Control Language, DCL)】
      • 数据库维护: 转储/恢复/重组/性能监测/分析...
    • 性质:
      • 一条数据库语言语句相当于高级语言的一个或多个循环程序
      • 数据库语言可以嵌入到高级语言(宿主语言)中使用
  • 系统实现角度: 系统程序的集合
    • 语言编译器: 将用数据库语言书写的内容，翻译成DBMS可执行的命令。例如: DDL编译器，DML编译器，DCL编译器等
    • 查询优化(执行引擎)与查询实现(基本命令的不同执行算法): 提高数据库检索速度的手段；例如贯穿于数据存取各个阶段的优化程序
    • 数据存取与索引: 提供数据在磁盘、磁带等上的高效存取手段。例如：存储管理器，缓冲区管理器，索引/文件和记录管理器等
    • 通信控制: 提供网络环境下数据库操作与数据传输的手段
    • 事务管理: 提供提高可靠性并避免并发操作错误的手段
    • 故障恢复: 使数据库自动恢复到故障发生前正确状态的手段，例如提供了备份、运行日志操控等实用程序
    • 安全性控制: 提供合法性检验，避免非授权非法用户访问数据库的手段
    • 完整性控制: 提供数据及数据操作正确性检查的手段
    • 数据字典管理: 管理用户己经定义的信息
    • 应用程序接口(API): 提供应用程序使用DBMS特定功能的手段
    • 数据库数据装载、重组等实用程序
    • 数据库性能分析: 统计在运行过程中数据库的各种性能数据，便于优化运行
• 意义:
  • 解决直接管理数据的问题
    • 读写文件并解析出数据需要大量重复代码
    • 从成千上万的数据中快速查询出指定数据需要复杂的逻辑，容易出错且难以复用
  • 解决应用程序自己管理数据的问题
    • 代码效率低，且容易出错
    • 接口各不相同，数据难以复用
• 按照数据模型分类:
  • 数据模型:
    1. 关系模型/表格模型: 使用表格(或称为关系)来组织数据，其中每个表包含行和列，行代表记录，列代表属性；表之间的关系通过外键来建立连接【e.g., MySQL、Oracle、SQL Server】
    2. 键值对模型(Key-Value Model): 数据以键值对的形式存储，每个键对应一个唯一的值【e.g., Redis】
    3. 文档模型(Document Model): 数据以类似于JSON或BSON格式的文档存储，每个文档可以包含不同的字段和值；文档可以嵌套，支持更为复杂的数据结构【e.g., MongoDB】
    4. 列族模型(Column-Family Model): 列族模型将数据组织为列族的集合，每个列族包含多个行，每行由一个键标识，并且包含多个列。这种模型适用于需要快速读取大量数据的场景，如分布式文件系统。【e.g., HBase】
    5. 图形模型(Graph Model): 数据以图的形式存储，由节点(node)和边(edge)组成，节点表示实体，边表示实体之间的关系。
  • 两者对比: SQL数据库仍然承担了各种应用程序的核心数据存储，而NoSQL数据库作为SQL数据库的补充，两者不再是二选一的问题，而是主从关系。
• 发展趋势:
  • 数据库即服务(DBaaS): 随着云计算的快速发展，云上数据库服务变得越来越受欢迎。以Supabase为例，它为开发者极大地简化了应用程序的后端开发过程。具体功能如下:
    1. 数据托管: 提供了PostgreSQL数据库的托管服务，开发者可以使用SQL查询和操作数据。这允许开发者在构建应用程序时使用强大的数据库功能。
    2. 身份验证: 具有内置的用户身份验证系统，支持电子邮件/密码、社交登录等多种身份验证方式。这使开发者能够轻松添加用户身份验证和授权功能。
    3. 实时通信: 具有实时数据同步功能，通过WebSockets支持实时通信，使应用程序能够在数据更改时立即更新。
    4. 数据交互: 自动生成REST和GraphQL API，使开发者能够方便地与后端数据进行交互。
    5. 定制扩展: 作为一个开源项目，允许开发者自行托管，并根据自己的需求进行自定义和扩展。

数据库设计:

1. 需求分析，分析用户的业务流程，从而得出用户的需求，并用数据流程图、数据字典将用户需求描述出来
2. 在需求分析的基础上，进行数据库概念设计，这种设计与具体的数据库管理系统无关，其任务是抽象出各用户所要求的数据视图(对应于外模式概念)，最后综合为全局的数据视图(对应于模式概念)，用概念数据模型来抽象，可用ER模型或对象模型
3. 将用ER模型或对象模型表示的数据视图，转换为关系模式，并对所得关系模式进行优化处理，这就是所谓的数据库逻辑设计(这一步仍然对应于外模式和模式)
4. 在逻辑设计的基础上，将所得的数据模式组织存储到物理介质上，这就是数据库的物理设计(这一步对应于内模式)
5. 数据库的安全设计，即允许什么样的用户访问数据库，以及合法用户访问数据库中数据的权限等问题

数据库模式: 使不同级别的用户对数据库形成不同的视图

• 面向建立和维护数据库人员的概念级(模式):
  • 由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构
  • 是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)
  • 由DDL来描述
• 面向用户或应用程序员的用户级(外模式)
  • 是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示
  • 是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据
  • 通过DML使用
• 面向系统程序员的物理级(内模式/存储模式)
  • 是数据库中全体数据的内部表示或底层描述
  • 描述了数据在存储介质上的存储方式及物理结构，对应着实际存储在外存储介质上的数据库

MySQL基础知识

版本划分

1. Community Edition: 社区开源版本，免费
2. Standard Edition: 标准版
3. Enterprise Edition: 企业版
4. Cluster Carrier Grade Edition: 集群版

数据类型

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	又叫FLOAT(24), 4字节浮点数，范围约+/-\(10^{38}\)
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串【缺少的部分会填充空格】
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储服务器时区对应的时间
TIMESTAMP	时间戳类型	存储UTC时间

• 字符编码:
  • Mysql自带的utf8只支持UTF-8中3字节的字符(utf8mb3)，而utf8mb4才支持完整的UTF-8
  • 在MySQL 8.0版本之前，默认字符集为latin1；从MySQL8.0开始，数据库的默认编码将改为utf8mb4
• DATETIME对比TIMESTAMP
  • 参考文档: MySQL 8.4 Reference Manual
  • 相同:
    • 默认值都可以设为CURRENT_TIMESTAMP
    • 5.6.4版本以上支持毫秒
  • 区别:
    • DATETIME:
      • 存储服务器时区对应的时间: 存取一致
      • 存储范围大，无2038问题
    • TIMESTAMP:
      • 存储UTC时间戳: 方便多时区转换
      • 支持自动更新: ON UPDATE CURRENT_TIMESTAMP

存储引擎

• 作用: 用于存储、处理和检索数据的底层软件组件
• 选择:
  • InnoDB: (默认)
    • 特点: 支持ACID事务和外键约束，提供了高度可靠的数据保护和恢复机制，聚簇索引(需要回表)
    • 场景: 需要事务支持、外键和高并发读写操作的应用
    • 优点: 事务安全、行级锁定、崩溃恢复
    • 缺点: 较高的磁盘使用，复杂的内部结构
  • MyISAM:
    • 特点: 不支持事务和外键，但具有较高的读操作性能和较低的存储开销，非聚簇索引(分表麻烦)
    • 场景: 以读操作为主，且不需要事务支持的应用，如数据仓库、日志系统等
    • 优点: 表级锁定，存储空间利用率高，查询性能好
    • 缺点: 不支持事务、外键，不具备崩溃恢复机制
  • MEMORY:
    • 特点: 将数据存储在内存中，读写速度极快，但数据在服务器关闭后会丢失
    • 场景: 需要快速访问的数据，如临时表、缓存数据等
    • 优点: 非常高的读写速度
    • 缺点: 数据持久性差
  • CSV:
    • 特点: 将数据存储为CSV格式的文本文件，便于与其他应用程序交换数据
    • 场景: 数据导入导出、跨平台数据交换
    • 优点: 易于数据交换，格式简单
    • 缺点: 不支持索引，性能较差
  • ARCHIVE:
    • 特点: 用于高效存储大量历史数据，支持高压缩比和只读操作
    • 场景: 日志、审计数据等需要长期存储但不需要频繁访问的数据
    • 优点: 高压缩率，节省存储空间
    • 缺点: 不支持索引，查询性能差，只支持插入和查询操作
  • FEDERATED:
    • 特点: 允许将数据存储在远程MySQL数据库中，类似于分布式数据库
    • 场景: 需要将不同数据库服务器上的数据集成在一起进行查询的场景
    • 优点: 数据分布灵活
    • 缺点: 性能受网络影响，事务和索引支持有限
  • NDB(Cluster):
    • 特点: 用于MySQL Cluster集群环境，提供高可用性和可扩展性，数据在多个节点之间进行分片和复制
    • 场景: 需要高可用性和横向扩展能力的应用，如电信系统、实时Web应用
    • 优点: 高可用性、自动故障转移
    • 缺点: 复杂性高，配置和维护成本较高

安装方式(Linux环境)

• 客户端:

  apt-get install -y default-mysql-client

  • 从原来的mysql-client改为了default-mysql-client
• 服务端:

  db1:
      image: mysql:8.4.0
      container_name: morningstar_db1
      restart: always
      command: ["mysqld", "--mysql-native-password=ON"]
      environment:
          - MYSQL_HOST=localhost
          - MYSQL_PORT=3306
          - MYSQL_DATABASE=djangodb
          - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      volumes:
          - morningstar_db1_data:/var/lib/mysql
      ports:
          - 3307:3306

  • 注意安装后版本不可以降低，会引发不兼容
  • 高版本默认不加载mysqL_native_password，且8.4之后的版本default-authentication-plugin参数被移除，需要使用新的mysql-native-password参数

数据库设计

关系模型

• 主键:
  • 原则: 不选取任何与业务相关的字段
  • 主键类型:
    1. 自增整数类型
    2. 全局唯一GUID类型: e.g. 8f55d96b-8acc-4636-8cb8-76bf8abc2f57
  • 联合主键
• 外键:
  • 一对一(OneToOneField):
    • 在实际的开发中应用不多，因为一对一可以创建成一张表，更多是为了提高读写效率，划分常用和非常用字段
    • 若一定要分表有两种建表原则:
      • 外键唯一: 任选一方为从表建立唯一约束的外键
      • 主外键一体: 主表的主键和从表的主键，形成主外键关系
  • 一对多(ForeignKey):
    • "一方"叫主表或一表，"多方"叫从表或多表。
    • 设计表时通常会在从表(多方)添加一个字段，用于存放主表主键的值，这个字段叫外键字段
  • 多对多(ManyToManyField):
    • 设计表时通常会建立一个中间表，里面的字段都是外键

ER图

实体-关系图(Entity-Relationship Diagram, ER)是一种用于设计数据库的图形表示方法，通常使用专门设计的工具来创建。一般推荐使用Microsoft Visio, Lucidchart或者Draw.io来绘制ER图。以下是一个ER图的典型案例:

请你设计一个图书管理系统的ER图，满足以下要求:

1. 图书有唯一的书号作为标识，可以查询书名、数目、类别。
2. 读者可以一次借多本书，每次书籍借阅都有借书和还书时间，
3. 读者有读者号作为唯一的标识，读者还有姓名、年龄的属性。
4. 每本有唯一的出版社，一家出版社可以出版多种书籍，出版社名各不同。在系统中保存了出版社的电话、邮编和地址信息。

关系规范化理论

• 作用: 减少数据冗余和提高数据完整性
• 内容:
  • 第一范式(1NF): 要求数据库表的每一列都是不可分割的基本数据项，即表中的所有字段都应该只包含原子性的值，没有重复的组或数组
  • 第二范式(2NF): 在第一范式的基础上进一步要求，表中的每一行都应该是唯一的，即表必须有一个主键，且非主键字段必须完全依赖于主键
  • 第三范式(3NF): 要求非主键字段不能相互依赖，即每个非主键字段必须直接依赖于主键，而不是通过另一个非主键字段间接依赖
  • BCNF(Boyce-Codd Normal Form): 是第三范式的加强版，要求表中的每一行都由唯一的候选键确定，即任何非主属性都不能由其他非主属性确定
  • 第四范式(4NF): 要求表中不存在多值依赖，即一个表中不应该有两个或多个独立的多值事实关于同一个主键
  • 第五范式(5NF): 进一步解决表中可能存在的联合多值依赖问题。
• 利弊: 过度规范化可能会导致查询性能下降(需要多表查询)，因此，在实际应用中，数据库设计往往需要在规范化和性能之间找到平衡点

基本语法

注意事项:

• 单表SQL语句执行顺序: FROM-> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
• where中不可以使用select中新定义的列名，但group by和having中可以使用
• 当标识数据库、表、列名等对象的名称与sql关键字冲突或包含空格时，可以使用反引号(`)避免语法错误
• 大小写问题: 数据库存储的数据是区分大小写的，只是一般比较查找时不启用，可以选择排序规则utf8_general_ci或者utf8_bin区分大小写

  SELECT * FROM users WHERE username COLLATE utf8_bin = 'SomeUsername';

  CREATE TABLE users (
      username VARCHAR(255) COLLATE utf8_bin NOT NULL,
      password VARCHAR(255) COLLATE utf8_bin NOT NULL
  );

DDL

DDL(Data Definition Language): 允许用户定义数据，也就是创建表/库、删除表/库、查看/使用库、修改表结构等操作。通常，DDL由数据库管理员执行。

• database操作

  SHOW DATABASES;
  SELECT DATABASE(); -- 查看当前使用的数据库
  USE testdb;
  CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8;
  DROP DATABASE IF EXISTS testdb;

• table增删改查
  1. 创建表格

     CREATE TABLE IF NOT EXISTS students (
         id INT,
         name VARCHAR(255) COMMENT '支持中文',
         city VARCHAR(255),
         PRIMARY KEY(id)
     );

     -- 按照查询的结果生成表
     CREATE TABLE db4.students AS SELECT * FROM db1.students;

  2. 删除表格

     DROP TABLE students;

  3. 修改表格

     ALTER TABLE students1 RENAME TO students2;
     ALTER TABLE students1 ADD birth VARCHAR(10) NOT NULL;
     ALTER TABLE students1 MODIFY birth VARCHAR(20) NOT NULL;
     ALTER TABLE students1 CHANGE birth birthday VARCHAR(20) NOT NULL;
     ALTER TABLE students1 DROP COLUMN birthday;

  4. 查看表格

     SHOW TABLES;
     DESC table1; -- 显示表结构

  5. 清空表格【将原本的表摧毁，重新创建一个表结构和原来一样的表(会影响AUTO_INCREMENT)】

     TRUNCATE TABLE students;

DML

DML(Data Manipulation Language): 为用户提供添加、删除、修改数据的能力，这些是应用程序对数据库的日常操作。 1. 插入记录

INSERT INTO students (id, name, city) VALUE (1, 'John Doe', 'Nanjing'); -- 单行插入
INSERT INTO students (id, name, city) VALUES (1, 'John Doe', 'Nanjing'), (2, 'Jane Smith', 'Shanghai'); --多行插入

-- 需要严格按照column顺序【开发中不允许使用】
INSERT INTO students VALUES (3, 'David', 'New York City');

2. 更新记录

UPDATE students SET name = 'Daniel', city='Xiangshui' WHERE id > 1;

3. 删除记录

DELETE FROM students WHERE city = 'Nanjing';
DELETE FROM students WHERE id IN (1,2);

4. 插入一条新记录，或者如果在唯一约束冲突时替换已经存在的记录

REPLACE INTO students(id, city, name) VALUES (1, 'New York City', 'Henry');

DQL

DQL(Data Query Language): 允许用户查询数据，这也是通常最频繁的数据库日常操作。

• 简单查询

  -- 基本查询
  SELECT * FROM students;
  -- 条件查询
  SELECT * FROM students WHERE age > 20;
  SELECT * FROM students WHERE gender = 'M' AND age > 20;
  SELECT * FROM students WHERE gender = 'F' OR age < 20;
  SELECT * FROM students WHERE NOT gender = 'M';
  SELECT * FROM students WHERE gender <> 'M';
  SELECT * FROM students WHERE (age < 20 OR age > 22) AND gender = 'M';
  SELECT * FROM students WHERE age between 10 and 20; -- 包含首尾
  SELECT name FROM students WHERE students.id NOT IN (1,2,3);
  SELECT DISTINCT name FROM students WHERE name LIKE 'J%';
  -- 分页查询
  SELECT * FROM students LIMIT 10 OFFSET 5; -- 返回从6开始的10个数据
  SELECT * FROM students LIMIT 5, 10; -- 返回从6开始的10个数据
  SELECT * FROM students LIMIT 5; -- 返回从1开始的5个数据

• 聚合查询/分组查询
  sql SELECT COUNT(*) FROM students; SELECT COUNT(*) AS students_num FROM students; SELECT MAX(age) FROM students; SELECT MIN(age) FROM students; SELECT AVG(age + age) FROM students; -- null加上任何数结果都为null，因此AVG(age + age)并不一定等于AVG(age) + AVG(age)；推荐用ifnull解决 SELECT name FROM students GROUP BY name HAVING COUNT(*) > 1; SELECT city, gender, COUNT(*) FROM students GROUP BY city, gender;

DCL

DCL(Data Control Language): 用来定义数据库的访问权限和安全级别，及创建删除用户。

• 设置数据库所有表权限

  GRANT ALL PRIVILEGES ON database_name.* TO user_name;
  GRANT SELECT, INSERT, UPDATE ON database_name.* TO user_name;
  REVOKE ALL PRIVILEGES ON database_name.* FROM user_name;

• 设置表权限

  GRANT SELECT ON table_name TO user_name;
  GRANT INSERT, UPDATE, DELETE ON table_name TO user_name;
  REVOKE ALL ON table_name FROM user_name;
  REVOKE SELECT ON table_name FROM user_name;

• 设置视图权限

  GRANT SELECT ON view_name TO user_name;
  REVOKE SELECT ON view_name FROM user_name;

• 增删用户

  CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
  DROP USER 'newuser'@'localhost';

约束

• 说明
  • SQL约束用于规定表中的数据规则
  • 如果存在违反约束的数据行为，行为会被约束终止
  • 约束可以在创建表时规定(通过CREATE TABLE语句)，或者在创建表之后规定(通过ALTER TABLE语句)
• 类型
  • PRIMARY KEY: 确保某列(或多列)有唯一标识，用于找到特定记录
    • 通常结合AUTO_INCREMENT使用
      • 插入失败会影响AUTO_INCREMENT
      • DELETE会影响AUTO_INCREMENT
      • TRUNCATE不会影响AUTO_INCREMENT
    • 作用:
      • 作为外键的引用: 主键通常被其他表中的外键引用。
      • 加速数据检索和连接操作: 主键通常会被数据库引擎用作索引的依据，通过主键可以让数据库引擎可以更加高效地检索数据或者进行表之间的连接操作，因为它可以利用主键索引来快速定位需要的数据
    • 与NOT NULL和UNIQUE的区别:
      • 主键约束只能存在一个，但是非空+唯一约束可以存在多个
      • 主键可以添加自增约束，但是非空+唯一约束不能
      • 主键约束底层维护了一个主键索引，而唯一约束维护的是唯一索引
  • NOT NULL: 字段不能存储空值
  • UNIQUE: 保证字段的每行都是唯一的值
  • DEFAULT: 规定没赋值时的默认值【赋null也算赋值】
  • FOREIGN KEY: 用来确保外键字段下的数据都来自主表中的主键字段下【实际开发中不常用，因为会降低效率】
  • CHECK: 保证字段的值符合指定条件
• 实践: 表与表之间的关系尽量通过业务逻辑维护，而不是通过使用数据库外键约束，原因如下:
  • 性能问题: 外键约束会使约束的表之间做级联检查，导致数据库性能降低
  • 并发问题: 外键约束的表在事务中需要获取级联表的锁，才能进行写操作，这更容易造成死锁问题
  • 扩展性问题: 数据分库分表时，加大了拆分的难度
• 用法

  CREATE TABLE employees (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      apartment_id INT,
      age INT CHECK(age > 18),
      [CONSTRAINT fk_employees_apartment_id] FOREIGN KEY (apartment_id) REFERENCES apartment(id) ON UPDATE CASCADE ON DELETE CASCADE; -- 级联删除/更新
  );

  ALTER TABLE students ADD PRIMARY KEY(id);
  ALTER TABLE students DROP PRIMARY KEY;
  ALTER TABLE students AUTO_INCREMENT = 100; --设置下一个自增的值设置为100

  ALTER TABLE students ADD UNIQUE(id);
  ALTER TABLE students DROP UNIQUE(id);

  ALTER TABLE students MODIFY age INT NOT NULL;
  ALTER TABLE students MODIFY sex CHAR(1) DEFAULT '男';

  ALTER TABLE students ADD CONSTRAINT fk_students_class_id FOREIGN KEY (class_id) REFERENCES classes(id);
  ALTER TABLE students DROP FOREIGN KEY fk_students_class_id;

多表查询

表的连接:

• 作用: 用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
• 分类:
  • INNER JOIN: 如果表中有至少一个匹配，则返回行 (INNER JOIN或者JOIN)
    • MySQL上以右表为主匹配
  • LEFT (OUTER) JOIN: 即使右表中没有匹配，也从左表返回所有的行
  • RIGHT (OUTER) JOIN: 即使左表中没有匹配，也从右表返回所有的行
  • (FULL) OUTER JOIN: 只要其中一个表中存在匹配，则返回行
    • Mysql不支持这一用法，需要通过UNION ALL模拟
  • NATURAL JOIN: 不需要ON，直接根据列名进行内连接
    • 可以使用USING指定单列
    • 因为不够直观，不推荐使用
  • CROSS JOIN: 返回两个表格中所有行的组合，即使这些行在逻辑上没有任何关联【笛卡尔积】
    • 与SELECT * FROM table1, table2;效果相同

具体语法:

• 连接查询: 【连接放要在所有的语句之前执行】
  • 隐式内连接

    SELECT S.student_id AS studentId, C.course_id AS courseId FROM students AS S, courses AS C where S.course_id = C.course_id;

  • 显式内连接(速度相对隐式较快)

    SELECT S.StudentID, C.CourseName FROM Students AS S INNER JOIN courses AS C ON S.course_id = C.course_id;    

  • 左外连接

    SELECT S.student_id, S.course_id, C.course_name FROM students AS S LEFT JOIN courses AS C ON S.course_id = C.course_id;

• 嵌套查询
  • 子查询: 单行单列

    SELECT * FROM students WHERE age =(SELECT MAX(age) FROM students);

  • 子查询: 多行单列【结合IN, ALL, ANY】

    -- LeetCode196
    SELECT * FROM Person WHERE id NOT IN (SELECT MIN(id) AS id FROM Person GROUP BY Email);

    SELECT * FROM students WHERE sex = '男' AND age > ALL(SELECT age FROM students WHERE sex = '女');

  • 子查询: 多行多列【需要把子查询当作虚拟表，并给出别名】

    -- LeetCode184
    SELECT t.Department, e.name AS 'Employee', e.salary AS 'Salary' FROM Employee e JOIN
    (
        SELECT Department.name AS 'Department', departmentId, MAX(salary) AS 'maxSalary' 
        FROM Employee
        LEFT JOIN Department ON Employee.departmentId = Department.id
        GROUP BY departmentId 
    
    )t 
    ON e.departmentId = t.departmentId
    WHERE e.salary = t.maxSalary;

触发器

• 定义: MySQL触发器是一种特殊的存储过程，它会自动在执行INSERT、UPDATE或DELETE操作之前或之后触发执行
• 作用: 触发器可以用来自动执行数据完整性规则、自动更新数据、自动填充字段等
• 使用:

  DELIMITER //
  CREATE TRIGGER trigger_name
  BEFORE/AFTER INSERT/UPDATE/DELETE
  ON table_name
  FOR EACH ROW
  BEGIN
    -- 触发器逻辑
    -- 可以使用 NEW 和 OLD 关键字来引用新旧数据
  END//
  DELIMITER;

• 举例:
  • 在用户表中插入新记录时自动设置一个默认的权限级别

    DELIMITER //
    CREATE TRIGGER before_insert_user
    BEFORE INSERT
    ON users
    FOR EACH ROW
    BEGIN
      SET NEW.permission_level = 1; -- 假设权限级别默认为1
    END//
    DELIMITER ;

• 注意: 触发器逻辑应该小心编写，因为它们可能会影响数据库的性能，并且在调试时可能会比较复杂

事务操作

• 定义: 把多条语句作为一个整体进行操作的功能【相当于操作系统中的原语】
• 理由: 在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分
• 分类:
  • 显式事务: 通过明确指定开始和结束事务的范围，来确保一系列数据库操作要么全部成功执行，要么全部失败回滚，以保证数据的一致性和完整性
  • 隐式事务: 对于单条SQL语句，数据库系统自动将其作为一个事务执行【MySQL默认】
• 原理:
• 特性: 以转账操作为例
  • A(Atomic, 原子性): 将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行
  • C(Consistent, 一致性): 事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100
  • I(Isolation, 隔离性): 如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离【通过多版本并发控制(Multi-Version Concurrent Contrl, MVCC)，查看行记录的版本id】
  • D(Duration, 持久性): 即事务完成后，对数据库数据的修改被持久化存储

基本用法

• 提交:

  START TRANSACTION; -- 或者 BEGIN
  UPDATE students SET age = age - 1 WHERE id = 2;
  UPDATE students SET age = age + 1 WHERE id = 3;
  COMMIT;

• 回滚: 有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  ROLLBACK;

• 默认提交方式:

  -- 查看
  SELECT @@autocommit;
  -- 修改
  SET @@autocommit = 0;

并发访问

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。 因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。

• 脏读: 一个事务读取到了另一个事务中尚未提交的数据
• 不可重复读: 一个事务中多次读取时数据是不一致的，这是事务update时引发的问题
• 幻读(虚读): 一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。这是INSERT或DELETE时引发的问题

其实上述三个问题中，最严重的就是脏读(读取了错误数据)，一定要避免；而不可重复读和虚读其实并不是逻辑上的错误，而是数据的时效性问题，所以这种问题并不属于很严重的错误， 如果对于数据的时效性要求不是很高的情况下，我们是可以接受不可重复读和虚读的情况发生的。

数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

隔离级别	脏读	不可重复读	幻读	数据库默认
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes	Oracle & SQL Server
Repeatable Read	-	-	Yes	MySQL
Serialzable	-	-	-

• Read Uncommitted: 隔离级别最低的一种事务级别。在这一个级别会遇到脏读(Dirty Read)的问题。
  • 脏读: 一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据。【A事务更新到一半后悔了，但B事务使用了A的更新】
• Read Committed: 在这一隔离级别下，一个事务可能会遇到不可重复读(Non Repeatable Read)的问题。
  • 不可重复读: 在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。【B在A更新前读了次数据，在A更新后又读了次数据，发现两次不一致】
• Repeatable Read: 在这一隔离级别下，一个事务可能会遇到幻读(Phantom Read)的问题。
  • 幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。【B第一次读不到某个数据，但由于在这之后A事务提交了数据，所以B第二次更新却成功了】
• Serializable: 最严格的隔离级别。在这一隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
  • 缺点: 由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

隔离级别查看与设置:

-- 查看
show variables like '%iso%';
select @@transaction_isolation;
-- 设置【新的连接才会改变】
set global transaction isolation level read uncommitted；

SQL函数

内置函数

• 聚合函数: COUNT(), SUM(), AVG(), MAX(), MIN(), GROUP_CONCAT(expression1 ORDER BY expression2 SEPARATOR sep)
• 判断函数:
  • CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '保密' END
  • CASE WHEN sex=1 THEN '男' WHEN sex=2 TEHN '女' ELSE '保密' END
• 字符函数: 【sql中字符串索引从1开始】
  • LENGTH()(字节数), CHAR_LENGTH()(字符数)
  • ASCII(string)【只获得第一个字符的ASCII值】
  • 截取字符串
    • SUBSTRING(string1 FROM start [FOR length])/SUBSTRING(string1, start, length)【等价于方言SUBSTR】
    • LEFT('Hello, World!', 5)
    • RIGHT('Hello, World!', 5)
  • 操作字符串:
    • UPPER(), LOWER()
    • TRIM(string)
    • REVERSE(string)
    • CONCAT(string1, ' ', string2)
  • 对比字符串:
    • REGEXP(正则表达式)
    • column LIKE '%str%'【mybatis中推荐使用column LIKE CONCAT('%', 'str', '%')】
  • 查找子字符串:
    • POSITION(substring IN string)
    • LOCATE(substring, string), LOCATE(substring, string, start)【方言】
    • SUBSTRING_INDEX(string, delimiter, count)【方言】:

      SELECT SUBSTRING_INDEX('www.example.com', '.', -1); -- 返回 'com'
      SELECT SUBSTRING_INDEX('path/to/your/file.txt', '/', 1); -- 返回 'path'

• 日期函数:
  • NOW([毫秒精度])/CURRENT_TIMESTAMP([毫秒精度]), CURDATE(), CURTIME()
  • UNIX_TIMESTAMP([时间字符串])(获取时间戳), FROM_UNIXTIME([时间戳])(获取时间)【都只支持秒级时间戳】
  • YEAR(d), MONTH(d), DAY(d), WEEK(d), DATE(d), TIME(d)
  • DATE_FORMAT(NOW(6), '%Y-%m-%d %H:%i:%s:%f')(格式: 2024-07-27 23:04:34:201406)
    • %Y: 2024; %y: 24
    • %M: July; %m: 07
    • %D: 27th; %d: 27
    • %i: 04
    • %f: 201406
  • DATEDIFF('2022-01-10', '2022-01-01'), DATE_ADD(event_date, INTERVAL 1 DAY), DATE_SUB(event_date, INTERVAL 1 DAY)【存在ADDDATE()和SUBDATE方言】
  • program_date BETWEEN '2020-06-01' and '2020-06-30'
• 数学函数:
  • RAND()
  • ABS()
  • SIGN(decimal): 返回-1、0或1
  • ROUND(decimal, int), TRUNCATE(decimal, int)
  • LEAST(expr1, expr2, expr3...), GREATEST(expr1, expr2, expr3...)
• 窗口函数:
  • 分类:
    • 排名函数: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK()
    • 聚合函数: SUM(), AVG(), MIN(), MAX(), STD()
    • 分析函数: FIRST_VALUE(), LAST_VALUE(), LAG()(前), LEAD()(后), NTILE()
  • 用途: 在不减少行数的情况下，在每行上进行分析计算。
  • 用法:

    <window_function> OVER (PARTITION BY <partition_expression> ORDER BY <order_expression>)

  • 举例:
    • ROW_NUMBER(): ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column)

      SELECT employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_department FROM employees;

      • 辨析ROW_NUMBER(), RANK(), DENSE_RANK(): 都为每行按照顺序排名，但ROW_NUMEBR的名次是连续无重复的,RANK()是有重复则跳过的，而DENSE_RANK是不跳过的。
    • LAG(): LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

      SELECT year, revenue, LAG(revenue, 1, 0) OVER (ORDER BY year) AS prev_year_revenue, (revenue - LAG(revenue, 1, 0) OVER (ORDER BY year)) / LAG(revenue, 1, 1) OVER (ORDER BY year) AS growth_rate FROM sales;

• 其他函数:
  • IFNULL(column_name, 0)

自定义函数

• 创建:

  DELIMITER //
  
  CREATE FUNCTION addTwoNumbers(a INT, b INT)
  RETURNS INT
  BEGIN
      DECLARE result INT;
      SELECT a + b INTO result;
      -- SET result = a + b;
      RETURN result;
  END //
  
  DELIMITER ;

  • DELIMITER: 用于修改分隔符，方便函数编写
• 调用:

  SELECT addTwoNumbers(10, 5);

• 删除:

  DROP FUNCTION IF EXISTS addTwoNumbers;

辅助功能

公共表表达式

• 定义: (Common Table Expression, CTE)
• 作用: 在查询中创建临时命名的结果集，这些结果集只在查询中可见
• 基本使用:

  WITH temp_table_name AS (
      SELECT column1, column2
      FROM table1
      WHERE column1 > 10
  )
  SELECT * from temp_table_name;

• 递归使用:
  • 语法模版:

    WITH RECURSIVE cte_name (column1, column2, ...)
    AS
    (
        -- 初始查询部分，定义了递归查询的起点
        SELECT ...
        FROM ...
        WHERE ...
        UNION [ALL]
        -- 递归查询部分，定义了递归查询的逻辑
        SELECT ...
        FROM cte_name
        JOIN ...
        WHERE ...
    )
    -- 最终查询部分，定义了如何使用递归CTE
    SELECT * FROM cte_name
    WHERE ...

  • 典型案例: 生成数字序列

    WITH RECURSIVE NumberSequence (num) AS (
        SELECT 1
        UNION ALL
        SELECT num + 1 FROM NumberSequence
        WHERE num < 10
    )

视图

• 作用: 类似CTE，但是永久存储，且结果会自动更新
• 使用:

  -- 定义
  CREATE VIEW view_name AS 
  SELECT column1, column2
  FROM table1
  WHERE column1 > 10;

  -- 使用
  SELECT * FROM view_name;

  -- 删除
  DROP VIEW [IF EXISTS] view_name;

SQL变量

• 使用:

  SET @my_variable := 10;
  SELECT @my_variable;
  SELECT @my_variable := 10 AS my_variable;

• 场景: 更多的是在存储过程和函数中(不需要@修饰)

存储过程

• 定义: 在数据库中预先编写好的一系列SQL语句集合，可以被保存在数据库中，并在需要时被调用执行
• 场景: 数据验证、复杂计算、生成报告
• 编写:

  CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
  BEGIN
      -- SQL语句和控制流语句
      ...
  END

  DELIMITER $$
  
  CREATE PROCEDURE GetUserById(IN user_id INT)
  BEGIN
      SELECT name, email FROM users WHERE id = user_id;
  END$$
  
  DELIMITER ;

• 对比函数:
  • 使用场景:
    • 存储过程: 适用于执行一系列复杂数据库操作
    • 函数: 通常用于执行计算并返回一个值，可以作为查询的一部分
  • 调用方式:
    • 存储过程: CALL MyProcedure(参数)
    • 函数: SELECT MyFunction(列名) FROM 表
  • 返回值:
    • 存储过程:
      • 通常不返回单一的值，而是可以通过输出参数或结果集返回多行数据
      • 存储过程可以没有返回值，或者返回多个结果集
    • 函数:
      • 必须返回一个标量值，例如整数、字符串、日期等
      • 函数不能返回结果集
  • 参数:
    • 存储过程: 可以有输入参数、输出参数或两者都有
    • 函数: 只能有输入参数，没有输出参数
  • 副作用:
    • 存储过程: 可以有副作用，比如修改数据库中的数据
    • 函数: 应该避免产生副作用，保持结果的确定性
• 性能:
• 存储过程: 由于存储过程可以包含预编译的代码，因此在执行时可能具有更好的性能
• 函数: 在每次调用时可能需要重新解析和编译，但某些数据库优化了函数的执行

性能优化

优化方法分类:

• 软优化: 在操作和设计数据库方面上进行优化
  • SQL语句及索引
  • 表结构
  • 系统配置
• 硬优化: 就是软优化之后性能还很低，只能在硬件上进行优化

软优化方法

判断哪种操作比较多:【查询密集型还是修改密集型】

SHOW GLOBAL STATUS LIKE 'Innodb_rows%';

• 查询密集型
  • 尽可能使用索引或覆盖索引
  • 优化查询语句: 确保查询语句写得简洁高效，避免不必要的联接和子查询
  • 小表驱动大表
  • 分区表: 如果数据量很大，可以考虑将表分成多个分区。分区可以提高查询性能，尤其是在一些特定的查询条件下，可以只扫描特定的分区而不是整个表。
  • 使用适当的数据类型: 选择合适的数据类型可以减少存储空间的占用，并且在查询时可以提高效率【例如，使用整数类型而不是字符串类型来存储数字数据】
  • 定期优化表: 定期进行表的优化操作，例如重新组织表的数据、重新构建索引等，可以提高查询性能
  • 缓存查询结果
• 修改密集型
  • 批量操作: 尽量使用批量操作而不是逐条操作。数据库系统通常会对批量操作进行优化，从而减少数据库连接和事务管理的开销。
  • 合理使用事务: 对于需要保证一致性和完整性的操作，使用事务是必要的。但是，事务的范围应该尽可能小，避免长时间锁定数据库资源。
  • 减少索引和触发器: 在执行大量的增删改操作时，索引和触发器可能会成为性能瓶颈。可以考虑在大批量操作之前暂时禁用索引和触发器，然后在操作完成后重新启用它们。
  • 合理设计数据表: 合理的表设计可以减少增删改操作的开销。例如，避免在频繁进行更新的字段上添加索引，尽量将数据表规范化，避免数据冗余等。
  • 分区表: 对于特别大的数据表，可以考虑将表进行分区。这样可以减少增删改操作涉及的数据量，提高操作效率。
  • 定期优化表: 定期进行表的优化操作，例如重新组织表的数据、重新构建索引等，可以提高增删改操作的效率。
  • 使用延迟索引更新: 某些数据库系统支持延迟索引更新，即在数据变更后不立即更新索引，而是在系统空闲时进行。这可以减少增删改操作的开销，但可能会导致查询性能略微下降。

索引的使用

索引(Index): 用B+ Tree提高数据检索效率，可以加速对表中数据的查询操作，通常基于表中的一个或多个列。

分类:

• 主键(约束)索引: 主键约束（唯一+非空）+ 提高查询效率
• 唯一(约束)索引: 唯一约束 + 提高查询效率
• 普通索引: 仅提高查询效率
• 组合(联合)索引: 多个字段组成索引[联合主键索引、联合唯一索引、联合普通索引]
• 全文索引 TEXT BIGTEXY: Mysql全文索引使用较少；基本针对文档类数据会选择sok、es等文档搜索类数据库；
• hash索引: 根据key-value等值查询效率非常高，但是不适合范围查询

基本用法:

• 直接创建
  • 创建唯一索引:

    CREATE UNIQUE INDEX idx_students_name ON students(name);

  • 创建普通索引:

    CREATE INDEX 索引名 ON 表名(字段);

  • 创建普通组合索引:

    CREATE INDEX 索引名 ON 表名(字段1, 字段2, ...);

  • 创建唯一组合索引:

    CREATE UNIQUE INDEX 索引名 ON 表名(字段1, 字段2...)`

• 修改时添加
  • 添加一个主键【默认索引名为"primary"】:

    ALTER TABLE 表名 ADD PRIMARY KEY(字段);

  • 添加唯一索引【默认索引名为字段名】:

    ALTER TABLE 表名 ADD UNIQUE(字段);

  • 添加普通索引【默认索引名为字段名】:

    ALTER TABLE 表名 ADD INDEX(字段);

• 创建表结构时指定:

  CREATE TABLE IF NOT EXISTS classes (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(10) NOT NULL,
      INDEX(name)
  );

• 查看索引页大小

  SHOW GLOBAL STATUS LIKE 'innodb_page_size';

创建原则:

• 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
• 经常使用WHERE条件搜索的字段，例如user表的id, name等字段
• 经常使用表连接的字段(内连接、外连接)，可以加快连接的速度
• 经常排序的字段，因为索引己经是排过序的，这样一来可以利用索引的排序，加快排序查询速度
• 索引数也不是越多越好，因为索引的建立和维护都是需要耗时的，创建表时需要通过数据库去维护索引，添加记录、更新、修改时也需要更新索引，会间接影响数据库的效率

避免索引失效:

• 全值匹配: 对索引中所有列都指定具体值(对索引尽量不用LIKE)
• 最左匹配原则: 复合索引中，查询从索引的最左列开始，并且不跳过索引中的列【如果条件中包含了复合索引的全部字段，那么可以不考虑前后顺序】
• 复合索引中，范围查询右边的列，不能使用索引
• 不要在索引列上进行运算操作，索引将失效【如SUBSTRING】
• 数据类型不匹配: 如果在查询中将字符串与数字进行比较，或者使用不同的字符集进行比较，索引也会失效
• 用OR分割开的条件，如果OR前的条件中的列有索引，而后面列没有索引，那么涉及的索引都不会被用到
• 以％开头的LIKE模糊查询，索引失效【如果仅仅是尾部模糊匹配，索引不会失效】
• 如果MySQL评估使用索引比全表更慢，则不使用索引
• IN走索引，NOT IN索引失效
• 尽量使用覆盖索引，避免SELECT【只访问索引的查询(索引列完全包含查询列)】
• IS NULL, IS NOT NULL有时索引失效。

日志

• redolog(重做日志): 用于MySQL数据库崩溃恢复的一种机制，它记录了在事务执行期间所做的所有修改操作。如果MysQL非正常关闭，重做日志将被用来重新执行己提交但未持久化的事务以确保数据的完整性。
• undolog(撤销日志): 用于MySQL事务处理的机制，它记录了正在进行的事务中所做的所有修改操作。如果需要回滚一个事务，那么撤销日志将被用来撤销该事务中对数据库所作的任何更改。
• binlog(二进制日志): 用于记录所有修改数据库的语句的机制，包括INSERT, UPDATE和DELETE等操作。通过binlog可以实现数据备份、复制和恢复。

常规维护

• 备份与还原:
  1. 备份整个数据库

     mysqldump testdb --result-file=dump.sql --user=root --host=127.0.0.1 --port=3307

  2. 还原整个数据库

     mysql --database=testdb --user=root --host=127.0.0.1 --port=3307 < dump.sql

• 密码设置:

  UPDATE mysgl.user SET password=PASSWORD('1234asdw') WHERE User=' root';
  FLUSH PRIVILEGES;