常用的SQL语句事例总结

--查询每个部门的普通员工
--Step1：查询出所有manager的id，且manager的id不能为空
select distinct manager from emp_cxg001 where manager is not null;
--Step2：在manager不能为空的基础上，查询id不在manager id的员工
select id,name,job,manager from emp_cxg001 
  where manager is not null and id not in(
    select distinct manager from emp_cxg001 where manager is not null
);
--查询除了manager以外的每个部门的普通员工的工资
--Step1找出所有的managerid，且manager的id不能为空
select distinct manager from emp_cxg001 where manager is not null;
--Step2在manager不能为空的基础上，查询id不在manager id的员工，并且计算平均工资
select deptno,avg(salary) from emp_cxg001 
  where manager is not null and id not in (
    select distinct manager from emp_cxg001 where manager is not null
  ) 
  group by deptno;
--查询名为soctt的员工的工作部门
select id,name,job,deptno from emp_cxg001 where name='scott';
--查询每个人的合计工资，要求货币格式化
select id,name,trim(
  to_char(nvl2(comm,salary+comm,salary),'L99,999.99')) 合计工资
from emp_cxg001;
--查询2000年以后的入职员工(两种方法:用和没有extract)
select id,name,hiredate from emp_cxg001
  where hiredate>to_date('20020101','yyyymmdd');
select id,name,hiredate from emp_cxg001
  where extract(year from hiredate)>=2002;
--查询工资高于5000的程序猿（大小写的区分）
select id,name,salary from emp_cxg001 
  where salary>5000 and job='PROGRAMMER';
select id,name,salary from emp_cxg001 
  where salary>5000 and job=upper('programmer');
--查询所有的身份证号码（模糊查询like的使用:% _ ）
select id,name,pid from emp_cxg001
  where pid is not null;
select id,name,pid from emp_cxg001
  where pid like '11%'
  and pid is not null;
--查询所有的经理人：在manager和clerk中满足一个即可（in 和 not in）
select id,name,salary,job from emp_cxg001
  where job in ('MANAGER','CLERK');
select id,name,salary,job from emp_cxg001
  where job not in ('MANAGER','CLERK');
--查询程序猿和销售的合计工资（工资加奖金）高于6000的(注意别名的使用时和SQL语句的执行顺序相关联)
select id,name,job,salary+nvl(comm,0) 合计工资 from emp_cxg001
  where job in('PROGRAMMER','SALESMAN') 
  and salary+nvl(comm,0)>6000;
--查询工资4000到6000的员工（between and 包含边界和不包含边界的区别）
select id,name,salary from emp_cxg001
  where salary between 4000 and 6000;--[4000,6000]
select id,name,salary from emp_cxg001
  where salary>=4000 and salary<6000;--[4000,6000)
--查询任意的一个工资大于4000,5000,6000的员工(any/all)
select id,name,salary from emp_cxg001
  where salary>all(4000,5000,6000);
select id,name,salary from emp_cxg001
  where salary>any(4000,5000,6000);
--合并相同行：distinct也就是去重复
select name,job from emp_cxg001;
select distinct name,job from emp_cxg001;
--查询工资的排序order by（默认升序asc，降序desc）注意别名的使用
select id,name,salary from emp_cxg001
  order by salary desc;
select id,name,salary,comm,salary+nvl(comm,0) from emp_cxg001
  order by salary+nvl(comm,0) desc;
select id,name,salary,comm,salary+nvl(comm,0) 总工资 from emp_cxg001
  order by 总工资 desc;
select id,name,salary,comm,salary+nvl(comm,0) 总工资,deptno from emp_cxg001
  order by deptno,总工资 desc;
--聚合函数
select min(hiredate) 最早入职
      ,max(salary) 最高工资 
      ,count(comm) 领奖金的人数
      ,count(salary) 领工资的人数
      ,sum(salary) 工资总额
      ,sum(comm) 奖金总额
      ,avg(salary) 平均工资
      ,avg(nvl(comm,0)) 平均奖金
from emp_cxg001;
--统计每个部门的平均工资（分组统计group by）
select  deptno,avg(salary) from emp_cxg001
  group by deptno;
select  deptno,avg(salary),count(salary),sum(salary) from emp_cxg001
  group by deptno;
--平均工资高于5000的部门(having的用法，注意SQL语句的执行顺序和别名的使用)
select  deptno,avg(salary+nvl(comm,0)) 平均工资 from emp_cxg001
  group by deptno
  having avg(salary+nvl(comm,0))>5000
  order by 平均工资,deptno desc;
--每个员工来自那个部门（关联查询 JOIN ON/USING主键和外键相同的时候使用）
select id,name,salary,deptno from emp_cxg001;
select deptno,dname from dept_cxg001;
select id,name,salary,dname from 
  emp_cxg001 e join dept_cxg001 d
  on (e.deptno=d.deptno);
select id,name,salary,dname from 
  emp_cxg001 e left join dept_cxg001 d
  on (e.deptno=d.deptno);
select id,name,salary,dname from 
  emp_cxg001 e right join dept_cxg001 d
  on (e.deptno=d.deptno);
select id,name,salary,dname from 
  emp_cxg001 e full join dept_cxg001 d
  on (e.deptno=d.deptno);
select id,name,salary,dname from 
  emp_cxg001 full join dept_cxg001
  using (deptno);
--和rose同职位的员工（子查询：一般分步骤写）
--step1先找出rose的职位
select id,deptno,job from emp_cxg001 where name='rose';--PROGRAMMER
--step2在找出早这个职位里的多有的员工
select id,name,deptno,job from emp_cxg001 
  where job in(
    select job from emp_cxg001 where name='rose'--step1SQL
  );
--查询工资高于全公司平均工资的员工
--step1先求出全公司的平均工资
select round(avg(salary+nvl(comm,0)),2)平均工资 from emp_cxg001;
--step2在那这个工资和每个人的工资进行比较
select id,name,deptno,salary from emp_cxg001
  where salary>(
    select round(avg(salary+nvl(comm,0)),2)平均工资 from emp_cxg001--step1SQL
  );
--包含销售部门的非销售员工
--step1先找出包含销售人员的所有的部门编号
select distinct deptno from emp_cxg001 where job=upper('salesman');--10,20
--step2在筛选出step1中筛出的部门中不是销售的员工
select id,name,job,salary,deptno from emp_cxg001
  where job!=upper('salesman') 
  and deptno in (
    select distinct deptno from emp_cxg001 where job=upper('salesman')--step1SQL
  );
--部门10中有人的工资比部门20中所有人的工资都低
--step1先找出部门20中最低的工资的员工
select min(salary) from emp_cxg001 where deptno=20;--4000
--step2在将部门10员工的工资和step1得出的值进行比较
select id,name,job,salary from emp_cxg001 
  where deptno=10 and salary<(
    select min(salary) from emp_cxg001 where deptno=20--step1SQL
  );
----部门10中有人的工资比部门20中所有人的工资都高
--step1先找出部门20中最低的工资的员工
select max(salary) from emp_cxg001 where deptno=20;--6000
--step2在将部门10员工的工资和step1得出的值进行比较
select id,name,job,salary from emp_cxg001 
  where deptno=10 and salary>(
    select max(salary) from emp_cxg001 where deptno=20--step1SQL
  );
--查询所有部门信息
--step1先将员工表和部门表关联起来，查询哪些部门有员工
select distinct deptno,dname from emp_cxg001 join dept_cxg001
  using(deptno);
--step2在查询以上step1生成表中的信息
select deptno,dname,loc from dept_cxg001
  where deptno in (
    select distinct deptno from emp_cxg001 join dept_cxg001
      using(deptno)--step1
  );
--最低工资高于部门20的最低工资的员工
--step1先查部门20的最低的工资
select min(salary) from emp_cxg001 where deptno=20;
--step2再查出每个本门的最低工资，并且各step1中生成的数值作比较
select deptno,min(salary) from emp_cxg001 
  where deptno!=20 and deptno is not null
  group by deptno
  having min(salary)>(
    select min(salary) from emp_cxg001 where deptno=20
  );
select id,name,job from emp_cxg001 where salary=6500 and deptno=11;
--工资高于本部门的平均工资的员工
--step1先计算出每个部门的平均工资
select round(avg(salary),2)平均工资 from emp_cxg001 group by deptno;
--step2按照部门分组，进step1生成的数值进行比较
select id,name,salary,deptno,平均工资 from 
  emp_cxg001 join 
    (select deptno,round(avg(salary),2) 平均工资 from emp_cxg001 group by deptno) 
  using (deptno)
  where salary>平均工资;
--找出比自己经理工资还高的员工（自连接的用法：注意使用别名）
select 员工.id,员工.name,员工.salary,经理.name,经理.salary from 
  emp_cxg001 员工 join emp_cxg001 经理
  on(员工.manager=经理.id)
where 员工.salary>经理.salary;
--分页查询
--step1排序之后发送
select row_number() over (order by salary desc) rn,
  id,name,salary
from emp_cxg001;
--step2截取：每r行，第n业
select * from (
  select row_number() over (order by salary desc) rn,
    id,name,salary
  from emp_cxg001)
where rn>2*(3-1) and rn<14;  

--查询每个部门中每个职位的最高的薪水
select empno,ename,max(salary),deptno from emp where group by deptno,job;
--查询比本部门最高工资低的员工
--查询结果中显示员工的部门名称、工资、部门编号以及员工对应部门的最高工资。
--首先在emp表中找到最高工资，并按照部门分组
select deptno,max(salary) maxsal from emp group by deptno;
--采用自连接的方式将第一步查询的表格和emp做联合查询
select a.ename,a.salary,a.deptno,b.maxsal from emp a,(
    select deptno,max(salary) maxsal from emp group by deptno) b
where a.deptno=b.deptno and a.salary<b.maxsal;
--查询EMP 中那些工资高于他们所在部门的管理者工资的员工。
--还是自连接的问题，在同一张表格中采用两张表的方式来进行查询
select * from emp a,emp b where a.mgr=b.mgr and a.salary>b.salary;
--找出EMP 中那些工资高于他们所在部门普通员工（不包含管理者）平均工资的员工。
select ename,sal,deptno from emp a
    where sal>(select avg(sal) from emp b
        where mgr is not null and a.mrg=b.mgr group by mgr);

--算出一班(class_id=1)每个学生各科的成绩
--三张表格的联合查询，学生表格和成绩表格中所对应的学生的编号作为条件和课程表编号和
--成绩表对应的编号为条件
select s.student_name,sub.subject_name,p.test_score
    from t_student s join t_performance p
    on s.student_id=p.student_id
    join t_subject sub on sub.subject_id=p.subject_id
    where s.class_id=1;
--对一班(class_id=1)每个学生各科成绩的总分进行排序
select s.student_name,sub.subject_name,sum(p.test_score) total_score
    from t_student s join t_performance p
    on s.student_id=p.student_id
    join t_subject sub on sub.subject_id=p.subject_id
    where s.class_id=1
    group by s.student_id,s.student_name
    order by toal_score;

--使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--首先根据条件找出学号，课程之间的联系
select s# from c,sc where c.c#=sc.c# and cn='税收基础';
--在根据根据条件找出符合条件的学生
select s#,sn from s where s# in(select s# from c,sc where c.c#=sc.c# and cn='税收基础');

--使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
select sn,sb from s,sc where s.s#=sc.s# and sc.c#='c2';

--使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--首先在sc表格中找到多有的c5编号的课程对应的学生的id
select s# from sc where c#='c5';
--在将这些S#的学生不在这个id的人除外就是
select sn,sd from s where s# not in(select s# from sc where c#='c5');

--查询选修了课程的学员人数
select toal_student=count(distinct s#) from sc;

--查询选修课程超过5门的学员学号和所属单位
select sn,sd from s where s# in(
    select s# from sc group by s# having count(distinct s#)>5
);
--查询表A中存在ID重复三次以上的记录
select * from table t where t.id in(
    select id from table group by id having count(id)>3
);