函数1111

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);

DML

INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-23', '惠惠', '13800138000', 20, '女', '惠惠之美，犹如春之花，丽质天生，艳压群芳。其貌若仙，举止若兰，人皆爱之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-22', '张三', '13900139000', 22, '男', '张三之姿，如玉树临风，英姿勃发。才情出众，众人皆赞之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-21', '李四', '13700137000', 21, '男', '李四之容，宛若秋水长天，清雅脱俗。才德兼备，人皆敬仰之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-20', '王五', '13600136000', 23, '男', '王五之貌，如松之挺拔，峻岭之雄浑。智勇双全，人皆敬之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-19', '赵六', '13500135000', 24, '女', '赵六之姿，如柳之柔媚，花之娇艳。心灵手巧，人皆爱之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-18', '孙七', '13400134000', 25, '男', '孙七之貌，似玉之温润，金之璀璨。才情横溢，人皆羡之。');
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-17', '周八', '13300133000', 26, '女', '周八之美，若月之皎洁，星之璀璨。温婉贤淑，人皆慕之。');

函数

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_student_info`(
    IN userName VARCHAR(50),
    IN phone VARCHAR(20),
    IN age INT,
    IN introduce TEXT
)
BEGIN
    -- 设置creatDate为当前日期
    DECLARE currentDate DATE DEFAULT CURDATE();
    -- 设置sex的默认值为'男'
    DECLARE defaultSex ENUM('男', '女') DEFAULT '男';
    
    -- 插入新的学生记录
    INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
    VALUES (currentDate, userName, phone, age, defaultSex, introduce);
END

函数

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_student_introduce`()
BEGIN
    -- DML：在存储过程内部执行SQL查询语句
    -- 使用SELECT语句从student表中选择userName为'惠惠'的记录的introduce字段值
    -- 这将返回惠惠的简介信息
    SELECT introduce 
    FROM student 
    WHERE userName = '惠惠';
END

函数

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_student_sex_to_male`(
    IN p_userName VARCHAR(50)
)
BEGIN
    -- 声明变量用于存储更新影响的行数
    DECLARE update_count INT DEFAULT 0;
    
    -- 检查用户名是否存在
    SELECT COUNT(*)
    INTO update_count
    FROM student
    WHERE userName = p_userName;
    
    -- 如果用户名存在（即更新影响的行数大于0）
    IF update_count > 0 THEN
        -- 更新性别为'男'
        UPDATE student
        SET sex = '男'
        WHERE userName = p_userName;
        
        -- 可以选择输出更新成功的消息，这里省略
        -- SELECT 'Gender updated successfully' AS message;
    ELSE
        -- 如果用户名不存在，可以抛出一个错误或者返回一个消息
        -- 这里选择抛出一个错误
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'User name not found in the student table.';
    END IF;
END