Original: 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