mysql函数

实验要求
1.根据编号查询名字【传入编号，获取名字】
2.根据编号查询vip的名字(传入编号，获取名字和性别)
3.创建过程，根据输入的成绩编号【pk】，获取该学号，科目号以及成绩
4.根据考试号查找学生的名字，科目名字，以及成绩
5.使用存储函数|过程 传入学号，获取学生的姓名，总分，平均分
6.输入，科目名字，学时，以及gradeid ，将该数据插入到数据库
7.根据传入的科目编号删除科目信息，如果该科目以及被考过试，则不能删除

delimiter $$
		create procedure test(id varchar(20),out sname varchar(20))
		begin
				SELECT studentName into sname from students WHERE studentNo=id;
		end$$
delimiter;
call test('s1004',@姓名);
SELECT @姓名 as 姓名;

delimiter $$
		create procedure test1(id varchar(20),out sname varchar(20), out thesex varchar(20))
		begin
				select studentName,sex into sname,thesex from students where studentNo=id;
		end$$
delimiter;
call test1('s1004',@姓名,@性别);
select @姓名 as 姓名,@性别 as 性别;

delimiter $$
	create procedure test2(sid int,out sNo varchar(20),out suid int ,out sScore varchar(10))
	begin
			select studentNo,subjectId,studentScore into sNo,suid,sScore from achievements where id=sid;
	end$$
delimiter;
call test2(4,@学号,@学科,@分数);
SELECT @学号 as 学号,@学科 as 学科, @分数 as 分数

	delimiter $$
	CREATE PROCEDURE test3 (
		eid INT,
		OUT sname VARCHAR ( 20 ),
		OUT subName VARCHAR ( 20 ),
		OUT score VARCHAR ( 10 )) BEGIN
	SELECT
		a.studentName,
		b.studentScore,
		sub.subjectName INTO sname,
		score,
		subName 
	FROM
		students a
		INNER JOIN achievements b ON b.studentNo = a.studentNo
		INNER JOIN subjects sub ON sub.subjectId = b.subjectId 
	WHERE
		b.id = eid;
	
END $$delimiter;
CALL test3 ( 2, @姓名, @学科, @分数 ) SELECT
@姓名,
@学科,
@分数;

delimiter$$
 create procedure test4(sid VARCHAR(20),out sname varchar(20),out sumall VARCHAR(20),out average varchar(20))
	BEGIN
			select studentName into sname from students WHERE studentNo=sid;
			select sum(studentScore),avg(studentScore) into sumall,average from achievements WHERE studentNo=sid;
	end$$
delimiter;
	call test4('s1002', @姓名,@总分,@平均分);
	SELECT @姓名,@总分,@平均分

delimiter $$
create PROCEDURE test113(subName varchar(20), subHour int, gid int(5))
		BEGIN
		insert into subjects values(null, subName, subHour, gid);
		END$$
DELIMITER;
		CALL TEST113('测试', 20, 1)