DQL query statement

Time：2021-1-19
1. Sort query
*Syntax: order by clause
*Order by sort field 1, sort method 1, sort field 2, sort method 2

*Sort by:
*ASC: ascending order, default.
*Desc: descending order.

*Note:
*If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.
1. Aggregate function: a column of data as a whole, vertical calculation.

1. Count: count
1. Generally, select a non empty column: primary key
2. count(*)
2. Max: calculate the maximum value
3. Min: calculate the minimum value
4. Sum: calculate and
5. AVG: calculate the average value
• Note: the calculation of aggregate function excludes null value.
Solution:
1. Select the column that does not contain non empty to calculate
2. Ifnull function
2. Group query:

1. Syntax: group by group field;

2. be careful:

1. Query fields after grouping: grouping fields and aggregate functions
2. What’s the difference between where and having?
1. Where is defined before grouping. If the condition is not met, it will not participate in grouping. Having is limited after grouping. If the result is not satisfied, it will not be queried
2. Where cannot be followed by aggregate function. Having can judge aggregate function.

–They were grouped by gender. Query the average score of male and female students respectively

SELECT sex , AVG(math) FROM student GROUP BY sex;

–They were grouped by gender. The average score and number of male and female students were inquired respectively

SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

–They were grouped by gender. The average scores of male and female students were inquired respectively. The number of students required: those with scores lower than 70 will not participate in the grouping
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

–They were grouped by gender. Query the average score of male and female students respectively, the number of requirements: the score is lower than 70 points, do not participate in the grouping, after grouping. There should be more than two people
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

Select sex, AVG (Math), count (ID) from student where math > 70 group by sex having > 2;

3. Paging query

1. Syntax: index starting from limit, number of queries per page;

2. Formula: starting index = (current page number – 1) * number of entries per page
–3 records per page

Select * from student limit 0,3; — page 1

Select * from student limit 3,3; — page 2

Select * from student limit 6,3; — page 3

3. Limit is a MySQL dialect

Application of tree

Application of tree Storage structure of tree Parental representation (sequential storage) Definition: in each node, the “pointer” (position subscript) pointing to the parents is saved. The root node is fixedly stored in 0, – 1 indicates that there are no parents. Add: add directly without following the logical order. Delete: ① set the pointer to […]