视图与函数相关练习

-- 创建视图查询员工的姓名，职位，工资和部门编号，并通过视图查询
create VIEW view1 AS
SELECT ename,job,sal,d.deptno FROM  emp e INNER JOIN dept d ON e.DEPTNO=d.DEPTNO;
-- 通过视图查询
SELECT* FROM view1;
-- 创建视图查询工资大于3000的员工名称，职位，工资和部门编号，并通过视图查询
CREATE view view2 AS
SELECT ename,job,sal,d.deptno FROM  emp e INNER JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>=3000;
SELECT*FROM view2;
-- 创建查询职位是“clark”的员工名称，职位，工资和部门编号，并通过视图查询
CREATE view view3 AS
SELECT ename,job,sal,d.deptno FROM  emp e INNER JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE e.ename="clark";
SELECT*FROM view3;
-- 创建视图，查询员工表中工资大于2000的员工信息，
-- 通过上面视图插入一条员工信息，要求工资大于1000
-- 测试，视图加了with check option 后，上面操作是否成功
CREATE VIEW view4 AS
SELECT *from emp WHERE  SAL>2000
SELECT *FROM view4;
INSERT INTO view4 VALUES(7890,'zkas',null,null,'1980-12-17',2000,null,null);
SELECT *FROM view4;
SELECT *FROM emp;
ALTER VIEW view4
AS
SELECT *from emp WHERE  SAL>2000 WITH CHECK OPTION;
INSERT INTO view4 VALUES(7892,'zkas',null,null,'1980-12-17',2000,null,null);
-- 查看定义视图
show CREATE VIEW view1;
-- 查看视图
SHOW TABLES
--  删除视图、
DROP VIEW view1;
-- 创建函数，查询最高的工资
CREATE function  myfun() RETURNS INT(9)
BEGIN 
DECLARE sal2 INT(9);
SELECT MAX(sal) INTO sal2 FROM emp;
RETURN sal2;
end;
-- 调用函数
SELECT myfun();
-- 创建一个函数，输入课程编号，返回课程名字
CREATE FUNCTION myfun2( ID INT(9)) RETURNS VARCHAR(16)
BEGIN
DECLARE name2 VARCHAR(16);
SELECT SubjectName INTO name2  FROM `subject` WHERE SubjectId=ID;
RETURN name2;
END; 
SELECT myfun2(3);