Simply learn SQL - select query

1. View

What is the view? In my opinion, the view is equivalent to creating a shortcut to the file, but it limits the open content. A view is one or more shortcuts created to a table.
View, understood from the original meaning of the word, is to create a window to see the contents of the table. The contents can be local or global, depending on the actual use. As shown in the figure, changing data through (through) the window is to change the data of the table.


1.1 advantages of view

  • By defining views, you can save frequently used SELECT statements to improve efficiency
  • By defining views, users can see the data more clearly
  • By defining the view, you can not disclose all the fields of the data table, so as to enhance the confidentiality of the data
  • Data redundancy can be reduced by defining views

1.2 view related syntax

Create view

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

The improvement of view efficiency is reflected in the query of frequently queried content through windows, which greatly reduces the amount of code, as shown in the following examples:

create view ViewPractice5_1(product_name,sale_price,regist_date) 
as 
select product_name,sale_price,regist_date
from product
where regist_date = '2009-09-20' and sale_price>=1000;

select * from ViewPractice5_1;-- For frequently queried content, the amount of code is greatly reduced by querying through windows
-- Equivalent to
select product_name,sale_price,regist_date
from product
where regist_date = '2009-09-20' and sale_price>=1000;


Modify view structure

ALTER VIEW <View name> AS <SELECT sentence>

Update data in view

UPDATE Window name
  SET <Listing> = <expression> [, <Column name 2>=<Expression 2>...];  
  WHERE <condition>;  -- Optional, very important.

Delete window

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

2. Sub query and association query

A subquery is a query in which one query statement is nested within another query statement.
For example, query the average sales unit price of all commodities

select 
product_id,product_name, product_type ,sale_price,
(select avg(sale_price) from product) as sale_price_all
from product;

Clauses can appear in different places. For example, where is a clause query

select product_id, product_name, sale_price
  from product
 where sale_price > (select avg(sale_price) from product);

2.1 Association query

The key of association query is how to establish the relationship between query and sub query -- where condition is the key
For example, query the commodity type and commodity name higher than the average 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);

Key sentence: WHERE p1.product_type = p2.product_type, which associates the query with the subquery

Another thing to keep in mind is the execution order of queries:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

For example, the query product information contains the average sales price of the same product type, as follows:

Query statement:

select product_id,product_name,product_type,sale_price,
 (select avg(sale_price) 
  from product as p2 
  where p1.product_type=p2.product_type) as avg_sale_price  -- Subquery
from product as p1 ;

From a writing point of view, it will be mistaken that the sub query select is executed first, and p2 appears first than p1. The actual execution is from p1 select... And then from p2 select

2.2 how to write association query

Idea: split first and then merge. First write query statements separately, and then find common attributes, so as to merge different queries as a bridge connection.

The first step is to check separately
Check commodity information

select 
product_id,
product_name,
product_type,
sale_price
from product;


Check the average price of the same kind of goods

select product_type,avg(sale_price)  from product group by product_type;


Merge, and use the where conditional sentence to connect the main query p1 and the sub query p2

select product_id,product_name,product_type,sale_price,
 (select avg(sale_price) 
  from product as p2 
  where p1.product_type=p2.product_type group by product_type) as avg_sale_price  -- Subquery
from product as p1 ;

Because the main table data order is from product in 0001_ Type = 'clothes' starts to execute. First judge p1.product_type('clothes') = p2.product_type('clothes'), if successful, AVG_ sale_ The value corresponding to price is attached. Because of this, the two tables product_ By comparing types, you can determine product_type to delete the group by product in the subquery_ type.

The complete statement is

select product_id,product_name,product_type,sale_price,
 (select avg(sale_price) 
  from product as p2 
  where p1.product_type=p2.product_type) as avg_sale_price  -- Subquery
from product as p1 ;

4. Predicate

  1. like
  2. between
  3. is null,is not null
  4. in
  5. exists

4.1 like

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)

4.2 between

The range between is a closed interval.

-- Select the sales unit price as 100~ 1000 Yuan commodity
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;-- replace WHERE sale_price >= 100 AND sale_price <= 100


+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T Shirt          |       1000 |
| Punch       |        500 |
| Fork         |        500 |
| Dish cleaning board       |        880 |
| ball pen       |        100 |
+--------------+------------+
5 rows in set (0.00 sec)

4.3 in

The advantage of the predicate in is to simplify the use of the logical word or in the where condition

-- adopt OR Specify multiple purchase unit prices for query
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);-- replace WHERE purchase_price = 320
											 -- OR purchase_price = 500
					 						 -- OR purchase_price = 5000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T Shirt          |            500 |
| Punch       |            320 |
| pressure cooker       |           5000 |
+--------------+----------------+
3 rows in set (0.00 sec)

4.4 exists

The predicate exists only determines whether it exists in the where condition, and does not query the content.

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT 1 -- Write any number here* Fine
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| motion T Shirt      |       4000 |
| kitchen knife         |       3000 |
| Fork         |        500 |
| Dish cleaning board       |        880 |
+--------------+------------+

5. case expression

Writing grammar

case when <Evaluation expression> then <expression>
     when <Evaluation expression> then <expression>
     .
     .
     .
else <expression>
end  

One advantage of a case expression is that it turns the content into a column item expression

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| clothes         |      5000 |
| Office Supplies      |       600 |
| kitchenware      |     11180 |
+--------------+-----------+
3 rows in set (0.00 sec)

Convert to column expression

-- Perform line to line conversion on the total sales unit price calculated by commodity type
SELECT SUM(CASE WHEN product_type = 'clothes' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = 'kitchenware' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = 'Office Supplies' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
|              5000 |             11180 |              600 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)

case expressions can also be used with aggregate functions.
For example, divide the price into three gears, the first gear is 0-1000, the second gear is 1001-3000, and the third gear is more than 3001.

Let's start with a wrong approach. First find the sub query. When it is combined into a new query, the result is duplicate records

select (select count(product_name) from product where sale_price <= 1000) as low_price,
(select count(product_name) from product where sale_price between 1001 and 3000) as mid_price,
(select count(product_name) from product where sale_price > 3000) as h_price
from product;


The correct approach is as follows

select count(case when  sale_price <= 1000   then product_name else null end) as low_price,
count(case  when  sale_price between 1001 and 3000  then product_name else null end) as mid_price,
count(case when  sale_price  > 3000  then product_name else null  end) as h_price 
from product;

Information:
1.Datawhale-team-learning-sql

Tags: Database Oracle SQL

Posted on Mon, 20 Sep 2021 18:47:06 -0400 by netizen