你的位置: Kiyo'Space首页 数据库 阅读文章 欢迎留下您的足迹

子查询收集

[ 数据库 ] 分享

怕忘了,备用

1.Update 子查询

SQL:

update   a   set   a.a=b.a,a.b=b.b  from   [xxx] as a   inner   join       [yyy] as   b   on   a.id=b.id  and   a.sortid=b.sortid

 

Access:

update [xxx] as a inner join [yyy] ason a.id=b.id SET a.a=b.a,a.b=b.b wher a.id=3

 

 

where后的列名要与子查询的列名要匹配
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’))–子查询.

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

没有评论

  • (Required)
  • (Required, will not be published)