SQL（結構化查詢語言）通常被分為四種主要類型，每種類型負責不同的數據庫操作。下面我將詳細介紹這四類SQL語言的語法和用途。

一、DDL (Data Definition Language) 數據定義語言

功能：定義和管理數據庫對象結構（表、視圖、索引等）

主要命令：

CREATE?- 創建數據庫對象

-- 創建數據庫
CREATE DATABASE school;-- 創建表
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT CHECK (age > 0),class_id INT,FOREIGN KEY (class_id) REFERENCES classes(id)
);-- 創建索引
CREATE INDEX idx_name ON students(name);-- 創建視圖
CREATE VIEW student_view AS
SELECT id, name FROM students WHERE age > 10;

ALTER?- 修改數據庫對象

-- 添加列
ALTER TABLE students ADD COLUMN gender CHAR(1);-- 修改列類型
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);-- 刪除列
ALTER TABLE students DROP COLUMN gender;

DROP?- 刪除數據庫對象

DROP TABLE IF EXISTS temp_students;
DROP VIEW student_view;

TRUNCATE?- 清空表數據（保留結構）

TRUNCATE TABLE log_data;

二、DML (Data Manipulation Language) 數據操作語言

功能：操作數據庫中的數據記錄

主要命令：

1. INSERT?- 插入數據

-- 插入單條記錄
INSERT INTO students (name, age, class_id)
VALUES ('張三', 15, 1);-- 插入多條記錄
INSERT INTO students (name, age, class_id)
VALUES ('李四', 16, 1), ('王五', 14, 2);-- 從其他表插入數據
INSERT INTO graduate_students
SELECT * FROM students WHERE age > 18;

UPDATE?- 更新數據

-- 更新單列
UPDATE students SET age = 16 WHERE name = '張三';-- 更新多列
UPDATE students 
SET age = age + 1, class_id = 3
WHERE id = 5;-- 使用子查詢更新
UPDATE students
SET class_id = (SELECT id FROM classes WHERE name = '高三')
WHERE age > 17;

DELETE?- 刪除數據

-- 刪除特定記錄
DELETE FROM students WHERE id = 10;-- 刪除所有記錄
DELETE FROM temp_students;-- 使用子查詢刪除
DELETE FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '畢業班');

MERGE?- 合并操作（UPSERT）

-- MySQL語法
INSERT INTO students (id, name, age)
VALUES (1, '張三', 15)
ON DUPLICATE KEY UPDATE age = 16;-- PostgreSQL語法
INSERT INTO students (id, name, age)
VALUES (1, '張三', 15)
ON CONFLICT (id) DO UPDATE SET age = 16;

三、DQL (Data Query Language) 數據查詢語言

功能：查詢數據庫中的數據

主要命令：

1. SELECT?- 查詢數據

-- 基本查詢
SELECT * FROM students;-- 條件查詢
SELECT name, age FROM students WHERE age > 15;-- 排序
SELECT * FROM students ORDER BY age DESC, name ASC;-- 分組聚合
SELECT class_id, COUNT(*) as student_count, AVG(age) as avg_age
FROM students
GROUP BY class_id
HAVING COUNT(*) > 5;-- 連接查詢
SELECT s.name, c.class_name
FROM students s
JOIN classes c ON s.class_id = c.id;-- 子查詢
SELECT name FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '高一');-- 分頁查詢
SELECT * FROM students LIMIT 10 OFFSET 20;  -- MySQL
SELECT * FROM students OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- SQL標準

WITH (CTE)?- 公用表表達式

WITH top_students AS (SELECT * FROM students ORDER BY score DESC LIMIT 10
)
SELECT * FROM top_students WHERE gender = 'F';

四、DCL (Data Control Language) 數據控制語言

功能：控制數據庫訪問權限和事務處理

主要命令：

1. GRANT?- 授予權限

-- 授予SELECT權限
GRANT SELECT ON students TO user1;-- 授予所有權限
GRANT ALL PRIVILEGES ON database.* TO 'admin'@'localhost';-- 授予特定列權限
GRANT SELECT (name, age), UPDATE (age) ON students TO teacher_role;

REVOKE?- 撤銷權限

-- 撤銷權限
REVOKE INSERT ON students FROM user2;-- 撤銷所有權限
REVOKE ALL PRIVILEGES ON database.* FROM 'old_admin'@'localhost';

COMMIT?- 提交事務

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

ROLLBACK?- 回滾事務

BEGIN TRANSACTION;
DELETE FROM orders WHERE status = 'pending';
-- 發現錯誤
ROLLBACK;

SAVEPOINT?- 設置保存點

BEGIN TRANSACTION;
INSERT INTO log (message) VALUES ('Operation started');
SAVEPOINT sp1;
UPDATE data SET value = 10 WHERE id = 1;
-- 部分回滾
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

五、四種語言對比總結

六、實際應用示例

場景：學生管理系統操作

-- DDL: 創建表結構
CREATE TABLE classes (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,grade VARCHAR(20)
) ENGINE=InnoDB;-- DML: 插入班級數據
INSERT INTO classes (name, grade) VALUES 
('一班', '高一'), ('二班', '高一'), ('三班', '高二');-- DQL: 查詢班級信息
SELECT * FROM classes WHERE grade = '高一';-- DML: 更新班級信息
UPDATE classes SET grade = '高三' WHERE name = '三班';-- DCL: 創建用戶并授權
CREATE USER 'teacher'@'%' IDENTIFIED BY 'password';
GRANT SELECT, UPDATE ON school.students TO 'teacher'@'%';
GRANT SELECT ON school.classes TO 'teacher'@'%';-- DDL: 添加索引提高查詢性能
CREATE INDEX idx_class_grade ON classes(grade);-- 事務處理示例 (DCL)
BEGIN TRANSACTION;-- DML: 轉班操作UPDATE students SET class_id = 2 WHERE id = 101;UPDATE class_stats SET student_count = student_count - 1 WHERE class_id = 1;UPDATE class_stats SET student_count = student_count + 1 WHERE class_id = 2;
COMMIT;