Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the advanced query function of MySQL is, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

MySQL advanced query function: 1, string function; 2, numeric function, [CEIL (x)] returns the minimum integer value of not less than X; 3, date function, [DATE_ADD/DATE_SUB] and so on.

MySQL advanced query function:

Classification of functions:

1, one-line function: calculate the input value of each record, get the corresponding calculation result, and return it to the user, that is to say, each record as an input parameter, the calculation result of each record is calculated by the function.

2, multi-line function: calculate the input values of multiple records and get a single result corresponding to multiple records.

One-line function:

①: string function (the user handles single-line character data, such as case conversion, string truncation, assembly, etc.)

A.LOWER/UPPER (LOWER (str): returns the string str becomes lowercase .upper (str): returns the string str becomes uppercase)

SELECT UPPER (name) FROM student; / / all uppercase SELECT LOWER (name) FROM student; / / all lowercase

B.CONCAT: CONCAT (str1,str2,...):

1, returns the string generated as a result of the connection parameter.

2. If any parameter is NULL, the return value is NULL

3, one or more parameters are allowed

SELECT name,age, CONCAT (name,'-',age) FROM student

C.INSERT: replaces the specified (location, length) substring with the target string

Format: INSERT (str,pos,len,newstr)

Parameter: str: (source string) pos: (position to start insertion, index starts at 1) len: (length of replacement string) newstr: (string to be inserted)

1, returns the string str, whose substring starts at the len character whose position and length is replaced by the string newstr.

2, if the pos exceeds the string length, the return value is the original string.

3, if the length of the len is greater than the length of other strings, the substitution starts at the position pos.

4. If any parameter is null, the return value is NULL

Example:

Replace part of the characters of the user name, the rules are as follows: keep the first 2 bits of the user name, replace the middle 3 bits with *, and if the name has extra characters, keep it.

SELECT INSERT (name,2,3,'***') FROM student

D. ① LENGTH: the number of bytes occupied by the string

SELECT LENGTH (name) FROM student

② CHAR_LENGTH: calculating the number of characters

SELECT CHAR_LENGTH (name) FROM student

E:LPAD/RPAD: if the number of characters in a string is greater than the given number, if it is less, the specified number is made up from the specified edge of the function, and if more is truncated from the end of the string

LPAD (str,len,padstr): left fill

1, returns the string str, whose left side is filled by the string padstr to a total length of len.

2, if the length of str is greater than len, the return value is shortened to len characters.

SELECT LPAD (NAME,10,'*') FROM student

The results show:

RPAD (str,len,padstr): right fill

1, returns the string str, whose right side is filled to the len character length by the string padstr.

2, if the length of the string str is greater than len, the return value is shortened to the same length as the len character.

SELECT RPAD (NAME,10,'*') FROM student

F:TRIM/LTRIM/RTRIM

LTRIM (str): the left space is dropped by trim

RTRIM (str): the space on the right is dropped by trim

TRIM (str) = LTRIM+RTRIM

TRIM ([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

Advanced usage to intercept remstr from str in a specified manner

TRIM (remstr FROM] str): equivalent to TRIM (BOTH remstr FROM str)

SELECT TRIM (name), CHAR_LENGTH (TRIM (name)), CHAR_LENGTH (name) FROM student;# remove the specified substring SELECT TRIM ('ja' FROM name) FROM student; from both ends of the string | (equivalent to) SELECT TRIM (BOTH' ja' FROM name) FROM student;# to turn around SELECT TRIM (LEADING 'ja' FROM name) FROM student;# to remove the tail SELECT TRIM (TRAILING' ja' FROM name) FROM student

G:REPLACE

REPLACE (str,from_str,to_str):

1, replace all from_str with to_str in str

2, case sensitive

# selective replacement # when the field value in the record of an entry is equal to the value of the second parameter # replace this field value with three parameters SELECT REPLACE (name,'rose','niceMan') FROM student

H:SUBSTRING (str,pos):

Returns a substring from the string str, starting with the position pos

SUBSTRING (str,pos,len):

Returns a substring of the same length as the len character from the string str, starting at the position pos

If pos is negative, calculate from the end of the string

# starting from the specified location, intercepting to the last SELECT SUBSTR (name,2) FROM student;# intercepting the specified length substring SELECT SUBSTR (name,2,3) FROM student from the specified location

②: numeric function

A.ABS/MOD ABS (x): returns the absolute value of a number

MOD (NMague M): returns the remainder of N divided by M (modulo)

SELECT ABS (- 13); / / take the absolute value SELECT MOD (10Magne3); / / take the module

B.CELT/FLOOR/ROUND/TRUNCATE

CEIL (x): returns the minimum integer value of not less than X

SELECT CEIL (3.5); result 4

FLOOR (x): returns the maximum integer value not greater than X

SELECT FLOOR (3.4); returns 3

ROUND (X): rounding of integers

SELECT ROUND (3.2); returns 3

ROUND (XBI D):

1, returns the parameter X, whose value is close to the nearest integer.

2, in the case of two parameters, return X, whose value is retained to D digits after the decimal point, while D bits are rounded.

3. To keep the D digit to the left of the X value decimal point, set D to a negative value.

SELECT ROUND (3.2228pc2); return 3.22

TRUNCATE (XBI D)

1, returns the number X that is rounded to D digits after the decimal point.

2, if the value of D is 0, the result has no decimal point or decimal part. You can set D to a negative number, if you want to truncate (return to zero) X from the left of the decimal point and start with all the following

SELECT TRUNCATE (3.456 and 1) returns 3.4

③: date function

A:DATE_ADD/DATE_SUB

TYPE:SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR

1, perform date operation

2 the date date is a DATETIME or date value used to specify the start time

3Gore expr is a string expression that specifies the interval value added or subtracted from the start date

4Grammer type is the keyword, which indicates how the expression is interpreted

DATE_ADD (date,INTERVAL expr type)

DATE_SUB (date,INTERVAL expr type)

SELECT DATE_ADD (CURDATE (), INTERVAL 1 DAY)

SELECT DATE_SUB (CURDATE (), INTERVAL 1 DAY)

B:DATEDIFF (expr,expr2): returns the number of days between the start time expr and the end time expr2

# calculate the difference between two dates, and the result is calculated in days

SELECT DATEDIFF ('2017-03-21 July 2017-03-10')

C:DateTime_module (YEAR,DAY,LAST_DAY,MONTH,HOUR,MINUTE)

# get the module value of a certain date, year, month, day, minute and second SELECT DAY (now ()) SELECT DAYOFMONTH (now ()) SELECT DAYOFWEEK (now ()) SELECT DAYOFYEAR (now ()) SELECT now () SELECT HOUR (now ()) SELECT MINUTE (now ())

E:UNIX_TIMESTAMP/FROM_UNIXTIME

UNIX_TIMESTAMP (date): the number of seconds FROM_UNIXTIME (unix_timestamp) FROM_UNIXTIME (unix_timestamp,format) SELECT UNIX_TIMESTAMP (NOW ()) SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (NOW () SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (NOW ()),'% yunix_timestamp,format)% mBX% d% HJV% iFROM_UNIXTIME% S') will be returned from the specified date of '1970-01-01 00FROM_UNIXTIME (NOW ().

④: other functions

A.UUID

SELECT UUID ()

B:COALESCE

COALESCE (value,...): the return value is the first non-null value in the list, NULLSELECT COALESCE ('Jerry',' Jack', 'Lucy') if there is no non-NULL worth; JerrySELECT COALESCE (NULL,' Jack', 'Lucy'); Jack

E:IF/IFNULL statement

The if function in the # database is equivalent to the ternary operator SELECT IF (1 > 1) in Java. # IFNULL (expr1,expr2): # if expr1 is not NULL, the return value of IFNULL () is expr1; otherwise its return value is expr2. SELECT IFNULL (NULL,10); SELECT IFNULL (NULL,'unempty') these are all about what the MySQL advanced query function is. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.