子查询收集

select city from suppliers where supplierid in--子查询.相关查询
(select supplierid from products where suppliers.supplierid=products.supplierid)

use pubs
go
select au_lname,au_fname,city from authors where city in  
(select city from authors where au_fname='Livia'or au_lname='Karsen')

--or自联接必须使用表别名
use pubs
go
select au1.au_lname,au1.au_fname,au1.city from authors   au1  
inner join authors   au2 on au1.city=au2.city and au2.au_lname='Karsen'
and au2.au_fname='Livia'
--or
use pubs
go
select au1.au_lname,au1.au_fname,au1.city from authors as au1
where au1.city in (select au2.city from authors as au2  
where au2.au_lname='Karsen'and au2.au_fname='Livia')

--使用in的子查询
use pubs
go
select pub_name from publishers where pub_id  in
(select pub_id  
from titles where type='business')

--上面in子查询同下
use pubs
go --因为先执行子查询,得到的数为'1389''0736',然后执行父查询
select pub_name from publishers where pub_id in('1389','0736')

--联接查询可显示多个表中的列,title,type是titles表中的列
use pubs
go
select pub_name,title from publishers inner join titles  
on publishers.pub_id=titles.pub_id and type='business'

--使用子查询
use pubs
go
select au_lname,au_fname from authors
where state='CA'and au_id in (select au_id
from titleauthor where royaltyper<30 and au_ord=2)

--上面子查询同下联接查询
use pubs
go
select au_lname,au_fname from authors inner join titleauthor  
on authors.au_id=titleauthor.au_id where state='CA'and royaltyper<30 and au_ord=2

--使用NOT IN的子查询,注:使用NOT IN子查询不能转换为一个联接查询
use pubs
go
select pub_name from publishers  
where pub_id not in(select pub_id from titles where type='business')

--update中的子查询
use pubs
go
update titles set price=price*2  
where pub_id in (select pub_id from publishers where pub_name='new moon books')

--上面的update子查询同下使用联接的update
use pubs
go
update titles set price=price*2 from titles inner join publishers
on titles.pub_id=publishers.pub_id and pub_name='new moon books'

--使用delete子查询
use pubs
go
delete sales  where title_id in (select title_id from titles
where type='business')
go
select *from sales

--同上面的delete子查询,使用联接查询
delete sales from sales inner join titles on sales.title_id=titles.title_id
and type='business'
go
select *from sales

--使用比较运算符的子查询.
use pubs
go  
select au_lname,au_fname  from authors where city =(select city
from publishers where pub_name='Algodata Infosystems')
--上面的子查询可用相等的联接查询
use pubs  
go
select au_lname,au_fname from authors inner join publishers
on authors.city=publishers.city and pub_name='Algodata Infosystems'

--查找所有价格高于当前最低价,distinct从结果集中删除重复的行
use pubs
go
select distinct title,price from titles where price>(select min(price)from titles)
order by price
--
use pubs
go
select distinct title,price from titles where price<(select min(price)
from titles group by type having type='trad_cook')  
order by price
select type,price from titles

--使用any,some,all或修改的比较运算符进行子查询
use pubs
--选择1:使用max函数
go
select title from titles  where advance>all
(select max(advance)from publishers inner join titles  
on titles.pub_id=publishers.pub_id where pub_name='Algodata Infosystems')

--选择2:使用group by和having函数
use pubs
go
select title from titles group by title having max(advance)>
(select max(advance) from publishers inner join titles  
on titles.pub_id=publishers.pub_id where pub_name='Algodata Infosystems')

--any大于最小的
use pubs
go
select title from titles where advance>any(select advance
from publishers inner join titles  
on titles.pub_id=publishers.pub_id  
and pub_name='Algodata Infosystems')

--
use pubs
go
select au_lname,au_fname from authors where city in(
select city from publishers)
--or in等同于=any
use pubs
go
select au_lname,au_fname from authors where city =any(
select city from publishers)

--
use pubs
go
select au_lname,au_fname,city from authors where city<>any (
select city from publishers)
--not in等同于<>all
use pubs
go  
select au_lname,au_fname,city from authors where city not in(
select city from publishers)
--使用<>all
use pubs
go
select au_lname,au_fname,city from authors where city <>all(
select city from publishers)

--使用esists子查询exists
use pubs
go
select pub_name from publishers where exists(
select *from titles where pub_id=publishers.pub_id and type='business')
select pub_id,type  from titles

--所有使用in,any,all的比较运算符查询都可以通过exists表示
use pubs
go
select au_lname,au_fname from authors where city=any(
select city from publishers)
--or
use pubs
go
select au_lname,au_fname from authors where exists(
select * from publishers where authors.city=publishers.city)

--查找以B开头的城市中的任一出版的书名
use pubs
go
select title from titles where pub_id in(
select pub_id from publishers where city like 'B%')
--or
use pubs
go
select title from titles where  exists(
select *from publishers where titles.pub_id=publishers.pub_id and city like 'B%')

--使用not exists子查询
use pubs
go
select pub_name from publishers where not exists(
select *from titles where pub_id=publishers.pub_id and type='business')

--
use pubs
go
select title from titles where not exists(
select title_id from sales where title_id=titles.title_id)

--使用exists和not exists查找交集与差集
use pubs--查找两个表中相同的列
go
select distinct city from authors where exists(
select *from publishers where authors.city=publishers.city)
--or上面查询相等于下面的联接查询
use pubs
go
select distinct authors.city from authors inner join publishers
on authors.city=publishers.city

--差集查询
use pubs
go
select distinct city from authors where not exists(
select * from publishers  where authors.city=publishers.city)
--相等于
use pubs  
go
select distinct city from authors where city not in(
select city from publishers)

--用于替代表达式的子查询
use pubs
go
select title,price,(select avg(price)from titles) as average,price-(
select avg(price)from titles)as diference  from titles where type='popular_comp'

--联合:用于合并多个数据表的行
--union
use  pubs
go
select title_id from titles
union --(all)如果有all表示不会删除相同的行
select title_id  from sales

--distinct从结果集中删除相同的行
use pubs
go
select distinct type from titles where pub_id=1389

--compute汇总总值
select stor_id,qty  from sales compute  max(qty),sum(qty),avg(qty)

--使用select into创建一个表,把指定的表复制
use pubs
go
select *into ybh from publishers  --复制表里面所有的数据
go
select *from ybh
  
go
select *into yyy from publishers where 1>2  --如果条件为假则复制表的结构
go
select *from yyy
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
create table Table1(a int ,b nchar(4))
go
select *into Table2 from Table1  
insert into Table2 values(4,'BBB')
insert into Table2 values(5,'EEE')
insert into Table2 values(2,'DDD')
insert into Table1 values(1,'BBB')

-------union

select distinct a,b from Table1
union  
select * from Table2
  

select  au_ord,royaltyper  as '总和' from titleauthor order by au_ord compute  
sum(royaltyper)by au_ord  
go
select *from titleauthor

-------子查询
select productname from products where productid in(
select productid from [order details])

--exists,not exists
use Northwind
go
select productname from products where   exists(
select productid from [order details] where categoryid=1)
go
select productname from products where not exists(
select productid from [order details] where categoryid=1)

--
use Northwind
go
select orderid ,productid ,unitprice from [order details]
where productid in(select productid from products where supplierid=(
select supplierid from suppliers where city='London'))
--p179练习
--1
use pubs
go
select title_id from titles
union
select title_id from titleauthor
go
select au_id from titleauthor
go
select au_id from authors
--2
(select title from titles where type='business')
--3
select distinct type from titles
--4
select  (royaltyper) as '版权费'from titleauthor where au_id =any
(select au_id  from authors ) compute sum(royaltyper)
go  
select *from  authors
go
select *from titleauthor
go
select *from titles
--5
select *from  authors
go
select *from sales
go
select *from titleauthor
------------? 返回的数据这么多呢?
select qty,au_fname  from sales,authors  
where sales.title_id in(select title_id from titleauthor  
where titleauthor.au_id in(select au_id from authors  
where authors.au_fname like '%' ))  

select distinct  au_lname from authors where au_id in(
select au_id from titleauthor where title_id in(select title_id from sales))  

select * from authors  
delete  authors where au_lname='White'

select *from sales
insert into sales values(8888,'fds12','1999-11-12 00:00:00.000',65,'net 32','ps999')
select getdate()
select datename(hh,getdate() )+':'+datename(mi,getdate() )+':'
+ datename(ss,getdate() )as '当前时间'
--6
select  avg(qty),title_id from sales group by title_id compute avg(qty)  
go
select *from titles
go
select *from sales
----作业
--1
use pubs
go
select *from sales
select *from  authors
go
select *from sales
go
select *from titleauthor
  

select sum(ord_num) from sales  
--2