SQL 四大语言分类中DDL、DML、DCL、DQL的使用

SQL语言根据功能可分为四类：DDL、DML、DQL、DCL。下面是详细的分类说明和使用示例：

一、DDL（数据定义语言）

作用：定义和管理数据库对象（表、索引、视图等结构）

常用语句：

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- 修改表结构
ALTER TABLE employees 
ADD COLUMN email VARCHAR(100);

-- 修改列
ALTER TABLE employees 
MODIFY COLUMN name VARCHAR(150);

-- 删除列
ALTER TABLE employees 
DROP COLUMN age;

-- 重命名表
RENAME TABLE employees TO staff;

-- 删除表
DROP TABLE staff;

-- 创建索引
CREATE INDEX idx_department ON employees(department);

-- 删除索引
DROP INDEX idx_department ON employees;

二、DML（数据操作语言）

作用：对表中的数据进行增删改操作

常用语句：

-- 插入数据
INSERT INTO employees (name, department, salary, hire_date)
VALUES 
('张三', '技术部', 15000.00, '2023-01-15'),
('李四', '市场部', 12000.00, '2023-03-20');

-- 更新数据
UPDATE employees 
SET salary = salary * 1.1  -- 涨薪10%
WHERE department = '技术部';

-- 删除数据
DELETE FROM employees 
WHERE hire_date < '2020-01-01';

-- 清空表（删除所有数据）
TRUNCATE TABLE employees;  -- 比DELETE快，不可回滚

三、DQL（数据查询语言）

作用：查询数据库中的数据

常用语句：

-- 基本查询
SELECT * FROM employees;

-- 选择特定列
SELECT name, department, salary FROM employees;

-- 条件查询
SELECT * FROM employees 
WHERE salary > 10000 AND department = '技术部';

-- 排序
SELECT * FROM employees 
ORDER BY salary DESC, name ASC;

-- 分组聚合
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    SUM(salary) as total_salary
FROM employees
GROUP BY department;

-- 分组后筛选
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 10000;

-- 连接查询
SELECT 
    e.name,
    e.salary,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 子查询
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- 分页查询
SELECT * FROM employees
LIMIT 10 OFFSET 20;  -- MySQL
-- 或
SELECT * FROM employees
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- SQL标准

四、DCL（数据控制语言）

作用：控制数据库访问权限和事务

常用语句：

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 授予权限
GRANT SELECT, INSERT ON mydb.employees TO 'user1'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';

-- 撤销权限
REVOKE INSERT ON mydb.employees FROM 'user1'@'localhost';

-- 删除用户
DROP USER 'user1'@'localhost';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;

-- 回滚到保存点
ROLLBACK TO sp1;

使用场景总结

实战示例

-- 1. 创建数据库和表（DDL）
CREATE DATABASE company;
USE company;

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 2. 插入数据（DML）
INSERT INTO departments VALUES 
(1, '技术部'),
(2, '市场部'),
(3, '财务部');

INSERT INTO employees (emp_name, dept_id, salary) VALUES
('王五', 1, 8000.00),
('赵六', 2, 7500.00),
('孙七', 1, 9000.00);

-- 3. 查询数据（DQL）
SELECT 
    d.dept_name,
    COUNT(e.emp_id) as emp_count,
    AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING AVG(e.salary) > 7000
ORDER BY avg_salary DESC;

-- 4. 创建视图（DDL）
CREATE VIEW high_salary_employees AS
SELECT emp_name, salary, dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE salary > 8000;

-- 5. 事务控制（DCL）
START TRANSACTION;

UPDATE employees 
SET salary = salary * 1.05 
WHERE dept_id = 1;

-- 如果检查无误则提交
COMMIT;
-- 如果发现问题则回滚
-- ROLLBACK;

-- 6. 权限管理（DCL）
GRANT SELECT, UPDATE ON company.employees TO 'manager'@'localhost';
REVOKE UPDATE ON company.employees FROM 'assistant'@'localhost';

注意事项

这些分类帮助开发者更好地组织SQL代码，理解不同语句的作用和使用场景。