preface

Function: encapsulates commonly used functions in a function definition, and executes encapsulated code logic through function calls.

Function has strong functionality and reuse.

As a powerful and popular database, MySQL itself contains a large number of and rich built-in functions. We can use these built-in functions to convert the retrieved data to value or format.

The MySQL function processes the parameters passed in and returns a result, that is, a value.

In our common range, MySQL functions can be divided into two broad categories: data processing functions and aggregation functions, if they are differentiated by processing on data rows or result sets.

This article is my own learning summary, summary summary of this part of the content.

content

Data processing function

The built-in functions of MySQL include functions that execute on each row of data to transform and process data. This class of functions is called data processing functions.

According to the data type of the data processed, it can be classified into text processing function, date and time processing function, and numerical processing function.

Text processing function

Text processing functions are used to process text strings and perform common text data operations such as length return, case conversion, and substring search. Common ones include:

function instructions function instructions
Left() Returns the character to the left of the string Right() Returns the character to the right of the string
LTrim() Remove the space to the left of the string RTrim() Remove the Spaces to the right of the string
SubString() Returns the character of a substring Locate() Find a substring of a string
Upper() Converts the string to uppercase Lower() Converts the string to lowercase
Length() Returns the length of the string Soundex() Returns the SOUNDEX value of the string

Date and time handlers

Date and time handlers are used to process date and time values and extract the date and time of a particular component from those values. Common ones include:

function instructions function instructions
AddDate() Add a date (day, week, etc.) AddTime() Add a time (hour, grade)
CurDate() Return current date CurTime() Return current time
Date() Returns the date portion of the date time DateDiff() Calculate the difference between the two dates
Date_Add() Highly flexible date manipulation function Date_Format() Returns a formatted date or time string
Day() Returns the number of days of a date DayOfWeek() For a date, return the day of the week
Hour() Returns the hour portion of a time Minute() Returns the minute portion of a time
Month() Returns the month portion of a date Now() Returns the current date and time
Second() Returns the second portion of a time Time() Returns the time portion of a date-time
Year() Returns the year portion of a date

Numerical processing function

Numerical processing functions are used to perform arithmetic operations on numerical data. Common ones include:

function instructions function instructions
Abs() Returns the absolute value of a number Tan() Returns the tangent of an Angle
Cos() Returns the cosine of an Angle Sin() Returns the sine of an Angle
Exp() Returns the exponent value of a number Mod() Returns the remainder of the division operation
Pi() Returns PI Rand() Returns a random number
Sqrt() Returns the square root of a number

Aggregation function

The above data processing functions are executed on a per-row basis, but this approach does not always meet our needs. In the case of statistical data (for example, summarizing data to get the average value of a field in a data set), we need to perform calculations and transformations on row groups (which can be result sets or subresult sets) rather than individual calculations on each row. At this point, we use the aggregate function.

An aggregate function is a function that runs on a group of rows, calculating and returning a single value. It targets groups of rows rather than individual rows, resulting in a summary of data.

Common aggregation functions include:

function instructions function instructions
AVG() Returns the average value of a column MAX() Returns the maximum value of a column
MIN() Returns the minimum value of a column SUM() Returns the sum of the values of a column
COUNT() Returns the number of rows in a column

The aggregation function has the following characteristics in use:

  • The above aggregate function ignores rows with NULL columns and does not aggregate the row.

  • COUNT() supports the asterisk *. COUNT(*) is used to COUNT rows in a table to obtain the number of rows in the table.

  • By using a DISTINCT keyword (the default ALL keyword if not specified), the result set is filtered into a result set with different values, followed by aggregation function calculations;

    Get the number of rows in the table
    SELECT COUNT(*) FROM [table_name];
    
    Get the average value of the columns in the table
    SELECT AVG([column_name]) FROM [table_name];
    
    Get the number of column values in the table
    SELECT COUNT(DISTINCT [column_name]) FROM [table_name];
Copy the code

The resources

  • MySQL Must Know must Know

  • MySQL Documentation