Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this "MYSQL function use case analysis" article, so the editor summarizes the following, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "MYSQL function use case analysis" article.

MYSQL function 1: aggregate function

The aggregate function is mainly composed of: count,sum,min,max,avg,group_count ()

Let's focus on the group_count () function, which is first grouped according to the columns specified by group by, separated by delimiters, concatenates the values in the same group, and returns a string result.

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

Description:

1: duplicate values can be excluded by using distinct.

2: if you need to sort the result values, you can use the order by clause.

3:separator is a string value and defaults to 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 (rounded 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 (xPowery) returns the remainder of x divided by y

9:PI () returns pi (3.141593)

10:POW (xPowery) returns x to the y power.

11:RAND () returns a random number from 0 to 1

12:ROUND (x) returns the integer closest to x (rounded)

13:ROUND (xQuery y) returns the decimal number of specified digits (followed by rounding)

14:TRUNCATE (xQuery y) returns the value x reserved to y places after the decimal point (the biggest difference with ROUND is that it is not rounded)

2: string function

1:char_length (s) returns the number of characters of the string s

2:character_length returns the number of characters of the string s

3:concat (s _ 1 ~ 2 ~ 2 ~ 3) string, s _ 1 ~ 2 and other strings are merged into a single string.

4:concat_ws (xmeme s 1m s 2.) It is the same as the concat (s _ 1 ~ S _ 2 ~ S _ 3) function, but each string is separated by x _ x.

5:field returns the first string s in the string list (s1Persons2.) Position in

6:length () returns the number of bytes of utf-8 encoding in mysql. A Chinese character is three bytes.

7:ltrim (s) remove the space at the beginning of the string s remove the space on the left rtrim () remove the space on the right trim () remove the spaces on both sides

8:mid truncates a substring of length len from the n position of the string s with substring

9:position (S1 _ divine _ in _ line _ s) gets the starting position of S1 from the string s

10:replcae substitutes string S2 for string S1 in string s

11:reverse (s) reverses the order of the string s

12:right (sQuery n) returns the last n characters of the string s (n characters from the right)

13:strcmp (S1 ~ S2) compares the strings S1 and S2, returns 0 if S1 is equal to S2, and returns 1 if S1 > S2 returns-1 if S1 is less than S2

14:substr truncates a substring of length length from the start position of the string s

15:ucase (s) upper (s) converts a string to uppercase

16:lcase (s) lower (s) converts a 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) converts the specified date to a millisecond value timestamp

3:from_unixtime (bigint unixtime,string-format) converts the millisecond value timestamp to 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) calculates the number of days between D1 and D2 (eg:datediff) ('2022-01-01-01')

8:currtime () returns the current time

9:date_format (dline f) displays the date d as required by the expression f

4: control flow function

1:if (expr,v1,v2) returns the result v1 if the expression expr holds true, otherwise returns the result v2

2:ifnull (v1 null v2) returns v1 if the value of v1 is v1, otherwise returns v2

3:isnull (expression) determines whether the expression is null

4:nullif (expr1,expr2) compare 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 indicates the beginning of the case function, end indicates the end of the function, result1 is returned if condition1 is established, result2 is returned if condition2 is established, result is returned when none is established, and the latter is not executed when one is established.

5: window function

The window function added by mysql8.0 is also known as the window function. Compared with the aggregate function, the aggregate function returns a single value (that is, grouping) after calculating a group of data, and the non-aggregate function only processes one row of data at a time. When the window aggregate function calculates the result of a field on the row record, it can input the data within the window range into the aggregate function without changing the number of rows.

5.1 ordinal function

You can sort groups and add serial numbers

1: row_number ()

2: rank ()

3: dense_rank ()

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

Note: no partition by means global sorting

5.2 Distribution function

1: percent_rank ()

Purpose: each row is calculated according to the formula (rank-1) / (row-1). Where rank is the sequence number generated by the rank () function, and row is the total number of record rows in the current window.

2: cume_dist ()

Purpose: the number of rows less than or equal to the current rank value in the group / the total number of rows in the group

Application scenario: query the proportion less than or equal to the current salary

Written as: select dname,ename,salary,cume_dist () over (order by salary) as rn1

Cume_dist () over (partition by dname order by salary) as rn2 from employe

5.3 before and after function

1: lag (expr,n)

2: lead (expr,n)

Purpose: returns the value of expr located in the first n rows (lag (exor,n)) or the last n rows (lead (expr,n)) of the current row

Application scenario: query the difference between the score of the first student and that of the current classmate (the current row can have a field value of the previous row of data)

5.4 header-tail function

1: first_value (expr)

2: last_value (expr)

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

Application scenario: as of now, query the salary of the first and last employee by date

5.5 other functions

1: nth_value (expr,n)

2: ntile (n)

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

Application scenario: displays the second or third salary in each employee's salary as of the current salary

5.6 windowed aggregate function

1: sum ()

2: avg ()

3: min ()

4: max ()

Written in: 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 salary of the current row and each previous row.

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

The above is about "MYSQL function use case analysis" of this article, I believe we all have a certain understanding, I hope the editor to share the content to help you, if you want to know more related knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.