全栈进阶指南

皮肤切换

第 7 章：视图与存储过程 ​

在日常的数据库操作中，我们经常会遇到需要重复执行的复杂查询，或者需要封装一些业务逻辑的情况。MySQL 提供了视图和存储过程来解决这些问题。视图可以简化复杂查询，存储过程可以封装业务逻辑。

本章内容概要 ​

  • 视图的概念与作用
  • 创建、修改、删除视图
  • 视图的优缺点
  • 什么是存储过程？
  • 创建与调用存储过程
  • 存储过程中的变量与参数
  • 存储函数
  • 触发器基础
  • 实战练习：创建常用视图和存储过程

视图的概念与作用 ​

视图（View）是一种虚拟表，它基于 SQL 语句的结果集。视图包含行和列，就像真实的表一样，但视图中的数据来自于一个或多个真实的表。

视图的优点 ​

  1. 简化复杂查询 - 将复杂的 SQL 查询封装成简单的视图
  2. 数据安全 - 只暴露需要的列和行，隐藏敏感数据
  3. 逻辑独立性 - 当底层表结构发生变化时，只需修改视图定义
  4. 重用性 - 同一个视图可以被多个应用程序使用

视图的缺点 ​

  1. 性能开销 - 查询视图时需要执行底层的 SQL 语句
  2. 更新限制 - 并非所有视图都可以更新
  3. 调试困难 - 视图的错误可能难以追踪

创建、修改、删除视图 ​

创建视图 ​

sql
USE student_management;

-- 创建学生基本信息视图
CREATE VIEW v_student_basic AS
SELECT 
    id,
    student_number AS 学号,
    name AS 姓名,
    gender AS 性别,
    major AS 专业,
    class_name AS 班级
FROM students
WHERE is_active = TRUE;

-- 创建学生成绩统计视图
CREATE VIEW v_student_statistics AS
SELECT 
    s.id AS student_id,
    s.student_number AS 学号,
    s.name AS 姓名,
    s.major AS 专业,
    COUNT(g.id) AS 选课数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.student_number, s.name, s.major;

-- 创建课程成绩统计视图
CREATE VIEW v_course_statistics AS
SELECT 
    c.id AS course_id,
    c.course_code AS 课程代码,
    c.course_name AS 课程名称,
    c.credits AS 学分,
    COUNT(g.id) AS 参考人数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    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_code, c.course_name, c.credits;

使用视图 ​

sql
-- 查询所有在读学生基本信息
SELECT * FROM v_student_basic;

-- 查询平均分在85分以上的学生
SELECT * FROM v_student_statistics 
WHERE 平均分 >= 85
ORDER BY 平均分 DESC;

-- 查询平均分低于70分的课程
SELECT * FROM v_course_statistics 
WHERE 平均分 < 70
ORDER BY 平均分 ASC;

修改视图 ​

sql
-- 修改视图定义
ALTER VIEW v_student_basic AS
SELECT 
    id,
    student_number AS 学号,
    name AS 姓名,
    gender AS 性别,
    major AS 专业,
    class_name AS 班级,
    enrollment_date AS 入学日期
FROM students
WHERE is_active = TRUE;

删除视图 ​

sql
-- 删除视图
DROP VIEW IF EXISTS v_student_basic;

-- 删除多个视图
DROP VIEW IF EXISTS v_student_basic, v_student_statistics;

可更新视图 ​

某些视图是可以更新的，但需要满足特定条件：

sql
-- 创建可更新视图
CREATE VIEW v_active_students AS
SELECT id, student_number, name, major
FROM students
WHERE is_active = TRUE;

-- 通过视图更新数据
UPDATE v_active_students 
SET major = '计算机科学与技术' 
WHERE student_number = '2023001';

-- 通过视图插入数据
INSERT INTO v_active_students (student_number, name, major) 
VALUES ('2023008', '新学生', '软件工程');

-- 通过视图删除数据
DELETE FROM v_active_students 
WHERE student_number = '2023008';

什么是存储过程？ ​

存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集合，它存储在数据库中，可以被应用程序调用执行。

存储过程的优点 ​

  1. 提高性能 - 预编译，执行速度快
  2. 减少网络流量 - 只需传递存储过程名和参数
  3. 增强安全性 - 可以控制用户对数据的访问
  4. 代码重用 - 可以被多个应用程序调用
  5. 模块化 - 将复杂的业务逻辑封装起来

存储过程的缺点 ​

  1. 调试困难 - 比普通 SQL 语句难调试
  2. 移植性差 - 不同数据库的存储过程语法不同
  3. 占用服务器资源 - 存储过程在数据库服务器上执行

创建与调用存储过程 ​

创建简单的存储过程 ​

sql
-- 创建一个简单的存储过程
DELIMITER //

CREATE PROCEDURE sp_get_all_students()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_get_all_students();

带参数的存储过程 ​

sql
-- 创建带输入参数的存储过程
DELIMITER //

CREATE PROCEDURE sp_get_students_by_major(IN major_name VARCHAR(50))
BEGIN
    SELECT * FROM students WHERE major = major_name;
END //

DELIMITER ;

-- 调用带参数的存储过程
CALL sp_get_students_by_major('计算机科学与技术');

带输出参数的存储过程 ​

sql
-- 创建带输出参数的存储过程
DELIMITER //

CREATE PROCEDURE sp_get_student_count_by_major(
    IN major_name VARCHAR(50),
    OUT student_count INT
)
BEGIN
    SELECT COUNT(*) INTO student_count 
    FROM students 
    WHERE major = major_name;
END //

DELIMITER ;

-- 调用带输出参数的存储过程
CALL sp_get_student_count_by_major('计算机科学与技术', @count);
SELECT @count AS 学生人数;

带输入输出参数的存储过程 ​

sql
-- 创建带输入输出参数的存储过程
DELIMITER //

CREATE PROCEDURE sp_update_student_major(
    INOUT student_id INT,
    IN new_major VARCHAR(50)
)
BEGIN
    UPDATE students 
    SET major = new_major 
    WHERE id = student_id;
    
    SELECT name INTO student_id 
    FROM students 
    WHERE id = student_id;
END //

DELIMITER ;

-- 调用带输入输出参数的存储过程
SET @student_id = 1;
CALL sp_update_student_major(@student_id, '软件工程');
SELECT @student_id AS 学生姓名;

存储过程中的变量与控制结构 ​

变量声明和使用 ​

sql
-- 创建包含变量的存储过程
DELIMITER //

CREATE PROCEDURE sp_student_statistics()
BEGIN
    DECLARE total_students INT DEFAULT 0;
    DECLARE avg_grade DECIMAL(5,2) DEFAULT 0;
    DECLARE max_grade DECIMAL(5,2) DEFAULT 0;
    
    SELECT COUNT(*) INTO total_students FROM students;
    SELECT AVG(grade) INTO avg_grade FROM grades;
    SELECT MAX(grade) INTO max_grade FROM grades;
    
    SELECT 
        total_students AS 总学生数,
        avg_grade AS 平均成绩,
        max_grade AS 最高成绩;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_student_statistics();

条件控制结构 ​

sql
-- 创建包含条件判断的存储过程
DELIMITER //

CREATE PROCEDURE sp_get_grade_level(IN student_id INT)
BEGIN
    DECLARE avg_grade DECIMAL(5,2) DEFAULT 0;
    DECLARE grade_level VARCHAR(20) DEFAULT '';
    
    SELECT AVG(grade) INTO avg_grade
    FROM grades
    WHERE grades.student_id = student_id;
    
    IF avg_grade >= 90 THEN
        SET grade_level = '优秀';
    ELSEIF avg_grade >= 80 THEN
        SET grade_level = '良好';
    ELSEIF avg_grade >= 70 THEN
        SET grade_level = '中等';
    ELSEIF avg_grade >= 60 THEN
        SET grade_level = '及格';
    ELSE
        SET grade_level = '不及格';
    END IF;
    
    SELECT grade_level AS 成绩等级;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_get_grade_level(1);

循环控制结构 ​

sql
-- 创建包含循环的存储过程
DELIMITER //

CREATE PROCEDURE sp_insert_test_students()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 10 DO
        INSERT INTO students (student_number, name, enrollment_date, major)
        VALUES (
            CONCAT('TEST', LPAD(i, 3, '0')),
            CONCAT('测试学生', i),
            CURDATE(),
            '测试专业'
        );
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_insert_test_students();

存储函数 ​

存储函数（Stored Function）与存储过程类似，但函数必须返回一个值。

创建存储函数 ​

sql
-- 创建计算平均分的函数
DELIMITER //

CREATE FUNCTION fn_get_avg_grade(student_id INT)
RETURNS DECIMAL(5,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE avg_grade DECIMAL(5,2) DEFAULT 0;
    
    SELECT AVG(grade) INTO avg_grade
    FROM grades
    WHERE grades.student_id = student_id;
    
    RETURN IFNULL(avg_grade, 0);
END //

DELIMITER ;

-- 使用存储函数
SELECT 
    name AS 学生姓名,
    fn_get_avg_grade(id) AS 平均分
FROM students
ORDER BY 平均分 DESC;

创建复杂函数 ​

sql
-- 创建获取学生等级的函数
DELIMITER //

CREATE FUNCTION fn_get_student_level(student_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE avg_grade DECIMAL(5,2) DEFAULT 0;
    
    SELECT AVG(grade) INTO avg_grade
    FROM grades
    WHERE grades.student_id = student_id;
    
    IF avg_grade >= 90 THEN
        RETURN '优秀';
    ELSEIF avg_grade >= 80 THEN
        RETURN '良好';
    ELSEIF avg_grade >= 70 THEN
        RETURN '中等';
    ELSEIF avg_grade >= 60 THEN
        RETURN '及格';
    ELSE
        RETURN '不及格';
    END IF;
END //

DELIMITER ;

-- 使用复杂函数
SELECT 
    name AS 学生姓名,
    fn_get_avg_grade(id) AS 平均分,
    fn_get_student_level(id) AS 等级
FROM students
ORDER BY 平均分 DESC;

触发器基础 ​

触发器（Trigger）是一种特殊的存储程序，它在特定的事件（INSERT、UPDATE、DELETE）发生时自动执行。

创建触发器 ​

sql
-- 创建插入学生时的触发器
DELIMITER //

CREATE TRIGGER tr_before_insert_student
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    -- 确保学号不为空
    IF NEW.student_number IS NULL OR NEW.student_number = '' THEN
        SET NEW.student_number = CONCAT('AUTO', UNIX_TIMESTAMP());
    END IF;
    
    -- 设置默认入学日期
    IF NEW.enrollment_date IS NULL THEN
        SET NEW.enrollment_date = CURDATE();
    END IF;
END //

DELIMITER ;

-- 创建更新成绩时的触发器
DELIMITER //

CREATE TRIGGER tr_after_update_grade
AFTER UPDATE ON grades
FOR EACH ROW
BEGIN
    -- 记录成绩变更日志
    INSERT INTO grade_logs (student_id, course_id, old_grade, new_grade, update_time)
    VALUES (NEW.student_id, NEW.course_id, OLD.grade, NEW.grade, NOW());
END //

DELIMITER ;

管理触发器 ​

sql
-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS tr_before_insert_student;

实战练习：创建常用视图和存储过程 ​

让我们创建一些实用的视图和存储过程来管理学生信息。

1. 创建综合视图 ​

sql
-- 创建完整的学生信息视图
CREATE VIEW v_student_full_info AS
SELECT 
    s.id AS 学生ID,
    s.student_number AS 学号,
    s.name AS 姓名,
    s.gender AS 性别,
    s.birth_date AS 出生日期,
    s.phone AS 电话,
    s.email AS 邮箱,
    s.major AS 专业,
    s.class_name AS 班级,
    s.enrollment_date AS 入学日期,
    COUNT(g.id) AS 选课数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
GROUP BY 
    s.id, s.student_number, s.name, s.gender, s.birth_date,
    s.phone, s.email, s.major, s.class_name, s.enrollment_date;

-- 使用视图查询
SELECT * FROM v_student_full_info 
ORDER BY 平均分 DESC;

2. 创建管理存储过程 ​

sql
-- 创建添加学生成绩的存储过程
DELIMITER //

CREATE PROCEDURE sp_add_student_grade(
    IN student_id INT,
    IN course_id INT,
    IN grade_value DECIMAL(5,2),
    IN exam_date DATE,
    IN semester VARCHAR(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO grades (student_id, course_id, grade, exam_date, semester)
    VALUES (student_id, course_id, grade_value, exam_date, semester);
    
    COMMIT;
    
    SELECT '成绩添加成功' AS 消息;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_add_student_grade(1, 3, 87.5, '2023-12-25', '2023秋季');

3. 创建统计存储过程 ​

sql
-- 创建生成成绩报告的存储过程
DELIMITER //

CREATE PROCEDURE sp_generate_grade_report()
BEGIN
    -- 创建临时表存储报告数据
    CREATE TEMPORARY TABLE temp_report (
        统计项目 VARCHAR(50),
        统计值 VARCHAR(50)
    );
    
    -- 插入统计数据
    INSERT INTO temp_report VALUES 
    ('总学生数', (SELECT COUNT(*) FROM students)),
    ('总课程数', (SELECT COUNT(*) FROM courses)),
    ('总成绩记录数', (SELECT COUNT(*) FROM grades)),
    ('平均成绩', (SELECT ROUND(AVG(grade), 2) FROM grades)),
    ('最高成绩', (SELECT MAX(grade) FROM grades)),
    ('最低成绩', (SELECT MIN(grade) FROM grades));
    
    -- 返回报告数据
    SELECT * FROM temp_report;
    
    -- 删除临时表
    DROP TEMPORARY TABLE temp_report;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_generate_grade_report();

4. 创建查询存储过程 ​

sql
-- 创建按条件查询学生的存储过程
DELIMITER //

CREATE PROCEDURE sp_search_students(
    IN search_major VARCHAR(50),
    IN search_class VARCHAR(50),
    IN min_avg_grade DECIMAL(5,2)
)
BEGIN
    SELECT 
        s.student_number AS 学号,
        s.name AS 姓名,
        s.major AS 专业,
        s.class_name AS 班级,
        ROUND(AVG(g.grade), 2) AS 平均分
    FROM students s
    LEFT JOIN grades g ON s.id = g.student_id
    WHERE 
        (search_major IS NULL OR s.major = search_major) AND
        (search_class IS NULL OR s.class_name = search_class)
    GROUP BY s.id, s.student_number, s.name, s.major, s.class_name
    HAVING 
        (min_avg_grade IS NULL OR AVG(g.grade) >= min_avg_grade) OR
        (min_avg_grade IS NOT NULL AND AVG(g.grade) IS NULL AND min_avg_grade <= 0)
    ORDER BY 平均分 DESC;
END //

DELIMITER ;

-- 调用存储过程查询计算机专业平均分80以上的学生
CALL sp_search_students('计算机科学与技术', NULL, 80);

-- 调用存储过程查询所有平均分75以上的学生
CALL sp_search_students(NULL, NULL, 75);

小结 ​

在本章中，我们学习了 MySQL 中的视图和存储过程：

  1. 视图 - 虚拟表，用于简化复杂查询和数据安全控制
  2. 存储过程 - 预编译的 SQL 语句集合，用于封装业务逻辑
  3. 存储函数 - 必须返回值的特殊存储程序
  4. 触发器 - 在特定事件发生时自动执行的程序
  5. 变量与控制结构 - 在存储过程中使用变量和控制流程

掌握这些高级数据库特性可以帮助我们：

  • 简化复杂查询
  • 提高查询性能
  • 封装业务逻辑
  • 增强数据安全性
  • 实现自动化处理

在下一章中，我们将学习事务与并发控制，这是保证数据一致性和完整性的关键技术。

Copyright © 2025 梁欢的前后端技术笔记 鲁ICP备2025190626号