Introduction to Mysql built-in functions (including a large number of examples)

The following is a summary of a Li's learning of MySQL built-in functions, hoping to help you; if you don't know mysql, you can read another blog of a Li first mysql Foundation

1: Date function

2: String function

1: Date function

Function name describe
current_date() Date of becoming a parent
current_time() current time
current_timetamp() Current timestamp
date(datetime) Returns the date part of the datetime parameter
date_add(date, interval d_value_type)

Add a date or event to date. The value after the interval can be: year, minute, second, day

date_sub(date, interval d_value_type) Subtract the date or event from the date. The value after the interval can be: year, minute, second, day
datediff(date1, date2) The difference between two dates, in days
now() Current date time

eg:

1: Get current date

MariaDB [(none)]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-06-26     |
+----------------+
1 row in set (0.00 sec)

2: Get current time

MariaDB [(none)]> select current_time;
+--------------+
| current_time |
+--------------+
| 13:59:33     |
+--------------+
1 row in set (0.00 sec)

3: Get timestamp

MariaDB [(none)]> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2020-06-26 14:00:08 |
+---------------------+
1 row in set (0.00 sec)

4: Add time to date

MariaDB [(none)]> select date_add('2021-6-27', interval 10 day);
+----------------------------------------+
| date_add('2021-6-27', interval 10 day) |
+----------------------------------------+
| 2021-07-07                             |
+----------------------------------------+
1 row in set (0.00 sec)

5: Subtract time from date

MariaDB [(none)]> select date_sub('2021-6-27', interval 10 day);
+----------------------------------------+
| date_sub('2021-6-27', interval 10 day) |
+----------------------------------------+
| 2021-06-17                             |
+----------------------------------------+
1 row in set (0.00 sec)

6: Calculate the difference between the two dates

MariaDB [(none)]> select datediff('2021-6-27','2021-3-27');
+-----------------------------------+
| datediff('2021-6-27','2021-3-27') |
+-----------------------------------+
|                                92 |
+-----------------------------------+
1 row in set (0.00 sec)

7: Get current date time

MariaDB [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-26 14:04:21 |
+---------------------+
1 row in set (0.00 sec)

8: Create a table to record birthdays

MariaDB [db1]> create table name_birthday( id int primary key auto_increment,         
               birthday date, 
                );
Query OK, 0 rows affected (0.01 sec)

//Insert current date
insert into table name_birthday(birthday) values(current_date()); 
mysql> select * from table name_birthday; 
+----+------------+ 
| id | birthday   | 
+----+------------+ 
|  1 | 2017-11-19 | 
+----+------------+

9: Create a message table

//establish
MariaDB [db1]> create table msg(
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

//insert
MariaDB [db1]> insert into msg(content,sendtime) values('hello1', now());
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> insert into msg(content,sendtime) values('hello2', now());
Query OK, 1 row affected (0.00 sec)

//see
MariaDB [db1]> select *from msg;
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2020-06-26 14:23:06 |
|  2 | hello2  | 2020-06-26 14:23:12 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

//Display all message information, the release date only shows the date, not the time
MariaDB [db1]> select content,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1  | 2020-06-26     |
| hello2  | 2020-06-26     |
+---------+----------------+
2 rows in set (0.00 sec)


//Query Posts posted in two minutes
MariaDB [db1]> select * from msg where date_add(sendtime, interval 2 minute) > now();
Empty set (0.00 sec)

2: String function

Function name describe
charset(str) Return string character set
concat(string2 [, ...]) Link string
instr(string, substring) Return the location of substring in string, no 0
ucase(string2) Convert to uppercase
lcase(string2) Convert to lowercase
left(string2, length) Take length characters from the left in string2
length(string) Length of string
replace(str, search_str, replace_str) Using replace in str_ STR replace search_str
strcmp(string1, string2) Character by character comparison of two string sizes
substring(str, position, [,length]) Starting from the post of str, take out the length characters
ltrim(string)  rtrim(string)  trim(string) Remove leading or trailing spaces

 

... ongoing update.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Tags: MariaDB MySQL

Posted on Fri, 26 Jun 2020 04:08:30 -0400 by TPerez