1、掌握视图功能和作用

2、掌握视图创建和管理办法

代码：

mysql> create or replace view Emp_view1 -> as select 员工编号,姓名,工作年限,学历 -> from employees where employees.性别=1 -> with check option;

`SELECT * from Emp_view1;

代码：

mysql> select * from Emp_view1 -> where 工作年限>=2;

代码：

mysql> create or replace view Emp_view2 as -> select Employees.员工编号, Employees.姓名,Departments.部门名称,Salary.收入 -> from Employees,Departments,Salary -> where Employees.员工部门号 = Departments.部门编号 -> and Employees.员工编号 = Salary.员工编号 -> with check option;

SELECT * from Emp_view2;

代码：

mysql> select 员工编号,姓名,收入 -> from Emp_view2 -> where 部门名称=‘研发部’;

历、出生日期、性别、工作年限和所在部门编号。在创建视图的时候加上 WITH CHECK OPTION 子句。

代码：

mysql> create or replace view Emp_view3 -> as -> select 员工编号,姓名,学历,出生日期,性别,工作年限,员工部门号 -> from Employees -> where 工作年限>=2;

SELECT * from Emp_view3;

代码：

mysql> insert into Emp_view3 -> values -> (041110,“钟晓玲”,“博士”,“1973-12-01”,1,3,4);

SELECT * from Emp_view3;

代码：

mysql> update Emp_view2 -> set 收入=收入+200.00 -> where 姓名=“李丽”;

mysql> SELECT * from Emp_view2;

代码：

mysql> delete from Emp_view3 -> where 学历=“本科”;

mysql> SELECT * from Emp_view3;

代码：

mysql> alter view Emp_view1 -> as -> select Employees.员工编号,Employees.姓名,Salary.收入 -> from Employees,Salary -> where Employees.员工编号=Salary.员工编号;

mysql> SELECT * from Emp_view1;

代码：

mysql> drop view Emp_view2, Emp_view3;

片转存中…(img-vo2ftppu-1650015123995)]

代码：

mysql> drop view Emp_view2, Emp_view3;

Query OK, 0 rows affected (0.00 sec)