子查询收集
怕忘了,备用
1.Update 子查询
SQL:
Access:
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
版权声明:
作者:Kiyo
链接:https://www.wkiyo.cn/html/2008-05/i568.html
来源:Kiyo's space
文章版权归作者所有,未经允许请勿转载。
共有 0 条评论