#四.常见函数
#4.1 字符串函数
-- 1、CONCAT 拼接字符
#CONCAT(S1,S2,......,Sn)连接S1,S2,......,Sn为一个字符串
select concat(first_name,' & ',last_name) as name from employees;
select concat(first_name , ' _ ' , last_name) as name from employees;
#CONCAT_WS(s, S1,S2,......,Sn)同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上s
select CONCAT_WS('#','a','b','c');
select CONCAT_WS('*','A','B','C','D');
#模糊查询员工姓名中包含字符a
select first_name from employees where first_name like '%a%';
select first_name from employees where first_name like concat('%','a','%'); -- 用的多
-- 2、LENGTH 获取字节长度
select length('abc'); -- 3
select length('你好'); -- 3+3=6个字节 一个UTF-8的字符(中文)占三个字节
-- 3、CHAR_LENGTH 获取字符个数
select char_length('abc'); -- 3
select char_length('你好'); -- 2个字符
-- 4、SUBSTRING 截取子串 注意:起始索引从1开始
#SUBSTRING(str,pos,len) str将要截取的字符串,pos起始下标,len截取的长度
select first_name , SUBSTRING(first_name,2,3) from employees;
select substring(last_name,2,5) from employees;
#SUBSTRING(str,pos) str将要截取的字符串,pos起始下标,默认截取至最后
select first_name , SUBSTRING(first_name,2) from employees;
-- 5、INSTR获取字符第一次出现的索引 注意:起始索引从1开始,若未查询到则直接返回0
#INSTR(str,substr) 查询子串substr在字符串str出现的索引位置
select first_name,INSTR(first_name,'a') from employees;
select last_name , instr(last_name,'b') from employees;
-- 6、TRIM去前后指定的字符,默认是去空格
#TRIM(s) 去掉字符串s开始与结尾的空格
select concat('###',' aaa ','###'),concat('###',trim(' aaa '),'###');
#LTRIM(s) 去掉字符串s左侧的空格
select concat('###',' aaa ','###'),concat('###',LTRIM(' aaa '),'###');
#RTRIM(s) 去掉字符串s右侧的空格
select concat('###',' aaa ','###'),concat('###',RTRIM(' aaa '),'###');
#TRIM(【BOTH 】s1 FROM s) 去掉字符串s开始与结尾的s1
select TRIM(BOTH '$' from '$$$aaa$$$');
select trim(both '*' from '**aa**bb**cc****'); -- aa**bb**cc
#TRIM(【LEADING】s1 FROM s) 去掉字符串s开始处的s1
select TRIM(LEADING '$' from '$$$aaa$$$');
select trim(leading '*' from '**aa**bb**cc****'); -- aa**bb**cc****
#TRIM(【TRAILING】s1 FROM s) 去掉字符串s结尾处的s1
select TRIM(TRAILING '$' from '$$$aaa$$$');
select trim(trailing '*' from '**aa**bb**cc****'); -- **aa**bb**cc
-- 7、LPAD/RPAD 左填充/右填充
#LPAD(str,len,padstr) 使用指定的padstr,在str字符串的左边填充数据,直至长度为len为止
select LPAD('aaa',10,'#');
select lpad('abc',10,'&*^');
#RPAD(str,len,padstr) 使用指定的padstr,在str字符串的右边填充数据,直至长度为len为止
select RPAD('aaa',6,'#');
select rpad('abcc',9,'!@'); -- abcc!@!@!
-- 8、UPPER/LOWER 变大写/变小写
#案例:查询员工表的姓名,要求格式:姓首字符大写,其他字符小写,名所有字符大写,且姓和名之间用_分割,最后起别名“OUTPUT”
select first_name , UPPER(first_name) '大写' from employees;
select first_name , LOWER(first_name) '小写' from employees;
select first_name , last_name , concat(UPPER(substring(last_name,1,1)) , LOWER(substring(last_name,2)) ,'_' , UPPER(first_name)) 'OUTPUT' from employees;
-- 9、STRCMP 比较两个字符大小
select STRCMP('aaa','abc');
select STRCMP('aaa','AAA');
select STRCMP('aaa','aaaa');
select strcmp('a','b'); -- -1
select strcmp('A','A'); -- 0
select strcmp('aa','cc'); -- -1
-- 10、LEFT/RIGHT 截取子串
#LEFT(s,n) 返回字符串s最左边的n个字符
select LEFT('aaabbb',3);
select left('update',5); -- updat
#RIGHT(s,n) 返回字符串s最右边的n个字符
select RIGHT('aaabbb',3);
select right('abcd',2); -- cd
-- 11、INSERT(str, index , len, instr) 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr
select INSERT('aaabbbccc',4,3,'BBB');
select insert('employees',2,5,'xxx'); -- exxxees
-- REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
select REPLACE('你好你不好','你','我我我');
select replace('hhh好吗','h好','真的'); -- hh真的吗
-- 12、REPEAT(str, n) 返回str重复n次的结果
select REPEAT('abc',3);
select repeat('xiba你',2);
#4.2 数学函数
#二、数学函数
-- 1、ABS 绝对值
select ABS(-12) , ABS(0) , ABS(10);
-- 2、CEIL 向上取整 返回>=该参数的最小整数
select CEIL(12.3) , CEIL(12.7) , CEIL(-12.5);
select ceil(50.999), ceiling(50.011); -- 51 51
-- 3、FLOOR 向下取整,返回<=该参数的最大整数
select FLOOR(12.3) , FLOOR(12.7) , FLOOR(-12.5);
-- 4、ROUND 四舍五入
select ROUND(12.2) , ROUND(12.5) , ROUND(12.45,1);
-- 5、TRUNCATE 截断
select TRUNCATE(12.2,0) , TRUNCATE(12.5,0) , TRUNCATE(12.45,1);
select truncate(15.111,2) , truncate(15,1), TRUNCATE(-99.99,1); -- 15.11 15 -99.9
-- 6、MOD 取余
select MOD(10,3);
#4.3 日期时间函数
-- 1、NOW 系统当前时间
select SYSDATE();
select NOW(); //获取系统当前时间 2021-11-01 19:57:06
select YEAR(now()) as 年;
select MONTH(now()) as 月;
select DAY(now()) as 日;
select HOUR(now()) as 小时;
select MINUTE(now()) as 分钟;
select SECOND(now()) as 秒;
-- WEEKDAY(date)返回指定的时间是周几,注意,周1是0,周2是1,。。。周日是6
select WEEKDAY(now()) as 星期;
-- DAYOFWEEK()返回当前是周几,注意:周日是1,周一是2,。。。周六是7
select DAYOFWEEK(now()) as 星期;
select concat('星期' ,
case dayofweek(now())
when 1 then '日'
when 2 then '一'
when 3 then '二'
when 4 then '三'
when 5 then '四'
when 6 then '五'
when 7 then '六'
end)as '星期';
-- DAYNAME(date)返回指定的时间是星期几:MONDAY,TUESDAY.....SUNDAY
select DAYNAME(now());
-- MONTHNAME(date)返回指定的时间的月份:January,。。。。。
select MONTHNAME(NOW());
-- WEEK(date)返回一年中的第几周 WEEKOFYEAR(date)
select WEEK(now()) , WEEKOFYEAR(now());
-- 2、CURDATE 系统当前日期
-- 3、CURTIME 系统当前时间
select CURDATE();
select CURTIME();
select CURRENT_DATE();
select CURRENT_TIME();
select CURRENT_TIMESTAMP();
-- 4、DATEDIFF(date1,date2) 返回date1 - date2的日期间隔
-- TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
select DATEDIFF('2020-10-10',NOW());
select DATEDIFF('2020-10-10','2020-11-11');
select TIMEDIFF('16:24:43','16:24:24');
select DATEDIFF('2020-10-10',CURDATE());
select TIMEDIFF('16:24:43',CURTIME());
-- 5、DATE_FORMAT(datetime ,fmt) 按照字符串fmt格式化日期datetime值
#案例:查看100号员工入职日期
select employee_id , hiredate , DATE_FORMAT(hiredate,'%y-%m-%d %H:%i:%s 星期%w') from employees where employee_id=100;
select hiredate , DATE_FORMAT(hiredate,'%Y_%m_%d %H_%i_%S 星期:%W') from employees; -- 1992_04_03 00_00_00 星期:Friday
-- 6、STR_TO_DATE 按指定格式解析字符串为日期类型
#案例:查看1998年6月以前入职的员工信息
select employee_id , hiredate
from employees
where hiredate < STR_TO_DATE('1998年6月','%Y年%m月');
select hiredate
from employees
where hiredate < STR_TO_DATE('1998年5月30日','%Y年%c月');
-- 7、DATE_ADD(datetime, INTERVALE expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
/*
使用 DATE_ADD(NOW(),INTERVAL 1 MONTH) 这个函数来进行修改时间
第一个参数是要修改的时间;
第二个参数固定写法;
第三个参数的修改的值 : 如果正数就是加,负数就是减;
第四个参数可填YEAR,MONTH,DAY,HOUR,MINUTE,SECOND;
*/
select now() , DATE_ADD(now(),INTERVAL 1 MONTH);
select now() , DATE_ADD(now(),INTERVAL -1 DAY);
select now() , DATE_ADD(now(),INTERVAL 1 HOUR);
SELECT NOW() , DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);
select now() , date_add(now(),INTERVAL -2 month);
#4.4 流程控制函数
-- 1、IF函数
#需求:如果有奖金,则显示最终奖金,如果没有,则显示0
#IF(expr1,expr2,expr3) expr1条件,expr1成立则显示expr2的值,否则显示expr3的值
select commission_pct , if(commission_pct is null ,0 , commission_pct) from employees;
select manager_id , if(manager_id is null,'Boss',manager_id) from employees;
select first_name , salary , if(salary>10000,'白领','蓝领') from employees;
#IFNULL(expr1,expr2) 若expr1为null,则显示expr2值,若不为null则显示自身的值
select commission_pct , IFNULL(commission_pct,0) from employees;
select manager_id , IFNULL(manager_id,'Boss') from employees;
select manager_id , IFNULL(manager_id,'Boss') from employees;
-- 2、CASE函数
/*
情况1 :类似于switch语句,可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END
*/
#案例:部门编号是30,工资显示为2倍;部门编号是50,工资显示为3倍;部门编号是60,工资显示为4倍;否则不变;
#显示部门编号,新工资,旧工资
select
department_id '部门编号' ,
salary '旧工资' ,
case department_id
when 30 then salary*2
when 50 then salary*3
when 60 then salary*4
else salary
end as 'newSalary'
from employees;
/*
情况2:类似于多重IF语句,实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END
*/
#案例:如果工资>20000,显示级别A;工资>15000,显示级别B;工资>10000,显示级别C;否则,显示D
select
salary as '工资',
case
when salary>20000 then '级别A'
when salary>15000 then '级别B'
when salary>10000 then '级别C'
else '级别D'
end as 'level'
from employees;
#4.5 分组函数
-- 案例:查询员工信息表中,所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
select sum(salary) '工资和' , avg(salary) '工资平均值' ,
min(salary) '最低工资' , max(salary) '最高工资' , count(salary)
from employees;
-- count() 函数
#添加筛选条件
-- 案例1:查询emp表中记录数
select count(*) from employees;
-- 案例2:查询emp表中有佣金的人数 count()会自动去除null值
select count(commission_pct) from employees;
-- 案例3:查询emp表中月薪大于2500的人数
select count(first_name) from employees where salary>2500;
-- 案例4:查询有领导的人数
select count(manager_id) from employees;
#其它用途
-- 案例5:统计结果集的行数,推荐使用count(*)。需求:查询员工表中30号部门的人数
select count(*) from employees where department_id=30;
-- 案例6:搭配distinct实现去重的统计。需求:查询有员工的部门个数
select count(distinct department_id) from employees;
-- SUM() 求和函数
-- 案例7:查询所有员工月薪和
select sum(salary) from employees;
-- AVG() 平均值函数
-- 案例8:统计所有员工平均工资
select AVG(salary) from employees;
-- MAX() 和 MIN() 最大值和最小值函数
-- 案例9:查询最高工资和最低工资
select MAX(salary) from employees;
select AVG(salary) from employees;
#4.6 作业
-- 1. 显示系统时间(注:日期+时间)
select concat(now(),' 星期',weekday(now())+1);
-- 2. 查询员工号,姓名,工资,以及工资提高百分之 20%后的结果(new salary)
select department_id , first_name , salary , salary*(1+0.2) as newSalary
from employees;
-- 3. 将员工的姓名按首字母排序,并写出姓名的长度(length)
select first_name , length(first_name) as length
from employees
order by substring(first_name,1,2);
-- 4. 做一个查询,产生下面的结果
-- <last_name> earns <salary> monthly but wants <salary*3>
-- Dream Salary
-- King earns 24000 monthly but wants 72000
select concat(last_name , ' earns ' , salary , ' monthly but wants ' , salary*3 )
from employees;
-- 5. 使用 case-when,按照下面的条件:
-- job grade
-- AD_PRES A
-- ST_MAN B
-- IT_PROG C
-- SA_REP D
-- ST_CLERK E
-- 产生下面的结果
-- Last_name Job_id Grade
-- king AD_PRES A
select
last_name ,
job_id ,
case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E'
else 'Other'
end as 'grade'
from employees;
-- 1. 查询公司员工工资的最大值,最小值,平均值,总和
select first_name , max(salary) as max , min(salary) as min , avg(salary) as avg , sum(salary) as sum
from employees;
-- 2. 查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)
select max(hiredate) , min(hiredate) ,abs(datediff(max(hiredate),min(hiredate))) as '相差天数'
from employees;
-- 3. 查询部门编号为 90 的员工个数
select count(department_id)
from employees
where department_id = 90;
MySQL(4.常见函数)
最新推荐文章于 2023-06-07 16:10:57 发布