CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
userName VARCHAR(50) NOT NULL,
phone VARCHAR(20),
age INT,
sex ENUM('男', '女'),
introduce TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张先生', '13800138000', 25, '男', '来自北京的张先生,爱好运动和阅读。'),
('李女士', '13900139000', 23, '女', '来自上海的李女士,擅长音乐和舞蹈。'),
('王同学', '13700137000', 20, '男', '来自深圳的王同学,喜欢编程和电子竞技。'),
('赵小姐', '13600136000', 22, '女', '来自广州的赵小姐,热衷于旅游和美食。'),
('陈先生', '13500135000', 24, '男', '来自杭州的陈先生,对历史和文学有浓厚的兴趣。');
DELIMITER //
-- 创建存储过程get_userName_introduce,用于查询张先生的介绍信息
CREATE PROCEDURE get_userName_introduce()
BEGIN
-- 声明变量用于存储查询结果
DECLARE introduce_text TEXT;
-- 查询userName为'张先生'的学生的introduce字段,并将结果存入introduce_text变量中
SELECT introduce INTO introduce_text
FROM student
WHERE userName = '张先生';
END //
DELIMITER ;
CALL get_userName_introduce();