一、常用语句和function
1、单行函数 lower,upper,substr,chr,to_char,to_date,to_number,nvl
select lower(ename) from emp; //将ename转化为小写,upper同理
select ename from emp lower(ename) like '_a%'; //忽略大小写取出名字第二字母是a的ename
select substring(ename, 1, 3) from emp; //截子串,从第一个字符开始截,一共截三个字符
select chr(65) from dual; //将ASCII码转化为字符
select ascii('A') from dual; //将字符转化为ASCII码
select round(23.625) from dual; <span style="white-space:pre"> </span>//四舍五入到个位数
select round(23.625, 1) from dual; <span style="white-space:pre"> </span>//四舍五入到一位小数
select round(23.625, -1) from dual; <span style="white-space:pre"> </span>//四舍五入到十位数
select to_char(sal, '$99,999.99') from emp; //转化数字格式,如果某一位没数字则不显示
select to_char(sal, 'L99,999.99') from emp; //转化数字格式,L表示本地货币
select to_char(sal, 'L00,000.00') from emp; //转化数字格式,如果某一位没数字则用零补齐
select to_char(hiredate, 'YY-MM-DD HH:MI:SS'); from emp; //转换日期格式
select to_char(sysdate, 'YY-MM-DD HH24:MI:SS'); from emp; //转换日期格式为24进制
select hiredate from emp where hiredate > to_date('1981-2-27 12:24:24', 'YYYY-MM-DD HH:MI:SS'); //将字符串转化为日期
select sal from emp where sal > to_number('$1,234.53', '$9,999.99'); //转化数字
select ename, sal*12 + comm from nvl(comm, 0) from emp; //处理空值,若是空值,用0替代
2、组函数 max,min,avg,sum
select max(sal) from emp; //求最大值
select min(sal) from emp; //求最小值
select avg(sal) from emp; //求平均
select sum(sal) from emp; //求总和
select count(*) from emp; //求记录总数,只count('string')不是空值的字段
select count(distinct deptno) from emp; <span style="white-space:pre"> </span>//去掉重复并记录总数
(组函数必记,一个都不能忘。组函数可以嵌套,只能嵌套两层。)
3、分组函数 group by
select deptno, avg(sal) from emp group by deptno; //分组求解
select max(sal) from emp group by deptno, job; //按照多个字段的组合进行分组
select ename from emp where sal = (select max(sal) from emp); //子查询
(规则:出现在select列表的字段如果没有出现在组函数里,必须出现在group by语句里)
4、having
where 是对单条 语句进行过滤。having是对分组之后的结果的限制
select avg(sal), deptno from emp group by deptno having avg(sal) >2000;
完整的select语句执行顺序,select -> where -> group by -> having -> order by -> ;