4.查询

一：基本查询

【语法】：select 字段名1，字段名2……  from 表名 [ where条件]；

【where条件常用判断关键词】：

1. 闭区间，包括两边的边界值  between ... and ...   

select empno, ename, sal from emp where sal between 2450 and 3000

2. 值与在括号内列举的值存在相同   in

Select * from emp where sal in (1600,1250,1500)

3.  is null ，条件where后面需要判断值是否为空 

select * from emp where comm is null

4 . =null   ,把数据置空，赋值使用

update emp set job =null where ename='KING'

5. order by  根据字段排序。

Asc表示升序， 可以省略，Desc表示降序，不能省略。

也可以根据多个字段排序，规则现根据第一个字段排序，如果第一个字段中有相同值，再根据第二个字段的规则排序。可以根据数字型排序，也可以是字符型排序，也可以是日期型排序。

select * from emp order by job desc, ename asc

6. group by ...having...  分组查询 

group by：用于对查询的结果进行分组统计（按什么分组）
having子句：用于限制分组显示结果（条件）

--（1） 如何显示每个部门的平均工资？
select deptno,avg(sal) from emp group by deptno
--（2） 如何显示每个部门的每种岗位的平均工资？
select deptno,job,avg(sal) from emp group by deptno,job order by deptno

【总结】

   （1） 如果select语句中同时包含了where -group by 、having、order by ,那么他们

         出现的顺序：select-from- where-group by- having，-order by

 执行顺序：from--where--group by--having--select--order by,

--(4)按照由高到低的顺序显示部门平均工资在1500以上的部门号和平均工资，并且不计算部门员工工资低于1600的
select deptno,avg(sal) from emp where sal>=1600 group by deptno having avg(sal)>=1500 order by avg(sal) desc

   （2）from后面的表关联，是自右向左解析的 ；而where条件的解析顺序是自下而上的。 

也就是说，在写SQL的时候，尽量把数据量大的表放在最右边来进行关联，而把能筛选出大量数据的条件放在where语句的最下面。

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

    （3）聚合函数（avg()、sum()、max()、min()、count()、……）只能出现在 选择列表（select）、having、order by子句中，where后面不能用

二：子查询

定义：嵌入到其他select语句中的select语句，叫 子查询，也叫 嵌套查询。

【单列单行子查询】

   单行子查询：子查询语句 只返回一行数据

   分析方法：

          如：查询与SMITH同一个部门的所有员工

（1）查询（ 与SMITH同一个部门的）所有员工 写出 select * from emp 

（2）分析：（ 与SMITH同一个部门的 ），选择部门，select deptno from emp where ename='SMITH'

（3）合并，并去掉 SMITH 自身数据信息

--(1) 如何查询与SMITH同一个部门的所有员工？
update emp set ename=upper(ename)   --把ename名字全部改成大写
select ename from emp where  deptno=(select deptno from emp where ename='SMITH') and ename!='SMITH'

--(2)如何查询出和ALLEN岗位相同的员工的信息？
select * from emp where job=(select job from emp where ename='ALLEN') and ename!='ALLEN'

【单列多行子查询】

  多行子查询：子查询语句 返回多行数据

       注意：单行子查询是指只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的

--（1）1.如何查询出和10号部门工作相同的雇员的名字、岗位、工资和部门号
select ename,job,sal,deptno from emp where  job in (select job from emp where deptno=10)

--(2) all的使用方法
--如何显示工资比30号部门所有员工工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where  deptno=30)

--(3) any的使用方法
--如何显示工资比30号部门任意一个员工工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30)

【多列子查询】

多列子查询：指查询返回多个列数据的子查询语句。

--(1)如何查询ALLEN的部门和岗位完全相同的所有雇员
select * from emp where (deptno,job)=(select deptno,job from emp where ename='ALLEN')  and ename!='ALLEN'

   分析技巧：分析问题的条件时都需要相等可以用 【in】或者【分表】 ，有一个不等就 【分表】

--(2)如何显示高于自己部门平均工资的员工信息？
--  部门相等，工资不等  ，用【分表】
select t2.*,avgSal from(select avg(sal) avgSal,deptno from emp group by deptno) t1, emp t2 where t2.deptno=t1.deptno and t2.sal>t1.avgSal

--(3)查询每个部门工资最高的人的详细信息？
-- 部门相等 ，工资等于最高工资  用【in】或【分表】
select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno) 
select t2.* from(select max(sal) maxsal,deptno from emp group by deptno) t1,emp t2 where t2.deptno=t1.deptno and t2.sal=t1.maxsal;

这种时候用分表更容易分析：

    

--(7)显示每个部门的信息(编号、名称和人员数量)
select t1.deptno,dname,allcount from (select count(*) allcount,deptno  from emp group by deptno) t1,dept where t1.deptno=dept.deptno

三：分页查询

问题：如何查询第n条到第m条数据    

方法：使用rownum，但是rownum永远从1开始，故而：

把rownum作为一个表的字段插入，再在外查询里选取新添加的rownum这个字段进行条件查询

  rownum永远从1开始：

select * from emp where rownum<=5
select * from emp where rownum>=5  --不合法的，没有结果，rownum一定从1开始，不管数据是什么，只做排序

  分页查询：

--(1)如何查询第四条到第六条员工的信息？
select t1.* from(select rownum 行数,emp.* from emp) t1 where t1.行数 between 4 and 6

知识点补充：

rowid
--rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。
select emp.*,rowid from emp