|
創(chuàng)建兩個表,，一個名為emp,，一個名為dept,，并且插入數(shù)據(jù) 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; --聚合函數(shù) max/min/sum/avg/count...使用聚合函數(shù)時,，慎用字段查詢,。 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 --內(nèi)連接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為右),， --即以左側數(shù)據(jù)全部顯示,，若右邊沒有對應數(shù)據(jù)則補上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開始計數(shù),，查到一條信息則自增1,。 create table empp as (select rownum rn,emp.*from emp ); --查看第3-6條數(shù)據(jù) 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()函數(shù) 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的人數(shù) select count(*),decode( --count（）后面+分組函數(shù) job, 'manager','vip', 'analyst','vip', 'operation' )c from emp group by decode( job, 'manager','vip', 'analyst','vip', 'operation' ); |
|
|
