Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge of "how to use PostgreSQL database views and subqueries". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

View

What is stored in the table is the actual data, and in the view is the SELECT statement (the view itself does not store data).

Read the data from the view, which executes the SELECT statement internally, creating a temporary table.

The benefits of using views: first, views do not save data, saving storage device capacity. Second, save the frequently used SELECT statements as views, and each time you use these statements, you don't have to repeat the writing, just call the view. Third, when the data is saved to the table, the data can be updated only by explicitly executing the SQL update statement, and the data in the view will be updated automatically with the change of the original table.

Create a view

Format:

CREATE VIEW view name (,...) AS

Example:

CREATE VIEW ProductSum (product_type, cnt_product) ASSELECT product_type, COUNT (*) FROM Product GROUP BY product_type; usage view

As you can see, if you use views, you don't have to write statements such as GROUP BY every time to fetch data from the Product table.

Also, if the data in the Product table is updated, the view is updated automatically.

This is because the view is the saved SELECT statement.

SELECT product_type, cnt_product FROM ProductSum

Multi-view: create a view based on the view. But this degrades SQL performance.

CREATE VIEW ProductSumA (product_type, cnt_product) ASSELECT product_type, cnt_product FROM ProductSum WHERE product_type = 'office supplies'

You cannot use the ORDER BY clause when defining a view. Because of the view and the table, the data rows are out of order.

(in PostgreSQL, you can use the ORDER BY clause when defining views, but some DBMS cannot)

View updates:

If the SELECT statement that defines the view satisfies some conditions, the view can be updated.

SELECT clause is useless DISTINCT, FROM clause has only one table, useless GROUP BY, useless HAVING.

The data obtained from the summary cannot be updated because the view and table are updated at the same time.

If you add data ('food', 3) to the above ProductSum, the original table needs to add three rows of data for food, but we don't know any of these data, so we can't update the data in the table.

You can update the following views that are not summarized.

CREATE VIEW ProductA (product_id, product_name, product_type, sale_price, purchase_price, regist_date) AS SELECT * FROM Product WHERE product_type = 'office supplies'

Insert data into the view.

INSERT INTO ProductA VALUES ('0009', 'pencil', 'office supplies', 95, 10, '2222-10-1')

At this point, you can see that the view and table are updated.

Delete the view:

Format

DROP VIEW view name (,...)

Examples

DROP VIEW ProductSum

And then report an error.

ERROR: cannot drop view productsum because other objects depend on it description: view productsuma depends on view productsum hint: Use DROP. CASCADE to drop the dependent objects too.

This is because you created a ProductSumA view based on ProductSum.

You can delete the ProductSum and the views associated with it as follows.

DROP VIEW ProductSum CASCADE; subquery

A subquery, which is equivalent to an one-time view.

Define the view ProductSum

CREATE VIEW ProductSum (product_type, cnt_product) ASSELECT product_type, COUNT (*) FROM Product GROUP BY product_type

Subquery: use the SELECT statement that defines the view directly into the FROM clause.

AS ProductSum,ProductSum is the name of the subquery. After executing the external SELECT statement, the subquery disappears.

The following code, in the order of execution, is first the SELECT statement in the FROM clause, and then the outer SELECT statement.

SELECT product_type, cnt_product FROM (SELECT product_type, COUNT (*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum

Let's take a look at ProductSum again and find that ProductSum no longer exists. From this, we can see that the subquery is one-time and is not saved to the hard disk like the view.

You can continue to use subqueries in the FROM clause of the subquery.

The following is the data selected for cnt_product = 4 in ProductSum.

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

Scalar quantum query scalar subquery, which returns a subquery of the value (single value) of a column in a row of a table.

Scalar quantum queries can be used in the WHERE clause.

Since aggregate functions cannot be used in the WHERE clause, statements like the following are incorrect.

SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > AVG (sale_price)

This can be done as follows.

SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG (sale_price) FROM Product); SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG (sale_price) FROM Product)

Scalar quantum queries can be used anywhere a single value is used.

Use scalar quantum queries in the SELECT clause:

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

Use scalar quantum queries in the HAVING clause:

The average unit price of different commodities is compared with the unit price of all goods.

SELECT product_type, AVG (sale_price) FROM Product GROUP BY product_typeHAVING AVG (sale_price) > (SELECT AVG (sale_price) FROM Product)

A scalar quantum query cannot return multiple rows of results, but if it returns multiple rows of results, it is an ordinary subquery that cannot be used where a single input value is required.

Associated subquery

Now it is necessary to select the goods in each category that are higher than the average selling price of that category.

Calculate the average price according to the type of commodity:

SELECT AVG (sale_price) FROM Product GROUP BY product_type

Because there are three items, the above query returns three results.

Then you can't use the following method. Because subqueries are not scalar queries, they cannot be used in the WHERE clause.

SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG (sale_price) FROM Product GROUP BY product_type)

Associated subqueries are used when comparing within subdivided groups.

A WHERE clause is added to the subquery. The aim is to compare the sales unit price and the average unit price of each commodity in the same category.

Because the comparison object is the same Product table, two aliases P1 and P2 are used.

Use the associated subquery, with. Form, limit product_type, compare to the average unit price.

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)

Moreover, without GROUP BY, you can get the same result:

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); "how to use PostgreSQL database views and subqueries" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.