SQL语言分类及命令详解（二）

目录

一、DQL (Data Query Language) 数据查询语言

核心命令：SELECT

基本语法：

详细分析：

高级特性：

示例：

二、DDL (Data Definition Language) 数据定义语言

核心命令

CREATE

ALTER

DROP

TRUNCATE

详细分析：

数据类型：需为每列指定适当的数据类型(INT, VARCHAR, DATE等)

约束：

TRUNCATE vs DELETE：

 示例：

三、DML (Data Manipulation Language) 数据操纵语言

核心命令

INSERT

UPDATE

DELETE

详细分析：

INSERT注意事项：

UPDATE/DELETE注意事项：

MERGE：高级操作，根据条件执行INSERT/UPDATE/DELETE

示例：

四、 DCL (Data Control Language) 数据控制语言

核心命令

GRANT

REVOKE

详细分析：

常见权限：

权限级别：

示例：

五、TCL (Transaction Control Language) 事务控制语言

核心命令

事务基本命令

SAVEPOINT

详细分析：

事务特性(ACID)：******

隔离级别：

SAVEPOINT：在事务内设置保存点，可以部分回滚

示例：

综合应用示例

总结

DQL：专注于数据检索，SELECT语句功能强大且复杂

DDL：定义数据结构，影响数据库整体架构

DML：操作实际数据，是业务逻辑的核心

DCL：管理数据安全，控制访问权限

TCL：保证数据一致性，管理事务边界

---

SQL(结构化查询语言)按照功能可以分为以下几大类，我将对每一类进行详细讲解和分析，包括语法、使用场景和注意事项。

一、DQL (Data Query Language) 数据查询语言

核心命令：SELECT

基本语法：

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column_name]
[HAVING group_condition]
[ORDER BY column_name [ASC|DESC]]
[LIMIT offset, count];

详细分析：

1. SELECT 子句：指定要检索的列，可以使用*表示所有列

2. FROM 子句：指定数据来源的表

3. WHERE 子句：过滤条件，支持比较运算符(=, >, <等)和逻辑运算符(AND, OR, NOT)

4. GROUP BY：分组聚合，常与聚合函数(COUNT, SUM, AVG等)一起使用

5. HAVING：对分组结果进行过滤

6. ORDER BY：结果排序，默认ASC(升序)

7. LIMIT：限制返回行数(MySQL语法)

高级特性：

• 子查询：SELECT语句嵌套

• 连接查询：INNER JOIN, LEFT JOIN等

• 集合操作：UNION, INTERSECT等

• 窗口函数：OVER(), RANK()等(高级分析功能)

示例：

-- 多表连接查询
SELECT e.name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 5000
ORDER BY e.salary DESC
LIMIT 10;-- 使用聚合函数
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;

二、DDL (Data Definition Language) 数据定义语言

核心命令

CREATE

-- 创建表
CREATE TABLE table_name (column1 datatype [constraints],column2 datatype [constraints],...[table_constraints]
);-- 创建索引
CREATE [UNIQUE] INDEX index_name 
ON table_name (column1, column2, ...);

ALTER

-- 添加列
ALTER TABLE table_name ADD column_name datatype;-- 修改列
ALTER TABLE table_name MODIFY column_name new_datatype;-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;-- 添加约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

DROP

DROP TABLE [IF EXISTS] table_name;
DROP INDEX index_name ON table_name;

TRUNCATE

TRUNCATE TABLE table_name;

详细分析：

1. 数据类型：需为每列指定适当的数据类型(INT, VARCHAR, DATE等)

2. 约束：

   • PRIMARY KEY：主键

   • FOREIGN KEY：外键

   • NOT NULL：非空

   • UNIQUE：唯一

   • CHECK：检查条件

3. TRUNCATE vs DELETE：

   • TRUNCATE是DDL操作，删除所有数据并重置自增计数器

   • DELETE是DML操作，可带条件删除，不重置计数器

 示例：

CREATE TABLE employees (emp_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,email VARCHAR(100) UNIQUE,salary DECIMAL(10,2) CHECK (salary > 0),dept_id INT,hire_date DATE DEFAULT CURRENT_DATE,FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);-- 修改表结构
ALTER TABLE employees ADD phone VARCHAR(20);
ALTER TABLE employees MODIFY salary DECIMAL(12,2);

三、DML (Data Manipulation Language) 数据操纵语言

核心命令

INSERT

-- 插入单行
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);-- 插入多行
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),(value1, value2, ...),...;-- 从查询结果插入
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

UPDATE

UPDATE table_name
SET column1 = value1,column2 = value2,...
WHERE condition;

DELETE

DELETE FROM table_name
WHERE condition;

详细分析：

1. INSERT注意事项：

   • 列名和值必须一一对应

   • 可以省略列名(需提供所有列的值)

   • 使用DEFAULT关键字插入默认值

2. UPDATE/DELETE注意事项：

   • 必须谨慎使用WHERE条件，避免误操作

   • 可以先使用SELECT测试WHERE条件

3. MERGE：高级操作，根据条件执行INSERT/UPDATE/DELETE

示例：

-- 插入数据
INSERT INTO employees (name, email, salary, dept_id)
VALUES ('张三', '[e-mail]', 8000.00, 10);-- 更新数据
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = 10 AND hire_date < '2020-01-01';-- 删除数据
DELETE FROM employees
WHERE emp_id = 1001;

四、 DCL (Data Control Language) 数据控制语言

核心命令

GRANT

GRANT privilege1, privilege2, ...
ON object_name
TO user1, user2, ...
[WITH GRANT OPTION]; -- 允许被授权者再授权

REVOKE

REVOKE privilege1, privilege2, ...
ON object_name
FROM user1, user2, ...;

详细分析：

1. 常见权限：

   • SELECT, INSERT, UPDATE, DELETE：表数据操作权限

   • ALTER, INDEX：表结构修改权限

   • CREATE, DROP：数据库对象创建删除权限

   • ALL PRIVILEGES：所有权限

2. 权限级别：

   • 数据库级别：ON database_name.*

   • 表级别：ON database_name.table_name

   • 列级别：部分数据库支持

示例：

-- 授予权限
GRANT SELECT, INSERT ON company_db.employees TO user1;-- 授予所有权限并允许传递
GRANT ALL PRIVILEGES ON company_db.* TO admin WITH GRANT OPTION;-- 撤销权限
REVOKE DELETE ON company_db.employees FROM user1;

五、TCL (Transaction Control Language) 事务控制语言

核心命令

事务基本命令

START TRANSACTION; -- 或 BEGIN
-- 执行一系列SQL语句
COMMIT; -- 提交事务
-- 或
ROLLBACK; -- 回滚事务

SAVEPOINT

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;

详细分析：

1. 事务特性(ACID)：******

   • 原子性(Atomicity)：全部成功或全部失败

   • 一致性(Consistency)：保持数据一致状态

   • 隔离性(Isolation)：事务间相互隔离

   • 持久性(Durability)：提交后永久生效

2. 隔离级别：

   • READ UNCOMMITTED

   • READ COMMITTED

   • REPEATABLE READ

   • SERIALIZABLE

3. SAVEPOINT：在事务内设置保存点，可以部分回滚

示例：

START TRANSACTION;SAVEPOINT before_update;UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;-- 检查是否有错误
IF error_occurred THENROLLBACK TO before_update;
ELSECOMMIT;
END IF;

综合应用示例

-- 创建数据库
CREATE DATABASE bank_system;-- 创建表
CREATE TABLE accounts (account_id INT PRIMARY KEY AUTO_INCREMENT,customer_name VARCHAR(100) NOT NULL,balance DECIMAL(15,2) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CREATE TABLE transactions (transaction_id INT PRIMARY KEY AUTO_INCREMENT,from_account INT,to_account INT,amount DECIMAL(15,2) NOT NULL,transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (from_account) REFERENCES accounts(account_id),FOREIGN KEY (to_account) REFERENCES accounts(account_id)
);-- 插入初始数据
INSERT INTO accounts (customer_name, balance)
VALUES ('张三', 5000.00), ('李四', 3000.00);-- 转账事务
START TRANSACTION;-- 检查账户余额
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;-- 执行转账
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;-- 记录交易
INSERT INTO transactions (from_account, to_account, amount)
VALUES (1, 2, 1000.00);-- 提交事务
COMMIT;-- 查询结果
SELECT a.customer_name, a.balance
FROM accounts a;SELECT * FROM transactions;

总结

1. DQL：专注于数据检索，SELECT语句功能强大且复杂

2. DDL：定义数据结构，影响数据库整体架构

3. DML：操作实际数据，是业务逻辑的核心

4. DCL：管理数据安全，控制访问权限

5. TCL：保证数据一致性，管理事务边界

在实际应用中，这些语言通常需要配合使用，以完成复杂的数据库操作。理解各类SQL语言的用途和特性，有助于设计更高效、更安全的数据库应用。