SQL 四大语言分类中DDL、DML、DCL、DQL的使用
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、DDL (Data Definition Language) 数据定义语言</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">主要命令：</a></li></ul><li><a href="#_label1">二、DML (Data Manipulation Language) 数据操作语言</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_1">主要命令：</a></li></ul><li><a href="#_label2">三、DQL (Data Query Language) 数据查询语言</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_2">主要命令：</a></li></ul><li><a href="#_label3">四、DCL (Data Control Language) 数据控制语言</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_3">主要命令：</a></li></ul><li><a href="#_label4">五、四种语言对比总结</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">六、实际应用示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_4">场景：学生管理系统操作</a></li></ul></ul></div><p>SQL（结构化查询语言）通常被分为四种主要类型，每种类型负责不同的数据库操作。下面我将详细介绍这四类SQL语言的语法和用途。</p><p class="maodian"><a name="_label0"></a></p><h2>一、DDL (Data Definition Language) 数据定义语言</h2>
<p><strong>功能</strong>：定义和管理数据库对象结构（表、视图、索引等）</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><p class="maodian"><a name="_lab2_1_1"></a></p><p class="maodian"><a name="_lab2_2_2"></a></p><p class="maodian"><a name="_lab2_3_3"></a></p><h3>主要命令：</h3>
<p><strong>CREATE</strong> - 创建数据库对象</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建数据库
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;</pre></div>
<p><strong>ALTER</strong> - 修改数据库对象</p>
<div class="jb51code"><pre class="brush:sql;">-- 添加列
ALTER TABLE students ADD COLUMN gender CHAR(1);
-- 修改列类型
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
-- 删除列
ALTER TABLE students DROP COLUMN gender;</pre></div>
<p><strong>DROP</strong> - 删除数据库对象</p>
<div class="jb51code"><pre class="brush:sql;">DROP TABLE IF EXISTS temp_students;
DROP VIEW student_view;</pre></div>
<p><strong>TRUNCATE</strong> - 清空表数据（保留结构）</p>
<div class="jb51code"><pre class="brush:sql;">TRUNCATE TABLE log_data;</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>二、DML (Data Manipulation Language) 数据操作语言</h2>
<p><strong>功能</strong>：操作数据库中的数据记录</p>
<h3>主要命令：</h3>
<p><strong>INSERT</strong> - 插入数据</p>
<div class="jb51code"><pre class="brush:sql;">-- 插入单条记录
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;</pre></div>
<p><strong>UPDATE</strong> - 更新数据</p>
<div class="jb51code"><pre class="brush:sql;">-- 更新单列
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;</pre></div>
<p><strong>DELETE</strong> - 删除数据</p>
<div class="jb51code"><pre class="brush:sql;">-- 删除特定记录
DELETE FROM students WHERE id = 10;
-- 删除所有记录
DELETE FROM temp_students;
-- 使用子查询删除
DELETE FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '毕业班');</pre></div>
<p><strong>MERGE</strong> - 合并操作（UPSERT）</p>
<div class="jb51code"><pre class="brush:sql;">-- 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;</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、DQL (Data Query Language) 数据查询语言</h2>
<p><strong>功能</strong>：查询数据库中的数据</p>
<h3>主要命令：</h3>
<p><strong>SELECT</strong> - 查询数据</p>
<div class="jb51code"><pre class="brush:sql;">-- 基本查询
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标准</pre></div>
<p><strong>WITH (CTE)</strong> - 公用表表达式</p>
<div class="jb51code"><pre class="brush:sql;">WITH top_students AS (
SELECT * FROM students ORDER BY score DESC LIMIT 10
)
SELECT * FROM top_students WHERE gender = 'F';</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>四、DCL (Data Control Language) 数据控制语言</h2>
<p><strong>功能</strong>：控制数据库访问权限和事务处理</p>
<h3>主要命令：</h3>
<p><strong>GRANT</strong> - 授予权限</p>
<div class="jb51code"><pre class="brush:sql;">-- 授予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;</pre></div>
<p><strong>REVOKE</strong> - 撤销权限</p>
<div class="jb51code"><pre class="brush:sql;">-- 撤销权限
REVOKE INSERT ON students FROM user2;
-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database.* FROM 'old_admin'@'localhost';</pre></div>
<p><strong>COMMIT</strong> - 提交事务</p>
<div class="jb51code"><pre class="brush:sql;">BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;</pre></div>
<p><strong>ROLLBACK</strong> - 回滚事务</p>
<div class="jb51code"><pre class="brush:sql;">BEGIN TRANSACTION;
DELETE FROM orders WHERE status = 'pending';
-- 发现错误
ROLLBACK;</pre></div>
<p><strong>SAVEPOINT</strong> - 设置保存点</p>
<div class="jb51code"><pre class="brush:sql;">BEGIN TRANSACTION;
INSERT INTO log (message) VALUES ('Operation started');
SAVEPOINT sp1;
UPDATE data SET value = 10 WHERE id = 1;
-- 部分回滚
ROLLBACK TO SAVEPOINT sp1;
COMMIT;</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>五、四种语言对比总结</h2>
<table><thead><tr><th>类别</th><th>全称</th><th>主要功能</th><th>常用命令</th><th>特点</th></tr></thead><tbody><tr><td>DDL</td><td>Data Definition Language</td><td>定义数据结构</td><td>CREATE, ALTER, DROP, TRUNCATE</td><td>自动提交，不可回滚</td></tr><tr><td>DML</td><td>Data Manipulation Language</td><td>操作数据记录</td><td>INSERT, UPDATE, DELETE, MERGE</td><td>需要显式提交，可回滚</td></tr><tr><td>DQL</td><td>Data Query Language</td><td>查询数据</td><td>SELECT, WITH</td><td>不改变数据，只检索</td></tr><tr><td>DCL</td><td>Data Control Language</td><td>权限控制</td><td>GRANT, REVOKE, COMMIT, ROLLBACK</td><td>管理访问和事务</td></tr></tbody></table>
<p class="maodian"><a name="_label5"></a></p><h2>六、实际应用示例</h2>
<p class="maodian"><a name="_lab2_5_4"></a></p><h3>场景：学生管理系统操作</h3>
<div class="jb51code"><pre class="brush:sql;">-- 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;</pre></div>
頁:
[1]