SQL Basics: Queries, Aggregation, JOINs, and Window Functions

This English version was machine-translated from the Chinese original. Technical terms may require verification.

SQL is widely used in a wide range of areas, including data analysis, development, testing, maintenance, product manager, etc. Course content is used, considering accessibility and accessibilityMySql The database is presented.

The questions in this article can also be addressedFind and Sort Algorithms: Retrieval, Dispersed Lists and Sort、Database system concept: data models, services and query processingHow the concept of a relatively close read together is developed in different contexts.

The environment in which the relevant databases are built is not recorded here, and we begin with an understanding of the databases by introducing basic queries and sequencing, and then by studying more complex calculations and advanced processing. Final study of some of the topics.

Initial knowledge database

Introduction to the database

The database is a computerized collection of data that is efficiently accessible by preserving large amounts of data. The data set is referred to as the database (Database, DB). The computer system used to manage the database is known as the database management system (Database Management System, DBMS).

DBMS is classified mainly through data preservation formats (type of database) and at this stage there are mainly the following five types.

• Level database (Hierarchical Database, HDB)
• Relationship database (Relational Database, RDB)
  • Oracle Database: Oracle RDBMS
  • SQL Server: Microsoft RDBMS
  • DB2: RDBMS
  • PostgreSQL: Open Source RDBMS
  • MySQL: Open Source RDBMS
• Object-oriented database (Object Oriented Database, OODB)
• XML Database (XML Database, XMLDB)
• Key-Value Store, KVS, for example: MongoDB

The most common of these is the relationship database RDB, which is characterized by a two-dimensional table of rows and columns to manage data, known as the relationship database management system (Relational Data Management System, RDBMS). That's what we're going to be working on.

For RDBMS, the most based system structure is that where the service side is separated from the client, we use SQL to request data from the service side

SQL Statement Basis

Stored in databaseTable structurelike rows and columns in excel, in the database, asRecordsIt's like a record. It's calledFields, which represents the data items stored in the table. The place where rows and columns intersect is called a cell, and only one record can be entered in a cell.

SQL is the language developed to operate the database. The International Organization for Standardization (ISO) has developed the corresponding standard for SQL, which is referred to as the standard SQL. We're just introducing standards, SQLs, smart learning for those SQLs that have been modified by the company.

Depending on the type of command given to RDBMS, SQL statements can be divided into three categories.

• DDL : DDL (Data Defense Language, Data Definition Language) is used to create or delete databases for storing data and tables in databases. DDL contains the following types of instructions.
  • CREATE: Create objects such as databases and tables
  • DROP: Delete objects such as databases and tables
  • ALTER: Modify the structure of objects such as databases and tables
• DML : DML (Data Manipulation Language, Data Manipulation Language) is used to query or change the records in the table. DML contains the following types of instructions.
  • SELECT: Data from the query table
  • INSERT: Insert new data into the table
  • UPDATE: Data in updated tables
  • DELETE: Delete data from the table
• DCL DCL (Data Control Language, Data Control Language) is used to confirm or cancel changes to data in databases. In addition to this, it is possible to set if the users of RDBMS have the permission to operate objects (database tables, etc.) in the database. DCL contains the following types of instructions.
  • COMMIT: Confirm changes to data in the database
  • ROLLBACK: Cancel changes to data in the database
  • GRANT: Give user access
  • REVOKE: Undo user permissions

90% of the SQL statements actually used are DML

Basic writing rules for SQL

Mandatory grammar rule

• SQL statements should end with semicolons (;)
• SQL does not distinguish between case of keywords, but the data inserted into the table is case-sensitive
• The win system default does not distinguish the size of a table name from a field name Write
• linux / Mac defaults strictly to distinguish the size of the table and field names Write
• The writing of constants is fixed, as if'abc', 1234, '26 Jan 2010', '10/01/26', '2010-01-26'......
• Words need to be separated by half-angled spaces or line breaks

The words of the SQL statement need to be separated by a half-angled space or a line break, and the full-angled space cannot be used as a separator for a word, otherwise there will be an error and an unexpected result.

Write Recommendations

** The general principle of the SQL syntax norm is that it is clear, readable and hierarchical. ** In the actual scene, thousands of SQL words are often used, and if not clearly written, life is questioned when review or someone else takes over.

The common concerns are as follows:

1. MySQL does not distinguish case by case per se, but strongly requires capitalisation of keywords, listings, etc.;
2. When creating tables, use uniform, descriptive field naming rules to ensure that field names are unique and not reserved, do not use continuous underlined endings; preferably start with letters
3. Right alignment of keywords, with space or indentation control at different levels to separate them, as in Example 2;
4. In the case of low listings, write in a row without harm; in many cases, and in relation to CASE WhEN or aggregation calculations, recommend line writing; and in the case of individuals, commas are used to pre-listed, to remove certain columns at ease and to list them;
5. (a) The use of aliases and aliases with as much meaning as possible, rather than a b c, otherwise the review will be painful;
6. A space is added to the operator;
7. When multiple forms are used, please write the aliases quoted before all listings, so as not to be cumbersome;
8. Each order ends with a semicolon;
9. A habit of hand-written notes.

单行注释 #注释文字 （MySQL专属）
单行注释 -- 注释文字
多行注释：/* 注释文字 */

Create grammar knowledge with it

Create CREATE Statement

The language code for creating the database is

CREATE DATABASE < 数据库名称 > ;

The language code for creating the form is

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

Of which < > Just to emphasize that this is a statement that you need to enter yourself.

Naming rules

• Use onlyHalf-angle English letters, numbers, underlining AsDatabases, tables and columnsName
• The name must start with a semi-English letter.
• It's a habit. We shouldn't end with a underlined.

Data Type

The table created by the database must specify the type of data, and each column cannot store data that does not match the type of data in the column.

Four minimum data types

• INTEGER type: Specifies the type of data (number type) used to store integer columns, which cannot store decimals.
• CHAR type: When the length of the string stored in a column is less than the maximum length, it is supplemented by a half-angle space, which is not generally used because storage space is wasted.To Set Length
• VARCHAR type: A long string is used to store a variable length string, which is filled with a half-angle space when the number of characters does not reach the maximum length, but with a variable-long string, even if the number of characters does not reach the maximum length.To set maximum length
• DATE type: Data type (date type) for columns that specify the date of storage (month of year).

In practical use, we use the VARCHAR type most often

Binding Settings

Constraint is the function of limiting or adding conditions to data stored in columns in addition to data types.

NOT NULLis a non-empty constraint, i.e. the column must enter data.

PRIMARY KEYis the primary key constraint, representing the only value of the column, from which you can extract data for a particular row. This sentence should be used as a binding form. PRIMARY KEY (product_id)

The main key constraint is an important part of the follow-up interview.

Delete and update

Changed database structure

Delete table DROP TABLE statement

DROP TABLE < 表名 > ;

It should be noted, in particular, that the deleted tables cannot be restored and can only be reinserted, with particular care being taken when executing the deletions.

Add column's ALTER TABLE statement

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

The definition of the column would need to include both the type of data listed and the column and the constraints

Delete column ALTER TABLE statement

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

Changes to database contents

Removes the specific row of the table

-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM < 表名 > WHERE COLUMN_NAME='XXX';

The WHERE statement is used to select a line.

Update of data

UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...]  
 WHERE <条件>  -- 可选，非常重要
 ORDER BY 子句  --可选
 LIMIT 子句; --可选

where condition select which line to operate, otherwise all lines will be modified by statement

SET in the UPDATE statement is the core statement that determines the update.

Multi-column update: The UPDATE statement's SET subword supports multiple columns as an update object. Here are some examples.

-- 基础写法，一条UPDATE语句只更新一列
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';
UPDATE product
   SET purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

It can get the right results, but the code is cumbersome. A consolidation approach could be used to simplify the code.

-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

It needs to be made clear that the columns in the SET clause can be not only two columns, but also three columns or more. One row to update one column

Insert

Database background for this section

To learn. INSERT The statement is used to create a table called producins, which reads as follows:

CREATE TABLE productins
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
sale_price     INTEGER      DEFAULT 0,
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id));

Here.sale_priceColumns are bound by default and will be replaced with default when NULL is inserted

Insert INSERT statement

INSERT statement Insert a line Basic syntax:

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  

When you complete the table with INSERT, you can omit the list after the name. The value of the VALUES clause is then given to each column by default in a left-to-right order.

An example to insert is

-- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');  

In principle, an INSERT statement is inserted in a row. When you insert multiple rows, you usually need to recycle the corresponding number of INSERT statements.

In INSERT statements you can write NULL directly in the list of values of the VALUES sub-rules if you want to give a NULL value to a column. Columns that want to insert NULL must not set the NOT NULL constraint.

Copy Insert From Other Tables

You can copy data from other tables using the INSERT... SELECT statement.

-- 将商品表中的数据复制到商品复制表中
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;  

Index

MySQL index is important for the efficient operation of MySQL, and the index can significantly increase MySQL's retrieval speed. In fact,The index is for a quick search behind us.

When creating a table, you can directly create an index with the following syntax:

CREATE TABLE mytable(

ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

You can also create:

-- 方法1
CREATE INDEX indexName ON table_name (column_name)

– 方法2
ALTER table tableName ADD INDEX indexName(columnName)

The index is more complex, and we'll introduce it later.

Basic queries and sorting

SELECT Statement Basis

Basic queries

When selecting data from a table, you need to use a SELECT statement, which means that only necessary data are selected from the table. The process of searching and extracting the necessary data through a SELECT statement is called matching query or query (query).

The underlying SELECT statements include both SELECT and FROM.

SELECT <列名>,
  FROM <表名>;

Of these, the SELECT sentence lists the names of the columns that wish to be consulted from the table, while the FROM sentence specifies the name of the table from which the data are selected. It's acceptable to choose more than one column.No comma between wordsIt's a split symbol.

Selective queries

WHERE statements are used when the full data need not be removed, but when the data are selected to meet certain conditions such as “commodity type is clothing” “sale unit price above Yen1,000”. In **WHERE sub- sentences you can specify conditions such as " the value of a column is equal to that string " or " the value of a column is greater than that number " . ** The execution of a SELECT statement containing these conditions makes it possible to search for records that only meet that condition.

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

Some additional knowledge

• The asterisk represents the meaning of all the columns.
• SQL is free to use line breaks without prejudice to execution
• Double quotes are required when setting a Chinese aliases"It's covered up.
• Use DISTINCT in SELECT statements to delete duplicate lines.

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
SELECT *
  FROM <表名>；
-- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。
SELECT product_id     As id,
       product_name   As name,
       purchase_price AS "进货单价"
  FROM product;
-- 使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_type
  FROM product;

arithmetical and comparative operators

Count Operators

The four main operators available in the SQL statement are as follows:

Meaning	Operators
Add	+
Subtract	-
Multiplication	*
Division	/

They only run calculations for INTEGER.

Compare operators

Operators	Meaning
=	Equal
<>	Unequal
>=	greater than or equal to
<=	less than or equal to
>	Greater than
<	less than

They only run calculations for INTEGER.

Common Law

• A constant or expression can be used in the SELECT sub-statement.
• String type data are in principle sorted according to dictionary order and cannot be confused with the numerical size order. If we can avoid this as much as we can.
• If you want to select a NULL record, use IS NULL operator in a condition expression. If you want to select a record that is not a NULL, use IS NOT NULL operator in a condition expression.

-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
  FROM product;
-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price - purchase_price >= 500;
/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL：创建表
CREATE TABLE chars
（chr CHAR（3）NOT NULL,
PRIMARY KEY（chr））;
-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
  FROM chars
 WHERE chr > '2';
-- 选取NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NULL;
-- 选取不为NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NOT NULL;

Logical Operators

Logical operators help us continue the conditions in the WHERE statement of complex inquiries, giving us greater freedom

NOT Operator

Wants to indicate 不是…… Except for the previous one.<>There is another negative operator in addition to the operator: NOT.

NOT cannot be used alone and must be used in combination with other query conditions such as

SELECT product_name, product_type, sale_price
  FROM product
 WHERE NOT sale_price >= 1000;

If it's not necessarily necessary to use the comparative operator directly, it's more intuitive.

AND and OR operators

An AND or OR operator can be used if you want to use multiple search conditions at the same time.

AND is equivalent to "and" similar to the intersection in mathematics; OR is equivalent to "or", similar to the combination in mathematics.

Note that, following the complexity of the operators, we need to express the priorities of the various calculations well in brackets and indents, so as not to lead to errors in results due to the priority of the logical operations, as follows:

-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '办公用品'
   AND ( regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

Apparently, we are. regist_date There's a clear choice, then he and ours. product_typeColumn conditions side by side

Aggregation queries

The function used in SQL for aggregation is called a polymer function. The following five aggregate functions are most commonly used:

• SUM: Computes total values in a value column in a value table
• AVG: Calculation of averages in a value column in the table
• MAX: Maximum values for data in any column in the calculation table, including text type and number type
• MIN: Calculate the minimum values for data in any column in the table, including text type and number type
• COUNT: Number of entries in the calculation table (lines)

Their grammar rules have changed somewhat more than the basic SELECTs.

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- 计算销售单价的最大值和最小值
SELECT MAX(sale_price), MIN(sale_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;
-- 计算全部数据的行数（包含 NULL 所在行）
SELECT COUNT(*)
  FROM product;
-- 计算 NULL 以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;

Sometimes, multiple lines may contain exactly the same data, and we may want to delete duplicate data.

SELECT COUNT(DISTINCT product_type)
  FROM product;

• The COUNT function operation results are associated with parameters, COUNT(asterisk) / COUNT(1) gets all rows containing NULL values, COUNT()<Listing>) Gets all rows that do not contain NULL values.
• The polymer function does not process rows containing NULL values, except for COUNT.
• The MAX/ MIN function applies to columns of text type and number type, while the SUM/ AVG function only applies to columns of digital type.
• Use DISTINCT keywords in the parameters of the polymer function, and you can remove the polymer result of duplicate values.

Cluster statistics

The previous use of the polymer function would have processed the entire table, and when you wanted to group the data together (i.e. aggregate the existing data by a column), GROUP BY could have helped us.

Syntax:

-- 在SELECT中使用的全部非聚合列，都应该出现在GROUP BY中，否则逻辑出错
SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

A very basic example.

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM product
WHERE product_type IN ('衣服', '鞋类')
GROUP BY product_type;
 -- 不含GROUP BY 此代码非法，因为混合使用了聚合列以及普通列且无分组
SELECT product_type, COUNT(*)
FROM product
WHERE product_type IN ('衣服', '鞋类')

At this point in time, we have chosen a statement based on GROUP BY in which the specified columns are aggregated

NULL aggregates as a special set of data

When using normal columns and polymer functions,Must use GROUP BY To specify how to group data Otherwise, the database does not know how to handle multiple rows of data for a polymer.

Specify conditions for group results

GROUP BY To help us achieve clusters, how can we remove special groups from them? That's what we're here to study.

You can use the HAVING substatement after GRUP BY. HAVING uses similar to WHERE.

It is noteworthy that:HAVING sub-words must be used in conjunction with GRUP BY sub-phrases and limited to group aggregation results, i.e. keys used here need to be included in SELECT, the WHERE clause is a limited data line (including grouping columns), each of which has its own function and in which the keys used are not required by SELECT.

HAVING'S IMPLEMENTING CLASS, JUST BEFORE ORDER BY

Here are some examples.

-- 只要行数大于2的分组
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

– 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

Sort Query Results

In some scenarios, a sorted result is required. And the SQL execution results are by default randomly sorted in order, using ORDER BY Second sentence. And the language code is...

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……

of which the parameter ASC is an ascending order, the parameter ASC is an descending order, and the default is an ascending order, at which point the parameter ASC can be defaulted.

Example:

-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;

– 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC, product_id DESC;

NULL cannot compare, so the sorting randomly places them at the beginning or at the end. In MySQL,NULL Value considered compared to any 非NULL Low value, so when the order is ASC,NULL value appears in first place, and when the order is DESC, the order is at last.

About the order of execution of statements

GROUP BY mentions that the aliases defined in the SELECT clause cannot be used in GROUP BY, but may be used in ORDER BY.

This is because SQL executes the SELECT statement in the following order when using the HAVING clause: FORM WHERE GRUP BY SELECT HAVING ORDER BY

Of which SELECT is executed in the order that follows the GRUP BY clause, before the ORDER BY clause. Therefore, aliases can be used in the ORDER BY sentence, but not in GRUP BY.

Complex queries

View

Basic knowledge of view

View is a virtual table, different from a direct operating data sheet, created on the basis of a SELECT statement (specifically described below), so the operating view generates a virtual table based on the SELECT statement that created the view, and then does SQL on this virtual table.

Difference between view and table - - Whether actual data are saved So view is not a data sheet that is actually stored in the database; it can be seen as a window through which we can see the real data in the database table.

Now that we have the data sheets, why do we need a view? The main reasons are the following:

1. By defining the viewSave frequently used SELECT statementsTo increase efficiency.
2. By defining the view, the data seen by the user can be made clearer.
3. By defining the view, the entire field of the data sheet can be kept closed and the confidentiality of the data enhanced.
4. By defining the view, you can reduce the redundancy of the data.

Create View

The basic syntax for creating view is as follows:

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

Of which SELECT statements need to be written after AS keywords. The order of the columns in the SELECT statement and the order of the columns in the view are the same. Column 1 in the SELECT statement is column 1 in the view, and column 2 in the SELECT statement is column 2 in the view, so push. And the view listing is defined in the list after the view name.

Note that the view name needs to be the only one in the database and cannot be renamed with other views and tables.

View can be based not only on a real table, but also on a view. But multiple views reduce the performance of SQL.

Unable to use during view creation SELECT Yes. ORDER BY Sub sentences because the lines in the view he created should not be sequential The definition of view in MySQL allows the use of ORDER BY statement but avoids it as much as possible

Create an example of a view based on a table below

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

Create an example of a view based on multiple tables

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;
--这使用的查询方法是隐式内连接，是模仿INNER JOIN的语句，效果一样

The views we create are well understood in code, and it's natural to take the data in multiple tables at the same time.

Modify View Structure

Syntax:

ALTER VIEW <视图名> AS <SELECT语句>

It's basically the same as when we delete and create.

Delete the basic syntax of the view as follows:

DROP VIEW <视图名1> [ , <视图名2> …]

Update View

Because the view is a virtual table, the action on the view is the action on the bottom base table, so the change can only be made successfully if the definition of the bottom base table is met.

Some changes can be made, but...However, this use is not recommended. And when we create the view, we try to use the limit not to allow changes to the table through the view.

Cannot be updated when view contains the following statement

• Aggregation functions SUM(), MIN(), MAX(), COUNT() etc.
• DISTINCT keyword.
• GRUP BY sentence.
• HAVING subword.
• Union or Union All Operator.
• FROM sub sentences contain multiple tables.

SubQuery

What's a sub-survey?

A simple example.

SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt
          FROM students_info
          GROUP BY stu_age) AS studentSum;

The statement appears to be well understood, with the use of bracketed sql statements to be executed first, and then outside sql to be executed after success. This is the statement of the sub-survey. (There is a logical problem with the code itself, which does not meet the need for cluster aggregation)

Sub-Query refers to a query where a query statement is embedded within another query statement, which calculates a sub-Query in a SELECT sub-statement, and the sub-Query results are a filter condition for another query in the outer layer, which can be based on a table or tables.

Sub-Query is the direct use of the SELECT statement used to define the view in the FROM sub-rule. Of these, AS studentsum can be considered a sub-searcher and the sub-searcher is one-off.

It is true that embedded multi-layer queries can yield results, but they reduce the readability of SQL statements and result in less efficient implementation and avoid them as much as possible

Standard Quantum Query

The scale is a single, then the quantum query is a single sub-search, and the single is the SQL statement that we are required to execute returns only one value, that is, returns the specifics of the table.A column in a row。

All places where a single value is required can be searched with a standard quantum, for example.

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

Association Sub-Query

Since the association sub-inquiries contain two words, they must mean that there is a link between the query and the sub-inquiries.

Requirements选取出各商品种类中高于该商品种类的平均销售单价的商品I don't know. The SQL statement reads as follows:

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);

The so-called connection is the use of a number of markers to connect both internal and external layers of queries for the purpose of filtering data. We mark the outside product form as p1, set the internal product as p2 and connect two queries through the WERERE statement.

The basic implementation line is

1. First do the main query without WERE
2. Match program type with main query results, get sub-search results
3. Execute the complete SQL statement in conjunction with the main query

View and sub-Query are more based elements in database operations, and some complex queries require a combination of sub-Query conditions to get the correct result. In any case, however, the SQL language should not be designed in such deep and particularly complex layers that not only readability, but also the efficiency of implementation, is difficult to ensure, so as to be as concise as possible to perform the required functions.

Various Functions

Sql carries a variety of functions, which greatly enhances the convenience of the sql language.

The functions are broadly divided into the following categories:

• Algorithmic functions (functions used for numerical calculations)
• String Functions (functions used for string operations)
• Date Functions (functions used for date operations)
• Convert Functions (functions used to convert data types and values)
• Aggregation Functions (letters used for data aggregation)

The total number of functions is over 200 and does not need to be fully remembered. The number of commonly used functions is 30-50, so that you can look at the document when other non-used functions are used.

Count Functions

• ABS - Absolute Value Syntax:ABS( 数值 ) The ABS function is used to calculate the absolute value of a number, representing the distance from a number to its original point. When the parameter for the ABS function isNULL, return valueNULL。

• MOD -- Ask for balance Syntax:MOD( 被除数，除数 ) The MOD is a function of calculating the residual number (excess) and is the abbreviation of the modulo. There is no concept of the number of decimals, but only the number of integer columns. Note: Mainstream DBMS supports the MOD function, only SQL Server does not support it, and it is used%symbol to calculate the balance.

• ROUND -- rounded Syntax:ROUND( 对象数值，保留小数的位数 ) The ROUND function is used for rounding operations. Note: when parameters Keep decimal places When you are a variable, you may encounter a mistake, so be careful with the variable.

String Functions

• CONCAT -- Spelling Syntax:CONCAT(str1, str2, str3) MySQL uses the CONCAT function to spell.

• LENGTH - String Length Syntax:LENGTH( 字符串 )

• LOWER -- lowercase conversion The LOWER function, which can only be used for letters, converts all strings in parameters to lowercase. This function does not apply to places other than English letters, without prejudice to characters that are originally lowercase. Similarly, the UPPER function is used for capitalisation.

• REPLACE - Replace String Syntax:REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

• SUBSTRING - Interception of Strings Syntax:SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数） Use the SUBSTRING function to intercept a part of a string. The starting position of the intercept starts with the leftmost side of the string and the index value starts with 1.

• SUBSTRING INDEX - Indexed Syntax:SUBSTRING_INDEX (原始字符串， 分隔符，n) This function supports positive and reverse indexing with initial values of 1 and -1 respectively, after the original string is separated by separator.

• REPEAT - String repeats repeatedly as required Syntax:REPEAT(string, number)

Date Functions

Different DBMS date functions vary, and this course presents a number of functions recognized by standard SQL that can be applied to most DBMS. A specific DBMS date function is sufficient to access the document.

• Current DATE - Get the current date

• CURRENT TIME -- Current Time

• CURRENT TIMESTAMP - Current date and time

• EXTRACT - Intercept Date Elements Syntax:EXTRACT(日期元素 FROM 日期) Use the EXTRACT function to intercept part of the date data, e.g. " year " month, " hour " seconds, etc. The return value of the function is not a date type but a value type

Convert Functions

The term "conversion" has a very broad meaning, and in SQL there are two main meanings: the first is the conversion of data types, abbreviated as the conversion of types, referred to in English ascast; the other level means value conversion.

• CAST - Type Conversion Syntax:CAST（转换前的值 AS 想要转换的数据类型） Of particular note is the need to specify SIGNED or UNSIGNED when converting to integers

• COALESSE - Find non-NULLs for ease of conversion Syntax:COALESCE(数据1，数据2，数据3……) COALESE is a function specific to SQL. This function returns the value of NULL that begins on the left side of variable A. The number of parameters is variable and can therefore be increased indefinitely as required.

Word

The word returns a function whose value is real. IncludingTRUE / FALSE / UNKNOWNI don't know. Besides the functions that we described in the Comparative Operators section, there are functions that produce a boolean value.

The terms are as follows:

• LIKE
• BETWEEN
• IS NULL、IS NOT NULL
• IN
• EXISTS

LIKE

The word is used when partial consistency of a string is required.

Partial consistency can broadly be divided into three categories: forward, intermediate and rear.

Unanimously forward.

The syntax structure of the front is as follows:WHERE strcol LIKE 'ddd%'

The string (here is 'dddd) that is the same as the beginning of the string of the query object, which is the condition of the query.

Of which%is a special symbol for " Zero or more arbitrary strings " , which in this case is for " all strings starting with ddd " .

Midway

Intermediately, the query object string contains a string as a condition for the query, whether it appears at the end or in the middle of the object string.

Syntax:WHERE strcol LIKE '%ddd%'

Rear Unanimously

The string (here'dddd) that is the condition of the query is the same as the end of the string of the query object.

Syntax:WHERE strcol LIKE '%ddd'

Any Character

Use the underlined instead of %, unlike %, it represents "any one character".

Syntax:WHERE strcol LIKE 'abc__'

Between.

Use BETWEEN to make range queries. The term differs from other terms or functions in that it uses three parameters.

Let's give you one example:

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

The Between feature is that the result will contain 100 and 1000 thresholds, i.e., the closed space. If you don't want the result to contain a threshold, you have to use it. < and >

IS NULL、 IS NOT NULL

In order to select the data for certain columns with values NULL, the = cannot be used, but only the specific term IS NULL can be used.

On the contrary, if you want to select data other than NULL, use IS NOT NULL.

The reason for this rule is that SQL uses a three-value boolean, that NULL means unknown, and that any non-relative calculation he makes of UNKNOWN.

In word

You can choose to use multiple query conditions when taken togetherorstatement.

That is, as more and more people want to choose, useorThe SQL statement will be longer and harder to read. So we can replace the OR statement with the IN word `IN.

One simple example:

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

By the same token, we can use the negative form NOT IN.

It should be noted that Null data cannot be selected when using IN and NOT IN.

In particular, the IN statements are often used in combination with sub-references, for example.

-- 取出大阪门店在售商品的销售单价 `sale_price`
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');

Sub-Query provides a new table that gives IN the word space to search.

EXIST term

The use of the EXIST word is difficult to understand.

1 EXIST is used differently than before

2 Syntax:

In fact, if you don't use EXIST, you can basically use IN instead.

In that case, is there a need to learn the EXIST word? The answer is yes, because once the EXIST word is used, it's extremely convenient.

You need not be too worried, however, that this course presents some basic usages, and more attention can be paid to the use of the EXIST term in future studies, so that it can be used when reaching the SQL intermediate level.

• Methodology for the use of EXIST

That's the word. “to determine whether there is a record of fulfilment of certain conditions”。

If such a record exists, it returns true (TRAE) or, if it does not exist, false (FALSE).

The main word for the term EXIST (existence) is “record”.

We continue to use the example of IN and Sub-Query, using EXIST to select the unit price of the sale of goods at Osaka Gate.

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT *
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
4 rows in set (0.00 sec)

• EXIST parameters

The terms we have learned before are basically like " Column LIKE string " or " Column BETWEEN value 1 AND value 2 " , which requires the specification of more than two parameters, while the left side of EXIST does not have any parameters. Because EXIST is a word for only one parameter. So EXIST only needs to write one parameter on the right, which is usually a sub-search.

(SELECT *
   FROM shopproduct AS sp
  WHERE sp.shop_id = '000C'
    AND sp.product_id = p.product_id)  

Such a sub-Query above is the only parameter. To be precise, as the link between the produd and the shopprodud tables is made through the condition “SP.produtt id = P.produtt id”, the associated sub-reference is the parameter. EXIST usually uses associated sub-inquiries as parameters.

• SELECT*

Since EXIST only cares about the existence of records, it is irrelevant to return any columns. EXIST only determines whether there is a condition specified in the WHERE sub-rule "shop number (shop id) as '000C', product forms and stores

The record of the commodity number (product id) in the list of commodities is identical and returns only when such a record exists.

Therefore, using the query statement below, the search results will not change.

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
4 rows in set (0.00 sec)

You can use writing SELECT* in an EXIST sub-reference as a custom for SQL.

• Replace NOT IN with NOT EXIST

Just like EXIST can replace IN, NOT IN can also be replaced by NOT EXIST.

The following code examples are taken out of the unit prices for the sale of goods not sold at the Tokyo Gate.

SELECT product_name, sale_price
  FROM product AS p
 WHERE NOT EXISTS (SELECT *
                     FROM shopproduct AS sp
                    WHERE sp.shop_id = '000A'
                      AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀         |       3000 |
| 高压锅       |       6800 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+
5 rows in set (0.00 sec)

NOT EXIST, unlike EXIST, returns real (TRUE) when "does not exist " fulfils the record specified in the sub-search.

CASE Expression

What's a CASE expression?

CASE expression is a function. It's an important function of the SQL medium count. It's important to learn.

CASE expressions are used to distinguish situations, which are commonly referred to as branches (conditions) in programming.

The syntax of CASE expression is divided into simple CASE expression and search for CASE expression. Because the search for CASE expression contains all the functions of a simple CASE expression. This course will highlight the search for CASE expressions.

Syntax:

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END  

When the expression is executed, the statement after THEEN is executed, and if all the expressions are false, the statement after ELSE is executed. No matter how large the CASE expression, it will only return one value.

CASE use method

Apply scene 1: Different column values obtained from different branches

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END AS abc_product_type
  FROM  product;

– 下面是SELECT语句返回的表
+————–+——————+
| product_name | abc_product_type |
+————–+——————+
| T恤          | A ： 衣服        |
| 打孔器       | B ： 办公用品    |
| 运动T恤      | A ： 衣服        |
| 菜刀         | C ： 厨房用具    |
| 高压锅       | C ： 厨房用具    |
| 叉子         | C ： 厨房用具    |
| 擦菜板       | C ： 厨房用具    |
| 圆珠笔       | B ： 办公用品    |
+————–+——————+

The ELSE clause can also be omitted and will be defaulted as ELSE Null. But in order to prevent people from reading out, it is hoped that you will be able to write the ELSE subword in a prominent way.

In addition, the final “END” of the CASE expression cannot be omitted, and please be careful not to be omitted. Forget writing about ENDs can cause grammatical errors, which are the easiest mistakes at first school.

Apply scenario 2: no change in aggregation/content in column direction and modify the presentation Aggregation in the line direction is achieved using the method in the Syndication Query section, and convergence in the column direction requires reliance on CASE expression

Example 1

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

– 返回表结构
+——————-+——————-+——————+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+——————-+——————-+——————+
|              5000 |             11180 |              600 |
+——————-+——————-+——————+

In fact, if we choose the traditional one-action structure of a column, direct use of GROUP BY Cluster Convergence, but transversalization requires this CASE expression. Pattern

Example 2

-- CASE WHEN 实现数字列 score 行转列
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+

The original table structure is a three-column structure of name subject score, and we've been transformed into such a line structure, inside. SUM This polymer function is only designed to convert a list returned by CASE to a number

Example 3

-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+

It's not a practical example. It's just that we can use the text column if we want it. MIN，MAXThis aggregate query function performs

Pool Operations

About the assembly itself

集合In the mathematical field, the expression “summary of all things” and in the database area the expression of a collection of records. Specifically, the results of the execution of tables, views and queries are a collection of records, with the elements being a table or each line of the query results.

In standard SQL, use separate search results UNION，INTERSECT， EXCEPT to perform the search results in combination, intersection and differential calculations. The operator used to perform the pool operation is referred to as the pool operator.

In the database, all tables — as well as the search results — can be considered as a collection, so that the tables can also be considered as a collection for the above-mentioned aggregation operation, and in many cases this abstraction is very helpful in providing a workable idea of complex queries.

Add to Table - UNION

Let's start with an example.

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

As you can see, we remove the symbol that symbolizes the end of the sentence from the end of the preceding sentence, usingUNIONConnect them, and we'll end up with two teams.

UNION and others usually remove duplicate records.

This weighting will remove not only the repetition of the two result sets, but also the repetition of the concentration of results. In practice, however, there is sometimes a need not to be heavy, and it is very simple to keep the grammatical of repeat lines in the results of UNION, just add an all-word to the UNION. As follows:

-- 保留重复行
SELECT product_type
  FROM product
 UNION ALL
SELECT product_type
  FROM product2;

Intersection Operator INTERSECT

The condensation is the public part of the conglomerate, which, because of the differentity of the conglomerate elements, can be seen in a very intuitive way by means of the scribe.

Use INTERSECT The cross-coding code is as follows:

TABLE product INTERSECT TABLE product2;

Use INTERSECT The number of columns in the two tables for which operators perform cross-counting must be the same.The same type of field is required.

INTERSECT Operator priority above UNION and EXCEPT , and when they appear, they give priority to cross-counting

For the two search results of the same table, their surrender to INTERSECT actually enables the search conditions for the two queries to be matched by the AND word.

Discrepancies, patches and table subtractions

The reduction of the sum difference is somewhat different from the reduction of the actual amount. When one pool A minus another B is used, a policy of direct neglect is applied to those elements that exist only in the pool B and not in the pool A, so that the reduction of A and B is only the reduction of those elements in the pool A that also belong to the pool B.

The statement using MySQL version 8.0.31 for differential computation is as follows:

TABLE product EXCEPT TABLE product2;

In fact, the use of the NOT IN term can essentially achieve the same effect as the EXCEPT operation in SQL standard syntax.

For those operations that do not provide direct operators, we can do this by combining them with terminologies and common query codes.

JOIN Foundation

We've discussed a lot of ways to search and process data, but they can't get us to add the information that we have.

• Pool operations such as Union and INTERSECT operate in line directions
• function or CASE expression equation, which increases the number of columns and does not provide more information in substance

As we mentioned in the previous section on Linked Sub-Query, associated sub-Query allows us to obtain information from multiple tables at the same time, butLinkBetter to get information from multiple tables

JOIN is a connection.It's usually the equivalent of judgement."="), add columns in other tables for the " Add Columns " pool operation. It can be said that the connection is the core of the SQL query, that it is connected, that it is possible to obtain columns from two or more tables, that it is possible to simplify the past to a more readable form of an overly complex query, such as the use of associated sub-inquiries, and that it involves more complex queries.

Interconnection (INNER JOIN)

Basic INNER JOIN

The syntax format of the connection is:

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

Among them, the INNER keyword indicates the use of interconnectivity, which, for the time being, is not subject to scrutiny.

Using INNER JOIN in the FROM subword to connect two tables and to specify the link condition for the ON subphrase is shopprodud.produd id=product.produd id, the following query statement was obtained:

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id;

We have a simple aliases for each of the two tables. This is very common when using a connection.

Point 1: Need to use multiple tables in the FROM sentence when connecting Element 2: The link condition must be specified by using the ON clause Point three: The column in the SELECT clause should preferably be used in the form of a lister.

Use an inline link in conjunction with the WHERE clause

If you need to filter the search results with a WHERE sub-phrase at the same time as using an inline link, you need to write the WHERE sub-phrase behind the ON sub-phrase.

There are several ways to add the WHERE clause.

The first way to add the WEHRE clause is to use the above-mentioned query as a sub-search, encapsulating it in brackets, and then adding filter conditions to the outer layer of the query.

SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROM shopproduct AS SP
         INNER JOIN product AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服';

If we know that the WHERE clause will be implemented after the FROM clause, that is, after the INNER JOIN ON gets a new table, we get the standard formula:

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服';

Implementation order: FROM sub-rule -> What's the matter?> SELEECT sub-rules; the two tables are linked by a linked column, and a new form is obtained, and the WERERE sub-rules screen the rows of this new table on two conditions, and finally the SELEECT sub-rule selects those columns that we need.

And of course, we can also use the WHERE to screen first in two tables, and then connect the two sub-inquiries.

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- 子查询 1:从 shopproduct 表筛选出东京商店的信息
        SELECT *
          FROM shopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROM product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

Use inline connection in conjunction with GRUP BY sub-words

The use of an inline link in conjunction with the GRUP BY sub-word requires a distinction according to which table is located in the cluster column.

In the simplest case, the GRUP BY clause was used before the inline link.

However, if the columns are not identical to the one that is consolidated and neither is used to link the two tables, they can only be linked and then aggregated.

Just write the code according to our feelings. Group first and group later are on demand.

Self-connection (SELF JOIN)

The previous interlinks were all linked to two different tables. But in practice a table can also be linked to itself, which is called self-connection. It is important to note that self-connection is not the third link that is distinguished from interconnection and interconnection, and that self-linking can be either external or internal, but is another classification that differs from interconnection.

Inline links and association sub-inquiries

In thinking, the association sub-inquiries, which search for the same lines in the association column in table B, each of the rows of the table A takes, result in repeated queries resulting in a high cost calculation, and the inline links, although not functionally different, are significantly improved, both in performance and in grammar structure

Natural connection (NATURAL JOIN)

Natural connections are not a third link, distinct from internal and external connections, but are a special case of interconnections -- when the two tables are naturally connected, the intra-value link is made by reference to the listings contained in both tables, and the use of ON is not required to specify the connection conditions.

Syntax:

SELECT *  FROM shopproduct NATURAL JOIN product

Extra JOIN

The connection is to abandon the line of two tables that do not meet the requirements of the O.N., and the connection is to be linked. The outer link will selectively retain lines that cannot be matched according to the type of connection.

The outer link takes three forms according to which line is maintained: left link, right link and full external link.

Left-link saves lines that cannot be matched by an ON sub-rule in the left table, where the corresponding right-table lines are missing; right-link saves lines that cannot be matched by an ON sub-rule, where the corresponding left-table lines are missing; and full-out links save both lines that cannot be matched by an ON sub-rule and the corresponding rows are filled with missing values in the corresponding one.

Since the link can be linked by exchanging the position of the left and right watch, there is no difference in the essence between the left and right links. Whatever you want. It's easy to see that the whole outer link is the result of the left and right connections.

The three external syntaxes are:

-- 左连结
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

Except for the difference between de-linking and interlinking, the connection is not too different from the interlinking, so we can just choose precisely according to the changing circumstances.

Multi-table links

The link usually involves only 2 tables, but sometimes there are instances where more than 3 tables must be linked simultaneously, and in principle the number of such tables is not limited. Of course, we should distinguish the concept of the master table at this time.FROMstatement.

Give us a simple example of how to write multiple forms.

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 INNER JOIN Inventoryproduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

Even if you want to increase the number of linked tables to four, five...the same way you add them using INNER JOIN.

ON sub-speech step - non-equivalent link

At the beginning of the introduction of the link, it was mentioned that, in addition to using the equivalent of the judgement, a comparative operator could be used to connect. In fact, including comparative operators (%)<、<=、>、>=, BETWEEN, and all logical calculations (LIKE, IN, NOT, etc.) can be placed in the ON sub-word as a condition for connection.

Like what?

SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS my_rank
  FROM (--使用自左连结对每种商品找出价格不低于它的商品
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM product AS P1
          LEFT OUTER JOIN product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY my_rank; 

Understand that the non-equivalent connection is not a line addition, but a condition for those who fulfil our conditions for association and are selected in the SELECT statement to expand the information in the column

Cross-link - COSS JOIN

The first condition of a connection, whether external or external, is a condition of connection -- the "ON clause" -- which specifies the condition of the connection.

If you try not to use this connection, you may have found that there are many lines of action. The link removes the "on" clause, which is called "Cross JOIN."

Cross-links create a lot of rows and columns that combine each row of the left and right tables, which often leads to many meaningless lines appearing in search results. Of course, there is some utility in cross-linking some of the queries.

The syntax of the interconnection takes several forms:

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP
 CROSS JOIN product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP , product AS P;

Cross-linking is not applied to actual operations for two reasons. The first is that the results are of no practical value and the second is that they are too numerous and require support from a large amount of computing time and high performance equipment.

Advanced processing

Window Functions

The concept of the function and the underlying method of use

The function of the window is also known asORAP functionI'm sorry. ORAP, yes. OnLine AnalyticalProcessing The abbreviations are for real-time analysis of database data processing.

window function:

<窗口函数> OVER ([ PARTITION BY <列名> ]
                     [ ORDER BY <排序用列名> ])  

[ ] The content in [ ] could be omitted.

PARITITON BY Sub-Parat Optional parameters, which indicate how to group query lines into groups, similar to GRUP BY sub-rules, but the PARTITON BY sub-rules do not have the grouping function for GRUP BY sub-rules and do not change the number of lines recorded in the original table.

ORDER BY Sub-word Optional parameters, indicating how to sort rows in each partition, i.e., to determine which rules (fields) are to be sorted in the window.

Although... PARITITON BY Sub-Parat and ORDER BY Sub-word Both are optional parameters, but both cannot be absent at the same time (at least two or two). No, I'm not. <窗口函数> OVER( ) This usage is not meaningful (windows consist of all query lines, and window functions use all rows to calculate results).

window function, as in the end of the SELECT statement, you can specify the order of ascending/downs by key word ASC/DECC. Default to default the keyword by ASC

In principle, the window function can only be used in the SELECT sub-rule.

Window function OVER does not affect the sorting of the final result. It is only used to determine the order in which the function is calculated.

Window Function Type

In general, the functions of the window can be divided into two categories.

• Use SUM, MAX, MIN, etc. in window functions
• Specialized window functions for RANK, DENSE RANK etc.

Special Window Functions

RANK function

When you are calculating the sorting, the subsequent place is skipped if you have a record of the same place.

3 entries at 1st place: 1 bit, 1 bit, 4 bit...

DENSE RANK function

Also calculates the order of the order, even if a record of the same place is in place, it does not skip the subsequent place.

Example: 3 entries at 1st place: 1 bit, 1 bit, 2 bit...

ROW NUMBER function

Gives only consecutive places.

3 entries at 1st place: 1 bit, 2 bit, 3 bit, 4 bit

One example of a code is

SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product;  

Use of the polymer function on the window function

The polymer function is used in the window function by the same method as the previous dedicated window function, except that the result is oneCumulative. The syntax function value.

Gives an example of a code as

SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM product;  

The result of the polymer function is, in order of our order, this is the protocol id,Current and All Previous Lines. is the aggregate of the current line.

Move Average

As mentioned above, the polymer function calculates the aggregation of all data accumulated in the current row when the window function is used. Actually, you could have specified more details.Summary scopeI'm sorry. The aggregation is described as Frame (frame)。

Syntax:

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING (“Previous”), assigns the frame to "n rows before the deadline" and adds its own line FOLLOWING (“after”), assigns the frame to "n rows after the deadline" plus its own line

Example:

BETWEEN 1 PRECEDING AND 1 FOLLOWING, specify the frame as "Preceive 1 line" + "Post 1 line" + "Beyond itself"

GRUPING Operator

The regular GRUP BY receives only a subtotal for each classification, and sometimes the sum of the classification is calculated and can be used as ROLLUP keywords.

Syntax:

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP;  

Title

Continuous login user tags

Original table structure is the user login information, created by

CREATE TABLE login_records
(user_id INT,
 login_date DATE);

The information created for the login may relate to the exact time of the day, but we will last only consider the day of the login;

Marks the user (user id) who logs in for at least three consecutive days and generates the following fields for these users:

• Userid
• Start of continuous login
• End of consecutive login
• Number of consecutive login days

Here is an example of an AI-generated code.

WITH ranked_logins AS (
  SELECT
    user_id,
    DATE(login_date) AS login_date,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY DATE(login_date)) AS rn
  FROM login_records
  GROUP BY user_id, DATE(login_date)
),
grouped_logins AS (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL rn DAY) AS base_dt
  FROM ranked_logins
),
consecutive_streaks AS (
  SELECT
    user_id,
    base_dt,
    MIN(login_date) AS start_date,  -- 连续登录的第一天
    MAX(login_date) AS end_date,    -- 连续登录的最后一天
    COUNT(1) AS days
  FROM grouped_logins
  GROUP BY user_id, base_dt
  HAVING COUNT(1) >= 3
)
SELECT
  user_id,
  start_date,                   -- 直接使用第一天，无需+1
  end_date,                     -- 直接使用最后一天，无需+1
  days
FROM consecutive_streaks
ORDER BY user_id, start_date;

This is an example of what can be achieved through multilayer queries.

SELECT
  user_id,
  base_dt,
  COUNT(1)
FROM (
  SELECT
    *,
    DATE_SUB(dt, INTERVAL rn DAY) AS base_dt
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY a.user_id ORDER BY a.dt) AS rn
    FROM (
      SELECT
        user_id,
        DATE(dt) AS dt  -- 使用 MySQL 的 DATE() 函数
      FROM log_table
      GROUP BY
        user_id,
        DATE(dt)  -- 分组依据同步修改为 DATE(dt)
    ) a
  ) b
) c
GROUP BY user_id, base_dt
HAVING COUNT(1) >= 3;