MySQL some of the important commands

Objective:

Recently, during the interview, I was deeply concerned about the missing of some important commands in MySQL. Take this opportunity to fill this piece of knowledge. Do not let oneself only will delete to change, understand some advanced things

• Limit usage
• Order BY usage
• In and between usage
• Usage of Join

(i) Limit usage

Let's look at the following code:

1 Select ID, name from table limit 5,

A simple limit command, limit m, n means to fetch n rows of data from m+1, output

Above that is, starting from line sixth, output 10 lines. That is, output 6-15 lines

Suppose we have the following table:

The data is about 100W.

Execute the following command:

1 Select *  from Ten ten;

0s is used and is not indexed.

Execute the following command:

1 Select *  from 1000000 ten;

The same 10 data was used for 0.31 seconds. It's also a full-fledged scan.

Below I use index optimization:

It took 0.17 seconds. 0.31/0.17 about twice times faster.

Optimize again below:

0.00s, can say quickly do not know a little bit two.

Remember to use the limit must be indexed, and if M (offset) is too large, first use where to filter a wave of data out in processing.

(ii) Order by usage

oder by is very simple, and the range index is used by default.

By default ascending, you can add desc to descending order.

(iii) Use of in and between

We can see from explain that both in and between are indexed.

And between has a powerful feature, when compared to limit. When the offset is large, but the amount of data taken is small, it is most suitable to use between

A look at the experiment

The direct 0.00s is done, a little bit unambiguous.

(iv) Use of join

• JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

"Persons" table:

id_p	lastname	firstname	address	City
1	adams	john	oxford Street	London
2	bush	george	fifth Avenue	New York
3	carter	thomas	changan Street	Beijing

"Orders" table:

Id_o	OrderNo	id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

INNER JOIN:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

Left JOIN:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

Notice that there is no last line OrderNo is empty

Right join:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

After statement:

After writing, found that good tired, really good tired. Wrote for two hours, begged Dad to let me on the hot.

However, some understanding of MySQL is also more profound, especially the use of limit.

MySQL some of the important commands