SQL Server View article

1 View describes a view is a table that is exported from one or several basic tables (or views). Unlike a base table, it is a virtual table. Only the definition of the view is stored in the database, and there is no data for the view, and the data is still stored in the original base table. So once the data in the underlying table changes, the data queried from the view changes as well. In this sense, a view is like a window through which you can see the data of interest in the database and its changes. 1.1 Overview of views

Views are exported from one or more tables, and behave much like a table, but a view is a virtual table that can query data using a SELECT statement in a view and modify records using INSERT, UPDATE, and DELETE statements, and the operation of the view is ultimately translated into operations on the base data table. The view is not only convenient to operate, but also can guarantee the security of database system.

When a view is defined, it is stored in the database, and the data corresponding to it is not stored in the database as the table data, and the data seen through the view is only the data stored in the base table. It can be used to delete the search, through the view of the data modification, the basic table data corresponding changes, and vice versa.

1.2 Purpose and benefits of using views 1. Focus on specific data: enable users to see and manipulate only data related to them, improving the security of the data.
2. Simplifies data manipulation: Enables users to manipulate data without having to write complex query statements.
3. Customizing user data: Enables different levels of users to see different data in different ways.
4. Merge detached data: Views can split data horizontally and vertically, but the structure of the original database remains intact.
2 CREATE VIEW Syntax:

[WITH CHECK option]--forces all data that passes the same modification to satisfy the conditions specified in the SELECT statement as[with encryption]--the definition of the encrypted view, which the user can only view without modification. [(Column Name table)]create view

Create a student table first

Use marvel_db; --Creating a Student table CREATE table stutable ( ID int identity (primary) key,--ID primary key, self-increment name varchar, ge NDEr char (2), age int,)--inserts data into the table insert into stutable (name,gender,age) VALUES (' Liu Bang ', ' male ', ' 23 '), (' Xiang Yu ', ' Male ', 22), (' Han Letter ', ' Male ', 21); INSERT INTO stutable (name,gender,age) VALUES (' Xiao ', ' Male ',

Create a View

--Create a view if (exists (SELECT * from sys.objects where name = ' Stu_view ')) Drop View Stu_viewgo--stu_view () is not a practical parameter, default to the column name in the underlying table --note that create view must be a statement inside the batch create view Stu_view as select Name,age from stutable where age>20;go--execute view select * FROM St U_view;

Query Result:

3 Modifying views

Goalter View Stu_view Asselect * from stutable where Age>22;goselect * from Stu_view

Displaying results: 4 Delete View

go--syntax drop view view_name1,view_name2,......, view_namen;--The statement can delete multiple views at the same time, separated by commas between the deleted view names.

Example: Delete View Stu_view

--Syntax Drop view stu_view;--The statement can delete multiple views at the same time, separated by commas when deleting each view name.

5 Manage data in tables by view (1). Inserting data into a base table from a view

Attention:

1. You can insert data into the base table through a view, but the inserted data is actually stored in the base table, not in the view.

2. If the view references more than one table, the columns inserted using the INSERT statement must belong to the same table.

3. When creating a view with the check option defined, when inserting data into a base table using a view, you must ensure that the inserted data satisfies the constraints that define the view.

--(1). Inserting data into a base table from a View Gocreate view Stu_insert_view (number, name, gender, age) Asselect id,name,gender,age from Stutable;goselect * from stutable;---Inserts a data insert into stu_insert_view values (' Sun Quan ', ' Male '),----view the contents of the table after inserting the record. SELECT * from Stutable;

Show Results:

(2). Modifying the data of a base table through a view

--View the data before the modification SELECT * from Stutable;

Show Results:

--Modify Data Update Stu_insert_view set age =30 where name = ' Liu Bang ';--View modified data select * from Stutable;

The results show:

(3). Delete the data for the base table from the view

Attention:

1. The data to be deleted must be included in the result set of the view.

2. If the view references more than one table, you cannot delete the data with the Delete command.

Grammar

--Grammar Delete Stu_insert_view where condition;

Before deleting:

Delete:

--Example Delete stu_insert_view where name = ' Liu Bang '; select * from Stu_insert_view;select * from stutable;

Displaying results: 6 Summary 1. Usage scenarios: 1. Frequently used queries, or more complex federated queries should create views that are optimized for performance
2. When it comes to rights management, such as some fields in a table that contain confidential information and should not be accessed by low-privileged users, they are given a view that is appropriate for their rights, so that they can read their own data.
2. The difference between a view and a table:

1. The view is a compiled SQL statement, which is a visual table based on the result set of the SQL statement, and the table is not;
2. Views (except indexed views) have no actual physical records, and basic tables have;
3. Presentation of content, view is a window;
4. The table occupies the physical space, and the view does not occupy the physical space, the view is only the existence of the logical concept;
5. A view is a way of looking at a data table, querying data from some fields in a data table, just a collection of some SQL statements. From a security standpoint, the view can prevent users from touching the data table, so as not to know the table structure;
6. Table is a global schema table, is a real table, view data local mode table, is a virtual table;
7. The creation and deletion of views affects only the view itself and does not affect the underlying table.

Reprinted from: 53353475

SQL Server View article