-- ============================================
-- 1. 创建数据库
-- ============================================
create database if not exists school_db -- 创建名为 school_db 的数据库
    character set utf8mb4 -- 设置字符集为 utf8mb4（支持中文和表情符号）
    collate utf8mb4_unicode_ci; -- 设置校对规则

-- ============================================
-- 2. 使用数据库
-- ============================================
use school_db; -- 切换到 school_db 数据库

-- 创建学生表
create table if not exists students
(
    id              int primary key auto_increment,                                          -- 学生id，主键，自增长
    student_no      varchar(20) not null unique,                                             -- 学号，不允许为空，唯一
    name            varchar(50) not null,                                                    -- 学生姓名
    gender          enum ('男', '女') default '男',                                          -- 性别，枚举类型
    age             int check (age >= 0 and age <= 100),-- 年龄，添加检查约束
    class_id        int,                                                                     -- 班级id（外键）
    enrollment_date date,                                                                    -- 入学日期
    created_at      timestamp         default current_timestamp,                             -- 创建时间，默认为当前时间
    updated_at      timestamp         default current_timestamp on update current_timestamp, -- 更新时间，自动更新
    index idx_class_id (class_id),                                                           -- 为class_id创建索引
    index idx_name (name)                                                                    -- 为姓名创建索引
) engine = innodb
  default charset = utf8mb4; -- 使用innodb引擎，字符集utf8mb4

-- 创建班级表
create table if not exists classes
(
    class_id     int primary key auto_increment, -- 班级id，主键
    class_name   varchar(50) not null,           -- 班级名称
    major        varchar(100),                   -- 专业
    head_teacher varchar(50)                     -- 班主任
);

-- 创建成绩表
create table if not exists scores
(
    score_id    int primary key auto_increment,                          -- 成绩id，主键
    student_id  int          not null,                                   -- 学生id（外键）
    course_name varchar(100) not null,                                   -- 课程名称
    score       decimal(5, 2) check (score >= 0 and score <= 100),       -- 成绩，0-100分，保留两位小数
    exam_date   date,                                                    -- 考试日期
    foreign key (student_id) references students (id) on delete cascade, -- 外键约束，级联删除
    index idx_student_id (student_id),                                   -- 为学生id创建索引
    index idx_course (course_name)                                       -- 为课程名称创建索引
);

-- 向班级表插入数据
insert into classes (class_name, major, head_teacher)
values ('计算机2023级1班', '计算机科学与技术', '张老师'),
       ('软件工程2023级1班', '软件工程', '李老师'),
       ('网络安全2023级2班', '网络安全', '王老师');

-- 向学生表插入数据
insert into students (student_no, name, gender, age, class_id, enrollment_date)
values ('2023001', '张三', '男', 20, 1, '2023-09-01'),
       ('2023002', '李四', '女', 19, 1, '2023-09-01'),
       ('2023003', '王五', '男', 21, 2, '2023-09-01'),
       ('2023004', '赵六', '女', 20, 3, '2023-09-01'),
       ('2023005', '钱七', '男', 22, 2, '2023-09-01');

-- 向成绩表插入数据
insert into scores (student_id, course_name, score, exam_date)
values (1, '数据库原理', 85.5, '2024-01-15'),
       (1, '数据结构', 90.0, '2024-01-16'),
       (2, '数据库原理', 88.0, '2024-01-15'),
       (3, '数据结构', 76.5, '2024-01-16'),
       (4, '网络安全基础', 92.5, '2024-01-17'),
       (5, '软件工程', 81.0, '2024-01-18');

-- 查询所有学生信息
select *
from students;

-- 查询特定列
select student_no, name, gender, age
from students;

-- 条件查询：查询年龄大于20岁的学生
select *
from students
where age > 20;

-- 模糊查询：查询姓张的学生
select *
from students
where name like '张%';

-- 排序：按年龄降序排列
select *
from students
order by age desc;

-- 分组统计：统计每个班级的学生人数
select class_id, count(*) as student_count
from students
group by class_id;

-- 连接查询：查询学生及其班级信息
select s.name, s.student_no, c.class_name, c.major
from students s
         join classes c on s.class_id = c.class_id;

-- 子查询：查询成绩高于平均分的学生
select *
from scores
where score > (select avg(score) from scores);

-- 分页查询：每页2条，查询第2页
select *
from students
limit 2 offset 2;

-- 更新学生年龄
update students
set age = 21
where name = '张三';

-- 批量更新：为所有学生年龄加1（演示用，谨慎操作）
-- update students set age = age + 1;

-- 删除特定学生（假设删除学号为2023005的学生）
delete
from students
where student_no = '2023005';

-- 清空表数据（谨慎使用！会删除所有数据）
-- delete from students;

-- 插入新学生
insert into students (student_no, name, gender, age, class_id, enrollment_date)
values ('2023006', '孙八', '男', 23, 1, '2023-09-01');

-- 插入多条数据
insert into scores (student_id, course_name, score, exam_date)
values (1, '操作系统', 87.0, '2024-01-19'),
       (2, '数据结构', 91.5, '2024-01-16');

create view v_student_details as
select s.id,
       s.student_no,
       s.name,
       s.gender,
       s.age,
       s.enrollment_date,
       c.class_name,
       c.major,
       c.head_teacher
from students s
         left join classes c on s.class_id = c.class_id;

create view v_student_scores as
select s.student_no,
       s.name,
       sc.course_name,
       sc.score,
       sc.exam_date,
       c.class_name
from students s
         join scores sc on s.id = sc.student_id
         left join classes c on s.class_id = c.class_id;

create view v_class_avg_scores as
select c.class_id,
       c.class_name,
       c.major,
       count(distinct s.id)    as student_count,
       count(sc.score_id)      as score_count,
       round(avg(sc.score), 2) as avg_score
from classes c
         left join students s on c.class_id = s.class_id
         left join scores sc on s.id = sc.student_id
group by c.class_id, c.class_name, c.major;

-- 查询所有学生详细信息
select *
from v_student_details;

-- 查询特定班级的学生
select *
from v_student_details
where class_name = '计算机2023级1班';

-- 查询每个学生的平均分
select student_no,
       name,
       count(*)             as course_count,
       round(avg(score), 2) as avg_score
from v_student_scores
group by student_no, name
order by avg_score desc;

-- 查询90分以上的成绩
select *
from v_student_scores
where score >= 90;

-- 查询班级平均成绩及学生数量
select class_name,
       major,
       student_count,
       avg_score
from v_class_avg_scores
where avg_score is not null
order by avg_score desc;

-- 可更新视图的条件：视图来自单表，且不包含聚合函数、group by、distinct等
-- 通常不建议通过视图更新数据，这里仅作演示
-- 创建一个简单的可更新视图
create view v_simple_students as
select id, student_no, name, age
from students;

-- 通过视图更新数据
update v_simple_students
set age = 22
where student_no = '2023001';

delimiter // -- 更改语句结束符，以便在存储过程中使用分号
create procedure sp_get_all_students()
begin
    select * from students order by id;
end //
delimiter ; -- 恢复语句结束符

delimiter //
create procedure sp_get_students_by_class(
    in p_class_id int -- 输入参数：班级id
)
begin
    select * from students where class_id = p_class_id order by name;
end //
delimiter ;

delimiter //
create procedure sp_add_student(
    in p_student_no varchar(20),
    in p_name varchar(50),
    in p_gender enum ('男', '女'),
    in p_age int,
    in p_class_id int,
    in p_enrollment_date date,
    out p_result varchar(100) -- 输出参数：操作结果
)
begin
    declare v_count int;

    -- 检查学号是否已存在
    select count(*) into v_count from students where student_no = p_student_no;

    if v_count > 0 then
        set p_result = '学号已存在，添加失败';
    else
        -- 插入新学生
        insert into students (student_no, name, gender, age, class_id, enrollment_date)
        values (p_student_no, p_name, p_gender, p_age, p_class_id, p_enrollment_date);

        set p_result = concat('添加成功，学生id为：', last_insert_id());
    end if;
end //
delimiter ;

delimiter //
create procedure sp_update_student_age(
    in p_student_id int,
    in p_new_age int,
    out p_result varchar(100)
)
begin
    declare v_exists int;

    -- 检查学生是否存在
    select count(*) into v_exists from students where id = p_student_id;

    if v_exists = 0 then
        set p_result = '学生不存在，更新失败';
    elseif p_new_age < 0 or p_new_age > 100 then
        set p_result = '年龄必须在0-100之间';
    else
        update students set age = p_new_age where id = p_student_id;

        if row_count() > 0 then
            set p_result = '年龄更新成功';
        else
            set p_result = '年龄更新失败';
        end if;
    end if;
end //
delimiter ;

delimiter //
create procedure sp_get_student_full_info(
    in p_student_id int
)
begin
    -- 查询学生基本信息
    select '学生基本信息' as info_type, s.* from students s where s.id = p_student_id;

    -- 查询学生成绩
    select '学生成绩' as info_type, sc.* from scores sc where sc.student_id = p_student_id;

    -- 查询学生平均分
    select '学生平均分'         as info_type,
           round(avg(score), 2) as avg_score,
           count(*)             as course_count
    from scores
    where student_id = p_student_id;
end //
delimiter ;

delimiter //
create procedure sp_batch_insert_students(
    in p_count int,
    out p_message varchar(100)
)
begin
    declare i int default 1;
    declare v_student_no varchar(20);
    declare v_class_id int;
    declare exit handler for sqlexception
        begin
            rollback;
            set p_message = '插入过程中发生错误，已回滚';
        end;

    start transaction;

    while i <= p_count
        do
            set v_student_no = concat('test2024', lpad(i, 3, '0'));
            set v_class_id = floor(rand() * 3) + 1; -- 随机分配班级id(1-3)

            insert into students (student_no, name, gender, age, class_id, enrollment_date)
            values (v_student_no,
                    concat('测试学生', i),
                    if(rand() > 0.5, '男', '女'),
                    floor(rand() * 10) + 18, -- 年龄18-27岁
                    v_class_id,
                    date_add('2023-09-01', interval floor(rand() * 30) day));

            set i = i + 1;
        end while;
    commit;
    set p_message = concat('成功插入', p_count, '条测试数据');
end //
delimiter ;

call sp_get_all_students();

call sp_get_students_by_class(1); -- 查询班级id为1的学生

set @result = ''; -- 定义用户变量接收输出参数
call sp_add_student('2023008', '周九', '女', 21, 1, '2023-09-01', @result);
select @result as 操作结果;

call sp_update_student_age(1, 22, @result);
select @result as 更新结果;

call sp_get_student_full_info(1);

call sp_batch_insert_students(5, @result);
select @result as 批量插入结果;

show procedure status where db = 'school_db';

show create procedure sp_get_all_students;

-- 查看所有视图
show full tables where table_type like 'view';

-- 查看视图定义
show create view v_student_details;

-- 修改视图
create or replace view v_simple_students as
select id, student_no, name, age, gender
from students;

-- 删除视图
drop view if exists v_simple_students;

start transaction;
insert into students (student_no, name, gender, age, class_id, enrollment_date)
values ('2023009', '吴十', '女', 20, 1, '2023-09-01');
insert into scores (student_id, course_name, score, exam_date)
values (last_insert_id(), '数据库原理', 95.0, '2024-01-20');
-- 如果所有操作都成功，提交事务
commit;
-- 如果出现错误，可以回滚：rollback;

-- 删除存储过程
-- drop procedure if exists sp_get_all_students;

-- 删除表（谨慎操作）
-- drop table if exists scores;
-- drop table if exists students;
-- drop table if exists classes;

-- 删除数据库（谨慎操作）
-- drop database if exists school_db;