Mysql8.0习题系列

软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9

@TOC

0.准备工作

CREATE DATABASE test15_pro_func;
USE test15_pro_func;
1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);

2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话
CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');
SELECT * FROM beauty;

1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中

CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);
DROP PROCEDURE insert_user;
DELIMITER $;
CREATE PROCEDURE insert_user(IN username VARCHAR(15),IN pass_word VARCHAR(20))
BEGIN
INSERT INTO admin(user_name,pwd) VALUES (username, pass_word);
END $;
DELIMITER ;

CALL insert_user('YOYO',123);
SELECT * FROM admin;

2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话

CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');
SELECT * FROM beauty;

DROP PROCEDURE get_phone;
DELIMITER $;
CREATE PROCEDURE get_phone(IN id INT,OUT out_name VARCHAR(20),OUT out_phone VARCHAR(15))
BEGIN
SELECT NAME, phone INTO out_name, out_phone
FROM beauty b
WHERE b.id = id;
END $;
DELIMITER ;
CALL get_phone(1,@out_name,@out_phone);
SELECT @out_name, @out_phone;

3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小

方法一

DROP PROCEDURE date_diff;
DELIMITER $;
CREATE PROCEDURE date_diff(IN bth1 DATETIME, IN bth2 DATETIME)
BEGIN
DECLARE data_diff INT; # 定义接受日期间隔的变量
SET data_diff = DATEDIFF(bth1,bth2); # 计算生日间隔
SELECT data_diff; # 查询生日间隔
END $;
DELIMITER ;
CALL date_diff('2018-08-15','2008-10-15');

方法二

DROP PROCEDURE date_diff;
DELIMITER $;
CREATE PROCEDURE date_diff(IN bth1 DATETIME, IN bth2 DATETIME, OUT result INT)
BEGIN
SELECT DATEDIFF(bth1, bth2) INTO result; 
END $;
DELIMITER ;
CALL date_diff('2018-08-15','2008-10-15',@result);
SELECT @result;

4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回

DROP PROCEDURE format_date ;
DELIMITER $;
CREATE PROCEDURE format_date(IN indate DATETIME, OUT outdate VARCHAR(50) )
BEGIN
SELECT DATE_FORMAT(indate, "%y年%m月%d日") INTO outdate;
END $;
DELIMITER ;

CALL format_date('2000-05-25', @outdate);
SELECT @outdate;

5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录

DROP PROCEDURE beauty_limit ;
DELIMITER $;
CREATE PROCEDURE beauty_limit(IN idx INT, IN num INT )
BEGIN
SELECT * FROM beauty
LIMIT idx, num;
END $;
DELIMITER ;

CALL beauty_limit(1,2)

创建带inout模式参数的存储过程

6. 传入a和b两个值，最终a和b都翻倍并返回

DELIMITER $;
CREATE PROCEDURE double_num(INOUT a INT, INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2; 
END $;
DELIMITER ;
SET @a = 3;
SET @b = 2;
CALL double_num(@a,@b);
SELECT @a, @b;

7. 删除题目5的存储过程

DROP PROCEDURE beauty_limit;

8. 查看题目6中存储过程的信息

SHOW PROCEDURE STATUS LIKE 'add_double';