SQL Application and Development: (4) view applications and SQL Application Development View

SQL Application and Development: (4) view applications and SQL Application Development View

A view is a table created based on a predefined query. Its definition exists as a schema object. Like a base table, a view contains a series of columns and row data with names. However, unlike a persistent base table, the view does not store any data. The data of rows and columns can be used to define the base table referenced by the View query and dynamically generate the view when the view is applied. The view is a virtual table because the data corresponding to the view is not actually stored in the database in the view structure, but in the table referenced by the view.

For a base table with multiple references, the view function is similar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. That is to say, a view is a specific subset of data in some tables or other views in a database. Once a view is defined, it can be used just like a basic table.

1. Understand the view

A view combines the features of a base table and a query: You can extract a dataset (query feature) from one or more related base tables. You can use a view to update information in the view, and permanently store the results to the disk (Table feature ).

A view can also be used as a security mechanism by allowing users to access data through a view without granting users the permission to directly access the base table.

The advantages of a view are as follows:

Centralized data display: Views enable users to focus on specific data they are interested in and specific tasks they are responsible for, improving data operation efficiency.

Simplify data operations: views can greatly simplify user operations on data. You can often use connections, projections, joint queries, or select a query definition view. In this way, you do not have to re-write these complex query statements each time you execute the same query, you only need a simple View query statement. The visible view hides complex join operations between tables.

Custom Data: Views allow different users to view different or identical datasets in different ways. This is also true when users of different levels share a single database.

Export and import data: You can use views to export data to other applications.

Merge split data: In some cases, due to the large data volume in the table, you may need to perform horizontal or vertical table segmentation during the table design process, however, such changes in the table structure will have a negative impact on the application. You can use the view to remaintain the original structure, so that the external mode remains unchanged. The original application can still use the view to reload data.

Security Mechanism: A view can be used as a security mechanism. You can only view and modify the data you can view. Other databases or tables are neither visible nor accessible. If a user wants to access the view result set, he/she must be granted the access permission. The access permission of the table referenced by the view does not affect the settings of the view permission.

2. View operations

2.1 "add" Operation

CreateUpdatable ViewBasic statement:

Create view <view_name> [(<view_column_name>)]

AS <query_expression>

[With check option]

The placeholder <view_name> indicates the name of the created view. The placeholder <view_column_name> indicates the column name provided by the view. The placeholder <with check opyion> indicates that the row is modified through the view, after the modification is submitted, you can still view the modified data in the view.

For example, create a VIEW named "customer _ VIEW" in the "sales management system" of the database. This VIEW contains the column "number of sales personnel" and "number of customers ", the execution statement is as follows:

Create view customer _ VIEW (Clerk ID, number of customers)

AS

Clerk ID of SELECT, COUNT (customer name)

FROM customer information

Clerk ID of GROUP

After the preceding statement is executed, a VIEW named "customer _ VIEW" is created in the corresponding database to query the number of customers received by each clerk.

If you create a view, this view does not summarize or aggregate data. Each column corresponds to a source column in a base table, and each row corresponds to a source row in a base table, this view is an updatable view. For example, in the Database "Sales Management System", create a VIEW named "salesman _ VIEW, the column in this view is from the "salesman name", "Home Address", and "phone number" columns in the "salesman information" column of the base table ".

Create view salesman _ VIEW

AS

SELECT Clerk ID, clerk name, home address, phone number

FROM salesman Information

In the statement for creating a view, the with check option clause uses an updatable view that contains the WHERE clause in the SELECT statement. For example, you can change "salesman _ VIEW" to the clerk information surnamed Wang.

Create view salesman _ VIEW

AS

SELECT Clerk ID, clerk name, home address, phone number

FROM salesman Information

WHERE salesman name LIKE 'wang %'

In this way, the "salesman _ VIEW" VIEW only contains information about the clerk surnamed Wang. If you add a clerk to the view, but the clerk is not surnamed Wang, updating the view will be different. The preceding settings are allowed because the view is updatable. However, if you call this view, you will not be able to see the newly updated row or update it further.

However, if you add the with check option clause at the end of the created view definition, the following statement is used:

Create view salesman _ VIEW

AS

SELECT Clerk ID, clerk name, home address, phone number

FROM salesman Information

WHERE salesman name LIKE 'wang % 'WITH CHECK OPTION

If you add a clerk whose name is not king to the view, an error message is displayed, indicating that the employee cannot be modified. Therefore, the with check option clause ensures that the updates executed by users do not prevent them from using the created updates effectively.

Practical drills:

1. Use the create view statement to CREATE a VIEW:

Create view warehouse receiving ticket _ VIEW (warehouse receiving ticket number, warehouse receiving quantity, warehouse receiving amount)

AS

SELECT warehouse receiving ticket number, warehouse receiving quantity, warehouse receiving commodity amount

FROM warehouse receiving ticket details

WHERE warehouse receiving item amount> 10000

Use the SELECT statement to view the View:

SELECT *

FROM warehouse receiving ticket _ VIEW

According to the explanation of the above Code, you should be able to understand the meaning of the Code.

2. Here is a computing-based Creation View:

In the Database "Sales Management System", create a view named "item info _ VIEW1". The column base table of this view is "item Info ", in addition, the product information after the "unit price" is off is returned in the view.

Create view Product Info _ VIEW1 (product name, supplier ID, price)

AS

SELECT Product Name, supplier ID, unit price * 0.8

FROM Product Information

2.2 "delete" Operation

If you do not need a VIEW, you can delete it. Use drop view to delete the VIEW from the current database. The basic syntax structure of the delete VIEW is as follows:

Drop view <view_name>

After the drop view statement is executed, the VIEW definition will be deleted, but it has no impact on the data stored in the base table, which is different from the deletion table. After deleting a view, you can either recreate the view or create different views with the same name.

For example, to delete the VIEW "item info _ VIEW" in the "sales management system" of the database, use the following statement:

Drop view item information_view

After deleting a view, although the table and data it is based on will not be affected, for other objects or queries dependent on the view, an error occurs during execution.

2.3 "change" Operation

If the base table changes or you want to query more information through the view, you must modify the view definition. To change the definition of a created VIEW, use the alter view statement. The basic syntax format is as follows:

Alter view <view_name> [(view_column_name)]

AS <query_expression>

[With check option]

The parameters are the same as those in the create view statement.

For example, in the Database "jewelry marketing system", a VIEW named "jewelry info _ VIEW" is created based on the "jewelry information" table, this view contains the following columns: "commodity number", "jewelry name", and "jewelry unit price". This view only accepts information with the "jewelry price" greater than 450. CREATE a VIEW using the create view statement:

Create view jewelry information_view

AS

SELECT jeweler ID, jewelry name, jewelry price

FROM jewelry Information

WHERE jewelry price> 450

After the preceding statement is executed to create a view, the following SELECT statement is used to view the view information:

SELECT *

FROM jewelry information_view

After the execution, we got information about our jewelry with a price greater than 450.

Then, modify the definition of VIEW "jewelry info _ VIEW. Limit the rows returned in the view to information with the "jewelry price" greater than 550. Modify the definition of the view with the following statement:

Alter view jewelry information_view

AS

SELECT jeweler ID, name, price

FROM jewelry Information

WHERE jewelry price> 550

After the preceding statement is successfully executed, use the SELECT statement to view the information in the modified view:

SELECT *

FROM jewelry information_view

After the execution, we got information about our jewelry with a price greater than 550.

3. modify data through views

Modifying the data in the view is actually modifying the data in the base table. This is determined by the nature of the view, because the view is a virtual table, it does not exist data, data only exists in the base table.

If the following content exists in the create view statement when creating a VIEW, the data in the VIEW cannot be modified:

The SELECT list contains DISTINCT;

The SELECT list contains expressions, such as calculation columns and functions;

Reference multiple tables in the FROM clause;

Reference a view that cannot be updated;

Group by or HAVING clause.

Modifications to the view are done through the INSERT, UPDATE, and DELECT clauses.

3.1 Use of INSERT statements

For example, in the Database "Sales Management System", create a VIEW named "supplier _ VIEW" based on the "supplier information" table, this view contains "supplier number", "supplier name", "contact name", and "contact number".

Create view supplier information-VIEW

AS

SELECT Supplier ID, supplier name, contact name, contact number

FROM supplier information

After successfully running the preceding statement, open the VIEW supplier _ VIEW to VIEW the information. If you want to add a row of data to the view, use the INSERT statement:

Insert into supplier information_view

VALUES (1010, 'Yellow River Science and Technology ', 'wu kui', '123 ')

Use the following SELECT statement to query the view after the inserted data row:

SELECT *

FROM supplier information_view

The query result is that we add a row of data at the end of the view, open the corresponding base table, and add the above data in the last row of the base table.

3.2 use of UPDATE statements

Updating data in a view is the same as updating data in a base table. However, when a view is based on data in multiple base tables, each update operation can only update the values of Data columns in a base table.

For example, in the previously created VIEW "operator info _ VIEW", change the "contact number" of the operator named "Zhang Rongrong" to 13933456770:

UPDATE operator information_view

SET contact number = '000000'

WHERE Operator name = 'zhang Rongrong'

Open the VIEW "operator info _ VIEW" and VIEW the data. The data in the column "contact number" in the last row is changed to 13933456770.

3.3 Use of DELETE statements

The method for deleting data from a view is the same as that for deleting data from a base table.When a view is based on two or more base tables, data in the view cannot be deleted.

For example, delete the row of tianjian data in the "supplier info _ VIEW" VIEW.

Delete from supplier information_view

WHERE supplier no. = 1010

After the preceding statement is executed, open the VIEW "supplier info _ VIEW", and delete a row of data with the "supplier number" 1010 added at the beginning. Similarly, A row of data in the base table is also deleted.

4. Learning Summary

View is a very important tool for users and operators. Simply put, it is a virtual table, and this table is the result you want, the results queried from the base table are displayed in this virtual table. It is simple and practical to change the expected results at any time when the application is satisfied.

Through the study in this chapter, I feel that the knowledge of the database is very practical. I will try my best to understand the knowledge. The following blog is still a summary of database-related knowledge. Let's look forward to it!