SQL query entry (Part 1) recommended for favorites

SQL is a simple, easy-to-learn, but powerful language that allows you to quickly get started and write complex query statements. However, for most developers, using SQL to query databases does not have an abstract process or a reasonable step, this may cause some specific SQL query statements to be blocked when solving specific problems. This series of articles mainly describes some basic theories about SQL query, and the abstract idea of writing query statements.

SQL query Overview
The SQL language originated from the relational database theory published by E. J. Codd in 1970. Therefore, SQL serves relational databases. For SQL queries, it refers to obtaining a subset of data from the database. This sentence seems to be a bit obscure. Below are some pictures to explain:
Assume that a database has only one table, and all the data is taken from the AdventureWork example database ):

 

For the concept of subset, look:

 

Finally, the subset is as follows:

 

In fact, no matter how complicated the SQL query is, it can be abstracted into the above process.

Prefix for exact Query
For correct acquisition of the required data subset, in addition to correct thinking and correct thinking into the corresponding SQL query statement. It is also important to have a good database design. the good design here refers to that the database design conforms to the business logic and at least implements the third paradigm. for the realization of the third paradigm, this is just my personal point of view. For a brief introduction to the paradigm, please read my blog: Database paradigm. if the database design is poor, there is a lot of redundancy, and there are a lot of exceptions in the database information, even if the SQL write is correct, no accurate results can be obtained.
Two methods, the same result
In SQL, different ideas or different SQL statements can be used to obtain the same data subset, because SQL is derived from Relational Database Theory and relational database theory is also derived from mathematics, two methods can be abstracted when you think about how to construct a query statement:
1. Relational algebra
The idea of relational algebra is to perform step-by-step operations on the database and finally obtain the desired results.
For example, the following statement:
Copy codeThe Code is as follows:
Select Name, Department, Age
From Employee
Where Age> 20

The preceding statement describes the concept of relational algebra: perform projection (select columns) operations on the table's Employee table, and then filter the results to obtain only results older than 20.
2. Relational Algorithm
Compared with relational algebra, relational algorithms focus more on the conditions for obtaining data. the preceding SQL statement can be described as follows: I want to get the names, departments, and ages of all employees older than 20.
Why do we need two methods?
For a simple query statement, the above two methods do not need to be used. You can come up with a script. The problem is that many query statements are very complicated. Relational algorithms focus more on the conditions that information is extracted, while relational algebra focuses more on how specific information is extracted. in short, relational algorithms represent "what", while relational algebra expresses "how ". the concept leaked in SQL statements is relational algebra, relational algorithms, and a mixture of two ideas.
In some queries, the relational algebra method may be simpler, while in other cases, the relational algorithm will become more direct. In other cases, we need to mix two ideas. Therefore, these two ways of thinking are required when writing SQL queries.
Single Table query
A single table query is the intermediate state of all queries. It is not only a complex query of multiple tables that can be abstracted into a single table query after such a connection. Therefore, the query starts from a single table.
Select a subset of a column
According to the data subset statement above, the selected column is implemented by adding the selected column name after the select statement:
For example, in the following database, select a column name after select to select a subset of the column.

The corresponding SQL statement is as follows:
Copy codeThe Code is as follows:
SELECT [Name]
, [GroupName]
FROM [AdventureWorks]. [HumanResources]. [Department]

Select a subset of rows
When selecting a subset of rows, the corresponding constraints are added to the where clause of the SQL statement. When the expression after the where clause is true, that is, when the so-called "condition" is met, the subset of the corresponding row is returned.

The operators following the where clause are divided into two types: Comparison operators and logical operators.

The comparison operator compares two data of the same type and returns the Boolean operator. in SQL, there are six comparison operators, which are equal to (= ), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), and not equal to (<> ), the less than or equal to and greater than or equal to can be seen as a combination of comparison operators and logical operators.

Logical operators connect two Boolean types and return a new Boolean operator. in SQL, logical operators usually connect the Boolean types returned by the comparison operators to determine whether the where clause is followed by true or false conditions. There are three logical operators, AND (AND), OR (OR), NOT (NOT ).

For example, I want to select Article 2 and Article 6 above. To illustrate comparison operators and logical operators, you can use the following SQL statement:
Copy codeThe Code is as follows:
SELECT [Name]
, [GroupName]
FROM [AdventureWorks]. [HumanResources]. [Department]
WHERE between mentid> 1 and between mentid <3 or between mentid> 5 and between mentid <7

From this we can see that these operators have priority, And the priority is sorted from large to small by comparison operator> at (And)> not (Or)

Of course, operators can also use parentheses to change the priority of the preceding table.

When no parentheses are added:
Copy codeThe Code is as follows:
SELECT *
FROM [AdventureWorks]. [HumanResources]. [Department]
WHERE between mentid> = 1 and between mentid <= 3 and between mentid> = 5 or between mentid <= 7


After the brackets are added to change the operation sequence:
Copy codeThe Code is as follows:
SELECT *
FROM [AdventureWorks]. [HumanResources]. [Department]
WHERE between mentid> = 1 and between mentid <= 3 and (between mentid> = 5 or between mentid <= 7)


Very special NULL
If some optional information does not need to be entered in a user-registered table, it is saved as null in the database. When these null values use the operator following the where clause above, data may be lost. For example, if an optional information is Gender, assume the following two condition clauses:
Copy codeThe Code is as follows:
Where Gender = "M"
Where NOT (Gender = "M ")

Due to the existence of the null value, the data rows returned by these two statements are not all data in the entire table. Therefore, when the null value is taken into account, the Condition Clause after the where clause has possible values from true to false, increasing to true, false, and unknown (null ). These are the possible answers when we think about some questions in the real world-True, false, I don't know.

So how can we avoid data loss in this case? For the above example, how can we prevent the data of the entire table from being lost ", the "unknown" option other than "false" IS included. SQL provides the "IS NULL" option to indicate unknown:
Where Gender is null adds the preceding statement, and no data IS lost.

Sorting result
The methods above are about data extraction, and the following is about sorting the extracted subsets. SQL uses the Order by clause for sorting. The Order by clause is the last clause of the SQL query statement. That is to say, no clause can be added after the Order by clause.

The Order By clause can be divided into ascending (ASC) and descending (DESC). If no ascending or descending Order is specified, the default value is ascending (from small to large ), order by is determined based on the data type of sorting, which can be sorted for three data types:
Character
Number
Time and date
Characters are sorted by alphabet, numbers are sorted by number size, and time and date are sorted by time.

Others
View
A view can be viewed as a saved virtual table or a saved query statement. The advantage of a view is that the view can change according to the changes in the content of the table queried by the view. For example, to understand this sentence:

The advantage of using a view is that it can encrypt and facilitate query management. It is said that it can also optimize the performance (I do not agree with this ).

Prevent duplication
Sometimes we do not want to repeat the data subsets, for example, you want to know that some specific employees belong to several departments.
Copy codeThe Code is as follows:
SELECT [EmployeeID]
, [Initialize mentid]
FROM [AdventureWorks]. [HumanResources]. [employee1_menthistory]

 
This result is meaningless. SQL provides the Distinct keyword to achieve this:
Copy codeThe Code is as follows:
SELECT distinct mentid
FROM [AdventureWorks]. [HumanResources]. [employee1_menthistory]

 
Aggregate functions
The so-called aggregate function aggregates multiple values in the same column into one for some specific purposes. For example, if I want to know the maximum Age of a group, MAX (Age) can be used ), for example, if you want to know the average test score of a class, you can use AVG (Result )......
Summary
This article briefly outlines the principles of SQL queries and simple single-table queries. These are basic concepts of database queries. It is essential to understand these concepts for complex queries.