创建视图

创建视图

1、创建视图v_emp_dept_id_1，查询销售部门的员工姓名和家庭住址

CREATE VIEW v_emp_dept_id_1 AS
SELECT e.emp_name,e.address FROM dept d, emp e WHERE e.dept_id = 
(SELECT dept_id FROM dept WHERE dept_name = '销售部');
SELECT * FROM v_emp_dept_id_1;

2、创建视图v_emp_dept，查询销售部门员工姓名和家庭住址及部门名称。

CREATE VIEW v_emp_dept AS
SELECT e.emp_name,e.address,d.dept_name FROM dept d, emp e WHERE e.dept_id = 
(SELECT dept_id FROM dept WHERE dept_name = '销售部');
SELECT * FROM v_emp_dept;

3、创建视图v_dept_emp_count(dept_name,emp_count,avg_salay)，统计每个部门人数并计算平均工资

CREATE VIEW v_dept_emp_count(dept_name,emp_count,avg_salay) AS
SELECT d.dept_name,a.emp_count,a.avg_salay FROM dept d,
(SELECT dept_id,COUNT(1) AS emp_count,AVG(salary) AS avg_salay FROM emp GROUP BY dept_id) a
WHERE d.dept_id = a.dept_id;
SELECT * FROM v_dept_emp_count;

4、修改视图v_emp_dept，查询销售部门员工姓名、家庭住址、工资和部门名称。

 ALTER VIEW v_emp_dept AS 
  SELECT e.emp_name,e.address,e.salary,d.dept_name FROM dept d, emp e 
  WHERE e.dept_id = 
(SELECT dept_id FROM dept WHERE dept_name = '销售部');
 SELECT * FROM v_emp_dept;

5、查看视图名称；
查看视图结构；

DESC v_emp_dept;

​ 查看创建视图语句；

SHOW TABLE STATUS LIKE 'v_emp_dept';

6、删除以上三个视图。

DROP VIEW v_emp_dept;
DROP VIEW v_emp_dept_id_1;
DROP VIEW v_dept_emp_count;


SELECT * FROM emp;
SELECT * FROM dept;