Summer self-study day 10 | database (III) – DQL

Time:2021-10-25

DQL

Sort query

  • ... order by keyword ASC/DESC
    • If ascending / descending order is not written, the default is ascending order

Aggregate function

  • Take a column as a whole and do longitudinal calculation
  • Null values are excluded
    • Solution: use the ifnull() function or select a non empty column
  • countNumber of calculations
    • SELECT COUNT(english) FROM stuorSELECT COUNT(IFNULL(english, 0)) FROM stuNull set to 0
  • max, min, sum, avgThe use method is the same as that of count

Grouping query

  • Group by group field
  • Query the data according to the grouping field. For example, if it is grouped according to gender, men and women are divided into two groups.
  • The syntax of two qualifications:where ...orhaving ...
    • difference:
      • Where is limited before grouping and does not participate in grouping if the conditions are not met; Having is limited after grouping. If the grouping is not satisfied, it will not be queried
      • Aggregate function cannot be used to judge where, but having can.

Paging query

  • Limit data start index, number of queries
    • select * from stu limit 0, 3;Start from 0 and check three records

Basic query

  • SELECT col1, col2, ... colN FROM t_table;
  • De duplication: use the distinct keyword
    • SELECT DISTINCT col1 FROM t_table;
  • Query the sum of two columns:
    • SELECT name, (math + english) FROM t_table
    • If a column of data is null, the operation result is null
  • Alias a column: use the as keyword
    • SELECT name, (math + english) AS total FROM t_table

Condition query

  • where
  • Operators >, <, =, > =,! =, < >, and/&&, or/||, not/|
  • BETWEEN … AND
    • ... BETWEEN 20 AND 30;
  • IN
    • ... WHERE age IN (22, 18, 25);
  • Is / is not: judge whether it is null
    • ... IS NULL

Fuzzy query

  • LIKE
    • _ Underscore: single arbitrary character
    • %Percent sign: multiple arbitrary characters
    • Query data containing a character:
      • ... where name like '% horse%';//Query contains the name of the horse

This work adoptsCC agreement, reprint must indicate the author and the link to this article