Detailed description of database view and view

Detailed description of database view and view

Database View Details

-Definition:

A View is a table exported from one or more tables (or views. The view and Table (sometimes different from the view, also known as the basic Table-Base Table) are different. The view is a virtual Table, that is, the data corresponding to the view is not actually stored, the database only stores view definitions. When operating on View data, the system operates the basic table associated with the view according to the view definition.

A view is like a window through which you can see the data you are interested in and its changes in the database.

-Create a view

Syntax for creating views on SQL Server:

CREATE VIEW  [ < database_name > .] [ < owner > .]      view_name [ （ column [ ,...n ] ） ]     [ WITH < view_attribute > [ ,...n ] ]  AS  select_statement      [ WITH CHECK OPTION ]  < view_attribute > ::=  　{ ENCRYPTION | SCHEMABINDING |        VIEW_METADATA }  

WITH check OPTION indicates that the updated, deleted, or inserted rows must meet the predicate conditions in the view definition during UPDATE, INSERT, and delete operations on The View.

• Example 1: Create S_view1 that displays student ID, name, gender, and other information older than 20 years old

create view S_view1   as  select sno,sname,sex from s where age>20  ;

• Example 2: Create v_score1. The source of the basic table is S, C, SC. The selected fields are sno and sname in Table S, and the cname in Table C and score in Table SC; the data to be queried is the score of the students whose student number is 20030001.

create view v_score1  As  Select s.sno,s.sname,c.cname,sc.score  From s,c,sc  Where s.sno=sc.sno and c.cno=sc.cno and sno=  “  20030001”  ;

• Example 3: Create a view for students in the Information System. Make sure that the view is only for students in the Information System during modification and insertion.

Create view IS_StudentASselect Sno,Sname,Sagefrom Studentwhere Sdept='IS'with check option;

The with check option clause IS added to the preceding view. When you insert, modify, and delete the view, the RDBMS automatically adds the Sdept = 'is column and column subset View: if a view is only exported from a single basic table, and only some columns are removed, but the primary key is retained, it is called the row and column subset view. For example, in the preceding example, 1 and 3 are a row and column subset view.

-Query view

After the view is defined, it can be queried like a basic table.

• Example 4 search for students younger than 20 in the Information System

select Sno,Sagefrom IS_Studentwhere Sage<20;

The converted statement is

select Sno,Sagefrom Studentwhere Sdept='IS' and Sage<20;

Sometimes, only one view cannot query the required data, for example, 5.

• Example 5: Query Information students who have taken course 1

select IS_Student.Sno,Snamefrom IS_Student,SCwhere IS_Student.Sno=SC.Sno and SC.Cno='1';

The preceding query involves the view IS_Student and basic table SC.

-Update View

Updating a view refers to inserting, deleting, and modifying data through a view.

Because the view is a virtual table that does not actually store data, the update of the view must be converted to the update of the basic table. Therefore, to prevent users from intentionally or unintentionally performing operations on the data, you can add the with check option when defining the view. Example 3:

• Example 6 change the name of the student whose IS_Student is good and whose secondary school number is 200215122 to "zhangda fart"

Update IS_Studentset Sname = 'Big fart 'where Sno = '000000 ';

After conversion, the corresponding statement is

Update Studentset Sname = 'Big fart 'where Sno = '000000' and Sdept = 'is ';

• Example 7 insert a new student record number 200215129 to the Student View of the information system. The name is 'Li hong' and the age is 21.

Insert into is_studentvalues ('20170901', 'Li hong', 21 );

The converted statement is

Insert into Student (Sno. Sname, Sage, Sdept) values ('000000', 'Li hong', 21, 'is ');

• Example 8 delete a student whose student ID is 200215111 from the Information Department

delete from IS_Studentwhere Sno='200215111';

The converted statement is

deletefrom Studentwhere Sno='200215111' and Sdept='IS';

View update restrictions

If the view definition contains the following clause, it cannot be updated.

• Group by and Having
• Join
• Subquery
• Union
• Aggregation Function MAX MIN COUNT AVG
• DISTINCT
• Export Column

-View Deletion

The view deletion format is

Drop view <VIEW Name> [CASCADE];

After a view is deleted, its definition is deleted from the data dictionary. CASCADE is used for cascading deletion.

• Example 9 Delete view IS_Student

Drop VIEW IS_Student;

-Advantages of views-1. simplified user operations

A view not only simplifies users' understanding of data, but also simplifies their operations. Frequently Used queries can be defined as views, so that you do not have to specify all the conditions for subsequent operations.

-2 It provides logical independence for restructuring the database to a certain extent.

View can make applications and database tables independent to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view.

-3 Security

Users can only query and modify the data they can see through the view. Other data in the database is neither visible nor accessible. Database authorization commands allow each user to restrict the retrieval of a database to a specific database object, but cannot authorize the database to a specific row or column. Through views, users can be restricted to different subsets of data.

View disadvantages

● Performance: SQL Server must convert View queries to basic table queries. If this view is defined by a complex multi-Table query, even if it is a simple View query, it also takes some time for SQL Server to turn it into a complex combination.

● Modify restrictions: when you try to modify certain rows of a view, SQL Server must convert it to modify certain rows of the basic table. For a simple view, this is very convenient, but for a complicated view, it may not be modifiable.