数据库题目整理及详解（四）

Posted on | In
Symbols count in article: 15k | Reading time ≈ 13 mins.

前言

有多少次挥汗如雨，伤痛曾添满记忆，只因为始终相信，去拼搏才能胜利。总在鼓舞自己，要成功就得努力。热血在赛场沸腾，巨人在赛场升起。

相信自己，你将赢得胜利，创造奇迹；相信自己，梦想在你手中，这是你的天地。当一切过去，你们将是第一。

相信自己，你们将超越极限，超越自己！
相信自己，加油吧，健儿们，相信你自己。

坐在中体对面, 听着这振奋激昂的加油欢呼声, 照样可以感受到校运会的气势磅礴, 虽然我还在敲代码…

来个这个吧, 特殊纪念, 沃夫慈悲:

DB-4

说明

老生常谈! 接着之前的SQL语句继续整理, 内容参考了互联网上的很多内容, 结尾缀上出处, 感谢分享.

这一部分主要内容为：mysql数据库存储过程相关的SQL语句, 在书写上的格式、规范及环境配置等，详见：数据库题目整理及详解（一），说明部分 .

题目详情

设某学生信息系统数据库包含如下关系:

1
STUDENT(ID, NAME, CLASSNO, BIRTH)

请根据所学存储过程相关知识完成如下如下表示的功能:

(1) 创建数据库及 student 表,并至少插入 3 条数据;
(2) 创建并调用名为 sp 的存储过程,查询学生的所有信息;
(3) 创建并调用名为 sp1 的存储过程,向表中插入一条学生信息;
(4) 创建并调用名为 sp2 的存储过程,查询学生表中的最大班级号;
(5) 创建并调用名为 sp3 的存储过程,修改学生学号,如果学号为 1,修改为
10; 否则,修改学号为 20,并查看信息表中的最大学号值;
(6) 创建并调用名为 sp4 的存储过程,使得学生的班级号既做输入参数又做输出参数;
(7) 创建并调用名为 sp5 的存储过程,根据输入的班级号,判断班级名称;
(8) 创建并调用名为 sp6的存储过程,根据输入的班级号,更新班级名称;

题目解答

(1) 创建数据库及 student 表, 并至少插入 3 条数据;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create database procedure_test;		## 创建数据库
use procedure_test;		## 切换至该数据库下

drop table if exists student; 		## 好习惯
create table student ( 		## 建表
id int(11) primary key, 
name char(10)
not null, 
classno int(11), 
birth datetime);

desc student;	## 看一下表结构

## 插入数据
insert into student values(1, 'xiaohua', 01, '1992-02-01 10:20:31');
insert into student values(2, 'xiaohua1', 02, '1993-05-11 20:34:35');
insert into student values(3, 'xiaohua2', 03, '1989-011-11 11:11:11');

## 查询一下啊
select * from student;

(2) 创建并调用名为 sp 的存储过程,查询学生的所有信息;

1
2
3
4
drop procedure if exists sp; 	## 好习惯
create procedure sp() select * from student;  ## 建立存储过程
call sp();		## 调用一哈
show procedure status \G; 		## 参看一哈

(3)创建并调用名为 sp1 的存储过程,向表中插入一条学生信息;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter //	## 自制分隔符，防止“；”带来的意外

## 接下来就和上边一样一样啦， 就不写了啊
create procedure sp1(in id int, in name char(10), in
classno int(11), in birth datetime ) comment 'insert into a value to student'
begin
	set @id = id;
	set @name = name;
	set @classno = classno;set @birth = birth;
	insert into student values(id, name, classno, birth);
end//

call sp1(4, 'xiaohua4', 4, '2010-10-05 13:14:27')//
select * from student//
show procedure status \G

(4) 创建并调用名为 sp2 的存储过程,查询学生表中的最大班级号;

1
2
3
4
5
6
7
8
9
create procedure sp2(out p int)
deterministic
begin
	select max(classno) into p from student;
end//

call sp2(@pv)//
select @pv//
show procedure status \G

(5) 创建并调用名为 sp3 的存储过程,修改学生学号,如果学号为 1,修改为
10;否则,修改学号为 20,并查看信息表中的最大学号值;

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure sp3(in p1 int, out p2 int)
begin
	if p1 = 1 then
		set @v = 10;
	else
		set @v = 20;
	end if;
	select max(id) into p2 from student;
end//

call sp3(1, @ret)//
select @ret;
show procedure status \G

(6) 创建并调用名为 sp4 的存储过程,使得学生的班级号既做输入参数又做输
出参数;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop procedure if exists sp4//
create procedure sp4(inout p4 int)
begin
	if p4 = 3 then
		set @value = 13;
	else
		set @value = 14;
	end if;
	select @value;
end//

call sp4(@result)//
set @result = 4//
call sp4(@result)//

(7) 创建并调用名为 sp5 的存储过程,根据输入的班级号,判断班级名称;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
drop procedure if exists sp5//

## 眼尖的童鞋可能会看到，这里加了一步操作， 为了弥补第一题所犯的错，表结构忘记建立该属性了
alter table student add classname char(11)//  

create procedure sp5(in classno int, out classname char(11))
begin
	if classno = 1 then
		set classname = 'firstclass';
	end if;
	
	if classno = 2 then
		set classname = 'secondclass';
	end if;
	
	if classno = 3 then
		set classname = 'thirdclass';
	end if;
	
	if classno = 4 then
		set classname = 'fourthclass';
	end if;
	
	select * from student;
end//

call sp5(2, @ret)//
select @ret;

(8) 创建并调用名为 sp5 的存储过程,根据输入的班级号,更新班级名称;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
## 错误总是无独有偶， 显然！这步是弥补上步未完成的任务
create procedure sp6(in classno int)
begin
	if classno = 1 then
		update student set classname = 'firstclass' where id=1;
	end if;

	if classno = 2 thenupdate student set classname =     'secondclass' where id=2;
	end if;
	
	if classno = 3 then
		update student set classname = 'thirdclass' where id=3;
	end if;
	
	if classno = 4 then
		update student set classname = 'fourthclass' where id=4;
	end if;
	
	select * from student;
end//

call sp6(1)//
call sp6(2)//
call sp6(3)//
call sp6(4)//

参考资料

[1]. http://www.cnblogs.com/zhuawang/p/4185302.html
[2]. http://blog.csdn.net/woshixuye/article/details/8348180
[3]. http://www.2cto.com/database/201408/327315.html

0%