mysql 全方面知识点基本框架——大学生复习资料以上涵盖了《数据库原理及运用》课程中涉及的主要SQL操作和概念。如果你

1. 数据库基础

1.1 定义与特点

定义：数据库是一个按照数据结构来组织、存储和管理数据的仓库。它能够确保数据的安全性、完整性和高效访问。
特点：
- 持久性：即使在系统崩溃后，数据仍然保持不变。
- 并发性：允许多个用户同时访问数据库而不产生冲突。
- 数据独立性：应用程序可以不依赖于具体的数据存储结构。

1.2 数据模型

屏幕截图 2024-12-30 204309.png

层次模型的特点

层次模型是一种树形结构，其中每个节点代表记录类型（实体），边则表示指针连接这些记录。这种模型规定除了根节点外，其他任何节点都有唯一的一个父节点。因此，在层次模型中，父子关系形成了一种严格的上下级结构。

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    Name VARCHAR(50),
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    DeptID INT FOREIGN KEY REFERENCES Department(DeptID)
);

上述SQL语句展示了如何创建一个简单的层次型数据库模式，通过FOREIGN KEY定义了部门与员工间的父子关联。

网状模型的特点

相比之下，网状模型提供了更为灵活的关系表达方式。它不仅支持单向链接还允许存在多对多以及一对多等多种复杂联系形式。具体而言：

多个结点可拥有超过一个直接前驱；
结点间可能存在多种形式的关联；
更贴近实际生活中的事物相互作用情况；

这使得网状模型能够更加真实地反映现实生活里错综复杂的对象交互场景。

关系模型的特点

对于关系模型来说，其核心优势在于简洁性和强大的理论基础之上构建而成。所有的实体及它们之间的联系都被统一成表格的形式存储起来，并且操作也围绕着表来进行。主要特性如下所示：

实体和实体间的联系均采用二维表格即“关系”的方式进行表述；
对于查询结果同样返回的是一个新的关系；
存储路径的选择对外界隐藏不可见，提高了数据的安全性和独立程度；
基础运算符集易于理解和掌握，降低了编程难度并促进了应用软件的发展；

由于以上原因，关系模型成为了当前最广泛使用的数据库设计范式之一。

1.3 SQL语言

DDL (Data Definition Language) ：用于定义或修改数据库结构，如CREATE, ALTER, DROP等命令。
DML (Data Manipulation Language) ：用于操作数据库中的数据，如SELECT, INSERT, UPDATE, DELETE等。
DCL (Data Control Language) ：用于控制对数据库的访问权限，如GRANT, REVOKE等。
TCL (Transaction Control Language) ：用于管理事务，如COMMIT, ROLLBACK等。

1.4 数据库管理系统(DBMS)

架构：典型的DBMS采用三层架构，即用户层、应用层和数据层。
功能：包括数据定义、数据操纵、数据控制、数据维护等。
类型：根据处理的数据模型分为关系型DBMS（RDBMS）、对象型DBMS、文档型DBMS等。

2. 关系数据库设计

2.1 范式理论

第一范式(1NF) ：确保每一列都是不可分割的基本数据项。
第二范式(2NF) ：在满足1NF的基础上，消除非主属性对候选键的部分函数依赖。
第三范式(3NF) ：在满足2NF的基础上，消除非主属性对候选键的传递函数依赖。
BCNF (Boyce-Codd Normal Form) ：进一步加强3NF，要求所有非平凡的函数依赖的左部都包含候选键。
第四范式(4NF) ：消除多值依赖。
第五范式(5NF) ：也称投影-连接范式，用于解决复杂的关系模式分解问题。

2.2 E-R图

1、什么是E-R图

E-R图即实体-联系图(Entity Relationship Diagram)，是指提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。E-R方法:是“实体-联系方法”（Entity-Relationship Approach）的简称。它是描述现实世界概念结构模型的有效方法。实体联系模型，实体关系模型或实体联系模式图(ERD)是由美籍华裔计算机科学家陈品山(Peter Chen)发明，是概念数据模型的高层描述所使用的数据模型或模式图，它为表述这种实体联系模式图形式的数据模型提供了图形符号。这种数据模型典型的用在信息系统设计的第一阶段；比如它们在需求分析阶段用来描述信息需求和/或要存储在数据库中的信息的类型。但是数据建模技术可以用来描述特定论域(就是感兴趣的区域)的任何本体(就是对使用的术语和它们的联系的概述和分类)。在基于数据库的信息系统设计的情况下，在后面的阶段(通常叫做逻辑设计)，概念模型要映射到逻辑模型如关系模型上；它依次要在物理设计期间映射到物理模型上。注意，有时这两个阶段被一起称为”物理设计”。

2、E-R图的基本要素

通常，使用实体-联系图(entity-relationship diagram)来建立数据模型。可以把实体-联系图简称为ER图，相应地可把用ER图描绘的数据模型称为ER模型。ER图中包含了实体(即数据对象)、关系和属性等3种基本成分，通常用矩形框代表实体，用连接相关实体的菱形框表示关系，用椭圆形或圆角矩形表示实体(或关系)的属性，并用直线把实体(或关系)与其属性连接起来。例如，图1是某学校教学管理的ER图。人们通常就是用实体、联系和属性这3个概念来理解现实问题的，因此，ER模型比较接近人的习惯思维方式。此外，ER模型使用简单的图形符号表达系统分析员对问题域的理解，不熟悉计算机技术的用户也能理解它，因此，ER模型可以作为用户与分析员之间有效的交流工具。实体型(Entity)：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如学生张三丰、学生李寻欢都是实体。如果是弱实体的话，在矩形外面再套实线矩形。属性(Attribute)：实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如学生的姓名、学号、性别、都是属性。如果是多值属性的话，再椭圆形外面再套实线椭圆。如果是派生属性则用虚线椭圆表示。联系(Relationship)：数据对象彼此之间相互连接的方式称为联系，也称为关系。联系可分为以下 3 种类型： (1) 一对一联系 (1 ∶ 1) 例如，一个部门有一个经理，而每个经理只在一个部门任职，则部门与经理的联系是一对一的。 (2) 一对多联系 (1 ∶ N) 例如，某校教师与课程之间存在一对多的联系“教”，即每位教师可以教多门课程，但是每门课程只能由一位教师来教【见图1】。 (3) 多对多联系 (M ∶ N) 例如，图1表示学生与课程间的联系(“ 学 ”)是多对多的，即一个学生可以学多门课程，而每门课程可以有多个学生来学。联系也可能有属性。例如，学生 “ 学 ” 某门课程所取得的成绩，既不是学生的属性也不是课程的属性。由于 “ 成绩 ” 既依赖于某名特定的学生又依赖于某门特定的课程，所以它是学生与课程之间的联系 “ 学 ”的属性.

<图1>

3、实例图书借阅管理系统

屏幕截图 2024-12-30 205317.png 3.1数据库要求提供下述服务：

（1）可随时查询书库中现有书籍的品种、数量与存放位置。所有各类书籍均可由书号惟一标识。（2）可随时查询书籍借还情况，包括借书人单位、姓名、借书证号、借书日期和还书日期。约定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有惟一性。（3）当需要时，可通过数据库中保存的出版社的电报编号、电话、邮编及地址等信息向相应出版社增购有关书籍。约定，一个出版社可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有惟一性。

3.2数据建模：

（1）满足上述需求的E-R图如图：

（2）转换为等价的关系模式结构如下：

借书人（借书证号，姓名，单位）

图书（书号，书名，数量，位置，出版社名）

出版社（出版社名，电报编号，电话，邮编，地址）

借阅（借书证号，书号，借书日期，还书日期）

2.3 关系模式设计

转换规则：将E-R图中的实体和联系转换为关系模式，遵循一定的转换原则，例如强实体转换为单一关系模式，弱实体与主实体合并为一个关系模式等。
优化：通过分析和调整关系模式，确保其符合特定的范式标准，从而减少冗余并提高性能。

当然，我将为上述各个部分提供适当的SQL代码示例，以帮助你更好地理解和应用这些知识点。以下是针对每个部分的代码片段：

3. SQL进阶

3.1 子查询

相关子查询：假设我们有一个orders表和一个customers表，想要找出所有订单金额大于该客户平均订单金额的订单。

SELECT o.*
FROM orders o
WHERE o.amount > (
    SELECT AVG(amount)
    FROM orders
    WHERE customer_id = o.customer_id
);

非相关子查询：获取所有订单中最大的订单金额。

SELECT *
FROM orders
WHERE amount = (
    SELECT MAX(amount)
    FROM orders
);

EXISTS/NOT EXISTS：查找所有没有下过订单的客户。

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

IN/NOT IN：查找所有来自特定国家的客户。

SELECT *
FROM customers
WHERE country IN ('USA', 'Canada');

3.2 连接（JOIN）

内连接(INNER JOIN)：获取所有有订单记录的客户信息及其订单详情。

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

左外连接(LEFT OUTER JOIN)：获取所有客户信息，即使他们没有下过订单。

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id;

右外连接(RIGHT OUTER JOIN)：获取所有订单信息，即使这些订单对应的客户信息不存在。

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
RIGHT OUTER JOIN orders o ON c.customer_id = o.customer_id;

全外连接(FULL OUTER JOIN)：获取所有客户和订单的信息，无论是否有匹配。

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

交叉连接(CROSS JOIN)：生成两个表的所有组合。

SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p;

3.3 聚合函数

COUNT()：统计总行数或非NULL值的数量。

SELECT COUNT(*) AS total_rows, COUNT(customer_id) AS non_null_customers
FROM orders;

SUM()：计算数值列的总和。

SELECT SUM(amount) AS total_amount
FROM orders;

AVG()：计算平均值。

SELECT AVG(amount) AS average_amount
FROM orders;

MAX()/MIN()：找出最大值或最小值。

SELECT MAX(amount) AS max_amount, MIN(amount) AS min_amount
FROM orders;

GROUP BY：按指定列分组数据，并与聚合函数一起使用。

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

HAVING：用于过滤分组后的结果。

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5 AND SUM(amount) > 1000;

3.4 视图

创建视图：创建一个显示每个客户的订单总数和总金额的视图。

CREATE VIEW customer_order_summary AS
SELECT c.customer_name, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

查询视图：使用视图进行查询。

SELECT * FROM customer_order_summary;

更新视图：如果视图是可更新的，可以直接通过视图更新底层表的数据（注意并非所有视图都支持更新）。

UPDATE customer_order_summary
SET total_amount = 2000
WHERE customer_name = 'John Doe';

4. 事务处理

4.2 事务管理

BEGIN TRANSACTION：开始一个新的事务。

BEGIN TRANSACTION;

COMMIT：提交当前事务，使更改成为永久性的。

COMMIT;

ROLLBACK：撤销当前事务，恢复到事务开始前的状态。

ROLLBACK;

4.3 并发控制

锁机制：在MySQL中，可以通过SELECT ... FOR UPDATE来显式地加写锁。

START TRANSACTION;

-- 加锁读取，防止其他事务修改这些行
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 更新账户余额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

5. 数据库性能优化

5.1 索引

创建B树索引：为orders表的customer_id列创建索引。

CREATE INDEX idx_customer_id ON orders (customer_id);

创建哈希索引：某些数据库系统（如MySQL的Memory存储引擎）支持哈希索引。

CREATE INDEX idx_product_id USING HASH ON products (product_id);

创建普通索引是最常见的索引类型，它不强制唯一性，主要用于加速查询。可以在一个或多个列上创建普通索引。

sql
浅色版本
CREATE INDEX idx_product_name ON products (product_name);

创建组合索引：可以在多个列上创建一个索引，以优化涉及多列的查询。

sql
浅色版本
CREATE INDEX idx_product_price ON products (product_name, price);

创建唯一索引

唯一索引确保索引列中的所有值都是唯一的，但允许NULL值（除非该列为NOT NULL）。它可以用于防止重复数据插入。

sql
浅色版本
CREATE UNIQUE INDEX idx_email ON users (email);

- **创建全文索引**：为`articles`表的`content`列创建全文索引（适用于MyISAM和InnoDB）。

```sql
CREATE FULLTEXT INDEX idx_content ON articles (content);

覆盖索引：确保查询所需的所有列都在索引中。

CREATE INDEX idx_customer_order ON orders (customer_id, order_id, amount);

5.2 查询优化

执行计划：使用EXPLAIN查看查询的执行计划。

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

重构查询：避免不必要的子查询，使用JOIN代替。

-- 原始查询
SELECT o.*
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

-- 重构后的查询
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

缓存：利用查询缓存（如果数据库支持）或应用程序级缓存。

-- MySQL查询缓存已弃用，这里仅作示例
SELECT SQL_CACHE * FROM orders WHERE customer_id = 1;

6. 数据库安全

6.1 用户权限管理

创建角色并授予权限：创建一个名为data_analyst的角色，并授予其对sales数据库的读取权限。

CREATE ROLE data_analyst;
GRANT SELECT ON sales.* TO data_analyst;

授予用户权限：将data_analyst角色分配给用户john_doe。

GRANT data_analyst TO 'john_doe'@'localhost';

回收权限：从用户john_doe收回data_analyst角色。

REVOKE data_analyst FROM 'john_doe'@'localhost';

6.2 加密技术

静态数据加密：在MySQL中，可以使用ENCRYPT()函数加密数据。

INSERT INTO users (username, password)
VALUES ('john_doe', ENCRYPT('password123'));

传输数据加密：配置MySQL服务器以使用SSL/TLS加密客户端连接。

-- 在my.cnf中配置
[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

6.3 备份与恢复

物理备份：使用mysqldump工具进行逻辑备份。

mysqldump -u root -p --all-databases > all_databases.sql

增量备份：使用mysqlbinlog工具进行基于二进制日志的增量备份。

mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" /path/to/binlog.000001 > incremental_backup.sql

热备份：使用Percona XtraBackup等工具进行热备份。

xtrabackup --backup --target-dir=/path/to/backup

冷备份：直接复制数据库文件（需停止服务）。

cp -r /var/lib/mysql /path/to/backup

以上涵盖了《数据库原理及运用》课程中涉及的主要SQL操作和概念。如果你有任何特定的问题或者需要进一步的帮助，请随时告诉我！

年轻就是好，有耐心，看到这里，不愧是我雷总想要的人！！

送你一个专属表情包吧

屏幕截图 2024-12-30 211646.png