sql--View

1. Why use a View

1) Improve the security of data, different permissions of users can view the different data.

2) in line with the user's daily business logic

2. What is a view

A view is a way to view data in one or more tables in a database.

A view is a virtual table. It is usually created as a subset of rows or columns from one or more tables, and of course he can contain all the rows and columns.

A view is not a collection of data values stored in a database, and its rows and columns come from the tables referenced in the query. At execution time, he directly displays the data from the table.

3. The role of the view

Filter rows in a table

Prevent unauthorized users from accessing sensitive data

Abstract multiple physical data tables into a single logical data table

4. How to create a view

Method One: Use SSMs, select the database, choose the "view" option and right-click, select "New view Item" in the Popup shortcut menu, add a Pharmacy query table, select the column you want to see, and save the view.

Method Two: Use statement creation, syntax:

CREATE VIEW View_name

As

<select Statement >

5. View syntax collation:

1) Create a view

CREATE VIEW View_name

As

<select Statement >

2) Modify the View

Alter VIEW View_name

As

<select Statement >

3) Delete View

Drop View View_name

4) Modify the view name

exec sp_rename View_oldname view_newname

5) View View

Select Column name from View_name

6. Precautions

Multiple tables can be used in each view

Similar to a query, a view can nest another view, but it is best not to exceed 3 layers.

Select statement cannot include content in view definition

ORDER BY clause unless there is a TOP clause in the Select column of the SELECT statement

into keyword

Referencing a temporary table or table variable

7. Advantages and disadvantages of views

Advantages: Simple, secure, logical data Independent

Cons: Performance, modification restrictions

If a function exists in the view or involves a complex multi-table query, the user spends a certain amount of time in querying the view.

For a simple view, you can update with the UPDATE statement, and you may not be able to use it for complex views. Deletion is also true. So it's best to stay on top of the query for view usage.

sql--View