学习的好苗子

Appearance

MySQL核心操作 ​

在掌握了MySQL的基础知识后，我们现在来学习如何对数据库中的数据进行操作。这包括数据的插入、更新、删除以及查询等核心操作，这些是数据库管理的核心技能。

数据操纵语言（DML） ​

数据操纵语言（DML）用于操作数据库中的数据，主要包括插入、更新和删除数据的操作。

插入数据 ​

向表中插入数据是数据库操作中最常见的任务之一。MySQL提供了多种插入数据的方式。

单条插入 ​

最基本的插入操作是向表中插入单行数据：

sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

示例：

sql
-- 向students表插入一条记录
INSERT INTO students (student_id, name, gender, birth_date, phone, email)
VALUES ('2021001', '张三', '男', '2003-05-15', '13800138001', 'zhangsan@example.com');

-- 向courses表插入一条记录
INSERT INTO courses (course_code, course_name, credits, teacher, description)
VALUES ('CS101', '计算机科学导论', 3, '李教授', '计算机科学的基础课程');

注意：

  1. 列名是可选的，但如果省略，必须为表中的所有列提供值
  2. 字符串值需要用单引号或双引号括起来
  3. 日期值应使用正确的日期格式

批量插入 ​

为了提高效率，可以一次插入多行数据：

sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1_1, value2_1, value3_1, ...),
(value1_2, value2_2, value3_2, ...),
(value1_3, value2_3, value3_3, ...);

示例：

sql
-- 批量插入学生记录
INSERT INTO students (student_id, name, gender, birth_date, phone, email)
VALUES 
('2021002', '李四', '女', '2002-08-20', '13800138002', 'lisi@example.com'),
('2021003', '王五', '男', '2003-12-10', '13800138003', 'wangwu@example.com'),
('2021004', '赵六', '女', '2002-03-25', '13800138004', 'zhaoliu@example.com');

-- 批量插入课程记录
INSERT INTO courses (course_code, course_name, credits, teacher)
VALUES 
('MATH101', '高等数学', 4, '张教授'),
('ENG101', '大学英语', 2, '王老师'),
('PHYS101', '大学物理', 3, '李教授');

插入查询结果 ​

还可以将一个查询的结果插入到另一个表中：

sql
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

示例：

sql
-- 创建一个临时表来存储优秀学生
CREATE TABLE excellent_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id VARCHAR(20),
    name VARCHAR(50),
    avg_grade DECIMAL(5,2)
);

-- 将平均成绩大于85的学生插入到优秀学生表中
INSERT INTO excellent_students (student_id, name, avg_grade)
SELECT s.student_id, s.name, AVG(e.grade) as avg_grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.student_id, s.name
HAVING AVG(e.grade) > 85;

更新数据 ​

更新数据用于修改表中已存在的记录。

sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

sql
-- 更新单个学生的邮箱
UPDATE students
SET email = 'zhangsan_new@example.com'
WHERE student_id = '2021001';

-- 更新多个字段
UPDATE courses
SET credits = 4, teacher = '陈教授'
WHERE course_code = 'CS101';

-- 更新满足条件的多条记录
UPDATE students
SET status = '毕业'
WHERE enrollment_date < '2020-01-01';

-- 使用表达式更新数据
UPDATE enrollments
SET grade = grade * 1.05  -- 给所有成绩增加5%
WHERE grade IS NOT NULL;

重要提示：

  1. 永远不要忘记WHERE子句：如果没有WHERE子句，UPDATE语句会更新表中的所有记录！
  2. 在执行UPDATE之前，最好先用SELECT语句检查WHERE条件是否正确。

删除数据 ​

删除数据用于从表中移除记录。

DELETE语句 ​

DELETE语句逐行删除满足条件的记录：

sql
DELETE FROM table_name WHERE condition;

示例：

sql
-- 删除特定学生
DELETE FROM students WHERE student_id = '2021001';

-- 删除满足条件的多条记录
DELETE FROM enrollments WHERE grade < 60;

-- 删除所有记录（但保留表结构）
DELETE FROM temp_table;

TRUNCATE语句 ​

TRUNCATE语句用于清空整个表，比DELETE更快，但有一些重要区别：

sql
TRUNCATE TABLE table_name;

示例：

sql
-- 清空临时表
TRUNCATE TABLE temp_data;

DELETE和TRUNCATE的主要区别：

  1. 速度：TRUNCATE比DELETE快
  2. 事务：TRUNCATE不能回滚（在某些存储引擎中）
  3. 触发器：TRUNCATE不会触发DELETE触发器
  4. 自增ID：TRUNCATE会重置自增ID为初始值

重要提示：

  1. 谨慎使用TRUNCATE：它会删除表中的所有数据且无法恢复
  2. 永远不要忘记WHERE子句：DELETE语句没有WHERE子句会删除所有记录

约束 ​

约束用于确保数据的完整性和一致性。

主键约束（PRIMARY KEY） ​

主键约束确保表中的每一行都有唯一的标识符：

sql
-- 在创建表时定义主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 在修改表时添加主键
ALTER TABLE temp_table ADD PRIMARY KEY (id);

非空约束（NOT NULL） ​

非空约束确保字段不能为空：

sql
-- 在创建表时定义非空约束
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,  -- 产品名称不能为空
    price DECIMAL(10,2) NOT NULL  -- 价格不能为空
);

唯一约束（UNIQUE） ​

唯一约束确保字段中的值是唯一的：

sql
-- 在创建表时定义唯一约束
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名必须唯一
    email VARCHAR(100) UNIQUE  -- 邮箱必须唯一
);

-- 在修改表时添加唯一约束
ALTER TABLE students ADD UNIQUE (student_id);

默认值约束（DEFAULT） ​

默认值约束为字段指定默认值：

sql
-- 在创建表时定义默认值
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE DEFAULT (CURRENT_DATE),  -- 默认为当前日期
    status VARCHAR(20) DEFAULT '待处理',      -- 默认状态
    amount DECIMAL(10,2) DEFAULT 0.00        -- 默认金额为0
);

外键约束（FOREIGN KEY） ​

外键约束用于建立表之间的关系：

sql
-- 在创建表时定义外键
CREATE TABLE enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 在修改表时添加外键
ALTER TABLE enrollments 
ADD CONSTRAINT fk_student 
FOREIGN KEY (student_id) REFERENCES students(id);

数据查询语言（DQL）基础 ​

数据查询语言（DQL）主要用于从数据库中检索数据，SELECT语句是DQL的核心。

基本查询 ​

最基本的SELECT语句用于从表中检索数据：

sql
-- 查询所有列
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2, column3 FROM table_name;

-- 查询并指定别名
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

示例：

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

-- 查询学生的姓名和邮箱
SELECT name, email FROM students;

-- 查询并使用别名
SELECT name AS 学生姓名, email AS 邮箱地址 FROM students;

条件查询 ​

使用WHERE子句可以根据条件筛选数据：

sql
SELECT column_list
FROM table_name
WHERE condition;

常见的比较操作符：

  • =：等于
  • !=或<>：不等于
  • >：大于
  • <：小于
  • >=：大于等于
  • <=：小于等于
  • BETWEEN：在某个范围内
  • IN：在某个列表中
  • LIKE：模式匹配
  • IS NULL：为空
  • IS NOT NULL：不为空

示例：

sql
-- 查询特定ID的学生
SELECT * FROM students WHERE student_id = '2021001';

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

-- 查询在指定范围内的学生
SELECT * FROM students WHERE age BETWEEN 18 AND 25;

-- 查询特定专业的学生
SELECT * FROM students WHERE major IN ('计算机科学', '软件工程');

-- 模糊查询（查找姓张的学生）
SELECT * FROM students WHERE name LIKE '张%';

-- 查询邮箱不为空的学生
SELECT * FROM students WHERE email IS NOT NULL;

排序 ​

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

sql
SELECT column_list
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

示例：

sql
-- 按姓名升序排列
SELECT * FROM students ORDER BY name ASC;

-- 按年龄降序排列
SELECT * FROM students ORDER BY age DESC;

-- 多列排序（先按专业升序，再按年龄降序）
SELECT * FROM students ORDER BY major ASC, age DESC;

限制结果 ​

使用LIMIT子句可以限制返回的记录数，常用于分页查询：

sql
SELECT column_list
FROM table_name
LIMIT offset, count;
-- 或者
LIMIT count OFFSET offset;

示例：

sql
-- 只返回前5条记录
SELECT * FROM students LIMIT 5;

-- 分页查询（每页10条，第1页）
SELECT * FROM students LIMIT 0, 10;
-- 或者
SELECT * FROM students LIMIT 10 OFFSET 0;

-- 分页查询（每页10条，第2页）
SELECT * FROM students LIMIT 10, 10;
-- 或者
SELECT * FROM students LIMIT 10 OFFSET 10;

去重 ​

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

sql
SELECT DISTINCT column_list FROM table_name;

示例：

sql
-- 查询所有不同的专业
SELECT DISTINCT major FROM students;

-- 查询不同专业和年级的组合
SELECT DISTINCT major, grade FROM students;

聚合函数与分组查询 ​

聚合函数用于对一组值执行计算并返回单个值。

聚合函数 ​

常用的聚合函数包括：

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

示例：

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

-- 计算所有成绩的总和
SELECT SUM(grade) AS 总分 FROM enrollments;

-- 计算平均成绩
SELECT AVG(grade) AS 平均成绩 FROM enrollments;

-- 找出最高分和最低分
SELECT MAX(grade) AS 最高分, MIN(grade) AS 最低分 FROM enrollments;

-- 计算有成绩记录的数量
SELECT COUNT(grade) AS 有成绩记录数 FROM enrollments;

分组查询 ​

使用GROUP BY子句可以将查询结果按指定列分组：

sql
SELECT column_list, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_list;

示例：

sql
-- 按专业分组统计学生人数
SELECT major, COUNT(*) AS 人数
FROM students
GROUP BY major;

-- 按课程分组计算平均成绩
SELECT course_id, AVG(grade) AS 平均成绩
FROM enrollments
GROUP BY course_id;

-- 按年级分组统计各专业学生人数
SELECT grade, major, COUNT(*) AS 人数
FROM students
GROUP BY grade, major;

过滤分组 ​

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

sql
SELECT column_list, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_list
HAVING condition;

示例：

sql
-- 查询学生人数超过10人的专业
SELECT major, COUNT(*) AS 人数
FROM students
GROUP BY major
HAVING COUNT(*) > 10;

-- 查询平均成绩超过80分的课程
SELECT course_id, AVG(grade) AS 平均成绩
FROM enrollments
GROUP BY course_id
HAVING AVG(grade) > 80;

重要提示：

  • WHERE子句在分组前过滤行
  • HAVING子句在分组后过滤分组
  • WHERE子句中不能使用聚合函数
  • HAVING子句中可以使用聚合函数

多表查询 ​

在实际应用中，数据通常存储在多个相关的表中，需要使用多表查询来获取完整的信息。

表连接类型 ​

内连接（INNER JOIN） ​

内连接返回两个表中匹配的记录：

sql
SELECT column_list
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

示例：

sql
-- 查询学生姓名和他们的成绩
SELECT s.name, c.course_name, e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

左连接（LEFT JOIN） ​

左连接返回左表的所有记录和右表中匹配的记录：

sql
SELECT column_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

示例：

sql
-- 查询所有学生及其成绩（包括没有成绩的学生）
SELECT s.name, c.course_name, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id;

右连接（RIGHT JOIN） ​

右连接返回右表的所有记录和左表中匹配的记录：

sql
SELECT column_list
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例：

sql
-- 查询所有课程及选课学生（包括没有学生选的课程）
SELECT c.course_name, s.name, e.grade
FROM courses c
RIGHT JOIN enrollments e ON c.id = e.course_id
RIGHT JOIN students s ON e.student_id = s.id;

全连接（FULL JOIN） ​

MySQL不直接支持FULL JOIN，但可以用UNION模拟：

sql
SELECT column_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT column_list
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

连接条件 ​

使用ON子句指定表之间的关联字段：

sql
-- 使用ON子句
SELECT s.name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

-- 使用USING子句（当关联字段同名时）
SELECT s.name, e.grade
FROM students s
INNER JOIN enrollments e USING(id);

自连接 ​

自连接是将同一张表视为两张表进行连接：

sql
-- 查询员工及其上级（假设employees表中有manager_id字段）
SELECT e.name AS 员工姓名, m.name AS 上级姓名
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

实践项目 ​

为了巩固所学知识，建议进行以下实践项目：

实现学生成绩管理系统的CRUD操作 ​

  1. 创建数据库和表：
sql
-- 创建学生成绩管理系统数据库
CREATE DATABASE student_grade_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE student_grade_system;

-- 创建学生表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女'),
    birth_date DATE,
    major VARCHAR(50),
    enrollment_date DATE DEFAULT (CURRENT_DATE)
);

-- 创建课程表
CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits INT DEFAULT 3,
    teacher VARCHAR(50)
);

-- 创建成绩表
CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade DECIMAL(5,2),
    exam_date DATE DEFAULT (CURRENT_DATE),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);
  1. 实现CRUD操作：
sql
-- 插入学生信息
INSERT INTO students (student_id, name, gender, birth_date, major)
VALUES 
('2021001', '张三', '男', '2003-05-15', '计算机科学'),
('2021002', '李四', '女', '2002-08-20', '软件工程');

-- 插入课程信息
INSERT INTO courses (course_code, course_name, credits, teacher)
VALUES 
('CS101', '计算机科学导论', 3, '王教授'),
('MATH101', '高等数学', 4, '李教授');

-- 插入成绩信息
INSERT INTO grades (student_id, course_id, grade)
VALUES 
(1, 1, 85.5),
(1, 2, 92.0),
(2, 1, 78.0);

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

-- 查询特定学生的成绩
SELECT s.name, c.course_name, g.grade
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
WHERE s.student_id = '2021001';

-- 更新学生成绩
UPDATE grades 
SET grade = 88.0 
WHERE student_id = 1 AND course_id = 1;

-- 删除学生成绩记录
DELETE FROM grades WHERE student_id = 2 AND course_id = 1;

编写多表联合查询 ​

sql
-- 查询每个学生的平均成绩
SELECT s.name, AVG(g.grade) AS 平均成绩
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name;

-- 查询每门课程的最高分和最低分
SELECT c.course_name, MAX(g.grade) AS 最高分, MIN(g.grade) AS 最低分
FROM courses c
LEFT JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name;

-- 查询成绩优秀的学生（平均分>85）
SELECT s.name, AVG(g.grade) AS 平均成绩
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
HAVING AVG(g.grade) > 85;

-- 查询每门课程的选课人数和平均分
SELECT c.course_name, COUNT(g.student_id) AS 选课人数, AVG(g.grade) AS 平均分
FROM courses c
LEFT JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name;

统计分析数据 ​

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

-- 统计各课程平均分
SELECT c.course_name, AVG(g.grade) AS 平均分
FROM courses c
JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name
ORDER BY 平均分 DESC;

-- 查询不及格的学生和课程
SELECT s.name, c.course_name, g.grade
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
WHERE g.grade < 60;

-- 查询优秀率（成绩>=90）统计
SELECT c.course_name, 
       COUNT(*) AS 总人数,
       SUM(CASE WHEN g.grade >= 90 THEN 1 ELSE 0 END) AS 优秀人数,
       AVG(CASE WHEN g.grade >= 90 THEN 1.0 ELSE 0.0 END) AS 优秀率
FROM courses c
JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name;

通过以上实践项目，您将掌握MySQL核心操作的各个方面，包括数据的增删改查、多表查询以及数据分析等重要技能。