SQL Server create View

What is a view?

As we all know, our country is now "God 7. From the moon landing in the United States, the heavens are no longer mythical. It is said that in the United States, you only need to pay a few hundred thousand dollars to make a space trip to the moon. Therefore, we believe that in the near future, traveling to Heaven will enter the lives of our people. What a magical thing is that. Many Americans watched our Earth with telescopes last day, and the Earth shapes were different. Some are green oranges, some are white pears, and some are blue watermelons. Why? You will definitely say: It's because you see different shapes from different angles (perspectives. Then, we can see different "Images" from different perspectives. This is the view we will talk about right away. The same is true for our student information. For the student information and scores, the teacher is more concerned about the student's score and whether or not to take the test, including the name, student ID, written examination, computer test, and whether the student passes the test. The class teacher is more concerned with the student archive, including the name, student ID, gender, and age.

In fact, a view is a virtual table, which indicates part of the data of a table or the comprehensive data of multiple tables. Its structure and data are based on the query of the table.

• The view does not store data, but is stored in the original table referenced by the view (base table ).

• Different Views can be created for the same original table based on different users' needs

 

View usage

-Filter rows in a table

-Prevent Unauthorized users from accessing sensitive data

-Reduce database complexity

-Abstract multiple physical databases into one logical database

 

Using views can bring many benefits to users and developers. Specifically:

1. Benefits to end users

(1) The results are easier to understand

When creating a view, you can change the column name to a meaningful name, making it easier for you to understand what the column represents. Modifying the column name in the view does not affect the column name of the base table.

(2) easier Data Acquisition

Many people do not know much about SQL, so it is difficult for them to create complex queries for multiple tables. You can create a view to allow you to access data in multiple tables.

 

2. Benefits for developers

(1) It is easier to restrict Data Retrieval

Developers sometimes need to hide information in certain rows or columns. By using views, users can flexibly access the data they need and ensure the security of other data in the same or other tables. To achieve this goal, you can exclude columns that are confidential to users when creating a view.

(2) more convenient application maintenance

Debugging view is easier than debugging query. It is easier to trace errors in each step of a view because all steps are part of the view.

 

How to Create a view

• Syntax for creating views using T-SQL statements

Create view view_name

As

<SELECT statement>

If exists (select * From sysobjects where/* Check for existence */

Name = 'view _ stuinfo_stumarks ')

Drop view view_stuinfo_stumarks/* Delete view */

Go

Create view view_stuinfo_stumarks/* Create view */

As

Select name = stuname, student ID = stuinfo. stuno,

Test score = writtenexam, test score = labexam,

Average score = (writtenexam + labexam)/2

From stuinfo left join stumarks

On stuinfo. stuno = stumarks. stuno

Go

Select * From view_stuinfo_stumarks/* use view */

 

The structure and data of a virtual table exported from one or more tables or views are based on the query of the table.

Theoretically, it can be used like a common physical table, such as adding, deleting, modifying, and querying data. Modifying data in a view is actually modifying

Original data table. Because there are many restrictions on modifying views, in actual development, views are generally used only for queries.