dept表
emp表
创建视图v_emp_dept_id_1,查询销售部门的员工姓名和家庭住址
mysql> create view v_emp_dept_id_1 as
-> select emp_name,address from emp e
-> inner join dept d
-> on e.dept_id=d.dept_id
-> where dept_name='销售部';
创建视图v_emp_dept,查询销售部门员工姓名和家庭住址及部门名称。
mysql> create view v_emp_dept as
-> select emp_name,address,dept_name from emp e
-> inner join dept d
-> on e.dept_id=d.dept_id
-> where dept_name='销售部';
创建视图v_dept_emp_count (dept_name,emp_count,avg_salay),统计每个部门人数并计算平均工?修改视图v emp dept,查询销售部门员工姓名、家庭住址、工资和部门名称。
mysql> create view v_dept_emp_count(dept_name,emp_count,avg_salay) as
-> select dept_name,count(1),avg(salary)
-> from emp e inner join dept d
-> on e.dept_id=d.dept_id
-> group by dept_name;
查看视图名称;
查看视图结构;
查看创建视图语句;
删除以上三个视图。
存储过程作业:
创建一个提取emp表所有员工工资和的存储过程s1;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_1`.`s1`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT SUM(salary) FROM emp;
END$$
DELIMITER ;
调用存储过程s1;
创建存储过程s2,实现输入员工姓名后返回员工的家庭住址;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_1`.`s2`(IN NAME CHAR(20))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT address FROM emp
WHERE emp_name=NAME;
END$$
DELIMITER ;
调用存储过程s2;
创建一个存储过程avg sai,有3个参数,分别是dept,gender,接收平均工资,功能査询emp表dep;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_1`.`avg_sai`(IN dept CHAR(20),IN sex CHAR(2),OUT avg_sai DECIMAL(10,2))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT AVG(salary) INTO avg_sai FROM emp e
INNER JOIN dept d
ON e.dept_id=d.dept_id
WHERE dept_name=dept
AND gender=sex;
END$$
DELIMITER ;
调用存储过程avg sai;
删除以上存储过程;