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

Recommended Today

Pandas data analysis — detailed explanation of super easy to use groupby

WeChat official account: “Python reads money”If there are any questions or suggestions, please official account message. In the daily data analysis, it is often necessary to analyze the dataDivide into different groups according to one (more) fieldFor example, in the field of e-commerce, the total sales of the whole country are divided by provinces, and […]