<3> View (SQL)

In order to reduce data redundancy during database design, it is generally designed according to the three paradigm. However, sometimes we need to obtain

Fields are associated with several fields, but they are distributed in different tables. At this time, if you follow the normal path, you need to query several fields at the same time.

Tables are not only difficult to operate but also prone to errors. Of course, We Have shortcuts to integrate all the fields to be queried into a virtual table. This is the view application.

 

Brief Introduction

 

Concept: A view is a table constructed from several basic tables or other views. It is a virtual table whose content is defined by the query. Like a real table,

A view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns

The table referenced by the View query is dynamically generated when the view is referenced.

 ViewAdvantages:

1. view focuses on specific data.

A view allows users or program developers to only view the data they need, without exposing all the information and fields in the table. This enhances data security.

2. Simplified data operations and easy maintenance.

We can define the commonly used multi-table joint query data or a specific result set as a view, which plays a role in modular data. When using this data, we can directly query this view without having to write long SQL statements everywhere, which also plays a role of easy maintenance.

3. The view can only query data.

For example, for different users, we only provide some data to them. In this way, we can limit the result set in the view and return it to the view. In this way, no matter how the user defines the query conditions for the view, he cannot query the data that we do not want to provide to him.

 

Trial

To reduce data redundancy when designing the database of the data room charging system, the original student table is divided into two tables: the card table and the student table. Card table only

Card information. The student table only stores student information. This follows the requirements of the three paradigm, but it cannot be as original when querying information.

Convenient. You need to query both tables at the same time. So I tried the view here.

1. Create a view

2. Select the tables or views involved

3. Select the fields to be queried in each table.

4. Save the name

5. Practical Application

Just like a normal table. "Select * from StuCardView_info whereCID = @ CID"

 

Although a view can bring us various conveniences, it does not mean we can abuse it. Because a view is an SQL statement, its results are dynamically generated during each call. If an unreasonable view is defined, performance loss will inevitably occur.

The following are some notes for creating a view:

1. The operation view is slower than directly operating the basic table, so we should avoid creating views on large tables as much as possible.

2. Try not to create a nested view, that is, use the view in the view. In this way, the basic table will be accessed multiple times during query, resulting in performance loss.

3. Try to return only the required information in the view, and try not to use tables that do not need to be accessed in the view.

4. You can use stored procedures instead of large tables or complex-defined views.

5. frequently-used views can be replaced by indexed views.

 

The understanding of the view is still very simple, and the above instances are only the most basic application of the view. Others, such as index view, split view, and summary view, are not available yet.

Specific applications. I have not tried to update the view, but I still need to do a lot of work.