MYSQL function usage example analysis

MYSQL function

1: Aggregation function

Aggregation function mainly consists of: count,sum,min,max,avg,group_count()

Focus on group_count( ) function, first group according to the column specified by group by, and separate it with a delimiter, connect the values ​​​​in the same group, and return a string result.

Format: group_count([distinct ]Field name [order by sorting field asc/desc] [separator 'separator'])

Description:

1: Use distinct to exclude duplicate values.
2 : If you need to sort the result values, you can use the order by clause.
3:separator is a string value, the default is a comma.

2: Mathematical function

1:ABS(x) returns the absolute value of x
2:CEIL(x) returns the smallest integer greater than or equal to x (rounded up)
3:FLOOR(x) returns the largest integer less than or equal to x( Round down)
4:GREATEST(expr1,expr2...) returns the maximum value in the list
5:LEAST(expr1,expr2....) returns the minimum value of the list
6 :MAX(x) Returns the maximum value of field x
7:MIN(x) Returns the minimum value of field x
8:MOD(x,y) Returns the remainder after dividing x by y
9: PI() returns pi (3.141593)
10:POW(x,y) returns x raised to the power of y
11:RAND() returns a random number from 0 to 1
12:ROUND(x) returns The nearest integer to x (following rounding)
13:ROUND(x,y) Returns the specified number of decimal places (following rounding)
14:TRUNCATE(x,y) Returns the value x to y decimal places value, (the biggest difference from ROUND is that it will not be rounded)

2: String function

1: char_length(s) returns the string s Number of characters
2:character_length Returns the number of characters of string s
3:concat(s1,s2,s3) Strings s1, s2 and other strings are combined into one string
4:concat_ws( x,s1,s2..) Same as concat(s1,s2,s3) function, but x is added between each string, x can be a separator
5:field(s,s1,s2) return The position of the first string s in the string list (s1, s2..)
6:length() returns the number of bytes. The encoding of utf-8 in mysql is three bytes for a Chinese character
7:ltrim(s) removes the spaces at the beginning of the string s and removes the spaces on the left. rtrim() removes the spaces on the right. trim() removes the spaces on both sides.
8: mid(s,n,len) from the string Intercepting a substring of length len at position n of s is the same as substring(s,n,len)
9:position(s1,in,s) Get the starting position of s1 from string s
10:replcae (s,s1,s2) Replace string s2 with string s1 in string s
11:reverse(s) Reverse the order of string s
12:right(s,n)Return characters The last n characters of string s (n characters taken from the right)
13:strcmp(s1,s2) Compares strings s1 and s2. If s1 and s2 are equal, 0 is returned. If s1>s2, 1 is returned. If s1 is less than s2 returns -1
14:substr(s,start,length) intercepts a substring of length length from the start position of string s
15:ucase(s) upper(s)converts the string to Uppercase
16:lcase(s) lower(s) Convert the string to lowercase

3:Date function

1:unix_timestamp() returns 1970- 01-01 00:00:00 to the current millisecond value
2:unix_timestamp(date_string) Convert the specified date to a millisecond value timestamp
3:from_unixtime(bigint unixtime,string-format) Convert the millisecond value timestamp For the specified format date
4:curdate() returns the current date
5:current_date() returns the current date
6:current_timestamp() returns the current date and time
7:datediff(d1,d2) Calculate the number of days between dates d1>d2 eg:datediff('2022-01-01','2022-02-01')
8:currtime() Return the current time
9:date_format(d, f) Display date d according to the requirements of expression f

4: Control flow function

1:if(expr,v1,v2) If the expression expr is true, return Result v1, otherwise return result v2
2:ifnull(v1,v2) If the value of v1 is null, return v1, otherwise return v2
3:isnull(expression) Determine whether the expression is null
4 :nullif(expr1,expr2) Compares two strings. If the strings expr1 and expr2 are equal, return null, otherwise return expr1
5:case expression when condition1 then result1 when condition2 then result2 else result end represents the start of the case function, and end represents the function End, if condition1 is established, return result1, if condition2 is established, return result2, if all are not established, return result, and when one is established, the following will not be executed.

5: Window function

The window function newly added in mysql8.0 is also called the window function. The non-aggregation window function is relative to the aggregate function. The aggregate function returns a single value (that is, grouping) after calculating a set of data. Non-aggregate functions will only process one row of data at a time. When the window aggregate function calculates the result of a certain field on a row record, the data within the window range can be input into the aggregate function without changing the number of rows

5.1 The serial number function

can realize group sorting and add the serial number

1: row_number()
2: rank()
3: dense_rank()

Writing: select id,...,dense_rank() over(partition by dname order by salary desc) as rn from employee;

Note: Do not add partition by Indicates global sorting

5.2 Distribution function

1: percent_rank()
Purpose: Each row is based on the formula (rank-1)/(row- 1) Calculate. Rank is the sequence number generated by the rank() function, and row is the total number of rows of records in the current window.
2: cume_dist()
Purpose: Within the group, it is less than or equal to the current rank Number of rows of value/total number of rows in the group
Application scenario: Query the proportion that is less than or equal to the current salary

Writing method: select dname,ename,salary,cume_dist() over(order by salary) as rn1,
cume_dist() over(partition by dname order by salary) as rn2 from employee;

##5.3 Before and after functions

1: lag(expr,n)

2: lead(expr,n)

Purpose: Return the n lines before or after the current line (lag(exor,n)) The value of expr in row n (lead(expr,n))

Application scenario: Query the difference between the score of the first student and the score of the current student (there can be a field of the previous row of data in the current row) value)

5.4 Head and tail functions

1: first_value(expr)

2: last_value(expr)

Purpose: Return the value of the first (first_value(expr)) or the last (last_value(expr)) expr

Application scenario: As of now, sort by date Query the salary of the first and last employee

5.5 Other functions

1: nth_value(expr,n)

2: ntile(n)

Purpose: Return the value of the nth expr in the window. expr can be an expression or a column name

Application scenario: As of the current salary, display The second or third salary of each employee

##5.6 Windowed aggregate function

1: sum()

2: avg()

3: min()
4: max()

Writing: select id,.. .,sum(salary) over(partition by dname order by hiredate desc) as rn from employee;

The data of each row of rn is the sum of the current row and the salary of each previous row

If If there is no order by sorting statement, all data in the group will be summed by default

The above is the detailed content of MYSQL function usage example analysis. For more information, please follow other related articles on the PHP Chinese website!