MySQL Study Notes 10 (MySQL functions)
MySQL learning notes 10MySQL Functions
MySQL database provides a wide range of functions. MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, encryption functions, and formatting functions. These functions simplify your operations. SELECT statements and their conditional expressions can all use these functions. The INSERT, UPDATE, and DELECT statements and their conditional expressions can also use these functions.
1: mathematical functions
Math functions are commonly used in SQL. It is mainly used to process numbers, including integer and floating point numbers. Mathematical functions include absolute value functions, sine functions, Cosine functions, and functions for obtaining random numbers.
2: string functions
String functions are mainly used to process strings in a table. String functions include obtaining the length of a string, merging strings, inserting substrings into a string, and switching between uppercase and lowercase letters.
2.1 functions that calculate the number of character strings and functions of String Length
The CHAR_LENGTH (s) function calculates the number of characters in string s;
The LENGTH (s) function calculates the LENGTH of string s.
2.2 functions for string Merging
The CONCAT (s1, s2,) function and the CONCAT_WS (x, s1.s2,) function can combine multiple strings such as s1 and s2 into one string. However, CONCAT_WS (x, s1, s3,) can directly separate each string with the x parameter.
SELECT CONCAT('bei','ji','ng'),CONCAT_WS('-','bei','ji','ng');
Running result:
CONCAT ('bei', 'ji ', 'ng ') |
CONCAT_WS ('-', 'bei', 'ji', 'ng ') |
Beijing |
Bei-ji-ng |
2.3 string replacement functionThe INSERT (s1, x, len, s2) function replaces the string whose length starts from position x in string s1 with Len WITH s2.
SELECT s,INSERT(s,4,4,'fang') FROM t2;
Running result:
S |
INSERT (s, 4, 4, 'fang ') |
Beijing |
Beifang |
2.4 case-insensitive Conversion FunctionThe UPPER (s) function and UCASE (s) function convert the letters of string s into uppercase letters. The LOWER (s) function and LCASE (s) Function) the function converts all letters of string s to lowercase letters.
SELECT UPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');
Running result:
UPPER ('mysql ') |
UCASE ('mysql ') |
LOWER ('mysql ') |
LCASE ('mysql ') |
MYSQL |
MYSQL |
Mysql |
Mysql |
2.5 obtain the function of a string of the specified lengthThe LEFT (s, n) function returns the first n characters of string s. The RIGHT (s, n) function returns the last n characters of string s.
SELECT s, LEFT(s,3),RIGHT(s,4) FROM t2;
Running result:
S |
LEFT (s, 3) |
RIGHT (s, 4) |
Beijing |
Bei |
Jing |
2.6 string filling functionThe LPAD (s1, len, s2) function fills string s2 at the beginning of s1 so that the string length reaches len; RPAD (s1, len, s2) the function fills string s2 at the end of s1 so that the string reaches len.
SELECT s,LPAD(s,10,'+-'),RPAD(s,10,'+-') FROM t2;
Running result:
S |
LPAD (s, 10, '+ -') |
RPAD (s, 10, '+ -') |
Beijing |
+-+ Beijing |
Beijing +-+ |
2.7 Delete space functionsThe LTTIM (s) function removes spaces starting with string s;
The RTRIM (s) function removes spaces at the end of string s;
The TRIM (s) function removes spaces at the start and end of string s.
SELECT CONCAT('+',' me ','+'),CONCAT('+',LTRIM(' me '),'+');
Running result:
CONCAT ('+', 'me', '+ ') |
CONCAT ('+', LTRIM ('me'), '+ ') |
Me + |
+ Me + |
2.8 Delete the function of the specified stringThe TRIM (s1 FROM s) function removes the string s1 at the start and end of string s.
SELECT TRIM('ab' FROM 'ababddddabddab');
Running result:
TRIM ('AB' FROM 'ababddddabddab ') |
Ddddabdd |
2.9 repeated string generation functionsThe REPEAT (s, n) function repeats the string s n times.
SELECT REPEAT('MYSQL-',5);
Running result:
REPEAT ('mysql-', 5) |
MYSQL-MYSQL-MYSQL-MYSQL-MYSQL- |
2.10 space functions and replacement FunctionsThe SPACE (n) function returns n spaces. The REPLACE (s, s1, s2) function replaces string s2 with string s1 in string s.
SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');
Running result:
CONCAT ('+', SPACE (4), '+ ') |
REPLACE ('mysql', 'SQL', 'book ') |
+ |
Mybook |
2.11 functions that compare the string sizeThe STRCMP (s1, s2) function is used to compare strings s1 and s2. If s1 is greater than s2, 1 is returned; If s1 is equal to s2, 0 is returned; If s1 is less than s2,-1 is returned.
SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');
Running result:
STRCMP ('abc', 'Abb ') |
STRCMP ('abc', 'abc ') |
STRCMP ('abc', 'abd ') |
1 |
0 |
-1 |
2.12 obtain the sub-string FunctionThe SUBSTUING (s, n, len) function and the MID (s, n, len) function obtain the string with the length of len from the nth position of string s. The following describes how to use SUBSTRING (s, n, len) and MID (s, n, len) functions.
SELECT s ,SUBSTRING(s,4,3),MID(s,4,3) FROM t2;
Running result:
S |
SUBSTRING (s, 4, 3) |
MID (s, 4, 3) |
Beijing |
Jin |
Jin |
2.13 functions that match the start position of a stringThe LOCATE (s1, s), POSITION (s1 IN s), and INSTR (s, s1) functions obtain the start POSITION of s1 from string s.
SELECT s,LOCATE('jin',s),POSITION('jin' IN s),INSTR(s,'jin') FROM t2;
Running result:
S |
LOCATE ('jin', s) |
POSITION ('jin' IN s) |
INSTR (s, 'jin ') |
Beijing |
4 |
4 |
4 |
2.14 reverse string functionsThe REVERSE (s) function returns the order of string s.
SELECT s,REVERSE(s) FROM t2;
Running result:
S |
REVERSE (s) |
Beijing |
Gnijieb |
2.15 return the string function at the specified positionThe BLT (n, s1, s2,) function returns the nth string.
2.16 return the function at the specified string positionThe round LD (s, s1, s2,) function returns the position of the first string matching string s.
2.17 return the sub-string position FunctionThe FIND_IN_SET (s1, s2) function returns the position of the string matching s1 in string s2. Specifically, string s2 contains several strings separated by commas.
SELECT FIND_IN_SET('like','i,like,bei,jing');
Running result:
FIND_IN_SET ('like', 'I, like, bei, jing ') |
2 |
2.18 functions for selecting stringsThe MAKE_SET (x, s1, s2,) function selects strings from s1, s2, and sn Based on the binary number of x. For example, the binary value of 12 is 1100. The third and fourth digits from right to left are 1, so s3 and s4 are selected.
3: Date and Time FunctionsThe date and time functions are mainly used to process the date and time data in the table. Date and time functions include functions for obtaining the current date, functions for obtaining the current time, functions for calculating the date, and functions for calculating the time.
3.1 obtain functions of the current date and function of the current timeThe CURDATE () and CURRENT_DATE () functions obtain the current date;
The CURTIME () and CURRENT_TIME () functions obtain the current time.
SELECT CURDATE(),CURTIME(),CURRENT_DATE(),CURRENT_TIME();
Running result:
CURDATE () |
CURTIME () |
CURRENT_DATE () |
CURRENT_TIME () |
|
20:21:13 |
|
20:21:13 |
3.2 obtain the function of the current date and timeNOW (), CURRENT_TIMESTAMP (), LOCALTIME (), and SYSDATE () are all used to obtain the current date and time.
3.3 UNIX timestamp FunctionsThe UNIX_TIMESTAMP () function returns the current time in the form of a UNIX timestamp;
The UNIX_TIMESTAMP (d) function returns time d as a UNIX timestamp;
The FROM_UNIXTIME (d) function converts the UNIX timestamp time to a normal format time.
The UNIX_TIMESTAMP (d) and FROM_UNIXTIME (d) functions are inverse functions.
SELECT NOW(), UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());
NOW () |
UNIX_TIMESTAMP () |
UNIX_TIMESTAMP (NOW ()) |
20:30:05 |
1460205005 |
1460205005 |
3.4 obtain the month FunctionThe MONTH (d) function returns the MONTH value in date d. The value range is 1 ~ 12; MONTHNAME (d) function returns the English name of the month in date d. The parameter d can be a date or a date.
SELECT NOW(),MONTH(NOW()),MONTHNAME(NOW());
Running result:
NOW () |
MONTH (NOW ()) |
MONTHNAME (NOW ()) |
20:38:05 |
4 |
Else l |
3.5 obtain the week FunctionThe DAYNAME (d) function returns the date d of the week, showing its English name;
The DAYOFWEEK (d) function also returns the number of days in a week. 1 indicates Sunday, 2 indicates Monday,
The WEEKDAY (d) function also returns the number of days in a week. 0 indicates Monday, and 1 indicates Tuesday,
SELECT NOW(),DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW());
Running result:
NOW () |
DAYNAME (NOW ()) |
DAYOFWEEK (NOW ()) |
WEEKDAY (NOW ()) |
20:42:55 |
Saturday |
7 |
5 |
3.6 obtain the number of weeksBoth the WEEK (d) function and WEEKOFYEAR (d) function calculate the date d, which is the day of the year.
SELECT NOW(),WEEK(NOW()),WEEKOFYEAR(NOW());
Running result:
NOW () |
WEEK (NOW ()) |
WEEKOFYEAR (NOW ()) |
20:46:15 |
14 |
14 |
3.7 obtain the function of daysDAYOFYEAR (d) function date d is the day of the year;
The DAYOFMONTH (d) function returns the calculation date d, which is the day of the month.
SELECT NOW(),DAYOFYEAR(NOW()),DAYOFMONTH(NOW());
Running result:
NOW () |
DAYOFYEAR (NOW ()) |
DAYOFMONTH (NOW ()) |
20:49:01 |
100 |
9 |
3.8 obtain the year, quarter, hour, minute, and second FunctionsThe YEAR (d) function returns the YEAR value from date d;
The QUARTER (d) function returns the QUARTER value;
The HOUR (t) function returns the HOUR value of the time t;
The MINUTE (t) function returns the MINUTE value in time t;
The SECOND (t) function returns the SECOND value in the time t.
SELECT NOW(),YEAR(NOW()),QUARTER(NOW());
Running result:
NOW () |
YEAR (NOW ()) |
QUARTER (NOW ()) |
20:55:24 |
2016 |
2 |
SELECT CURTIME(),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());
Running result:
CURTIME () |
HOUR (CURTIME ()) |
MINUTE (CURTIME ()) |
SECOND (CURTIME ()) |
20:57:08 |
20 |
57 |
8 |
3.9 function for obtaining the specified date valueThe EXTRACT (type FROM d) function obtains the specified value FROM date d. The value depends on the type. Type can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. If the value of type is YEAR, The result returns the annual score.
SELECT NOW(),EXTRACT(MONTH FROM NOW());
Running result:
NOW () |
EXTRACT (month from now ()) |
21:03:06 |
4 |
3.10 time and second conversion functionsThe TIME_TO_SEC (t) function converts time t to the time in seconds;
The SEC_TO_TIME (s) function converts the time s in seconds to the format of hour, minute, and second.
SELECT CURTIME(),TIME_TO_SEC(CURTIME()),SEC_TO_TIME(76084);
Running result:
CURTIME () |
TIME_TO_SEC (CURTIME ()) |
SEC_TO_TIME (76084) |
21:08:30 |
76110 |
21:08:04 |
3.11 Date and Time Calculation Functions1. TO_DAYS (d), FROM_DAYS (n), and DATEDIFF (d1, d2) Functions
2. ADDDATE (d, n) increases by n days in date d, and SUBDATE (d, n) minus n days in date d, ADDTIME (t, n) increase n S and SUBTIME (t, n) in time t to reduce n S function
3. ADDDATE (d, INTERVAL expr type) and DATE_ADD (d, INTERVAL expr type) Functions
1:
SELECT CURDATE(),TO_DAYS(CURDATE()),FROM_DAYS(76084),DATEDIFF(CURDATE(),'2016-04-12');
Running result:
| CURDATE () | TO_DAYS (CURDATE () | FROM_DAYS (76084) | DATEDIFF (CURDATE (), '2017-
04-12 ') |
3.12 functions that format the date and timeDATE_FORMAT (d, f) Function
TIME_FORMATE (t, f) Function
GET_FORMAT (type, s) Function
SELECT CURDATE(),DATE_FORMAT(CURDATE(),'%b %D %Y');
Running result:
CURDATE () |
DATE_FORMAT (CURDATE (), '% B % D % y ') |
|
Apr 9th 2016 |
4: Conditional judgment FunctionThe condition judgment function is used to determine conditions in SQL statements. The SQL statement executes different branches based on whether the judgment conditions are met. For example, you can query employee performance from the employee table. If the performance is higher than the specified value n, "good" is output ". Otherwise, "bad" is output ".
4.1 IF (expr, V1, V2) FunctionIn the IF (expr, V1, V2) function, IF the expression expr is true, return the result V1; otherwise, return the result V2.
SELECT num,score,IF(score>=90, 'PASS', 'FAIL') FROM grade LIMIT 4;
Running result:
Num |
Score |
IF (score> = 90, 'pass', 'fail ') |
1001 |
80 |
FAIL |
1001 |
90 |
PASS |
1001 |
85 |
FAIL |
1001 |
95 |
PASS |
4.2 IFNULL (V1, V2) FunctionIn the IFNULL (V1, V2) function, If V1 is not empty, the V1 value is displayed; otherwise, the V2 value is displayed.
4.3 CASE Function1. case when expr1 THEN v1 [WHEN expr2 THEN v2,] [ELSE vn] END
2. CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2,] [ELSE vn] END
SELECT id,grade,CASE WHEN grade>60 THEN 'GOOD' WHEN grade=60 THEN 'PASS' ELSE 'FAIL' ENDLEVEL FROM t6;
5: system information functionsThe system information function is used to query the system information of the MySQL database. For example, query the database version and the current user of the database.
5.1 function for obtaining MySQL version number, number of connections, and database nameThe VERSION () function returns the database VERSION number;
The CONNECTION_ID () function returns the number of connections to the server, that is, the number of connections to the MySQL service until now;
DATABASE () and SCHEMA () return the current DATABASE name
5.2 function for obtaining the user nameUSER (), SYSTEM_USER (), SESSION_USER (), CURRENT_USER (), and CURRENT_USER functions can return the name of the current USER.
5.3 functions for retrieving character sets and sorting methods of stringsThe CHARSET (str) function returns the character set of the str string. Generally, this character set is the default Character Set of the system. The COLLATION (str) function returns the character arrangement of the str string.
5.4 obtain the last automatically generated ID value functionThe LAST_INSERT_ID () function returns the final AUTO_INCREMENT value.
6. encryption functionsThe encryption function is used in MySQL to encrypt data. Because some sensitive information in the database is not expected to be viewed by others, it should be encrypted to make the data look garbled.
6.1 encryption function PASSWORD (str)The PASSWORD (str) function can encrypt the string 'str'. Generally, the PASSWORD (str) function is mainly used to encrypt the user's PASSWORD.
SELECT PASSWORD('abcd');
Running result:
PASSWORD ('abc ') |
* A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
6.2 encryption function MD5 (str)The MD5 (str) function can encrypt the string 'str. The MD5 (str) function encrypts common data.
SELECT MD5('abcd');
Running result:
MD5 ('abcd ') |
Fce2714c4727ee9395f324cd2e7f331f |
6.3 encryption function ENCODE (str, pswd_str)The ENCODE (str, pswd_str) function can use the string pswd_str to encrypt the string str. The encrypted result is a binary number, which must be saved using BLOB fields.
6.4 decryption function DECODE (crypt_str, pswd_str)The DECODE (crypt_str, pawd_str) function can use the string pswd_str to decrypt crypt_str. Crypt_str is the binary data encrypted by ENCODE (str, pswd_str. The string pswd_str should be the same as the encrypted string pswd_str.
SELECT DECODE(ENCODE('abcd','aa'),'aa');
Running result:
DECODE (ENCODE ('abc', 'aa'), 'aa ') |
Abcd |
7. Other functionsIn addition to appeal functions, MySQL also contains many functions. For example, the FORMAT (x, n) function is used to FORMAT the number x. The INET_ATON () function can convert the IP address to a number.
7.1 formatting FunctionsThe FORMAT (x, n) function can FORMAT the number x and retain x to n digits after the decimal point.
7.2 functions for conversion of numbers in different hexadecimal formatsASCII (s) returns the ASCII code of the first character of string s; BIN (x) returns the binary code of x; HEX (x) returns the hexadecimal code of x; OCT (x) returns the octal code of x. CONV (x, f1, f2) converts x from the f1 hexadecimal number to the f2 hexadecimal number.
7.3 functions for converting IP addresses and numbersThe INET_ATON (IP) function can convert an IP address to a number;
The INET_NTOA (n) function can convert the number n to an IP address.
The IP value in the INET_ATON (IP) function must be enclosed by quotation marks.
7.4 locking and unlocking FunctionsThe GET_LOCT (name, time) function defines a lock with the name and duration as time seconds. If the lock succeeds, 1 is returned. If the attempt times out, 0 is returned. If an error occurs, NULL is returned.
The RELEASE_LOCK (name) function removes the lock named name. If the unlock succeeds, 1 is returned; if the attempt times out, 0 is returned; if the unlock fails, NULLL is returned;
The IS_FREE_LOCK (name) function checks whether a lock named name is used. If used, 0 is returned. Otherwise, 1 is returned.
7.5 The function that repeats the specified operationThe BENCHMARK (count, expr) function repeats the expression expr for count times and returns the execution time. This function can be used to determine the speed of MySQL expressions.