【MYSQL】MYSQL 主要函数

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数

​MySQL函数是MySQL数据库提供的内部函数。这些内部函数可以帮助用户更加方便的处理表中的数据

MySQL函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。这些函数可以使MySQL数据库的功能更加强大

一、字符串函数

由于业务需求变更，工号，统一为6位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为0000a1

update customer set cust_id = lpad(cust_id, 6, '0')

select * from customer

二、数值函数

round：四舍五入

 select round(6.66666666,6) '四舍五入';

生成一个六位数的随机验证码

思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

select lpad(round(rand()*1000000 , 0), 6, '0')

三、日期函数

1、curdate：当前日期

select curdate();

2、 curtime：当前时间

select curtime();

3、 now：当前日期和时间

select now();

4、YEAR , MONTH , DAY：当前年、月、日

select YEAR(now());
select MONTH(now());
select DAY(now());

select quarter(curdate()) AS '当前季度'

select weekofyear(curdate()) AS '当前周数'

select YEAR(now()) '年',MONTH(now())'月',DAY(now())'日';

获取年月日

获取当前季度

获取当前周数

5、 date_add：增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR );

6、datediff：获取两个日期相差的天数

select datediff('2021-10-01', '2021-12-01');

6.1 查询所有员工的入职天数，并根据入职天数倒序排序

思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

SELECT NAME AS '姓名',
	datediff( curdate(), entrydate ) AS 'entrydays' 
FROM
	emp 
ORDER BY
	entrydays DESC;

6.2 了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数

2021年8月份，匹配date字段即可，匹配方法主要有三种：

（1）like语法：date like “2021-08%”
（2）year、month函数：year(date)=‘2021’ and month(date)=‘08’；
（3）date_format函数：date_format(date, ‘%Y-%m’)=‘2021-08’;

select
count(distinct device_id)did_cnt,
count(question_id)question_cnt
from question_practice_detail
where date_format(date,'%Y-%m')= '2021-08'
# where date like '%2021-08%'

计算出2021年8月每天用户练习题目的数量


关键在于：使用日期函数取出8月的每天，day(date) day

select 
day(date) day,
count(question_id) question_cnt
from question_practice_detail
where date like '%2021-08%'
group by day

7、获取两个日期之间的天数

SELECT DATEDIFF('2023-09-06','2023-01-06')

四、流程函数

case when then else end

需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

SELECT NAME AS '姓名',
	( CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END ) AS '工作地址' 
FROM
	emp;

SELECT
	id,
	NAME,
	( CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END ) '数学',
	( CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END ) '英语',
	( CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END ) '语文' 
FROM
	score;

五、模拟所需SQL语句

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NULL DEFAULT NULL COMMENT '编号',
  `workno` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '工号',
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '性别',
  `age` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
  `idcard` char(18) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '身份证号',
  `workaddress` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '工作地址',
  `entrydate` date NULL DEFAULT NULL COMMENT '入职时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '员工表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO `emp` VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO `emp` VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO `emp` VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO `emp` VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO `emp` VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO `emp` VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO `emp` VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO `emp` VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO `emp` VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO `emp` VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO `emp` VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO `emp` VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO `emp` VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO `emp` VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO `emp` VALUES (16, '00016', '周芷若', '女', 18, NULL, '北京', '2012-06-01');

SET FOREIGN_KEY_CHECKS = 1;