Shulou(Shulou.com)06/02 Report--

This article focuses on "MySQL series to understand how to use union (all) and limit and exists keywords", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "MySQL series to understand how to use union (all) and limit and exists keywords"!

Catalogue

1.union: query results can be added

1) union all: cannot remove weight

2) union: can achieve the effect of weight removal.

2.limit: paging queries depend on it

1) some instructions on the usage of limit

2) case description

3) General standard paging SQL

Usage of 3.exists: also known as "related subquery"

1) take you to understand the implementation principle of exists

2) case demonstration

3) A diagram to illustrate the principle of subquery.

1.union: query results can be added

Union is used to add query results, especially when data from two unrelated tables are spliced and displayed.

But there is one prerequisite: when different results are spliced, the number of columns must be the same.

Use the following data to illustrate the use of union:

1) union all: cannot remove weight

2) union: can achieve the effect of weight removal.

2.limit: paging queries rely on it 1) some instructions on the use of limit

① limit is unique in MySQL, but not in other databases and is not universal.

② limit takes part of the data in the result set, which is what it does.

③ limit is the last step in the execution of the sql statement

Usage syntax of limit:

Limit startIndex,length; where startIndex represents the starting position, starting with 0, 0 represents the first piece of data, and length represents several.

2) case description

The data sources are as follows:

① takes out the top five employees and displays their information.

② identified employees who ranked fourth to ninth in salary.

3) General standard paging SQL

According to the above figure, we can find that:

Take Baidu browser to explain:

Usage of 3.exists: also known as "related subquery"

1) take you to understand the implementation principle of exists

The data sources are as follows:

① when the returned result is an one-line record

② when the returned result is a multi-line record

Explanation of ③ principle

From the illustration above, we can see that regardless of the SQL statement after the exists keyword, whether it is querying a result or multiple results, as long as the result is found, the whole result is True, and the True in MySQL is represented by 1, so the final result is 1. Once the SQL statement after the exists keyword, the query does not produce any results, the final return value is False, and 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.

In the ① query jobs table, which work is done?

In the ② query jobs table, which work is not done?

3) A diagram to illustrate the principle of subquery.

The explanation is as follows:

"there is a company A where all the work is in the jobs table, and you can see what work has been done in the emp table."

Select jobs.job

From jobs

Where not exists (select * from emp where jobs.job=emp.job)

1) figure out what you want to get.

What you need to get here is "what work has not been done", that is, the returned results come from the jobs table, but

"how do you know which jobs are done and which are not?" This requires us to compare the emp table.

2) first, take the first record from the jobs table and throw it into the emp table to match each row of the table. When a horse

When it comes to the first row, since each row in the emp table has eight columns, what exactly do you want to match? Should it be clear?

It does specify the following, that is, the condition of "where jobs.job=emp.job", which indicates that I am from jobs

Take the first record from the table and match it with each line in emp, and I'm more specific, I'm on the first line with you.

To match the job field, you just need to see if jobs.job and emp.job are equal, and if so, return the entry

Record, then, take the first line and match it down, as long as it is whether jobs.job and emp.job are equal, return

The record. So matching the first row in jobs with each row in emp returns a result set.

3) if you look at the exists keyword, the result returned by exists () is true or false, when there is a value in parentheses

It exists, and true; is returned. When there is no value in parentheses, false is returned. According to (1), we

It is already known that after the first row in jobs matches each row in emp, a result set is returned, which proves that there is a return

Value, so the result returned by exists () is true.

4) when a not is added before exists (), it indicates inversion. Exists () returns true,not exists ()

What is returned is false.

5) according to the above description, when not exists () becomes false, the original statement becomes:

Select jobs.job from jobs where false

Therefore, the first line of clerk cannot be taken out.

6) then, take the second line "SALESMAN" in the jobs to match each row in the emp table one by one, and it is still heavy.

Repeat the above steps.

At this point, I believe you have a deeper understanding of "MySQL series understand how to use union (all) and limit and exists keywords". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.