建设学校网站的意义,搜索引擎官网,毕设 代做 网站,seo推广培训费用实验 一、
实验要求#xff1a;
理解存储过程的概念掌握存储过程的语法格式、使用方法掌握存
储过程的创建、执行 二、实验前提#xff1a; – drop table if exists student; – Create table student – (Id varchar(255), #学号 – Name varchar(255), #姓名 – Roomid…实验 一、
实验要求
理解存储过程的概念掌握存储过程的语法格式、使用方法掌握存
储过程的创建、执行 二、实验前提 – drop table if exists student; – Create table student – (Id varchar(255), #学号 – Name varchar(255), #姓名 – Roomid varchar(255), #班级 – Sex char(1), #性别 – Degree varchar(3) )#成绩; Insert into student value
s(‘1’,‘amy’,‘8’,‘0’,‘65’); Insert into student values(‘2’,‘bob’,‘4’,‘1’,‘80’); Insert into student values(‘3’,‘candy’,‘6’,‘0’,‘70’); Insert into student values(‘4’,‘danny’,‘8’,‘1’,‘90’); Insert into student values(‘5’,‘edward’,‘8’,‘1’,‘95’); Insert into student values(‘6’,‘frank’,‘7’,‘1’,‘100’); 1创建一个存储过程实现查询表student中的记录信息并执行存储过程 建一个存储 结果查询表student中的记录信息 方法1 delimiter // drop procedure IF EXISTS xx; CREATE PROCEDURE xx () BEGIN SELECT * FROM student; END//
call xx;
2:创建一个存储过程根据给定的学生学号返回该学生的姓名 结果学生姓名 条件 语句 select name from student wehre id x select name into y from student wehre id x 方法 delimiter // create procedure student3(in x int(10),out y varchar(10)) BEGIN select Name into y from student where idx; END //
call student3(1,y) select y
3:创建一个存储过程根据所给的班级的编号统计该班人数并将人数以输出变量返回给用户。 结果 条件roomid x select countname into y from roomid x 方法 delimiter // create PROCEDURE student10(in z varchar(225),out x varchar(225)) begin select count(id)into x from student where roomid z; END //
call student10(“8”,x) select x
方法2 delimiter // create PROCEDURE p3 (in x varchar(255) , out v int(10)) begin
SELECT count(student.Id) into v from student where student.Roomidx ;
SELECT v;
END//
call p3(‘8’,v); 4创建一个存储过程根据所给的学号查询该学生的考试分数是否超过了85分 若超过则输出“ 考得好” 否则输出 “考得不好”。
结果 85 “考的好” 考的不好 条件select degree from student where id x 判断if
方法1 delimiter // DROP PROCEDURE IF EXISTS stu4; CREATE PROCEDURE stu4(IN x VARCHAR(225)) BEGIN IF (SELECT Degree FROM student s WHERE xs.Id)85 THEN SELECT “考得好”; ELSE SELECT “考得不好”; END IF; END // CALL stu4(5)
方法2 delimiter // create procedure student5(in x varchar(255), out y varchar(255)) BEGIN select level into y from (select *, case when Degree 85 then “考得好” when Degree 85 then “考得不好” end level from student) a where Idx; END //
call student5(4,y) select y
方法3 drop procedure studentresult; delimiter// create procedure studentresult(in stuid varchar(20),out sturesult varchar(20)) begin select degree into sturesult from student where id stuid; if(sturesult 85) then select ‘考得好’ ; elseif(sturesult 85) then select ‘考得不好’ ; end if; end; // call studentresult(4,sturesult); 方法4 delimiter// create procedure p6(in x int(10) ) BEGIN DECLARE exam_score int DEFAULT (SELECT Degree FROM student WHERE Id x);
IF exam_score 85 THENSELECT 考得好 ;
ELSESELECT 考得不好 ;
END IF;END//
call p6(4)
实验5:创建一个存储过程对表student增加两条记录。
1已建表在插入数据 方法1 drop procedure studentinsert; delimiter// create procedure studentinsert() begin insert into student values(7,‘mary’,7,1,75); insert into student values(8,‘smith’,6,0,92); end // call studentinsert(); select * from student; 方法2 delimiter // CREATE procedure cc() BEGIN declare i int DEFAULT (select count(id) from student) ; insert into student(id) VALUES(i1) ; insert into student(id) VALUES(i2) ; select * from student ; END //
call cc()
2、不存在表在如数据 a、方法1
delimiter// create procedure p7(in a int(10),in b VARCHAR(255),in a1 int(10),in b1 VARCHAR(255)) BEGIN drop table if EXISTS s1; create table s1(sid int(10) PRIMARY key,sname VARCHAR(255) ); insert into s1 VALUES (a,b); insert into s1 VALUES (a1,b1); SELECT * from s1; end// call p7(9,‘aa’,10,‘bb’)
6:请撰写一个存储过程输出各班学生的平均成绩。 结果avg(degree) 分组 roomid select avg(degree) from student group by roomid; 方法 drop procedure studentavg; delimiter// create procedure studentavg() begin select avg(degree) from student group by roomid; end // call studentavg();
7:请撰写一个存储过程,能根据用户输入的学号输出学生的姓名、性别到两个参数变量中以供其它程序使用。 结果姓名、性别 条件 方法1select namesex into mn from idx
drop procedure if exists studentnamesex ; delimiter// create procedure studentnamesex(in stuid varchar(20),out stuname varchar(20),out stusex char(10)) begin select name,sex into stuname ,stusex from student where id stuid; end // call studentnamesex(1,stuname,stusex); select stuname,stusex;
方法2 select name into m from idx select sex into n from idx
drop procedure if exists studentnamesex ; delimiter// create procedure studentnamesex(in stuid varchar(20),out stuname varchar(20),out stusex char(10)) begin select name into stuname from student where id stuid; select sex into stusex from student where id stuid; end // call studentnamesex(1,stuname,stusex); select stuname,stusex;
方法3 drop procedure if exists studentnamesex ; delimiter// create procedure studentnamesex(in stuid varchar(20)) begin sety(select name from student where id stuid); setz(select sex from student where id stuid); select y,z ; end // call studentnamesex(1);
8撰写一个存储过程根据用户输入的学号输出学生性别成绩两个参数放到新表temp中Create table temp(Sex varchar(255),Degree varchar(255));
结果性别成绩 条件输入的学号 建表
方法1 delimiter // drop procedure if EXISTS student2 ; create procedure student2(in x int(10)) BEGIN drop table if exists temp; create table temp(Sex1 char(1) , Degree1 varchar(3)); insert into temp(Sex1,Degree1) select Sex,Degree from student where xId; select * from temp; end //
call student2(2)
方法2
delimiter // drop PROCEDURE if EXISTS p9 ; create PROCEDURE p9( in I int(10),out s VARCHAR(255),out d VARCHAR(255)) BEGIN
SELECT Sex,Degree into s,d from student where Id I ; SELECT s,d; drop table if EXISTS temp; Create table temp(Sex varchar(255),Degree varchar(255)); insert into temp VALUES(s,d); SELECT * from temp; END
call p9(2,s,d) 方法3
两张表的字段一致插入数据 方法一insert into 目标表 select * from 来源表#插入全部数据 方法二insert into 目标表字段 select 字段1,字段2 from 来源表。
9:请撰写一个存储过程求123…x的值。 方法1 delimiter // drop procedure if exists he; create procedure he(inout x int(10)) BEGIN set xx*(x1)/2; END
set x10 call he(x) select x
方法2 drop procedure if exists studentadd1; delimiter// create procedure studentadd1(in x varchar(20)) begin declare result varchar(255) default 0; while x 0 do set result result x; set x x - 1; end while; select result; end; // call studentadd1(10);
方法3
Delimiter // Create procedure stu9(in x VARCHAR(225)) Begin Declare i int default 1; Declare num int default 0; While ix do Set numnumi; Set ii1; End while; Select num; End // call stu9(10)
请撰写一个存储过程求024…x的值。 方法1 delimiter // drop PROCEDURE if EXISTS p11; create PROCEDURE p11(in x int) BEGIN
DECLARE i int DEFAULT 0; DECLARE s int DEFAULT 0; WHILE ix do
set sis; set ii2; end WHILE ; select s; END//
call p11(10) 方法2 drop procedure if exists studentadd1; delimiter// create procedure studentadd1(in x varchar(20)) begin declare result varchar(255) default 0; while x 0 do set result result x; set x x - 2; end while; select result; end; // call studentadd1(10);
请撰写一个存储过程求135…x的值。
方法 delimiter // drop PROCEDURE if EXISTS p11; create PROCEDURE p11(in x int) BEGIN
DECLARE i int DEFAULT 1; DECLARE s int DEFAULT 0; WHILE ix do
set sis; set ii2; end WHILE ; select s; END//
call p11(10)
方法2 drop procedure if exists studentadd1; delimiter// create procedure studentadd1(in x varchar(20)) begin declare result varchar(255) default 0 ; while x 0 do set result result x; set x x - 2; end while; select result; end; // call studentadd1(9);
