19、SQL 数据库操作全解析-优快云博客

本文链接：https://blog.youkuaiyun.com/vscode6remote/article/details/154180746

SQL 数据库操作全解析

1. 数据查询与结果展示

在 SQL 操作中，我们可以使用特定的查询语句获取所需的数据。例如，执行以下查询：

WHERE Package_Descriptions.package_name = Package_Files.package_name)
ORDER BY package_name;

执行该查询后，会得到一系列包含包名和描述的结果：
| package_name | description |
| -------------------- | -------------------------------------------------------- |
| adwaita-icon-theme | default icon theme of GNOME (small subset) |
| codium | Code editing. Redefined. |
| gimp-data | Data files for GIMP |
| … | … |

2. 更新表数据

UPDATE 语句用于更改一个或多个现有行中的值。下面以 sqlite3 包为例，演示如何更新表中文件的大小。
- 查看原始数据 ：

sqlite> .mode column
sqlite> .header on
sqlite> .width 50 -10
sqlite> SELECT file, size_bytes FROM Package_Files
   ...> WHERE package_name = 'sqlite3';

结果如下：
| file | size_bytes |
| -------------------------------------------------- | ---------- |
| /usr/bin/sqldiff | 1103280 |
| /usr/bin/sqlite3 | 1260976 |
| /usr/share/doc/sqlite3/copyright | 1261 |
| /usr/share/man/man1/sqlite3.1.gz | 3596 |
| /usr/share/doc/sqlite3/changelog.Debian.gz | 35 |

更新数据 ：

sqlite> UPDATE Package_Files SET size_bytes = size_bytes + 100
   ...> WHERE package_name = 'sqlite3';

再次查看数据，会发现文件大小已更新：
| file | size_bytes |
| -------------------------------------------------- | ---------- |
| /usr/bin/sqldiff | 1103380 |
| /usr/bin/sqlite3 | 1261076 |
| /usr/share/doc/sqlite3/copyright | 1361 |
| /usr/share/man/man1/sqlite3.1.gz | 3696 |
| /usr/share/doc/sqlite3/changelog.Debian.gz | 135 |

恢复原始数据 ：

sqlite> UPDATE Package_Files SET size_bytes = size_bytes - 100
   ...> WHERE package_name = 'sqlite3';

UPDATE 还可以同时修改多个值。例如，创建一个名为 Package_Stats 的新表，并使用 UPDATE 填充值：

-- create_Package_Stats.sql
DROP TABLE IF EXISTS Package_Stats;
CREATE TABLE Package_Stats (
    package_name  VARCHAR(60),
    count         INTEGER,
    tot_size      INTEGER,
    min_size      INTEGER,
    max_size      INTEGER,
    avg_size      REAL
);
INSERT INTO Package_Stats (package_name)
     SELECT package_name
       FROM Package_Descriptions;
UPDATE Package_Stats
   SET count = (SELECT COUNT(file)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Stats.package_name),
    tot_size = (SELECT SUM(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    min_size = (SELECT MIN(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    max_size = (SELECT MAX(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    avg_size = (SELECT AVG(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name);

查看 Package_Stats 表的前 10 行：
| package_name | count | tot_size | min_size | max_size | avg_size |
| ------------------------- | ----- | ---------- | -------- | -------- | ---------- |
| a2ps | 299 | 3455890 | 117 | 388096 | 11558.1605 |
| accountsservice | 19 | 261704 | 42 | 182552 | 13773.8947 |
| acl | 11 | 91106 | 30 | 35512 | 8282.36363 |
| … | … | … | … | … | … |

3. 删除行

在 SQL 中，删除行相对简单，使用 DELETE 语句并结合 WHERE 子句指定目标。同时，SQLite 支持一个小技巧，可将输出模式更改为输出 INSERT 语句。
- 设置输出模式并保存数据 ：

sqlite> .mode insert Package_Files
sqlite> SELECT * FROM Package_Files WHERE package_name = 'sqlite3';

执行上述语句后，会输出一系列 INSERT 语句。接着，将输出保存到文件 insert_sqlite3.sql 中：

sqlite> .output insert_sqlite3.sql
sqlite> select * from Package_Files where package_name = 'sqlite3';
sqlite> .output

删除行 ：

sqlite> DELETE FROM Package_Files WHERE package_name = 'sqlite3';

再次查询 sqlite3 包的文件，会得到空结果。

恢复行 ：

sqlite> .read insert_sqlite3.sql

再次查询，会发现行已恢复。

4. 添加和删除列

SQL 提供 ALTER TABLE 语句来修改表的架构。
- 添加列 ：
创建 add_column.sql 文件，内容如下：

-- add_column.sql
-- Add and populate columns to Package_Descriptions
ALTER TABLE Package_Descriptions ADD COLUMN files INTEGER;
ALTER TABLE Package_Descriptions ADD COLUMN size INTEGER;
UPDATE Package_Descriptions
   SET files = (SELECT COUNT(file)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Descriptions.package_name),
        size = (SELECT SUM(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Descriptions.package_name);

执行该文件并查看结果：

sqlite> .read add_column.sql
sqlite> .schema
sqlite> SELECT * FROM Package_Descriptions WHERE package_name = 'sqlite3';

结果显示 Package_Descriptions 表已添加 files 和 size 列。

删除列 ：
由于 SQLite 不支持直接删除列，需要通过以下四个步骤实现：
1. 创建一个具有所需架构的新临时表。
2. 将需要保留的列复制到临时表中。
3. 删除原始表。
4. 重命名临时表。

创建 drop_column.sql 文件：

-- drop_column.sql
-- Remove extra columns from Package_Descriptions
BEGIN TRANSACTION;
-- Create new table with temporary name
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
    package_name VARCHAR(60),
    description  VARCHAR(120));
-- Copy columns we want into new table
INSERT INTO temp
    (package_name, description)
    SELECT package_name, description
      FROM Package_Descriptions;
-- Get rid of old table and rename the new replacement table
DROP TABLE Package_Descriptions;
ALTER TABLE temp RENAME TO Package_Descriptions;
COMMIT;

5. 连接表（Joins）

连接是一种执行查询并生成结合两个表数据结果的方法。这里主要介绍最常用的内连接（Inner Join），可以将内连接看作两个表的交集。
创建 join_demo.sql 文件：

-- join_demo.sql
-- Demonstrate join by selecting columns from 2 tables
.header on
.mode column
.width 20 35 -5 -10
SELECT Package_Descriptions.package_name AS Package,
       description AS Description,
       count AS Files,
       tot_size AS Size
  FROM Package_Descriptions
  INNER JOIN Package_Stats
          ON Package_Descriptions.package_name =
             Package_Stats.package_name
LIMIT 10;

执行该查询，结果如下：
| Package | Description | Files | Size |
| -------------------- | ----------------------------------- | ----- | ---------- |
| a2ps | GNU a2ps - ‘Anything to PostScript’ | 299 | 3455890 |
| accountsservice | query and manipulate user account i | 19 | 261704 |
| acl | Access control list utilities | 11 | 91106 |
| … | … | … | … |

6. 视图（Views）

视图允许将复杂的查询存储在数据库中，并生成一个虚拟表，可使用简单的查询命令操作。
创建 view_demo.sql 文件：

-- view_demo.sql
DROP VIEW IF EXISTS Stats;
CREATE VIEW Stats
AS
    SELECT Package_Descriptions.package_name AS Package,
           description AS Description,
           count AS Files,
           tot_size AS Size
      FROM Package_Descriptions
      INNER JOIN Package_Stats
              ON Package_Descriptions.package_name =
                 Package_Stats.package_name
        ORDER BY Package;

创建视图后，可以像操作普通表一样操作 Stats 视图：

sqlite> .header on
sqlite> .mode column
sqlite> .width 20 35 -5 -10
sqlite> SELECT * FROM Stats LIMIT 10;

若要删除视图，使用 DROP VIEW 语句：

sqlite> DROP VIEW Stats;

7. 索引（Indexes）

索引是数据库维护的数据结构，可加快数据库搜索速度。它是按一个或多个列中的元素排序的表中各行的有序列表。
- 未使用索引的查询 ：

sqlite> .header on
sqlite> .mode column
sqlite> .width 20 -5 -8 -8 -8 -8
sqlite> SELECT * FROM Package_Stats WHERE rowid = 100;

数据库可以快速定位第 100 行。但如果要搜索包含特定包名的行，SQLite 必须检查表中的每一行。使用 EXPLAIN QUERY PLAN 查看搜索策略：

sqlite> .width -8 -5 -4 55 
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM Package_Stats WHERE package_name = 'cups-server-common';

结果显示为顺序搜索。

创建索引 ：

sqlite> CREATE INDEX idx_package_name
   ...> ON Package_Stats (package_name);

再次查看查询计划：

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM Package_Stats WHERE package_name = 'cups-server-common';

此时显示使用索引进行搜索。

索引的副作用 ：
创建另一个索引 idx_tot_size ：

sqlite> CREATE INDEX idx_tot_size
   ...> ON Package_Stats (tot_size);
sqlite> .width 20 -5 -10 -8 -8 -8
sqlite> SELECT * FROM Package_Stats
   ...> WHERE tot_size > 100000000;

查询结果会按 tot_size 升序排列。

删除索引 ：

sqlite> DROP INDEX idx_package_name;
sqlite> DROP INDEX idx_tot_size;

8. 触发器和存储过程

SQL 允许将 SQL 代码存储在数据库中，除了视图，还有存储过程和触发器两种方式。
- 存储过程 ：
存储过程允许将一组 SQL 语句存储起来，作为一个子程序供其他 SQL 程序使用。创建存储过程的语法如下：

CREATE PROCEDURE procudure_name
AS
    [block of SQL code];

例如：

CREATE PROCEDURE list_pkg_files @package VARCHAR(60)
AS
    SELECT package_name, file
      FROM Package_Files
     WHERE package_name = @package;

调用存储过程：

EXEC list_package_files @package = 'bash';

但 SQLite 不支持存储过程。

触发器 ：
触发器是在某些事件发生且满足指定条件时自动调用的存储代码块，通常用于执行某些维护任务。
创建 trigger_demo.sql 文件：

/*
    trigger_demo.sql
    Trigger demo where we create a "trash can" for the
    Package_Files table and set a trigger to copy rows
    to the PF_Backup table just before they are deleted
    from Package_Files.
*/
-- Create backup table with the same schema as Package_Files
CREATE TABLE IF NOT EXISTS PF_Backup (
    package_name VARCHAR(60),
    file         VARCHAR(120),
    size_bytes   INTEGER
);
-- Define trigger to copy rows into PF_Backup as they are
-- deleted from Package_Files
CREATE TRIGGER backup_row_before_delete
BEFORE DELETE ON Package_Files
BEGIN
    INSERT INTO PF_Backup
    VALUES (OLD.package_name, OLD.file, OLD.size_bytes);
END;

通过以上操作，我们可以全面掌握 SQL 数据库的各种操作，包括数据查询、更新、删除、表结构修改、连接表、视图、索引以及触发器和存储过程的使用。这些操作可以帮助我们更高效地管理和操作数据库。

SQL 数据库操作全解析

9. 总结与注意事项

在使用 SQL 进行数据库操作时，有一些要点需要我们特别注意，以确保操作的正确性和高效性。

数据更新与删除的谨慎性 ：在使用 UPDATE 和 DELETE 语句时，务必确保 WHERE 子句的条件准确无误。因为如果没有正确的条件限制，这些语句可能会对大量数据甚至整个表造成不可逆的修改。例如，在更新 sqlite3 包文件大小时，我们明确指定了 package_name = 'sqlite3' 的条件，避免影响其他包的文件数据。
索引的合理使用 ：虽然索引可以显著提高查询性能，但创建过多的索引会增加数据库的维护开销。每次插入、删除或更新行时，数据库都需要更新相应的索引。因此，应根据实际的查询需求，有针对性地在经常用于查询条件的列上创建索引。例如，在 Package_Stats 表中，如果经常根据 package_name 进行查询，那么为该列创建索引是有意义的。
存储过程和触发器的适用性 ：存储过程和触发器可以帮助我们封装复杂的业务逻辑，但不同的数据库系统对它们的支持程度不同。如 SQLite 不支持存储过程，在选择使用这些功能时，需要考虑数据库的兼容性。同时，触发器的使用应该谨慎，因为它们会在特定事件发生时自动执行，可能会对数据库的性能和数据一致性产生影响。

10. 实际应用案例分析

为了更好地理解 SQL 数据库操作的实际应用，我们来看一个具体的案例。假设我们正在开发一个软件包管理系统，需要对软件包的信息进行管理和查询。

需求分析 ：系统需要存储软件包的名称、描述、包含的文件数量、文件总大小等信息，同时能够根据软件包名称进行快速查询和更新操作。
数据库设计 ：我们可以创建两个表， Package_Descriptions 表用于存储软件包的基本信息， Package_Files 表用于存储每个软件包包含的文件信息。为了提高查询性能，我们可以在 Package_Descriptions 表的 package_name 列和 Package_Files 表的 package_name 列上创建索引。

-- 创建 Package_Descriptions 表
CREATE TABLE Package_Descriptions (
    package_name VARCHAR(60),
    description  VARCHAR(120),
    files        INTEGER,
    size         INTEGER
);

-- 创建 Package_Files 表
CREATE TABLE Package_Files (
    package_name VARCHAR(60),
    file         VARCHAR(120),
    size_bytes   INTEGER
);

-- 在 Package_Descriptions 表的 package_name 列上创建索引
CREATE INDEX idx_package_name_desc ON Package_Descriptions (package_name);

-- 在 Package_Files 表的 package_name 列上创建索引
CREATE INDEX idx_package_name_files ON Package_Files (package_name);

数据操作 ：当有新的软件包添加到系统中时，我们需要在 Package_Descriptions 表中插入软件包的基本信息，并在 Package_Files 表中插入该软件包包含的文件信息。同时，使用 UPDATE 语句更新 Package_Descriptions 表中的 files 和 size 列。

-- 插入软件包基本信息
INSERT INTO Package_Descriptions (package_name, description) VALUES ('new_package', 'This is a new software package');

-- 插入软件包文件信息
INSERT INTO Package_Files (package_name, file, size_bytes) VALUES ('new_package', '/usr/bin/new_app', 1024);
INSERT INTO Package_Files (package_name, file, size_bytes) VALUES ('new_package', '/usr/share/doc/new_app/copyright', 512);

-- 更新 Package_Descriptions 表中的 files 和 size 列
UPDATE Package_Descriptions
SET files = (SELECT COUNT(file) FROM Package_Files WHERE Package_Files.package_name = Package_Descriptions.package_name),
    size = (SELECT SUM(size_bytes) FROM Package_Files WHERE Package_Files.package_name = Package_Descriptions.package_name)
WHERE package_name = 'new_package';

查询操作 ：当用户需要查询某个软件包的详细信息时，我们可以使用内连接查询 Package_Descriptions 表和 Package_Files 表，获取所需的信息。

-- 查询 new_package 的详细信息
SELECT Package_Descriptions.package_name, description, files, size
FROM Package_Descriptions
INNER JOIN Package_Files
ON Package_Descriptions.package_name = Package_Files.package_name
WHERE Package_Descriptions.package_name = 'new_package';

通过这个实际案例，我们可以看到 SQL 数据库操作在软件包管理系统中的具体应用，包括数据库设计、数据插入、更新和查询等操作。这些操作可以帮助我们构建一个高效、稳定的软件包管理系统。

11. 未来发展趋势

随着信息技术的不断发展，SQL 数据库也在不断演进和完善。以下是一些 SQL 数据库未来可能的发展趋势：

与大数据和云计算的融合 ：随着大数据时代的到来，数据量呈现爆炸式增长。SQL 数据库将与大数据技术（如 Hadoop、Spark 等）和云计算平台（如 Amazon RDS、阿里云 RDS 等）更紧密地结合，以处理大规模的数据存储和分析需求。
智能化和自动化 ：未来的 SQL 数据库可能会具备更多的智能化和自动化功能。例如，自动优化查询计划、自动调整索引策略、自动处理数据备份和恢复等，从而降低数据库管理员的工作负担。
支持更多的数据类型和应用场景 ：随着新兴技术的发展，如物联网、人工智能等，SQL 数据库需要支持更多的数据类型（如传感器数据、图像数据、文本数据等）和应用场景（如实时数据分析、机器学习等）。

12. 学习建议

对于想要深入学习 SQL 数据库操作的读者，以下是一些学习建议：

理论与实践相结合 ：学习 SQL 不仅要掌握基本的语法和概念，还需要通过大量的实践来巩固所学知识。可以使用一些开源的数据库管理系统（如 SQLite、MySQL 等）进行实践操作，尝试不同的查询和操作场景。
阅读优秀的书籍和文档 ：有许多优秀的 SQL 相关书籍和文档可供学习参考。可以选择一些经典的教材和在线文档，深入学习 SQL 的高级特性和应用技巧。
参与开源项目和社区 ：参与开源的数据库项目和相关社区，可以与其他开发者交流经验和学习心得，了解最新的技术动态和发展趋势。同时，也可以通过贡献代码来提升自己的技术水平。

通过不断学习和实践，我们可以逐渐掌握 SQL 数据库操作的精髓，成为一名优秀的数据库开发者。

13. 总结

本文全面介绍了 SQL 数据库的各种操作，包括数据查询、更新、删除、表结构修改、连接表、视图、索引以及触发器和存储过程的使用。通过详细的代码示例和实际应用案例，我们可以看到这些操作在实际数据库管理中的重要性和实用性。同时，我们也讨论了在使用 SQL 数据库时需要注意的事项以及未来的发展趋势。希望本文能够帮助读者更好地理解和掌握 SQL 数据库操作，提高数据库管理和开发的能力。

以下是一个简单的 mermaid 流程图，展示了 SQL 数据库操作的基本流程：

graph LR
    A[数据库连接] --> B[数据查询]
    A --> C[数据更新]
    A --> D[数据删除]
    A --> E[表结构修改]
    B --> F[结果展示]
    C --> G[确认更新结果]
    D --> H[确认删除结果]
    E --> I[确认结构修改结果]

这个流程图清晰地展示了从数据库连接开始，到进行不同类型的操作（查询、更新、删除、表结构修改），最后确认操作结果的整个过程。通过这个流程图，我们可以更直观地理解 SQL 数据库操作的基本流程和步骤。