SQL2009.4.8

//author 满晨晨
//time 2009 4 8 上午

关联 join 两个表的关联  inner join

内联  符合条件的显示出来

--查找‘NEW NOON BOOKS’出版社的图书名称，价格，每本图书出版的出版日期
select title ,price,pubdate from title,publishers
where pub_name='NEW MOON BOOKS' and publishers.pub_id=titles.pub_id

或者select title ,price,pubdate from titles join publishers
on publishers.pub_id=titles.pub_id
where pub_name='NEW MOON BOOKS'

任何一个语句先执行的是where 是对原始表格的内容进行查询

一般为了避免歧义
select titles.title ,titles.price,titles.pubdate from titles join publishers
on publishers.pub_id =titles.pub_id
where publishers.pub_name='NEW MOON BOOKS'

select t.title,t.price,t.pubdate from titles t join publishers p
on p.pub_id=t.pub_id
where p.pub_name='NEW MOON BOOKS'
ORDER BY t.pubdate desc

--查找'Sushi,Anyone?'图书的作者姓名和电话  查询3个表的情况
select authors.au_lname+''+authors.au_fname,authors.phone from authors,titles,titleauthor
where authors.au_id=titleauthor.au_id
and titles.title_id=titleauthor.title_id
and titles.title='Sushi,Anyone?'

或者
select a.au_lname+''+a.au_fname,a.phone
from authors ajoin titleauthor ta
on a.au_id=ta.au_id
join titles t
on ta.title_id =t.title_id
where t.title='Sushi , Anyone?'

'' 表示空格

--查找'New Moon Books'出版社从事Editor和Designer工作的人，他们的九折工资是多少
--（要求：最后显示结果集包括，姓名，工种名称，九折后的工资）
select e.lname+'  '+e.fname as name,j.job_desc,e.job_lvl*0.9 as money
from employee e join jobs j
on e.job_id=j.job_id
join publishers p
on p.pub_id=e.pub_id
where p.pub_name='New Moon Books'
and j.job_desc='Editor'or j.job_desc='Designer'错误的 or优先 所以用括号and （j.job_desc='Editor'or j.job_desc='Designer'）
and j.job_desc in ('Editor','Designer')
--查找与出版社在同一个城市的作者///姓名，出版社名称，城市名称
select a.au_lname+'  '+a.au_fname as name ,p.pub_name ,a.city
from authors a join publishers p
on a.city=p.city

外联 outer join 符合条件的显示出来 此外还要显示出其他条件的值
left join 左边的表为核心表都要显示出来  右边的那个表符合条件的显示出来 其他的NULL  
right join
full join 左右为核心一定要显示出来
--查找所有作者的姓名，电话 ，如果存在和该作者在同一个城市的出版社的话，列出出版社名称和城市名称
select a.au_lname,a.au_fname,p.pub_name,p.city
from authors a left join publishers p
on a.city =p.city

select a.au_lname,a.au_fname,p.pub_name,p.city
from authors a right join publishers p
on a.city =p.city

select a.au_lname,a.au_fname,p.pub_name,p.city
from authors a full join publishers p
on a.city =p.city

 

自联
  Create  Table  tabEmployee
(
 eId int primary key,
 eName varchar(40),
 eAddress varchar(300),
 eLeader int
)
go
Insert into tabEmployee
Values(4,'周扒皮','中国#####',3)

--查找周扒皮的领导的姓名

 

select leader.eName
from tabEmployee emp join tabEmployee leader
on emp.eLeader=leader.eId
where emp.eName ='周八皮'

select leader.eName //查找领导的名字
from tabEmployee leader join//从领导的表中
tabEmployee employee//自联上自己为雇员表
on leader.eId=employee.eLeader//领导的ID=雇员的领导ID
where employee.eName='周扒皮'