1, Date and time functions
1. Function to get the current date
It's the same. It's different
CURDATE(): used to get the current date of the system
Current "date() is used by the system to get the current date
mysql> select CURDATE(),CURRENT_DATE();
2. Get function of current time
CURTIME(): used to get the current time of the system
mysql> select CURTIME();
3. Function to get the current date and time
CURRENT TIMESTAMP (): used to get the current date and time of the system
LOCALTIME(): used to get the current date and time of the system
NOW(): used to get the current date and time of the system
SYSDATE(): used to get the current date and time of the system
mysql> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
4. Function to get time stamp
UNIX? Timestamp() to get a timestamp in UNIX format
mysql> select UNIX_TIMESTAMP();
A time stamp is a complete and verifiable data that can represent the existence of a data at a specific point in time.
5. Function to convert timestamps
From? Unixtime() is used to convert a time stamp in UNIX format to a time in normal format
mysql> select FROM_UNIXTIME(1587693176); # Add time stamp found before
6. Function to get UTC date
UTC? Date(): used to get the current UTC (world standard time) date value
mysql> select UTC_DATE();
7. Get UTC time function
UTC? Time() is used to get the current UTC (world standard time) time value
mysql> select UTC_TIME();
8. Get function of month
MONTH(date): used to return the month corresponding to date
MONTHNAME(date): used to return the full English name of the month corresponding to date
mysql> select MONTH('2020-4-24'),MONTHNAME('2020-4-24');
9. Get week function
DAYNAME(date): English name used to return the working day corresponding to date
DAYOFWEEK(date): used to return the index of the week corresponding to date, 1 for Sunday, 2 for Monday,..., 7 for Saturday
WEEKDAY(date): used to return the working day index corresponding to the date, 0 for Monday, 1 for Tuesday,..., 6 for Sunday
WEEK(date): used to calculate the week number of a year, 53 weeks in a year
WEEKOFYEAR(date): used to calculate the date, which is the week of the year. There are 53 weeks in the year
mysql> select DAYNAME('2020-4-24'),DAYOFWEEK('2020-4-24'),WEEKDAY('2020-4-24'),WEEK('2020-4-24'),WEEKOFYEAR('2020-4-24');
10. Function to get days
Day of year (date): used to return the day of the year, 365 days of the year
DAYOFMONTH(date): used to calculate the day ordinal of a month
mysql> select DAYOFYEAR('2020-4-24'),DAYOFMONTH('2020-4-24');
11. Function to get the year
YEAR(date): returns the year corresponding to date
mysql> select YEAR('20-04-24'),YEAR('98-04-24');
12. Get function of quarter
QUARTER(date): returns the quarter value of the year corresponding to date
mysql> select QUARTER('20-04-24');
13. Get function for minutes
MINUTE(time) returns the minute value corresponding to time
mysql> select MINUTE('20-04-24 10:18:00');
14. Get function for seconds
SECOND(time) returns the number of seconds corresponding to time
mysql> select SECOND('10:18:22');
15. Function to get the specified value of a date
EXTRACT(type FROM date) is used to get the specified date value
mysql> select EXTRACT(YEAR FROM '2020-04-24') AS col1, # When type is YEAR, only the annual value is returned
-> EXTRACT(YEAR_MONTH FROM '2020-04-24 10:18:22') AS col2, # Returns year and month when type is year ﹣ month
-> EXTRACT(DAY_MINUTE FROM '2020-04-24 10:18:22') AS col3; # Returns day, hour, and minute when the type is day [minute] 
16. Functions of time and second conversion
Time? To? Sec (time): used to convert time to seconds, the formula is "hour 3600 + minute 60 + second"
SEC to time (time): used to convert a second value to a time format
mysql> select TIME_TO_SEC('23:23:00'),SEC_TO_TIME('84180'); 
17. Function to calculate date and time
DATE_ADD(): used to add a date. The format is DATE_ADD(date, INTERVAL expr type)
Date UU sub(): used to subtract the date. The format is date UU sub (date, interval expr type)
Submit(): used to subtract the date. The format is submit (date, interval expr type)
ADDTIME(): used to add a date. The format is ADDTIME(date, expr)
Subtitle(): used to subtract the date. The format is subtitle (date, expr)
Date? Diff(): used to calculate the number of days between two dates
mysql> select DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND); # Add 1 second to the specified date 
mysql> select DATE_ADD('2020-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); # Add 1 minute and 1 second to the specified date
mysql> select DATE_SUB('2020-01-01', INTERVAL 31 DAY); # Subtract 31 days from the specified date 
mysql> select SUBDATE('2020-01-01', INTERVAL 31 DAY); 
mysql> select ADDTIME('2020-12-31 23:59:59', '1:1:1'); # Add 1 hour, 1 minute and 1 second to the specified date 
mysql> select SUBTIME('2020-12-31 23:59:59', '1:1:1'); # Subtract one hour, one minute and one second from the specified date
mysql> select DATEDIFF('2020-06-01', '2020-04-24');
18. Functions that format date and time
DATE_FORMAT(date, format) is used to format the date, that is, display the date value according to the format specified by format
Time "format (time, format) is used to format time, that is, to display the time value according to the format specified by format
GET_FORMAT(), we specify the value type and format type, and then the format string will be displayed
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
mysql> select TIME_FORMAT('16:00:00', '%H %k %I'); 
mysql> select DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA')); 
%d date of the month in numerical form (00.. 31)
%e date of the month in numerical form (0.. 31)
%f microseconds (000000... 999999)
%H in 2 digits for 24 hours (00.. 23)
%h. % I in 2 digits for 12 hours (01.. 12)
%i minute, digital form (00-59)
%j number of days in a year (001366)
%k for 24 hours (0-23)
%l in 12 hours (0.. 12)
%M month name (january..December)
%m month numerical form (00.. 12)
%p AM or PM
%r time, 12 hour system (hour hh: minute mm: second ss followed by AM or PM)
%S. % s in 2-digit seconds (00.. 59)
%T time, 24-hour system (hours hh: minutes mm: seconds ss)
%U week (00.. 53), where Sunday is the first day of the week
%u week (00.. 53), where Monday is the first day of the week
%V week (01.. 53), where Sunday is the first day of the week, used with% X
%Week v (01.. 53), where Monday is the first day of the week, used with% x
%W working day name (Sunday.. Saturday)
%w day of the week (0 = Sunday.. 6 = Saturday)
%X the year of the week, where Sunday is the first day of the week; the number is in 4 digits and is used with% V
%x the year of the week, where Monday is the first day of the week; the number is in 4 digits and is used with% v
%Year in Y4 digits
%y2 digit represents year
%%'%' text character
2, Conditional judgment function
IF() IF(expr, v1, v2) if the expression expr is TRUE, the return value is v1, otherwise v2
mysql> select IF(1>2,2,3);
It can be seen that one is no more than two, so v2 is returned, that is, 3
IFNULL() IFNULL(v1, v2), if v1 is not NULL, the return value is v1; if v1 is NULL, the return value is v2
mysql> select IFNULL(1,2),IFNULL(NULL,10);
CASE syntax: CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END meaning: if expr is equal to a vn, the result after the corresponding position THEN is returned; if it is not equal to all the values, the rn after ELSE is returned
mysql> select CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
3, System information function
1. Function to get MySQL version number
VERSION() is used to get MySQL version number
mysql> select VERSION();
2. ID function to view the number of connections for the current user
Connection? ID () is used to view the number of connections for the current user
mysql> select CONNECTION_ID();
mysql> show PROCESSLIST; # View the connection information of the current user
The meaning of the above label is as follows:
- id: the system assigned connection id when the user logs in to MySQL
- User: currently connected user
- Host: shows which IP and which port this statement is issued from. It can be used to track the users of the statement in question
- db: show which database this process is currently connected to
- Command: displays the commands executed by the current connection. The values are sleep, query and connect
- Time: displays the duration of this status, in seconds
- State: displays the state of the SQL statement using the current connection
- Info: show this SQL statement
3. View the functions of the currently used database
DATABASE(): used to view the currently used database
SCHEMA(): used to view the currently used database
mysql> select database(),SCHEMA();
4. Function to view the currently logged in user name
USER(): returns the currently logged in user and host name
CURRENT_USER(): used to return the current login user and host name
SYSTEM_USER(): used to return the currently logged in user and host name
mysql> select USER(),CURRENT_USER(),SYSTEM_USER();
5. Function to view the character set of a specified string
CHARSET(str) the character set used to view the string str
mysql> select CHARSET('abc'); 
6. Function to view the arrangement of a specified string
COLLATION(str): used to view the character arrangement of string str
mysql> select COLLATION('abc'); 
7. Get the last auto generated ID value function
Last? Insert? Id(): used to get the next automatically generated ID value
#First, create a table with an auto increment constraint for its id field
mysql> create table tab1(
-> id int auto_increment primary key,
-> name varchar(30)
-> );
mysql> insert into tab1 values (NULL,'zhangsan'); #Insert a piece of data. If the id is not specified, it will be generated automatically. The id is 1
mysql> insert into tab1 values (NULL,'lisi'); # Insert a piece of data. If the id is not specified, it will be generated automatically. The id is 2
mysql> select * from tab1; # View table information
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
mysql> select LAST_INSERT_ID();
mysql> insert into tab1 values (NULL,'aaa'),(NULL,'bbb'),(NULL,'ccc'); mysql> select * from tab1; # If we insert multiple pieces of data at once, though the id is 5 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | aaa | | 4 | bbb | | 5 | ccc | +----+----------+ mysql> select LAST_INSERT_ID(); # However, when we view it with last ﹣ insert ﹣ id(), it is 3. This is because last ﹣ insert ﹣ id() only returns the first row of inserted data +------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+
4, Encryption / decryption function
1. Encryption function
PASSWORD(str): calculated from the clear text password str and returns the encrypted password string. When the parameter is NULL, it returns NULL
MD5(str) calculates an MD5 128 bit checksum for the string str
Encode (STR, pswd? STR) uses pswd? STR as the password to encrypt str
mysql> select PASSWORD('newpwd');
mysql> select MD5('newpwd');
mysql> select ENCODE('secret','123.com');
2. Decryption function
Decode (crypt? STR, pswd? STR) uses pswd? STR as the password to decrypt the encrypted string crypt? Str
mysql> select DECODE(ENCODE('secret','cry'),'cry');
5, Other functions
1. Format function
FORMAT(x, n): format the number x and round it to the nearest N decimal places. The result is returned as a string
mysql> select FORMAT(1.23456, 4), FORMAT(1.2, 4), FORMAT(1.234, 0);
2. Functions for converting numbers in different bases
CONV(): used for conversion between different decimal numbers
mysql> select CONV('a',16,2), # Convert hexadecimal a to binary
-> CONV(15,10,2), # Convert decimal 15 to binary
-> CONV(15,10,8), # Convert decimal 15 to octal
-> CONV(15,10,16); # Convert decimal 15 to hexadecimal 
3. Functions of IP address and number conversion
INET_ATON(expr): used to convert a network address to an integer representing the address value
mysql> select INET_ATON('192.168.1.1');
4. Lock function and unlock function
Get lock (str, timeout): use the string str to get a lock, and the duration is timeout seconds
- If the lock is obtained successfully, 1 will be returned
- 0 if the operation times out
- NULL if an error occurs
mysql> select GET_LOCK('lock1',10); # The returned result is 1, indicating that a lock named 'lock1' has been successfully obtained, with a duration of 10 seconds 
Release_lock (STR): used to unlock the lock obtained by GET_LOCK(), named after the string str
- If the lock is unlocked, return to 1
- Returns 0 if the thread has not created a lock
- NULL if the named lock does not exist
- If the lock has never been acquired by the call of GET_LOCK(), or the lock has been untied in advance, the lock does not exist
mysql> select RELEASE_LOCK('lock1'); # If the return value is 1, the unlocking is successful 
Is? Free? Lock (str): check if the lock named str can be used
- If the lock can be used, return 1
- 0 if lock is in use
- NULL if an error occurs
mysql> select IS_FREE_LOCK('lock1');
Is? Used? Lock (str): used to check whether the lock named str is in use. If it is blocked, the connection identifier of the client using the lock will be returned, otherwise NULL will be returned
mysql> select IS_USED_LOCK('lock1');
5. Functions that repeat the specified operation
BENCHMARK(count, expr): used to repeat count times of execution expression expr
- Can be used to calculate the speed of MySQL processing expressions
- The execution time of the statement can be reported inside the MySQL client
mysql> select PASSWORD('newpwd'); # # It took 0.00 seconds to perform 1 encryption operation 
mysql> select BENCHMARK( 500000, PASSWORD('newpwd') );
+-----------------------------------------+
| BENCHMARK( 500000, PASSWORD('newpwd') ) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set, 1 warning (0.06 sec) # It took 0.06 seconds to perform 500000 encryption operations 6. Function to change character set
CONVERT(... USING...) is used to change the default character set of a string
mysql> select CHARSET('abc'); # The default is utf8
+----------------+
| CHARSET('abc') |
+----------------+
| utf8 |
+----------------+
mysql> select CHARSET(CONVERT('abc' USING latin1)); # Convert to latin1 character set
+--------------------------------------+
| CHARSET(CONVERT('abc' USING latin1)) |
+--------------------------------------+
| latin1 |
+--------------------------------------+7. Function to change data type
CAST(x, AS type): used to convert a value of one data type to another
CONVERT(x, type): used to convert a value of one data type to another
mysql> select CAST(100 AS CHAR(2)); # Convert integer type 100 to a string type with two display widths, resulting in '10'
mysql> select CONVERT(100,CHAR(2)); # Convert 100 of integer type to a string type with two display widths, resulting in '10'
