Introduction to the usage of common functions of MySQL

This article mainly introduces "introduction to the usage of common functions of MySQL". In daily operation, I believe many people have doubts about the introduction of common functions of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "introduction to the usage of common functions of MySQL". Next, please follow the editor to study!

Concept

Equivalent to a method in java, encapsulating a set of logical statements in the method body and exposing the method name to the outside

Hides the implementation details

Improve the reusability of code

Use

The select function name (argument list) [from table] [] can be omitted

Text

Character function:

Length: get the number of bytes (3 bytes for utf-8 and 2 bytes for gbk)

SELECT LENGTH ('cbuc') # output 4SELECT LENGTH (' Cai Bucai cbuc') # output 13

Concat: concatenating strings

SELECT CONCAT ('Coulomb and BUC') # output C_BUC

Upper: make letters uppercase

SELECT UPPER ('cbuc') # output CBUC

Lower: lowercase letters

SELECT LOWER ('CBUC') # output cbuc

Substr / substring: clipping strings this method is refactored

Substr (str,pos) # str: string to be clipped, pos: length to be clipped substr (str,pos,len) # str: string to be cropped, pos/len: where to start clipping several bits of # substring

Instr: returns the index of the first occurrence of the substring, or 0 if not

SELECT INSTR ('Cai Bucai', 'Cai') # output 1 (mysql counts from 1)

Trim: string to [character]

SELECT TRIM ('cbuc') # output cbuc ELECT TRIM ('a 'from' aaaacbucaaaa') # output cbuc

Lpad: fill the specified length with the left with the specified character

SELECT LPAD ('cbuc',6,'*') # output * * cbuc

Rpad: right fill specified length with specified characters

SELECT LPAD ('cbuc',6,'*') # output cbuc**

Replace replacement

SELECT REPLACE ('small dishes love to sleep', 'sleep', 'eat') # output small dishes love to eat

Mathematical function

Round: rounded

SELECT round (1.5) # output 2 ELECT round (- 1.5) # output-2 the rounding is calculated as: absolute values are rounded plus a minus sign

Ceil: round up and return the smallest integer > = the parameter

SELECT CEIL (1.5); # output 2SELECT CEIL (- 1.5); # output-1

Floor: round down and return