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

Paging query:

Paging queries are unique to MySQL and generally do not exist in other databases. A paging query can fetch a range of rows from a table, such as 0 to 50 rows of data and 30 to 100 rows of data.

The key word of the paging query is LIMIT, written at the end of the query statement.

SELECT * FROM student5 LIMIT which line, to which line

Code example:

Running result:

You can also take out several lines at a time:

Because there are no 12 lines, only the seventh line is taken.

Paging queries can also add conditions, as long as you write LIMIT after WHERE:

Running result:

When a paging query needs to sort at the same time, LIMIT needs to be written after the sort keyword.

Sort paging query code example:

Running result:

Example of grouping sort paging query code:

Running result:

Second-level conditions:

The second-level condition is to judge again after the previous statement has been run, the second-level condition keyword is HAVING, and the second-level condition needs to be written after the WHERE and grouping query statements.

Code example:

Running result:

Example of secondary condition code with WHERE condition:

Running result:

IN:

In is used to judge multiple values, the usual WHERE can only judge a single value, using in can judge multiple values at the same time.

Code example:

The above code means that all the lines in parentheses in IN are taken out.

Running result:

NOT IN:

NOT IN is the value excluded in parentheses, taking out all the values except in parentheses.

Code example:

Running result:

ALL:

ALL needs to satisfy everything in parentheses, but some versions of mysql cannot be used.

Code example:

ANY:

ANY is to satisfy any one of the parentheses to take out, like ALL, some versions can not be used.

Code example:

Multi-table federated query:

Multi-table federated query combines two tables together by UNION and UNION ALL keywords to query two tables at the same time.

UNION: removes all duplicates from the table and does not show duplicate rows.

Code example:

Running result:

UNION ALL: does not remove the repetition, but can display all the duplicate lines.

Code example:

Running result:

Table partition:

Table partitioning is to divide a table into multiple partitions. In the past, when the lower version of mysql did not support table partitioning, you can only create multiple tables to achieve the effect of partitioning, and multiple tables are not as convenient as a table divided into multiple extents.

Table partitioning is only supported by mysql version 5.1. table partitioning and partitioning are mainly to improve the performance of mysql and perform well in a high concurrency state. The benefits of table partitioning can be realized when there are millions of levels of data.

Mysql supports four kinds of partitions: range partition, hash partition, list partition, and subpartition.

Range Partition:

A range partition is a partition in a certain range, such as 0-1000, 1000-2000, 2000 and 2000, and so on, but the main concern is to create the last partition called maxvalue, which is used to store the remaining data. The key for table partitioning is: PARTITION, and the keyword for range partitioning is: RANGE.

Schematic diagram of range partition:

Code example:

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.