曾续缘

切换主题

SQL语法结构 ​

SQL语法结构通常包括以下组成部分：

  1. 关键字（Keywords）：SQL语句由关键字组成，这些关键字是SQL语言预定义的词汇，用于执行特定的操作。
  2. 表达式（Expressions）：在SQL中，表达式可以是列名、常量、函数调用、运算符等，它们用于计算或生成数据。
  3. 子句（Clauses）：子句是SQL语句的一部分，用于指定操作的条件或细节。例如，WHERE子句用于指定数据检索的条件，ORDER BY子句用于指定排序的规则。
  4. 注释（Comments）：注释用于在SQL代码中添加说明，它们可以是单行注释（使用--）或多行注释（使用/* ... */）。

SQL分类 ​

SQL根据其功能主要分为四类：数据定义语言（DDL - Data Definition Language）、数据操纵语言（DML - Data Manipulation Language）、数据查询语言（DCL - Data Control Language）和数据控制语言（TCL - Transaction Control Language）。

数据定义语言 ​

用途 ​

DDL主要用于定义数据库的结构。这包括创建、修改和删除数据库对象，如数据库本身、表、视图、索引等。DDL命令通常由数据库管理员或其他有相应权限的用户执行，以确保数据库的结构符合业务需求和技术规范。

关键字 ​

CREATE ​

CREATE关键字用于在数据库中创建新的对象，如数据库、表、索引和视图。

  1. 创建数据库：
sql
CREATE DATABASE SchoolDB;

这条命令会在数据库系统中创建一个名为SchoolDB的新数据库。

  1. 创建表：
sql
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    EnrollDate DATE
);

这里创建了一个名为Students的表，包含五个列：StudentID（主键）、FirstName、LastName、BirthDate和EnrollDate。

  1. 创建索引：
sql
CREATE INDEX idx_student_name ON Students (LastName, FirstName);

这个命令在Students表的LastName和FirstName列上创建了一个名为idx_student_name的复合索引。

  1. 创建视图：
sql
CREATE VIEW StudentDetails AS
SELECT StudentID, FirstName, LastName, BirthDate
FROM Students;

这里创建了一个名为StudentDetails的视图，它从Students表中选择了一些列。

ALTER ​

ALTER关键字用于修改数据库中已经存在的对象的结构。

  1. 向表中添加新列：
sql
ALTER TABLE Students ADD Email VARCHAR(100);

这条命令在Students表中添加了一个名为Email的新列，数据类型为VARCHAR(100)。

  1. 修改列的数据类型：
sql
ALTER TABLE Students MODIFY BirthDate VARCHAR(10);

这个命令将Students表中的BirthDate列的数据类型从DATE改为VARCHAR(10)。

  1. 删除表中的列：
sql
ALTER TABLE Students DROP COLUMN Email;

此命令从Students表中删除了名为Email的列。

  1. 添加主键约束：
sql
ALTER TABLE Students ADD CONSTRAINT PK_StudentID PRIMARY KEY (StudentID);

这条命令为Students表的StudentID列添加了一个名为PK_StudentID的主键约束。

DROP ​

DROP关键字用于从数据库中删除对象。

  1. 删除数据库：
sql
DROP DATABASE SchoolDB;

这条命令会删除名为SchoolDB的整个数据库，包括其中的所有表、索引、视图等。

  1. 删除表：
sql
DROP TABLE Students;

此命令会删除名为Students的表及其所有数据。

  1. 删除索引：
sql
DROP INDEX idx_student_name ON Students;

这个命令会删除在Students表上创建的名为idx_student_name的索引。

  1. 删除视图：
sql
DROP VIEW StudentDetails;

此命令会删除名为StudentDetails的视图。

数据操纵语言 ​

用途 ​

DML主要用于对数据库中的数据进行操作，包括插入、更新、删除和查询数据。这类操作通常由应用程序或用户通过应用程序接口执行，以满足日常业务需求。

关键字 ​

INSERT ​

INSERT关键字用于向数据库表中添加新的数据行。

  1. 向表中插入一条新记录：
sql
INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, EnrollDate)
VALUES (1, 'John', 'Doe', '2000-05-15', '2020-09-01');

这条命令向Students表中插入了一条新的学生记录。

  1. 从另一个表中插入数据：
sql
INSERT INTO Students (StudentID, FirstName, LastName, BirthDate)
SELECT StudentID, FirstName, LastName, BirthDate FROM BackupStudents;

这个命令将BackupStudents表中的数据插入到Students表中。

UPDATE ​

UPDATE关键字用于修改数据库表中已经存在的数据行。

  1. 更新单个表中的记录：
sql
UPDATE Students
SET Email = 'john.doe@example.com'
WHERE StudentID = 1;

这条命令更新了Students表中StudentID为1的学生的电子邮件地址。

  1. 更新多个表中的记录：
sql
UPDATE Students, Enrollment
SET Students.Email = 'john.doe@example.com', Enrollment.Status = 'Graduated'
WHERE Students.StudentID = Enrollment.StudentID AND Students.StudentID = 1;

这个命令同时更新了Students和Enrollment两个表中的记录，将StudentID为1的学生的电子邮件地址更新，并且将其在Enrollment表中的状态设置为'Graduated'。

DELETE ​

DELETE关键字用于从数据库表中删除数据行。

  1. 删除单个表中的记录：
sql
DELETE FROM Students
WHERE StudentID = 1;

这条命令从Students表中删除了StudentID为1的记录。

  1. 使用JOIN从多个表中删除记录：
sql
DELETE Students, Enrollment
FROM Students
INNER JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
WHERE Students.StudentID = 1;

这个命令通过内部连接Students和Enrollment表，删除了StudentID为1的学生在两个表中的记录。

SELECT ​

SELECT关键字用于从数据库表中检索数据。

  1. 基本查询：
sql
SELECT StudentID, FirstName, LastName
FROM Students;

这条命令从Students表中检索所有学生的StudentID、FirstName和LastName。

  1. 带条件的查询：
sql
SELECT StudentID, FirstName, LastName
FROM Students
WHERE BirthDate > '2001-01-01';

这个命令检索出生日期在2001年1月1日之后的所有学生的StudentID、FirstName和LastName。

  1. 连接查询：
sql
SELECT Students.StudentID, Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
INNER JOIN Courses ON Enrollment.CourseID = Courses.CourseID;

这个命令通过两个内部连接，检索了学生的信息以及他们所注册的课程名称。

  1. 分组查询：
sql
SELECT CourseID, COUNT(*) AS NumberOfStudents
FROM Enrollment
GROUP BY CourseID;

这个命令按CourseID分组，并计算每个课程的学生数量。

  1. 排序查询：
sql
SELECT StudentID, FirstName, LastName
FROM Students
ORDER BY LastName ASC, FirstName ASC;

这个命令按姓氏和名字的升序排列检索到的学生列表。

数据控制语言 ​

用途 ​

DCL用于控制数据库中数据的访问权限，确保只有经过授权的用户才能访问和修改数据。这有助于保护数据的安全性和完整性，防止未经授权的访问和操作。

关键字 ​

GRANT ​

GRANT关键字用于为用户或角色分配权限，以便他们可以执行特定的数据库操作。

  1. 授予单个用户特定权限：
sql
GRANT SELECT, INSERT, UPDATE ON Students TO john_doe;

这条命令授予用户john_doe在Students表上执行SELECT、INSERT和UPDATE操作的权限。

  1. 授予所有权限给用户：
sql
GRANT ALL PRIVILEGES ON Students TO jane_doe;

这条命令授予用户jane_doe对Students表的所有权限。

  1. 授予角色权限：
sql
GRANT SELECT ON Students TO role_students_viewer;

这条命令授予角色role_students_viewer对Students表的SELECT权限。

REVOKE ​

REVOKE关键字用于撤销之前使用GRANT命令授予的权限。

  1. 撤销单个用户权限：
sql
REVOKE INSERT ON Students FROM john_doe;

这条命令撤销了用户john_doe在Students表上执行INSERT操作的权限。

  1. 撤销所有权限：
sql
REVOKE ALL PRIVILEGES ON Students FROM jane_doe;

这条命令撤销了用户jane_doe在Students表上的所有权限。

  1. 撤销角色权限：
sql
REVOKE SELECT ON Students FROM role_students_viewer;

这条命令撤销了角色role_students_viewer对Students表的SELECT权限。

角色管理 ​

在数据库中，角色是一组权限的集合，可以分配给一个或多个用户。

  1. 创建角色：
sql
CREATE ROLE student_manager;

这条命令创建了一个名为student_manager的新角色。

  1. 将权限授予角色：
sql
GRANT SELECT, INSERT, UPDATE, DELETE ON Students TO student_manager;

这条命令授予student_manager角色对Students表的所有DML权限。

  1. 将角色分配给用户：
sql
GRANT student_manager TO john_doe, jane_doe;

这条命令将student_manager角色分配给用户john_doe和jane_doe，他们现在拥有该角色的所有权限。

  1. 撤销角色分配：
sql
REVOKE student_manager FROM jane_doe;

这条命令撤销了用户jane_doe的student_manager角色，从而撤销了她通过该角色获得的所有权限。

事务控制语言 ​

用途 ​

TCL用于管理数据库中的事务，确保数据的一致性和完整性。事务是指一系列的操作，这些操作要么全部成功，要么全部失败。TCL提供了控制事务生命周期的方法，如开始、提交、回滚和设置保存点。

关键字 ​

COMMIT ​

COMMIT关键字用于永久保存事务中的所有更改。一旦事务被提交，它就不能被撤销。

假设我们需要将一笔交易记录到数据库中，涉及更新两个表：Accounts（账户表）和Transactions（交易表）。

sql
START TRANSACTION; -- 开始一个新事务
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; -- 从账户1中扣除100元
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; -- 向账户2中增加100元
INSERT INTO Transactions (FromAccount, ToAccount, Amount, TransactionDate) VALUES (1, 2, 100, NOW()); -- 记录交易
COMMIT; -- 提交事务，所有更改将永久保存

如果COMMIT执行成功，上述操作将不可逆。

ROLLBACK ​

ROLLBACK关键字用于撤销事务中所有的更改，将数据库状态恢复到事务开始之前。

如果在上面的例子中，在执行INSERT INTO Transactions之后，我们发现问题（例如，账户1的余额不足），我们可以选择回滚整个事务：

sql
ROLLBACK; -- 回滚事务，撤销所有更改

执行ROLLBACK后，Accounts表和Transactions表都不会有任何更改。

SAVEPOINT ​

SAVEPOINT关键字用于在事务中设置一个标记点，可以在后续的操作中回滚到这个点，而不是回滚整个事务。

继续上面的例子，如果我们想在扣除账户1的余额和增加账户2的余额之间设置一个保存点：

sql
START TRANSACTION; -- 开始一个新事务
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; -- 从账户1中扣除100元
SAVEPOINT BeforeTransfer; -- 设置保存点
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; -- 向账户2中增加100元
-- 假设在这里检测到错误，账户2不允许接收转账
ROLLBACK TO SAVEPOINT BeforeTransfer; -- 回滚到保存点，撤销增加账户2余额的操作
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 3; -- 改为向账户3中增加100元
COMMIT; -- 提交事务，现在账户1扣除100元，账户3增加100元

在这个例子中，如果事务在向账户2增加余额时出现错误，我们可以回滚到BeforeTransfer保存点，然后继续事务，将100元转给账户3。

SQL执行顺序 ​

SELECT语句中常见的各个子句的执行顺序如下：

  1. FROM子句
    • 表的组装：这是查询的第一步，涉及到从指定的表中读取数据。如果FROM子句中有多个表，那么这些表会被组合在一起。在没有显式JOIN的情况下，默认是笛卡尔积（即所有可能的行组合）。如果有JOIN操作，那么会根据JOIN类型和条件来组装表。
  2. JOIN操作
    • 连接条件：根据JOIN类型（如LEFT JOIN、INNER JOIN等）和ON子句中的连接条件，对前一步生成的虚拟表进行行筛选和添加外部行，形成新的虚拟表。
  3. WHERE子句
    • 行筛选：应用WHERE子句中的条件对上一步生成的虚拟表进行行筛选，只保留符合条件的行。
  4. GROUP BY子句
    • 数据分组：根据GROUP BY子句中的列列表，将上一步筛选后的数据集进行分组。分组后的每一组通常用于聚合函数（如COUNT、SUM、AVG等）的计算。如果没有GROUP BY子句，整个结果集将被视为一个单一的组。
  5. HAVING子句
    • 分组过滤：在应用了聚合函数后，根据HAVING子句的条件对分组结果进行筛选，移除不满足条件的分组。
  6. SELECT子句
    • 字段选择：处理SELECT子句列表，生成新的虚拟表，包含选定的列和表达式的结果。
  7. DISTINCT关键字
    • 去重：如果使用DISTINCT关键字，则从结果集中移除重复的行，生成新的虚拟表。
  8. ORDER BY子句
    • 结果排序：根据ORDER BY子句中的列列表对结果集进行排序，生成游标。排序可以是升序（ASC）或降序（DESC）。
  9. LIMIT/OFFSET子句
    • 结果限定：使用LIMIT子句可以从排序后的行中选择指定数量或比例的行，作为最终结果返回给调用者。如果同时使用了LIMIT和OFFSET，OFFSET子句会首先应用，用于跳过指定数量的行，然后LIMIT子句会返回剩余的行。

常用关键字 ​

DISTINCT ​

DISTINCT 用于去除查询结果中的重复行。当使用 SELECT 语句查询数据时，可能会得到包含重复数据的记录集。在这种情况下，如果只对唯一的记录感兴趣，就可以使用 DISTINCT 关键字。

基本用法 ​

sql
SELECT DISTINCT column1, column2, ...
FROM table_name;

这里的 DISTINCT 关键字应用于 SELECT 语句中列出的所有列。查询结果将只包含这些列组合的唯一值。

DISTINCT 不能用于 SELECT *，因为 * 表示所有列，而 DISTINCT 需要明确指定列名。

在使用 DISTINCT 时，不能使用别名，因为 DISTINCT 需要在结果集生成之前去除重复项。

作用范围 ​

DISTINCT 关键字作用于 SELECT 语句中列出的所有列的组合。换句话说，如果指定了多个列，则 DISTINCT 会根据所有列的组合来消除重复的行。

示例 ​

假设有一个名为 Employees 的表，其中包含以下数据：

ID  Name   Department
1   John   HR
2   Jane   IT
3   John   HR
4   Mike   IT
5   Jane   HR

以下是一些使用 DISTINCT 的示例：

示例 1：单列去重 ​

sql
SELECT DISTINCT Name
FROM Employees;

结果将是：

John
Jane
Mike

这里去除了 Name 列中的重复值。

示例 2：多列去重 ​

sql
SELECT DISTINCT Name, Department
FROM Employees;

结果将是：

John   HR
Jane   IT
Mike   IT
Jane   HR

这里去除了 Name 和 Department 列组合的重复值。注意，尽管 "Jane" 出现了两次，但它们分别属于不同的部门，因此都被包含在结果中。

GROUP BY ​

GROUP BY 允许将查询结果集中的多行数据根据一个或多个列进行分组。使用 GROUP BY 可以对每个分组进行聚合计算，如计算每个分组的总和、平均值、最大值、最小值等。

基本用法 ​

GROUP BY 语句通常与聚合函数（如 COUNT(), MAX(), MIN(), SUM(), AVG() 等）一起使用，以对每个分组的数据进行计算。

sql
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

GROUP BY 语句可以包含任意数量的列，这将根据这些列的组合进行分组。

如果 SELECT 语句中包含了任何聚合函数之外的列，则这些列必须出现在 GROUP BY 子句中。

GROUP BY 通常与 ORDER BY 一起使用，以对结果进行排序。

示例 ​

假设有一个 Orders 表，包含以下列：OrderID, CustomerID, EmployeeID, OrderDate, ShipperID, Freight。

以下是一个使用 GROUP BY 的示例，计算每个客户的订单数量：

sql
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID;

在这个例子中，结果集将包含两列：CustomerID 和 NumberOfOrders。COUNT(OrderID) 计算每个客户的订单数量，GROUP BY CustomerID 指定按照 CustomerID 列进行分组。

多列分组 ​

GROUP BY 也可以按照多个列进行分组。以下示例按照 CustomerID 和 EmployeeID 进行分组，计算每个客户和每个员工的订单数量：

sql
SELECT CustomerID, EmployeeID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID, EmployeeID;

使用 HAVING 子句 ​

与 GROUP BY 一起使用的另一个关键字是 HAVING。HAVING 子句类似于 WHERE 子句，但是它用于过滤分组后的结果，而不是单个行。以下示例仅显示订单数量大于 10 的客户：

sql
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 10;

LIKE ​

LIKE 用于在 WHERE 子句中搜索特定模式的字符串。它通常与 % 和 _ 通配符一起使用，以匹配特定格式的数据。

基本语法 ​

sql
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

通配符 ​

  • %：百分号代表任意数量的字符（包括零个字符）。
  • _：下划线代表任意单个字符。

示例 ​

假设有一个名为 Employees 的表，其中包含 LastName 和 FirstName 列。

下面的查询将返回 LastName 列中包含 "son" 的所有记录：

sql
SELECT *
FROM Employees
WHERE LastName LIKE '%son%';

以下查询将返回 LastName 列以 "Sm" 开头的所有记录：

sql
SELECT *
FROM Employees
WHERE LastName LIKE 'Sm%';

以下查询将返回 LastName 列以 "ith" 结尾的所有记录：

sql
SELECT *
FROM Employees
WHERE LastName LIKE '%ith';

以下查询将返回 LastName 列第二个字符是 "o" 的所有记录：

sql
SELECT *
FROM Employees
WHERE LastName LIKE '_o%';

当搜索的字符串中包含 % 或 _ 字符时，可以使用 ESCAPE 关键字指定一个转义字符。例如，要搜索包含百分号的字符串，可以使用以下查询：

sql
SELECT *
FROM Employees
WHERE LastName LIKE '%\%%' ESCAPE '\';

在这个例子中，反斜杠 \ 被用作转义字符，表示随后的 % 应该按字面意义处理。

聚合函数 ​

聚合函数（Aggregate Functions）是 SQL 中用于对一组值进行计算并返回单个值的函数。它们通常在 SELECT 语句中与 GROUP BY 子句一起使用，或者在不需要分组的情况下对整个列进行计算。

聚合函数作用语法示例
COUNT()返回表中的行数或特定列中非空值的数量。COUNT(*) 返回所有行，COUNT(column_name) 返回指定列的非空值数量。SELECT COUNT(*) FROM Employees;
SUM()返回数值列的总和。SUM(column_name)SELECT SUM(Salary) FROM Employees;
AVG()返回数值列的平均值。AVG(column_name)SELECT AVG(Salary) FROM Employees;
MAX()返回数值列、日期列或字符列中的最大值。MAX(column_name)SELECT MAX(Salary) FROM Employees;
MIN()返回数值列、日期列或字符列中的最小值。MIN(column_name)SELECT MIN(Salary) FROM Employees;
GROUP_CONCAT()返回一个字符串，该字符串是列中值的连接。GROUP_CONCAT(column_name [SEPARATOR 'string'])SELECT GROUP_CONCAT(FirstName SEPARATOR ', ') FROM Employees;
STDDEV() / STDEV()返回数值列的样本标准偏差。STDDEV(column_name) 或 STDEV(column_name)SELECT STDDEV(Salary) FROM Employees;
VAR_POP() / VARIANCE()返回数值列的总体方差。VAR_POP(column_name) 或 VARIANCE(column_name)SELECT VAR_POP(Salary) FROM Employees;
VAR_SAMP()返回数值列的样本方差。VAR_SAMP(column_name)SELECT VAR_SAMP(Salary) FROM Employees;