数据库查询-常用函数

1、lower 转换小写

select lower('Abc') as x;       select lower('ABC') as x;

查询：员工name，用小写显示

select name,lower(name) name2 from emp;

2、upper 转换大写  as 可省略  title：自定义的列名

select upper('abc') as title;

3、substr：取子串（substr（被截取的字符串，起始下标，截取的长度））

select substr('123456,2,3) x;

取出员工的中文姓名的第一个字

select name,substr(name,1,1) name2 from emp;

4、rand() 生成随机数

select rand() x;

查询：员工信息（姓名，工资，随机数列）

select name,sal,rand() as rand_col from emp;

应用：随机数通常与order by + limit 一起使用    例如：随机查询5个员工

select name,sal,from emp order by rand() limit 5;

5、round() 四舍五入

select round(12345.67) x;   结果：123456

select round(12345.678,2) x;   结果：12345.68

员工工资，精确到1位小数

select name,sal,round(sal,1) sal2 from emp;

6、ifnull(a,b)：当a为null时，就返回b的值

查询:员工姓名、工资和奖金信息：select cname, sal,comm from emp;

查询:奖金为空的员工：select cname,sal, comm from emp where comm is null;

能查询几条记录?6--查看:奖金为空的员工， sal+comm以后的结果是?    select cname, sal, comm, sal+comm as total from emp where comm is null;

说明: null不能用=、!=判断，也不能参与计算（计算结果永远为null)    解决:将comm为空的当作日。

select cname , sal, comm, ifnull(comm,0) comm2 sal+ifnull( comm,0) as total from emp where comm is null;

isNull(a):如果a的值为null则返回1否则返回日

select ename,sal, comm,ifnull(comm,0) comm2,isnull( comm) comm3 from emp;

面试:ifnul1与isnull的区别?

7、长度：length(),BIT_LENGTH()

select name,length(name) len_1,BIT_LENGTH(name) len_2 from emp;

select length('我爱java') len1,BIT_LENGTH('我爱java') len2,CHAR_LENGTH('我爱java') len3;

经常查询字段中有几个字符个数，所以常用的是CHAR_LENGTH

8、timestampdiff（查询两个时间之差）

select timestampdiff(day,'2022-6-1',curdate()) x;

查询各位员工雇佣日期和已入职天数（假设都未离职）；改为：年数

select name,hiredate,timestampdiff(year,hiredate,curdate()) x from emp;

9、转换函数

字符串转日期：select date('2022-3-12') x;

字符串转日期时间：select date('2022-1-12 12:12:12) x;

日期转整数：select unix_timestamp(now()) x;                     select unix_timestamp('2022-1-12 12:12:12') x;

整数转日期：select FEOM_UNIXTIME(142532132) x;

说明：1、在有的项目中，日期字段不用datetime类型，而是用long类型，用来保存时间戳对应的整数                2、查询效率要高些，但是可读性要低些

10、计算函数

增加或减少指定天数或月数：1、DATE_ADD(date,interval 数字 单位)   2、DATE_SUB(date,interval 数字 单位)

查询：当前时间，七天前，一个月后：select now() a,     date_add(now(),interval -7 day) b,     date_add(now(),interval 1 month) c;

说明：项目中经常用于查询一周内注册的新用户：select * from users where create_time>=date_add(now(),interval - 7 day);

11、case函数

语法：case when 条件1 then 值1 when 条件2 then 值2...else end

查询：员工工资1000以内显示p1,2000内显示p2,3000内显示p3，其他的显示p4

select empno,name,sal,

case when sal<=1000 then 'p1'

        when sal<=2000 then 'p2'

        when sal<=3000 then 'p3'

        else 'p4'

end as p_rank

from emp;

建议总是带上else部分，这样可以保证所有数据都有对应标签