二、数据库-基础函数

1.基础函数语法

1.lower 数据转小写
select 'ABC',lower('ABC') from dept;

2.upper 数据转大写
select upper(字段名) from dept;

3.length 数据的长度
select length(字段名) from dept;

4.substr 截取
select dname substr(dname,1,3) from dept;

5.concat 拼接
select dname,concat(dname,'123') x from dept;

6.replace 替换
select dname,replace(dname,'a','666') x from dept; 把字符a替换成666

7.ifnull 
select ifnull(comm,10) comm from dept; 判断如果comm是null，用10替换

8.round四舍五入，ceil向上取整，floor向下取整
select comm，round(comm) from emp
select comm,found(comm,1) from emp  //保留一位小数
select comm,ceil(comm) from emp
select comm,floor(comm) from emp

9.now
select now()--年与日 时分秒
select curdate()--年与日
select curtime()--时分秒

时分秒
select now(),hour(now()),minute(now()),second(now()) from emp ;

年月日
select now(),year(now()),month(now()),day(now()) from emp ;

2.条件查询

distinct去重
select distinct loc from dept

like通配符%
select * from emp where ename like '1%'
select * from emp where ename like '%a'
select * from emp where enmae like '%a%'

null
select * from emp where mgr is null 过滤字段值为空的
select * from emp where mgr is not null 过滤字段值不为空的

between and 
SELECT * FROM emp WHERE sal<=3000 AND sal>=10000;
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000;

LIMIT 分页
SELECT * FROM emp LIMIT 0,200   展示前200条数据
SELECT * FROM emp LIMIT 200,500 展示201-500之间的记录

ORDER BY 排序
SELECT * FROM emp ORDER BY sal 升序
SELECT * FROM emp ORDER BY sal DESC 降序

聚合函数 aggregation
COUNT 
SELECT COUNT(*) FROM emp

MAX/MIN
SELECT MAX(sal) FROM emp 从emp表中查询工资最高的、
SELECT MIN(sal) FROM emp 

SUM/AVG 求和/平均数
SELECT COUNT(*) FROM emp 总数记录
SELECT SUM(sal) FROM emp 求和
SELECT AVG(sal) FROM emp 平均数

group by 表示分组，having子句  类似where过滤后返回的结果
SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno

HAVING
按照部门编号分类，从emp表中查询平均工资小于8000的信息
SELECT deptno ,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)<8000