1. View

A view is a virtual table, which is different from a direct operation data table. A view is created based on a SELECT statement (which will be described in detail below). Therefore, when operating a view, a virtual table will be generated according to the SELECT statement that created the view, and then SQL operations will be performed on this virtual table.

  1.1 difference between view and table:

• Is the actual data saved

View is a virtual table based on the real table, and its data sources are based on the real table.

  one point two   Why the view exists:

1. Save frequently used SELECT statements to improve efficiency.
2. Make the data seen by users clearer.
3. Do not disclose all fields of the data table to enhance the confidentiality of the data.
4. Reduce data redundancy.

1.3 view creation:

CREATE VIEW <View name>(<Column name 1>,<Column name 2>,...) AS <SELECT sentence>

• The SELECT statement needs to be written after the AS keyword
• The columns in the SELECT statement are arranged in the same order as the columns in the view
• The view name must be unique in the database and cannot be the same as other views and tables
• You can continue to create views based on views
• The ORDER BY statement cannot be used when defining a view in a general DBMS (because, like a view and a table, the data rows are out of order)

In MySQL, the definition of a view allows the use of ORDER BY statements. However, if you select from a specific view and the view uses its own ORDER BY statement, the ORDER BY in the view definition will be ignored.

Example of creating a view:

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;

On Navicat:

Query - > new query - > run:

  View created:

New shop_product: :

  In the product table and shop_ Create a view based on the product table.

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
FROM product,
shop_product
WHERE product.product_id = shop_product.product_id;

Connected through "product.product_id = shop_product.product_id".

View created Views:

  1.4 modify view

ALTER VIEW <View name> AS <SELECT sentence>

Example: modify the upper productSum view:

ALTER VIEW productSum --SELECT Change to ALTER
AS
SELECT product_type, sale_price
FROM Product
WHERE regist_date > '2009-09-11';

Note that the register of type date is compared_ Method of the date data segment.

The operation on Navicat is still "query - > new query":

The results are as follows:

  1.5 update view content

The operation on the view is the operation on the underlying basic table, so the definition of the underlying basic table needs to be met when modifying.

The following structures are included and cannot be modified:

• aggregate functions SUM(), MIN(), MAX(), COUNT(), etc
• DISTINCT keyword
• GROUP BY clause
• HAVING clause
• UNION or UNION ALL operator
• multiple tables in the FROM clause

The view is derived from the table. If the original table can be updated, the data in the view can also be updated, and vice versa.

Example:

UPDATE productsum
SET sale_price = '5000'
WHERE product_type = 'Office Supplies';

Before modification:

After modification:

  Only the data selected into the view is modified in the original table. The view is only a window of the original table, so it can only modify the contents that can be seen through the window.

  1.6 delete view

Deleting a view requires appropriate permissions.

DROP VIEW <View name 1> [ , <View name 2> ...]

Example: delete the productSum view:

DROP VIEW productSum;

  The productSum view has been deleted,

  Operate it again, and the display does not exist:

  2 sub query

Subquery refers to a query in which one query statement is nested inside another query statement. The subquery is calculated first in the SELECT clause, and the result is used as the filter condition of the outer query. The query can be based on one table or multiple tables.
Relationship between subquery and view:

• A subquery is to use the SELECT statement used to define the view directly in the FROM clause.
• AS... Can be seen AS the name of a subquery.
• Because the subquery is one-time, the subquery will not be saved in the storage medium like the view, but will disappear after the SELECT statement is executed.

2.1 nested sub query

SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type,
COUNT(*) AS cnt_product
FROM product
GROUP BY product_type) AS productsum
WHERE cnt_product = 4) AS productsum2;

Although nested subqueries can produce results, with the superposition of nested layers of subqueries, SQL statements will not only be difficult to handle
And the execution efficiency will be very poor, so try to avoid such use.

two point two   scalar subquery  

That is, a single sub query requires that the executed SQL statement return only one value, that is, the data at a specific location in the table.

Example:

Table product:

  Execute the following scalar subquery:

SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;

The results are as follows:

  2.3 associated sub query

The internal and external queries are connected through some flags to filter data.

  Execute the following statement:

SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);

The results are as follows:

  Select the commodities in each commodity category that are higher than the average sales unit price of the commodity category:

SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type =p2.product_type
GROUP BY product_type);

Execution order of associated sub query:

1. First execute the main query without WHERE
2. Match product according to the main query result_ Type to get sub query results
3. Combine the sub query results with the main query to execute a complete SQL statement