数据库应用开发

DDl

-- 创建student表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(50) NOT NULL,
    result INT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

DMl

-- 插入5条学生信息
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 18, '男', '爱好体育，善于沟通。'),
('李四', '13900139000', 19, '男', '热爱学习，乐于助人。'),
('王五', '13700137000', 20, '女', '喜欢音乐，乐于助人。'),
('赵六', '13600136000', 18, '女', '热爱艺术，性格开朗。'),
('孙七', '13500135000', 19, '男', '喜欢编程，逻辑清晰。');

-- 插入10条成绩信息，每个学生2个科目的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90, (SELECT id FROM student WHERE userName = '张三')),
('语文', 85, (SELECT id FROM student WHERE userName = '张三')),
('数学', 88, (SELECT id FROM student WHERE userName = '李四')),
('英语', 92, (SELECT id FROM student WHERE userName = '李四')),
('物理', 89, (SELECT id FROM student WHERE userName = '王五')),
('化学', 95, (SELECT id FROM student WHERE userName = '王五')),
('政治', 82, (SELECT id FROM student WHERE userName = '赵六')),
('历史', 87, (SELECT id FROM student WHERE userName = '赵六')),
('生物', 91, (SELECT id FROM student WHERE userName = '孙七')),
('地理', 86, (SELECT id FROM student WHERE userName = '孙七'));

DQL

DELIMITER //

CREATE PROCEDURE GetStudentAndScoreInfoByName(IN p_userName VARCHAR(50))
BEGIN
    -- 声明变量来存储从游标中检索到的数据
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_student_id INT;
    DECLARE v_createDate DATETIME;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_introduce TEXT;
    DECLARE v_score_id INT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result INT;

    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.id, s.createDate, s.userName, s.phone, s.age, s.sex, s.introduce, 
            sc.id AS score_id, sc.scoreName, sc.result
        FROM 
            student s
        LEFT JOIN 
            score sc ON s.id = sc.studentId
        WHERE 
            s.userName = p_userName;

    -- 声明处理游标结束时的状态
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 读取游标数据
    read_loop: LOOP
        FETCH cur INTO v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_score_id, v_scoreName, v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 这里可以按需处理或输出数据，比如使用SELECT语句
        SELECT 
            v_student_id AS student_id, 
            v_createDate AS createDate, 
            v_userName AS userName, 
            v_phone AS phone, 
            v_age AS age, 
            v_sex AS sex, 
            v_introduce AS introduce, 
            v_score_id AS score_id, 
            v_scoreName AS scoreName, 
            v_result AS result;
    END LOOP;

    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;