sql 多表查询 代码示例

1、SQL多表查询代码示例在Pubs数据库中，完成以下查询use pubs-使用内联接查询出authors和publishers表中位于同一个城市的作者和出版社信息select au_fname+au_lname as 作者,pub_name as 出版社from authors inner join publisherson authors.city=publishers.city-查询出作者号以15开头的所有作者，并使用右外联接在查询的结果集中列出和作者-在同一个城市的出版社名select au_fname+au_lname as 作者,pub_name as 出版社from authors

2、right outer join publisherson authors.au_id like 1-5% where authors.city=publishers.city-使用自联接查找居住在 Oakland 相同邮码区域中的作者。select distinct a.au_fname+a.au_lname as 作者,a.au_id,a.zipfrom authors a inner join authors bon a.zip = b.zipwhere a.city=Oakland and a.au_fname b.au_fname-P26学习手册上机试验的所有题目select asc

3、ii(sql)-结果：115select char(66)-结果：Bselect charindex(E,HELLO)-结果：2select left(RICHARD,4)-结果：RICHselect len(RICHARD)-结果：7select lower(RICHARD)-结果：richardselect SQL+ltrim(RICHARD)-结果：SQLRICHARDselect reverse(ACTION)-结果：NOITCAselect right(RICHARD,4)-结果：HARDselect rtrim(RICHARD )+SQL-结果：RICHARDSQLselect p

4、atindex(%BOX%,ACTIONBOX)-结果：7select RICHARD+space(2)+HELL-结果：RICHARD HELLselect stuff(Weather,2,2,I)-结果：WItherselect substring(Weather,2,2)-结果：easelect upper(Richard)-结果：RICHARDselect dateadd(dd,10,getdate()-结果：2005-10-26 16:04:58.030select datediff(dy,getdate(),2005-01-01)-结果：-288select datepart(dw

5、,2004-10-01)-结果：6select datename(dw,2004-10-01)-结果：星期五-第七讲 多表查询上机实验use recruitment- 需要得到年龄在35岁到40岁之间的外部候选人的信息select * from ExternalCandidatewhere datediff(yy,dbirthdate,getdate() between 35 and 40- 需要在当前日期之后的10天在报纸上登载一则广告，系统需要计算出日期并显示select distinct getdate() as today,dateadd(day,10,getdate() as 10

6、days from todayfrom newsad- 统计外部候选人接受测试和面试日期的间隔的时间平均值select avg(datediff(day,dtestdate,dinterviewdate) as 测试面试日期间隔平均天数from externalcandidate- 需要获取外部候选人的姓名和他们申请的职位select externalcandidate.vcandidatename as 姓名, position.vdescription as 申请职位from externalcandidate left join positionon externalcandidate.

7、cpositioncode=position.cpositioncode- 需要获得在2001年应聘的外部候选人的名字，及推荐他们的招聘机构名select externalcandidate.vcandidatename as 名字, ame as 推荐他们的招聘机构名from externalcandidate left join recruitmentagencieson externalcandidate.cagencycode=recruitmentagencies.cagencycodewhere year(externalcandidat

8、e.ddateofapplication)=2001- 需要获取外部候选人的姓名以及他们的参照的招聘的广告所属的报纸名select externalcandidate.vcandidatename as 姓名, ewspapername as 参照招聘广告所属报纸from externalcandidate,newsad,newspaperwhere ewsadno=ewsadno and ewspapercode=ewspapercode- 需要获取大学名称、报纸名称以

9、及它们地址的列表select college.cCollegeName as 大学名称,college.vcollegeaddress 学校地址, ewspapername as 报纸名称,newspaper.vhoaddress as 报社地址from college,newspaper-问题：这两张表之间没有联系，那么应选用何种联接？否则这里面有太多冗余数据- 是否为同一所城市里有哪些大学和哪些报纸？select college.cCollegeName as 大学名称,college.vcollegeaddress 学校地址, ewspap

10、ername as 报纸名称,newspaper.vhoaddress as 报社地址from college,newspaperwhere college.ccity=newspaper.ccity-因为大学所在城市的值为某某，而报纸所在城市的值为某某市，因此按此不能正确查出结果-采用以下办法可以解决select college.cCollegeName as 大学名称,college.vcollegeaddress 学校地址, ewspapername as 报纸名称,newspaper.vhoaddress as 报社地址from college,newspape

11、rwhere left(ltrim(college.ccity),2)=left(ltrim(newspaper.ccity),2)-还是显示出大学表里符合条件的记录与报纸表里符合条件的记录之积，内联接结果一样-第七讲 多表查询作业-P26学习手册上机作业的所有题目use GlobalToyz-按指定格式（详见学习手册P27）显示所有运货的报表（天数=实际到达日期-运货日期）select corderno as 定单号, dshipmentdate as 运货日期, dactualdeliverydate as 实际到达日期, datediff(day,dshipmentdate,dactua

12、ldeliverydate) as 运送天数from shipment-小结：两日期之差运算为第二个日期参数-第一个日期参数-按指定格式（详见学习手册P27）显示所有的订单select cOrderNo as 定单号,cShopperId as 购物者号,dOrderDate as 订单日期（号）, datename(dw,dorderdate)星期几from orders-小结：求星期几，日期元素只能用DW，而不能用WK，WK求得是在一年中的第几周，而列别名如果有- 特殊字符需要引号引起来-显示所有玩具名和所属的种类名select toys.vToyName as 玩具名,Category.

13、cCategory as 种类名from category join toyson toys.cCategoryId = Category.cCategoryId-小结：交叉联接不能使用条件，而内联接和右外联接在此效果相同，- 左外联接和全外联接效果相同，但多出九条玩具名为空的记录，- 因为左外联接时将显示所有左表中即种类表中的记录，即使没有该玩具属于该种类,- 此时玩具名为NULL值- JOIN前不加关键字时默认为内联接- 用join联接表名时，后面条件语句只能先跟on关键字，不能直接用where-按指定格式（详见学习手册P27）显示所有玩具的名称、商标和种类select toys.vtoy

14、name as 玩具名,ToyBrand.cBrandName as 商标名, Category.ccategory as 类别名from toys,ToyBrand,Categorywhere toys.cBrandId=ToyBrand.cBrandId and toys.cCategoryId=Category.cCategoryId-问题：如果用逗号联系多张表，之间采用的是什么联接方式？表与表之间的前后顺序影不影响结果？-按指定格式（详见学习手册P28）显示所有玩具的订货号、玩具ID和玩具使用的礼品包装说明select orderdetail.corderno as 定单号,order

15、detail.ctoyid as 玩具号, wrapper.vdescription as 包装信息from orderdetail left join wrapperon orderdetail.cwrapperid=wrapper.cwrapperidselect orderdetail.corderno as 定单号,orderdetail.ctoyid as 玩具号, wrapper.vdescription as 包装信息from toys,orderdetail,wrapperwhere toys.ctoyid=orderdetail.ctoyid and orderdetail.

16、cwrapperid=wrapper.cwrapperid-小结：外连接的关键字outer可以省略不写-问题：采用以上方式查出的结果好象未能满足需求，没有显示所有的玩具，如果用三张表，即- 加入toys表后，加上一个toys.ctoyid=orderdetail.ctoyid后也不能列出所有玩具。-按指定格式（详见学习手册P28）显示所有购物者名，及他们所购买的订单信息（无论购物者是否有订单）select shopper.vfirstname as 购物者名,orders.corderno as 定单号, orders.dorderdate as 定单时间,orders.mtotalcost

17、as 定单金额from shopper left join orderson shopper.cshopperid=orders.cshopperid-按指定格式（详见学习手册P28）显示订单号码、订单日期和每个订单所在的季节select cOrderNo as 定单号,dOrderDate as 定单日期,datename(qq,dOrderDate) as 季节from orders-问题：如果要显示季节，是否需要用到分支选择语句？-按指定格式（详见学习手册P28）显示所有购物者ID、名字、电话和相应订单的接受者select shopper.cshopperid as 购物者号,shopp

18、er.vfirstname as 名字, shopper.cphone as 电话,recipient.vfirstname as 接受者名,recipient.cphone as 电话from shopper,orders,recipientwhere shopper.cshopperid = orders.cshopperid and orders.corderno = recipient.corderno-小结：如果表与表之间联接没用JOIN，则条件语句关键字不能用ON，只能用WHERE-按指定格式（详见学习手册P28）显示所有购物者和接受者的名字、地址select shopper.vfirstname as 购物者名字,shopper.vaddress as 购物者地址, recipient