sql多表查询代码示例
SQL多表查询代码示例
在Pubs数据库中，完成以下查询
use pubs
--使用内联接查询出authors和publishers表中位于同一个城市的作者和出版社信息select au_fname+'·'+au_lname as 作者,pub_name as 出版社
from authors inner join publishers
on =
--查询出作者号以1~5开头的所有作者，并使用右外联接在查询的结果集中列出和作者--在同一个城市的出版社名
select au_fname+'·'+au_lname as 作者,pub_name as 出版社
from authors right outer join publishers
on like '[1-5]%'
where =
--使用自联接查找居住在 Oakland 相同邮码区域中的作者。
select distinct +'·'+ as 作者,,
from authors a inner join authors b
on =
where ='Oakland' and <>
--P26《学习手册》上机试验的所有题目
select ascii('sql')
--结果：115
select char(66)
--结果：B
select charindex('E','HELLO')
--结果：2
select left('RICHARD',4)
--结果：RICH
select len('RICHARD')
--结果：7
select lower('RICHARD')
--结果：richard
select 'SQL'+ltrim('RICHARD')
--结果：SQLRICHARD
select reverse('ACTION')
--结果：NOITCA
select right('RICHARD',4)
--结果：HARD
select rtrim('RICHARD ')+'SQL'
--结果：RICHARDSQL
select patindex('%BOX%','ACTIONBOX')
--结果：7
select 'RICHARD'+space(2)+'HELL'
--结果：RICHARD HELL
select stuff('Weather',2,2,'I')
--结果：WIther
select substring('Weather',2,2)
--结果：ea
select upper('Richard')
--结果：RICHARD
select dateadd(dd,10,getdate())
--结果：2005-10-26 16:04:
select datediff(dy,getdate(),'2005-01-01')
--结果：-288
select datepart(dw,'2004-10-01')
--结果：6
select datename(dw,'2004-10-01')
--结果：星期五
--第七讲多表查询上机实验
use recruitment
--- 需要得到年龄在35岁到40岁之间的外部候选人的信息
select * from ExternalCandidate
where datediff(yy,dbirthdate,getdate()) between 35 and 40
--- 需要在当前日期之后的10天在报纸上登载一则广告，系统需要计算出日期并显示
select distinct getdate() as today,dateadd(day,10,getdate()) as '10 days from today' from newsad
--- 统计外部候选人接受测试和面试日期的间隔的时间平均值
select avg(datediff(day,dtestdate,dinterviewdate)) as 测试面试日期间隔平均天数
from externalcandidate
--- 需要获取外部候选人的姓名和他们申请的职位
select as 姓名,
as 申请职位
from externalcandidate left join position
on =
--- 需要获得在2001年应聘的外部候选人的名字，及推荐他们的招聘机构名
select as 名字,
as 推荐他们的招聘机构名
from externalcandidate left join recruitmentagencies
on =
where year=2001
--- 需要获取外部候选人的姓名以及他们的参照的招聘的广告所属的报纸名
select as 姓名,
as 参照招聘广告所属报纸
from externalcandidate,newsad,newspaper
where = and
=
--- 需要获取大学名称、报纸名称以及它们地址的列表
select as 大学名称, 学校地址,
as 报纸名称, as 报社地址
from college,newspaper
--问题：这两张表之间没有联系，那么应选用何种联接否则这里面有太多冗余数据
-- 是否为同一所城市里有哪些大学和哪些报纸
select as 大学名称, 学校地址,
as 报纸名称, as 报社地址
from college,newspaper
where =
--因为大学所在城市的值为某某，而报纸所在城市的值为某某市，因此按此不能正确查出结果--采用以下办法可以解决
select as 大学名称, 学校地址,
as 报纸名称, as 报社地址
from college,newspaper
where left(ltrim,2)=left(ltrim,2)
--还是显示出大学表里符合条件的记录与报纸表里符合条件的记录之积，内联接结果一样
--第七讲多表查询作业
--P26《学习手册》上机作业的所有题目
use GlobalToyz
--按指定格式（详见学习手册P27）显示所有运货的报表（天数=实际到达日期-运货日期）select corderno as 定单号, dshipmentdate as 运货日期,
dactualdeliverydate as 实际到达日期,
datediff(day,dshipmentdate,dactualdeliverydate) as 运送天数
from shipment
--小结：两日期之差运算为第二个日期参数-第一个日期参数
--按指定格式（详见学习手册P27）显示所有的订单
select cOrderNo as 定单号,cShopperId as 购物者号,dOrderDate as '订单日期（号）', datename(dw,dorderdate)星期几
from orders
--小结：求星期几，日期元素只能用DW，而不能用WK，WK求得是在一年中的第几周，而列别名如果有
-- 特殊字符需要引号引起来
--显示所有玩具名和所属的种类名
select as 玩具名, as 种类名
from category join toys
on =
--小结：交叉联接不能使用条件，而内联接和右外联接在此效果相同，
-- 左外联接和全外联接效果相同，但多出九条玩具名为空的记录，
-- 因为左外联接时将显示所有左表中即种类表中的记录，即使没有该玩具属于该种类, -- 此时玩具名为NULL值
-- JOIN前不加关键字时默认为内联接
-- 用join联接表名时，后面条件语句只能先跟on关键字，不能直接用where
--按指定格式（详见学习手册P27）显示所有玩具的名称、商标和种类
select as 玩具名, as 商标名,
as 类别名
from toys,ToyBrand,Category
where = and =
--问题：如果用逗号联系多张表，之间采用的是什么联接方式表与表之间的前后顺序影不影响结果
--按指定格式（详见学习手册P28）显示所有玩具的订货号、玩具ID和玩具使用的礼品包装说明
select as 定单号, as 玩具号,
as 包装信息
from orderdetail left join wrapper
on =
select as 定单号, as 玩具号,
as 包装信息
from toys,orderdetail,wrapper
where = and =
--小结：外连接的关键字outer可以省略不写
--问题：采用以上方式查出的结果好象未能满足需求，没有显示所有的玩具，如果用三张表，即-- 加入toys表后，加上一个=后也不能列出所有玩具。
--按指定格式（详见学习手册P28）显示所有购物者名，及他们所购买的订单信息（无论购物者是否有订单）
select as 购物者名, as 定单号,
as 定单时间, as 定单金额
from shopper left join orders
on =
--按指定格式（详见学习手册P28）显示订单号码、订单日期和每个订单所在的季节
select cOrderNo as 定单号,dOrderDate as 定单日期,datename(qq,dOrderDate) as 季节from orders
--问题：如果要显示季节，是否需要用到分支选择语句
--按指定格式（详见学习手册P28）显示所有购物者ID、名字、电话和相应订单的接受者select as 购物者号, as 名字,
as 电话, as 接受者名, as 电话
from shopper,orders,recipient
where = and =
--小结：如果表与表之间联接没用JOIN，则条件语句关键字不能用ON，只能用WHERE
--按指定格式（详见学习手册P28）显示所有购物者和接受者的名字、地址
select as 购物者名字, as 购物者地址,
as 接受者名字, as 接受者地址
from shopper,orders,recipient
where = and =
--显示所有玩具名及该玩具的销售数量
select as 玩具名, as 销售数量
from toys left join orderdetail
on =
--显示在2001年5月消费金额最高的前3名购物者名及消费金额
select top 3 as 购物者名,sum as 消费金额
from shopper left join orders
on year=2001 and month=5
and =
group by ,
order by sum desc