Previously reviewed:
SQL knowledge points -- insert records
SQL knowledge points -- update records and delete records
Basic skills of SQL (I) -- SQL and database
Basic skills of SQL (II) -- basic query
Basic skills of SQL (III) -- aggregation and sorting
catalogue
view
From the perspective of SQL, views and tables are the same. The difference between the two is that the actual data is saved in the table, while the SELECT statement is saved in the view (the view itself does not store data). Using views, you can easily complete complex operations such as querying data across multiple tables, The view contains two restrictions: "ORDER BY cannot be used" and "it can be updated with restrictions".
Advantages of view
- The first point is that since the view does not need to save data, it can save the capacity of the storage device.
- The second advantage is that frequently used SELECT statements can be saved as views, so there is no need to rewrite them every time. Frequently used SELECT statements should be made into views.
The CREATE VIEW statement is required to create a view
CREATE VIEW View name (< View column name 1>, < View column name 2>, ...... ) AS <SELECT sentence > CREATE VIEW ProductSum (product_type, cnt_product)--Alias of the view AS --there AS Alias used when defining AS Not the same. It belongs to fixed grammar and cannot be omitted SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;--The body of the view ---Use view SELECT product_type, cnt_product FROM ProductSum;
Although the view supports creating views on top of views, multiple views will reduce the performance of SQL. Try to avoid creating views on the basis of views. Precautions for using Views:
- The ORDER BY clause cannot be used
- Conditions under which the view can be updated:
- DISTINCT is not used in the SELECT clause
- There is only one table in the FROM clause
- The GROUP BY clause is not used
- HAVING clause not used
Delete view
To delete a view, you need to use the DROP VIEW statement and use the CASCADE option to delete the associated view
DROP VIEW View name (< View column name 1>, < View column name 2>, ...... ) --DROP VIEW ProductSum CASCADE;
Subquery
A subquery is a one-time view (SELECT statement). Unlike views, subqueries disappear after the SELECT statement is executed. The sub query is to directly use the SELECT statement used to define the view in the FROM clause. As an inner query, the sub query will be executed first.
-- stay FROM Clause to define the view SELECT sentence SELECT product_type, cnt_product FROM ( SELECT product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type ) AS ProductSum;--Subquery
scalar subquery
Scalar subquery has a special limitation, that is, it must and can only return the results of one row and one column, that is, it returns the value of a column of a row in the table. Scalar subquery is a subquery that returns a single value. The writing position of scalar subquery is not limited to WHERE clause. Generally, any position that can use a single value can be used. In other words, constants or column names can be used in almost all places, whether in the SELECT clause, GROUP BY clause, HAVING clause, or ORDER BY clause. Scalar subqueries must not return multi row results.
Associated subquery
Like the GROUP BY clause, the associated subquery can also segment the data in the table.
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);
What plays a key role here is the condition of the WHERE clause added in the subquery. This condition means to compare the sales unit price and average unit price of each commodity in the same commodity category. When comparing within a subdivided group, you need to use an associated subquery.
Knowledge test
1. Create a view that meets the following three conditions (the view name is ViewPractice5_1). Using the Product table as the reference table, it is assumed that the table contains 8 rows of data in the initial state.
Condition 1: the sales unit price is greater than or equal to 1000 yen.
Condition 2: the registration date is September 20, 2009.
Condition 3: it includes three columns: commodity name, sales unit price and registration date.
The result of executing the SELECT statement on the view is as follows.
SELECT * FROM ViewPractice5_1;
results of enforcement
CREATE VIEW ViewPractice5_1 AS SELECT product_name, product_price, regist_date FROM Product WHERE sale_price >= 1000 AND regist_date > "2009-09-20"
2. Add viewpractice5 to the view created in 1_ What results will be obtained by inserting the following data into 1?
INSERT INTO ViewPractice5_1 VALUES (' knife ', 300, '2009-11-02');Error: field of view 'shop viewpractice5_ 1' underlying table doesn't have a default value
When inserting data into the view, it will also insert data into the original table. However, multiple fields in the original table (employees) are not allowed to be empty, so they cannot be inserted. Modify these fields that are not allowed to be empty to allow them to be empty.
3. Write a SELECT statement according to the following results, where sale_price_all is the average selling unit price of all goods
SELECT product_id, product_name, product_type, sale_price, ( SELECT sum( sale_price ) FROM product ) AS sale_price_all FROM product
4. Write an SQL statement according to the condition} in 1 and create a view (named AvgPriceByType) containing the following data.
Tip: the key is AVG_ sale_ The price column. Unlike 3, what needs to be calculated here is the average sales unit price of each commodity category. This column can be created using an associated sub query. The question is where to use this associated subquery.
CREATE VIEW AvgPriceByType AS
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)
FROM Product P2
WHERE P1.product_type = P2.product_type
GROUP BY P1.product_type) AS avg_sale_price
FROM Product P1;