数据库教程:SQL-函数及多表关联

 1 -- 1.字符串连接   2 select concat('aa','12') from dual;   3 select 'aa'||'12' from dual;   4    5 -- 2.首字母大写   6 select initcap('abc') from dual;   7 --- 把大写转化小写   8 select lower('abc') from dual;   9 select upper('abc') from dual;  10   11 -- 把所有员工的姓名小写输出  12 select lower(e.ename),e.empno  13 from emp e  14   15 -- 3.填充字符lpad/rpad  16 select lpad('sxt',5,'*') from dual;  17 select rpad('sxt',5,'*') from dual;  18   19 -- 4.去掉空白字符  20 select '  kallen' from dual;  21 select ltrim('  kallen',' ') from dual;  22 select rtrim('  kallen  ',' ') from dual;  23 -- trim 删除左右两边的字符  24 select trim('a' from 'abc') from dual;  25   26 -- 5.求子串 substr(str,loc,len)-->loc从1开始  27 select substr('abcd',2,2) from dual;  28   29 -- 6.查找字符串  30 /*  31 如果找到返回>=1的索引；如果没找到返回0  32 */  33 select instr('abcd','b') from dual;  34   35 -- 7.求长度  36 select length('abcd') from dual;

 1 -- 四舍五入round(x,y)对x保留y为小数   2 select round(23.652) from dual;   3 select round(23.652,1) from dual;   4 select round(25.652,-1) from dual;   5    6 -- 返回x按精度y截取后的值   7 select trunc(23.652) from dual;   8 select trunc(23.652,2) from dual;   9 select trunc(23.652,-1) from dual;  10   11 -- mod（x，y）求余数  12 select mod(9,2) from dual;  13   14 -- ceil 向上取整  15 select ceil(1.9) from dual;  16 -- floor 向下取整  17 select floor(1.9) from dual;

 1 -- 返回系统当前时间   2 select sysdate from dual;   3 -- 返回当前会话时区中的当前日期    4 select current_date from dual;   5    6 -- 添加月数   7 select add_months(sysdate,1) from dual;   8 -- 返回两个时间相差的月数   9 select months_between(sysdate,add_months(sysdate,2)) from dual;  10   11 -- 需求:查询工作年限在30年以上  12 select e.ename,e.hiredate  13 from emp e  14 where months_between(sysdate,e.hiredate)/12 > 30  15   16 -- 返回date下一月份的最后一天  17 select last_day(add_months(sysdate,1)) from dual;  18 -- next_day(date1，week) 返回date1下周星期几的日期  19 select sysdate "当时日期",next_day(sysdate,'monday') "下周星期一" from dual;

1 --两个时间进行四则运算的单位是天  2 select sysdate+2 from dual;  3 select sysdate-2 from dual;

1 --字符和数字/日期之间的隐式转换  2 -- 字符隐式转换成数值  3 select '100' - 10 from dual;  4   5 -- 字符隐式转化为日期  6 -- dd-mon-rr 默认的日期格式  7 select 1 from dual  8 where sysdate > '13-may-19';

1 -- 把日期转化成字符  2 -- 按照默认格式dd-mon-rr  3 select to_char(sysdate) from dual;  4 -- 按指定格式  5 select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') as t from dual;  6 -- 格式内要打中文（非给定允许的时间分隔符）需要用双引号引起来

1 -- 把数值格式化成字符串  2 select to_char(12345,'99999.99') from dual;  3 select to_char(12345,'99,999.99') from dual;  4 -- 没有数的位置补空格  5 select to_char(12345,'999,999.99') from dual;  6 -- 没有数的位置补0  7 select to_char(12345,'000,000.00') from dual;  8 -- 格式化成美元显示  9 select to_char(12345,'$000,000.00') from dual;

 1 -- to_number   2 select to_number('$12,345','$99,999') from dual;   3 select to_number('$12,345.12','$99,999.99') from dual;   4    5 -- to_date   6 select to_date('14-may-19','dd-mon-rr') from dual;   7 select to_date('2004-09-19','yyyy-mm-dd') from dual;   8    9 -- 查询雇用期满6个月的下一个周一的日期。  10 select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'monday')  11 from emp e  12 where months_between(sysdate,e.hiredate) > 6

1 -- 需求:查询员工所在的部门名称  2 select   3 e.ename,  4 e.deptno,  5 decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知')  6 from emp e;

 1 select    2 e.ename,   3 e.deptno,   4 case e.deptno   5   when 10 then '部门1'   6   when 20 then '部门2'   7   when 30 then '部门3'   8   else '未知'   9 end  10 from emp e;

 1 -- 需求:根据工资分布输出以下信息   2 /*   3  <1000 真屌丝   4  (1001,2000] 屌丝   5  (2001,3000] 白领    6  (3001,5000] 高富帅   7  (5001,10000] 土豪   8 */   9   10 select   11 e.ename "姓名",  12 e.sal "工资",  13 case  14   when e.sal <= 1000 then '真屌丝'  15   when e.sal <= 2000 then '屌丝'  16   when e.sal <= 3000 then '白领'  17   when e.sal <= 5000 then '高富帅'  18   when e.sal <= 10000 then '土豪'  19   else '未知'  20 end "描述"  21 from emp e;

 1 -- 求公司雇员的数量   2 select count(*)   3 from emp e;   4    5 select count(e.empno)    6 from emp e;   7    8 select count(1)   9 from emp e;  10   11 -- avg:对多个记录的某个字段求平均值  12 -- 需求:求底薪的平均值  13 select avg(e.sal)  14 from emp e;  15   16 -- 需求:求雇员的最高薪资/最低薪资  17 select max(e.sal),min(e.sal),avg(e.sal)  18 from emp e;  19   20 -- 需求:求公司一个月的员工基本开销  21 select sum(e.sal)  22 from emp e;

1 -- 最早入职的员工  2 select e.ename,e.hiredate  3 from emp e  4 where e.hiredate = (select min(e.hiredate) from emp e);

1 select field1,...  2 from tablename  3 group by field1[,field2,…]

 1 -- 需求:统计部门10的人数   2 select count(1)   3 from emp e   4 where e.deptno = 10;   5    6 -- 需求:求各个部门的平均薪资   7 select e.deptno,avg(e.sal)   8 from emp e   9 group by e.deptno  10   11 -- 需求:求各个部门的月收入平均值  12 select e.deptno,avg(e.sal+nvl(e.comm,0))  13 from emp e  14 group by e.deptno

1 -- 特例:按照津贴分组  2 select e.comm,count(1)  3 from emp e  4 group by e.comm;

 1 -- group by having   2 -- 查询部门平均薪资大于3000的部门   3 select e.deptno   4 from emp e   5 group by e.deptno   6 having avg(e.sal) > 3000   7    8 -- 查询部门薪资大于3000的雇员按部门分组的平均薪资   9 select e.deptno,avg(e.sal)  10 from emp e  11 where e.sal > 3000  12 group by e.deptno;

1 select field1, field2,...  2 from tablename  3 order by field1,field2

 1 -- order by   2 -- 按雇员薪资排序   3 select e.ename,e.sal   4 from emp e   5 order by e.sal desc   6    7 -- 按薪资升序，名称降序   8 select e.ename,e.sal   9 from emp e  10 order by e.sal,e.ename desc;

1 --薪资大于1200的雇员的平均薪资大于1500的部门，按照平均薪资升序排序   2 select e.deptno,avg(e.sal)  3 from emp e  4 where e.sal > 1200  5 group by e.deptno  6 having avg(e.sal) > 1500  7 order by avg(e.sal) asc

1 -- 笛卡尔积  2 select *  3 from emp,dept

1 -- 等值连接  2 -- 需求:查询雇员的部门名称  3 select e.ename,e.deptno,d.dname  4 from emp e,dept d  5 where e.deptno = d.deptno

1 -- 查询每个雇员的薪资等级  2 select e.ename,e.sal,sg.grade  3 from emp e,salgrade sg  4 where e.sal >= sg.losal and e.sal <= sg.hisal  5 -- where e.sal between sg.losal and sg.hisal

1 -- 需求:查询所有部门的雇员  2 select *  3 from dept d,emp e  4 where d.deptno = e.deptno(+)

1 -- 右外连接（b）  2 select *  3 from emp e,dept d  4 where e.deptno(+) = d.deptno;

1 -- 查询每个雇员的上级领导  2 select e.ename "雇员",m.ename "领导"  3 from emp e,emp m  4 where e.mgr = m.empno