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_countFROM studentWHERE userName = p_userName;-- 如果用户名存在（即更新影响的行数大于0）IF update_count > 0 THEN-- 更新性别为'男'UPDATE studentSET 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
