oracle SQL高级编程第1章-札记1

下订单超过4次的女顾客查询：

select c.customer_id, count(o.order_id) as orders_ct
from oe.customers c
join oe.orders o
on c.customer_id = o.customer_id
where c.gender = 'F'
group by c.customer_id
having count(o.order_id) > 4
order by orders_ct, c.customer_id

 注意：group by子句

select 列表中的任何非聚合字段都必须包括在group by 表达式中
group by 还可以包含两个附加运算 ROLLUP和CUBE

 标量子查询：在select中使用另外一个select 语句来产生结果中的一列值，这个查询只能返回一行一列的值，这种类型的查询称为标量子查询

例子：

select  c.customer_id, c.cust_first_name||' '||c.cust_last_name,
		(select e.last_name 
		 from hr.employees e 
		 where e.employee_id = c.account_mgr_id) acct_mgr
from oe.customers c;

 子查询插入：

insert into scott.bonus (ename, job, sal)
select ename, job, sal * .10
from scott.emp;

 多表插入：

insert all
--小于10000插入small_customers
when sum_orders < 10000 then
into small_customers
--介于10000~100000插入medium_customers
when sum_orders >= 10000 and sum_orders < 100000 then
into medium_customers
--大于100000插入 large_customers
else
into large_customers
select customer_id, sum(order_total)  sum_orders
from oe.orders
group by customer_id ;

  update语句：

--创建表利用已有表的数据
create table employees2 as select * from hr.employees ; 
--添加主键 
alter table employees2 add constraint emp2_emp_id_pk primary key (employee_id) ;
--1.使用表达式更新一个单列的值
update employees2 set salary = salary * 1.10 -- increase salary by 10% 
where department_id = 90 ; 
--2.通过子查询更新一个单列的值 
update hr.employees employees set salary = 
(select employees2.salary from employees2 where employees2.employee_id = 
employees.employee_id and employees.salary != employees2.salary) 
where department_id = 90 ; 
--3.通过在where子句中使用子查询确定要更新的单列的值 
update hr.employees set salary = salary * 1.10 where department_id 
in (select department_id from departments where department_name = 'Executive') ;
--4.通过select语句定义表及列的值来更新表 
update (select e1.salary, e2.salary new_sal 
from hr.employees e1, employees2 e2 
where e1.employee_id = e2.employee_id and e1.department_id = 90) 
set salary = new_sal; 
--5.通过子查询来更新多列
update hr.employees employees 
set (salary, commission_pct) = 
(select employees2.salary, .10 comm_pct 
from employees2 where employees2.employee_id = 
employees.employee_id and employees.salary != employees2.salary) 
where department_id = 90 ; 

  delete语句：

 --1.通过where 字句筛选的条件进行删除 
delete from employees2 where department_id = 90;
 --2.使用from子句的子查询删除 
delete from (select * from employees2 where department_id = 90);
 --3.通过where 字句中的子查询来进行删除
 delete from employees2 where department_id in 
(select department_id from hr.departments where department_name = 'Executive');

 

merge语句：同时完成了插入，更新，删除

merge into dept60_bonuses b
using (
select employee_id, salary, department_id
from hr.employees
where department_id = 60) e
on (b.employee_id = e.employee_id)
when matched then
update set b.bonus_amt = e.salary * 0.2
where b.bonus_amt = 0
delete where (e.salary > 7500)
when not matched then
insert (b.employee_id, b.bonus_amt)
values (e.employee_id, e.salary * 0.1)
where (e.salary < 7500);