SQL View)

Document directory

• What is a view?
• SQL create view syntax

• Next Page

A view is a visualized table.

This chapter describes how to create, update, and delete views.

What is the SQL create view statement?

In SQL, a view is a visualized table based on the SQL statement result set.

A view contains rows and columns, just like a real table. A field in a view is a field from a real table in one or more databases. You can add SQL functions, where statements, and join statements to the view, and submit data, just like a single table.

Note: the database design and structure will not be affected by functions, where, or join statements in the view.

SQL create view syntax

CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

Note: The view always displays the latest data. Whenever a user queries a view, the database engine uses SQL statements to recreate the data.

SQL create view instance

You can use a view within a query, within a stored procedure, or within another view. By adding functions and joins to the view, we can precisely submit the data we want to submit to users.

The sample database northwind has some installed views by default. View "current product list" lists all products in use from the products table. This view is created using the following SQL:

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No

We can query the preceding view:

 

SELECT * FROM [Current Product List]

Another view of the northwind Sample Database selects products whose unit prices are higher than the average unit prices in the products table:

CREATE VIEW [Products Above Average Price] ASSELECT ProductName,UnitPriceFROM ProductsWHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 

We can query the preceding view as follows:

SELECT * FROM [Products Above Average Price]

Another view instance from the northwind database calculates the total sales volume of each category in 1997. Note that this view selects data from another view named "product sales for 1997:

CREATE VIEW [Category Sales For 1997] ASSELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySalesFROM [Product Sales for 1997]GROUP BY CategoryName 

We can query the preceding view as follows:

SELECT * FROM [Category Sales For 1997]

You can also add conditions to the query. Now, we only need to check all the sales volume of the "beverages" class:

SELECT * FROM [Category Sales For 1997]WHERE CategoryName='Beverages'

SQL update View

You can use the following syntax to update the View:

SQL CREATE OR REPLACE VIEW SyntaxCREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

Now, we want to add the "category" column to the "current product list" view. We will update the view through the following SQL:

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductName,CategoryFROM ProductsWHERE Discontinued=No

SQL revocation View

You can use the drop view command to delete a view.

SQL DROP VIEW SyntaxDROP VIEW view_name

View is very important in the database development process, which greatly improves the query speed. Therefore, we learn to create views and use them effectively. (1) standard views of quasi-Table SQL views are relatively simple and everyone is using them. We will not describe them here. (2) standard views using dynamic views have a serious limitation, that is, standard views do not support parameters. As the saying goes, "the legal network is great, so we can use a view with parameters. The implementation method is to use a user-defined Table value function as a dynamic view that supports parameters: Create Function fntestview (@ m_id INT) returns table as return (select * from view name where condition = @ m_id) so that they can be referenced in the from clause of the SELECT statement. Usage: Select * From fntestview (2)

(3) How can we add an index to a view when using an index view? In fact, an index can be added to a view like a common table, when SQL server must join many tables, this technology can greatly improve the performance of select statements. When a unique clustered index (unique clusterd index) is created on the view, SQL Server materialized the view. Let's take a look at the example below: Create view DBO. vtusers with schemabinding as select DBO. tusers. userid, DBO. tusers. username, DBO. tusers. password, DBO. tusers. question, DBO. tusers. answer, DBO. tusers. email, DBO. tusers. realname, DBO. tusers. sex, DBO. tusers. birthday, DBO. tusers. country, DBO. tusers. city, DBO. tusers. address, dbo.tusers.zip, DBO. tusers. tele, DBO. tusers. exdate, DBO. tusers. totalfund, DBO. tusers. ordertotal, DBO. tusers. jifen, DBO. tusers. PID, DBO. tusers. agentid, DBO. tusers. agentid2, DBO. tusers. agentid3, DBO. tusers. status, DBO. tusers. checkmod, DBO. tusers. account, DBO. tusers. bank, DBO. tusers. logip, DBO. tusers. sitename, DBO. tuserinfo. siteurl from DBO. tusers inner join DBO. tuserinfo on DBO. tusers. userid = DBO. tuserinfo. userid where DBO. tuserinfo. userid> 20

Create unique clustered index idxvtuser on vtusers (userid)

Although this index only references a subset of a column, this index contains all columns in the leaf-level node (this is also true for each clustered index ). Like standard views, creating and using an index view is also limited. To convert a standard view to an index view, follow the following rules:. the view must be created using the with schemabinding option; B. in this view, you cannot use other views, export tables, row set functions, or self-queries. That is to say, you can only use tables. the basic table used by the view must belong to the same owner as the view. D. views can only link tables in the same database; E. A view cannot contain an external link or a self-link. In other words, an inner join can only be used for a chain table, and the same table cannot be used before and after inner join. left (right) Join or left (right) cannot be used) outer Join; let's look at the example below: for example, the following view is created (self-linked): Create view DBO. vtusers with schemabinding as select DBO. tusers. userid, DBO. tusers. username, DBO. tusers. password, DBO. Tusers. question, DBO. tusers. answer, DBO. tusers. email, DBO. tusers. realname from DBO. tusers inner join DBO. tusers as t on DBO. tusers. userid = T. the USERID view can be created. However, when you create an index, an error occurs when you create a unique clustered index idxvtuser on vtusers (userid). Then, you can view the following view (external connection ): create view DBO. vtusers with schemabinding as select DBO. tusers. userid, DBO. tusers. username, DBO. tusers. password, DBO. tusers. question, DBO. tusers. answe R, DBO. tusers. email, DBO. tusers. realname from DBO. tusers left Outer Join DBO. tuserinfo on DBO. tusers. userid = DBO. tuserinfo. an error occurs when userid creates an index. F. the view cannot contain the union clause, top clause, order by clause, having clause, rollup clause, cube clause, compute clause, compute by clause, or distinct keyword; G. views do not allow certain set functions, such as Count (*), count_big (*), AVG (), max (), min (), STDev (), stdevp (), VAR (), or varp (); H. the view cannot use statements such as select *, that is, all fields in the view must be specified. the view cannot contain columns of the text, ntext, and image types. J. if the view contains a group by clause, it must contain count_big (*); K. all labels and user-defined functions in the view must be referenced using two-segment names, that is, the owner. table or function name; L. all basic tables and views must be created using set ansi_nulls on. Creation; M. when an index is created or after the index is created, the following operations must be performed explicitly or implicitly: set ansi_nulls on set ansi_padding on set ansi_warnings on set arithabort on set rule on set quoted_identifier on set numeric_roundabort off related information or meaning of each option, you can refer to SQL Server online books, n. the index view can only be created in enterprise or development or later versions of SQL Server. If a view can add a unique clustered index, the view can also add non-clustered indexes like a database table after a unique clustered index is added. Create index idxvtusers on vtusers (username, realname ).
We hope you can add more methods and suggestions on views.