当前位置：首页 > news >正文

驻马店网站优化门户网站

news 2026/4/7 12:17:03

驻马店网站优化,门户网站,配色相关网站,最新的销售平台文章目录日期函数字符串函数数学函数其他函数日期函数获取年月日： mysql> select current_date(); ---------------- | current_date() | ---------------- | 2023-02-01 | ---------------- 1 row in set (0.00 sec)获得时分秒： mysql> se…

文章目录

日期函数
字符串函数
数学函数
其他函数

日期函数

在这里插入图片描述

获取年月日：

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-02-01     |
+----------------+
1 row in set (0.00 sec)

获得时分秒：

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 01:10:26       |
+----------------+
1 row in set (0.00 sec)

获取时间戳：

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-02-01 01:11:08 |
+---------------------+
1 row in set (0.00 sec)

在日期的基础上加日期：

mysql> select date_add('2013-10-01', interval 10 day);
+-----------------------------------------+
| date_add('2013-10-01', interval 10 day) |
+-----------------------------------------+
| 2013-10-11                              |
+-----------------------------------------+
1 row in set (0.00 sec)

在日期的基础上减去时间：

mysql> select date_sub('2013-10-01', interval 10 day);
+-----------------------------------------+
| date_sub('2013-10-01', interval 10 day) |
+-----------------------------------------+
| 2013-09-21                              |
+-----------------------------------------+
1 row in set (0.00 sec)

计算两个日期之间相差多少天：

mysql> select datediff('2023-02-01', '1949-10-01');
+--------------------------------------+
| datediff('2023-02-01', '1949-10-01') |
+--------------------------------------+
|                                26786 |
+--------------------------------------+
1 row in set (0.00 sec)

案例：

创建一张表，记录生日：

mysql> create table birthday (-> id int unsigned primary key auto_increment,-> birthday date-> );
Query OK, 0 rows affected (0.02 sec)

添加当前日期：

mysql> insert into birthday (birthday) values (current_date());
Query OK, 1 row affected (0.00 sec)mysql> select * from birthday;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2023-02-01 |
+----+------------+
1 row in set (0.00 sec)

案例：
创建一个留言表：

mysql> create table msg (-> id int unsigned primary key auto_increment,-> content varchar(30) not null,-> sendtime datetime-> );
Query OK, 0 rows affected (0.02 sec)

插入数据：

mysql> insert into msg (content, sendtime) values ('hello world', now());
Query OK, 1 row affected (0.01 sec)mysql> insert into msg (content, sendtime) values ('hello mysql', now());
Query OK, 1 row affected (0.01 sec)mysql> select * from msg;
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  1 | hello world | 2023-02-01 01:21:23 |
|  2 | hello mysql | 2023-02-01 01:21:30 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

显示所有留言信息，发布日期只显示日期，不用显示时间：

mysql> select content, date(sendtime) from msg;
+-------------+----------------+
| content     | date(sendtime) |
+-------------+----------------+
| hello world | 2023-02-01     |
| hello mysql | 2023-02-01     |
+-------------+----------------+
2 rows in set (0.00 sec)

查询在2分钟内发布的帖子：

mysql> select * from msg;
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  1 | hello world | 2023-02-01 01:21:23 |
|  2 | hello mysql | 2023-02-01 01:21:30 |
|  3 | hello Linux | 2023-02-01 01:24:34 |
+----+-------------+---------------------+
3 rows in set (0.00 sec)mysql> select content, sendtime from msg where date_add(sendtime, interval 2 minute) > now();
+-------------+---------------------+
| content     | sendtime            |
+-------------+---------------------+
| hello Linux | 2023-02-01 01:24:34 |
+-------------+---------------------+
1 row in set (0.00 sec)

字符串函数

在这里插入图片描述

获取 emp表的 ename列的字符集：

mysql> select charset(ename) from emp limit 1;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)

要求显示exam_result表中的信息，显示格式： “XXX的语文是XXX分，数学XXX分，英语XXX分”：

mysql> select concat(name, '的语文是', chinese, '分，数学', math, '分，英语', english, '分') from exam_result;
+---------------------------------------------------------------------------------------------+
| concat(name, '的语文是', chinese, '分，数学', math, '分，英语', english, '分')              |
+---------------------------------------------------------------------------------------------+
| 唐三藏的语文是134分，数学98分，英语56分                                                     |
| 猪悟能的语文是176分，数学98分，英语90分                                                     |
| 曹孟德的语文是140分，数学90分，英语67分                                                     |
| 刘玄德的语文是110分，数学115分，英语45分                                                    |
| 孙权的语文是140分，数学73分，英语78分                                                       |
| 宋公明的语文是150分，数学95分，英语30分                                                     |
+---------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

求学生表中学生姓名占用的字节数

mysql> select name, length(name) from students;
+----------+--------------+
| name     | length(name) |
+----------+--------------+
| Curry    |            5 |
| Durant   |            6 |
| Bryant   |            6 |
| Mitchell |            8 |
+----------+--------------+
4 rows in set (0.00 sec)

将EMP表中所有名字中有S的替换成’数据库’

mysql> select replace(ename, 'S', '数据库'), ename from emp;
+----------------------------------+--------+
| replace(ename, 'S', '数据库')    | ename  |
+----------------------------------+--------+
| 数据库MITH                       | SMITH  |
| ALLEN                            | ALLEN  |
| WARD                             | WARD   |
| JONE数据库                       | JONES  |
| MARTIN                           | MARTIN |
| BLAKE                            | BLAKE  |
| CLARK                            | CLARK  |
| 数据库COTT                       | SCOTT  |
| KING                             | KING   |
| TURNER                           | TURNER |
| ADAM数据库                       | ADAMS  |
| JAME数据库                       | JAMES  |
| FORD                             | FORD   |
| MILLER                           | MILLER |
+----------------------------------+--------+
14 rows in set (0.00 sec)

截取EMP表中ename字段的第二个到第三个字符

mysql> select substring(ename, 2, 2), ename from emp;
+------------------------+--------+
| substring(ename, 2, 2) | ename  |
+------------------------+--------+
| MI                     | SMITH  |
| LL                     | ALLEN  |
| AR                     | WARD   |
| ON                     | JONES  |
| AR                     | MARTIN |
| LA                     | BLAKE  |
| LA                     | CLARK  |
| CO                     | SCOTT  |
| IN                     | KING   |
| UR                     | TURNER |
| DA                     | ADAMS  |
| AM                     | JAMES  |
| OR                     | FORD   |
| IL                     | MILLER |
+------------------------+--------+
14 rows in set (0.00 sec)

以首字母小写的方式显示所有员工的姓名

mysql> select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from emp;
+------------------------------------------------------------+
| concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) |
+------------------------------------------------------------+
| sMITH                                                      |
| aLLEN                                                      |
| wARD                                                       |
| jONES                                                      |
| mARTIN                                                     |
| bLAKE                                                      |
| cLARK                                                      |
| sCOTT                                                      |
| kING                                                       |
| tURNER                                                     |
| aDAMS                                                      |
| jAMES                                                      |
| fORD                                                       |
| mILLER                                                     |
+------------------------------------------------------------+
14 rows in set (0.00 sec)

数学函数

在这里插入图片描述
绝对值：

mysql> select abs(-200);
+-----------+
| abs(-200) |
+-----------+
|       200 |
+-----------+
1 row in set (0.00 sec)

向上取整：

mysql> select ceiling(8.1);
+--------------+
| ceiling(8.1) |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)mysql> select ceiling(-8.1);
+---------------+
| ceiling(-8.1) |
+---------------+
|            -8 |
+---------------+
1 row in set (0.00 sec)

向下取整：

mysql> select floor(8.6);
+------------+
| floor(8.6) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)mysql> select floor(-8.6);
+-------------+
| floor(-8.6) |
+-------------+
|          -9 |
+-------------+
1 row in set (0.00 sec)

保留2位小数位数（小数四舍五入)：

mysql> select format(12.456, 1);
+-------------------+
| format(12.456, 1) |
+-------------------+
| 12.5              |
+-------------------+
1 row in set (0.00 sec)

十进制转二进制：

mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+
1 row in set (0.00 sec)

转化成十六进制：

mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)

随机数：

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7938835246431488 |
+--------------------+
1 row in set (0.00 sec)

其他函数

user() 查询当前用户：

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串，相当于对内容进行加密：

mysql> select md5('root');
+----------------------------------+
| md5('root')                      |
+----------------------------------+
| 63a9f0ea7bb98050796b649e85481845 |
+----------------------------------+
1 row in set (0.00 sec)

database()显示当前正在使用的数据库：

mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)

ifnull（val1， val2）如果val1为null，返回val2，否则返回val1的值：

mysql> select ifnull('a', 123);
+------------------+
| ifnull('a', 123) |
+------------------+
| a                |
+------------------+
1 row in set (0.00 sec)mysql> select ifnull(null, 123);
+-------------------+
| ifnull(null, 123) |
+-------------------+
|               123 |
+-------------------+
1 row in set (0.00 sec)