SQL必须知道的基本表操作(三)查询操作

 

 

 

首先呢，是查询语句的格式
 --在sql中 各关键字出现的顺序
   --SELECT
   --FROM 
   --WHERE
   --GROUP BY
   --HAVING
   --ORDER BY

---查询一张表中的所有数据
select * from emp;
--- 查询数据某几个字段的值 按照指定字段顺序来显示数据
select empno, ename from emp;
--- 去重复查询
select distinct deptno,job from emp;
---  条件查询  查询内容区分大小写
select deptno,job,sal from emp where ename='SMITH';
-- 查询部门编号 10或者20 的所有员工
select * from emp where deptno=10 or deptno=20;
-- 查询薪水大于2000 小于2500
 select * from emp where sal >2000 and sal<2500

--查询入职时间早于 
 select * from emp where hiredate < '17-9月 -81';
  --模糊查询  %表示通配任意个字符  _表示通配一个字符
--查询名字中包含K字符的结果
  select * from emp where ename like '%K%'
 --查询第三个字母为o的员工
 select * from emp where ename like '__O%';
 --在数组里面出现过的数据都查询出来
  select * from emp where empno in (7369,7844,7521);
--显示没有上司的员工 
   select * from emp where mgr is null;
 --使用别名查询 as可省略
   select empno as 编号,ename as 姓名, sal as 月薪 from emp where ename like 'S%'; 
 --使用别名查询 SMITH的编号 和年薪
select empno 编号,ename 姓名 ,sal*12 年薪 from emp where ename='SMITH';
  --查询工资大于500 或者工作为MANAGER 并且姓名首字母为J开头
  select * from emp where (sal>500 or  job='MANAGER') and ename like 'J%';
  --查询所有的数据，按照薪水从高到低依次排列 
  select * from emp order by sal desc;
  --从低到高
   select * from emp order by sal asc;
   
   --统计所有员工的编号姓名年薪（月薪+奖金）
   select empno 编号 ,ename 姓名,(sal+nvl(comm,0))*12 年薪 from emp;
   --根据带奖金的年薪 排序
   select empno 编号 ,ename 姓名,(sal)*12 年薪 from emp order by 年薪 desc;
   -- 分组函数 max min abg sum count 
   --查询出工资月薪最高的员工的编号 姓名 月薪
   select empno,ename,sal from emp where sal=(select max(sal) 月薪 from emp); 
    --查询出工资月薪最高和最低的员工的编号 姓名 月薪
      select empno,ename,sal from emp where sal=(select min(sal)  from emp) or sal=(select max(sal)  from emp);
   --计算有多少员工 
   select count(empno) 员工数 from emp;
   --计算员工月工资薪水总数
   select sum(sal) 总工资 from emp;
   --计算员工平均工资
   select avg(sal) from emp;
   
   --显示平均工资和最高工资 
         select avg(sal) 平均工资,max(sal) 最高工资 from emp;
   --显示每个部门的平均工资和最高工资
   --分组的条件一定要查询出来
   select avg(sal) 平均工资 ,max (sal) 最高工资,deptno 部门 from emp group by deptno;
   --显示每个部门各种岗位的平均工资和最低工资
  select avg(sal) 平均工资 ,min (sal) 最低工资,deptno 部门,job 岗位 from emp group by deptno,job 
  --显示平均工资低于2000的部门和它的平均工资o
   select avg(sal) 平均工资,deptno 部门 from emp group by deptno  having avg(sal)<2000;
   --显示平均工资高于2000的部门和它的平均工资（总裁办(部门编号10)不参与计算）
   select avg(sal) 平均工资,deptno 部门 from emp where deptno!=10 group by deptno  having avg(sal)>2000;
   ---group by 需要注意的是 在select中出现的字段 除了汇总字段以外，全部都要出现在group by里面 
   --如 select avg(sal) 平均工资 ,sal 最低工资,deptno 部门,job 岗位 from emp group by deptno,job 会出现错误
   --因为 sal 出现在了select 中，确没有出现在group by 中 并且 sal不是汇总字段(使用了汇总函数的字段 ,如avg,max,min等)

-------------------------------------多表查询---------------------------------------
   --查询部门表
   select * from dept;
   --显示雇员名，雇员工资 雇员部门名   雇员部门名在dept表中
   select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
   --显示雇员名 雇员工资 雇员工资级别  工资级别在salgrade表中
   select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
   --显示员工名，员工工资，员工工资级别 所在部门名字，并按照部门排序
   select e.ename ,e.sal,s.grade,d.dname from emp e,dept d ,salgrade s 
    where e.deptno=d.deptno and( e.sal between s.losal and s.hisal) order by e.deptno ;
  
   --查询比部门30所有员工工资高的员工的姓名 工资
   select ename,job,sal,deptno from emp where sal > all (select sal from emp where deptno=30);
   select ename,job,sal,deptno from emp where sal >  (select max(sal) from emp where deptno=30);
---查询SMITH的上司 
    --自连接查询 
    select e1.ename,e2.ename 上司 from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename='SMITH'; 
   --嵌套查询
   select ename from emp where empno=(select mgr from emp where ename='SMITH'); 
 --如果子查询返回多个结果，则应该使用in 或者 = any
  select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);
  select ename,job,sal,deptno from emp where job  = any (select job from emp where deptno=10);
   
  --如何查询部分数据
  --rownum rowid  伪列  伪劣是根据查询结果动态生成的
   --分页查询 只显示前5条结果
    select rownum,ename ,job from emp where rownum <=5;
   --查询6-10条 不能用 select rownum,ename ,job from emp where rownum <=10 and rownum>=5;
   --而要  
   select * from (select rownum r,ename,job from emp where rownum <=10) where r>=5;
   --合并查询   集合操作符 union   合并结果 去掉重复行
   select * from emp where job='SALESMAN'union select * from emp where sal>=1500;
   --union all 合并结果 保留重复行
   select * from emp where job='SALESMAN'union all select * from emp where sal>=1500;
   --取交集  intersect 
   select * from emp where job='SALESMAN'intersect select * from emp where sal>=1500;
   --取差集 第一个集合中有第二个集合中没有的   minus
   select * from emp where job='SALESMAN' minus select * from emp where sal>=1500;