网站开发的目的实习报告代理平台推荐
日期函数
规定：日期：年月日 时间：时分秒
| 函数名称 | 作用描述 |
| current_date() | 当前日期 |
| current_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| date(datetime) | 返回datetime参数的日期部分 |
| date_add(date,interval d_value_type) | 在date中添加时间或日期。interval后面可以是year、day、minute、second |
| date_sub(date,interval d_value_type) | 在date中减去时间或日期。interval后面可以是year、day、minute、second |
| datediff(date1,date2) | 两个日期的时间差，单位是天 |
| now() | 当前时间日期 |
函数使用演示
获得年月日：
mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2023-08-16 |
+--------------+
1 row in set (0.00 sec)
获得时分秒：
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 23:37:33 |
+--------------+
1 row in set (0.00 sec)
获得时间戳：
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2023-08-16 23:38:11 |
+---------------------+
1 row in set (0.00 sec)
在日期的基础上加日期：
mysql> select date_add(now(),interval 10 day); ---加10天
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2023-08-26 23:39:04 |
+---------------------------------+
1 row in set (0.00 sec)mysql> select now();-----当前的日期天数
+---------------------+
| now() |
+---------------------+
| 2023-08-16 23:39:11 |
+---------------------+
1 row in set (0.00 sec)
在日期的基础上减去时间：
mysql> select now();-----原本的日期
+---------------------+
| now() |
+---------------------+
| 2023-08-16 23:40:08 |
+---------------------+
1 row in set (0.00 sec)mysql> select date_sub(now(),interval 5 day);----5天前
+--------------------------------+
| date_sub(now(),interval 5 day) |
+--------------------------------+
| 2023-08-11 23:40:26 |
+--------------------------------+
1 row in set (0.00 sec)
计算两个日期之间相差多少天：
mysql> select datediff('2019-12-31','2023-8-16');
+------------------------------------+
| datediff('2019-12-31','2023-8-16') |
+------------------------------------+
| -1324 |
+------------------------------------+
1 row in set (0.00 sec)mysql> select datediff('2023-8-16','2019-12-31');
+------------------------------------+
| datediff('2023-8-16','2019-12-31') |
+------------------------------------+
| 1324 |
+------------------------------------+
1 row in set (0.00 sec)
案例
1.创建一张表，记录生日，添加当前日期：
mysql> create table tmp(-> id int primary key auto_increment,-> birthday date-> );
Query OK, 0 rows affected (0.03 sec)mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2023-08-16 |
+----+------------+
1 row in set (0.00 sec)
2.创建一个留言表，插入相关数据。①显示所有留言信息，发布日期只显示日期，不用显示时间②查询在2分钟内发布的帖子。
- 建表
mysql> create table msg(-> id int primary key auto_increment,-> content varchar(30) not null,-> sendtime datetime);
Query OK, 0 rows affected (0.03 sec)
- 插入数据
mysql> insert into msg(content,sendtime) values('中午吃什么？',now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg(content,sendtime) values('我想吃螺蛳粉，可以吗',now());
Query OK, 1 row affected (0.00 sec)mysql> select * from msg;
+----+--------------------------------+---------------------+
| id | content | sendtime |
+----+--------------------------------+---------------------+
| 1 | 中午吃什么？ | 2023-08-16 23:51:57 |
| 2 | 我想吃螺蛳粉，可以吗 | 2023-08-16 23:52:09 |
+----+--------------------------------+---------------------+
2 rows in set (0.00 sec)
- 显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> select content,date(sendtime) from msg;
+--------------------------------+----------------+
| content | date(sendtime) |
+--------------------------------+----------------+
| 中午吃什么？ | 2023-08-16 |
| 我想吃螺蛳粉，可以吗 | 2023-08-16 |
+--------------------------------+----------------+
2 rows in set (0.00 sec)
- 请查询在2分钟内发布的帖子
mysql> insert into msg(content,sendtime) values('项目做了吗？',now());
Query OK, 1 row affected (0.01 sec)mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
+----+--------------------+---------------------+
| id | content | sendtime |
+----+--------------------+---------------------+
| 3 | 项目做了吗？ | 2023-08-16 23:56:26 |
+----+--------------------+---------------------+
1 row in set (0.00 sec)
字符串函数
案例：
- 获取stu表的 name的字符集----使用charset字符串函数
stu表：
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> select charset(name) from stu; ----获取字符串的字符集
+---------------+
| charset(name) |
+---------------+
| utf8 |
| utf8 |
+---------------+
2 rows in set (0.00 sec)
- 要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”----使用concat字符串函数
mysql> desc exam_result-> ;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| chinese | float | YES | | 0 | |
| math | float | YES | | 0 | |
| english | float | YES | | 0 | |
| qq | char(10) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)mysql> select concat(name,'的语文是',chinese,'分, 数学是',math,'分') as '分数' from exam_result;
+----------------------------------------------+
| 分数 |
+----------------------------------------------+
| 唐三藏的语文是134分, 数学是98分 |
| 猪悟能的语文是176分, 数学是98分 |
| 曹孟德的语文是140分, 数学是90分 |
| 刘玄德的语文是110分, 数学是115分 |
| 孙权的语文是140分, 数学是73分 |
| 宋公明的语文是150分, 数学是95分 |
+----------------------------------------------+
6 rows in set (0.00 sec)
- 求exam_result中学生姓名占用的字节数---使用length字符串函数
注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）。
mysql> select length(name),name from exam_result;
+--------------+-----------+
| length(name) | name |
+--------------+-----------+
| 9 | 唐三藏 |
| 9 | 猪悟能 |
| 9 | 曹孟德 |
| 9 | 刘玄德 |
| 6 | 孙权 |
| 9 | 宋公明 |
+--------------+-----------+
6 rows in set (0.00 sec)
- 以首字母小写的方式显示所有同学的姓名
select concat(lcase(substring(name,1,1)),substring(name,2)) from exam_result;
数学函数
案例：
- 绝对值
mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)
- 向上取整
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)
- 向下取整
mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)
- 保留2位小数位数（小数四舍五入)
mysql> select format(12.3456,2);
+-------------------+
| format(12.3456,2) |
+-------------------+
| 12.35 |
+-------------------+
1 row in set (0.00 sec)- 产生随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.3399681042320729 |
+--------------------+
1 row in set (0.00 sec)mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.5697201356009768 |
+--------------------+
1 row in set (0.00 sec)mysql> select rand();
+------------------+
| rand() |
+------------------+
| 0.82869639604231 |
+------------------+
1 row in set (0.00 sec)
其它函数
- user() 查询当前用户
select user();- md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串
mysql> select md5('admin');
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)
- database()显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| my_test |
+------------+
1 row in set (0.00 sec)- password()函数，MySQL数据库使用该函数对用户加密
mysql> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.02 sec)
- ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc','123');
+---------------------+
| ifnull('abc','123') |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)mysql> select ifnull(null,'123');
+--------------------+
| ifnull(null,'123') |
+--------------------+
| 123 |
+--------------------+
1 row in set (0.00 sec)