MySQL过程函数等练习

1、根据编号查询名字【传入编号，获取名字】

DELIMITER $$
	CREATE PROCEDURE aa(_id INT)
	BEGIN
		SELECT vipname FROM vip WHERE vipid = _id;
	END$$
DELIMITER ;

2、根据编号查询vip的名字(传入编号，获取名字和性别)

DELIMITER $$
	CREATE PROCEDURE bb(_id INT)
	BEGIN
		SELECT vipname , vipsex FROM vip WHERE vipid = _id;
	END$$
DELIMITER ;

3、创建过程，根据输入的成绩编号【pk】，获取该学号，科目名以及成绩

DELIMITER $$
	CREATE PROCEDURE cc(_markid INT)
	BEGIN
		SELECT studentno , (SELECT subjectname FROM subjects WHERE result.subjectid = subjects.subjectid) subjectname, 
		studentscore FROM result WHERE markid = _markid;
	END$$
DELIMITER ;

4、创建过程，根据输入的成绩编号【pk】，获取该学生名字，科目名以及成绩

DELIMITER $$
	CREATE PROCEDURE dd(_markid INT)
	BEGIN
		SELECT (SELECT studentname FROM student WHERE result.studentno = student.studentno) studentname , 
		(SELECT subjectname FROM subjects WHERE result.subjectid = subjects.subjectid) subjectname ,
		 studentscore FROM result WHERE markid = _markid;
	END$$
DELIMITER ;

5、输入员工号，查找员工名字,上级领导名字以及工资

DELIMITER $$
	CREATE PROCEDURE ee(_empid INT)
	BEGIN
		SELECT CONCAT(first_name, last_name) NAME , 
		(SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) departmentname ,
		 salary FROM employees WHERE _empid = employee_id;
	END$$
DELIMITER ;

6、使用存储函数|过程 传入学号，获取学生的姓名，总分，平均分

DELIMITER $$
	CREATE PROCEDURE ff(_stuno VARCHAR(50))
	BEGIN
		SELECT (SELECT studentname FROM student WHERE _stuno = studentno) studentname , 
		SUM(studentscore) , AVG(studentscore) FROM result WHERE _stuno = studentno;
	END$$
DELIMITER ;

7、输入，科目名字，学时，以及gradeid ，将该数据插入到数据库

DELIMITER $$
	CREATE PROCEDURE gg(_subname VARCHAR(50) , _hour INT , _gradeid INT)
	BEGIN
		DECLARE _id INT;
		DECLARE _count INT;
		SELECT COUNT(1) INTO _count FROM grade WHERE _gradeid = gradeid;
		IF _count = 0 THEN
			SELECT "插入失败,没有这个班级";
		ELSE
			SELECT MAX(subjectid) INTO _id FROM subjects;
			INSERT INTO subjects VALUES (_id + 1, _subname , _hour , _gradeid);
			SELECT "插入成功";
		END IF;
	END$$
DELIMITER ;

8、根据传入的科目编号删除科目信息，如果该科目以前被考过试，则不能删除

DELIMITER $$
	CREATE PROCEDURE hh(_subid INT)
	BEGIN
		DECLARE _count INT;
		SELECT COUNT(1) INTO _count FROM result WHERE _subid = subjectid;
		IF _count = 0 THEN
			DELETE FROM subjects WHERE _subid = subjectid;
			SELECT "删除成功";
		ELSE
			SELECT "删除失败，此科目被考过试";
		END IF;
	END$$
DELIMITER ;

9、自动更新会员等级，
总金额达到2000的时候，等级编号变为2
总金额达到5000的时候，等级编号变为3
总金额达到10000的时候，等级编号变为4
总金额达到20000的时候，等级编号变为5

DELIMITER $$
	CREATE TRIGGER setlevel AFTER INSERT ON sales2 FOR EACH ROW
		BEGIN
			DECLARE summoney INT;
			SELECT SUM(smoney) INTO summoney FROM sales2 WHERE vipid = new.vipid;
			IF summoney >= 20000 THEN
				UPDATE vip SET viplevel = 5 WHERE vipid = new.vipid;
			ELSEIF summoney >= 10000 THEN
				UPDATE vip SET viplevel = 4 WHERE vipid = new.vipid;
			ELSEIF summoney >= 5000 THEN
				UPDATE vip SET viplevel = 3 WHERE vipid = new.vipid;
			ELSEIF summoney >= 2000 THEN
				UPDATE vip SET viplevel = 2 WHERE vipid = new.vipid;
			END IF;
		END$$
DELIMITER ;