SQL语句

-- 创建student表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE,
    userName VARCHAR(255),
    phone VARCHAR(15),
    age INT,
    sex ENUM('男', '女'),
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255),
    result FLOAT,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id)
);

-- 为studentId为3的学生添加数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 98.5, 3);


-- 为studentId为3的学生添加英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('英语', 89.0, 3);


-- 为studentId为4的学生添加数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 92.5, 4);


-- 为studentId为4的学生添加英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('英语', 94.0, 4);
-- 插入成绩信息
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.5, 1), ('英语', 85.5, 1),
('数学', 88.0, 2), ('英语', 92.0, 2),
('数学', 95.5, 3), ('英语', 87.0, 3),
('数学', 89.5, 4), ('英语', 91.0, 4),
('数学', 93.0, 5), ('英语', 86.5, 5);
-- 插入学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-10-23', '张三', '13800138000', 20, '男', '我是张三。'),
('2023-10-22', '李四', '13800138001', 21, '女', '我是李四。'),
('2023-10-21', '王五', '13800138002', 22, '男', '我是王五。'),
('2023-10-20', '赵六', '13800138003', 20, '女', '我是赵六。'),
('2023-10-19', '孙七', '13800138004', 21, '男', '我是孙七。');
    SELECT 
    (@row_number:=@row_number + 1) AS ranking,
    s.id,
    s.userName,
    SUM(sc.result) AS totalScore
FROM
    student s
JOIN score sc ON s.id = sc.studentId
JOIN (SELECT @row_number:=0) r -- 初始化排名变量
GROUP BY s.id, s.userName
ORDER BY totalScore DESC;






DELIMITER //

CREATE PROCEDURE InsertRandomData()
BEGIN
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_student_id INT;
    DECLARE v_score_counter INT;
    DECLARE v_random_score FLOAT;
    DECLARE v_random_score_name VARCHAR(255);
    
    -- 插入10条学生信息
    WHILE v_counter < 10 DO
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (
            NOW(), -- 使用当前日期作为创建日期
            CONCAT('Student', FLOOR(RAND() * 1000) + 1), -- 随机用户名
            CONCAT('1', FLOOR(RAND() * 900000000) + 100000000), -- 随机电话号码
            FLOOR(RAND() * 10 + 16), -- 16到25之间的随机年龄
            IF(RAND() > 0.5, '男', '女'), -- 随机性别
            'Random introduction text.' -- 简介文本（可以根据需要随机生成）
        );
        SET v_counter = v_counter + 1;
    END WHILE;
    
    -- 设置v_counter为0，用于score表的循环
    SET v_counter = 0;
    
    -- 为每个学生插入100条score记录
    WHILE v_counter < 10 DO
        -- 获取刚刚插入的student的id（假设id自增且连续，从6开始）
        SET v_student_id = v_counter + 6;
        SET v_score_counter = 0;
        
        WHILE v_score_counter < 100 DO
            SET v_random_score = FLOOR(RAND() * 101); -- 生成0到100之间的随机分数
            SET v_random_score_name = CONCAT('Subject ', FLOOR(RAND() * 10 + 1)); -- 随机科目名称
            
            INSERT INTO score (scoreName, result, studentId)
            VALUES (v_random_score_name, v_random_score, v_student_id);
            
            SET v_score_counter = v_score_counter + 1;
        END WHILE;
        
        SET v_counter = v_counter + 1;
    END WHILE;
END //

DELIMITER ;


-- 调用存储过程以插入随机数据
CALL InsertRandomData();





















DELIMITER //

CREATE PROCEDURE GetStudentInfoByName(IN student_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_createDate DATE;
    DECLARE v_userName VARCHAR(255);
    DECLARE v_phone VARCHAR(15);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_introduce TEXT;
    DECLARE v_scoreName VARCHAR(255);
    DECLARE v_result FLOAT;
    -- 声明游标
    DECLARE cur STUDENT_SCORES_CURSOR;
    -- 声明NOT FOUND处理器，当游标达到结果集末尾时，将done设置为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建一个包含学生信息和成绩信息的临时表（或者你可以直接在应用中处理这些结果）
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_student_scores (
        id INT,
        createDate DATE,
        userName VARCHAR(255),
        phone VARCHAR(15),
        age INT,
        sex ENUM('男', '女'),
        introduce TEXT,
        scoreName VARCHAR(255),
        result FLOAT
    );
    
    -- 定义游标，选择特定姓名的学生信息和他们的成绩信息
    SET @query = "SELECT s.id, s.createDate, s.userName, s.phone, s.age, s.sex, s.introduce, sc.scoreName, sc.result 
                 FROM student s 
                 LEFT JOIN score sc ON s.id = sc.studentId 
                 WHERE s.userName = ?";
    PREPARE stmt FROM @query;
    EXECUTE stmt USING student_name;
    DEALLOCATE PREPARE stmt;

    -- 此处为示例，实际使用中，你可能希望将上述查询的结果直接返回给调用者，而不是存入临时表。
    -- 假设我们将结果存入临时表以便进一步处理或展示。
    INSERT INTO temp_student_scores(id, createDate, userName, phone, age, sex, introduce, scoreName, result)
    SELECT s.id, s.createDate, s.userName, s.phone, s.age, s.sex, s.introduce, sc.scoreName, sc.result 
    FROM student s 
    LEFT JOIN score sc ON s.id = sc.studentId 
    WHERE s.userName = student_name;

    -- 游标使用示例（可选，如果你不需要逐行处理结果，可以省略这部分）
    OPEN cur; -- 此处应定义具体的游标打开操作，但为简化，省略了游标的具体定义部分。
    read_loop: LOOP
        -- 获取游标中的数据（需要根据你的游标定义来调整FETCH语句）
        -- FETCH cur INTO ...;
        -- 对获取到的数据进行操作
        -- ...
        -- 如果完成了就退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
    END LOOP;
    CLOSE cur; -- 关闭游标

    -- 最后，可以选择从临时表中检索结果，或直接在前面将查询结果返回给客户端。
    SELECT * FROM temp_student_scores;
    DROP TEMPORARY TABLE IF EXISTS temp_student_scores; -- 清理临时表
END //

DELIMITER ;

CALL GetStudentInfoByName('张三'); -- 将'张三'替换为你想要查询的学生姓名。