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

This article introduces how to understand the union limit exists keyword in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

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 removing weight. 2.limit: paging query depends on it 1) some instructions on the use of limit ① limit is unique in MySQL, not in other databases, not universal; ② limit takes part of the data in the result set, which is its function; ③ limit is the last part of the execution of the sql statement; ④ limit uses syntax: limit startIndex,length; where startIndex represents the starting position, 0 represents the first 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:

3.exists usage: also known as "related subquery" 1) to help you understand the principle of exists execution

The data sources are as follows:

① when the return result is an one-line record ② when the return result is a multi-line record ③ principle explanation

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?

Do point out, that is, the condition of "where jobs.job=emp.job", which indicates that I have learned 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. On how to understand the union limit exists keyword in mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.