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 ;