数据库突击

最新推荐文章于 2024-07-19 00:12:38 发布

Uafhængige

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量276

点赞数

文章标签：数据库 java 服务器

本文链接：https://blog.csdn.net/weixin_43896452/article/details/133915738

版权

文章目录

联结

在数据库中，“联结”（JOIN）是一种强大的工具，它允许我们查询两个或更多的表，根据特定的条件将它们的行组合起来。不同类型的联结会根据不同的需求和条件返回数据。这里，我们将探讨几种不同类型的联结：内联结（Inner Join），自联结（Self Join），自然联结（Natural Join）和外部联结（Outer Join）。

当然可以，我们将使用更详细的例子来解释这些联结的不同之处。下面是两个虚构的数据表，我们将用它们来演示不同类型的SQL联结。

Students 表 - 代表学生信息。

+------------+--------+
| student_id | name   |
+------------+--------+
| 1          | Alice  |
| 2          | Bob    |
| 3          | Charlie|
+------------+--------+

Courses 表 - 记录课程与学生的对应信息。

+----------+---------+------------+
| course_id| name    | student_id |
+----------+---------+------------+
| 101      | Math    | 1          |
| 102      | Science | 2          |
| 103      | Art     | 3          |
| 104      | History | NULL       |
+----------+---------+------------+

现在，我们来看几种不同类型的联结操作及其结果：

1. 内联结 (INNER JOIN)

这种联结返回两个表中匹配条件的记录。

SQL 语句:

SELECT Students.name AS StudentName, Courses.name AS CourseName
FROM Students
INNER JOIN Courses ON Students.student_id = Courses.student_id;

结果:

+------------+-----------+
| StudentName| CourseName|
+------------+-----------+
| Alice      | Math      |
| Bob        | Science   |
| Charlie    | Art       |
+------------+-----------+

这里，内联结没有返回 History 课程，因为没有学生选这门课（即没有匹配的学生ID）。

2. 自联结 (SELF JOIN)

自联结是表与自身的联结。假设 Students 表还有一个列，记录每个学生的朋友（也是学生ID）。

修改后的 Students 表:

+------------+--------+---------+
| student_id | name   | friend_id|
+------------+--------+---------+
| 1          | Alice  | 3       |
| 2          | Bob    | 1       |
| 3          | Charlie| 2       |
+------------+--------+---------+

SQL 语句:

SELECT A.name AS StudentName, B.name AS FriendName
FROM Students A, Students B
WHERE A.friend_id = B.student_id;

结果:

+------------+-----------+
| StudentName| FriendName|
+------------+-----------+
| Alice      | Charlie   |
| Bob        | Alice     |
| Charlie    | Bob       |
+------------+-----------+

3. 自然联结 (NATURAL JOIN)

自然联结（NATURAL JOIN）是基于两个表中所有具有相同名称和数据类型的列。
（下面这个例子并不完善）

SQL 语句:

SELECT *
FROM Students
NATURAL JOIN Courses;

结果:

+------------+--------+---------+------------+
| student_id | name   | course_id| name      |
+------------+--------+---------+------------+
| 1          | Alice  | 101     | Math       |
| 2          | Bob    | 102     | Science    |
| 3          | Charlie| 103     | Art        |
+------------+--------+---------+------------+

自然联结通过 student_id 列联结表，并返回匹配的行。

4. 外部联结 (OUTER JOIN)

左外联结 (LEFT OUTER JOIN)

返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配项，则结果为 NULL。

SQL 语句:

SELECT Students.name AS StudentName, Courses.name AS CourseName
FROM Students
LEFT OUTER JOIN Courses ON Students.student_id = Courses.student_id;

结果:

+------------+-----------+
| StudentName| CourseName|
+------------+-----------+
| Alice      | Math      |
| Bob        | Science   |
| Charlie    | Art       |
+------------+-----------+

右外联结 (RIGHT OUTER JOIN)

返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配项，则结果为 NULL。

SQL 语句:

SELECT Students.name AS StudentName, Courses.name AS CourseName
FROM Students
RIGHT OUTER JOIN Courses ON Students.student_id = Courses.student_id;

结果:

+------------+-----------+
| StudentName| CourseName|
+------------+-----------+
| Alice      | Math      |
| Bob        | Science   |
| Charlie    | Art       |
| NULL       | History   |
+------------+-----------+

在这里，所有课程都被列出了，即使没有学生注册“History”。

全外联结 (FULL OUTER JOIN)

结合左外联结和右外联结的特点，返回左表和右表中的所有记录。

SQL 语句:

SELECT Students.name AS StudentName, Courses.name AS CourseName
FROM Students
FULL OUTER JOIN Courses ON Students.student_id = Courses.student_id;

结果:

+------------+-----------+
| StudentName| CourseName|
+------------+-----------+
| Alice      | Math      |
| Bob        | Science   |
| Charlie    | Art       |
| NULL       | History   |
+------------+-----------+

这种联结确保获取表中的所有数据，无论它们是否在两个表中都有匹配的记录。在此示例中，“History”课程没有对应的学生，但仍然出现在结果中。

(CROSS JOIN)用于实现两个表的笛卡尔积

笛卡尔积

笛卡尔积是一个数学术语，但在数据库领域，特别是在使用SQL时，它有特定的含义。在SQL中，当你在没有指定联接条件的情况下联接两个或更多的表时，就会发生笛卡尔积。

定义:

笛卡尔积是指在两个集合中的所有可能的有序对组合。当应用于数据库表时，它返回参与运算的每个表中的每一行与另一个表中的每一行的组合。如果第一个表有"A"行，第二个表有"B"行，那么笛卡尔积的结果将有"A x B"行。

例子:

假设我们有两个表，Students 和 Courses。

Students 表

+------------+--------+
| student_id | name   |
+------------+--------+
| 1          | Alice  |
| 2          | Bob    |
+------------+--------+

Courses 表

+------------+---------+
| course_id  | name    |
+------------+---------+
| 101        | Math    |
| 102        | Science |
+------------+---------+

现在，我们来执行一个没有任何联接条件的基本联接查询。

SELECT * FROM Students, Courses;

或者

SELECT * FROM Students CROSS JOIN Courses;

以上两个查询都不考虑表之间的任何关系，因此，返回的结果是两个表的笛卡尔积。

结果将是：

+------------+-------+-----------+---------+
| student_id | name  | course_id | name    |
+------------+-------+-----------+---------+
| 1          | Alice | 101       | Math    |
| 1          | Alice | 102       | Science |
| 2          | Bob   | 101       | Math    |
| 2          | Bob   | 102       | Science |
+------------+-------+-----------+---------+

如您所见，结果集中的每一行都是 Students 表中的每一行与 Courses 表中的每一行的所有可能组合。在这个例子中，我们有2个学生和2门课程，所以我们得到4行数据，即 2 x 2 = 4。这就是笛卡尔积：每个集合中的项与另一个集合中的每个项的所有可能组合。

在实际应用中，通常避免这种情况，因为它生成了大量的、往往是无意义的数据。这也是为什么在进行表连接时总是应该使用适当的联接条件（如 INNER JOIN...ON），这样可以只获取有意义的、相关联的行。

UNION

在MySQL中，UNION操作符用于合并两个或更多SELECT语句的结果集为单个结果集。它删除了重复的记录并按列顺序对结果进行排序。为了使UNION工作，这些SELECT语句必须拥有相同数量的列，列也要拥有相似的数据类型。

`UNION`的作用:

合并多个SELECT语句的结果集到一个表中。
只包含不同的值（删除重复项）。

示例场景:

假设我们有两个销售部门，它们各自的销售数据存储在两个不同的表中。我们希望查询公司所有的销售记录，不论它们来自哪个部门。

Sales_Dept1 表

+---------+-----------+-------------+
| OrderID | Product   | Amount      |
+---------+-----------+-------------+
| 1       | Product A | $100        |
| 2       | Product B | $150        |
+---------+-----------+-------------+

Sales_Dept2 表

+---------+-----------+-------------+
| OrderID | Product   | Amount      |
+---------+-----------+-------------+
| 3       | Product C | $200        |
| 4       | Product D | $350        |
+---------+-----------+-------------+

使用`UNION`:

为了获取两个部门所有的销售记录，我们使用UNION来合并两个查询结果：

SELECT OrderID, Product, Amount FROM Sales_Dept1
UNION
SELECT OrderID, Product, Amount FROM Sales_Dept2;

结果集:

这个查询将返回以下结果，整合了两个部门的销售数据，且每条记录是唯一的：

+---------+-----------+-------------+
| OrderID | Product   | Amount      |
+---------+-----------+-------------+
| 1       | Product A | $100        |
| 2       | Product B | $150        |
| 3       | Product C | $200        |
| 4       | Product D | $350        |
+---------+-----------+-------------+

UNION ALL:

相对于UNION，UNION ALL操作符合并两个结果集，但包括了所有的记录（即使是重复的记录）。

如果在上述例子中，某一产品在两个部门都有销售记录，UNION将只显示一条记录，而UNION ALL将显示两条记录。

使用UNION和UNION ALL的选择取决于您是否想在最终结果集中包含重复的行。通常，如果您知道合并的结果集不会产生重复行，或者您想保留那些重复的行，您应该使用UNION ALL，因为它不会检查重复，因此效率更高。

增删改查

drop：删除整张表
delete：删除某一行
insert：插入数据
truncat：删除表中所有数据
create：创建表
update：更新特定的一个数据
alter：更改表的结构
rename：重命名表

在MySQL中，增删改查（CRUD）以及其他表操作是日常数据库管理的基本组成部分。下面是每个操作的简单示例和相关注意事项。

1. CREATE：创建表

CREATE TABLE Students (
    StudentID int NOT NULL AUTO_INCREMENT,
    Name varchar(255) NOT NULL,
    Age int,
    PRIMARY KEY (StudentID)
);

注意：定义合适的数据类型和约束（如NOT NULL、PRIMARY KEY等）是非常重要的。

2. INSERT：插入数据

INSERT INTO Students (Name, Age) VALUES ('John Doe', 22);

注意：插入数据需要确保数据类型与列定义相匹配，并且必须遵守表约束（如非空约束）。

3. UPDATE：更新数据

UPDATE Students SET Age = 23 WHERE StudentID = 1;

注意：在没有WHERE子句的情况下执行UPDATE将更新表中的所有行。这可能导致数据不一致，应谨慎使用。

4. DELETE：删除行

DELETE FROM Students WHERE StudentID = 1;

注意：与UPDATE一样，省略WHERE子句将删除表中的所有行。

5. SELECT：查询数据

SELECT * FROM Students WHERE Age >= 18;

注意：为了提高查询性能，避免使用SELECT *，而是指定需要的列。

6. DROP：删除表

DROP TABLE Students;

注意：DROP操作是不可恢复的。执行后，所有数据和表结构都将永久丢失。

7. TRUNCATE：清空表

TRUNCATE TABLE Students;

注意：TRUNCATE比DELETE快很多，因为它不记录个别行的删除。它删除所有行，并重置任何自动增量计数器。

8. ALTER：修改表

ALTER TABLE Students ADD COLUMN Gender VARCHAR(10);

注意：更改现有的表结构可能会影响到基于该表的视图、存储过程或应用程序功能。

9. RENAME：重命名表

RENAME TABLE Students TO Alumni;

注意：重命名表时，需要确保更新所有引用该表的相关查询和程序代码。

综合注意点：

备份：在执行可能影响数据完整性的操作（尤其是DROP、DELETE、TRUNCATE）之前，先做备份是一个好习惯。
事务：对于一系列必须全部执行或全部不执行的操作，应使用事务来保证数据的完整性。
性能影响：SELECT、UPDATE、DELETE等操作在大数据表上可能导致性能问题，使用索引可以减轻这些操作的性能影响。
权限管理：确保执行这些命令的数据库用户具有适当的权限，避免权限过大导致的潜在安全风险。

索引

在MySQL中，索引是帮助加速对数据库表的查询的数据结构。您可以将其视为一本书的目录，您可以快速查找特定章节，而不必一页一页地翻阅整本书。

作用:

提高检索速度：最主要的优点是加快数据检索速度。特别是在大数据量的表中，没有索引，数据库必须扫描整个表来找到所需的行。索引可以极大地减少需要扫描的数据量。
加快排序和分组操作：索引还可以加快ORDER BY和GROUP BY操作的速度。
加强数据完整性：唯一索引保证列的唯一性，从而加强了数据的完整性。
更有效的连接操作：在涉及连接的查询中，如果要连接的列已经被索引，那么这些查询的性能也会得到改善。

弊端:

占用更多的存储空间：每一个索引都需要占用物理存储空间。
降低数据修改速度：当对表进行INSERT、UPDATE或DELETE操作时，数据库不仅要保存数据，还要保存索引文件。每次数据修改都要同步更新索引，这可能会导致性能下降。
可能的不当索引：如果没有正确地选择或设计索引，有时索引可能不会被查询优化器使用，造成资源浪费。

使用场景:

大量的频繁的读取操作，但写操作相对较少。
表数据量大，查询条件中经常使用到的列。
对查询性能要求高的情况。

示例:

假设您有一个包含数百万条记录的订单表“Orders”。

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber varchar(255) NOT NULL,
    CustomerID int NOT NULL,
    ProductID int NOT NULL,
    Quantity int NOT NULL,
    OrderDate date NOT NULL
);

在这个场景中，如果您经常根据CustomerID进行查询来检索客户的订单信息，那么您应该为CustomerID创建索引。

CREATE INDEX idx_customer ON Orders (CustomerID);

这样做之后，当您查询特定客户的订单时，MySQL可以使用这个索引快速定位到表中相应的行，而不是扫描整个表：

SELECT * FROM Orders WHERE CustomerID = 1001;

这个查询将会利用我们创建的索引idx_customer，大大减少查询所需的时间。

索引策略:

选择性高的列: 索引的列应该是在查询条件（WHERE子句）中经常使用或是连接条件中常用的列。
避免过度索引: 过多的索引会导致写操作变慢，同时消耗更多的存储资源。
使用合适的索引类型: MySQL提供了多种索引类型（如：B树，哈希，全文本，空间索引等）。根据需求选择最适合的索引类型至关重要。

总的来说，索引是优化数据库查询性能的强大工具，但必须谨慎使用，确保它们能为您的特定用例提供实际价值。

为了解释索引如何改变数据的检索方式，我们需要比较在有索引和无索引情况下，MySQL如何处理数据检索的。

1. 无索引情况：全表扫描

假设"Orders"表没有任何索引，情况如下：

原始状态：数据库存储了数百万条订单记录，每条记录都是表中的一行。这些数据分布在磁盘上。
查询操作：当您发出查询请求，如SELECT * FROM Orders WHERE CustomerID = 1001;，数据库管理系统（DBMS）需要执行"全表扫描"。意思是，它会检查表中的每一行，看是否CustomerID的值等于1001。
性能问题：全表扫描非常耗时，尤其是对于大表而言。DBMS必须逐一读取每行数据，加载它们到内存中，并检查CustomerID字段。这个过程涉及大量的磁盘I/O操作，消耗很多计算资源，导致查询速度慢。

2. 有索引情况：索引扫描

现在，假设您为"CustomerID"列创建了索引。情况将大为不同：

索引结构：索引可以被视为指向表中数据的指针列表。这些指针按CustomerID的值排序。索引本身也存储在磁盘上，并且通常使用高效的数据结构（如B树），这使得搜索操作非常快。
查询操作：当相同的查询请求到来时，DBMS现在可以使用索引来加速查找。由于索引是排序的，DBMS可以使用高效的搜索算法（如二分搜索），快速定位到具有特定CustomerID的记录。
性能提升：一旦找到索引项，DBMS就知道如何迅速地定位实际的记录行在磁盘上的位置。这消除了对除目标记录以外的数据的大部分磁盘I/O操作，从而大大加快了检索速度。
结果：通过避免全表扫描，查询响应时间变得更短，系统的整体负载降低，因为处理查询所需的磁盘I/O大大减少了。

总之，索引的加入改变了DBMS内部查找数据的方式，从无效率的全表扫描转变为快速的索引搜索，大大减少了处理大量数据时的时间消耗和系统资源的使用。

事务

定义：

在数据库管理系统中，一个事务是一系列可能要进行的更改，这些更改作为一个单元执行。事务可以看作是一个单一的逻辑工作单元，它自己本身要么完全执行，要么完全不执行（即“所有或无”规则）。

特性（ACID）:

事务的特性通常用ACID（原子性、一致性、隔离性、持久性）来概括：

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。没有部分完成的场景。
一致性（Consistency）：事务必须使数据库从一个一致性状态变到另一个一致性状态。不会因为事务的部分应用而破坏数据的完整性。
隔离性（Isolation）：并发执行的事务彼此之间是独立的，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：一旦事务提交，其结果就是永久的，即使系统故障也不会丢失。

利弊:

优点：

数据完整性：事务提供一种保证数据完整性的方法，确保一系列操作在出现故障的情况下不会部分完成。
错误恢复：在事务处理期间，如果发生错误，系统可以回滚，返回到事务开始之前的状态。
并发控制：事务通过锁定资源和“提交”或“回滚”更改，管理多用户同时访问数据时可能发生的冲突。

缺点：

性能损耗：事务处理需要系统资源来维护各种状态，可能会稍微降低性能，尤其是在高并发系统中。
复杂性：正确管理事务需要精心设计，特别是在需要跨多个系统或服务的分布式事务中。
死锁风险：事务可能导致死锁，即两个或更多事务互相等待对方释放资源。

注意事项:

尽量简短：保持事务尽可能简短，减少锁定资源的时间，提高并发处理能力。
谨慎管理锁：理解事务中的锁机制，避免死锁。
错误处理：实现适当的错误处理机制，以便在事务中出现问题时，可以适当地执行回滚操作。
避免不必要的事务：不是所有操作都需要事务。如果操作本身是原子的或不影响数据完整性，不必使用事务。

示例：

假设您管理一个银行数据库，需要从一个账户转账到另一个账户。这涉及到两个操作：从一个账户扣款和向另一个账户存款。这两个操作必须都成功或都失败，所以您需要使用事务。

START TRANSACTION;  -- 开始事务

-- 从账户A扣除100
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 'A';
-- 向账户B存入100
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 检查错误并确定提交或回滚
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- 回滚事务
    ROLLBACK;
END;

COMMIT;  -- 提交事务

在这个例子中，通过使用事务，我们确保了资金的完整转移。如果在转账过程中的任何点发生错误或系统故障，整个事务都会回滚，资金将返回到原始账户，保证了资金的安全性。

视图 create view xx as select…

定义：

在数据库中，视图是一个虚拟表，其内容由查询定义。与包含数据的表不同，视图只包含使用时动态生成的数据的SQL查询。

功能和优点：

简化复杂查询：可以通过创建视图来隐藏复杂的SQL查询，只展示简单的表。
安全性：视图可用于为用户提供数据的部分视图，而不是整个数据库表，从而确保数据的安全性。
数据抽象：视图可以保持数据库模式的一致性，即使底层数据模型发生变化，用户也不会注意到变化。
聚合数据：视图可用于聚合多个表中的数据，为用户呈现一个统一的数据集。

缺点和限制：

性能：视图的查询有时可能因为需要额外处理而导致性能下降，尤其是对于复杂的嵌套视图。
更新限制：并非所有视图都是可更新的。如果视图基于多个基础表或具有复杂的SQL语句，可能无法执行更新操作。
依赖管理：如果底层表发生更改（例如，删除列），依赖于这些表的视图可能会变得无效。

使用场景：

当用户需要频繁访问基于多表查询的相同结果集时。
为了提高安全性，限制对特定用户访问的数据。
简化复杂查询，用户可以查询视图而不必了解复杂的SQL语句。

注意事项：

谨慎更新：如果需要更新视图，必须确保视图是可更新的，并且更新不会导致数据不一致。
性能监控：由于一些视图可能导致性能问题，因此在使用视图时要监控查询性能。
维护：数据库结构更改时要考虑视图的维护，确保其持续正确反映所需信息。

示例：

假设有一个包含所有员工详细信息的表“Employees”，现在需要频繁地查询员工的部分信息。可以创建一个视图，只展示员工的姓名和部门。

-- 创建视图
CREATE VIEW View_EmployeeBasicInfo AS
SELECT employee_name, department FROM Employees;

-- 查询视图
SELECT * FROM View_EmployeeBasicInfo;

在这个例子中，View_EmployeeBasicInfo视图为用户提供了一个简化的界面，隐藏了底层的复杂性。用户可以像查询普通表一样查询这个视图，但不需要关心其他不相关的列信息。此外，由于这是一个只读视图，所以数据的完整性和安全性得到了保障。

触发器

当然，MySQL中的触发器是数据库编程的一个重要组成部分，下面是关于触发器的详细解释。

定义：

触发器（Trigger）是与表有关的数据库回调函数，当表上发生指定事件时自动执行。这些事件包括各种INSERT、UPDATE和DELETE操作。简单来说，触发器是一种自动响应某些事件发生的特殊存储过程。

功能和优点：

数据完整性：触发器可用于强制执行数据库的完整性规则，这些可能超出了标准SQL约束的范围。
自动化审计：可以用触发器来自动维护修改记录（如数据修改的时间、修改者等信息），实现对关键表的审计跟踪。
复杂业务规则：通过触发器，可以在数据库层实现复杂的业务逻辑，确保数据的一致性和准确性。
自动化任务：触发器可用于自动执行响应数据库表更改的例行任务，例如发送通知、更新其他表等。

缺点和限制：

性能影响：触发器可以在数据库系统中增加额外的开销，如果大量使用或设计不当，可能导致性能问题。
维护难度：由于触发器是透明的，如果数据库中有很多触发器，可能会很难追踪它们产生的影响，导致问题难以调试。
复杂性增加：过度依赖触发器可以使数据库逻辑变得复杂，进而增加数据库的复杂性和难以管理。

使用场景：

在用户更新某些敏感数据表时自动记录审计日志。
当在一个表中插入数据时，自动在另一个关联表中更新或插入数据。
验证数据库更新操作，防止无效数据的录入。

注意事项：

避免过度使用：只在必要时使用触发器，避免不必要的复杂性和潜在性能降低。
错误处理：确保触发器代码能妥善处理错误，避免无限循环或事务阻塞。
测试与监控：对触发器进行充分测试，监控其对数据库性能的影响。

示例：

假设有一个订单表orders，每当向其中添加新订单时，我们都想在audit_log表中添加一条记录来跟踪这个添加操作。

首先，有一个审计日志表audit_log：

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
);

然后，创建一个触发器，当有新订单插入orders表时触发：

DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action) VALUES ('New order added with ID:' || NEW.id);
END;
//
DELIMITER ;

现在，每当有新订单添加到orders表时，触发器就会自动工作，向audit_log表中插入一条关于此操作的记录。这种自动化跟踪和记录是触发器的常见用途。

Uafhængige

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库突击

笛卡尔积是指在两个集合中的所有可能的有序对组合。当应用于数据库表时，它返回参与运算的每个表中的每一行与另一个表中的每一行的组合。如果第一个表有"A"行，第二个表有"B"行，那么笛卡尔积的结果将有"A x B"行。在数据库管理系统中，一个事务是一系列可能要进行的更改，这些更改作为一个单元执行。事务可以看作是一个单一的逻辑工作单元，它自己本身要么完全执行，要么完全不执行（即“所有或无”规则）。在数据库中，视图是一个虚拟表，其内容由查询定义。与包含数据的表不同，视图只包含使用时动态生成的数据的SQL查询。触发器。
复制链接

扫一扫