数据库 联接查询 SQL

-- 联接查询
-- 查询所有选择了教师的学生信息以及对应的老师信息
-- 
-- 1)内联接  两张表都有的记录才查询出来 
-- 简化写法
select * from student,teacher  where  student.tid=teacher.tid
-- 标准写法
select * from student inner join teacher on student.tid=teacher.tid


-- 联接成一张表后  可以在此基础上做查询 
select sname,tname from student inner join teacher on student.tid=teacher.tid
where sid=8 

-- 也可以直接用and
select sname,tname from student inner join teacher on student.tid=teacher.tid
and sid=8 

-- 查询所有的学生信息以及对应的老师信息
-- 2) 左外联接   左边这张表的数据全部查询出来 右边只显示与之能关联的数据
select * from student left outer  join teacher 
on student.tid=teacher.tid

select * from teacher

-- 3)右外  右边的表为主表  
select * from student right outer  join teacher 
on student.tid=teacher.tid

select * from teacher left outer  join  student
on student.tid=teacher.tid 


select * from teacher left outer  join  student
on student.tid=teacher.tid  where teacher.tid=101 and sex='男'