Indexes, views, cursors, stored procedures, and triggers (1-2. Views)

I. view itself

A>, Create

Format: Create view <view Name> [<View list>] As subquery

Commentary: The View list is optional. You can write the View list name at will. If the list name is omitted (however, the view is obtained by joining multiple tables and the same column name exists in different tables, or, when the View column name is an expression or library function calculation result, the view list name cannot be omitted.) The View column name is the same as the list name.

Example: Create a physics department instructor name and gender view sub_t. (The instructor information table is teacher_info)

Creat view sub_t (t_name, t_sex)

As select t_name, t_sex from teacher_info where dept = 'physical'

B>, Delete

Format: Drop view <view Name>

Note: After a view is deleted, its definition in the digital dictionary is deleted without affecting the basic table on which it is based.

C>, Modify

Format: Alter view <view Name>
[(View list)] as <subquery>

For example, modify the view sub_t so that it can observe the salaries of teachers in the physics department.

Alter view sub_t (t_name, t_sex, t_salary)

As select t_name, t_sex, t_salary from teacher_info where dept = 'physical'

2. Data in the view (operations on the tables in the View Graph are the same as those on the tables in the base table)

A>, Query

For example, query all the information of a teacher named "Zhang San" in sub_t.

Select * From sub_t where t_name = 'zhang san'

B>, Update

1> Add

Example: Add a record to the view (Instructor name: Li Si; Gender: male; salary: 3000)

Insert into sub_1 (t_name, t_sex, t_salary)

Values ('Lee 4', 'male', 3000)

Process description: when the system executes this statement, it first finds the sub_t definition from the data dictionary, and then combines this definition with the add operation, convert to an equivalent addition to the basic table teacher_info.

2> modify

For example, in sub_1, the salary of instructor Zhang San is changed to 400.

Update sub_1

Set t_salary = 4000

Where t_name = 'zhangsan'

3> Delete

Example: Delete the information of instructor Zhang in view sub_1

Delete from sub_1

Where t_name = 'zhangsan'

Note: The t_ SQL statements for database object operations are almost the same. Compare with the previous summary.

 

 

Supplement:

1. Composite functions used to create a view