5.表的联接查询(多表)

联结：
表的联结查询，需要在sql语句中通过外键关联。如果没有关联则出现笛卡尔积
如果查询多个表中有相同的字段名，则需要通过表限定。
如果表名很长，语句里面出现多次，就给表取别名。

例：查询所有学生的姓名、系名
select sname,dname from t_student,t_dept where t_student.did = t_dept.did;

查询所有男生的姓名、 系编号、系名、系主任名.
select sname,t_student.did,dname,mname from t_student,t_dept,t_man
where t_student.did = t_dept.did and t_dept.mid = t_man.mid
and ssex='男';

 

select sname,t2.did,dname,mname from t_student t1,t_dept t2,t_man t3
where t1.did = t2.did and t2.mid = t3.mid
and ssex='男';

例： 查询1202学生的分数属于哪个等级
select level from t_student,t_level where sid=1202 and
sscore >= low and sscore < hig;
查询每个学生的姓名、分数、分数等级
select sname,sscore,level from t_student,t_level
where sscore >= low and sscore < hig;

查询每个系的平均分数，及这个分数属于的等级.
select did,avg_s,level from t_level,
( select did,avg(sscore) as avg_s from t_student group by did ) t
where avg_s >= low and avg_s<hig;

查询 平均等级以上 人最多的信息

1、每个系的平均分
select did,avg(sscore) as a_s from t_student group by did;
2、每个系的平均分属于的等级
( select did,a_s,level from t_level,
( select did,avg(sscore) as a_s from t_student group by did ) t
where a_s >= low and a_s<hig ) t1;
3、求出每个人的分数和等级
( select sscore,did,level from t_student,t_level where sscore >= low and sscore < hig ) t2;

--------------------------------------------------------

一个比较复杂的例子：

select * from t_dept where did =
(
select did from (
select t2.did, count(*) as max_a from

( select did,a_s,level from t_level,
( select did,avg(sscore) as a_s from t_student group by did ) t
where a_s >= low and a_s<hig ) t1,
( select sscore,did,level from t_student,
t_level where sscore >= low and sscore < hig ) t2

where t2.level > t1.level and t1.did = t2.did
group by t2.did
) t4 where max_a =

(select max(max_a) from
( select t2.did, count(*) as max_a from

( select did,a_s,level from t_level,
( select did,avg(sscore) as a_s from t_student group by did ) t
where a_s >= low and a_s<hig ) t1,
( select sscore,did,level from t_student,
t_level where sscore >= low and sscore < hig ) t2

where t2.level > t1.level and t1.did = t2.did
group by t2.did ) as t3 ));

联接分类:
自然联结
内联结 inner join on t1 inner join t2 on 条件 必须左右两张表都有数据，并且满足条件，才会显示
外联结
（左联结 left outer join on 以左边的表为主，左边的表的数据都会显示，即使不满足条件
右联结 rigth outer join on）以右边的表为主，右边的表的数据都会显示，即使不满足条件

自联结(自己跟自己联结)

例： 使用内联结查询男生的姓名和系名
select sname,dname from t_student,t_dept where t_studen.did = t_dept.did;
select sname,dname from t_student inner join t_dept
on t_student.did = t_dept.did and ssex='男';

select ***** from t1 inner join t2 on *** inner join t3 on **;

select sname,dname from t_student right outer join t_dept
on t_student.did = t_dept.did;


select distinct t1.sscore from t_student t1,t_student t2
where t1.sscore > t2.sscore ;

select * from t_student where sid not in (
select distinct t1.sid from t_student t1,t_student t2
where t1.sscore > t2.sscore );

create table t_t(
id int primary key,
name varchar(20) not null,
age int default 10,
high double
)