SQL回炉之一：单表操作、函数小结

一：单表查询

--模糊查询A，A之前只有一个字母，后边有0或n个字母SELECT * FROM emp WHERE ename LIKE '_A%';--DISTINCT两项，去除这两项全都重复的项目SELECT DISTINCT deptno,sal FROM emp;--靠一项(deptno)排序后，结果靠另一项(sal)再排一次SELECT * FROM emp ORDER BY deptno ASC,sal DESC;

二：函数总结

1.字符串--1.lower(args)，upper(args)SELECT lower(ename) FROM emp;--忽略大小写SELECT ename FROM emp WHERE lower(ename) like '_a%';--2.substr(),从第1个开始截取，一共截取3个字符SELECT substr(ename,1,3) FROM emp;--3.chr(),ascii(''),ASCII码数值和字符转换SELECT chr(65) FROM dual;SELECT ascii('A') FROM dual;2.数值：--1.round()四舍五入SELECT round(1.2345) FROM dual;--2.截取位数，小数点后几位SELECT round(1.234567,4) FROM dual;--3.to_char(,)数字或日期转成某种格式--数字显示格式SELECT to_char(sal,'$99,999.999') FROM emp;3.日期转换：--1.to_char(),HH24是二十四时制，HH十二小时时制)SELECT to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') FROM emp;--2.to_date()SELECT * FROM emp WHERE hiredate > to_date('1970-01-01','YYYY-MM-DD');4.空值：--nvl(),某一项是空值，就显示成0SELECT ename,nvl(comm,0) FROM emp;5.运算：SELECT max(sal) FROM emp;SELECT min(sal) FROM emp;SELECT sum(sal) FROM emp;SELECT round(avg(sal),3) FROM emp;--求非空项目个数SELECT count(*) FROM emp;SELECT count(comm) FROM emp;SELECT count(DISTINCT deptno) FROM emp;

三：分组查询

--按XXX分组SELECT avg(sal) FROM emp GROUP BY deptno;--HAVING过滤分组后的数据（WHERE过滤单条）SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)>2000ORDER BY deptno ASC;

--按deptno分组的avg(sal)语句select中只能出现这两项SELECT deptno,avg(sal)FROM empWHERE sal>1200GROUP BY deptno HAVING avg(sal)>1500ORDER BY deptno DESC;

--GROUP BY分组必须返回唯一值，有些东西必须用子查询SELECT * FROM emp ,(SELECT max(sal) max_sal FROM emp GROUP BY deptno)maxWHERE sal = max_sal;

四：关键字执行顺序

SELECT FROM->WHERE->GROUP BY->HAVING->ORDER BY