|
创建两个表，一个名为emp，一个名为dept，并且插入数据 create table emp( empno number(4,0), ename varchar2(10), job varchar2(9), rngr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0) ); create table dept( deptno number(2,0), dname varchar2(14 byte), loc varchar2(13 byte) ); insert into emp values(7369,'smith','clerk',7902,to_date('1980/12/17','yyyy-mm-dd'),800.00,null,20); insert into emp values(7499,'allen','salesman',7698,to_date('1981/12/20','yyyy-mm-dd'),1600.00,300.00,30); insert into emp values(7521,'ward','salesman',7698,to_date('1982/2/22','yyyy-mm-dd'),1250.00,500.00,30); insert into emp values(7566,'jones','manager',7839,to_date('1981/4/2','yyyy-mm-dd'),2975.00,null,20); insert into emp values(7654,'martin','manager',7698,to_date('1981/9/28','yyyy-mm-dd'),1250.00,1400.00,30); insert into emp values(7698,'blake','manager',7839,to_date('1981/5/1','yyyy-mm-dd'),2850.00,null,30); insert into emp values(7782,'clark','manager',7839,to_date('1981/6/9','yyyy-mm-dd'),2450.00,null,10); insert into emp values(7788,'scott','analyst',7566,to_date('1987/4/19','yyyy-mm-dd'),3000.00,null,20); insert into emp values(7839,'king','president',null,to_date('1981/11/17','yyyy-mm-dd'),5000.00,null,10); insert into emp values(7844,'turner','salesman',7698,to_date('1981/9/8','yyyy-mm-dd'),1500.00,0.00,30); insert into emp values(7876,'adamas','clerk',7788,to_date('1987/5/23','yyyy-mm-dd'),1100.00,null,20); insert into emp values(7900,'james','clerk',7698,to_date('1981/12/3','yyyy-mm-dd'),950.00,null,30); insert into emp values(7902,'ford','analyst',7566,to_date('1981/12/3','yyyy-mm-dd'),1300.00,null,20); insert into emp values(7934,'miller','clerk',7782,to_date('1982/1/23','yyyy-mm-dd'),1300.00,null,10); insert into dept values(10,'accounting','new york'); insert into dept values(20,'research','dallas'); insert into dept values(30,'sales','chicago'); insert into dept values(40,'operations','boston'); ------------------------------------------------------------------------------------------------------------------------------------------ --order by:排序.要放在sql语句的最后. 如果有多个需要排序，用逗号隔开。 --asc 升序，desc降序。默认为升序 select *from emp where job='manager' order by sal; select *from emp order by deptno,sal; select *from emp; --部门升序，工资降序排列 select *from emp order by deptno asc,sal desc; --查看所有员工，并按照入职时间降序排列 select ename from emp order by hiredate desc; --聚合函数 max/min/sum/avg/count...使用聚合函数时，慎用字段查询。 select max(sal),min(sal),ceil(avg(sal)),count(sal) from emp; --查看公司平均奖金 select sum(comm),avg(nvl(comm,0)) from emp; --分组 --查看每个职位的平均薪资 select job,round(avg(sal)) from emp group by job; --查看平均薪资高于3000的职位 select job,round(avg(sal),2) from emp group by job having avg(sal)>1500; --在分组中设置条件用having --内连接a join b on a.x=b.x select emp.ename ,dept.loc from dept join emp on emp.deptno=dept.deptno; --外连接 左/右/全 外连接。 left join/right join/ full join左外连接是以左侧为基准(from a join b a为左b为右)， --即以左侧数据全部显示，若右边没有对应数据则补上null。 --将King的部门号改为50号部门 update emp set deptno=50 where ename='king'; select emp.ename,dept.deptno from emp full join dept on emp.deptno=dept.deptno; --自连接 select e.ename,e.rngr,m.empno from emp e,emp m where e.rngr=m.empno; --子查询 --查找和scott同职位的员工 1：知道斯科特啥职位 2：查询这个职位的人 select job from emp where ename='scott'; select ename from emp where job=(select job from emp where ename='scott'); --查询部门中除了salesman之外的人 select ename,deptno from emp where deptno in (select deptno from emp where job='salesman'); --分页查询 select rownum ,ename,job,sal,from emp; --rownum :从1开始计数，查到一条信息则自增1。 create table empp as (select rownum rn,emp.*from emp ); --查看第3-6条数据 select * from empp where rn>3 and rn<6; drop table empp; --查看工资排名6-10 select sal from emp; select * from emp order by sal; select * from ( select rownum rn,e.*from( select *from emp order by sal) e) where rn between 6 and 10; --decode()函数 select ename,job,sal,decode( job, 'manager',sal*1.2, 'analyst',sal*1.1, 'salesman',sal*1.05, sal )de from emp; --de为别名 --把analyst和manager当做vip，其他的为operation，并计算vip和非vip的人数 select count(*),decode( --count（）后面+分组函数 job, 'manager','vip', 'analyst','vip', 'operation' )c from emp group by decode( job, 'manager','vip', 'analyst','vip', 'operation' ); |
|
|
