MySQL advanced query function (single-line function)

Classification of functions :
1, single-line function: For each record input value to calculate, obtains the corresponding calculation result, returns to the user, namely, each record as an input parameter, after the function computation obtains each record the calculation result.
2, multi-line function: A number of records entered the value of the calculation, to obtain a number of records corresponding to a single result.

Single-line function:

①: string function (user handles character data for a single line, such as case conversion, string interception, assembling, etc.)

A.lower/upper (LOWER (str): String that returns the string str into lowercase letters. UPPER (str): String that returns the string str becomes uppercase)

SELECT UPPER  from student;// All caps  SELECT LOWER  from student; All lowercase

b.concat: CONCAT (str1,str2,...) ：

1, returns the string resulting from the connection parameter.

2, if any one of the arguments is NULL, the return value is null

3, one or more parameters are allowed

SELECT name,age, CONCAT (name,'-' from student;

The result of the operation is:

c.insert: Replace substring of specified (position, length) with target string

format: INS ERT (STR,POS,LEN,NEWSTR)
     Parameters: str: (source string)       pos: (Start insertion position, index starting at 1) & nbsp Len: (length of replacement string)   NEWSTR: (string to be inserted)
      1, returns the string str, whose substring starts at the POS position and length is replaced by the string newstr len character 。
      2, if the POS exceeds the string length, the return value is the original string.
      3, if Len is longer than the length of other strings, replace it from position pos.
      4, if any of the parameters are null, the return value is NULL
Example:
Replace the partial character of the user name name with the following rule: retain the first 2 digits of the user name, the middle 3 bits use * instead, and if the name has extra characters , leave the

SELECT  INSERT (Name,2,3,'* * *')   from student;

The result of the operation is :

d.①length: The number of bytes occupied by the string

SELECT  from student;

②char_length: Calculating the number of characters

SELECT  from student;

E:lpad/rpad: If the number of characters in a string is greater than the given number, if it is less then the specified number from the edge of the function, if more than the last truncated from the string

　　　Lpad (STR,LEN,PADSTR): Left to fill
1, returns the string Str, whose left is filled by the string padstr to the total length of Len.
2, if the length of STR is greater than Len, the return value is shortened to the Len character.

SELECT Lpad (NAME,ten,'*') from   student;

Results show:
　　　　Rpad (STR,LEN,PADSTR): Right to fill
1, returns the string str, whose right is padded with the string padstr to Len character length.
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,ten,'*') from   student;

Results show:

F:trim/ltrim/rtrim

LTRIM (str): Left space is trim off;
RTRIM (str): The right space is trim off;
TRIM (str) =ltrim+rtrim
TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str)
Advanced usage, intercepting remstr from STR in the specified manner;
Trim (remstr from] str): equivalent to trim (BOTH remstr from str);

SELECTTrim (name), Char_length (Trim (name)), Char_length (name) fromstudent; #去掉字符串中两端的指定子字符串SELECTTRIM ('ja'  fromName fromstudent; || (equivalent to)SELECTTRIM (BOTH'ja'  fromName fromstudent; # To turn around # SELECTTRIM (Leading'ja'  fromName fromstudent; # Remove the tail SELECTTRIM (TRAILING'ja'  fromName fromStudent

G:replace

REPLACE (STR,FROM_STR,TO_STR):
1, replace all from_str in str with TO_STR;
2, case sensitive;

# Selective Substitution # When the value of a field in a record of a piece is equal to that of the second parameter # Replace this field value with a word three parameters SELECT REPLACE (Name,'rose','niceman' from student;

H:substring (Str,pos):
Returns a substring from the string str, starting at position Pos
SUBSTRING (Str,pos,len):
Returns a substring of the same length as the Len character from the string str, starting at position Pos
If the POS is negative, it is calculated from the end of the string;

# start at the specified position and intercept to the last SELECT SUBSTR (name,2 from student;  # intercepts a substring of the specified length from the specified location  SELECT SUBSTR (name,2,3 from student;

②: Numeric functions

A.abs/mod ABS (x): Returns the absolute value of a number;
MOD (N,M): Returns the remainder of N divided by M (modulo);

SELECT ABS (-);   //Take absolute value SELECT MOD (3); //Take mold

B.celt/floor/round/truncate

Ceil (x): Returns the smallest integer value not less than x;

SELECT Ceil (3.5);      Results 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 (x,d):
1, returns the parameter x, whose value is close to the nearest integer.
2, in the case of two parameters, returns X, whose value is reserved to the D-bit after the decimal point, and the D-bit is rounded.
3, to retain the D-bit to the left of the X-value decimal point, set D to a negative value.

SELECT ROUND (3.2228,2);    returns 3.   A

TRUNCATE (X,D)
1, returns the number x that is removed to the D-digit after the decimal point.
2, if the value of D is 0, the result does not have a decimal point or a fractional part. D can be set to a negative number, to intercept (zero) x to the left of the decimal point starting at the beginning of all

SELECT TRUNCATE (3.456,1)    returns 3. 4 

③: Date function

A:date_add/date_sub

Type:second, MINUTE, HOUR, Day, WEEK, MONTH, year

1, execution date arithmetic;
2,date is a DATETIME or date value used to specify the starting time;
3,expr is a string expression that specifies the time interval value to add or subtract from the start date;
4,type is a keyword that indicates how the expression is interpreted

Date_add (Date,interval expr type)
Date_sub (Date,interval expr type)

SELECT 1  Day )SELECT1Day)

B: DATEDIFF (EXPR,EXPR2): Returns the number of days between the start time expr and the end time EXPR2

#计算两个日期的差值, the unit of calculation is • Days ·

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

C:datetime_module (Year,day,last_day,month,hour,minute)

# Gets the value of a module for a date, year, month, day, seconds 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): Returns the number of seconds after the specified date from ' 1970-01-01 00:00:00 ' GMT
From_unixtime (Unix_timestamp) from_unixtime (Unix_timestamp,format)

SELECT Unix_timestamp (now ()) SELECT From_unixtime (Unix_timestamp (now ())) SELECT ' %y/%m/%d%h:%i:%s ')

④: Other functions

A.uuid

SELECT UUID ();

B:coalesce

COALESCE (value,...) : The return value is the first non-null value in the list, and the return value is NULL if no non-null is worth

 select  coalesce  ("  jerry   ", "  jack   ",  " lucy  "   Span style= "color: #000000");   select  coalesce  (null ,  "  Jack   ", "     Lucy   ");  

E:if/ifnull statements

# If function in the database, equivalent to the three-mesh operator in JavaSELECT IF(1>1,'true','false')# ifnull (EXPR1,EXPR2): #假如expr1 not for NULL , the return value of Ifnull () is EXPR1, otherwise its return value is EXPR2.  SELECTIfnull (NULL,Ten);SELECTIfnull (NULL,'Unempty')

MySQL advanced query function (single-line function)