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

This article will explain in detail what the advanced query syntax of MySQL is. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

I. sorting

Sort query syntax:

Select * from table name order by column 1 asc/desc [column 2 asc/desc,...]

Syntax description:

Sort by column 1 first, and if column 1 has the same value, sort by column 2 asc: sort in ascending order (from small to large) desc: sort in descending order (from big to small) default is ascending sort (asc)

The information of boys has not been deleted, in descending order by student number:

Select * from students where is_del = 0 and sex = 'male' order by id desc

All student information is displayed, sorted first by age from oldest to youngest, and by height when the same age is the same:

Select * from students order by age desc,height desc

Second, paging query

When shopping online, when browsing the list of goods, because there is so much data, it can not be displayed on a page, and it is displayed on a page by page. This is paging query.

Select * from table name limit start,count

Description:

Limit is the paging query keyword start indicates the start row index, and the default is 0count indicates the number of query entries

Query the information of the boys in the first three lines:

Select * from students where sex=' male 'limit 0Jing 3

Can be abbreviated as

Select * from students where sex=' male 'limit 3

Display m pieces of data on each page and find the data displayed on page n (the key is to find the index of the beginning row of each page)

Select * from students limit (n Mel 1) * m M; III. Aggregate function

Aggregate function, also known as group function, is usually used to count and calculate the data in the table, usually combined with grouping (group by), to count and calculate grouped data.

Common aggregate functions:

Count (col): the total number of rows of the specified column max (col): the maximum value of the specified column min (col): the minimum value of the specified column sum (col): the sum of the specified column and avg (col): the average value of the specified column

Calculate the total number of rows:

Returns the total number of rows of non-null data select count (height) from students; returns the total number of rows, including the null value record select count (*) from students

Find the maximum value:

Query the maximum value of the boy number select max (id) from students where sex=' male'

Find the minimum:

Query the undeleted student minimum number select min (id) from students where is_del=0

Summation:

Query the total height of boys select sum (height) from students where sex=' male'; query the average height of boys select sum (height) / count (*) from students where sex=' male'

Find the average:

To calculate the average height of boys, the aggregation function does not count the null value select avg (height) from students where sex=' male'; calculate the average height of male students, including the value select avg (ifnull (height,0)) from students where sex=' male 'whose height is null

Description:

Ifnull function: determines whether the value of the specified field is null. If it is empty, use the value provided by yourself.

Aggregate function characteristics:

The aggregate function ignores records with field null by default. In order for records with column values of null to participate in the calculation, the null value must be replaced with the ifnull function.

IV. Grouping query

Grouping query is to group the query results according to the specified field, and the data in the field is divided into a group.

The basic syntax format of grouping query:

Group by column name [having conditional expression] [with rollup]

Description:

Column name: refers to grouping according to the value of the specified field having conditional expression: used to filter the grouped data with rollup: add a record to the end of all records to display the statistics and calculation results of the aggregate function in the select query

Use of group by:

Group by can be used for a single field grouping or for multiple field grouping

Grouping according to sex field select gender from students group by sex; grouping select name,sex from students group by name,sex according to name and sex field

Use of group by + group_concat ():

Group_concat (field name): counts the set of information in the specified fields of each group, separated by commas

Group according to sex field, query sex field and grouped name field information select sex,group_concat (name) from students group by sex

The use of group by + aggregate functions:

Statistics of the average age of people of different genders select sex,avg (age) from students group by sex; statistics of the number of people of different genders select sex,count (*) from students group by sex

Use of group by + having:

Having is similar to where in filtering data, but having filters packet data and can only be used in group by

Group according to sex field, and count the select sex,count (*) from students group by sex having count (*) > 2 for which the number of packets is greater than 2.

Use of group by + with rollup:

The function of with rollup is to add a row after the last record to display the statistics and calculation results of the aggregate function in the select query.

Group according to the sex field, summarize the total number of people select sex,count (*) from students group by sex with rollup; group according to the sex field, summarize the age of all people select sex,group_concat (age) from students group by sex with rollup

Summary:

Group by groups data according to one or more fields specified group_concat (field name) function is to count the information set of each grouping specified field aggregate function when used in conjunction with group by, the aggregate function counts and calculates the data of each group having conditionally filters the grouped data with rollup adds a row after the last record, showing the statistics and calculation results of the aggregate function during the select query

Fifth, join query

Join query can be used to query multiple tables. When the field data of the query comes from different tables, it can be done using join query.

Join queries are divided into:

Inner join query left join query right join query self-join query

1. Internal join query

Query qualified common records in two tables (take intersection)

Inner join query syntax format:

Select field from Table 1 inner join Table 2 on Table 1. Field 1 = Table 2. Field 2

Description:

Inner join is the inner join query keyword on is the join query condition

Use the inner join to query the student table and class table:

Select * from students s inner join classes c on s.c_id = c.id

The contents of the original two tables:

Use an internal connection:

two。 Left join query

Query the data of the right table according to the condition based on the left table, and fill it with null value if the data of the right table does not exist according to the condition.

Left join query syntax format:

Select field from Table 1 left join Table 2 on Table 1. Field 1 = Table 2. Field 2

Description:

Left join is the left join query keyword on is join query condition Table 1 is the left Table 2 is the right Table

Use the left join to query the student table and class table:

Select * from students s left join classes c on s.c_id = c.id

3. Right join query

Query the left table data according to the condition based on the right table, and fill it with null value if the left table data does not exist according to the condition.

Right join query syntax format:

Select field from Table 1 right join Table 2 on Table 1. Field 1 = Table 2. Field 2

Description:

Right join is the right join query keyword on is join query condition Table 1 is left Table 2 is right Table

Use the right join to query the student table and class table:

Select * from students s right join classes c on s.c_id = c.id

4. Self-join query

The left table and the right table are the same table, and the data in the two tables are queried according to the join query criteria.

Create the areas table:

Create table areas (id varchar (20) not null primary key,title varchar (30) not null,pid varchar (20))

Execute the sql file to import data into the areas table:

Source areas.sql

Contents of sql file:

Insert into areas values ('11003', 'Beijing', null); insert into areas values ('11001', 'Beijing', '11000'); insert into areas values ('11002,' Dongcheng', '11001'); insert into areas values ('11003,' Xicheng', '11001'); insert into areas values ('11004, Chaoyang District,' 11001'); insert into areas values ('11005, Fengtai District,' 11001') Insert into areas values ('11006, Haidian District,' 11001'); insert into areas values ('12000, Hebei Province, null); insert into areas values (' 12001, Shijiazhuang, '12000'); insert into areas values ('12002Qing,' Chang'an District, '12001'); insert into areas values (' 12003Qing, Qiaodong District, '12001'); insert into areas values (' 12004Han, 'Qiaoxi District', '12001') Insert into areas values ('12005', 'Xinhua District', '12001')

Description:

Source represents the executed sql file

Usage of self-join query:

Select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id

Description:

Since the join query must alias the table

VI. Sub-query

In a select statement, if another select statement is embedded, the embedded select statement is called the subquery statement, and the external select statement is called the main query.

The relationship between the main query and the subquery

The subquery is embedded in the main query and the subquery is auxiliary to the main query, either as a condition or as a data source subquery that can exist independently. It is a complete select statement.

Inquire about students who are older than average:

Select * from students where age > (select avg (age) from students)

Check the names of all the students in the class:

Select name from classes where id in (select c_id from students where c_id is not null)

Look for the oldest and tallest student:

Select * from students where age= (select max (age) from students) and height= (select max (height) from students); can be abbreviated as: select * from students where (age,height) = (select max (age), max (height) from students)

This is the end of this article on "what is the advanced query syntax of MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.