第124集 SQL增删改查

一、课程导入

在前面的课程中，我们已经学习了SQLite数据库的基本操作和SQL基础语法。今天，我们将深入学习SQL的核心功能——增删改查（CRUD）操作。CRUD是数据库操作的四大基本功能，包括：

  • Create（创建）：向数据库表中插入新的数据记录
  • Read（读取）：从数据库表中查询数据
  • Update（更新）：修改数据库表中已有的数据记录
  • Delete（删除）：删除数据库表中的数据记录

这些操作是数据库编程中最常用的功能，掌握它们是成为数据库开发人员的必备技能。

二、增删改查的重要性

CRUD操作是数据库应用程序的核心，几乎所有与数据相关的应用都离不开这些操作。例如：

  • 社交媒体应用中发布新帖子（Create）、查看他人帖子（Read）、编辑自己的帖子（Update）、删除不当内容（Delete）
  • 电子商务网站中添加商品（Create）、查询商品（Read）、修改商品价格（Update）、下架商品（Delete）
  • 学生管理系统中添加学生信息（Create）、查询学生成绩（Read）、更新学生档案（Update）、删除毕业学生（Delete）

三、Create（创建）操作详解

Create操作通过SQL的INSERT语句实现，用于向数据库表中插入新的数据记录。

3.1 基本插入语法

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

示例：

INSERT INTO students (name, age, gender, email) VALUES ('张三', 18, '男', 'zhangsan@example.com');

3.2 插入全部列

如果要插入表中的所有列，可以省略列名列表，但需要确保值的顺序与表结构中列的顺序一致。

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

示例：

-- 假设students表的列顺序为：id, name, age, gender, email
INSERT INTO students VALUES (1, '张三', 18, '男', 'zhangsan@example.com');

3.3 批量插入

使用INSERT语句可以一次插入多条记录，这比逐条插入效率更高。

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

示例：

INSERT INTO students (name, age, gender, email) VALUES 
('李四', 19, '男', 'lisi@example.com'),
('王五', 20, '女', 'wangwu@example.com'),
('赵六', 21, '男', 'zhaoliu@example.com');

3.4 插入默认值

如果表中的列设置了默认值，可以使用DEFAULT关键字来插入默认值。

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

示例：

-- 假设students表的created_at列有默认值CURRENT_TIMESTAMP
INSERT INTO students (name, age, created_at) VALUES ('孙七', 22, DEFAULT);

3.5 从其他表插入数据

可以使用INSERT ... SELECT语句从一个表向另一个表插入数据。

INSERT INTO 目标表 (列1, 列2, ...) 
SELECT 列1, 列2, ... FROM 源表 WHERE 条件;

示例：

-- 从old_students表向new_students表插入年龄大于20的学生
INSERT INTO new_students (name, age, gender, email) 
SELECT name, age, gender, email FROM old_students WHERE age > 20;

四、Read（读取）操作详解

Read操作通过SQL的SELECT语句实现，用于从数据库表中查询数据。

4.1 基本查询语法

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

示例：

-- 查询students表中所有学生的姓名和年龄
SELECT name, age FROM students;

4.2 查询所有列

使用*可以查询表中的所有列。

SELECT * FROM 表名;

示例：

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

4.3 条件查询

使用WHERE子句可以筛选满足特定条件的数据。

SELECT 列1, 列2, ... FROM 表名 WHERE 条件;

示例：

-- 查询年龄大于20的学生
SELECT * FROM students WHERE age > 20;

4.4 排序查询

使用ORDER BY子句可以对查询结果进行排序。

SELECT 列1, 列2, ... FROM 表名 ORDER BY 排序列 ASC|DESC;

示例：

-- 按年龄降序查询学生
SELECT * FROM students ORDER BY age DESC;

4.5 限制查询结果

使用LIMIT子句可以限制查询结果的数量。

SELECT 列1, 列2, ... FROM 表名 LIMIT 数量 OFFSET 偏移量;

示例：

-- 查询年龄最大的3个学生
SELECT * FROM students ORDER BY age DESC LIMIT 3;

-- 查询第4到第6个学生（偏移量从0开始）
SELECT * FROM students ORDER BY id ASC LIMIT 3 OFFSET 3;

4.6 去重查询

使用DISTINCT关键字可以去除查询结果中的重复记录。

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

示例：

-- 查询students表中所有不同的性别
SELECT DISTINCT gender FROM students;

4.7 聚合查询

使用聚合函数可以对数据进行统计计算。

SELECT 聚合函数(列) FROM 表名;

常用聚合函数：

  • COUNT()：计算记录数
  • SUM()：计算数值总和
  • AVG()：计算平均值
  • MAX()：找出最大值
  • MIN()：找出最小值

示例：

-- 计算学生总数
SELECT COUNT(*) FROM students;

-- 计算学生的平均年龄
SELECT AVG(age) FROM students;

4.8 分组查询

使用GROUP BY子句可以将数据分组，然后对每组数据进行聚合计算。

SELECT 分组列, 聚合函数(列) FROM 表名 GROUP BY 分组列;

示例：

-- 按性别分组，计算每个性别的学生人数
SELECT gender, COUNT(*) FROM students GROUP BY gender;

4.9 分组后过滤

使用HAVING子句可以对分组后的结果进行过滤。

SELECT 分组列, 聚合函数(列) FROM 表名 GROUP BY 分组列 HAVING 条件;

示例：

-- 按性别分组，计算每个性别的学生人数，并筛选出人数大于2的性别
SELECT gender, COUNT(*) FROM students GROUP BY gender HAVING COUNT(*) > 2;

五、Update（更新）操作详解

Update操作通过SQL的UPDATE语句实现，用于修改数据库表中已有的数据记录。

5.1 基本更新语法

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

示例：

-- 将张三的年龄修改为25
UPDATE students SET age = 25 WHERE name = '张三';

5.2 批量更新

使用UPDATE语句可以一次更新多条记录，只需在WHERE子句中指定合适的条件。

示例：

-- 将所有18岁学生的年龄修改为19
UPDATE students SET age = 19 WHERE age = 18;

5.3 基于其他列的值更新

可以使用表中其他列的值来更新指定列。

示例：

-- 将所有学生的年龄增加1
UPDATE students SET age = age + 1;

5.4 安全更新的重要性

在使用UPDATE语句时，一定要加上WHERE子句，否则会更新表中的所有记录，这可能导致严重的数据损失。

-- 危险！这会更新students表中的所有记录
UPDATE students SET age = 20;

5.5 使用事务确保更新的安全性

对于重要的更新操作，建议使用事务来确保数据的一致性和完整性。

示例：

BEGIN TRANSACTION;
UPDATE students SET age = 25 WHERE name = '张三';
UPDATE students SET age = 26 WHERE name = '李四';
COMMIT;

六、Delete（删除）操作详解

Delete操作通过SQL的DELETE语句实现，用于删除数据库表中的数据记录。

6.1 基本删除语法

DELETE FROM 表名 WHERE 条件;

示例：

-- 删除名为张三的学生
DELETE FROM students WHERE name = '张三';

6.2 批量删除

使用DELETE语句可以一次删除多条记录，只需在WHERE子句中指定合适的条件。

示例：

-- 删除所有年龄大于30的学生
DELETE FROM students WHERE age > 30;

6.3 清空表

使用DELETE FROM 表名可以删除表中的所有记录，但保留表结构。

示例：

-- 删除students表中的所有记录
DELETE FROM students;

6.4 安全删除的重要性

与UPDATE语句类似，在使用DELETE语句时，一定要加上WHERE子句，否则会删除表中的所有记录。

-- 危险！这会删除students表中的所有记录
DELETE FROM students;

6.5 使用事务确保删除的安全性

对于重要的删除操作，建议使用事务来确保数据的一致性和完整性。

示例：

BEGIN TRANSACTION;
DELETE FROM students WHERE id = 1;
DELETE FROM students WHERE id = 2;
COMMIT;

七、Python中的CRUD操作示例

下面我们将学习如何在Python中使用sqlite3模块执行CRUD操作。

7.1 插入数据

import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('school.db')
cursor = conn.cursor()

# 插入单条记录
cursor.execute('INSERT INTO students (name, age, gender, email) VALUES (?, ?, ?, ?)', 
              ('张三', 18, '男', 'zhangsan@example.com'))
print(f"插入了 {cursor.rowcount} 条记录")

# 插入多条记录
students = [
    ('李四', 19, '男', 'lisi@example.com'),
    ('王五', 20, '女', 'wangwu@example.com'),
    ('赵六', 21, '男', 'zhaoliu@example.com')
]
cursor.executemany('INSERT INTO students (name, age, gender, email) VALUES (?, ?, ?, ?)', students)
print(f"批量插入了 {cursor.rowcount} 条记录")

# 提交事务
conn.commit()

# 关闭连接
cursor.close()
conn.close()

7.2 查询数据

import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('school.db')
cursor = conn.cursor()

# 查询所有学生
cursor.execute('SELECT * FROM students')
students = cursor.fetchall()
print("所有学生:")
for student in students:
    print(student)

# 条件查询
cursor.execute('SELECT * FROM students WHERE age > 19')
old_students = cursor.fetchall()
print("\n年龄大于19的学生:")
for student in old_students:
    print(student)

# 排序查询
cursor.execute('SELECT * FROM students ORDER BY age DESC')
sorted_students = cursor.fetchall()
print("\n按年龄降序排列的学生:")
for student in sorted_students:
    print(student)

# 关闭连接
cursor.close()
conn.close()

7.3 更新数据

import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('school.db')
cursor = conn.cursor()

# 更新数据
cursor.execute('UPDATE students SET age = ? WHERE name = ?', (25, '张三'))
print(f"更新了 {cursor.rowcount} 条记录")

# 提交事务
conn.commit()

# 验证更新结果
cursor.execute('SELECT * FROM students WHERE name = ?', ('张三',))
student = cursor.fetchone()
print(f"更新后的张三信息: {student}")

# 关闭连接
cursor.close()
conn.close()

7.4 删除数据

import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('school.db')
cursor = conn.cursor()

# 删除数据
cursor.execute('DELETE FROM students WHERE name = ?', ('张三',))
print(f"删除了 {cursor.rowcount} 条记录")

# 提交事务
conn.commit()

# 验证删除结果
cursor.execute('SELECT * FROM students')
students = cursor.fetchall()
print(f"删除后剩余学生数量: {len(students)}")
for student in students:
    print(student)

# 关闭连接
cursor.close()
conn.close()

八、增删改查的最佳实践

  1. 使用参数化查询：避免SQL注入攻击
  2. 添加适当的WHERE子句：防止误操作影响所有记录
  3. 使用事务：确保数据的一致性和完整性
  4. 及时提交或回滚：避免长时间占用数据库资源
  5. 备份重要数据：在执行批量操作前先备份
  6. 使用索引：提高查询效率
  7. 优化查询：避免不必要的全表扫描
  8. 限制返回的数据量：使用LIMIT子句减少网络传输

九、常见错误和解决方案

  1. SQL注入攻击

    • 错误：直接拼接SQL语句
    • 解决方案：使用参数化查询

  2. 更新或删除所有记录

    • 错误：忘记添加WHERE子句
    • 解决方案：始终添加WHERE子句，并在执行前仔细检查条件

  3. 数据类型不匹配

    • 错误：插入或更新的数据类型与表结构不匹配
    • 解决方案：确保数据类型一致，使用类型转换函数

  4. 主键冲突

    • 错误：插入的记录主键值已存在
    • 解决方案：使用自动增量主键，或检查主键值是否已存在

  5. 外键约束错误

    • 错误：插入或更新的数据违反外键约束
    • 解决方案：确保引用的外键值存在

十、课程总结

在本集课程中，我们详细学习了SQL的增删改查（CRUD）操作：

  1. Create：使用INSERT语句向数据库表中插入数据，包括单条插入、批量插入等
  2. Read：使用SELECT语句查询数据，包括条件查询、排序查询、聚合查询等
  3. Update：使用UPDATE语句修改数据，注意加上WHERE子句避免误操作
  4. Delete：使用DELETE语句删除数据，同样需要注意WHERE子句的使用

同时，我们还学习了Python中如何使用sqlite3模块执行这些操作，以及增删改查的最佳实践和常见错误的解决方案。

掌握增删改查是数据库编程的基础，希望同学们能够多做练习，熟练掌握这些操作。在后续的课程中，我们将学习如何连接MySQL等其他数据库，以及更高级的数据库操作技术。

十一、课后练习

  1. 创建一个员工表（id, name, department, salary, hire_date），包含以下字段：

    • id：员工ID，主键，自动增量
    • name：员工姓名，不允许为空
    • department：部门名称
    • salary：工资
    • hire_date：入职日期，默认值为当前日期

  2. 向员工表中插入5条记录

  3. 查询工资大于5000的员工

  4. 查询每个部门的员工人数和平均工资

  5. 将部门为"技术部"的员工工资增加10%

  6. 删除入职日期超过5年的员工

  7. 使用Python实现上述所有操作，并添加适当的错误处理

« 上一篇 SQL基础语法 下一篇 » MySQL数据库连接
↑