preface

This blog follows the content of the previous blog and supplements the DQL language. It mainly talks about the sorting query, common functions and grouping query in the DQL language. These three pieces of content mainly reflect the sorting and grouping query functions of MySQL. Here is the link to the blog that knows MySQL for the first time( [start making friends with MySQL] - get to know MySQL ), friends can click to see what they want to know. If you think the blogger writes well, remember to click three times! Crab crab~

Sort query

 
Syntax:

select Query list
from Table name
[where  [filter criteria]
order by Sorted field or expression;

 
characteristic:

1. asc represents ascending order and can be omitted; desc represents descending order

2. The order by clause can support a single field, alias, expression, function, multiple fields

3. The order by clause is at the end of the query statement, except for the limit clause
 
 

Common functions

 

Definition and overview

concept

A method similar to java encapsulates a group of logical statements in the method body and exposes the method name
 

call

select Function name(Argument list) [from [table];

 

benefit

1. Hidden implementation details

2. Improve code reusability
 

classification

1. Single line functions, such as concat, length, ifnull, etc

2. Grouping function, function: used for statistics, also known as statistical function, aggregation function and group function

 

Single-Row Functions

1, Character function

1.length Gets the number of bytes of the parameter value

2.concat Splice string

3.upper Convert to uppercase lower Convert to lowercase

4.substr,substring

Intercepts all subsequent characters from the specified index

Select substr (string, variable);

Intercepts characters of the specified length from the specified index

Select substr (string, variable, variable);

5.instr Returns the index of the first occurrence of the substring. If it is not found, it returns 0

select instr (string, substring);

6.trim Remove the leading and trailing spaces,You can also specify the characters to be removed  

select trim (character from string);

7.lpad Fill the specified length with the specified character

select lpad (string, length, specified character);

8.rpad Fill the specified length with the specified character

select rpad (string, length, specified character);

9.replace replace

select replace (string, replaced character, replaced character);
 

2, Mathematical function

round rounding

ceil Round up,return>=The minimum integer for this parameter

floor Round down and return<=The maximum integer for this parameter

truncate truncation

select truncate (number of digits reserved after the decimal point)

mod Surplus

select mod (dividend, divisor)

 

3, Date function

now Return to current system period+Time (no parameters)

curdate Returns the current system date, excluding time

curtime Returns the current time, excluding the date

str_to_date Converts characters into dates in the specified format

select str_to_date (character, specify format)

date_format Convert date to character

select date_format (date, specify format)

You can get the specified part, such as year, month, day, hour, minute and second, for example:

SELECT YEAR(NOW()) year;
SELECT YEAR('1998-1-1') year;

SELECT MONTH(NOW()) month;
SELECT MONTHNAME(NOW()) month;  (Get month name)

 

4, Other functions

SELECT VERSION(); //View the version number of sql
SELECT DATABASE();//View table
SELECT USER();

 

5, Process control function

1.if function: effect of if else

if(Judgment conditions (if true, if false)

2. Use of case function I: effect of switch case

case Field or expression to judge
when Constant 1 then Value 1 or statement 1 to display;
when Constant 2 then Value 2 or statement 2 to display;
...
else Value to display n Or statement n;
end

3. Use of case function 2: similar to multiple if

case 
when Condition 1 then Value 1 or statement 1 to display
when Condition 2 then Value 2 or statement 2 to display
. . . 
else Value to display n Or statement n
end

 

Grouping function

classification

sum, avg average, Max max, min Min, count

 

characteristic

1. sum and avg are generally used to handle numeric types. max, min and count can handle any type

2. The above grouping functions ignore null values

3. It can be used with distinct to realize the operation of de duplication

sum(distinct ...)

4. The count function is introduced separately. Generally, count(*) is used to count the number of rows

Efficiency:
Under the MYISAM storage engine, the efficiency of count (asterisk) is high
Under the INNODB storage engine, the efficiency of count (asterisk) and COUNT(1) is similar and higher than that of count (field)

5. The fields queried together with the grouping function must be the fields after group by

6. There are restrictions on the fields queried with grouping functions

give an example:

SELECT AVG(salary),employee_id  FROM employees;

In the above example, AVG(salary) is a row of data, while employee_id is a list of data

 
 

Grouping query

 

grammar

select Query list
from surface
[where [filter criteria]
group by Grouped fields
[order by Sorted fields];

 

characteristic

1. The field queried with the grouping function must be the field after group by

2. There are two types of filtering: pre grouping filtering and post grouping filtering

be careful:
Grouping functions cannot be placed after where for filtering
From left to right: where -- group by -- having

3. Grouping can be by a single field or by multiple fields

4. It can be used with sorting

 

knowledge has no limit