MySQL之DQL（数据查询语言）-常见函数

已于 2022-03-30 14:49:49 修改
阅读量5.8k 收藏 4
点赞数 2
分类专栏： MySQL数据库 文章标签： mysql mysql常用函数
于 2022-03-23 09:38:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Alian_1223/article/details/123304076
版权

目录

一、简介

   MySQL 中的函数特别的多，本文主要讲解 MySQL 中一些常见的函数：

  • 字符串函数
  • 数字函数
  • 日期函数
  • 高级函数

  本文就用这个员工信息表进行演示

CREATE TABLE `tb_employee` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `emp_code` int unsigned DEFAULT NULL COMMENT '员工编码',
  `emp_name` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `gender` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  `dep_code` int DEFAULT NULL COMMENT '部门',
  `job` varchar(20) DEFAULT '' COMMENT '工作',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `salary` double(8,2) DEFAULT NULL COMMENT '工资',
  `hire_date` date DEFAULT NULL COMMENT '入职时间',
  `manage_code` int DEFAULT NULL COMMENT '所属领导',
  PRIMARY KEY (`id`),
  KEY `idx_emp_code` (`emp_code`),
  KEY `idx_manage_code` (`manage_code`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci comment '员工信息表';

二、字符串函数

2.1、字符串长度

  使用 char_length(str) 或者 character_length(str) 都是可以的，这里的 str 是一个字符串。

获取指定字符串的长度

#获取指定字符串的长度示例
SELECT CHAR_LENGTH("Alian") AS '字符串长度';

+-----------------+
| 字符串长度      |
+-----------------+
|               5 |
+-----------------+

获取字段的长度

#获取字段的长度（比如获取员工编号为8000的名字的长度）
SELECT emp_name AS '姓名', char_length(emp_name) AS '姓名的长度' FROM tb_employee WHERE emp_code='8000';
#或者
SELECT emp_name AS '姓名', character_length(emp_name) AS '姓名的长度' FROM tb_employee WHERE emp_code='8000';

+-----------+-----------------+
| 姓名      | 姓名的长度      |
+-----------+-----------------+
| 赖晓畅    |               3 |
+-----------+-----------------+

2.2、字符串合并

  使用 CONCAT(s1,s2…sn) 这里的 s1,s2…sn 是多个字符串，用逗号隔开。

常规字符串合并

#合并常规字符串
SELECT concat('Alian',' lover',' java') as '字符串合并'; 

+------------------+
| 字符串合并       |
+------------------+
| Alian lover java |
+------------------+

字符串和字段值合并

#给前面4个员工名字后面都拼一个 lover mysql
SELECT concat(emp_name,' lover',' mysql') as '字符串合并' from tb_employee limit 4; 

+-----------------------+
| 字符串合并            |
+-----------------------+
| 赖晓畅 lover mysql    |
| 谢又蓝 lover mysql    |
| 吕芸溪 lover mysql    |
| 谭淑慧 lover mysql    |
+-----------------------+

转义符使用

  有时我们需要拼接单引号，反斜杠等，就需要用到转义符

#比如把前4个员工的名字用单引号拼起来
SELECT concat('\'',emp_name,'\'') as '字符串合并' from tb_employee limit 4;
#上面只是为了演示，实际你也可以使用下面的语句，但是一般建议写sql还是用单引号
SELECT concat("'",emp_name,"'")  as '字符串合并' from tb_employee limit 4; 

+-----------------+
| 字符串合并      |
+-----------------+
| '赖晓畅'        |
| '谢又蓝'        |
| '吕芸溪'        |
| '谭淑慧'        |
+-----------------+

  有时如果想用一个特定符合分隔（比如下划线，短横线等）所有的结果值，则使用 CONCAT_WS(x, s1,s2…sn) 其中这里的 x 是就是分隔字符串， s1,s2…sn 是多个字符串，用逗号隔开。

使用分隔符合并常规字符串

#比如把Alian lover java用下划线分隔
SELECT concat_ws('_','Alian','lover','java') as '分隔符合并字符串'; 

+--------------------------+
| 分隔符合并字符串         |
+--------------------------+
| Alian_lover_java         |
+--------------------------+

2.3、数字格式化

  将数字进行格式化，格式使用 FORMAT(x,n) 这里的 x 是要格式化的数字， n 就是将 x 保留到小数点后 n 位，最后一位四舍五入。

#将12345.6789格式化，保留2位小数
SELECT format(12345.6789,2) as '格式化字符串';

+--------------------+
| 格式化字符串       |
+--------------------+
| 12,345.68          |
+--------------------+

2.4、英文大小写

转为小写

转小写方法功能
LCASE(s) 把 s 中的字母转为小写
LOWER(s) 把 s 中的字母转为小写

  使用 LCASE(s) 或 LOWER(s) 可以将字符串里的字母转换为小写，其中括号里的 s 是要转换的字符串。

#我们这里使用这两个方法来把ALIAN转换为小写
SELECT lcase('ALIAN') as '转为小写方式一',lower('ALIAN') as '转为小写方式二';

+-----------------------+-----------------------+
| 转为写方式一        | 转为小写方式二        |
+-----------------------+-----------------------+
| alian                 | alian                 |
+-----------------------+-----------------------+

转为大写

转大写方法功能
UCASE(s) 把 s 中的字母转为大写
UPPER(s) 把 s 中的字母转为大写

  使用 UCASE(s) 或 UPPER(s) 可以将字符串里的字母转换为大写，其中括号里的 s 是要转换的字符串。

#我们这里使用这两个方法来把alian转换为大写
SELECT ucase('alian') as '转为大写方式一',upper('alian') as '转为大写方式二';

+-----------------------+-----------------------+
| 转为大写方式一        | 转为大写方式二        |
+-----------------------+-----------------------+
| ALIAN                 | ALIAN                 |
+-----------------------+-----------------------+

  不要因为我没有举数据库字段的就不会写了啊，就把 s 换成你要操作的字段的列名就行了，不要加引号！！！，比如：

#把名字转为大写或者小写（指含有英文字母的）
SELECT lcase(emp_name),lower(emp_name),ucase(emp_name),upper(emp_name) from tb_employee;

2.5、去空格

去空格方法功能
LTRIM(s) 去掉字符串 s 开始处的空格
RTRIM(s) 去掉字符串 s 结尾处的空格
TRIM(s) 去掉字符串 s 开始和结尾处的空格

  使用 LTRIM(s) 、 RTRIM(s) 或 TRIM(s) 分别可以将字符串 s 左边，右边以及两边的空格去掉，其中括号里的 s 是要去除空格的字符串。

去除左边空格

#去除左边空格
SELECT '          左边空格去除' as '原字符串', ltrim('          左边空格去除') as '去除左边的空格';

+------------------------------+-----------------------+
| 原字符串                     | 去除左边的空格        |
+------------------------------+-----------------------+
|           左边空格去除       | 左边空格去除          |
+------------------------------+-----------------------+

去除右边空格

#去除右边空格
SELECT '右边边空格去除          ' as '原字符串', rtrim('右边边空格去除          ') as '去除右边的空格';

+---------------------------------+-----------------------+
| 原字符串                        | 去除右边的空格        |
+---------------------------------+-----------------------+
| 右边边空格去除                  | 右边边空格去除        |
+---------------------------------+-----------------------+

去除两边空格

#去除两边空格
SELECT '          两边空格去除          ' as '原字符串', trim('          两边空格去除          ') as '去除两边的空格';

+----------------------------------------+-----------------------+
| 原字符串                               | 去除两边的空格        |
+----------------------------------------+-----------------------+
|           两边空格去除                 | 两边空格去除          |
+----------------------------------------+-----------------------+

其实有可能你对右边的空格感官不明显，其实结合前面的合并字符串函数 CONCAT(s1,s2…sn) 得到更明显的效果（其实还可以使用后面要讲的RPAD函数）

SELECT 
concat('右边边空格去除          ','#####') as '未去除空格',
concat(rtrim('右边边空格去除          '),'#####')  as '去除右边的空格';

+--------------------------------------+----------------------------+
| 未去除空格                           | 去除右边的空格             |
+--------------------------------------+----------------------------+
| 右边边空格去除          #####        | 右边边空格去除#####        |
+--------------------------------------+----------------------------+

  同样的如果是数据库字段就把 s 换成你要操作的字段的列名就行了，不要加引号！！！，比如：

#去除名字里的空格演示
SELECT ltrim(emp_name),rtrim(emp_name),trim(emp_name) from tb_employee;

2.6、替换字符串

  替换字符串可以使用 REPLACE(s,s1,s2) 这里的 s 是原字符串， s1 就是要被替换的字符串， s2 就是用来替换 s1 的字符串，简单来说就是：把字符串 s 中的 s1 字符串替换为 s2 。

替换字符串

#把字符串aaabbbccc中b替换为x
SELECT REPLACE('aaabbbccc','b','x') as '替换后字符串';

+--------------------+
| 替换后字符串       |
+--------------------+
| aaaxxxccc          |
+--------------------+

2.7、填充字符串

填充字符串方法功能
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

  使用 LPAD(s1,len,s2) 或 RPAD(s1,len,s2) 可以分别在字符串里 s1 的左边或者右边添加字符串 s2 直到添加后的字符串的长度达到 len 。（其中 s1 是原字符串， s2 是要添加的字符串， len 是字符串规定的长度）
左边添加"#"号

#比如在前4个员工名字前面添加#符号，直到长度为10
SELECT lpad(emp_name,10,'#') from tb_employee limit 4; 

+-----------------------+
| lpad(emp_name,10,'#') |
+-----------------------+
| #######赖晓畅         |
| #######谢又蓝         |
| #######吕芸溪         |
| #######谭淑慧         |
+-----------------------+

右边添加"#"号

#比如在前4个员工名字后面添加#符号，直到长度为10
SELECT rpad(emp_name,10,'#') from tb_employee limit 4; 

+-----------------------+
| rpad(emp_name,10,'#') |
+-----------------------+
| 赖晓畅#######         |
| 谢又蓝#######         |
| 吕芸溪#######         |
| 谭淑慧#######         |
+-----------------------+

  这两个函数在定长的字段上会使用到，比如数据位为10位，不足的补零。

2.8、截取字符串

截取字符串方法功能
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
MID(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串

  使用 SUBSTR(s, start, length) 或 SUBSTRING(s, start, length) 或 MID(s,start,len) 可以从字符串里 s 的指定位置截取指定长度的字符串。（其中 s 是原字符串， start 是开始截取的位置（从1开始）， length 是截取的字符串的长度，长度不够或没有指定长度则是从指定截取到最后）

左边添加"#"号

#比如截取4个员工名字，从第2位开始
SELECT emp_name as '全名',substr(emp_name,2) as '简称' from tb_employee limit 4; 

+-----------+--------+
| 全名      | 简称   |
+-----------+--------+
| 赖晓畅    | 晓畅   |
| 谢又蓝    | 又蓝   |
| 吕芸溪    | 芸溪   |
| 谭淑慧    | 淑慧   |
+-----------+--------+

  三个函数差不多，一样的效果和使用，就不一一演示了

2.9、比较

  使用 STRCMP(s1,s2) 可以比较两个字符串的大小，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

SELECT STRCMP("Alian", "Alian") as '相等',
STRCMP("JavaScript", "Java") as '大于',
STRCMP("a", "b") as '小于',
STRCMP("alian", "Alian") as '不区分大小写';

+------------------+----------------------+----------+--------------------+
| Alian等于Alian   | JavaScript大于Java   | a小于b   | 不区分大小写       |
+------------------+----------------------+----------+--------------------+
|                0 |                    1 |       -1 |                  0 |
+------------------+----------------------+----------+--------------------+

三、数字函数

3.1、统计，求和，求平均值

方法功能
COUNT(expression) 返回查询的记录总数，expression 参数是一个字段 或者 * 号
SUM(expression) 返回指定字段的总和
AVG(expression) 返回一个表达式的平均值，expression 是一个字段

统计

  我们统计下部门编号为14员工的总数

#统计部门编号为14员工的总数
SELECT count(*) as '员工总数' from tb_employee where dep_code=14;
#或者
SELECT count(emp_code) as '员工总数' from tb_employee where dep_code=14;

+--------------+
| 员工总数     |
+--------------+
|           20 |
+--------------+

求和

  我们算下部门编号为14的员工的总工资

#部门编号为14的员工的平均工资
SELECT sum(salary) as '总工资' from tb_employee where dep_code=14;

+-----------+
| 总工资    |
+-----------+
| 298000.00 |
+-----------+

求平均值

  我们算下部门编号为14的员工的平均工资

#部门编号为14的员工的平均工资
SELECT avg(salary) as '平均工资' from tb_employee where dep_code=14;

+--------------+
| 平均工资     |
+--------------+
| 14900.000000 |
+--------------+

3.2、最值

  我们说的最值就是最大值和最小值，最大值使用 MAX(expression) ，最小值使用 MIN(expression) ，expression 参数是一个 字段

方法功能
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
LEAST(expr1, expr2, expr3, …) 返回列表中的最小值

字段最大值（纵向）

  查询员工表中部门编号为14的员工最高工资

#查询员工表中部门编号为14的员工最高工资
SELECT max(salary) as '最高工资' from tb_employee where dep_code=14;

+--------------+
| 最高工资     |
+--------------+
|     34000.00 |
+--------------+

列表最大值（横向）

#获取列表最大值
SELECT greatest(10,33,88,20) as '列表最大值';

+-----------------+
| 列表最大值      |
+-----------------+
|              88 |
+-----------------+

#我们演示下greatest的使用
select dep_code,age,greatest(dep_code*3,age) as '列表中最大值' from tb_employee where emp_code in(8000,8003);

+----------+------+--------------------+
| dep_code | age  | 列表中最大值       |
+----------+------+--------------------+
|       11 |   45 |                 45 |
|       12 |   32 |                 36 |
+----------+------+--------------------+
  • 第一条记录当部门编号11乘以3后等于33，还是比年龄45小，所以取值是45
  • 第二条记录部门编号12乘以3后等于36，它比年龄32大，所以取值是36
  • 取值是横向的，并不是固定某一列

字段最小值（纵向）

  查询员工表中部门编号为14的员工最低工资

#查询员工表中部门编号为14的员工最低工资
SELECT min(salary) as '最低工资' from tb_employee where dep_code=14;

+--------------+
| 最低工资     |
+--------------+
|      3000.00 |
+--------------+

列表最小值（横向）

#获取列表最小值
SELECT LEAST(10,33,88,20) as '列表最小值';

+-----------------+
| 列表最小值      |
+-----------------+
|              10 |
+-----------------+

  那如何查询部门编号为14最高工资的员工信息呢？用子查询就可以查到了

#查询部门编号为14的最高工资的员工的员工编号，姓名及工资
SELECT emp_code,emp_name,salary 
from tb_employee 
where salary=(SELECT max(salary) as '最高工资' from tb_employee where dep_code=14) 
and dep_code=14;

+----------+-----------+----------+
| emp_code | emp_name  | salary   |
+----------+-----------+----------+
|     8081 | 韩隽雅    | 34000.00 |
+----------+-----------+----------+

  需要注意的是指定了部门编号，子查询外面的条件（ and dep_code=14）不能少，不然查的就是全员工工资等于部门编号为14员工的最高工资的所有员工了。

3.3、取整

取整的方法功能
CEIL(x) 或 CEILING(x) 返回大于或等于 x 的最小整数， 向上取整
FLOOR(x) 返回小于或等于 x 的最大整数， 向下取整
ROUND(x) 返回离 x 最近的整数，也就是 四舍五入

向上取整

#比如分别对1.0,1.2,1.5,1.8向上取整
SELECT ceil(1.0),ceil(1.2), ceil(1.5), ceil(1.8);

+-----------+-----------+-----------+-----------+
| ceil(1.0) | ceil(1.2) | ceil(1.5) | ceil(1.8) |
+-----------+-----------+-----------+-----------+
|         1 |         2 |         2 |         2 |
+-----------+-----------+-----------+-----------+

向下取整

#比如分别对1.0,1.2,1.5,1.8向上取整
SELECT floor(1.0),floor(1.2), floor(1.5), floor(1.8);

+------------+------------+------------+------------+
| floor(1.0) | floor(1.2) | floor(1.5) | floor(1.8) |
+------------+------------+------------+------------+
|          1 |          1 |          1 |          1 |
+------------+------------+------------+------------+

四舍五入

#比如分别对1.0,1.2,1.5,1.8四舍五入
SELECT round(1.0),(1.2), round(1.5), round(1.8);

+------------+-----+------------+------------+
| round(1.0) | 1.2 | round(1.5) | round(1.8) |
+------------+-----+------------+------------+
|          1 | 1.2 |          2 |          2 |
+------------+-----+------------+------------+

  从上面三组结果大家就可以区分它们的作用了，最终都是得到整数，实际中根据需要选择即可。

3.4、随机数

  使用 RAND() 可以返回 0 到 1 的随机数。

#得到一个随机数
SELECT rand();

+---------------------+
| rand()              |
+---------------------+
| 0.05654980061254446 |
+---------------------+

  那怎么得到从m到n的随机数呢？比如5到1000的随机数

#获取从5到1000的随机数
SELECT round(rand()*(1000-5)+5) as '随机数';

+-----------+
| 随机数    |
+-----------+
|       549 |
+-----------+

3.5、数学函数

方法功能
ABS(x) 返回 x 的绝对值
MOD(x,y) 返回 x 除以 y 以后的余数　
POW(x,y) 或 POWER(x,y) 返回 x 的 y 次方
SQRT(x) 返回x的平方根
PI() 返回圆周率(3.141593）

  这个没什么好说的

SELECT abs(-1) as '-1的绝对值',
mod(5,3) as '5模3',
pow(2,3) as '2的3次方',
sqrt(-1) as '16的平方根',
pi() as '圆周率';

+----------------+-------+-------------+----------------+-----------+
| -1的绝对值     | 5模3  | 2的3次方    | 16的平方根     | 圆周率     |
+----------------+-------+-------------+----------------+-----------+
|              1 |     2 |           8 |           NULL |  3.141593 |
+----------------+-------+-------------+----------------+-----------+

3.6、三角函数

三角函数方法功能功能
SIN(x) sin A = 对边 / 斜边 求正弦值(参数是弧度)
COS(x) cos A = 邻边 / 斜边 求余弦值(参数是弧度)
TAN(x) tan A =对边 / 邻边 求正切值(参数是弧度)
COT(x) cot A= 邻边 / 对边 求余切值(参数是弧度)

  上面的A是角度，对边指的是角A的对边的长度，邻边指角A的邻边的长度，斜边

SELECT sin(30) as '正弦值',
cos(30) as '余弦值',
tan(30) as '正切值',
cot(30) as '余切值';

+---------------------+---------------------+--------------------+----------------------+
| 正弦值              | 余弦值              | 正切值             | 余切值               |
+---------------------+---------------------+--------------------+----------------------+
| -0.9880316240928618 | 0.15425144988758405 | -6.405331196646276 | -0.15611995216165922 |
+---------------------+---------------------+--------------------+----------------------+

四、日期函数

4.1、获取当前日期或当前时间

方法功能
LOCALTIME() 或 LOCALTIMESTAMP() 或 SYSDATE() 返回本地日期和时间
CURRENT_TIMESTAMP() 或 NOW() 返回当前日期和时间
CURRENT_DATE() 或 CURDATE() 返回当前日期
CURRENT_TIME() 或 CURTIME() 返回当前时间

  获取当前时间+日期，当前日期，当前时间

#获取当前时间+日期，当前日期，当前时间
SELECT now(),curdate(),curtime();

+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2022-03-08 08:54:59 | 2022-03-08 | 08:54:59  |
+---------------------+------------+-----------+

4.2、获取年月日时分秒

方法功能
YEAR(d) 返回年份值，d是日期部分
MONTH(d) 返回月份值，d是日期部分
DAY(d) 返回日期值，d 的日期部分
HOUR(d) 返回小时值，d 的日期部分
MINUTE(d) 返回分钟值，d 的日期部分
SECOND(d) 返回秒钟值，d 的日期部分
WEEK(d) 或 WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53

  获取年月日时分秒

SELECT 
year('2022-03-08 17:30:05') as '年',
month('2022-03-08 17:30:05') as '月',
day('2022-03-08 17:30:05') as '日',
hour('2022-03-08 17:30:05') as '时',
minute('2022-03-08 17:30:05') as '分',
second('2022-03-08 17:30:05') as '秒';

+------+------+------+------+------+------+
| 年   | 月   | 日   | 时   | 分   | 秒   |
+------+------+------+------+------+------+
| 2022 |    3 |    8 |   17 |   30 |    5 |
+------+------+------+------+------+------+

学以致用，实际中使用举例：

#获取员工编号为8000的入职的出生年月日
SELECT 
year(hire_date) as '年',
month(hire_date) as '月',
day(hire_date) as '日'
from tb_employee where emp_code=8000;

+------+------+------+
| 年   | 月   | 日   |
+------+------+------+
| 2022 |    3 |    5 |
+------+------+------+

4.3、获取天和周

方法功能
DAYOFWEEK(d) 计算日期d是一周中的第几天
DAYOFMONTH(d) 计算日期d是一月中的第几天
DAYOFYEAR(d) 计算日期d是一年中的第几天
LAST_DAY(d) 返回给定日期的那一月份的最后一天
WEEK(d) 或 WEEKOFYEAR(d) 计算日期d 是本年的第几个星期，范围是 0 到 53
WEEKDAY(d) 日期 d 是星期几，0 表示星期一，1 表示星期二
YEARWEEK(date, mode) 返回年份及第几周（0到53）
SELECT 
dayofweek('2022-03-08 17:30:05') as '一周中第几天',
dayofmonth('2022-03-08 17:30:05') as '一月中第几天',
dayofyear('2022-03-08 17:30:05') as '一年中第几天',
last_day('2022-03-08 17:30:05') as '月的最后一天',
week('2022-03-08 17:30:05') as '一年中第几周',
weekday('2022-03-08 17:30:05') as '星期几',
yearweek('2022-03-08 17:30:05') as '年周';

+--------------------+--------------------+--------------------+--------------------+--------------------+-----------+--------+
| 一周中第几天       | 一月中第几天        | 一年中第几天        | 月的最后一天       | 一年中第几周       | 星期几    | 年周   |
+--------------------+--------------------+--------------------+--------------------+--------------------+-----------+--------+
|                  3 |                  8 |                 67 | 2022-03-31         |                 10 |         1 | 202210 |
+--------------------+--------------------+--------------------+--------------------+--------------------+-----------+--------+

4.4、日期和时间的加减

方法功能
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
ADDTIME(t,n) n 是一个时间表达式，时间 t 加上时间表达式 n
SUBDATE(d,n) 日期 d 减去 n 天后的日期
SUBTIME(t,n) n 是一个时间表达式，时间 t 减去时间表达式 n
DATE_ADD(d,INTERVAL expr type) 计算起始日期d 加上一个时间段后的日期
DATE_SUB(d,INTERVAL expr type) 计算起始日期d 减去一个时间段后的日期

加减日期

#当前日期加一天和减一天
SELECT 
now() as '当前时间',
adddate(now(),1) as '当前时间加一天',
subdate(now(),1) as '当前时间减一天';

+---------------------+-----------------------+-----------------------+
| 当前时间            | 当前时间加一天        | 当前时间减一天        |
+---------------------+-----------------------+-----------------------+
| 2022-03-08 10:11:10 | 2022-03-09 10:11:10   | 2022-03-07 10:11:10   |
+---------------------+-----------------------+-----------------------+

加减时间

#当前时间加10秒和减10秒
SELECT 
now() as '当前时间',
addtime(now(),10) as '当前时间加10秒',
subtime(now(),10) as '当前时间减10秒';

+---------------------+----------------------+----------------------+
| 当前时间            | 当前时间加10秒       | 当前时间减10秒       |
+---------------------+----------------------+----------------------+
| 2022-03-08 10:13:35 | 2022-03-08 10:13:45  | 2022-03-08 10:13:25  |
+---------------------+----------------------+----------------------+

  加减时间还能使用表达式，注意表达式的格式，使用引号包裹

#当前时间加1小时2分3秒 和  当前时间减1小时2分3秒
SELECT 
now() as '当前时间',
addtime(now(),'1:2:3') as '当前时间加1小时2分3秒',
subtime(now(),'1:2:3') as '当前时间减1小时2分3秒';

+---------------------+--------------------------------+--------------------------------+
| 当前时间            | 当前时间加1小时2分3秒          | 当前时间减1小时2分3秒          |
+---------------------+--------------------------------+--------------------------------+
| 2022-03-08 10:16:10 | 2022-03-08 11:18:13            | 2022-03-08 09:14:07            |
+---------------------+--------------------------------+--------------------------------+

#计算一个日期加上一个时间段后的时间
SELECT 
now() as '当前时间',
DATE_ADD(now(), INTERVAL 1 year) as '加1年',
DATE_ADD(now(), INTERVAL 2 month) as '加2月',
DATE_ADD(now(), INTERVAL 3 day) as '加3天',
DATE_ADD(now(), INTERVAL 4 hour) as '加4小时',
DATE_ADD(now(), INTERVAL 5 minute) as '加5分钟',
DATE_ADD(now(), INTERVAL 6 second) as '加6秒钟';

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 当前时间            | 加1年               | 加2月               | 加3天               | 加4小时             | 加5分钟             | 加6秒钟             |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2022-03-08 12:16:47 | 2023-03-08 12:16:47 | 2022-05-08 12:16:47 | 2022-03-11 12:16:47 | 2022-03-08 16:16:47 | 2022-03-08 12:21:47 | 2022-03-08 12:16:53 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

#计算一个日期减去一个时间段后的时间
SELECT 
now() as '当前时间',
DATE_SUB(now(), INTERVAL 1 year) as '减1年',
DATE_SUB(now(), INTERVAL 2 month) as '减2月',
DATE_SUB(now(), INTERVAL 3 day) as '减3天',
DATE_SUB(now(), INTERVAL 4 hour) as '减4小时',
DATE_SUB(now(), INTERVAL 5 minute) as '减5分钟',
DATE_SUB(now(), INTERVAL 6 second) as '减6秒钟';

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 当前时间            | 减1年               | 减2月               | 减3天               | 减4小时             | 减5分钟             | 减6秒钟             |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2022-03-08 12:20:42 | 2021-03-08 12:20:42 | 2022-01-08 12:20:42 | 2022-03-05 12:20:42 | 2022-03-08 08:20:42 | 2022-03-08 12:15:42 | 2022-03-08 12:20:36 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

4.5、时间间隔

方法功能
TIMEDIFF(time1, time2) 计算时间差值，time1-time2
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数
PERIOD_DIFF(period1, period2) 返回两个时段之间的月份差值，period1-period2
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差
#两个时间的差值
SELECT 
timediff('2022-03-10 18:30:10','2022-02-08 17:30:05') as '时分秒差值',
datediff('2022-03-10 18:30:10','2022-02-08 17:30:05') as '天数差值',
period_diff('202209','202203') as '月份差值';

+-----------------+--------------+--------------+
| 时分秒差值      | 天数差值     | 月份差值      |
+-----------------+--------------+--------------+
| 721:00:05       |           30 |            6 |
+-----------------+--------------+--------------+
  • TIMEDIFF(time1, time2) 得到的结果只会有：时分秒，参数可以是年月日时分秒
  • DATEDIFF(d1,d2) 得到的结果只有整数天，参数可以是年月日时分秒
  • PERIOD_DIFF(period1, period2) 得到的结果只有整数月，注意格式有年月即可，不然你会得到你意想不到的结果

  一个更强大的方法

#计算时间差值
SELECT 
timestampdiff(year,'2020-03-10','2022-02-08') as '年数差',
timestampdiff(month,'2020-03-10','2022-02-08') as '月份差',
timestampdiff(day,'2022-03-10 18:30:10','2022-03-11 17:30:05') as '天数差',
timestampdiff(hour,'2022-03-10 18:30:10','2022-03-11 17:30:05') as '小时差',
timestampdiff(minute,'2022-03-10 18:30:10','2022-03-11 17:30:05') as '分钟差',
timestampdiff(second,'2022-03-10 18:30:10','2022-03-11 17:30:05') as '秒钟差';

+-----------+-----------+-----------+-----------+-----------+-----------+
| 年数差    | 月份差    | 天数差    | 小时差    | 分钟差    | 秒钟差    |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         1 |        22 |         0 |        22 |      1379 |     82795 |
+-----------+-----------+-----------+-----------+-----------+-----------+

4.6、时间转化

方法功能
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式
TIME_TO_SEC(t) 将以秒为单位的时间 s 转换为时分秒的格式
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数

  时间和秒之间的互转

#时间和秒之间的互转
SELECT 
SEC_TO_TIME(3600) '3600秒转时间',
TIME_TO_SEC('1:00:00') as '时间(1:00:00)转秒';

+------------------+-----------------------+
| 3600秒转时间     | 时间(1:00:00)转秒     |
+------------------+-----------------------+
| 01:00:00         |                  3600 |
+------------------+-----------------------+

  0000 年 1 月 1 日开始后的日期与天进行互转

SELECT 
FROM_DAYS(4383) as '4383天转特定日期',
TO_DAYS('0012-01-01') '特定日期转天';

+------------------------+--------------------+
| 4383天转特定日期        | 特定日期转天       |
+------------------------+--------------------+
| 0012-01-01             |               4383 |
+------------------------+--------------------+

  然后你是不是觉得这个没有啥用啊？来来来，搞个实用的，假设我们知道员工是2008年6月6日出生的，那么他多少岁了呢？首先要要用到我们之前的知识： DATEDIFF(d1,d2) 

#可以得他出生了多少天
SELECT datediff(now(), '2008-06-06'));

  接下里，就可以用到刚刚学的知识了： FROM_DAYS(n) 

#转为特殊的日期
SELECT FROM_DAYS(datediff(now(), '2008-06-06')));

  然后就可以用之前的： YEAR(d) 

#从日期得到年龄
SELECT year(FROM_DAYS(datediff(now(), '2008-06-06'))) as '年龄';

+--------+
| 年龄   |
+--------+
|     13 |
+--------+

  本文写的时候是2022年3月，所以2008年6月6日出生的就是13岁，没错吧。你看是不是组合起来很强大？当然还有其他更简单的方式：

#得到年龄
SELECT  TIMESTAMPDIFF(YEAR, '2008-06-06', CURDATE()) AS age

4.7、时间格式化

方法功能
STR_TO_DATE(string, format_mask) 计算从 0000 年 1 月 1 日开始 n 天后的日期
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d
TIME_FORMAT(t,f) 返回
#时间字符串转时间
select 
str_to_date('03/01/2022','%m/%d/%Y') as '格式一',
str_to_date('20220308221006','%Y%m%d%H%i%s') as '格式二',
str_to_date('2022-03-12 15:47:08','%Y-%m-%d%H:%i:%s') as '格式三';

+------------+---------------------+---------------------+
| 格式一     | 格式二              | 格式三              |
+------------+---------------------+---------------------+
| 2022-03-01 | 2022-03-08 22:10:06 | 2022-03-12 15:47:08 |
+------------+---------------------+---------------------+

#时间格式化
SELECT DATE_FORMAT('2022-02-22 22:22:22','%Y-%m-%d %H:%i:%s') as '标准转化',
 DATE_FORMAT('2022-02-22 22:22:22','%Y年%m月%d日%H时%i分%s秒') as '自定义转化';

+---------------------+----------------------------------+
| 标准转化            | 自定义转化                       |
+---------------------+----------------------------------+
| 2022-02-22 22:22:22 | 2022年02月22日22时22分22秒       |
+---------------------+----------------------------------+

4.8、提取时间元素

  使用 EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。

  比如想获取’2022-03-08 22:00:08’的年月日时分秒

select
EXTRACT(year from '2022-03-08 22:00:08') as '年',
EXTRACT(month from '2022-03-08 22:00:08') as '月',
EXTRACT(day from '2022-03-08 22:00:08') as '日',
EXTRACT(hour from '2022-03-08 22:00:08') as '时',
EXTRACT(minute from '2022-03-08 22:00:08') as '分',
EXTRACT(second from '2022-03-08 22:00:08') as '秒';

+------+------+------+------+------+------+
| 年   | 月   | 日   | 时   | 分   | 秒   |
+------+------+------+------+------+------+
| 2022 |    3 |    8 |   22 |    0 |    8 |
+------+------+------+------+------+------+

  还有很多的类型可以去提取的，可以去官网查看就不一一列举了。

五、高级函数

5.1、IF(expr,v1,v2)

  使用 IF(expr,v1,v2) 当表达式expr的值为true时，返回v1，否则返回v2。比如你数据库性别字段男女存的是1和0,1表示男，0表示女，现在查询出结果显示男女。

SELECT emp_name as '姓名',if(gender='1','男','女') as '性别' FROM tb_employee limit 4;

+-----------+--------+
| 姓名      | 性别   |
+-----------+--------+
| 赖晓畅    | 男     |
| 谢又蓝    | 男     |
| 吕芸溪    | 女     |
| 谭淑慧    | 女     |
+-----------+--------+

5.2、IFNULL(v1,v2)

  使用 IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。就相当于没有值就设置默认值一样的。

#查询员工的岗位信息，没有则显示未分配
SELECT 
emp_name as '员工姓名',
ifnull(job,'未分配') as '岗位信息'
FROM tb_employee 
where emp_code in(8000,8001);

+--------------+--------------+
| 员工姓名     | 岗位信息     |
+--------------+--------------+
| 赖晓畅       | 未分配       |
| 谢又蓝       | 技术总监     |
+--------------+--------------+

5.3、COALESCE(expr1, expr2, …, expr_n)

  使用 COALESCE(expr1, expr2, …, expr_n) 可以返回参数中的第一个非空表达式（从左向右）

select COALESCE(null,2);

你感觉看起来没有啥用对不？实际上会有很多的场景，假设数据设置了一个int型的字段，但是没有设置默认值，那么就是null了，这个时候如果用sum时，那么结果也就是null，就会需要做很多的数据处理，甚至业务异常了。但是这个时候如果给一个0的默认值，问题自然就没有了。简单使用如下：

#工资如果
select COALESCE(sum(salary),0) as tt from tb_employee;

学习计划：

嘉禾嘉宁papa
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【DQL】总结（四）：常见函数
Awt_FuDongLai的博客
10-03 176
文章目录1 常见函数1.1 概念:1.2 好处:1.3 语法1.4 分类:1.4.1 单行函数1.4.2 分组函数2 单行函数2.1 字符函数2.1.1 length2.1.2 concat2.1.3 upper / lower2.1.4 substr / substring3参考资料 1 常见函数 1.1 概念: 类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 1.2 好处: 1、隐藏了实现细节 2、提高代码的重用性 1.3 语法 select 函数名(实参列表)【from 表】;
MYSQL（DQL数据查询语言上）
月亮投递员的博客
06-16 248
DQL数据查询语言前言DQL1、概念2、特点3、基本的SELECT语句4、过滤和排序数据5、分组查询总结 前言 今天为大家分享的主要内容是DQL,也就是数据查询语言，一起来看看吧！ DQL 1、概念 DQL(Data Query Language)数据查询语言，基本结构是由SELECT子句、FROM子句、WHERE子句组成的查询块。 2、特点 1、查询列表可以是：表中的字段、常量值、表达式、函数 2、查询的结果是一个虚拟的表格 3、基本的SELECT语句 1、查询表中的单个字段名 SELECT
强化学习（八） - 深度Q学习(Deep Q-learning, DQL,DQN)原理及相关实例
热门推荐
Stan_Fu的博客
11-13 1万+
深度Q学习将深度学习和强化学习相结合，是第一个深度强化学习算法。深度Q学习的核心就是用一个人工神经网来代替动作价值函数。由于神经网络具有强大的表达能力，能够自动寻找特征，所以采用神经网络有潜力比传统人工特征强大得多。。最近基于深度Q网络的深度强化学习算法有了重大的进展，在目前学术界有非常大的影响力。当同时出现异策、自益和函数近似时，无法保证收敛性，会出现训练不稳定或训练困难等问题...
mysql数据库DQL语句回顾（包括常用函数）
雪落无痕的博客
07-19 559
DQL语句dql语句LIMIT三级目录 dql语句 语法： select <字段名列表> from <表名或视图> [where <查询条件>] [group by <分组的字段名>] [having <条件>] [order by <排序的字段名>[ASC 或 DESC]]； 其中： 字段名列表为要查询的字段名，各字段之间使用逗号分隔，若查询表中所有字段，则使用“*”号表示。 from 后的表名为要查询的数据的来源，可以单个或者多个
MySQL | DQL—常见函数
我要学Java_blog
09-15 148
一、概述 功能：类似于java中的方法 好处：提高重用性和隐藏实现细节 调用：select 函数名(实参列表); 二、单行函数 1、字符函数 concat:连接 substr:截取子串 upper:变大写 lower：变小写 replace：替换 length：获取字节长度 trim:去前后空格 lpad：左填充 rpad：右填充 instr:获取子串第一次出现的索引 2、数学函数 ceil:向上取整 round：四舍五入 mod:取模 floor：向下取整 truncate:截断 rand:获取随机数，返
MySql中DQL常用单行函数介绍
weixin_44174358的博客
05-08 121
DQL常用单行函数介绍 字符函数 length: 返回长度(使用的是utf-8编码，一个中文占用3个字节大小) SELECT LENGTH('JOHN') AS LENGTH; SELECT LENGTH('周') AS LENGTH; PS：在utf-8中，中文占用的字节数是3个字节，但是在GBK编码中中文占用2个字节 concat:用于拼接字符串 select concat('john','_','zhou') Upper函数和Lower函数:转为大写或者小写 SELECT
MySQL数据查询语言-DQL
qq_72561574的博客
09-15 263
MySQL数据查询语言-DQL语句
一、MySQL查询学习笔记（基础查询、条件查询、排序查询、常见函数、分组查询 详解）
12-14
DQL语言的学习 1：基础查询 语法： SELECT 要查询的东西 【FROM 表名】; 类似于Java中 :System.out.println(要打印的东西); 特点： ①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在 ② 要查询的东西 ...
PHP连接MySQL数据的操作要点
12-19
- **DQL (Data Query Language)**：用于查询数据，最常用的是`SELECT`语句。 - **DTL (Data Transaction Language)**：用于管理数据库事务，如`BEGIN`, `COMMIT`, `ROLLBACK`。 ```php $sql = "select * from ...
mysql-8.0.11-winx64.rar
05-09
4. **SQL语法**：MySQL 8.0支持标准的SQL语言，包括DDL（数据定义语言）、DML（数据操纵语言）、DQL（数据查询语言）和DCL（数据控制语言）。例如，你可以使用CREATE TABLE来创建表，INSERT来插入数据，SELECT来查询...
MySQL学习：表中数据的CRUD
12-14
MySQL是世界上最流行的开源关系型数据库管理系统之一，它提供了丰富的数据操作语言（DML）用于对表中的数据进行增、删、改以及查询（DQL）。本文将深入探讨这些基本操作。 **1. 添加数据（Create）** 在MySQL中，...
mysql-5.7.23-win32.zip
02-28
在使用MySQL时，了解SQL语言的基础是必不可少的，包括数据定义（DDL）、数据操纵（DML）、数据查询（DQL）和数据控制（DCL）语句。同时，熟悉如何创建表、插入数据、更新数据、删除数据、建立索引、编写存储过程和...
MySQL面试篇章——MySQL核心SQL（基本语法）
itzzan的博客
07-17 944
扩展：group by 查询慢，explain 分析发现是 Using temporary，即产生了一个临时表，因此最好给分组列添加索引。数据控制语句，用于控制不同的许可和访问级别的语句。每次的 insert 操作都会有一个数据库连接操作，是占用资源的，一次连接完成的事情肯定比多次连接完成的事情所花费的时间短。数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括。低，pageNum的偏移所花费的性能，要先偏移到对应的位置，在进行分页拿去后面的数据记录。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 471
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL：SELECT 语句
Zachyy的博客
07-20 333
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 677
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
mysql中ONLY_FULL_GROUP_BY报错
最新发布
zzzzzucc的博客
07-21 134
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
xzzteach的博客
07-21 183
【代码】Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
Mysql的insert批量插入性能问题
m0_54123999的博客
07-17 233
同时分批插入也有数据一致性风险，如果在批次中途出现失败，部分数据可能已经插入，而部分数据可能未插入，需要额外处理数据一致性；当需要往数据库插入大量数据是，一条一条插入会导致插入时间长，性能不佳，所以我们一般会进行insert批量插入，一次性插入较大批次的数据，而不是每条记录单独插入，这通常会提高性能，因为批量操作减少了上下文切换和 SQL 解析的开销。事务期间，所有涉及的表或行会被锁定（根据具体的存储引擎），可能会导致更长时间的锁定，从而影响系统的并发性能，且资源占用高，大事务可能会占用较多的数据库资源。
2024年MySQL-DQL进阶教程：单表与多表查询详解
DQL，即数据查询语言，是SQL家族的核心组成部分，主要用于从数据库表中检索数据。在实际的数据库开发中，查询操作占据了主导地位，因为它们负责呈现用户所需的信息，无论是网站内容的显示还是应用程序的数据获取。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值