MySQL之内置函数

目录

一：日期函数

二:字符串函数 

三：数学函数

四：其他函数 

---

一：日期函数

举例: (1)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-09-17     |
+----------------+
1 row in set (0.00 sec)

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

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-17 10:41:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-09-17 10:41:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2024-09-17  |
+-------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 10 day);
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2024-09-27 10:42:59             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(now(),'2000-01-01');
+------------------------------+
| datediff(now(),'2000-01-01') |
+------------------------------+
|                         9026 |
+------------------------------+

 (2)创建一张表，记录生日

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.02 sec)

mysql> select* from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2024-09-17 |
+----+------------+

(3)创建一个留言表

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

mysql> insert into msg(content,sendtime) values('hello1', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(content,sendtime) values('hello2', now());
Query OK, 1 row affected (0.02 sec)

mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2024-09-17 10:49:01 |
|  2 | hello2  | 2024-09-17 10:49:10 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

#显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> select content,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1  | 2024-09-17     |
| hello2  | 2024-09-17     |
+---------+----------------+
2 rows in set (0.00 sec)

#请查询在2分钟内发布的帖子
mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2024-09-17 10:49:01 |
|  2 | hello2  | 2024-09-17 10:49:10 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
Empty set (0.00 sec)

二:字符串函数 

 举例:

(1)获取emp表的ename列的字符集

select charset(ename) from emp;

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

select concat (name,'的语文是',chinese,'分,的数学是',math,'分,的英语是',english,'分') as '分数' from student;

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

select length(name),name from student;

length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字
节字符则算作一个字节。比如：字母，数组算作一个字节，中文表示多个字节数（与字符集编码有关）

(4)将EMP表中所有名字中有S的替换成'上海'

select replace(ename,'S','上海'),ename from emp;

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

select substring(ename,2,2),ename from emp;

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

select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;

三：数学函数

举例：

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

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

mysql> select ceiling(12.01);
+----------------+
| ceiling(12.01) |
+----------------+
|             13 |
+----------------+
1 row in set (0.00 sec)

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

mysql> select format(12.2303,2);
+-------------------+
| format(12.2303,2) |
+-------------------+
| 12.23             |
+-------------------+
1 row in set (0.00 sec)

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

mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 43                   |
+----------------------+

四：其他函数 

 (1)   user() 查询当前用户        select user();

(2)md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串   select md5('admin')

(3)password()函数，MySQL数据库使用该函数对用户加密  select password('root');

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