1. aggregate function 11 summation function ----- SUM () summation function SUM () is used to SUM the data and return the SUM of all values in the selected result set. Syntax: SELECTSUM (column_name) FROMtable_name description: the SUM () function can only act on data type 1. aggregate functions 1.1 summation function ----- SUM ()
SUM () is used to SUM data and return the SUM of all values in the selected result set.
Syntax: select sum (column_name) FROM table_name
Note: the SUM () function can only act on numeric data, that is, the data in column column_name must be numeric.
1.2 counting function ----- COUNT ()
The COUNT () function is used to calculate the number of records in a table or the number of values in a column. the calculation content is specified by the SELECT statement.
When using the COUNT function, you must specify a column name or use an asterisk. An asterisk indicates that all records in a table are calculated.
The two methods are as follows:
COUNT (*) is used to calculate the total number of rows in the table. even if the row data in the table is NULL, it is included.
COUNT (column) is used to calculate the number of rows contained in the column. if the data of a row in this column is NULL, this row is not counted in the total number of statistics.
-- Use the COUNT (*) function to COUNT the number of rows in a table:
The COUNT (*) function returns a function that meets the search criteria in the WHERE clause of the SELECT statement.
-- Use the COUNT () function to COUNT data in a column:
The COUNT () function can be used to COUNT data values in a column.
Unlike the COUNT (*) function that ignores all columns, the COUNT () function checks values in one or more columns one by one and counts rows whose values are not NULL.
-- Use the COUNT () function to COUNT data in multiple columns:
The COUNT () function can be used not only to COUNT data values in a column, but also to COUNT data values in multiple columns.
If you COUNT multiple columns, you need to connect the multiple columns to be counted using a connector and use it as a parameter of the COUNT () function.
1.3 MAX/MIN functions ------ MAX ()/MIN ()
When you need to know the maximum values in a column, you can use the MAX () function;
Similarly, you can use the MIN () function to understand the minimum values in a column. The syntax is as follows.
Select max (column_name)/MIN (column_name) FROM table_name
Description: the data in column_name can be a value, string, or datetime data type.
The MAX ()/MIN () function returns a single value of the same data type as the passed column.
1.4 Mean function ----- AVG ()
The AVG () function is used to calculate the average value of data values in a column.
Syntax: select avg (column_name) FROM table_name
Note: the execution process of the AVG () function is to add up the values in a column and divide them by the number of non-NULL values.
Therefore, like the SUM () function, the AVG () function can only act on numeric data, that is, the data in column_name must be numeric.
1.5 merge functions ----- GROUP_CONCAT ()
GROUP_CONCAT can merge (splice) strings in the grouped columns ).
Syntax:
GROUP_CONCAT (
[DISTINCT] [, expr...] [, col_name]
[Order by {, col_name...} [ASC | DESC]
[SEPARATOR str_val]
)
Example: The table is as follows:
Country | population | name
China | 1 |
US | 1 | B
Japan | 5 |
Europe | 5 | c
South Korea | 2 |
Africa | NULL | B
(FROM table_name ignored)
-- SELECT GROUP_CONCAT (population) group by name
(Result: 1, 5, 2 | 1 | 5)
-- SELECT GROUP_CONCAT (population order by population) group by name
(Result: 1, 2, 5 | 1 | 5)
-- SELECT GROUP_CONCAT (population, '-', country) group by name
(Result: 1-China, 2-South Korea, 5-Japan | 1-US | 5-Europe)
-- SELECT GROUP_CONCAT (
(CASE country
WHEN 'China' THEN 'good'
ELSE 'bad'
END)
, '-', Population) group by name
(Result: good-1, bad-2, bad-5 | bad-1 | bad-5)
-- SELECT GROUP_CONCAT (population, SEPARATOR '-') group by name
(Result: 1-5-2 | 1 | 5)
1.6 processing of aggregated analysis values
The preceding five aggregate functions can act on all data in the selected column (no matter whether the data in the column is reset or not ),
You can also process the non-values in the column only, that is, take the repeated values only once for aggregation analysis.
Of course, for the MAX ()/MIN () function, the value processing is of little significance.
You can use the ALL keyword to indicate that ALL data in the selected column is processed,
Use the DISTINCT keyword to indicate that non-valued data in the selected column is processed.
Take the AVG () function as an example. The syntax is as follows.
Select avg ([ALL/DISTINCT] column_name) FROM table_name
Note: [ALL/DISTINCT] is the ALL keyword by default,
That is, all data is processed regardless of whether there are values. Other aggregate functions are used in the same way.
II. mathematical functions
ABS (x) returns the absolute value of x.
BIN (x) returns the binary value of x (OCT returns octal, HEX returns hexadecimal)
CEILING (x) returns the smallest integer greater than x.
EXP (x) returns the x power of e (the base of the natural logarithm ).
FLOOR (x) returns the largest integer less than x.
GREATEST (x1, x2,..., xn) returns the maximum value in the set.
LEAST (x1, x2,..., xn) returns the smallest value in the set.
LN (x) returns the natural logarithm of x.
LOG (x, y) returns the base y logarithm of x.
MOD (x, y) returns the modulus (remainder) of x/y)
PI () returns the pi value (circumference rate)
RAND () returns a random value between 0 and 1. you can provide a parameter (seed) to generate a specified value for RAND () random number generator.
ROUND (x, y) returns the rounded y decimal value of parameter x.
SIGN (x) returns the value of the symbol representing the number x.
SQRT (x) returns the square root of a number.
TRUNCATE (x, y) returns the result of truncating x to y decimal places.
III. string functions
ASCII (char) returns the ASCII value of a character
BIT_LENGTH (str) returns the bit length of the string.
CONCAT (s1, s2. .., sn) concatenates s1, s2. .., and sn into a string.
CONCAT_WS (sep, s1, s2. .., sn) concatenates s1, s2. .., sn into a string, and uses the sep character interval.
INSERT (str, x, y, instr): Start from position x of String str. replace substring with string instr with length y. The result is returned.
FIND_IN_SET (str, list) analyzes the list separated by commas (,). if str is found, the position of str in the list is returned.
LCASE (str) or LOWER (str) returns the result of changing all characters in the str string to lowercase.
LEFT (str, x) returns the leftmost x characters in the str string.
LENGTH (s) returns the number of characters in the str string.
LTRIM (str) removes the leading space from the str string.
POSITION (substr, str) returns the POSITION of the substring substr that appears for the first time in the str string.
QUOTE (str) escape single quotes in str with a backslash
REPEAT (str, srchstr, rplcstr) returns the string 'str' repeated x times.
REVERSE (str) returns the result of REVERSE string str
RIGHT (str, x) returns the rightmost x characters in the str string.
RTRIM (str) returns the space at the end of the str string.
STRCMP (s1, s2) compares strings s1 and s2
TRIM (str) removes all spaces at the beginning and end of the string.
UCASE (str) or UPPER (str) returns the result of converting all characters in the str string to uppercase.
IV. date and time functions 4.1 obtain the current system time
-- CURDATE () or CURRENT_DATE () returns the current date, for example, '2017-07-27'
-- CURTIME () or CURRENT_TIME () returns the current time, for example, '09: 36: 23'
-- NOW (), CURRENT_TIMESTAMP (), or SYSDATE () returns the current date and time, for example, '2017-07-27 09:37:11'
-- UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ).
If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT.
Date can be a number of a DATE string, a DATETIME string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format.
Example: SELECT UNIX_TIMESTAMP (); // result 1437965279
Example: SELECT UNIX_TIMESTAMP ('2017-10-04 22:23:00 '); // result 1997
-- FROM_UNIXTIME (unix_timestamp)
Returns the value of the unix_timestamp parameter in the format of 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS,
It depends on whether the function is used in a string or a number context.
Example: SELECT FROM_UNIXTIME (875996580); // '2017-10-04 22:23:00'
Example: select FROM_UNIXTIME (875996580) + 0; // 19971004222300
-- FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time Mark, formatted according to the format string.
Format can contain the same modifier as the entries listed by the DATE_FORMAT () function. For details, refer to the format table below.
Example: SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % S'); // '2017 27th July 10:53:29'
-- SEC_TO_TIME (seconds)
Returns the seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: SS' or HHMMSS,
It depends on whether the function is used in a string or a number context.
Example: SELECT SEC_TO_TIME (2378); // '00: 39: 38'
Example: SELECT SEC_TO_TIME (2378) + 0; // 3938
-- TIME_TO_SEC (time)
Returns the time parameter, which is converted to seconds.
Example: SELECT TIME_TO_SEC ('22: 23: 00'); // 80580 v
Example: SELECT TIME_TO_SEC ('00: 39: 38'); // 2378
4.2 date interval function
(4.2.1) Increase (decrease) a few years, months, days, hours, minutes, and seconds
-- ADDDATE or DATE_ADD (date, INTERVAL expr type)
-- SUBDATE or DATE_SUB (date, INTERVAL expr type)
-- The EXTRACT (type FROM date) function returns the "type" interval FROM the date.
Expr is an expression that specifies the interval value added to or subtracted from the start date. expr is a string;
It can start with a "-" to indicate the negative interval. Type is a keyword that specifies how the expression should be interpreted.
SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"
Example:
(1) SELECT DATE_ADD (NOW (), INTERVAL 60 SECOND); // The INTERVAL is 60 seconds.
(2) SELECT DATE_ADD (NOW (), INTERVAL "" MINUTE_SECOND); // INTERVAL of 2 minutes and 60 seconds
(3) SELECT DATE_SUB ("00:00:00", INTERVAL "-1 1 1" DAY_SECOND );
// The interval is one hour 1 minute 1 second. if the number of days is null, the value 0. expr can be added before "-" by default "-"
(4) select extract (HOUR_SECOND from now (); // The result is 102111, indicating 10: 21: 11 seconds.
-- PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return value in the format of YYYYMM. Note that the phase parameter P is not a date value.
Example: SELECT PERIOD_ADD (9801,2); // result 199803
-- PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.
Example: SELECT PERIOD_DIFF (9802,199703); // result 11
(4.2.2) date conversion
-- TO_DAYS (date) indicates a date, and returns a number of days (from 0 years ).
Example: SELECT TO_DAYS (950501); // result 728779
Example: SELECT TO_DAYS ('2017-10-07'); // result 1997
-- FROM_DAYS (N)
Returns a DATE value for the number of days N.
Example: SELECT FROM_DAYS (366); // result 0001-01-01
4.3 date formatting (conversion)
-- DATE_FORMAT (date, fmt) format the date value according to the specified fmt format.
-- TIME_FORMAT (time, format) is used to process format modifiers that contain hours, minutes, and seconds. Other modifiers generate a NULL value or 0.
% M month name (January ...... December)
% W name of the week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H Hour (00 ...... 23)
% K hour (0 ...... 23)
% H Hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM
% W days in a week (0 = Sunday ...... 6 = Saturday)
% U week (0 ...... 52). Sunday is the first day of the week.
% U week (0 ...... 52) Monday is the first day of the week.
% A text "% ".
4.4 extract date
-- DAYOFYEAR (date) returns the day (1 ~ 366)
Example: select dayofyear ('2017-07-27 '); // 2015
-- DAYOFMONTH (date) or DAY () returns the DAY of a month (1 ~ 31)
Example: select dayofmonth ('2017-07-27 '); // 27
-- DAYOFWEEK (date) returns the day (1 ~ 7)
Example: select dayofweek ('2017-07-27 '); // Monday 2 is the next day.
-- WEEKDAY (date) returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Example: select weekday ('2017-07-27 10:09:08 '); // 0 indicates Monday
-- DAYNAME (date) returns the name of the week of date.
Example: select dayname ('2017-07-27 10:09:08 '); // Monday
-- MONTHNAME (date) returns the month name of date.
Example: select monthname ('2017-07-27 10:09:08 '); // July
-- LAST_DAY (date)
Function usage: gets a date or date and time value, and returns the value corresponding to the last day of the month.
If the parameter is invalid, NULL is returned.
Example: SELECT LAST_DAY ('2017-02-1 10:09:08 '); // 2015
-- YEAR (date) returns the YEAR (1000 ~ 9999)
Example: select year ('2014-07-27 10:09:08 '); // 2015
-- QUARTER (date) returns the QUARTER (1 ~ 4)
Example: select quarter ('2014-07-27 10:09:08 '); // 3
-- MONTH (date) returns the MONTH value of date (1 ~ 12)
Example: select month ('2014-07-27 10:09:08 '); // 7
-- WEEK (date) returns the WEEK (0 ~ 52)
Example: select week ('2017-07-27 10:09:08 '); // 30
-- WEEK (date, first)
If Sunday is the first day of a week, a single parameter returns the number of weeks of the date,
The value range is 0 to 52. Two parameter formats: WEEK () allows you to specify whether the WEEK starts on Sunday or Monday.
If the second parameter is 0, the week starts from Sunday, and if the second parameter is 1, it starts from Monday.
Example: select week ('2017-07-27 10:09:08 ', 1); // 31
-- HOUR (time) returns the HOUR value of time (0 ~ 23)
Example: select hour ('2017-07-27 10:09:08 '); // 10
-- MINUTE (time) returns the MINUTE value of time (0 ~ 59)
Example: select minute ('2017-07-27 10:09:08 '); // 9
-- SECOND (time) returns the number of seconds of time, ranging from 0 to 59.
Example: select second ('2017-07-27 10:09:08 '); // 8
V. control flow functions
Case when [test1] THEN [result1]... ELSE [default] END if testN is true, resultN is returned; otherwise, default is returned.
CASE [test] WHEN [val1] THEN [result]... ELSE [default] END if test and valN are equal, resultN is returned; otherwise, default is returned.
IF (test, t, f) IF test is true, t is returned; otherwise, f is returned.
IFNULL (arg1, arg2) if arg1 is not empty, arg1 is returned; otherwise, arg2 is returned.
NULLIF (arg1, arg2) returns NULL if arg1 = arg2; otherwise, arg1 is returned.