MySQL advanced query functions: 1. String function; 2. Numeric function, [CEIL(x)] returns the smallest integer value not less than X; 3. Date function, [DATE_ADD/DATE_SUB], etc.
MySQL advanced query functions:
Classification of functions:
1, Single-line function: Calculate the input value of each record, obtain the corresponding calculation result, and return it to the user. In other words, each record is used as an input parameter, and the calculation result of each record is obtained through function calculation.
2, Multi-line function: Calculate the input values of multiple records and obtain a single result corresponding to multiple records.
Single-line function:
①:String function (users process single-line character data, such as case conversion, string interception, assembly, etc.)
a.LOWER/UPPER(LOWER(str): Returns the string str in which the string str becomes lowercase letters. UPPER(str): Returns the string str in which the string str becomes uppercase letters. )
SELECT UPPER(name) FROM student; //全部大写 SELECT LOWER(name) FROM student; //全部小写
b.CONCAT: CONCAT(str1,str2,...)：
1, the return result is the string generated by the connection parameters.
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: Replace the specified (position, length) substring with the target string
Format: INSERT(str,pos,len,newstr)
Parameters: str: (source character String) pos: (the starting position of insertion, the index starts from 1) len: (the length of the replacement string) newstr: (the string to be inserted)
1, return the string str, its substring Starting at position pos and length replaced by len characters of the string newstr.
2. If pos exceeds the string length, the return value is the original string.
3. If the length of len is greater than the length of other strings, replacement starts from position pos.
4. If any parameter is null, the return value is NULL
Example:
Replace some characters of the user name, the rules are as follows: keep the first 2 digits of the user name , use * instead of the middle 3 digits. If there are extra characters in the name, keep
SELECT INSERT(name,2,3,'***') FROM student;
d. ①LENGTH: The number of bytes occupied by the string
SELECT LENGTH(name) FROM student;
②CHAR_LENGTH: Calculate the number of characters
SELECT CHAR_LENGTH(name) FROM student;
e: LPAD/RPAD: If the number of characters in the string is greater than the given number, if it is less, it will be filled in from the edge specified by the function Specify the number, if there are more, truncate
from the end of the string. LPAD(str,len,padstr): left padding
1, return the string str, whose left side is represented by The string padstr is padded 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;
Result display:
RPAD(str,len,padstr): right padding
1, returns the string str, the right side of which is padded to len characters by the string padstr length.
2. If the length of the string str is greater than len, the return value is shortened to the same length as len characters.
SELECT RPAD(NAME,10,'*') FROM student;
f:TRIM/LTRIM/RTRIM
LTRIM (str): The left space is trimmed;
RTRIM (str): The right space is trimmed;
TRIM(str)=LTRIM RTRIM
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
Advanced usage, intercept from str in the specified way remstr;
TRIM(remstr FROM] str): equivalent to TRIM(BOTH remstr FROM str);
SELECT TRIM(name), CHAR_LENGTH(TRIM(name)), CHAR_LENGTH(name) FROM student; #去掉字符串中两端的指定子字符串 SELECT TRIM('ja' FROM name) FROM student; ||(等价于) SELECT TRIM(BOTH 'ja' FROM name) FROM student; # 去掉头 SELECT TRIM(LEADING 'ja' FROM name) FROM student; # 去掉尾 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;
# 选择性的替换 # 当某一条的记录中的字段值和第二个参数的值相等的时候 #把这个字段值替换成字三个参数 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):
Return a substring with the same length as len characters from the string str, starting at position pos
If pos is a negative number, counting from the end of the string;
# 从指定的位置开始，截取到最后 SELECT SUBSTR(name,2) FROM student; # 从指定的位置截取指定的长度的子字符串 SELECT SUBSTR(name,2,3) FROM student;
②: Numeric function
a.ABS/MOD ABS(x): Returns the absolute value of a number;
MOD(N,M): Returns N divided by M Remainder (modulo);
SELECT ABS(-13); //取绝对值 SELECT MOD(10,3);//取模
b.CELT/FLOOR/ROUND/TRUNCATE
CEIL(x): Returns the smallest integer value that is not less than X;
SELECT CEIL(3.5); 结果4
FLOOR (x): Returns the largest integer value that is not greater than ,D)：
1. Return parameter X, whose value is close to the nearest integer.
2，在有两个参数的情况下，返回X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。
3，若要接保留X值小数点左边的D 位，可将 D 设为负值。
SELECT ROUND(3.2228,2); 返回3.22
TRUNCATE(X,D)
1，返回被舍去至小数点后D位的数字X。
2，若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有
SELECT TRUNCATE(3.456,1) 返回3.4
③:日期函数
a:DATE_ADD/DATE_SUB
TYPE:SECOND ,MINUTE ,HOUR ,DAY ,WEEK ,MONTH ,YEAR
1，执行日期运算；
2，date 是一个 DATETIME 或DATE值，用来指定起始时间；
3，expr 是一个字符串表达式，用来指定从起始日期添加或减去的时间间隔值；
4，type 为关键词，它指示了表达式被解释的方式
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)：返回起始时间expr和结束时间expr2之间的天数
#计算两个日期的差值, 计算结果的单位是·天·
SELECT DATEDIFF('2017-03-21','2017-03-10')
c:DateTime_module (YEAR,DAY,LAST_DAY,MONTH,HOUR,MINUTE)
# 获取某个日期的模块的值， 年，月日时分秒 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)：将返回从'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 FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()), '%y/%m/%d%H:%i:%S')
④:其他函数
a.UUID
SELECT UUID();
b:COALESCE
COALESCE(value,...)：返回值为列表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL SELECT COALESCE('Jerry', 'Jack', 'Lucy'); 结果为Jerry SELECT COALESCE(NULL, 'Jack', 'Lucy'); 结果为Jack
e:IF/IFNULL语句
# 数据库中的if函数， 相当于Java中的三目运算符 SELECT IF(1>1,'true','false') # IFNULL(expr1,expr2)： #假如expr1 不为 NULL，则 IFNULL() 的返回值为expr1; 否则其返回值为expr2。 SELECT IFNULL(NULL,10); SELECT IFNULL(NULL,'unempty')
更多相关免费学习推荐：mysql教程(视频)
The above is the detailed content of What are the advanced query functions of MySQL?. For more information, please follow other related articles on the PHP Chinese website!