第五章：子查询、字典、merge



- -查询数据字典
- -查看自己的视图的信息
desc user_views;

select view_name
   from user_views;

- -查看视图定义的 select语句

select text
   from user_views
   where view_name = 'DEPT_SUM_VU';

- -查看自己的序列的信息
desc user_sequences;

select * 
   from user_sequences;

- -查询自己的索引的信息
- -两个视图
desc user_indexes;

- -查询employees表上的索引名称、类型以及唯一性
select index_name,index_type,uniqueness
   from user_indexes
   where table_name = 'EMPLOYEES';

- -查询索引分别定义在哪个列上
desc user_ind_columns;

select index_name, column_name
   from user_ind_columns
   where table_name = 'EMPLOYEES';

- -查询同义词的信息
select *
   from user_synonyms;

- -使用 comment语句给表和列添加注释
comment on table copy_emp
is '这是employees表的副本，用来测试';

- -从数据字典查询
select *
   from user_tab_comments;

comment on column copy_emp.email
is '邮箱地址';

select *
   from user_col_comments
   where table_name = 'COPY_EMP';

- -没有权限。
select *
   from dba_col_comments
   where table_name = 'COPY_EMP';

- -操纵大数据集
- -子查询
- -内联视图（inline view）：子查询写在 from子句中时，就叫做
- -内联视图。内联视图就是查询的数据源。

- -在你没有必须的权限来创建数据库视图的情况下，或者当你想
- -测试一个要变成视图的 SELECT语句的合适性的情况下，
- -你就可以使用一个内联视图。

- -查找所有在欧洲的部门的名称及所在城市的名称
select department_name,city
   from departments
   natural join ( select l.location_id,l.city,l.country_id
                   from locations l
                   join countries c
                     on(l.country_id = c.country_id)
                   join regions r using(region_id)
                   where r.region_name = 'Europe'
                  );

- -可以把子查询作为 insert语句的目标，
- -本质上就是往视图里插入

- -插入一个欧洲的部门
insert into ( select l.location_id,l.city,l.country_id
                   from locations l
                   join countries c
                     on(l.country_id = c.country_id)
                   join regions r using(region_id)
                   where r.region_name = 'Europe')
   values( 3300, 'Cardiff', 'UK');

- -在子查询中也可以带 with check option选项，
- -防止插入非法数据
- - SQL 错误: ORA - 01402: 视图 WITH CHECK OPTION where 子句违规
insert into ( select l.location_id,l.city,l.country_id
                   from locations l
                   where country_id in (
                     select country_id
                       from countries c                    
                       join regions r using(region_id)
                       where r.region_name = 'Europe')
                   with check option
                  )
   values( 3600, 'Washton', 'US');


- - DEFAULT关键字可用在 INSERT and UPDATE 语句中来确定一个默认的列值。如果没有默认值存在，就使用 null值。
- - DEFAULT选项可让你摆脱必须在你的程序里硬编码默认值或者查询字典来找到它。
create table deptm3
as select * from departments;

insert into deptm3(department_id,department_name,manager_id)
   values( 340, 'bgs', default);

alter table deptm3
   modify (manager_id number( 6, 0) default 100);

update deptm3
   set manager_id = default
   where department_id = 340;

- -使用多表 insert语句
- -在多表 INSERT语句中，你可以把从一个子查询返回的行
- -插入到一个或多个表中。
- -多表 INSERT语句在数据仓库场景中是有用的。

- - 4种类型
- - 1、无条件的 insert all
- -将子查询返回的每一行无条件地插入到所有的目标表中

create table sal_history
as select employee_id empid,hire_date hiredate,
          salary sal
      from employees
      where 1 = 2;

create table mgr_history
as select employee_id empid,manager_id mgr,
          salary sal
      from employees
      where 1 = 2;     

- -将编号大于 200的所有雇员同时插入到上面两张表中
insert all
   into sal_history values(empid,hiredate,sal)
   into mgr_history values(empid,mgr,sal)
   select employee_id empid,manager_id mgr,
          hire_date hiredate,salary sal
      from employees
      where employee_id > 200; 

- - 2、有条件的 insert all：
- -将子查询返回的每一行插入到满足条件的所有的目标表中
create table emp_history
as select employee_id empid,
          hire_date hiredate,salary sal
      from employees
      where 1 = 2;

create table emp_sales
as select employee_id empid,commission_pct comm
          ,salary sal
      from employees
      where 1 = 2;      

insert all
   when hiredate < to_date( '2005-01-01', 'yyyy-mm-dd') then
     into emp_history values(empid,hiredate,sal)
   when comm is not null then
     into emp_sales values(empid,comm,sal)  
   select employee_id empid,commission_pct comm
          ,salary sal,hire_date hiredate
      from employees;


- - 3、有条件的 insert first：
- -将子查询返回的每一行插入到满足条件的第一个目标表中
create table low_sal
as select employee_id,last_name,salary
     from employees
     where 1 = 2;

create table mid_sal
as select employee_id,last_name,salary
     from employees
     where 1 = 2;

create table high_sal
as select employee_id,last_name,salary
     from employees
     where 1 = 2;

insert first
   when salary < 5000 then
     into low_sal values(employee_id,last_name,salary)
   when salary between 5000 and 10000 then
     into mid_sal values(employee_id,last_name,salary)
   else
     into high_sal values(employee_id,last_name,salary)
    select employee_id,last_name,salary
     from employees;

- - - - 4、pivoting insert：
- -实现数据的“列转行”
create table sales_source_data(
  employee_id number,
  week_id number,
  sales_mon number,
  sales_tue number,
  sales_wed number,
  sales_thur number,
  sales_fri number
);

insert into sales_source_data
   values( 176, 6, 2000, 3000, 4000, 5000, 6000);

select * from sales_source_data;

create table sales_info(
  employee_id number,
  week_id number,
  sales number
);

insert all
   into sales_info values(employee_id,week_id,sales_mon)
   into sales_info values(employee_id,week_id,sales_tue)
   into sales_info values(employee_id,week_id,sales_wed)
   into sales_info values(employee_id,week_id,sales_thur)
   into sales_info values(employee_id,week_id,sales_fri)
   select * from sales_source_data;

- -merge语句（dml语句）
- -Oracle服务器对 INSERT, UPDATE, and DELETE 操作支
- -持MERGE语句。使用这个语句，你可以有条件地
- -更新、插入或删除一行，因此避免多条DML语句。
- -决定是更新、插入还是删除是基于 ON子句的条件。

create table copy_emp3
as select * from employees
    where 1 = 2;

- -分别执行 3次，观察copy_emp3表中行的数量变化   
merge into copy_emp3 c
   using ( select * from employees) e
   on( c.employee_id = e.employee_id)
   when matched then
     update set
       c.salary = e.salary 
     delete where (e.commission_pct is not null)
   when not matched then
     insert values(e.employee_id,e.first_name,
      e.last_name,e.email,e.phone_number,
      e.hire_date,e.job_id,e.salary,e.commission_pct,
      e.manager_id,e.department_id);

- -闪回版本查询：使用versions between子句
- -查看某行数据在事务提交前后值的变化

- -查询 107雇员当前的薪水
select salary
   from employees
   where employee_id = 107;

- -更新 107的薪水
update employees
   set salary = salary * 1. 20
   where employee_id = 107;

commit;

update employees
   set salary = salary * 1. 30
   where employee_id = 107;

commit;

- -查看 107雇员更新前后的薪水
select salary
   from employees
  versions between scn minvalue and maxvalue
   where employee_id = 107;

- -使用版本查询时，可以使用伪列versions_starttime,
- -versions_endtime分别查看一个值的开始和结束时间
select versions_starttime,versions_endtime,salary
   from employees
  versions between scn minvalue and maxvalue
   where employee_id = 107;


- -在不同的时区中管理数据
- -用户会话的时区可以使用参数time_zone来进行设置

- -把用户会话的时区设置为西五区
alter session set time_zone = '-05:00';

- -设置默认的日期显示格式
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

- -对比一些日期函数的返回值
select sessiontimezone, current_date from dual;
select sessiontimezone, current_timestamp from dual;
select sessiontimezone, localtimestamp from dual;

- -DBA通过指定 CREATE DATABASE 语句的 SET TIME_ZONE 子句
- -来设置数据库的默认时区。如果省略，那么默认数据库时区是
- -操作系统时区。但是，如果操作系统的时区是oracle不识别的格式，
- -那么，会将数据库的时区设置为 0时区。
- -推荐将数据库的时区设置为 0时区。
- -会话使用 ALTER SESSION语句不能改变数据库时区。
select dbtimezone from dual;

- - timestamp with time zone和
- - timestamp with local time zone类型的重要区别：
- -前一个类型的列值，在存、取的时候不会做任何时区转换，
- -原样存储。
- -而后一个类型的列值，在存的时候，会按照数据库的时区进行
- -转换；在取得时候，会按照用户会话的时区做转换。

create table web_orders(
  order_date timestamp with time zone,
  delivery_time timestamp with local time zone
);

- -插入一个美国客户的订单
insert into web_orders
   values( current_date, current_timestamp + 2);

commit;

- -美国客户查询（西五区）
select * from web_orders;

- -在sqlplus中（东 8区）进行本地客户查询
select * from web_orders;

- -时间间隔类型
- - 2个：
- - interval year[(精度)] to month:保存年到月的时间间隔
- - - interval day[(精度)] to second:保存天到秒的时间间隔
- -两种类型的差别就是保存的值的单位大小不一样

- -精度值不指定，默认都是 2

create table warranty(
  prod_id number,
  warranty_time interval year( 3) to month
);

- -注意字面量的写法：
insert into warranty
   values( 123, interval '8' month);

- - SQL 错误: ORA - 01873: 间隔的前导精度太小
insert into warranty
   values( 123, interval '100' year);

insert into warranty
   values( 456, interval '100' year( 3));

insert into warranty
   values( 789, '200-11');

select * from warranty;

create table lab(
  exp_id number,
  test_time interval day to second
);

insert into lab
   values( 123, '60 00:00:00');

insert into lab
   values( 456, interval '30 03:30:16' day to second);

select * from lab;

- -一些日期时间函数
- - EXTRACT 函数
- -从给定的日期值中抽取出特定的部分
- -语法：
   SELECT   EXTRACT ([ YEAR] [ MONTH][ DAY] 
        [ HOUR] [ MINUTE][ SECOND]
         FROM [datetime_value_expression] [interval_value_expression]);

select extract( year from sysdate)
   from dual;

- -查看每个雇员入职的月份
select last_name, extract( month from hire_date)
   from employees;

- -tz_offset:
- -函数对一个输入值返回时区偏移。

- -对命名表示的时区返回以时间偏移来表示
select tz_offset( 'Canada/Yukon'),
       tz_offset( 'Asia/Shanghai')
   from dual;

- -要得到合法时区名的列表，可以查询V$TIMEZONE_NAMES动态性能视图。
SELECT * FROM V$TIMEZONE_NAMES;

- -日期转换函数
- -to_timestamp:将字符串转换成时间戳类型

select to_timestamp( '2000 12 12 15:30:00',
    'YYYY MM DD HH24:MI:SS')
   from dual;

- -to_yminterval:将字符串转换成年到月的间隔
select hire_date,
       hire_date + to_yminterval( '01-02')
   from employees;

- -to_dsinterval:将字符串转换成天到秒的间隔
select hire_date,
       hire_date + to_dsinterval( '100 10:00:00')
   from employees;  


- -子查询的使用
- -多列子查询
- -子查询返回多行多列多个值
- -在外查询的 where条件中，对多列子查询的返回值应该进行
- -成对比较。

select manager_id,department_id
   from copy_emp
   where first_name = 'John';

- -查询和John在同一个部门工作并且被同一个经理管辖的雇员的信息
select last_name,manager_id,department_id
   from copy_emp
   where (manager_id,department_id) in ( select manager_id,department_id
                                      from copy_emp
                                      where first_name = 'John');

- -标量子查询表达式
- -它是一个子查询，对外部查询返回的每一行会执行一次，
- -并且只返回一个值
- -可用在：
- - 1）decode和 case的条件部分
- - 2）除了 group by之外的所有 select子句中
- - 3） update语句中

select employee_id,last_name,
        case
          when department_id =( select department_id
                                from departments
                                where location_id = 1800) then
                'Canada' else 'USA' end   "location" 
    from employees;

- -相关子查询
- -当子查询引用了父语句中的表的列时，Oracle执行一个相关子查询。
- -相关子查询的执行步骤：
- - 1）先执行外部查询（ from 表），得到一个候选行的集合
- - 2）将候选行传入到子查询中，子查询使用候选行的某个列值来
- -执行查询
- - 3）外部查询使用子查询的返回值来决定候选行是否出现在最终的
- -结果集中
- - 4）重复以上步骤 2和 3，将所有的候选行处理完毕

- -可见，外查询有多少候选行，子查询就执行多少次

- -查询所有的雇员，条件是：这些雇员的薪水要大于他所在部门的
- -平均薪水
select last_name,salary,department_id
   from employees out
   where salary > ( select avg(salary)
                     from employees inner
                       where inner.department_id = out.department_id);

- -查找至少换过 2次工作的雇员
select last_name,salary
   from employees e
   where 2 < = ( select count( *)
                 from job_history
                 where employee_id =e.employee_id);

- - exists操作符
- -他是一个 boolean操作符，返回 true或者 false。
- -它专门用来测试子查询返回的结果集是否为空。
- -如果不为空，则返回 true，否则返回 false。

- -该操作符主要用于相关子查询。

- -查找所有是经理的雇员
select last_name,salary
   from employees e
   where exists ( select 1
                  from employees
                  where manager_id = e.employee_id);

- -注意：子查询 select列表中写字面量 1，是因为
- - exists操作符不关系子查询返回的具体值是什么，只关心
- -结果集中是否有行。因此，指定字面量可以减少一点计算过程，
- -从而提高效率。

- - not exists：

- -查找所有没有雇员的部门
select department_id,department_name
   from departments d
   where not exists( select 1
                     from employees 
                     where department_id = d.department_id);

- -相关更新
- -如果 update语句中使用了相关子查询，那么
- -该 update就叫做相关更新

- -给copy_emp表添加一个department_name列，然后
- -根据每个雇员的部门号，将相应的部门名称填到该列中
alter table copy_emp
   add (department_name varchar2( 20));

update copy_emp c
   set department_name =( select department_name
                          from departments
                          where department_id = c.department_id);

- -相关删除：
- -从在职雇员表中删除所有已经离职的雇员。
- -离职的雇员在emp_history表中
delete from copy_emp c
   where employee_id = ( select employee_id
                           from emp_history
                           where empid = c.employee_id);