子查询一

--单行子查询
select first_name,last_name from customers
where customer_id=(select customer_id from customers where last_name='Brown');
--John Brown


select product_id,name,price from products
 where price>(select avg(price) from products);
 2 Chemistry 30
3 Supernova 25.99


select * from products;
select product_type_id,avg(price) from products
group by product_type_id
having avg(price)
group by product_type_id);


select max(avg(price)) from products
group by product_type_id
24.975


select prds.product_id,price,purchases_data.product_count 
from products prds,
(select product_id,count(product_id) product_count from purchases
group by product_id) purchases_data
where prds.product_id=purchases_data.product_id;
--从product表中检索出 product_id 与 price列 再从另外一个表中检索出购买产品的次数


--单行子查询不能使用order by


单行子查询最多返回一行（如果子查询返回多行就会出现如下错误）
select product_id,name from products
where product_id=(SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');--使用等于  两边内容并不等  所以就产生了错误
01427. 00000 -  "single-row subquery returns more than one row"




改成 in 则变为多行子查询 便不会出错
select product_id,name from products
where product_id in (SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');


(not in)
select product_id,name from products
where product_id not in (SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');


any操作符
select salary from employees;
800000
600000
150000
500000
select low_salary from salary_grades;
1
250001
500001
750001


select employee_id,last_name,salary from employees
where salary< any (select low_salary from salary_grades);
3 Hobbs 150000
4 Jones 500000
2 Johnson 600000


all操作符
select employee_id,last_name,salary from employees
where salary> all (select high_salary from salary_grades);
no rows selected


多列子查询
select product_id,product_type_id,name,price
from products
where (product_type_id,price) in
(select product_type_id,min(price) from products group by product_type_id);
1 1 Modern Science 19.95
4 2 Tank War 19.95




关联子查询
关联子查询会引用sql语句中的一列或多列，这种子查询之所以被称为关联子查询
是因为它们通过相同的列与外部的sql语句关联


例
查询检索那些价格高于同类产品平均价格的产品
select product_id,product_type_id,name,price from products outer
where price>(select avg(price) from products 
inner where inner.product_type_id=outer.product_type_id);
2 1 Chemistry 30
3 2 Supernova 25.99


外部查询从products表中检索出所有的行，并将其传递给内部查询
内部查询一次读取外部查询传递来的每一行数据，并对内部查询中product_type_id
等于外部查询中product_type_id值得每种产品计算平均价格


在关联查询中使用EXISTS和NOT EXISTS
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES OUTER
WHERE EXISTS
  (SELECT EMPLOYEE_ID FROM EMPLOYEES INNER 
    WHERE INNER.MANAGER_ID=OUTER.EMPLOYEE_ID);
    
1 Smith
2 Johnson




SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES OUTER
WHERE EXISTS
  (SELECT 1 FROM EMPLOYEES INNER 
    WHERE INNER.MANAGER_ID=OUTER.EMPLOYEE_ID);
1 Smith
2 Johnson


NOT EXISTS
检索从来未曾卖出的产品
select product_id,name from products outer
where not exists
(select 1 from purchases inner
where inner.product_id=outer.product_id);
3 Supernova
4 Tank War


EXISTS与in不同 EXISTS注重检查行的存在性 而in则检查实际值的存在性
select * from PRODUCT_types;
1 Book
2 Video
3 DVD
4 CD
5 Magazine
create table product_types_1(id number,name varchar2(20));
insert into product_types_1  select * from product_types;
update product_types_1 set name=' ' where ID<5;


select id,name from product_types_1 outer
where not exists
(select 1 from products inner
where inner.product_type_id=outer.id);


select id,name from product_types_1 outer
where id not in
(select product_type_id from products);




--嵌套子查询 嵌套层次最多为255
外部查询
select product_type_id,avg(price) from products
group by product_type_id
having avg(price)
  子查询
  select max(avg(price)) from products where product_type_id in()
    嵌套子查询
    select product_id from purchases where quantity>1;
  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29749380/viewspace-1347202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29749380/viewspace-1347202/