Skip to main content

3.SQL基础

3.1 什么是SQL

SQL(Structured Query Language)是用于管理和操作关系型数据库的标准编程语言。通过 SQL,用户可以创建、修改、查询和删除数据库中的数据,同时也能控制数据库的访问权限和事务管理。SQL 起源于 1970 年代的关系数据库理论,由 IBM 公司开发,并逐渐成为关系型数据库领域的标准语言,现已被广泛应用于如 MySQL、PostgreSQL、SQL Server 和 Oracle 等主流数据库系统。

SQL 既具备简单易用的查询语言特性,又有复杂的操作能力,能够高效处理数据存储、数据管理和业务逻辑。SQL 语言的核心功能主要围绕数据定义、数据操作、数据查询、事务控制和访问权限管理等方面展开。

3.2 SQL的分类

SQL 可以根据不同的功能划分为多个子集。SQL 不仅是简单的数据查询工具,它提供了全面的数据操作和管理能力,确保数据库的完整性和一致性。以下是 SQL 的主要分类:

3.2.1 DDL(数据定义语言)

数据定义语言(Data Definition Language) 用于定义、修改和删除数据库结构对象,如数据库、表、视图和索引。它是数据库管理中的基础部分,负责确定数据库的组织和结构。常见的 DDL 命令包括:

1. CREATE:用于创建数据库对象,例如创建新的数据库、表、视图或索引。

-- 创建数据库
CREATE DATABASE company_db;

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2)
);

2. ALTER:用于修改已有数据库对象的结构,例如为表增加、修改或删除列。

-- 向表中添加新列
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- 修改已有列的数据类型
ALTER TABLE employees MODIFY salary DECIMAL(12,2);

3. DROP:用于删除数据库对象,如表或索引。该操作会永久性地移除对象及其数据。

-- 删除表
DROP TABLE employees;

4. TRUNCATE:用于清空表中的所有数据,但保留表的结构。与 DELETE 不同,TRUNCATE 不能回滚操作。

-- 清空表
TRUNCATE TABLE employees;

DDL 的关键特性是其操作不可回滚,一旦执行了诸如 DROP 或 TRUNCATE 这样的命令,删除的数据和结构将永久丢失。

3.2.2 DML(数据操作语言)

数据操作语言(Data Manipulation Language) 主要用于对数据库中的数据进行插入、更新、删除等操作。DML 命令处理的数据均存储在数据库表中,它是用户和数据库交互的主要方式。DML 包括以下操作:

1. INSERT:用于向数据库表中插入新记录。

-- 插入新数据
INSERT INTO employees (id, name, position, salary) 
VALUES (1, 'John Doe', 'Software Engineer', 60000.00);

2. UPDATE:用于更新表中已有数据。通过 WHERE 子句,可以有选择地更新部分记录。

-- 更新数据
UPDATE employees 
SET salary = 65000.00 
WHERE id = 1;

3. DELETE:用于删除表中的数据。与 TRUNCATE 不同,DELETE 可以通过 WHERE 子句选择性地删除记录,并且可以回滚操作。

-- 删除指定数据
DELETE FROM employees 
WHERE id = 1;

DML 操作通常与事务控制语言(TCL)结合使用,确保数据的完整性和一致性。

3.2.3 DQL(数据查询语言)

数据查询语言(Data Query Language) 专注于数据的检索,主要通过 SELECT 语句完成。DQL 是 SQL 中使用最广泛的部分,它允许用户按照特定条件从数据库中提取数据,进行排序、过滤、分组等操作。常用的 DQL 操作包括:

SELECT:用于查询数据。可以通过 WHERE 子句过滤数据,ORDER BY 子句排序,GROUP BY 子句分组,JOIN 子句关联多个表。

-- 查询所有员工信息
SELECT * FROM employees;

-- 查询特定字段
SELECT name, position FROM employees;

-- 查询符合条件的记录
SELECT * FROM employees 
WHERE salary > 50000;

DQL 是 SQL 语言中最灵活和强大的部分之一。通过结合 WHERE、GROUP BY、HAVING、ORDER BY 等子句,用户可以灵活高效地提取所需数据。

3.2.4 TCL(事务控制语言)

事务控制语言(Transaction Control Language) 用于管理数据库中的事务。事务是一个操作序列,包含了一个或多个 SQL 语句,这些语句要么全部执行成功,要么全部回滚。TCL 确保了数据操作的原子性、一致性、隔离性和持久性(ACID)。常用的 TCL 命令有:

1. COMMIT:用于提交当前事务,确保所有 DML 操作永久保存。

-- 提交事务
COMMIT;

2. ROLLBACK:用于回滚事务,将所有尚未提交的操作恢复到事务开始前的状态。

-- 回滚事务
ROLLBACK;

3. SAVEPOINT:用于设置事务中的保存点,便于回滚到特定的操作步骤。

-- 设置保存点
SAVEPOINT savepoint1;

TCL 是多表操作或复杂数据处理场景中非常关键的工具。通过合理使用事务,可以确保数据在出现错误时得以恢复,从而维持数据库的完整性。

3.2.5 DCL(数据控制语言)

数据控制语言(Data Control Language) 主要用于控制数据库用户的访问权限。通过 DCL 命令,数据库管理员可以为不同的用户分配权限,控制他们对数据和表结构的操作。常用的 DCL 命令有:

1.CREATE USER:创建新用户

--  创建一个新用户并设置密码
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password';

2. GRANT:授予用户特定的权限,例如查询、插入、更新或删除数据的权限。

-- 授予用户查询权限
GRANT SELECT ON employees TO 'john'@'localhost';

-- 查看当前用户权限
SHOW GRANTS FOR CURRENT_USER();

3. REVOKE:撤销用户的某些权限。

-- 撤销用户的查询权限
REVOKE SELECT, INSERT, UPDATE ON employees FROM 'username'@'localhost';

4. DROP USER: 删除用户

-- 列出数据库所有用户
SELECT user,host FROM mysql.user;

-- 删除指定用户
DROP user 'john'@'localhost';

DCL 操作通常由数据库管理员执行,确保系统的安全性和数据的机密性。

3.3 总结

SQL 作为数据库管理的标准语言,涵盖了从数据定义、操作、查询到权限管理的广泛功能。通过划分为 DDL、DML、DQL、TCL 和 DCL 等不同子集,SQL 提供了灵活的工具集,适用于各种数据管理需求。在实际工作中,熟练掌握 SQL 各部分的功能及其操作方式,可以大大提升数据库管理和操作的效率与安全性。