MySQL series tutorial on understanding the use of union (all) and limit and exists keywords

1.union: You can add query results

Union is used to add query results, especially when data from two unrelated tables are spliced ​​together for display.
But there is a prerequisite: when different results are spliced, the number of columns must be the same.

Use the following data to illustrate the usage of union:

1) union all: cannot remove duplicates

2) Union: can achieve the effect of deduplication.

2.limit: paging query depends on it

1) Some explanations on the usage of limit

① limit is unique to MySQL and does not exist in other databases, so it is not universal;

② Limit takes part of the data in the result set, which is its function;

③ Limit is the last step in the execution of the SQL statement;

The syntax for using limit is:

limit startIndex,length; startIndex indicates the starting position, starting from 0, 0 indicates the first piece of data, and length indicates how many pieces to take.

2) Case Description

The data sources are as follows:

① Take out the top five employees in terms of salary and display their information.

② Find the employees whose salaries rank 4th to 9th.

3) Common standard paging SQL

According to the above figure, we can find that:

Take Baidu Browser for example:

3. Exists usage: also known as "correlated subquery"

1) Let you understand the execution principle of exists

The data sources are as follows:

① When the returned result is a row of records

② When the returned result is multiple rows of records

③ Principle explanation

From the above figure, we can see that no matter the SQL statement after the exists keyword, no matter whether one result or multiple results are queried, as long as the result is found, the entire result is True, and True in MySQL is represented by 1, so the final result is 1. Once the SQL statement following the exists keyword fails to find any results, the final return value is False. In MySQL, False is represented by 0, so the final result is 0.

2) Case Demonstration

Using the data source below, complete the following two exercises.

① Query the jobs table to see which job is being done.

② Query the jobs table, which job is not being done?

3) A diagram illustrating the principle of the exists subquery

The explanation is as follows:

"There is a company A. All the jobs in the company are in the jobs table. The emp table shows which jobs have been done."
select jobs.job
from jobs
where not exists(select * from emp where jobs.job=emp.job);

1) Figure out what results you want to achieve.
What you want to get here is "which jobs are not done", that is, the returned results come from the jobs table, but
"How do we know which jobs are being done and which are not?" This requires us to refer to the emp table.

2) First, take the first record from the jobs table and put it into the emp table to match it with every row in the table. When the horse
When you match the first row, since each row in the emp table has 8 columns, what exactly do you want to match? Should you specify
Specify the following, that is, "where jobs.job = emp.job". This condition indicates that I
Take the first record from the table and match it with each row in emp, and I am more specific, I am with your first row
To match the job field, you only need to see if jobs.job and emp.job are equal. If they are equal, return the record.
Then, take the first line and match it down in sequence. As long as jobs.job and emp.job are equal, return
This record. Therefore, if the first row in jobs is matched with each row in emp, a result set will be returned.

3) Let's look at the exists keyword. The result returned by exists() is true or false. When there is a value in the brackets,
If it exists, true is returned; if there is no value in the brackets, false is returned. According to (1), we
As we know, after the first row in jobs matches each row in emp, a result set is returned, which proves that there is a result set returned.
value, so exists() returns true.

4) When a not is added before exists(), it means negation. exists() returns true, not exists()
The return value is false.

5) According to the above description, when not exists() becomes false, the original statement is equivalent to:
select jobs.job from jobs where false;
Therefore, the first row of clerk cannot be fetched.

6) Next, take the second row "SALESMAN" in jobs and match it with each row in the emp table.
Repeat the above steps.

The above is the detailed content of the MySQL series of articles on understanding union (all), limit and exists keywords. For more information about the MySQL series of union (all), limit and exists keywords, please pay attention to other related articles on 123WORDPRESS.COM!