【 favorites to eat grey series 】MySQL common functions detailed summary

preface

This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021. MySQL common functions not skilled? Just read my article!!

▶ common MySQL functions overview

★ String function

LENGTH(str)

Master index: ★★★★

Function description:

Return STR string length in bytes

Note:

A character in English is 1 byte
GBK encoded is 2 bytes
Utf-8 encodes Chinese characters in 3 bytes

Example SQL statement:

The length of the string is in bytesselect length('HUALEI'); # 6

select length('Hello.'); # 9
Copy the code

INSERT(str,pos,len,newstr)

★★★

Function description:

Insert a newStr string of length len starting at position pos in STR

Example SQL statement:

If I want to give STR = ‘HUALEI’ and want to splice it into “HUALEI is a hansome boy “with insert(), what do I do?


select insert('HUALEI', length('HUALEI')+1, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI

or

select insert('HUALEI', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI

but answer:

select insert('HUALEI ', length('HUALEI')+2, length('is a hansome boy.'), 'is a hansome boy.'); # HUALEI is a hansome boy.
Copy the code

MySQL > insert pos <= length(STR);

If it is a Chinese character string, how to make “blue and white porcelain” into “blue and white porcelain”, “Socrates Square” into “Socrates square dance”?

    
select insert('Blue and white Porcelain'.3, length('PFM'), 'PFM'); # Blue and white porcelainselect insert('Socrates Square'.7, length('dance'), 'dance'); # Socrates Square DanceCopy the code

In the first SQL, pos => 3 is the index of the Chinese character string. From the third Chinese character, insert ‘length’ (‘ porcelain ‘). SQL > insert string (‘ Socratic square ‘) => 18;

LEFT(str,len)

★★★

Function description:

Intercepts a string of length len from the left of the string STR

Example SQL statement:


select left('HUALEI'.3); # HUA
Copy the code

Then the character string “hello everyone” I want to cut left “everyone”, how to do?


select left('Hello.', length('you')); # hiCopy the code

Strangely, the intercept results are not as expected. Why?

Note that len is again obtained not by length() but by char_length(), starting from 1 to len.

So, the correct way to write a Chinese string is:

Select left(' hello ', 2); # you

RIGHT(str,len)

★★★

Function description:

Intercepts a string of length len from the right of the string STR

Example SQL statement:


select right('HUALEI'.3); # LEI

select right('Hello.'.1); # goodCopy the code

No need to repeat the same reasoning as LEFT().

SUBSTR(str FROM pos) <=> SUBSTR(str,pos) SUBSTR(str FROM pos FOR len) <=> SUBSTR(str,pos,len)

Master index: ★★★★

Function description:

A substring of length len is truncated starting at the pos position of STR and ending if there is no len argument

Example SQL statement:


select substr('abcdefg' from 2); # bcdefg # is equivalent toselect substr('abcdefg'.2); # bcdefg

select substr('abcd' from 1 for 3); # ABC # is equivalent toselect substr('abcd'.1.3); # ABC # where len is taken from posselect substr('Hello.'.2.1); #,Copy the code

STRCMP(expr1,expr2)

★★★

Function description:

Expr1 > expr2 => return 1; Expr2 => return 0; Expr1 < expr2 => Returns -1

Example SQL statement:


select strcmp('bbcd'.'bacd'); # returns the character in the corresponding position compared to the corresponding character1

select strcmp('hello'.'hello'); # the two strings are identical, return0

select strcmp('ABC'.'abc'); # case insensitive, return0

select strcmp('abadf'.'abadfe'); # back big, return- 1
Copy the code

CONCAT(str1,str2,…)

Master index: ★★★★

Function description:

Str1, STR2… And so on to concatenate into a new string

Example SQL statement:


select concat('hel'.'llo'); # hello

select concat('you'.'good'); # hiCopy the code

Note: As long as a null value exists in the connection string, the final result will be null as well.


select concat(null.'abc'); # null

select concat('abc'.null);  # null
Copy the code

LOCATE(substr,str) | POSITION(substr IN str) | INSTR(str,substr)

Master index: ★★★★

Function description:

Return the starting position of substr in the parent string STR, or 0 if the parent string contains no substr at all

Example SQL statement:


select locate('LEI'.'HUALEI'); # substring'LEI'In the parent list'HUALEI'The starting position of the4
select locate('LEI '.'HUALEI'); # substring does not exist, return0

select position('LEI' in 'HUALEI'); # substring'LEI'In the parent list'HUALEI'The starting position of the4
select position('LEI ' in 'HUALEI'); # substring does not exist, return0

select instr('HUALEI'.'LEI'); # 4

select instr('HUALEI'.'LEI '); # 0
Copy the code

Summary:

All three functions take the starting position of the child in the parent string
The locate() and position() functions are similar except that the argument list is different. The latter uses in to indicate the position of the child within the parent string, which is easier to understand and more recommended
The only difference between instr() and locate() is that the parameter positions are swapped

LOWER(str) | UPPER(str)

★★★

Function description:

LOWER/UPPER all STR strings

Example SQL statement:

# lowercaseselect lower('HUALEI'); # hualei # Capital wordsselect upper('hualei'); # HUALEI
Copy the code

Note: This parameter is valid only for English strings, not Chinese strings.


select lower('Hi, I'm HUALEI.'); # Hi, I'm HualeiCopy the code

LTRIM(str) | RTRIM(str) | TRIM([remstr FROM] str)

Master index: ★★★★

Function description:

Removes whitespace from STR string

Example SQL statement:


select ltrim(' HUALEI'); # HUALEI

select rtim('Hello everyone! '); # Hello!select trim(' HUALEI '); # HUALEI
Copy the code

Note: the trim() function only removes whitespace before and after the STR string, not all whitespace!


select trim(' HUA LEI '); # HUA LEI
Copy the code

REPEAT(str,count)

Master index: ★★

Function description:

Returns the result of STR repeating count times

Example SQL statement:


select repeat('HUALEI '.5); # HUALEI HUALEI HUALEI HUALEI HUALEI 

select repeat('Thunder Monkey'.2); # Thunder monkey thunder monkeyCopy the code

REVERSE(str)

Master index: ★★

Function description:

Invert the string STR in reverse order

Example SQL statement:


select reverse('I got turned around.'); # Have come to be meselect reverse('HUALEI'); # IELAUH
Copy the code

RPAD(str,len,padstr) | LPAD(str,len,padstr)

★★★

Function description:

Specifies the string length len, len > length(STR) is not enough to fill right/left with padstr; Len < length(STR) if sufficient, the length is truncated according to the specified length.

Example SQL statement:


select rpad('you'.3.'good'); # hiselect rpad('HUALEI', length('HUALEI')+length(' NB'), ' NB'); # HUALEI NB

select length('Hello.'); # 9

select lpad('SQL'.3.'My'); # SQL

select lpad('SQL', length('MySQL'), 'My'); # MySQL
Copy the code

★ Mathematical function

FORMAT(X,D) | ROUND(X) | ROUND(X,D)

Master index: ★★★★

Function description:

Round X to keep D decimal places

Example SQL statement:


select format(3.1415926.3); # 3.142# is equivalent toselect ROUND(3.1415926.3); # 3.142If there are no reserved digits, the integer is roundedselect round(3.1415926); # 3
Copy the code

CEIL(X) | FLOOR(X)

Master index: ★★★★

Function description:

Ceil ceiling (to a greater value direction) rounded; Floor The floor (to a smaller value) is rounded

Example SQL statement:

# round upselect ceil(3.5); # 4
select ceil(3.5); # - 3# round downselect floor(3.5); # 3
select floor(3.5); # 4 -
Copy the code

MOD(N,M)

★★★

Function description:

You take the remainder of N over M, which is the same thing as N % M

Example SQL statement:


select mod(10.3); # 1# is equivalent toselect 10 % 3; # 1
Copy the code

POW(X,Y) | POWER(X,Y)

★★★

Function description:

Return X to the Y

Example SQL statement:


select pow(2.10); # 2^10 = 1024# You can also writeselect power(2.10); # 2^10 = 1024
Copy the code

SQRT(X)

★★★

Function description:

Returns the square root of X, which is the square root of X

Example SQL statement:


select sqrt(100); # 10

select sqrt(2); # the square root of2 => 1.4142135623730951
Copy the code

GREATEST (expr1, expr2 expr3,…). | further (expr1, expr2 expr3,…).

★★★

Function description:

Returns the maximum/minimum value in the parameter list

Note: Argument lists can be character sequences.

Example SQL statement:


select greatest(1.2.3.4.51.6.7.8); # 51

select greatest('Java'.'MySQL'.'JavaScript'); # MySQL

select least(- 1.2.3.4.5.6.7.8); # - 1

select least('Java'.'MySQL'.'JavaScript'); # Java
Copy the code

RAND()

Master index: ★★★★★

Function description:

Returns a random number between (0, 1)

Example SQL statement:


select rand(CURRENT_TIMESTAMP); Given the seed value, the current timestamp is used as the value, which is guaranteed to be relatively randomselect format(rand()*100.0); # (0.100) between random integersselect round(rand()*100); # (0.100) between random integersselect.order byrand() limit N; Select * from N recordsCopy the code

The aggregation function MAX (expr) | MIN (expr) | SUM (expr) | COUNT (expr) | AVG ([DISTINCT] expr)

Master index: ★★★★★

Function description:

Aggregate functions, used in conjunction with group by, are used to find maximum and minimum values/sum/count/average values

Example SQL statement:

# Query the highest paid male colleagueselect max(salary) from emp where sex = 'male'; # Query the lowest paid female colleagueselect min(salary) from emp where sex = 'woman'; Select * from student where name = 'wang'select sumSscore Indicates the total score of a student surnamed Wangfrom score where sid in (select sid from student stu where stu.sname like 'the king %') group bysid; # number of female colleaguesselect count(id) Number of female colleaguesfrom emp group by sex having sex = 'woman'; # Query the average salaryselect avgThe average salaryfrom emp;
Copy the code

Note:

Aggregate functions ignore null values unless otherwise specified
If you use an aggregate function in a statement that does not contain the group by clause, it is equivalent to grouping all rows, resulting in one row
Time type values are not valid for sum() and avg()! They replace it with a number, discarding all information after the first non-numeric character

In addition, aggregate functions can pass in independent expressions as arguments:

# query the score in80The number of people with score or aboveselect count(1) from score where sscore > = 80; # is equivalent toselect count(if(sscore > = 80.1.null)) fromscore; # query the score in80Score and aboveselect sum(sscore) from score where sscore > = 80; # is equivalent toselect sum(if(sscore> =80, sscore, null)) fromscore; # query the score in80Average score or aboveselect avg(sscore) from score where sscore > = 80; # is equivalent toselect avg(if(sscore> =80, sscore, null)) from score;
Copy the code

★ Date function

CURDATE() <=> CURRENT_DATE

Master index: ★★★★

Function description:

Returns the current date in the format YYYY-MM-DD

Example SQL statement:


select curdate(); # 2021- 1107 -

select current_date; # 2021- 1107 -
Copy the code

CURTIME() <=> CURRENT_TIME

Master index: ★★★★

Function description:

Returns the current time in the format of HH:mm:ss

Example SQL statement:


select curtime(); # 10:31:23

select current_time; # 10:31:23
Copy the code

NOW() <=> CURRENT_TIMESTAMP

Master index: ★★★★

Function description:

Returns the current date and time in the format yyyY-MM-DD HH: MM :ss

Example SQL statement:


select now(); # 2021- 1107 - 10:31:46

select current_timestamp; # 2021- 1107 - 10:31:46
Copy the code

DAY(date) | DAYOFWEEK(date) | DAYOFMONTH(date) | DAYOFYEAR(date)

Master index: ★★★★

Function description:

Returns dd/the day of the week/month/year in date

Example SQL statement:

# removedateIn the dayselect day('2021-11-07'); # 7# Days of the weekselectdayofweek(now()); # Sunday->return1# The day of the monthselect dayofmonth(now()); # 7# Days of the yearselect dayofyear(now()); # 311
Copy the code

WEEK(date[,mode]) | WEEKOFYEAR(date)

★★★

Function description:

Mode	First day of week
0	`Sunday => 1`
1	`Monday => 1`

Use mode to specify Whether Sunday is the first day of the week or Monday, and then determine what week date is based on this criterion. WEEKOFYEAR() always starts on Monday, so mode is always set to 1

Example SQL statement:

# default, mode=> 0On Sunday=> 1
select week('2021-11-07'); # 45
select week('2021-11-07'.0); # 45# Monday=> 1
select week('2021-11-07'.1); # 44# is equivalent toselect weekofyear('2021-11-07'); # 44
Copy the code

MONTH(date) | QUARTER(date)

★★★

Function description:

Returns the month/quarter in date

Example SQL statement:


select month(now()); # 11# Q1 (1.2.3) Second quarter (4.5.6) Third quarter (7.8.9) Fourth quarter (10.11.12)select quarter(curdate()); # 4
Copy the code

YEAR(date) | YEARWEEK(date,mode)

Master index: ★★★★

Function description:

Returns the year/year + week in date

Example SQL statement:


select year(curdate()); # 2021# the default mode= 0On Sunday=> 1
select yearweek('2021-11-07'); # 202145# is equivalent toselect YEARWEEK('2021-11-07'.0); # 202145# Monday=> 1
select YEARWEEK('2021-11-07'.1); # 202144
Copy the code

DAYNAME(date) | MONTHNAME(date)

Master index: ★★

Function description:

Returns the English name of the day/month

Example SQL statement:


select dayname('2021-11-07'); # Sundayselect monthname('2021-11-07'); # November 11 月
Copy the code

STR_TO_DATE(str,format) | DATE_FORMAT(date,format)

Master index: ★★★★★

Function description:

Convert date to a date, depending on the format of the date string. Conversely, you can convert date to a string of the specified format

Example SQL statement:

# string transferdatetypeselect str_to_date('November 07, 2021'.'%Y年%m月%d日'); # 2021- 1107 -
select str_to_date('November 07, 2021 12:28:34'.'%Y year %m month % D day %H point % I minute %s second '); # 2021- 1107 - 12:28:34

# dateGoes to a string of the specified formatselect date_format(now(), '%Y year %m month % D day %H point % I minute %s second '); # 2021years11month07day11point29points56secondsCopy the code

DATEDIFF(expr1,expr2)

Master index: ★★★★★

Function description:

Returns the number of days separated by two dates

Example SQL statement:


select concat(datediff(curdate(), '2021-01-01'), 'day') as 'It has passed since the beginning of the New Year.'; # 312dayselect concat(datediff(str_to_date(concat(year(now()), 12/31 '/'), '%Y/%m/%d'), now()), 'day') as 'Only until the end of the year'; # 52dayCopy the code

DATE_ADD(date,INTERVAL expr unit) | DATE_SUB(date,INTERVAL expr unit)

Master index: ★★★★★

Function description:

Add and subtract date

Example SQL statement:


select ceil(rand()*31); # (0.31] # add the current timestamp to (0.31Randomness in the intervalDAYNumber of daysselect date_add(CURRENT_TIMESTAMP.interval ( ceil(rand()*31))DAY );

select ceil(rand()*4); # [1.4] # add the current timestamp to [1.4] Indicates the number of random weeks in the intervalselect date_add(now(), interval ( ceil(rand()*4) ) WEEK ); # Conversely, to specifydateSubtract the current timestamp from [0.10Randomness in the intervalYEARNumber of yearsselect date_sub(CURRENT_TIMESTAMP.interval ( round(rand(CURRENT_TIME)*11))YEAR );
Copy the code

TO_DAYS(date) | FROM_DAYS(N)

Master index: ★★

Function description:

Given a date, return the number of days from AD 0 to date

Fact: There is no year 0 AD in history, but year 0 is the basis for the alignment of AD digits

Example SQL statement:


select to_days('2021-11-07'); # 738466To_days () contains the day, datediff() does notselect datediff('2021-11-07'.'0-01-01'); # 738465The inverse of # to_days(), given a date from AD0The number of days at the beginning of the year, return onedate
select from_days(737515); # 20194 -- 1
select from_days(to_days('2021-11-07')); # 2021- 1107 -
Copy the code

Dynamic process control function

IF(expr1,expr2,expr3)

Master index: ★★★★

Function description:

Check whether the expr1 expression is true or false. If true, exPR2 is returned, otherwise expr3 is returned

Example SQL statement:


select if(10 > 5.'10 more'.'5 more'); # 10A biggerCopy the code

IFNULL(expr1,expr2) | NULLIF(expr1,expr2)

★★★

Function description:

IFNULL is used to check whether expr1 is null. If not, expr2 is returned; otherwise, expr1 is returned. NULLIF is used to check whether exPR1 and 2 are equal. If they are equal, null is returned; otherwise, exPR1 is returned

Example SQL statement:


# expr1 is not null.return expr1
select ifnull('exp1 is not null'.null); # exp1 is not null
# expr1 is null.return expr2
select ifnull(null.'exp1 is null'); # exp1 is null

select nullif('HUALEI'.'hualei'); # return equalnull# equivalent to (case insensitive)select nullif('HUALEI'.'HUALEI'); # return equalnull

# expr1 ! = expr2
select nullif('HUALEI'.null); # HUALEI
Copy the code

Note: expr1! = null, otherwise null is returned.

select nullif(null.'HUALEI'); # null
Copy the code

IF … ELSE statements

Master index: ★★★★

Function description:

It’s written differently than IF()

Grammar:


IF search_condition THEN
    statement_list
ELSE
    statement_list
END IF;
Copy the code

SWITCH … A CASE statement

Master index: ★★★★

Function description:

The switch statement

Grammar:


CASE case_value
WHEN when_value THEN
  statement_list
ELSE
  statement_list
END;
Copy the code

The Message digest function

PASSWORD(str)

★★★

Function description:

Evaluates and returns the password string

Example SQL statement:


select PASSWORD('abc') # *0D3CED9BEC10A777AEC23CCC353A8C08A633045E

select PASSWORD('ABC') # *71B101096C51D03995285042443F5C44D59C8A31
Copy the code

Note: This function was removed in MySQL8.0.11.

MD5(str)

Master index: ★★★★

Function description:

Calculates the MD5 sum verification code

Example SQL statement:


select MD5("abc"); # 900150983cd24fb0d6963f7d28e17f72
Copy the code

SHA(str) | SHA1(str)

Master index: ★★★★

Function description:

Calculate the SHA/SHA1 sum verification code

Example SQL statement:


select SHA('abc') # # a9993e364706816aba3e25717850c26c9cd0d89d equivalent toselect SHA1('abc') # a9993e364706816aba3e25717850c26c9cd0d89d
Copy the code

Dynamic symmetric encryption function

ENCODE(str,pass_str) | DECODE(crypt_str,pass_str)

Master index: ★★★★

Function description:

Encryption via public key (Encode)/decryption (Decode Decode)

Example SQL statement:


# 'password'Encrypts string information as a public key'HUALEI'
select encode('HUALEI'.'password'); # ��e # Pass the encrypted ciphertext through the public key'password'Decrypt to get the plaintext before encryptionselect decode(encode('HUALEI'.'password'), 'password'); # HUALEI
Copy the code

AES_ENCRYPT(str,key_str) | AES_DECRYPT(crypt_str,key_str) | DES_ENCRYPT(str[,{key_num|key_str}]) | DES_DECRYPT(crypt_str[,key_str])

Master index: ★★★★

Function description:

The Advanced Encryption Standard (AES) and Data Encryption Standard (DES) algorithms are used to encrypt information symmetrically

Example SQL statement:

# AES algorithm encryption, public key is'salt'
select aes_encrypt('HUALEI'.'salt'); # � � b ɫ D*�ճ�ϐe� # AES decryption algorithmselect aes_decrypt(aes_encrypt('HUALEI'.'salt'), 'salt'); # ABC # DES algorithm encryption, public key is'password'
select des_encrypt('HUALEI'.'password'); #� �� # DES decryption algorithmselect des_decrypt(des_encrypt('HUALEI'.'password'), 'password'); # HUALEI
Copy the code

Dynamic system information function

VERSION()

Master index: ★★★★

Function description:

Return the current MySQL version number

Example SQL statement:


select version(); # 5.731.-log
Copy the code

USER() | CURRENT_USER

Master index: ★★★★

Function description:

Returns the current user role

Example SQL statement:


select user(a); # root@localhost# is equivalent toselect current_user; # root@localhost
Copy the code

DATABASE()

Master index: ★★★★

Function description:

Returns the name of the current database

Example SQL statement:


select database(); # mysql
Copy the code

CONNECTION_ID()

★★★

Function description:

Returns the number of current user connections

Example SQL statement:


select connect_id(); # 38
Copy the code

Function overview

All the above functions are summarized into a mind map, which is very intuitive for everyone to learn and refer to:

At the end

Writing is not easy, welcome everyone to like, comment, your attention, like is my unremitting power, thank you to see here! Peace and Love.