Introduction to database sql view

Recommendation (free): SQL

View introduction

View(View ) is a virtually existing table that is basically transparent to users using the view. The view does not actually exist in the database. The row and column data come from the table used in the query of the custom view and are dynamically generated when the view is used.

Define view

create view view name as select statement

SELECT * FROM provinces;

CREATE VIEW v_pro AS SELECT * FROM provinces;

SELECT * FROM v_pro;

View view

When viewing the table, the view table will be Also listed

show tables;

Use view

select * from v_pro;

Delete View

drop view View name;

The role of view

• Simple: Improves reusability, Like a function.
• Security: Improved security performance, different views can be set for different users.
• Data independence: Once the structure of the view is determined, the impact of changes in the table structure on users can be shielded. Adding columns to the source table has no impact on the view; modifying the column name of the source table can be done by Modify the view to solve the problem without causing any impact on visitors

Modification of the view

The view cannot be modified if one of the following contents is included

• The select clause contains distinct
• The select clause contains the group function
• The select statement contains the group by clause
• selecy statement red contains the order by clause
• The where clause contains related subqueries
• The from clause contains multiple tables
• If there are calculated columns in the view, it cannot be updated
• If there are in the base table If a column with a non-null constraint does not appear in the view definition, the insert operation cannot be performed.

The above is the detailed content of Introduction to database sql view. For more information, please follow other related articles on the PHP Chinese website!