第 3 章：数据的基本操作（增删改查）

在前两章中，我们学习了数据库和表的基本操作。现在我们来学习如何对数据进行增删改查操作，这是数据库使用中最常用的功能。

本章内容概要

• INSERT 语句详解
• SELECT 语句基础
• UPDATE 语句详解
• DELETE 语句详解
• WHERE 条件筛选
• ORDER BY 排序
• LIMIT 限制结果集
• 实战练习：学生信息管理操作

INSERT 语句 - 插入数据

INSERT 语句用于向表中插入新记录。

基本语法

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

插入单行数据

让我们使用上一章创建的学生管理系统数据库进行练习：

USE student_management;

-- 插入单条学生记录
INSERT INTO students (student_number, name, gender, birth_date, phone, email, address, enrollment_date, major, class_name)
VALUES ('2023001', '张三', '男', '2005-03-15', '13800138001', 'zhangsan@example.com', '北京市朝阳区', '2023-09-01', '计算机科学与技术', '计科2301');

插入多行数据

可以一次插入多条记录：

INSERT INTO students (student_number, name, gender, birth_date, phone, email, address, enrollment_date, major, class_name)
VALUES 
('2023002', '李四', '女', '2005-07-22', '13800138002', 'lisi@example.com', '上海市浦东新区', '2023-09-01', '软件工程', '软工2301'),
('2023003', '王五', '男', '2005-11-08', '13800138003', 'wangwu@example.com', '广州市天河区', '2023-09-01', '数据科学与大数据技术', '数据2301'),
('2023004', '赵六', '女', '2005-01-30', '13800138004', 'zhaoliu@example.com', '深圳市南山区', '2023-09-01', '人工智能', 'AI2301');

插入部分字段数据

如果某些字段有默认值或允许为空，可以只插入部分字段：

INSERT INTO students (student_number, name, enrollment_date)
VALUES ('2023005', '钱七', '2023-09-01');

INSERT INTO SELECT

从其他表中复制数据：

-- 假设我们有一个临时学生表 temp_students
CREATE TABLE temp_students (
    student_number VARCHAR(20),
    name VARCHAR(50),
    enrollment_date DATE
);

INSERT INTO temp_students VALUES 
('2023006', '孙八', '2023-09-01'),
('2023007', '周九', '2023-09-01');

-- 将临时表中的数据插入到学生表中
INSERT INTO students (student_number, name, enrollment_date)
SELECT student_number, name, enrollment_date FROM temp_students;

SELECT 语句 - 查询数据

SELECT 语句用于从数据库中查询数据。

基本语法

SELECT 列名1, 列名2, ... FROM 表名;

查询所有数据

-- 查询所有学生信息
SELECT * FROM students;

查询指定列

-- 查询学生的学号、姓名和专业
SELECT student_number, name, major FROM students;

使用别名

-- 为列和表设置别名
SELECT student_number AS 学号, name AS 姓名, major AS 专业 FROM students AS 学生表;

去除重复记录

-- 查看所有专业（去除重复）
SELECT DISTINCT major FROM students;

WHERE 子句 - 条件筛选

WHERE 子句用于筛选满足条件的记录。

基本比较操作

-- 查询计算机科学与技术专业的学生
SELECT * FROM students WHERE major = '计算机科学与技术';

-- 查询入学日期在2023年之后的学生
SELECT * FROM students WHERE enrollment_date > '2023-01-01';

-- 查询年龄大于18岁的学生（通过出生日期计算）
SELECT * FROM students WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) > 18;

逻辑操作符

-- AND 操作符
SELECT * FROM students WHERE major = '计算机科学与技术' AND gender = '男';

-- OR 操作符
SELECT * FROM students WHERE major = '计算机科学与技术' OR major = '软件工程';

-- NOT 操作符
SELECT * FROM students WHERE NOT major = '计算机科学与技术';

范围查询

-- BETWEEN 操作符
SELECT * FROM students WHERE birth_date BETWEEN '2005-01-01' AND '2005-12-31';

-- IN 操作符
SELECT * FROM students WHERE major IN ('计算机科学与技术', '软件工程', '人工智能');

模糊查询

-- LIKE 操作符
SELECT * FROM students WHERE name LIKE '张%';  -- 姓张的学生
SELECT * FROM students WHERE name LIKE '%三%'; -- 名字中包含"三"的学生
SELECT * FROM students WHERE phone LIKE '138_______'; -- 138开头的11位手机号

空值查询

-- 查询电话号码为空的学生
SELECT * FROM students WHERE phone IS NULL;

-- 查询电话号码不为空的学生
SELECT * FROM students WHERE phone IS NOT NULL;

ORDER BY 子句 - 排序

ORDER BY 子句用于对查询结果进行排序。

升序和降序

-- 按姓名升序排列（默认）
SELECT * FROM students ORDER BY name;

-- 按姓名降序排列
SELECT * FROM students ORDER BY name DESC;

-- 按多个字段排序
SELECT * FROM students ORDER BY major, name;

LIMIT 子句 - 限制结果集

LIMIT 子句用于限制查询结果的数量。

-- 只查询前5条记录
SELECT * FROM students LIMIT 5;

-- 查询第6到第10条记录（分页查询）
SELECT * FROM students LIMIT 5 OFFSET 5;
-- 或者简写为
SELECT * FROM students LIMIT 5, 5;

UPDATE 语句 - 更新数据

UPDATE 语句用于修改表中的现有记录。

基本语法

UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2, ... WHERE 条件;

更新单个字段

-- 更新张三的电话号码
UPDATE students SET phone = '13900139001' WHERE name = '张三';

更新多个字段

-- 更新张三的电话号码和邮箱
UPDATE students 
SET phone = '13900139001', email = 'zhangsan_new@example.com' 
WHERE name = '张三';

更新所有记录（谨慎使用）

-- 更新所有学生的在读状态为true（谨慎使用）
UPDATE students SET is_active = TRUE;

DELETE 语句 - 删除数据

DELETE 语句用于删除表中的记录。

基本语法

DELETE FROM 表名 WHERE 条件;

删除特定记录

-- 删除姓名为钱七的学生记录
DELETE FROM students WHERE name = '钱七';

删除所有记录（谨慎使用）

-- 删除所有学生记录（谨慎使用）
DELETE FROM students;

实战练习：学生信息管理操作

让我们通过完整的例子来练习数据的增删改查操作。

1. 插入课程数据

-- 插入课程数据
INSERT INTO courses (course_code, course_name, credits, description, teacher)
VALUES 
('CS101', '计算机基础', 3, '计算机科学入门课程', '张教授'),
('CS102', '数据结构', 4, '数据结构与算法分析', '李教授'),
('CS103', '数据库原理', 3, '关系数据库设计与实现', '王教授'),
('CS104', '操作系统', 4, '操作系统原理与实践', '赵教授');

2. 查询操作练习

-- 查询所有学生信息
SELECT * FROM students;

-- 查询所有课程信息
SELECT * FROM courses;

-- 查询计算机专业的学生
SELECT * FROM students WHERE major LIKE '%计算机%';

-- 查询按入学日期排序的学生列表
SELECT student_number, name, major, enrollment_date 
FROM students 
ORDER BY enrollment_date;

-- 查询前3名学生
SELECT * FROM students LIMIT 3;

3. 更新操作练习

-- 更新张三的专业为计算机科学与技术
UPDATE students 
SET major = '计算机科学与技术', class_name = '计科2301' 
WHERE name = '张三' AND student_number = '2023001';

-- 更新所有学生的更新时间为当前时间
UPDATE students SET updated_at = CURRENT_TIMESTAMP;

4. 删除操作练习

-- 删除临时学生表中的数据
DELETE FROM temp_students;

-- 删除特定学生（假设我们添加了一个测试学生）
INSERT INTO students (student_number, name, enrollment_date) 
VALUES ('TEST001', '测试学生', '2023-09-01');

DELETE FROM students WHERE student_number = 'TEST001';

5. 综合查询练习

-- 查询学生数量
SELECT COUNT(*) AS 学生总数 FROM students;

-- 查询各专业学生人数
SELECT major AS 专业, COUNT(*) AS 人数 
FROM students 
GROUP BY major;

-- 查询有邮箱的学生人数
SELECT COUNT(*) AS 有邮箱学生数 FROM students WHERE email IS NOT NULL;

-- 查询最新的5条学生记录
SELECT * FROM students ORDER BY created_at DESC LIMIT 5;

注意事项和最佳实践

1. WHERE 子句的重要性

在执行 UPDATE 和 DELETE 操作时，务必使用 WHERE 子句，否则会更新或删除所有记录。

2. 使用事务保护重要操作

对于重要的数据操作，建议使用事务：

START TRANSACTION;

UPDATE students SET major = '计算机科学与技术' WHERE student_number = '2023001';
INSERT INTO grades (student_id, course_id, grade) VALUES (1, 1, 85.5);

COMMIT; -- 或者 ROLLBACK;

3. 备份重要数据

在执行大规模数据操作前，建议先备份数据。

4. 使用 LIMIT 保护性查询

在不确定查询结果数量时，可以使用 LIMIT 限制结果集大小：

SELECT * FROM students LIMIT 100;

小结

在本章中，我们学习了数据的基本操作：

1. INSERT - 插入数据，包括单行、多行插入和从其他表复制数据
2. SELECT - 查询数据，包括基本查询、条件筛选、排序和限制结果集
3. UPDATE - 更新数据，修改表中的现有记录
4. DELETE - 删除数据，删除表中的记录
5. WHERE 条件 - 使用各种条件筛选数据
6. ORDER BY - 对查询结果进行排序
7. LIMIT - 限制查询结果数量

下一章我们将学习更高级的 SELECT 查询，包括聚合函数、分组查询等。