The second day of SQL learning-SQL DML and CTE Overview

DML (Data Manipulation Language) and CTE (Common Table Expression)
I did not know the meaning of the two abbreviations I encountered when I was reading a book today. So I wrote them here to facilitate the next review.

About DML (Data Manipulation Language ):
Data manipulation language: the computer language that allows you to query databases and operate data in existing databases. Specifically, UPDATE, INSERT, and DELETE.

About CTE (Common Table Expression ):
CTE (Common Table Expression), a public Table Expression, can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, or CREATE vi EW statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query.

CTE can be used:
1. Create a recursive query (I personally think it is best to use CTE ).
2. The generated table is referenced multiple times in the same statement.

CTE advantages:
1. Using CTE can improve readability and easily maintain complex queries.
2. query can be divided into separate blocks, simple blocks, and logical generation blocks. Then, these simple blocks can be used to generate more complex temporary CTE until the final result set is generated.
Scope of CTE:
You can define CTE in user-defined routines (such as functions, stored procedures, triggers, or views.

Here is a simple CTE example:
Store the largest salary id record in the test table in test_CTE, and then call

The Code is as follows:
Copy codeThe Code is as follows:
With test_CTE (id, salary)
As
(
Select id, max (salary)
From test
Group by id
)
Select * from test_cte