sql基础大全

--建表 （雇员表）
create table EMP
(
EMPNO NUMBER(5),--员工号
ENAME VARCHAR2(10),--员工名称
JOB VARCHAR2(30),--职位
MGR NUMBER(5),--上级的员工号
HIREDATE DATE,--入职时间
SAL NUMBER(8,2),--工资
COMM NUMBER(8,2),--奖金
DEPTNO NUMBER(6)--部门编号
)
--部门表
create table DEPT
(
DEPTNO NUMBER(2),--部门编号
DNAME VARCHAR2(14),--部门名称
LOC VARCHAR2(13)--部门地址
)

--工资等级表
create table SALGRADE
(
GRADE NUMBER,--工资等级编号
LOSAL NUMBER,--最低数
HISAL NUMBER--最高数
)

--奖金表
create table BONUS
(
ENAME VARCHAR2(10),
JOB VARCHAR2(20),
SAL NUMBER,
COMM NUMBER
)


/*基础查询语句*/

--部门一中的所有员工
select * from emp where deptno=1
--找出职位为中级工程师的所有员工
select * from emp where job='中级工程师'
--找出奖金高于工资的员工
select * from emp where comm>sal
--找出每个员工工资和奖金的总和
select t.*,(t.sal+t.comm) totalM from emp t
--找出部门1中的项目经理和部门2 中的初级工程师
select t.* from emp t where (t.deptno=1 and t.job = '项目经理') or (t.job ='初级工程师' and t.deptno =2)
--找出部门1中既不是项目经理也不是初级工程师，而且工资大于等于3000 的员工
select t.* from emp t where t.deptno =1 and t.job !='项目经理' and t.job !='初级工程师' and t.sal>3000
--找出有奖金的员工的不同工作
select distinct(t.job) from emp t where t.comm !=0
--找出没有奖金或者奖金低于600 的员工
select t.* from emp t where t.comm<600 or t.comm = 0
--显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select t.ename,t.hiredate from emp t order by t.hiredate

/*单行函数用法*/

--找出每个月倒数第三天受雇的员工 *(用到last_day函数)
select * from emp where last_day(hiredate)-3 = hiredate
--找出1年前雇的员工 *(用到add_months函数)
select * from emp where hiredate<=add_months(sysdate,-1*12)
--所有员工名字前加上Dear ,并且名字首字母大写 *(用到last_day函数)
select 'Dear '|| initcap(t.ename) from emp t
--找出姓名为5 个字母的员工
select * from emp t where length(t.ename) = 3
--找出姓名中不带'张'这个字的员工
select * from emp where ename not like '%张%'
--显示所有员工的姓名的第一个字
select substr(ename,0,1) from emp
--显示所有员工的姓
select distinct(substr(ename,0,1)) from emp
--显示所有员工，按名字降序排列，若相同，则按工资升序排序
select * from emp order by ename desc,sal
--假设一个月为30 天，找出所有员工的日薪，不计小数 *(去掉小数点floor,四舍五入：round 直接去小数点后几位：trunc)
select t.*,floor(t.sal/30,1) ri from emp t
select t.*,round(t.sal/30,1) ri from emp t
select t.*,trunc(t.sal/30,1) ri from emp t
--找到1月份受雇的员工*(to_char的用法)
select * from emp where to_char(hiredate,'fmmm')='1'
--列出员工加入公司的天数(四舍五入）
select t.*,round(sysdate-t.hiredate) from emp t
--分别用case 和decode 函数列出员工所在的部门，deptno=10 显示'部门10' 用2种方法实现
select t.ename,t.empno,(case when t.deptno='1' then '部门1' when t.deptno='2' then '部门2' else '管理部门' end) 部门 from emp t order by t.deptno
select t.ename,t.empno,decode(t.deptno,'1','部门1','2','部门2','管理部门') 所在部门 from emp t order by t.deptno


/*分组函数用法*/

---分组统计各部门下工资>5000 的员工的平均工资
select round(avg(t.sal)) from emp t where t.sal>5000 group by t.deptno
--统计各部门下平均工资大于500 的部门
select avg(t.sal) avgsal from emp t where t.sal>5000 group by t.deptno having avg(t.sal)>7000
--或
select t.* from (select t.deptno,avg(t.sal) avgsal from emp t where t.sal>5000 group by t.deptno) t where t.avgsal>7000
--算出部门1中得到最多奖金的员工奖金
select max(comm) from emp where deptno = 1
select t.* from (select t.* from emp t order by t.comm desc) t where rownum<2 and t.deptno =2
--算出部门30 中得到最多奖金的员工姓名
select * from emp t1 where t1.comm = (select max(comm) from emp where deptno = 1) and t1.deptno =1
--或
select t.* from (select t.* from emp t order by t.comm desc) t where rownum<2 and t.deptno =2
--算出每个职位的员工数和最低工资
select job,count(*),min(sal) from emp group by job
--算出每个部门,每个职位的平均工资和平均奖金(平均值包括没有奖金)，如果平均奖金大于300，显示“奖
-- 金不错”，如果平均奖金100 到300，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”，
-- 按部门编号降序，平均工资降序排列
select t.deptno,t.job,avg(sal),avg(comm),(case when avg(comm)>800 then '奖金不错'
when 500<=avg(comm) and 800>= avg(comm) then '奖金一般' else '基本没有奖金' end)
奖金情况 from emp t group by t.deptno,t.job order by t.deptno desc,avg(sal) desc
--列出员工表中每个部门的员工数，和部门no
select t.deptno,count(*) from emp t group by t.deptno
--得到工资大于自己部门平均工资的员工信息
select t.* from emp t,(select avg(t.sal) pj,t.deptno from emp t group by t.deptno) t1 where t.sal>t1.pj and t1.deptno = t.deptno
--分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) *(函数nvl 没有就算是0)
select deptno,job,avg(nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by deptno,job;


/*多表查询*/

--列出员工表中每个部门的员工数，和部门no
select count(*),deptno from emp group by deptno
--列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
select d.* ,ed.cou from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>3) ed where d.deptno=ed.deptno;
--找出工资比张老大 多的员工
select t2.* from emp t1,emp t2 where t1.ename='张老大' and t1.sal<t2.sal
--或
select * from emp t1,(select sal from emp where ename='张老大') t where t1.sal>t.sal
--列出所有员工的姓名和其上级的姓名
select * from emp t1,(select empno,ename from emp) t where t1.mgr = t.empno
--以职位分组，找出平均工资最高的两种职位
select t1.* from (select job,avg(sal) jabsal from emp group by job order by avg(sal) desc) t1 where rownum<3
--查找出不在部门2，且比部门2中任何一个人工资都高的员工姓名、部门名称
select * from emp t1,(select max(sal) maxsal from emp where deptno = '2') t where t1.sal>t.maxsal and t1.deptno !='2'
--得到平均工资大于8000 的工作职种
select job,avg(sal) jobsal from emp group by job having(avg(sal)>8000)
--分部门得到工资大于4000 的所有员工的平均工资，并且平均工资还要大于7500
select deptno,avg(sal) depavgsal from emp where sal>4000 group by deptno having(avg(sal)>7500)
--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select t1.*
from dept t1,
(select e.deptno, sals
from (select deptno, sum(sal) sals
from emp
group by deptno
order by sum(sal)) e
where rownum = 1) t
where t1.deptno = t.deptno
--分部门得到平均工资等级为2 级（等级表）的部门编号
select t.deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t,(select losal,hisal from salgrade where GRADE = 2) t1
where t1.losal<t.avgsal and t1.hisal>t.avgsal
--查找出部门10 和部门20 中，工资最高第3 名到工资第5 名的员工的员工名字，部门名字，部门位置

/*对于rownum的重要说明：rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。
简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。
另外还要注意：rownum不能以任何基表的名称作为前缀。*/

select t.*
from (select rownum rn, t.*
from (select empno, sal, deptno
from emp
where deptno = '1'
or deptno = '2'
order by sal desc) t) t
where t.rn > 2
and t.rn < 6
--或
select emp.ename, emp.empno, emp.sal, dept.dname, dept.loc
from emp,
dept,
(select rownum no, new.*
from (select *
from emp
where emp.deptno = 1
or deptno = 2
order by emp.sal desc) new) e
where emp.deptno = dept.deptno
and e.no >= 3
and e.no <= 5
and e.empno = emp.empno;
--查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入
select e.ename, e.empno, e.sal + nvl(e.comm, 0)
from emp e, emp m
where e.mgr = m.empno
and (e.sal + nvl(e.comm, 0)) > (m.sal + nvl(m.comm, 0));
--查找出工资等级不为4 级的员工的员工名字，部门名字，部门位置
select t.ename, t.empno, t.sal, t.deptno, d.DNAME, d.loc
from emp t, (select * from salgrade where grade = 4) t1, dept d
where t.deptno = d.deptno and (t.sal > t1.hisal or t.sal < t1.losal)
--查找出职位和'张小' 或者'李三'一样的员工的平均工资
select avg(t1.sal) from emp t1,(select job from emp where ename='张小' or ename='李三') t where t1.job in t.job
--或
select avg(sal) from emp where job in (select job from emp where ename='张小' or ename='李三');
--查找出不属于任何部门的员工
select * from emp where deptno is null or deptno not in(select deptno from dept)
--按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
select t.counts,d.dname,d.loc from dept d,
(select rownum nos,t.* from (select deptno,count(*) counts from emp group by deptno order by count(*) desc) t) t
where d.deptno = t.deptno and (t.nos<5 and t.nos>1)
--或
select dept.dname, dept.loc
from (select rownum no, deptno from
(select count(*) employeeSum, deptno from emp group by deptno order by employeeSum
desc)) e,dept where e.no between 2 and 5 and e.deptno = dept.deptno;
--查询出张小所在部门的部门号\部门名称\部门人数 ****
select count(*),deptno from emp where deptno in (select deptno from emp where ename='张小') group by deptno

select t.countno, d.dname, d.loc
from dept d,
(select count(*) countno, deptno
from emp
where deptno in (select deptno from emp where ename = '张小')
group by deptno) t
where d.deptno = t.deptno;
--查询出张小所在部门的工作年限最大的员工名字
select ename, hiredate
from emp
where hiredate in
(select min(hiredate)
from emp
where deptno in (select deptno from emp where ename = '张小'));
--查询出工资成本最高的部门的部门号和部门名称
select deptno,total from
(select rownum rw,t.deptno,t.total from (select sum(sal) total,deptno from emp group by deptno order by sum(sal) desc) t) t
where t.rw=1
--或
select d.deptno, d.dname, t.sum_sal
from dept d,
(
select deptno, sum(sal) sum_sal
from emp
group by deptno
having sum(sal) = (select max(sum(sal)) from emp group by deptno)
) t
where d.deptno = t.deptno



--随机返回5 条记录
select *
from (select ename, job from emp order by dbms_random.value())
where rownum <= 5
--查询跳过表中的偶数行
select ename from (select row_number() over (order by ename) rn,ename
from emp) x where mod(rn,2)=1;
--查询员工信息与其中工资最高最低员工
select ename,sal,max(sal) over(), min(sal) over() from emp;
--连续求和
select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from
emp;
--分部门连续求和
select deptno, sal, sum(sal) over(partition by deptno order by ename) as s
from emp
--查询EMP 员工表下每个部门工资前二名的员工信息
select deptno, ename, sal
from emp e1
where
(select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal)
<2
order by deptno, sal desc;[color=red][/color][color=blue][/color][align=center][/align]