第二章 : 连接、子查询、函数



- -使用 case表达式
- -在 SQL语句中有两种方法被用来实现条件
- -处理(IF - THEN - ELSE 逻辑) ： CASE表达式和DECODE函数。
- -注意： CASE表达式和ANSI SQL一致。
- -DECODE函数是特有的Oracle语法

- - case表达式有两种形式： simple case和searched case

- - 1） simple case：简单 case
- -语法：
case 列名
    when 值 1 then 返回值 1
   [ when 值 2 then 返回值 2
    when 值n then 返回值n
    else 返回值]    - -返回值用“”
end

- -将某个列值和值 1比较，如果相等则返回“返回值 1”；
- -如果不等则将列值和值 2比较，如果相等则返回“返回值 2”；
- -以此类推。如果列值和值 1、值 2、。。。值n都不等，则
- -返回 else后的值。

- -要求所有的返回值必须是同一个数据类型。

select last_name,job_id,salary,
        case job_id
          when 'IT_PROG' then 1. 10 *salary
          when 'ST_CLERK' then 1. 15 *salary
          when 'SA_REP' then 1. 20 *salary
          else salary end "revised_salary"
    from employees;



- - 1）searched case：搜索式 case
- -语法：
case 
    when 列名 =值 1 then 返回值 1
   [ when 列名 =值 2 then 返回值 2
    when 列名 =值n then 返回值n
    else 返回值] 
end   

- -以搜索式 case改写上例
select last_name,job_id,salary,
        case 
          when job_id = 'IT_PROG' then 1. 10 *salary
          when job_id = 'ST_CLERK' then 1. 15 *salary
          when job_id = 'SA_REP' then 1. 20 *salary
          else salary end "revised_salary"
    from employees;

- -decode函数实现和 case表达式一样的功能
select last_name,job_id,salary,
       decode(job_id
              , 'IT_PROG' , 1. 10 *salary
              , 'ST_CLERK' , 1. 15 *salary
              , 'SA_REP' , 1. 20 *salary
              , salary ) "revised_salary"
    from employees;

- -使用组函数（聚集函数）
- -组函数对一组一行进行操作并返回一个值。
- -所有的组函数默认情况下会忽略 null值

- - 5个组函数：
- - avg、 max、 min、 sum、 count

- -查询所有销售代表的平均月薪、最低月薪、最高月薪和月薪总和
select avg(salary), min(salary),
        max(salary), sum(salary)
   from employees
   where job_id like '%REP%';

- -注意：上面的查询对返回的结果集中的行没有明确地进行分组，
- -那么结果集中的所有行自动形成一个组。然后对该组行应用
- -组函数进行计算，最终返回的结果集中只有一行（因为只有一组）

- -对日期和字符串都可以求最大和最小
- -查询雇员的最早和最晚的入职日期
select min(hire_date), max(hire_date)
   from employees;

select min(last_name), max(last_name)
   from employees;  


- - count函数：返回表中满足条件的行的数量
- -常见 3种写法：
- - COUNT( *) 
- - COUNT(expr)
- - COUNT( DISTINCT expr)

- - 1、 COUNT( *)：返回表中满足 where条件的行的数量 
- -查询部门 50的雇员的数量
select count( *)
   from employees
   where department_id = 50;

- -查询雇员的总数量
select count( *)
   from employees;


- - 2、 COUNT(列)：返回表中列值非空的行的数量  
- -查询拿佣金的雇员的数量
select count(commission_pct)
   from employees;

- - 3、 COUNT( distinct 列)：返回表中列值非空的并且列值不重复的行的数量   
- -查询所有雇员分布的部门的数量
select count( distinct department_id)
   from employees;

- -组函数和 null值的关系
- -组函数默认值忽略列值为 null的行

- -计算公司的平均佣金百分比，要求只有拿佣金的雇员参与计算
select avg(commission_pct)
   from employees;

- -计算公司的平均佣金百分比，要求所有的雇员参与计算
- -这里，使用nvl函数对 null值做转换
select avg(nvl(commission_pct, 0))
   from employees;  


- -使用 group by子句
- -使用 GROUP BY 子句将表中的行分成组。
- -使用组函数返回每一组的统计信息

- -规则：
- - 1）如果在 SELECT子句中包含了组函数，那么对于出现在 SELECT子句中的单独的列，
- -也必须同时包含在 GROUP BY 子句中
- - 2）在 GROUP BY 子句中必须包含列
- - 3）在 GROUP BY 子句中不能使用列别名
- - 4）使用 WHERE子句，可以在分组前排除一些行

- -计算每个部门的平均薪水
select department_id, avg(salary)
   from employees
   group by department_id;

- -注意：分组列可以不出现在 select列表中，但是
- -这样做使得结果集无意义
select avg(salary)
   from employees
   group by department_id;

- -指定多个分组列，可以形成“大组中再分小组”
- -计算每个部门的每个岗位所拿的薪水总和
select department_id,job_id, sum(salary)
   from employees
   group by department_id,job_id
   order by department_id;


- -注意：
- - 1）不能使用 where子句对分组后的结果进行过滤。因为他在
- -分组之前就已经执行完毕了
- - 2）不能在 where子句中使用组函数。因为 where比 group by
- -先执行，而组函数必须要分完组之后才能执行
- - 3）要对分组后的结果进行过滤，必须使用 having子句。在 having
- -子句中可以使用组函数

- -本例显示了最大月薪大于 10000的部门的部门号和最大月薪
select department_id, max(salary)
   from employees
   group by department_id
   having max(salary) > 10000;

- -组函数能被嵌套到两个函数的深度。  
- -本例对每个部门计算平均月薪，然后显示部门的最大平均月薪
select max( avg(salary))
   from employees
   group by department_id;


- -使用多表连接查询
- -连接查询可分为 3类：
- - 1、内连接 inner join：
- -只返回满足连接条件的行。内连接绝大部分都是相等连接

- - 2、外连接 outer join
- -除了返回满足连接条件的行之外，还返回某些不满足连接条件的行。
- -外连接绝大部分也都是相等连接

- - 3、交叉连接 cross join
- -又叫做笛卡尔积。没有连接条件的连接

- -内连接：
- -关键字有： natural join、 join... using、 join... on

- - 1） natural join
- -这种连接会自动地把两个表中所有同名同类型的列拿来作为
- -连接列，做相等连接。用户不能挑选连接列。从代码上不能
- -看到连接条件

- -查询每个部门的编号、名称、所在位置编号以及所在城市名称
select department_id,department_name,
       location_id,city
from departments
natural join locations;

- - 2） join... using：
- -使用 using子句指定连接条件。 using指定的列是两个表中
- -同名同类型的列，做相等连接。他比 natural join直观，
- -并且可以挑选连接列

- -查询每个部门的编号、名称、所在位置编号以及所在城市名称
select department_id,department_name,
       location_id,city
from departments
join locations
using (location_id);

- -使用 using有一个语法限制：
- - using用到的列，在语句的任何地方（包括 using自己）
- -都不能加表别名来限定它

- -ORA - 25154: USING 子句的列部分不能有限定词
select d.department_id,d.department_name,
       d.location_id,l.city
from departments d
join locations l
using (location_id);

- -ORA - 01748: 此处只允许简单的列名
select d.department_id,d.department_name,
       location_id,l.city
from departments d
join locations l
using (d.location_id);


- - 3) join... on:
- -使用 on子句来指定连接条件。
- -它没有 using的语法限制，而且连接列可以不同名
select d.department_id,d.department_name,
       d.location_id,l.location_id,l.city
from departments d
join locations l
on (d.location_id = l.location_id);

- -使用 on写连接条件是 sql标准推荐的写法，
- -但是实际当中写连接老式的写法更常见
- -老式的写法：所有要连接的表名写在 from子句中，
- -连接条件写在 where子句中
select d.department_id,d.department_name,
       d.location_id,l.location_id,l.city
from departments d,locations l
where d.location_id = l.location_id;

- -老式写法的弊端：他把连接条件写在了 where子句中，
- -而 where本身是过滤条件，这样容易造成混淆，代码
- -可读性差。而 on子句专门写连接条件，和过滤条件分开，
- -代码可读性很好。
select d.department_id,d.department_name,
       d.location_id,l.location_id,l.city
from departments d,locations l
where d.location_id = l.location_id
    and l.location_id = 1700;

select d.department_id,d.department_name,
       d.location_id,l.location_id,l.city
from departments d
join locations l
on (d.location_id = l.location_id)
where l.location_id = 1700;

- - 3表连接查询
- -使用多个 join   on子句
- -查询雇员的编号、所在部门的名称以及部门所在的城市名称

- -注意：只有 106个雇员， grant雇员的部门号为 null。不满足
- -连接条件
select e.employee_id,d.department_name,
       l.city
   from employees e
   join departments d
   on (e.department_id = d.department_id)
   join locations l
   on (d.location_id = l.location_id);


- -自连接查询（ self join）：
- -一张表连接到它自己。
- -技巧：给同一张表给两个不同的别名，看成是两张表，然后
- -做连接。

- -做自连接查询的表有个特点：
- -该表的外键引用的是本表的主键，这叫做自引用

- -查询每个普通雇员的名字及其对应的经理的名字
select worker.last_name,manager.last_name
   from employees worker
   join employees manager
   on (worker.manager_id = manager.employee_id);

- -不等连接：
- -连接条件不使用等号。很少见。

create table job_grades(
  grade_level varchar2( 1),
  lowest_sal number,
  highest_sal number
);

insert into job_grades
   values( 'A', 1000, 2999);

insert into job_grades
   values( 'B', 3000, 5999);

insert into job_grades
   values( 'C', 6000, 7999);

- -查询每个雇员的姓名、薪水以及工资级别
select e.last_name,e.salary,
      j.grade_level
   from employees e
   join job_grades j
   on e.salary between j.lowest_sal and j.highest_sal;

- -外连接 outer join
- - 3种：
- -左外连接、右外连接和全外连接

- - 1） left join：左外连接
- -除了返回两张表中所有满足连接条件的行之外，还返回左表中
- -所有不满足连接条件的行。所谓左表，就是写在 left关键字
- -左边的表

- -查询每个雇员的姓名、所在部门的编号及名称，要求
- -没有部门的雇员也要显示
select e.last_name,e.department_id,
       d.department_name
   from employees e
   left join departments d
   on (e.department_id = d.department_id);


- - 2） right join：右外连接
- -除了返回两张表中所有满足连接条件的行之外，还返回右表中
- -所有不满足连接条件的行。所谓右表，就是写在 left关键字
- -右边的表

- -查询每个雇员的姓名、所在部门的编号及名称，要求
- -没有雇员的部门也要显示
select e.last_name,e.department_id,
       d.department_name
   from employees e
   right join departments d
   on (e.department_id = d.department_id);


- - 3） full join：全外连接
- -除了返回两张表中所有满足连接条件的行之外，还返回左表以及右表中
- -所有不满足连接条件的行。

- -查询每个雇员的姓名、所在部门的编号及名称，要求
- -没有部门的雇员以及没有雇员的部门也要显示
select e.last_name,e.department_id,
       d.department_name
   from employees e
   full join departments d
   on (e.department_id = d.department_id);

- -笛卡尔积连接
- -如果连接查询没有写任何连接条件，那么该查询的结果集
- -就叫做笛卡尔积。结果集的记录行数等于两个的总行数的乘积。

- -应该避免连接查询产生笛卡尔积，特别是大表。

- -使用 cross join关键字可以专门产生笛卡尔积连接，
- -可用于测试目的

select last_name,department_name
   from employees
   cross join departments;

- -使用子查询（subquery）
- -子查询相当于把两个或者多个顺序执行的查询合并
- -到一个 select查询中

- -子查询分两种：
- - 1、不相关子查询：先执行子查询，然后再执行外部查询
- - 2、相关子查询：先执行外部查询，然后再执行子查询

- - 1、不相关子查询
- -单行子查询和多行子查询

- -什么时候用子查询？
- -如果一个查询需要的结果需要经过两步或者两步以上的中间
- -计算过程才能得到，就可考虑使用子查询

- -子查询要放在小括号中

- -查询赚的比亚伯多的雇员的信息
select last_name,salary
   from employees
   where salary > ( select salary
                     from employees
                     where last_name = 'Abel');

- -子查询也可以放在操作符的左边，但是没人这么写。
select last_name,salary
   from employees
   where ( select salary
                     from employees
                     where last_name = 'Abel') < salary  ;

- -单行子查询：
- -子查询返回一行一列一个值

- -对于外部查询来说，要比较单行子查询的返回值，就要使用
- -单行比较操作符： =, >, <, > =, < =, <>

- -操作和Chen干同样的工作，但是赚的比Chen多的雇员的信息
select last_name,salary,job_id
   from employees
   where job_id = ( select job_id
                  from employees
                  where last_name = 'Chen')
    and salary > ( select salary
                  from employees
                  where last_name = 'Chen');

- -在子查询中可以使用组函数
- -查询拿公司最低薪水的雇员的信息
select last_name,salary,job_id
   from employees
   where salary = ( select min(salary)
                     from employees);

- -特别注意：如果子查询的结果集为空，那么
- -当它的值用在了外部查询的 where条件中时，会导致
- -外部查询的 where条件也为空，从而外部查询的结果集为空

select last_name,job_id
   from employees
   where job_id = ( select job_id
                       from employees
                       where last_name = 'Haas');

- -多行子查询
- -多行子查询返回多行单列多个值

- -在外查询中，要对多行子查询的返回值进行比较，必须使用
- -多行比较操作符。 3个
- - in、 any、 all

- -注意： any all不能单独使用，其前面必须加上单行比较操作符，
- -例如： < any   > any   < all > all = any等等

- -查询工作不是IT程序员，并且工资少于任何一个程序员
- -的雇员的信息
select last_name,job_id,salary
   from employees
   where salary < any ( select salary
                       from employees
                       where job_id = 'IT_PROG')
   and job_id <> 'IT_PROG';


- -查询工作不是IT程序员，并且工资少于所有程序员
- -的雇员的信息
select last_name,job_id,salary
   from employees
   where salary < all ( select salary
                       from employees
                       where job_id = 'IT_PROG')
   and job_id <> 'IT_PROG';


- -特别注意：
- -当外部查询的 where条件中使用 not in操作符时，
- -如果子查询返回的结果集中有一个 null值，就会
- -导致外部查询的 where条件也为 null，从而使得
- -外部查询的结果集为空

- -查询所有普通雇员（不是经理）的姓名
select last_name
   from employees
   where employee_id not in ( select manager_id
                               from employees);

- -解决办法：过滤掉子查询结果集中的 null值
select last_name
   from employees
   where employee_id not in ( select manager_id
                               from employees
                               where manager_id is not null);

- -使用集合操作符
- -集合操作符用于将两个或者多个查询的结果集进行合并，
- -生成一个结果集

- - 4个：
- - union、 union all、 intersect、minus

- -所有集合操作的语法：
- -两个查询的 select列表必须在数量上以及相应列的
- -数据类型上匹配。

- - 1、 union
- -将两个查询的结果集直接合并成一个
- - 1）会去掉两个查询结果集中的重复行
- - 2）最终的结果集默认以第一个查询的第一个列做升序排序

- -显示所有雇员当前的以及曾经干过的工作
select employee_id,job_id
   from employees
union
select employee_id,job_id
   from job_history;


- - 2、 union all
- -将两个查询的结果集直接合并成一个
- - 1）不会去掉两个查询结果集中的重复行
- - 2）最终的结果集默认不排序

- -显示所有雇员当前的以及曾经干过的工作和部门
select employee_id,job_id,department_id
   from employees
union all
select employee_id,job_id,department_id
   from job_history
order by employee_id;

- - 3、 intersect:交集
- -返回两个查询的结果集中重复的行
- - 1）最终的结果集默认以第一个查询的第一个列做升序排序

- -查询现在干的工作和曾经干过的工作相同的雇员的编号和工作
select employee_id,job_id
   from employees
   intersect
select employee_id,job_id
   from job_history;  


- - 4、minus:减
- -返回第一个查询的结果集减去第二个查询结果集中相同的行之后所剩余的行
- - 1）最终的结果集默认以第一个查询的第一个列做升序排序

- -查询从未换过工作的雇员的编号
select employee_id
   from employees
minus
select employee_id
   from job_history;

- -匹配 select列表
- -问题：假设你想把以下两个查询的结果集合并成一个来查看，
select location_id,department_name
   from departments;

select location_id,state_province
   from locations;

- -虽然两个查询的 select列表是匹配的，但是department_name列
- -和state_province列的业务含义是完全不同的，不应该
- -直接合并在一起引起混乱。

- -解决办法：一个查询没有的值就给它提供空值
select location_id,department_name,to_char( null) "Warehouse location"
   from departments
union all  
select location_id, to_char( null) ,state_province
   from locations;

- -注意：合并后结果集的列名是由第一个查询的列名决定的，
- -和第二个查询无关


- -使用dml语句对表的数据（行）进行增删改
- -dml语句有 4条：
- - insert 、 delete、 update、merge（合并）

- - 1、 insert：插入一个新行到表中

- -带 values子句的 insert语句一次只能插入一行

- -插入新行到表中，要求构成该行的所有列都必须进行赋值。
- -赋值分为隐式的和显式的两种。如果你不明确地给某个列
- -赋值，那么oracle会自动地给它赋 null值。这就叫做
- -隐式的赋值

- - insert语句有多种写法：
- - 1、插入新行时，明确地把所有列的列名写出来并赋值。
- -这是最规整的写法。

- -插入一个新的部门
insert into departments(department_id,department_name,manager_id,location_id)
   values( 280, '市场部', 100, 1700);

- - insert操作的返回值不是结果集，而是一个整数，表示
- -插入的行数

- - - - 2、插入新行时，只给部分列明确地提供值，那么没有
- -明确赋值的列将自动得到 null值

insert into departments(department_id,
          department_name)
   values( 290, '销售部');

- - - - 3、插入新行时，不写出列的列表。这就要求
- - values子句中必须按照列的定义顺序给每个列依次提供值
insert into departments
   values( 300, '公共部', null, null);

- -在 values子句中，经常使用系统函数来给某些列
- -提供特殊值。常用的函数有 user、sysdate、to_date等

create table insert_special(
   name varchar2( 20),
  dob date
);

insert into insert_special( name,dob)
   values( user,sysdate);

insert into insert_special( name,dob)
   values( user,to_date( '1980-10-10', 'YYYY-MM-DD'));  

- -带子查询的 insert语句
- -语法：
- - INSERT INTO table [ column (, column) ] subquery; 

- -使用这种语法，一条语句可以插入大量的行。他将子查询返回的结果集
- -插入到目标表中

- -语法限制：如果在 insert子句中列出了列的列表，那么
- -在子查询的 select列表中必须和他在数量上匹配

create table sales_reps
as select employee_id id,last_name name,
           salary,commission_pct
      from employees
      where 1 = 2;

- -把所有的销售插入到sales_reps表中
insert into sales_reps(id, name,salary,commission_pct)
   select employee_id ,last_name ,
           salary,commission_pct
      from employees
      where job_id like '%REP%';

- - 2、 update：修改表中已有行的一个或者多个列的值
- -一条 update语句可以修改表中的 1行或者多行

- -将 113号雇员的部门号改为 50
create table copy_emp
   as select * from employees;  

update copy_emp
   set department_id = 50
   where employee_id = 113;

- -没有 where条件的 update语句会把表中的所有行都修改
- -将所有雇员的部门号改为 110
update copy_emp
   set department_id = 110;

- - update语句也可以带子查询
- -将 113号雇员的工作和薪水改成和 205号雇员一样
update copy_emp
   set job_id =( select job_id
                 from employees
                 where employee_id = 205),
      salary =( select salary
                 from employees
                 where employee_id = 205)
   where employee_id = 113;