|
select...列名,表达式,函数
distinct,别名
from...表名
where...条件表达式betwween..and ... ,like,in
order by...列名,列别名,表达式,数字 desc,asc
函数--------单行函数
字符函数upper/lower/lpad/rpad/length/substr/replace/trim/concat
数字函数round/trunc/mod
日期函数sysdate
转换函数to_char
通用函数nvl
默认的日期格式:dd-mon-RR
日期格式:yyyy/year/mm/dd/day/hh24/mi/ss/am/month
日期函数:
months_between(sysdate,hiredate);间隔多少个月份
select add_months(sysdate,6) from dual;加上六个月
select add_months(sysdate.-6) from dual六个月以前
select sysdate+6 from dual;加上六天
select next_day(sysdate,5) from dual;下一个周四
select next_day(sysdate,'FRI') from dual;下一个周五
select next_day(sysdate.'星期五')from dual
英文环境下使用'FRI',中文环境下使用星期五,数字通用的
last_day(sysdate) from dual;本月的最后一天
转换函数:
TO_NUMBER TO_DATE
select to_number('12')*13 from dual;//156
select to_number('$12,345.67','$99,999.99')from dual;//12345.67
9数字位,
0数字位,如果长度不够
数字<==>字符
select to_number('$12,345.67','$99,999.99')*0.1 from dual;//Integer.parseInt();
select to_number('ff','xx') from dual;//255
select to_char(12345,'$999,999.00')from dual;//12,345.00
select to_char(12345,'$000,000.00)from dual;//012,345.0
日期<==>字符
----------to_date('10 September 2009','dd Month yyyy')--------
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;//2011-02-22 11:14:55
select to_char(sysdate,'yyyy/mm/dd day am') from dual;//2011/02/22 tuesday am
select to_date('2000-10-10','yyyy-mm-dd')from dual;//10-OCT-00
insert into emp(empno,ename,hiredate
values(1234,'zhangsaan',sysdate));
insert into emp(empno,ename,hiredate
values(1235,'zhangwuji',to_date('2010-2-24','yyyy-mm-dd'));
通用函数
nvl,coalesce,---空值处理函数
decode---分支处理函数
coalesce(comm,sal,100)返回第一个非空的数值
select ename,sal,comm,nvl(comm,100)bouns from emp;如果奖金是空的话,就添加上100。
select ename,nvl(job,'not job yet') from emp;将没有职位的人添加上not job yet这个职位
select ename,sal,comm,coalesce(comm,sal,100) from emp;
select ename,job,sal,decode(job,'SALESMAN',1.05*sal,if语句
'ANALYST',1.1*sal,
'MANAGER',1.15*sal,
sal) bonus from emp;
多表查询
查找叫Smit的人的部门名称
select t1.ename,t2.dname
from t1 join t2
on t1.c1= t2.c2
约束条件,用来保证数据的完整性
pk:primary key主键,唯一的定位标识`
fk:foreign外键用来表示表间的关系
select emp.ename,dept.dname
from emp join dept on emp.deptno=dept.deptno;
dept 被参照的表是主表,父表
emp:外键参照dept表,从表,子表
select emptno
------------------------内连接 三种表现形式joinon
select e.ename,e.job,e.sal,d.dname 等值连接
from emp e join dept d
on e.deptno=d.deptno
and e.job='MANAGER';
select e.ename,e.sal,s.grade非等值连接
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
自连接
select worker.ename,manager.ename
from emp worker join emp manager
on worker.mgr =manager.empno;
一张表里的记录一定在另一张表中存在匹配的记录,否则不能出现在结果集中
-----------------------外连接
select t1.cname, t2.dname
from t1
[left | right | full | outer | join ]
join t2 on t1.c1=t2.c2;
select e.ename,d.dname//emp是驱动表,以驱动表里的数据在结果集中找
from emp e left outer join dept d
on e.deptno=d.deptno;
select e.ename,d.dname
from dept d left outer join emp e
on e.deptno=d.deptno;
select e.ename,d.dname
from dept d full outer join emp e
on e.deptno=d.deptno;
哪些员工没有下属,不是别人的领导,
外连接+匹配表pk is null表示否定问题,不是不包括
select worker.ename,manager.ename列出所有的职员
from emp worker left outer join emp manager
on worker.mgr=manager.empno;
哪些部门没有叫smith的员工
1)用ename='SMITH'过滤emp表
2)用外连接dept做驱动表,得到结果集=有smith的部门(驱动表中匹配不到的记录)
3)用匹配表的pk is null过滤掉内连接的结果集
select d.dname from
emp e right outer join dept d on
e.deptno=d.deptno
and e.ename='SMITH'--现执行这条语句
where e.empno is null;
组函数(多行进去出来一行)
select count(*) from t1;
max/min/avg/sum/count
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp;
select avg(sal) from emp;
主函数嵌套单行函数
select avg(nvl(comm,0))from emp;
计算员工分布在几个部门
select distinct deptno from emp;
select count(distinct deptno) from emp;
按照部门分组,计算每个部门的总和
select deptno ,sum(sal)
from emp
group by deptno;
select sum(sal) from emp where deptno is null;//没有部门的薪水总和
按照职位分组,计算每个职位的平均薪水
select job,avg(sal)//分组函数要求 按照select中的某一个去group by的后面
from emp
group by job;
在select后面所有的非主函数的列必须出现在group by后面-
---结论:select后面的非组函数的列和group by后面的列名保持一致
select deptno ,job ,avg(sal)
from emp
group by deptno ,job
order by deptno ,job; |
|