MySQL (vi) common functions of DQL

Concept: A Java-like approach that encapsulates a set of logical statements in a method body, exposing the method name externally

Benefits: 1, hide the implementation of the details 2, improve the reusability of the Code

Call: Select function name (argument list) "From table";

1, character function 1.1, length gets the number of bytes of the parameter value

SELECT LENGTH (' John ');

1.2. Concat Stitching String
1.3, Upper, lower

Example: Capitalize the last name, change the name to lowercase, and then stitch

1.4, SUBSTR, substring

Note: The index starts at 1

Example: Intercepting all the characters that follow from the specified index

Example: Intercepting a character that specifies the length of a character from a specified index

Case: The first character in the name is capitalized, the other characters are lowercase and then spliced to show

1.5, InStr

Returns the index of the first occurrence of a substring if no return 0 is found

1.6. Trim

1.7, Lpad

Implements the left padding with the specified character for the specified length

1.8, Rpad

Implements the right padding with the specified character for the specified length

1.9. Replace replacement
2, Math function 2.1, round rounding
2.2, Ceil up, return >= the smallest integer of the parameter
2.3, floor down rounding, return <= the maximum integer of this parameter
2.4, Truncate truncation
2.5, MoD to take the remainder

MoD (b): A-a/b*b

MoD ( -10,-3): -10-(-10)/(-3) * (-3) =-1

3. Date function
3.1. Now returns the current system date + time
3.2, Curdate returns the current system date, does not include the time
3.3. Curtime returns the current time, not including the date
3.4, can get the specified part, year, month, day, hour, minute, second

3.5, Str_to_date

Converts a character to a date by a specified format

3.6, Date_format

Convert dates to Characters

4. Other functions

SELECT VERSION ();

SELECT DATABASE ();

SELECT USER ();

5. Process Control function 5.1, if function
5.2. Case function

Way One:

Way two:

6. Grouping functions

Function: Used as a statistic, also known as aggregate function or statistical function or group function

Categories: Sum sum, avg average, max Max, Min min, count calculation

Characteristics:

(1) Sum, AVG generally used for processing numerical type

Max, Min, count can handle any type

(2) The above grouping function ignores null values

(3) can be combined with distinct to achieve the calculation of the weight

(4) Count function: Use COUNT (*) generally as the number of rows of statistics

(5) fields that are queried together with the grouping function require a field after group by

6.1. Easy to use
6.2. If NULL is ignored
6.3. Match with distinct
6.4. Detailed description of the Count function

Efficiency:

The efficiency of COUNT (*) is high under the MyISAM storage engine

With the InnoDB storage engine, COUNT (*) and COUNT (1) are much more efficient than count (field).

Follow the public number: Java Back-end life, dry article first time delivery!

MySQL (vi) common functions of DQL