SQL Basic Learning _04_ View

View

1. Creation of views

? ? A view is a saved SELECT statement that, when executed, results in a new table, so the view and table are not treated differently in SQL, which means that SQL can do some work on the views.

? ? Because the view does not actually save the data, just save the execution of the SELECT statement, you can save storage space, but for the large data volume of storage, using the view to frequent operation, it is bound to occupy a large computational time, but also a time-to-space scheme.

? ? Create a view from the following statement:

? ? CREATE View name (< View column 1>,< View column 2>,< view column 3>,...)
? ? As
? ? <select Statement >

? ? Such as:

? ? CREATE VIEW shohinsum (Shohin_bunrui, Cnt_shohin)
? ? As
? ? SELECT Shohin_bunrui, COUNT (*)
? ? From Shohin
? ? GROUP by Shohin_bunrui;

? ? At this point, if you do:

? ? Show tables;

? ? Can see a shohinsum table, the table can be a general query, but can not insert and modify the data, the updatable view needs to meet certain conditions;

2. More attention to graphs

? ? Because the view is also a table in SQL, you can also create a view on the basis of the view, it is conceivable that this will make SQL efficiency becomes very slow

3. Limitations of view Definitions

? ? 1. Cannot be defined with the ORDER BY clause (except PostgreSQL, but should be avoided as much as possible)

? ? 2. Conditions for updating the view:

? ? ? ? 1. Distinct is not used in the SELECT clause

? ? ? ? 2. Only one table in the FROM clause

? ? ? ? 3. The GROUP BY clause is not used

? ? ? ? 4. Do you not use the HAVING clause?

? ? ? ? Can not update a view, and ultimately to see whether the response operation can also be in the original table to respond to the changes, if the original table can be updated at the same time, the view can be updated;

? ? ? ? In PostgreSQL, the view is read-only by default, and if you want to do the update in an updatable view, you need to do the following:

? ? ? ? CREATE? OR REPLACE RULE Insert_rule
? ? ? ? As on INSERT
? ? ? ? To < view name > do INSTEAD

4. Delete a view

? ? To delete a view from a drop view

Sub-query

1. Definition

? ? A subquery is a one-time view, because the view can be used as a table attribute, in the FROM clause, you can use parentheses to treat the view as a table:

? ? SELECT < column 1>,< column 2>,...
? ? From (SELECT statement) as?< subquery name >

? ? When the subquery executes, executes the SELECT statement that is considered the view, and then executes the outer query statement;

? ? Multiple subqueries can be nested, but the corresponding performance will also become worse;

2. Scalar Quantum Query

? ? A scalar subquery is a subquery that returns a single value, and because it returns a single value, it can be used in various operations such as comparison operations.

? ? One application of scalar quantum queries is to use the return value of an aggregate function in a where statement, such as:

? ? SELECT shohin_id, Shohin_mei, Hanbai_tanka
? ? From Shohin
? ? WHERE Hanbai_tanka > (SELECT AVG (Hanbai_tanka) from Shohin);?

? ? Because the SELECT statement in the view is executed first and a scalar is returned, there is no error in where, and the problem of where the WHERE clause cannot use the aggregate function is resolved;

? ? Scalar quantum queries can also be applied to queries in real-world scalars, such as:

? ? Select shohin_id, Shohin_mei, Hanbai_tanka, (select AVG (Hanbai_tanka) from Shohin) as Avg_tanka
? ? From Shohin;?

3. Associate Subqueries

? ? Scalar quantum queries are very handy, but if the value returned is not scalar, but there are multiple values, it is not easy to compare and other scalar operations,

? ? You can solve this problem by using the associated subquery.

? ? The associated subquery is actually setting a constraint so that the results returned by the query in the view are the only ones that apply to the outer query;

? ? If a subquery is considered a double loop, the associated subquery is actually adding a condition in the inner loop so that only one value is returned, and the value is required for the outer loop,

? ? More difficult to interpret in words, see examples:

? ? SELECT shohin_id, Shohin_mei, Hanbai_tanka
? ? From Shohin as S1
? ? WHERE Hanbai_tanka > (SELECT AVG (Hanbai_tanka)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? From Shohin as S2
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE S1.shohin_bunrui = S2.shohin_bunrui
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? GROUP by Shohin_bunrui);

? ? Because group by is used in the subquery above, the subquery returns multiple values, at which time an error occurs using the comparison operation;

? ? The key to correlating a subquery is to add a where S1.shohin_bunrui = S2.shohin_bunrui clause that causes the subquery to return only one value, and that value is the same group as the outer query, which is the value required by the outer query.

? ? The problem of comparison operation of multiple return values of subqueries is cleverly solved by correlation subquery;

? ? Through the above query statements, you can see the scope of the name of the relationship, S1 in both inside and outside the two layers can be used, but S2 only in the subquery can be seen; this is evident in the understanding of the double cycle.

SQL Basic Learning _04_ View