Press "Enter" to skip to content

5个步骤入门SQL

Published September 16, 2023 by 四海吧

5个步骤入门SQL 四海 第1张

结构化查询语言介绍

 

在关系数据库中管理和操作数据时，结构化查询语言（SQL）是最重要的工具。SQL是一种重要的领域特定语言，是数据库管理的基石，并提供了与数据库交互的标准化方式。随着数据驱动决策和创新的推动力，SQL仍然是数据分析师、开发人员和数据科学家需重点关注的关键技术。

SQL最初由IBM在1970年代开发，并于1980年代末被ANSI和ISO标准化。从小型企业到大学到大型企业，各种组织都依赖SQL数据库（如MySQL、SQL Server和PostgreSQL）来处理大规模数据。随着数据驱动行业的扩展，SQL的重要性也在不断增长。它的普遍应用使得它成为各种专业人员在数据领域及其他领域中都必备的技能。

SQL允许用户执行各种与数据相关的任务，包括：

  • 查询数据
  • 插入新记录
  • 更新现有记录
  • 删除记录
  • 创建和修改表格

本教程将提供SQL的逐步指南，重点是通过广泛的实例进行入门学习。

 

步骤1：设置SQL环境

 

选择SQL数据库管理系统（DBMS）

 

在进行SQL查询之前，您需要选择一个适合项目需求的数据库管理系统（DBMS）。DBMS作为您的SQL活动的支撑，提供不同的功能、性能优化和价格模型。您选择的DBMS可能会对您与数据的交互方式产生重大影响。

  • MySQL：开源，被广泛采用，由Facebook和Google使用。适用于各种应用，从小型项目到企业级应用。
  • PostgreSQL：开源，功能强大，被Apple使用。以其性能和符合标准的特点而闻名。
  • SQL Server Express：微软的入门级选择。适用于对可伸缩性要求有限的小型VoAGI应用。
  • SQLite：轻量级、无服务器、独立。适用于移动应用和小型项目。

 

MySQL安装指南

 

为了本教程的目的，我们将重点介绍MySQL，因为它被广泛使用且具有全面的功能集。安装MySQL的过程很简单：

  1. 访问MySQL的网站，下载适合您操作系统的安装程序。
  2. 运行安装程序，按照屏幕上的指示操作。
  3. 在安装过程中，您将被提示创建一个根账户。请确保记住或安全保存根密码。
  4. 安装完成后，您可以通过打开终端并输入mysql -u root -p来访问MySQL shell。您将被要求输入根密码。
  5. 成功登录后，您将看到MySQL提示，表示您的MySQL服务器已经启动。

 

设置SQL集成开发环境（IDE）

 

集成开发环境（IDE）可以通过提供自动完成、语法高亮和数据库可视化等功能，大大提升您的SQL编码体验。对于更复杂的任务和更大的项目，强烈推荐使用IDE，但对于运行SQL查询来说并不是必需的。

  • DBeaver：开源，支持广泛的DBMS，包括MySQL、PostgreSQL、SQLite和SQL Server。
  • MySQL Workbench：由Oracle开发，是MySQL的官方IDE，提供专为MySQL定制的全面工具。

下载并安装您选择的IDE后，您需要将其连接到MySQL服务器。这通常涉及指定服务器的IP地址（如果服务器在您的机器上，则为localhost），端口号（MySQL通常为3306），以及经授权的数据库用户的凭据。

测试你的配置

让我们确保一切都正常工作。您可以通过运行一个简单的SQL查询来显示所有现有的数据库来实现这一点：

SHOW DATABASES;

如果这个查询返回一个数据库列表，并且没有错误，那么恭喜您！您的SQL环境已经成功设置好，您可以开始进行SQL编程了。

第2步：基本的SQL语法和命令

创建数据库和表

在添加或操作数据之前，您至少需要一个数据库和一个表。创建数据库和表的方法如下：

CREATE DATABASE sql_tutorial;
USE sql_tutorial;
CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  name VARCHAR(50),
  email VARCHAR(50)
);

操作数据

现在您已经准备好操作数据了。让我们来看一下基本的CRUD操作：

  • 插入：INSERT INTO customers (name, email) VALUES ('John Doe', 'john@email.com');
  • 查询：SELECT * FROM customers;
  • 更新：UPDATE customers SET email = 'john@newemail.com' WHERE id = 1;
  • 删除：DELETE FROM customers WHERE id = 1;

筛选和排序

在SQL中，筛选是使用条件有选择地从表中检索行的过程，通常使用WHERE子句。排序是以特定的顺序排列检索到的数据，通常使用ORDER BY子句。在SQL中，分页将结果集分成较小的块，每页显示有限数量的行。

  • 筛选：SELECT * FROM customers WHERE name = 'John Doe';
  • 排序：SELECT * FROM customers ORDER BY name ASC;
  • 分页：SELECT * FROM customers LIMIT 10 OFFSET 20;

数据类型和约束

了解数据类型和约束对于定义表的结构至关重要。数据类型指定列可以保存的数据类型，比如整数、文本或日期。约束强制执行限制以确保数据的完整性。

  • 整数类型：INT、SMALLINT、TINYINT等。用于存储整数。
  • 十进制类型：FLOAT、DOUBLE、DECIMAL。适用于存储带有小数位的数字。
  • 字符类型：CHAR、VARCHAR、TEXT。用于文本数据。
  • 日期和时间：DATE、TIME、DATETIME、TIMESTAMP。设计用于存储日期和时间信息。
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    email VARCHAR(50) UNIQUE,
    salary FLOAT CHECK (salary > 0)
  );

在上面的例子中，NOT NULL约束确保列不能有NULL值。UNIQUE约束保证列中的所有值都是唯一的。CHECK约束验证工资必须大于零。

第3步：更高级的SQL概念

连接表

连接用于基于它们之间的相关列组合两个或多个表中的行。当您想要检索分布在多个表中的数据时，它们是必不可少的。了解连接对于复杂的SQL查询至关重要。

  • 内连接： SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
  • 左连接： SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
  • 右连接： SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;

连接操作可以很复杂，但在需要从多个表中提取数据时非常强大。让我们通过一个详细的例子来说明不同类型的连接操作是如何工作的。

考虑两个表： Employees 和 Departments。

-- Employees 表
CREATE TABLE Employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT
);

INSERT INTO Employees (id, name, department_id) VALUES
(1, 'Winifred', 1),
(2, 'Francisco', 2),
(3, 'Englebert', NULL);

-- Departments 表
CREATE TABLE Departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO Departments (id, name) VALUES
(1, '研发部'),
(2, '工程部'),
(3, '销售部');

 

让我们来探索不同类型的连接操作：

-- 内连接
-- 返回两个表中有匹配值的记录

SELECT E.name, D.name 
FROM Employees E
INNER JOIN Departments D ON E.department_id = D.id;

-- 左连接 (或左外连接)
-- 返回左表的所有记录，
-- 以及与右表匹配的记录

SELECT E.name, D.name 
FROM Employees E
LEFT JOIN Departments D ON E.department_id = D.id;

-- 右连接 (或右外连接)
-- 返回右表的所有记录
-- 以及与左表匹配的记录

SELECT E.name, D.name 
FROM Employees E
RIGHT JOIN Departments D ON E.department_id = D.id;

 

在上面的例子中，内连接仅返回两个表中有匹配的行。左连接返回左表的所有行，并返回与右表匹配的行，如果没有匹配则用 NULL 填充。右连接则相反，返回右表的所有行，并返回与左表匹配的行。

 

分组和聚合

 

聚合函数对一组值进行计算，并返回一个单一的值。聚合通常与 GROUP BY 子句一起使用，将数据分段为不同的类别，并对每个组进行计算。

  • 计数： SELECT customer_id, COUNT(id) AS total_orders FROM orders GROUP BY customer_id;
  • 求和： SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id;
  • 过滤分组： SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id HAVING total_spent > 100;

 

子查询和嵌套查询

 

子查询允许在查询内部执行查询，提供了一种获取将在主查询中用作条件来进一步限制检索的数据的方式。

SELECT *
  FROM customers
  WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE orderdate > '2023-01-01'
  );

 

事务

 

事务是一系列作为单个工作单元执行的 SQL 操作。在多用户系统中，它们对于维护数据库操作的完整性非常重要。事务遵循 ACID 原则：原子性、一致性、隔离性和持久性。

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
  COMMIT;

 

在上面的例子中，两个 UPDATE 语句都包含在一个事务中。它们要么都成功执行，要么如果出现错误，则都不执行，确保数据的完整性。

 

步骤 4：优化和性能调优

 

理解查询性能

 

查询性能对于维护响应式数据库系统至关重要。低效的查询可能导致延迟，影响用户体验。以下是一些关键概念：

  • 执行计划：这些计划提供了一个查询将如何执行的路线图，允许进行分析和优化。
  • 瓶颈：识别查询中的慢部分可以指导优化工作。像SQL Server Profiler这样的工具可以协助这个过程。

 

索引策略

 

索引是增强数据检索速度的数据结构。它们在大型数据库中非常重要。以下是它们的工作原理：

  • 单列索引：在单个列上的索引，通常用于WHERE子句；CREATE INDEX idx_name ON customers (name);
  • 组合索引：在多个列上的索引，在查询中根据多个字段进行筛选时使用；CREATE INDEX idx_name_age ON customers (name, age);
  • 何时使用索引：索引可以提高读取速度，但可能会降低插入和更新的速度。需要仔细考虑这些因素的平衡。

 

优化连接和子查询

 

连接和子查询可能会消耗大量资源。优化策略包括：

  • 使用索引：在连接字段上应用索引可以提高连接性能。
  • 减少复杂性：最小化连接的表数和选取的行数。
SELECT customers.name, COUNT(orders.id) AS total_orders
  FROM customers
  JOIN orders ON customers.id = orders.customer_id
  GROUP BY customers.name
  HAVING orders > 2;

 

数据库规范化和去规范化

 

数据库设计在性能方面起着重要作用：

  • 规范化：通过将数据组织成相关表来减少冗余。这可能使查询更复杂，但确保数据一致性。
  • 去规范化：合并表以提高读取性能，但可能导致数据不一致。在读取速度是优先考虑的情况下使用。

 

监控和分析工具

 

利用工具监控性能确保数据库运行顺畅：

  • MySQL的性能模式：提供有关查询执行和性能的见解。
  • SQL Server Profiler：允许跟踪和捕获SQL Server事件，有助于分析性能。

 

编写高效SQL的最佳实践

 

遵循最佳实践可以使SQL代码更易于维护和高效：

  • 避免使用SELECT *：只选择所需的列以减少负载。
  • 减少通配符：在LIKE查询中谨慎使用通配符。
  • 使用EXISTS而不是COUNT：在检查存在性时，EXISTS更高效。
SELECT id, name 
FROM customers 
WHERE EXISTS (
    SELECT 1 
    FROM orders 
    WHERE customer_id = customers.id
);

 

数据库维护

 

定期维护确保最佳性能：

  • 更新统计信息：帮助数据库引擎做出优化决策。
  • 重建索引：随着时间的推移，索引会变得碎片化。定期重建可以改善性能。
  • 备份：定期备份对于数据完整性和恢复至关重要。

 

第五步：性能和安全最佳实践

 

性能最佳实践

 

优化SQL查询和数据库的性能对于保持响应和高效的系统至关重要。以下是一些性能最佳实践：

  • 明智使用索引：索引可以加快数据检索，但会降低插入、更新和删除等数据修改操作的速度。
  • 限制结果：使用LIMIT子句仅检索所需数据。
  • 优化连接：始终在索引或主键列上连接表。
  • 分析查询计划：了解查询执行计划可以帮助您优化查询。

 

安全最佳实践

 

在处理数据库时，安全性至关重要，因为它们通常包含敏感信息。以下是增强SQL安全性的一些最佳实践：

  • 数据加密：在存储敏感数据之前始终进行加密。
  • 用户权限：授予用户执行任务所需的最低权限。
  • 防止SQL注入：使用参数化查询以防止SQL注入攻击。
  • 定期审计：定期进行安全审计以识别漏洞。

 

结合性能和安全

 

在性能和安全之间取得正确的平衡通常是具有挑战性但必要的。例如，虽然索引可以加快数据检索，但也可能使敏感数据更易访问。因此，始终考虑性能优化策略的安全性影响。

 

示例：安全高效的查询

 

-- 使用参数化查询同时优化
-- 性能和防止SQL注入

PREPARE secureQuery FROM 'SELECT * FROM users WHERE age > ? AND age < ?';
SET @min_age = 18, @max_age = 35;
EXECUTE secureQuery USING @min_age, @max_age;

 

此示例使用了参数化查询，不仅可以防止SQL注入，还可以让MySQL缓存查询，提高性能。

 

前进

 

本入门指南介绍了SQL的基本概念和常见的实际应用。从入门到掌握复杂查询，本指南应该为您提供了通过详细示例和实用方法来管理数据的技能。随着数据继续塑造我们的世界，掌握SQL为您打开了各种领域的大门，包括数据分析、机器学习和软件开发。

在您进一步学习的过程中，考虑通过其他资源扩展您的SQL技能。像w3schools SQL教程和SQLBolt上的SQL实践练习等网站提供了额外的学习材料和练习。此外，HackerRank的SQL问题提供了目标导向的查询练习。无论您是在构建复杂的数据分析平台还是开发下一代Web应用程序，SQL是您肯定会经常使用的技能。请记住，成为SQL专家的旅程是漫长的，通过持续的实践和学习可以丰富这个旅程。

    Matthew Mayo（@mattmayo13）拥有计算机科学硕士学位和数据挖掘研究生文凭。作为VoAGI的主编，Matthew致力于使复杂的数据科学概念易于理解。他的专业兴趣包括自然语言处理、机器学习算法和探索新兴的人工智能。他的使命是在数据科学社区中普及知识。Matthew从6岁开始编程。  

Published in 四海

Leave a Reply

Your email address will not be published. Required fields are marked *