Datawhale September team learning task03 complex query

preparation

1, View

Image source: https://www.bilibili.com/video/BV1va4y1x7tW?from=search&seid=15245999763303735560&spm_id_from=333.337.0.0

1. What is a view

Views are visual tables
Is a virtual table
Views are created based on SELECT statements
- 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

2. View VS table

The view does not save the actual data, but the actual data is saved in the table

A SELECT statement is saved in the view
The view is not a table, the view is a virtual table, and the view depends on the table

Users can directly operate the basic table, which is stored on disk in the data file

However, as a user, you don't need to pay much attention to the storage of the bottom layer. In addition to the operation table, you can also operate the view

The view fetches data from the basic table, but does not really store data

3.Why need view

It can save frequently used SELECT statements to improve efficiency
It can make the user see the data more clearly
All fields of the data table may not be disclosed to the public to increase data confidentiality
Only the definition of the view is stored without data redundancy

4.How to create view

Basic grammar
```
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 first column in the SELECT statement is the first column in the view, the second column in the SELECT statement is the second column in the view, and so on. Moreover, the column name of the view is defined in the list after the view name
- Note that the view name must be unique in the database and cannot be the same as other views and tables
```
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
```
Views can not only be based on real tables, but we can also continue to create views based on views
- However, this operation should be avoided as much as possible. This is because for most DBMS, multiple views degrade SQL performance

be careful

The ORDER BY statement cannot be used when defining views in a general DBMS

The following is wrong:
Because the view is the same as the table, the data rows have no 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

Single table based view

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

At this time, enter SHOW TABLES; Command, you will find more productsum

However, navicat and DataGrid still separate views from tables

View or available SELECT statements
Or use visualization tools directly

Multi table based view

First create a table shop_product

CREATE TABLE shop_product
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'Tokyo',		'0001',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'Tokyo',		'0002',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'Tokyo',		'0003',	15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'Nagoya',	'0002',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'Nagoya',	'0003',	120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'Nagoya',	'0004',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'Nagoya',	'0006',	10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'Nagoya',	'0007',	40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'Osaka',		'0003',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'Osaka',		'0004',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'Osaka',		'0006',	90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'Osaka',		'0007',	70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'Fukuoka',		'0001',	100);

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

5.How to alter view structure

Basic grammar
```
ALTER VIEW <View name> AS <SELECT sentence>
```
- The view name must be unique in the database
  - Cannot have the same name as other views and tables

Modify the previous productSum view

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

Modified view:

6.How to update view content

Because the view is a virtual table, the operation on the view is the operation on the underlying basic table. Therefore, the modification can be successful only when the definition of the underlying basic table is met

update the view

The above productSum view does not include the above restrictions

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

View original table

The original watch has become 5000!

However, the modified statement at that time was WHERE product_type = 'office supplies'; Why didn't the punch change to 5000?

7.How to delete view

Basic grammar
```
DROP VIEW <View name 1> [ , <View name 2> ...]
```
- Appropriate permissions are required to successfully delete
Delete productsum
```
DROP VIEW productsum;
```
- If you continue to operate after deleting, an error will be reported in this view

2, Subquery

introduce

1.What is subquery

A query that nested the SELECT statement used to define the view inside another query statement
- In the SELECT clause, the sub query is calculated first. The sub query result is used as the filter condition of another query. The query can be based on one table or multiple tables

2. Relationship between sub query and view

Then the introduction above

A subquery is to use the SELECT statement used to define the view directly in the FROM clause. Among them, AS studentSum can be regarded as the name of the sub query. Because the sub query is one-time, the sub query will not be saved in the storage medium like the view, but will disappear after the execution of the SELECT statement

3. Nested sub query (try to avoid)

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;

The innermost sub query is named productSum. This statement is based on product_type group and query the number. In the second level query, the number of products with 4 will be found, and the outermost layer will query product_type and cnt_product has two columns. Although nested subqueries can produce results, with the superposition of nested layers of subqueries, SQL statements will not only be difficult to understand, but also have poor execution efficiency. Therefore, such use should be avoided as far as possible.

4. Scalar subquery

Scalar: single
- Scalar subquery: single subquery
- The so-called singleton requires that our SQL statement can only return one value
  - That is, a column of a specific row in the table is returned

What's the use of scalar subqueries

Let's take a direct look at an example: find the goods whose sales unit price is higher than the average unit price through the scalar sub query statement
```
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);
```

It can also be used like this

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

5. Associated sub query

There is a connection between the query and the subquery
- How to establish contact
Relationship between query and subquery

3, Various functions

1. Arithmetic function

First construct a samplemath table

-- DDL : Create table
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(10,3),
 n INT,
 p INT);

-- DML : insert data
START TRANSACTION; -- Start transaction
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- Commit transaction
-- Query table content

add , subtract , multiply and divide
ABS absolute value
MOD remainder
- MOD is a function for calculating the division remainder (remainder), which is the abbreviation of modulo. Decimal has no concept of remainder. It can only find the remainder of integer column.
  
  Note: the mainstream DBMS supports MOD function. Only SQL Server does not support this function. It uses the% symbol to calculate the remainder
ROUND round
- Syntax: round (object value, number of decimal places reserved)

SELECT m,
ABS(m) AS abscol,
n ,p,
MOD(n,p) AS mod_col,
MOD(n,p) AS mod_col,
ROUND(m,1) AS round_col
FROM samplemath;

2. String function

First construct samplestr for learning

-- DDL : Create table
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML: insert data
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('sun',	'moon', 'Mars');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc ha-ha',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- Confirm the contents in the table

CONCAT splice
- CONCAT(str1,str2,str3)
LENGTH string LENGTH
- Length (string)
LOWER lowercase conversion
- The LOWER function can only be used for English letters. It converts all strings in the parameter to lowercase. This function is not applicable to situations other than English letters and does not affect characters that are originally lowercase.
ERPLACE string replacement
- Replace (object string, string before replacement, string after replacement)
Interception of SUBSTRING string
- SUBSTRING (the starting position of the object string intercepted FROM and the number of characters intercepted FOR)

Want to try it in DataGrid

Select Query Console first

Then select the appropriate database

try! Click the green button to execute the script

SELECT
    str1,
    str2,
    str3,
    CONCAT(str1,str2,str3) AS str_concat,
    LENGTH(str1) AS len_str,
    LOWER(str1) AS low_str,
    REPLACE(str1,str2,str3) AS rep_str,
    SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM
    samplestr;

Jump out of result

3. Date function

Here are some functions recognized by standard SQL that can be applied to most DBMS
- CURRENT_DATE current date
- CURRENT_TIME current time
- CURRENT_TIEMSTAMP current date and time
- EXTRACT intercept date element

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

4. Conversion function

CAST type conversion

CAST(Value before conversion AS Data type to convert)

-- Converts a string type to a numeric type
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

COALESCE converts NULL to another value
- ```
COALESCE(Data 1, data 2, data 3)
```
  - COALESCE is A SQL specific function. This function will return the first non NULL value from the left in variable parameter A. The number of parameters is variable, so it can be increased infinitely as needed.
    
    The conversion function is used when converting NULL to other values in SQL statements.
- ```
SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
```

4, Predicate

1.What is predicate

Function whose return value is true
- Including TRUE/FALSE/UNKONWN
Main predicate
- LIKE
- BETWEEN
- ISN NULL , IS NOT NULL
- IN
- EXISTS

2.LIKE predicate – used for partial consistent query of strings

Part of the agreement can be divided into
- Front consistent
- Middle consistency
- Rear consistent
View the previously imported table samplelike
Consistent front: select "dddabc"
- Consistent in front, that is, the string as the query condition (here "ddd") is the same as the beginning of the query object string
- ```
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
```
- Where% is a special symbol for "zero or more arbitrary strings", and in this case for "all strings starting with ddd"
Consistent in the middle: select "abcddd", "dddabc" and "abddddc"
- Consistent in the middle, that is, the query object string contains a string as the query condition. It doesn't matter whether the string appears at the end or in the middle of the object string
- ```
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
```
Consistent in the rear: select "abcddd"“
- The last part is consistent, that is, the string as the query condition (here "ddd") is the same as the end of the query object string
```
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
```

_Underscore matches any 1 character

SELECT *
FROM samplelike
WHERE strcol LIKE 'ab___';

summary

Based on the above three types of queries, we can see that the query conditions are the most relaxed, that is, the middle consistency can get the most records, because it contains both front consistent and rear consistent query results.

3.BETWEEN predicate – used for range query

For example, select commodities with a sales unit price of 100 ~ 1000 yuan

SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

Note that the above is a closed interval

To change to open interval:

SELECT product_name, sale_price
FROM product
WHERE sale_price >100
AND sale_price <1000;

4.IS NULL, IS NOT NULL – used to determine whether it is NULL

SELECT product_name, sale_price
FROM product
WHERE purchase_price IS NULL;

SELECT product_name, sale_price
FROM product
WHERE purchase_price IS NOT NULL;

5.IN predicate – simple use of OR

introduce:

SELECT product_name, sale_price
FROM product
WHERE sale_price IN(100,1000);

It should be noted that NULL data cannot be selected when IN and NOT IN are used. The same is true for the actual results. Neither of the above two groups of results includes forks and ballpoint pens with NULL purchase unit price. NULL can only be judged by IS NULL and IS NOT NULL.

Tags: Database SQL

Posted by mwgi2005 on Mon, 20 Sep 2021 15:48:37 +0530

Programs Team

Datawhale September team learning task03 complex query

Datawhale September team learning task03 complex query

1, View

1. What is a view

2. View VS table

3.Why need view

4.How to create view

Single table based view

Multi table based view

5.How to alter view structure

6.How to update view content

7.How to delete view

2, Subquery

1.What is subquery

2. Relationship between sub query and view

3. Nested sub query (try to avoid)

4. Scalar subquery

What's the use of scalar subqueries

5. Associated sub query

3, Various functions

1. Arithmetic function

2. String function

3. Date function

4. Conversion function

4, Predicate

1.What is predicate

2.LIKE predicate – used for partial consistent query of strings

summary

3.BETWEEN predicate – used for range query

4.IS NULL, IS NOT NULL – used to determine whether it is NULL

5.IN predicate – simple use of OR

Hot Tags