SQL语句实操练习：涵盖DQL、DML与DDL

本文还有配套的精品资源，点击获取

简介：SQL作为关系型数据库的标准操作语言，包含数据查询（DQL）、数据操纵（DML）和数据定义（DDL）等多种操作类型。本练习题集通过提供不同难度的SQL语句，旨在帮助学习者巩固和提升在单表查询、联接、子查询和聚合函数等方面的应用能力。掌握SQL对IT专业人员至关重要，因为它在数据分析、软件开发和系统管理等多个领域都具有广泛的应用。

1. 数据查询语言（DQL）基础知识和应用

在本章中，我们将深入了解数据查询语言（DQL），它是一种用于从数据库中检索数据的SQL子语言。DQL的核心是SELECT语句，通过它，用户可以灵活地定义所要检索的数据字段、数据来源的表以及过滤条件。

1.1 DQL的基本结构

首先，我们要熟悉SELECT语句的基本结构，包括 SELECT 关键字、要查询的字段列表、 FROM 子句指定的表名、以及可选的 WHERE 子句来限定查询条件。例如：

SELECT column1, column2 FROM table_name WHERE condition;

此代码块的含义是从 table_name 表中检索 column1 和 column2 两个字段，但仅包括那些满足 condition 条件的记录。

1.2 检索结果的排序

接下来，DQL允许我们对结果进行排序。使用 ORDER BY 子句可以按照一个或多个列的值对结果进行排序。例如，按 column1 升序排序：

SELECT * FROM table_name ORDER BY column1 ASC;

此语句会返回 table_name 表中的所有记录，结果将按照 column1 的值进行升序排列。如果需要降序，可以将 ASC 替换为 DESC 。

在本章的后续部分，我们将详细讨论如何利用DQL的各种功能来处理更复杂的查询场景，包括使用聚合函数、子查询以及连接多个表来扩展我们的查询能力。掌握这些基础知识对于任何需要与数据库交互的专业人士来说，都是至关重要的。

2. 数据操纵语言（DML）基础知识和应用

2.1 插入、更新与删除数据

2.1.1 INSERT语句的使用和场景

INSERT 语句是SQL中用于向数据库表中插入新数据行的基本命令。在使用 INSERT 语句时，我们首先需要确定要插入数据的表结构，并且必须为表中的每一个非空字段提供值。如果表具有自增的主键字段，那么在插入数据时可以省略该字段，数据库将自动为该字段生成值。

以下是一个简单的 INSERT 语句的使用示例：

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'jdoe@example.com');

在这个例子中，我们向 employees 表中插入了一条新的记录。 employees 表必须存在，且拥有 first_name 、 last_name 和 email 这三个字段。我们只插入了这三列的数据，数据库会自动为其他字段赋予默认值（如果有的话）或者 NULL 值（如果没有默认值的话）。

参数说明：

• INTO 关键字后面跟着的是目标表名，即要向其中插入数据的表。
• 表名后的括号里列出了需要提供值的字段名。
• VALUES 关键字后面跟着的是对应字段的数据，数据必须按顺序排列，并且数据类型必须匹配。

在实际应用中， INSERT 语句可以分为多种场景：

• 插入单条数据 ：如上述示例，每次向表中插入一条数据记录。
• 插入多条数据 ：在 VALUES 后面列出多组数据，一次向表中插入多条记录。
• 从其他表中选择数据插入 ：使用 SELECT 语句的查询结果来填充 INSERT 语句，可以将数据从一个表导入到另一个表。

使用 INSERT 时需要注意避免主键或唯一性约束冲突，并确保数据类型正确匹配。

2.1.2 UPDATE语句的技巧与限制

UPDATE 语句用于修改表中的现有记录。该语句非常强大，但也需要谨慎使用，因为不当的使用可能会导致数据错误或丢失。基本的 UPDATE 语句格式如下：

UPDATE employees
SET email = 'newemail@example.com', salary = salary * 1.1
WHERE id = 5;

在这个例子中，我们更新了 employees 表中 id 为5的员工的电子邮件和薪水。 SET 子句用于指定要更改的字段和新的值，而 WHERE 子句用于指定哪些记录将被更新。

参数说明：

• SET 后面跟着的是一个或多个字段及其新值，用逗号分隔。每个字段可以进行计算或赋值。
• WHERE 子句是可选的，如果没有指定，则更新所有记录，这通常不是一个好主意。

使用 UPDATE 时需要注意以下几点：

• 备份数据 ：在进行大量更新操作前，应该备份相关数据。
• 使用 WHERE 子句 ：确保只有目标数据被更新，避免使用无条件的 UPDATE 。
• 避免锁定问题 ：在高并发环境下，不恰当的 UPDATE 可能导致数据锁定，影响系统性能。

2.1.3 DELETE语句的注意事项和替代方案

DELETE 语句用于删除表中的现有记录。在使用 DELETE 时，务必小心，因为一旦执行，删除的数据无法恢复。

基本的 DELETE 语句格式如下：

DELETE FROM employees
WHERE id = 5;

在这个例子中，我们删除了 employees 表中 id 为5的记录。

参数说明：

• FROM 关键字后跟的是要删除数据的表名。
• WHERE 子句用于指定条件，仅满足条件的记录会被删除。

当使用 DELETE 时需要注意的事项：

• 备份数据 ：在执行删除操作前，一定要备份数据。
• 使用 WHERE 子句 ：仅删除需要删除的数据，避免使用无条件的 DELETE 。
• 考虑替代方案 ：有时使用 UPDATE 代替 DELETE 可能更安全，例如，可以将字段值设置为 NULL 而不是直接删除记录。

除了直接使用 DELETE 语句，有时还可以使用 TRUNCATE 语句来清空整个表的内容，这在性能上更高效，但是不可逆，因此使用时需要谨慎。

在实际应用中，除了这些基础的DML操作，事务控制和并发管理对于保证数据的一致性和完整性也至关重要。接下来我们将详细讨论这些高级主题。

3. 数据定义语言（DDL）基础知识和应用

3.1 创建、修改与删除数据库结构

在数据库的生命周期中，创建、修改和删除数据库结构是日常管理工作的重要组成部分。通过使用DDL，数据库管理员和开发人员可以按照需求调整数据库的架构。理解并熟练使用DDL相关语句是进行高效数据库管理的基础。

3.1.1 CREATE语句的结构化创建

CREATE 语句是数据定义语言中最基本的操作之一。它用于创建数据库、表、索引等数据库对象。在创建数据库对象时，需要考虑命名规则、数据类型、约束、默认值等多方面因素，以确保数据的结构清晰、逻辑合理。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    HireDate DATE,
    Salary DECIMAL(10,2)
);

在上述示例中， CREATE TABLE 语句用于创建一个名为 Employees 的表，其中包含了员工的标识符、名字、姓氏、出生日期、雇佣日期和薪水。每个字段的定义中包含了字段名称和数据类型，以及用于 EmployeeID 字段的主键约束。

3.1.2 ALTER语句的灵活应用

当数据库架构需要调整时， ALTER 语句提供了修改现有数据库对象的能力。这包括添加、修改、删除字段，添加或删除约束，重命名对象等。

ALTER TABLE Employees
ADD Email VARCHAR(100);

此代码将 Email 字段添加到 Employees 表中。 ALTER 语句是灵活的，可以通过不同的子句实现多种结构变更。

3.1.3 DROP语句与级联删除的影响

DROP 语句用于删除数据库中的整个对象，例如表或索引。在执行 DROP 操作时需要注意其级联删除的影响，因为 DROP 会删除对象的所有相关数据和结构。

DROP TABLE Employees;

上述命令将删除 Employees 表以及表中存储的所有数据。在某些数据库管理系统中，可以通过添加 CASCADE 关键字来实现级联删除，这将同时删除所有依赖于该表的其他对象，如视图和外键约束。

3.2 数据类型和约束的深入理解

3.2.1 主要数据类型的特性与选择

不同的数据类型存储不同种类的数据。选择合适的数据类型对于数据库性能和数据完整性的维护至关重要。常见的数据类型包括数值型、字符型、日期时间型和二进制型。

CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0),
    ProductDate DATE,
    ProductImage BLOB
);

在这个例子中， INT 、 VARCHAR 、 DECIMAL 、 DATE 和 BLOB 分别用于存储整数、字符串、十进制数、日期和二进制文件。 AUTO_INCREMENT 和 NOT NULL 是针对特定字段的约束。

3.2.2 约束的作用及其对数据完整性的保证

约束是数据库中用以保证数据完整性的规则。它们用于限制表中数据的类型和范围。常见的约束包括 PRIMARY KEY 、 FOREIGN KEY 、 UNIQUE 、 CHECK 和 NOT NULL 等。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    CreditLimit DECIMAL(10,2) CHECK (CreditLimit > 0)
);

在此表创建语句中， CustomerID 字段被定义为主键，意味着每个客户ID必须是唯一的且非空。 CustomerName 字段不允许为空， Email 字段具有唯一性约束，而 CreditLimit 字段上应用了检查约束，确保信贷额度必须大于零。

深入理解和正确应用数据类型和约束是确保数据库架构稳定性和数据安全性的关键。下一节将探讨单表查询的深入实践，包括条件查询、排序技巧和分组聚合函数的应用，这些内容是进一步理解数据库管理的基石。

4. SQL语句实操练习题集

4.1 基础题型的演练

4.1.1 简单查询语句的编写

编写基本的SQL查询语句是学习SQL的起点。简单查询语句通常涉及从单一表中检索数据。一个常见的查询操作是使用SELECT语句来选择特定的列。例如，如果我们有一个名为 employees 的表，要查询所有员工的姓名和年龄，我们可以使用如下语句：

SELECT name, age FROM employees;

在这个查询中， SELECT 指明了我们想要检索的数据列（在这个例子中是 name 和 age ）， FROM 指明了数据来源表（ employees ）。我们还可以利用 WHERE 子句来限定结果集。比如，如果我们只想要查询年龄大于30岁的员工：

SELECT name, age FROM employees WHERE age > 30;

以上两个语句均非常简单明了，但是它们奠定了基础，可以进一步展开成更复杂的查询。

4.1.2 基本的DML操作练习

数据操纵语言（DML）允许我们对数据库中的数据进行增加、修改和删除的操作。以下是DML操作的基础实践。

使用 INSERT 语句来向表中添加新的记录：

INSERT INTO employees (name, age, department)
VALUES ('John Doe', 32, 'Marketing');

使用 UPDATE 语句来更新表中的现有记录：

UPDATE employees
SET department = 'Sales'
WHERE name = 'John Doe';

使用 DELETE 语句来删除表中的记录：

DELETE FROM employees WHERE name = 'John Doe';

在练习时需要注意的是，应当小心使用 DELETE 和 UPDATE 操作，因为它们会永久性地更改数据。在对生产数据库执行之前，最好在测试环境中进行实验，并确保有适当的备份。

4.1.3 简单的DDL操作实例

数据定义语言（DDL）包括用于创建、修改和删除数据库结构的SQL语句。以下是一些DDL的基础操作实例。

创建一个新表：

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

修改表结构，添加一列：

ALTER TABLE employees ADD COLUMN email VARCHAR(255);

删除一个表：

DROP TABLE new_table;

DDL操作会改变数据库的结构，因此在执行之前应谨慎考虑，尤其是在生产环境中。在实际操作前进行备份和测试是良好的实践。

4.2 高级题型的挑战

4.2.1 复杂查询的构建技巧

当基础查询已掌握，我们进入复杂查询的构建。复杂查询通常涉及多表连接（JOINs）、子查询、分组（GROUP BY）和聚合函数（如COUNT, SUM, AVG）等。以下是一个涉及多表连接的查询示例：

SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

在这个查询中， JOIN 子句用于连接两个表（ employees 和 departments ），而 ON 子句指明了连接条件。理解连接类型（如INNER JOIN, LEFT JOIN, RIGHT JOIN等）对于构建正确的查询至关重要。

4.2.2 多表关联查询与视图操作

多表关联查询是SQL中较为高级的话题。这种查询可能涉及三个或更多表的连接，对于数据分析师和数据库管理员来说是日常任务。下面是一个示例：

SELECT orders.order_id, customers.customer_name, products.product_name, orders.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

在实践中，视图（VIEW）被用来简化复杂的查询。视图可以认为是存储在数据库中的一个查询语句。创建视图可以使用以下语句：

CREATE VIEW customer_orders AS
SELECT customers.customer_name, orders.order_id, orders.order_date
FROM customers
JOIN orders ON customers.id = orders.customer_id;

一旦视图被创建，它可以像任何其他表一样被查询：

SELECT * FROM customer_orders WHERE order_date >= '2023-01-01';

4.2.3 索引优化与性能分析

索引是提高查询性能的关键工具，但创建和管理索引需要对数据库有深入的理解。例如，在一个非常大的 orders 表上，如果频繁根据 order_id 进行查询，我们可能需要一个索引：

CREATE INDEX idx_order_id ON orders (order_id);

性能分析包括了解查询执行计划和使用诸如EXPLAIN的命令：

EXPLAIN SELECT * FROM employees WHERE age > 30;

通过查看查询的执行计划，数据库管理员可以决定是否需要添加索引，或者调整现有索引以提高性能。

Column	Type	Index	Description
id	INT	PRIMARY	主键索引
name	VARCHAR(255)		员工姓名，可以创建索引以优化查询
age	INT		员工年龄，根据查询需求决定是否创建索引
department	VARCHAR(255)		部门名称，可以创建索引以优化查询

请注意，在实际操作中，应考虑多种因素来决定是否为一列创建索引。这些包括查询模式、数据变化频率以及更新表时的性能影响。

5. 单表查询到复杂查询的练习

5.1 单表查询的深入实践

5.1.1 条件查询与排序技巧

在进行单表查询时，我们经常需要根据特定条件筛选数据。SQL 提供了 WHERE 子句来实现这一功能。 WHERE 子句中的条件可以包括逻辑运算符如 AND , OR , NOT ，以及比较运算符如 = , <> , > , < , >= , <= 。

SELECT * FROM employees WHERE salary > 50000 AND department_id = 10;

在上面的查询中，我们选择了 employees 表中薪水超过 50000 且属于部门 10 的所有员工记录。使用 AND 运算符联合了两个条件。

排序查询是另一种常用的功能，通过 ORDER BY 子句实现。默认情况下，使用 ASC 关键字进行升序排序，若要进行降序排序，则使用 DESC 关键字。

SELECT * FROM employees ORDER BY salary DESC, hire_date ASC;

这个例子首先按薪水降序排序，如果薪水相同，则按雇佣日期升序排序。这说明 ORDER BY 可以使用多个字段进行复合排序。

5.1.2 分组与聚合函数的应用

分组查询使用 GROUP BY 子句，并且经常与聚合函数一起使用，如 COUNT() , SUM() , AVG() , MIN() , MAX() 等，来对每个分组的数据进行统计。

SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

该查询会返回每个部门的员工数量和平均薪水。注意， GROUP BY 子句中列出的每个非聚合列都必须在 SELECT 列表中出现。

5.1.3 子查询的运用和优化

子查询是在另一个 SQL 语句的 WHERE 或 HAVING 子句中嵌套的查询。它允许你在查询中使用其他查询的结果。

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这个例子中，子查询计算了 employees 表中所有员工的平均薪水，并将其与外层查询中每个员工的薪水进行比较。

在执行子查询时，应考虑其性能影响。对于优化，可以考虑使用 JOIN 代替某些子查询，或者使用派生表（ FROM 子句中的子查询），特别是在处理大量数据时。

5.2 复杂查询的实战演练

5.2.1 多表连接查询的方法

多表连接查询是通过 JOIN 关键字，将多个表连接起来进行查询。在使用 JOIN 时，我们需要指定连接的类型和连接条件。

SELECT e.name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

在上述查询中，我们使用了 INNER JOIN 来连接 employees 表和 departments 表，根据部门 ID 关联它们的数据。

除了 INNER JOIN ，还有 LEFT JOIN , RIGHT JOIN , 和 FULL OUTER JOIN 等类型，它们在查询结果上有所差异，特别是当连接的表中的某些行在另一个表中没有匹配时。

5.2.2 子查询与连接查询的结合

在实际的 SQL 查询中，子查询和连接查询常常需要结合起来使用，以解决复杂的查询需求。

SELECT e.name, 
       (SELECT d.department_name FROM departments d WHERE e.department_id = d.department_id) AS department_name,
       e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

这个查询使用了子查询来获取部门名称，并使用了连接查询来过滤出高于部门平均薪水的员工。

需要注意的是，过多的子查询和连接可能会使 SQL 查询变得复杂且效率低下。优化这些查询通常需要重新组织查询逻辑，或者在数据库中建立适当的索引来提高查询性能。

5.2.3 处理复杂数据关系的高级查询

当处理具有复杂关系的数据时，可以使用诸如子查询、连接查询以及聚合函数等多种查询技术的组合。高级查询也可能涉及临时表的创建、视图的使用以及复杂的 GROUP BY 子句，甚至是递归查询。

例如，递归查询通常用于处理树状结构或层次结构的数据。在 SQL:1999 标准中，递归查询可以使用 WITH 子句（公用表表达式CTE）来实现。

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name, h.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;

上述查询构建了一个简单的员工层级结构，其中员工可以根据 manager_id 来确定其上级。

复杂的查询通常需要仔细设计，并且在开发阶段进行充分的测试，以确保结果的正确性和性能上的优化。在多表查询和复杂查询中，仔细分析查询执行计划是性能优化的关键步骤。

6. SQL在数据分析、软件开发和系统管理中的重要性

6.1 SQL在数据分析中的角色

SQL是数据分析领域中不可或缺的工具。它不仅允许数据分析师从关系型数据库中提取数据，还能够帮助他们在数据仓库和大数据分析平台中实现复杂的报告和数据查询。

6.1.1 SQL用于数据提取和报告生成

数据提取是数据分析的第一步，SQL通过各种查询语句帮助数据分析师从数据库中获取所需数据。报告生成则需要通过聚合函数、分组和排序等操作来对数据进行处理。

SELECT
  product_id,
  SUM(quantity) AS total_quantity,
  AVG(price) AS average_price
FROM
  sales
WHERE
  order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY
  product_id
ORDER BY
  total_quantity DESC;

上面的SQL语句提取了某个月份内每个产品的销售总量和平均价格，并按销售量进行排序，生成了一个销售报告。

6.1.2 数据仓库与OLAP中SQL的应用

数据仓库的构建往往涉及对大量数据的汇总和预计算，SQL在这一过程中用于创建视图、物化视图和进行数据聚合。

CREATE MATERIALIZED VIEW sales_report AS
SELECT
  region_id,
  SUM(sales_amount) AS total_sales,
  COUNT(*) AS number_of_orders
FROM
  sales
GROUP BY
  region_id;

该语句创建了一个物化视图，预计算了每个区域的总销售额和订单数，有助于提高数据仓库中报告的查询效率。

6.1.3 大数据环境下SQL的扩展与挑战

随着大数据技术的发展，传统SQL标准在处理PB级别的数据集时面临挑战。许多大数据技术如Hadoop、Spark等扩展了SQL的功能，以支持更高效的大数据分析。

SELECT
  customer_id,
  COUNT(*) AS orders_per_customer,
  AVG(total_order_value) AS average_order_value
FROM
  (SELECT
    customer_id,
    SUM(amount) AS total_order_value
  FROM
    sales
  GROUP BY
    customer_id
  ) subquery
WHERE
  orders_per_customer > 5;

此查询展示了在大数据环境下的SQL扩展，使用子查询和聚合函数计算每个顾客的订单数量和平均订单价值，这对于客户关系管理和个性化营销至关重要。

6.2 SQL在软件开发中的应用

软件开发过程中，数据库操作是不可或缺的。SQL在此过程中扮演了数据库层面的业务逻辑实现和数据持久化的角色。

6.2.1 数据库操作在业务逻辑中的实现

开发者通过编写SQL语句，将业务逻辑中涉及的数据库操作变得具体化，例如用户验证、订单处理、产品库存更新等。

UPDATE users
SET status = 'active'
WHERE email = 'user@example.com' AND activation_code = 'correct_code';

这个SQL语句用于在用户正确激活账户后更新其状态为活跃。

6.2.2 ORM技术与SQL代码生成

对象关系映射（ORM）技术抽象了SQL代码，允许开发者使用编程语言本身操作数据库，例如Python的Django ORM。

user = User.objects.get(email='user@example.com')
user.status = 'active'
user.save()

这段代码是使用Django ORM来更新用户状态的示例，虽然没有直接编写SQL语句，但最终会被转换成相应的SQL执行。

6.2.3 数据库迁移与版本控制的策略

数据库迁移是版本控制中的关键组件，它确保数据库模式的变更与应用代码的更新同步进行。SQL为数据库迁移提供了基础。

# 命令行执行数据库迁移
python manage.py migrate

以上命令行操作是使用Django框架时的数据库迁移命令，用于应用数据库模式的变更。

6.3 SQL在系统管理中的作用

在系统管理领域，SQL用于维护和监控数据库，确保系统运行的稳定性和安全性。

6.3.1 数据库监控与维护的SQL命令

系统管理员使用SQL监控和维护数据库性能，比如查看数据库状态、执行备份等。

SELECT
  name,
  status,
  recovery_model_desc
FROM
  sys.databases
WHERE
  database_id = DB_ID('your_database_name');

这个查询用于检查特定数据库的状态和恢复模式。

6.3.2 审计与安全策略中的SQL应用

SQL用于实现数据库级别的审计和安全策略，如查询日志、设定权限等。

SELECT
  session_id,
  login_name,
  last_request_start_time
FROM
  sys.dm_exec_sessions
WHERE
  last_request_start_time > DATEADD(minute, -5, GETDATE());

这个查询用于检索过去5分钟内活跃的所有数据库会话，是数据库安全审计的一个步骤。

6.3.3 自动化任务与SQL脚本的编写

SQL脚本常被用于编写自动化任务，如定期清理旧数据、执行报告等。

DELETE FROM
  temp_data
WHERE
  created_at < DATEADD(month, -1, GETDATE());

上述SQL语句用于删除一个月前创建的临时数据，是数据维护自动化的一个例子。

本文还有配套的精品资源，点击获取

简介：SQL作为关系型数据库的标准操作语言，包含数据查询（DQL）、数据操纵（DML）和数据定义（DDL）等多种操作类型。本练习题集通过提供不同难度的SQL语句，旨在帮助学习者巩固和提升在单表查询、联接、子查询和聚合函数等方面的应用能力。掌握SQL对IT专业人员至关重要，因为它在数据分析、软件开发和系统管理等多个领域都具有广泛的应用。

本文还有配套的精品资源，点击获取