数据库-Oracle学习笔记（8）

3月9日

DQL语言

查询语言的基本结构
select 字段…
from 表名
where 条件;

使用order by子句 – 排序

• 1）语法：select 字段…
  from 表名
  where 条件
  order by 列名;
  强调：order by子句必须放在最后使用。
• 2）asc和desc的使用
  asc用来指定升序排序（默认）
  desc用来指定降序排序

  查询员工表中所有的员工姓名，部门号
  select ename,deptno 
  from emp;
  查询员工表中所有的员工姓名，部门号，按照部门号进行排序
  select ename, deptno
  from emp
  order by deptno;
  使用desc降序
  select ename, deptno
  from emp 
  order by deptno desc;

• 3）对于列有空值null的排序
  null值视为最大，如果升序排列，排在最后
  如果降序排列，排在最前

  默认升序
  select * 
  from emp
  order by comm ;
  降序--必须写desc
  select * 
  from emp
  order by comm desc;
  练习：
  查询员工表中在10部门的员工编号，员工姓名和领导的编号，然后需要按照领导编号降序排序
  select ename，empno，mgr
  from emp 
  where deptno=10
  order by mgr desc;

• 4）多个列排序
  当以多列为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。
  按照多列排序，order by子句后每一列使用逗号分隔开。

  查询员工表中所有的员工信息，按照部门号升序，底薪降序排列。
  select *
  from emp
  order by deptno asc, sal desc;
  查询员工表中所有的员工信息，按照部门号升序，底薪降序，入职日期升序排列。
  select * 
  from emp
  order by deptno asc, sal desc, hiredate asc;

3月10日

聚合函数

聚合函数也称为分组函数、多行函数、集合函数，主要是参与运算会返回一个结果。
当查询时需要做一些数据的统计，比如：查询员工表中各部门员工的平均薪资，最高薪资，最低薪资等；

• 1）聚合函数-- max函数和min函数
  用来取得列中的最大、最小值
  可以用来统计任何数据类型，包括数字、字符串和日期

  查询员工表中最高底薪，和最低底薪
  select sal from emp;
  select max(sal), min(sal) from emp;
  查询员工表中最早入职和最晚入职的时间。
  select min(hiredate), max(hiredate) from emp;

• 2）聚合函数-- avg函数和sum函数
  用来统计列的平均值和总和值
  只能操作数字类型，忽略null值

  查询员工表中所有员工的平均薪水和薪水总和
  select  avg(sal), sum(sal) from emp;

• 3）聚合函数 – count函数
  用来记录条数
  忽略null值

  用法：count(列名)   统计该列共几条数据
        count(*)      统计表共有几条数据（往表中insert插入了几次，count*统计出来的就是几条）

  查询员工表中所有的职位
  select job from emp;  
  查看员工表中有多少人是有职位的
  select count(job) from emp;
  查看员工表中所有人的提成
  select comm from emp;
  查看员工表中有多少人是有提成的
  select count(comm)  from emp;    --注意会忽略空值
  查询员工表中共几条数据(一共有多少名员工)
  select count(*) from emp;

4）强调：聚合函数对空值的处理问题
聚合函数是忽略空值
使用聚合函数对存有空值的列使用的时候会出现一些问题

  comm列--提成列
  select count(comm), sum(comm), avg(comm) from emp;   4   2200 550
  select count(comm), sum(comm), avg(nvl(comm,0)) from emp;   4  2200 157.142857
  50人，1个人100分，其余要么没考试，要么0分。    平均分100/50=20

张三 李四 王五
100 80 计算总分，忽略空值无影响
100+0+80=180
100+80=180
计算平均分，忽略空值有影响
180/2=90 错
180/3=60 对

加上order by子句的一个完整的sql查询语言
select 列
from 表
where 条件
order by 列;

分组 – group by子句

分组+排序的SQL查询完整语句结构：
select 列
from 表
group by 列
having 条件
order by 列;

强调：having子句用来对分组以后的结果进行进一步的限制。比如按照部门分组后，按照每个部门的最高薪水进一步加条件的限制，或者按照每个部门的平均薪水加条件显示等等。
having子句必须跟在group by子句的后面，不能单独使用。
（使用group by可以不用having，但是使用having必须用group by，如果不是进行group by分组单独加条件只能使用where，having不能单独使用。）

  select * from emp where sal>1000;   对
  select * from emp having sal>1000;  错，错，错

  deptno   10部门   1300   2450   5000
           20部门  800   1100  2975  3000 3000
           30部门   950 1250 1250  1500  1600  2850
  查询员工表中每个部门的最高薪水
  select deptno, max(sal)
  from emp
  group by deptno;
  查询员工表中每个部门的最高薪资，最低薪资，平均薪资，薪资总和和员工人数
  select deptno,max(sal),min(sal),avg(sal),sum(sal),count(deptno)
  from emp 
  group by deptno;
  select deptno,max(sal),min(sal),avg(nvl(sal,0)),sum(sal),count(deptno)from emp group by deptno;
  select deptno,max(sal),min(sal),avg(sal),sum(sal),count(*) from emp group by deptno;
  查询员工表中每个部门的最高薪水，而且只有最高薪水大于4000的记录才被显示出来。
  select deptno, max(sal)
  from emp
  group by  deptno
  having max(sal)>4000;
  查询员工表中每个部门的最高薪水，而且只有最高薪水大于1000的记录才被显示出来，并且按照部门升序排列
  select deptno, max(sal)
  from emp
  group by deptno
  having max(sal)>1000
  order by deptno asc;
  查询员工表中每个部门的最高薪水，而且只有最高薪水大于1000的记录才被显示出来，并且最高薪水进行降序排列
  select deptno, max(sal)
  from emp
  group by deptno
  having max(sal)>1000
  order by max(sal) desc;

SQL关联查询

• 1）概念
  实际应用中所需要的数据，经常会需要查询两个或两个以上的表，这种查询叫做关联查询(连接查询)。
• 2）等值连接
  等值连接是连接查询中最常见的一种，通常是使用等于（=）连接相关的表。

  查询员工的姓名，职位，以及所在部门的名字和所在城市 -- 使用等值连接的方式
  emp表     ename,job
  dept表    dname, loc
  select  e.ename, e.job, d.dname, d.loc
  from emp e, dept d
  where e.deptno=d.deptno;

• 3）内连接
  使用内连接进行关联查询，会返回所有满足连接条件的记录。
  语法：使用join连接两个表，使用on作为连接条件
  内连接等价于等值连接，能用内连接完成的也可以用等值连接完成，只是语法（写格式）不一样而已。

  查询员工的姓名，职位，以及所在部门的名字和所在城市 -- 使用内连接的方式
  select  e.ename, e.job, d.dname, d.loc
  from  emp e  join  dept d
  on  e.deptno=d.deptno;