MYSQL——智能练习

DDL

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

DML

-- DML语句1
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-01-01', '张三', '13800138000', 20, '男', '张三之容，玉立亭亭，翩翩公子，温润如玉。');


-- DML语句2
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-02-02', '李四', '13900139000', 22, '男', '李四之貌，英俊潇洒，风度翩翩，神采飞扬。');


-- DML语句3
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-03-03', '王五', '13700137000', 21, '女', '王五之姿，婀娜多姿，婉约清雅，宛如仙子。');


-- DML语句4
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-04', '赵六', '13600136000', 23, '男', '赵六之形，魁梧雄壮，英气逼人，气宇轩昂。');


-- DML语句5
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-05-05', '孙七', '13500135000', 24, '女', '孙七之颜，秀丽端庄，气质非凡，宛若幽兰。');


-- DML语句6
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-06-06', '李明', '13400134000', 25, '男', '李明之貌，宛若天人，丰神俊朗，举世无双。眉如墨画，目若朗星，鼻梁高挺，唇红齿白。其仪态万方，举止从容，谈笑风生间，尽显翩翩公子之风范。文采斐然，才情出众，实为人间之翘楚。');


-- DML语句7
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-07-07', '周八', '13300133000', 22, '男', '周八之姿，俊逸非凡，风度翩翩，潇洒不羁。');

SQL

-- 创建一个存储过程get_introduce，用于查询指定用户名的简介信息
DELIMITER //
CREATE PROCEDURE get_introduce(IN userNameParam VARCHAR(50))
BEGIN
    -- 声明一个变量用于存储查询结果
    DECLARE introduceText TEXT;
    
    -- 使用SELECT INTO语句将查询结果赋值给变量
    SELECT introduce INTO introduceText
    FROM student
    WHERE userName = userNameParam;
    
    -- 如果查询结果不为空，则输出简介信息
    IF introduceText IS NOT NULL THEN
        SELECT introduceText AS '用户简介';
    ELSE
        -- 如果没有找到匹配的记录，则输出提示信息
        SELECT '未找到用户的简介信息' AS '提示信息';
    END IF;
END //
DELIMITER ;