mysql Database experiment training 6, Data view （ detailed ）

One 、 Purpose of training ：

1、 Master the function and function of view

2、 Master view creation and management methods

Two 、 Training content ：

Yes YGGL The database completes the following view operations ：

1、 In the employee management database YGGL Create a view in Emp_view1, Include employee numbers of all male employees 、 full name 、 Years of work and education ：

Code ：

mysql> create or replace view Emp_view1
-> as select Employee number , full name , Years of service , Education
-> from employees where employees. Gender =1
-> with check option;

`SELECT * from Emp_view1;

2、 from Emp_viewl Query the information of employees who have worked for more than two years .

Code ：

mysql> select * from Emp_view1
-> where Years of service >=2;

3、 Create view Emp_view 2, Include employee number 、 full name 、 Department name and income .

Code ：

mysql> create or replace view Emp_view2 as
-> select Employees. Employee number , Employees. full name ,Departments. Department name ,Salary. income
-> from Employees,Departments,Salary
-> where Employees. Employee Department No = Departments. Department number
-> and Employees. Employee number = Salary. Employee number
-> with check option;

SELECT * from Emp_view2;

4、 from Emp view2 Check the employee number of the R & D department 、 Name and income .

Code ：

mysql> select Employee number , full name , income
-> from Emp_view2
-> where Department name =‘ R & D department ’;

5、 Create view Emp_ view3, Including all working years 2 Employee number of employees with more than years 、 full name 、 learn

calendar 、 Date of birth 、 Gender 、 Working years and department number . Add WITH CHECK
OPTION Clause .

Code ：

mysql> create or replace view Emp_view3
-> as
-> select Employee number , full name , Education , Date of birth , Gender , Years of service , Employee Department No
-> from Employees
-> where Years of service >=2;

SELECT * from Emp_view3;

6、 stay Emp_view3 Insert a record :（041110, Zhong Xiaoling , Doctor ,1973-12-01, male ,3,4)

Code ：

mysql> insert into Emp_view3
-> values
-> (041110,“ Zhong Xiaoling ”,“ Doctor ”,“1973-12-01”,1,3,4);

SELECT * from Emp_view3;

7、 Modify the view Emp view2 Lieutenant general “ Li Li ” Income plus 200 element .

Code ：

mysql> update Emp_view2
-> set income = income +200.00
-> where full name =“ Li Li ”;

mysql> SELECT * from Emp_view2;

8、 Delete view Emp_view3 in “ Undergraduate ” Educated employees .

Code ：

mysql> delete from Emp_view3
-> where Education =“ Undergraduate ”;

mysql> SELECT * from Emp_view3;

9、 Modify the view Emp_view1, Include employee number 、 Name and actual income .

Code ：

mysql> alter view Emp_view1
-> as
-> select Employees. Employee number ,Employees. full name ,Salary. income
-> from Employees,Salary
-> where Employees. Employee number =Salary. Employee number ;

mysql> SELECT * from Emp_view1;

10、 Delete view Emp_view2 and Emp_view3.

Code ：

mysql> drop view Emp_view2, Emp_view3;

Chip transfer in progress …(img-vo2ftppu-1650015123995)]

10、 Delete view Emp_view2 and Emp_view3.

Code ：

mysql> drop view Emp_view2, Emp_view3;

Query OK, 0 rows affected (0.00 sec)

11、 Pretty girls, please pay attention , Continuous updating ing...