创建查询与应用

DDL

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

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-23 10:00:00', '张先生', '13800138000', 25, '男', '张先生是一位优秀的学生。');
 
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-24 11:00:00', '李小姐', '13900139000', 22, '女', '李小姐对学习充满热情。');
 
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-25 12:00:00', '王同学', '13700137000', 28, '男', '王同学是班级的学习委员。');
 
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-26 13:00:00', '赵同学', '13600136000', 23, '女', '赵同学爱好广泛，善于交际。');
 
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-10-27 14:00:00', '陈先生', '13500135000', 30, '男', '陈先生是班级里的体育健将。');

DQL

#-- 创建存储过程get_userName_introduce，用于查询userName为'张先生'的introduce信息
DELIMITER //
CREATE PROCEDURE get_userName_introduce()
BEGIN
   # -- 定义变量用于存储查询结果
    DECLARE introduce_text TEXT;
 
    #-- 查询userName为'张先生'的记录，并将introduce字段的值赋给introduce_text变量
    SELECT introduce INTO introduce_text
    FROM student
    WHERE userName = '张先生';
 
   # -- 输出查询结果
    SELECT introduce_text AS '张先生的介绍';
END //
DELIMITER ;
 
CALL get_userName_introduce();