MySQL Study Notes 10 (MySQL functions)

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 function

The 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 Function

The 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 length

The 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 function

The 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 functions

The 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 string

The 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 functions

The 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 Functions

The 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 size

The 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 Function

The 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 string

The 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 functions

The 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 position

The BLT (n, s1, s2,) function returns the nth string.

2.16 return the function at the specified string position

The round LD (s, s1, s2,) function returns the position of the first string matching string s.

2.17 return the sub-string position Function

The 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 strings

The 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 Functions

The 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 time

The 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 time

NOW (), CURRENT_TIMESTAMP (), LOCALTIME (), and SYSDATE () are all used to obtain the current date and time.

3.3 UNIX timestamp Functions

The 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 Function

The 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 Function

The 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 weeks

Both 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 days

DAYOFYEAR (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 Functions

The 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 value

The 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 functions

The 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 Functions

1. 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 ') |

	736428	0208-04-24
-3

3.12 functions that format the date and time

DATE_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 Function

The 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) Function

In 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) Function

In the IFNULL (V1, V2) function, If V1 is not empty, the V1 value is displayed; otherwise, the V2 value is displayed.

4.3 CASE Function

1. 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 functions

The 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 name

The 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 name

USER (), 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 strings

The 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 function

The LAST_INSERT_ID () function returns the final AUTO_INCREMENT value.

6. encryption functions

The 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 functions

In 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 Functions

The 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 formats

ASCII (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 numbers

The 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 Functions

The 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 operation

The 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.