Overview and basic operations of the SQL Server Foundation view

Read Catalogue

• One: Overview of views
• II: Classification of views
• Three: The advantages and effects of the view
• Four: basic operation and syntax of the view
• V: Differences in views and tables

The view in the database is a virtual table. As with real tables, views contain a series of column and row data with names, and row and column data are used to freely define the tables referenced by the view and query, and are generated dynamically when the view is referenced. This article will introduce some examples of the concept of views, the role of views, creating views, viewing views, modifying views, updating and deleting views of SQL Server database knowledge.

Back to TopOne: 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.

Once a view is defined, it is stored in a database, and its corresponding data is not stored in the database as if it were 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.

Back to TopII: Classification of views

The views of SQL Server can be divided into 3 categories: Standard view, indexed view, partitioned view

2.1. Standard view

Standard views combine data from one or more tables to get the most benefit from using views, including focusing on specific data and simplifying data operations.

2.2. Indexed views

An indexed view is a materialized view, that is, it has been computed and stored. You can create an index for the view, and a unique clustered index on the image. Indexed views can significantly improve the performance of certain types of queries, which are especially useful for queries that aggregate many rows, but they are less suited to the basic datasets that are updated frequently.

2.3. Partitioned views

Partitioned views connect the partitioned data of a group of member tables horizontally across a server, so that the data appears to come from a table. A view that connects to a member table in a local instance of SQL Server is a local-sensitive view.

Back to Top Three: The advantages and effects of the view

Views have advantages over reading data directly from a table

3.1. Simplification

What you see is what you need, the view not only simplifies the user's understanding of the data, but also simplifies the operation of them, and those frequently used queries can be defined as views, so that the user does not have to specify all the conditions for each subsequent operation.

3.2. Security

Views can be used as a security mechanism. Users can view and modify only the data they see through the view. Other or table is neither visible nor accessible. If a user wants to access the view, they must be granted access. Access to the table referenced by the view is not affected by the settings of the View permissions.

3.3. Logical data Independence

Views can help users mask the impact of changes in the real-world table structure.

Back to TopFour: basic operation and syntax of the view

4.1. Create a View

-- Grammar CREATE VIEW  as     SELECT from table_nameWHERE condition

Test Data Preparation:

 Usesample_db;Create Tablestudenttable (IDint Identity(1,1)Primary Key, namevarchar( -), GenderChar(2), ageint,)Insert  intostudenttable (name,gender,age)Values    ('Liu Bei','male', -),    ('Zhang Fei','male', -),    ('Guan Yu','male', -);

--Create a Viewif(exists(Select *  fromSys.objectswhereName= 'Student_view'))    Drop ViewStudent_viewGo--Student_view () is not a practical parameter, default is the column name in the underlying tableCreate ViewStudent_view asSelectName,age fromStudenttablewhereAge> -;--Execution ViewSelect *  fromStudent_view;

To view information for a view

• To view the definition information for a view using the sp_help stored procedure
• Use sp_helptext system stored procedures to display rules, default values, unencrypted stored procedures, user-defined functions, triggers or views of text, syntax

exec  ' Student_view ' ; exec ' Student_view ';

To create an encrypted view:

--Encrypted Viewif(exists(Select *  fromSys.objectswhereName= 'student_encryption'))    Drop Viewstudent_encryptionGoCreate Viewstudent_encryption withEncryption--Encrypt as    SelectID, name, age fromstudenttableGo--view_definition is null--View encrypted viewsSelect *  frominformation_schema.viewswheretable_name like 'student_encryption';

　

The View_definition field is shown as NULL from the execution result

4.2. Modifying basic table data using views

(1). Inserting data into a base table from a view

--(1). Inserting data into a base table from a viewCreate ViewStu_insert_view (number, name, gender, age) asSelectId,name,gender,age fromstudenttable;GoSelect *  fromstudenttable;---Insert a piece of dataInsert  intoStu_insert_viewValues('Caocao','male', +);----View the contents of the table after inserting the record. Select *  fromstudenttable;   

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

--(2). Modifying the data of a base table through a view--View the data before you modifySelect *  fromstudenttable;--Modifying DataUpdateStudent_viewSetAge= -whereName='Liu Bei';--View the modified dataSelect *  fromstudenttable;

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

-- Grammar Delete where condition; -- Example Delete where = ' Zhang Fei ' ; Select *  from Student_view; Select *  from Studenttable;

4.3. Fix the View

-- Modify a View Alter View  asselect*fromwhere age>;

4.4. Delete a view

-- Grammar Drop View view_name1,view_name2,......, view_namen; -- The statement can delete multiple views at the same time, separated by commas when deleting each view name.  Dropview studenttable;

Back to top five: differences between views and tables

1. A view is a compiled SQL statement, a visual table based on the result set of an SQL statement, and the table is not;
2. Views (except indexed views) have no actual physical records, whereas basic tables have;
3. Represents the content, the view is the window;
4. The table occupies the physical space, and the view does not occupy the physical space, the view is only the existence of logical concept;
5. A view is a way of looking at a data table, querying data from some fields in a datasheet, 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 the global schema of the 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 corresponding base table.

Transferred from: http://www.cnblogs.com/selene/p/4490503.html

Overview and basic operations of the SQL Server Foundation view