--单行子查询
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;
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/