1.创建sch表
操作命令:
CREATE TABLE sch(
id INT(10) PRIMARY KEY NOT NULL ,
NAME VARCHAR(50) NOT NULL UNIQUE KEY,
glass VARCHAR(50) NOT NULL
);
2.创建emp表
操作命令:
CREATE TABLE emp(
dept VARCHAR(10) NOT NULL ,
NAME VARCHAR(10) NOT NULL ,
salary INT(20) NOT NULL
)CHARSET='utf8';
要求:
3.创建存储函数count_sch()无参数
操作命令:
DELIMITER $$
CREATE FUNCTION count_sch()
RETURNS INT
BEGIN
DECLARE counts INT DEFAULT 0;
SELECT COUNT(*) FROM sch;
RETURN counts;
END$$
DELIMITER ;
CALL count_sch();
4.创建存储过程avg_sal有参数
操作命令:
DELIMITER $$
CREATE PROCEDURE avg_sal(IN depton INT,IN job VARCHAR(50),OUT avg_salary FLOAT)
BEGIN
SELECT AVG(salary) INTO avg_salary FROM emp WHERE dept=depton AND NAME=job;
END $$
DELIMITER ;
CALL avg_sal(30,);
运行结果: