SQL高级查询

三表内连接
    select s.sname,t.tname,b.subjectName from student s 
   
    inner join teacher t on(s.tid = t.tid)
   
    inner join subject b on(b.subjectNo = t.subjectNo);

in查询
select * from  students where sid in(select sid from result  where subjectNo=
(selcet form subject where subjectName ='HTML') 
and studentResult>60);

    
高级查询
1.连接查询
select * from Info,Nation --形成笛卡尔积
select * from Info,Nation where Info.Nation = Nation.Code
select Info.Code,Info.Name,Sex,Nation.Name,Birthday from Info,Nation where Info.Nation = Nation.Code
select * from Info join Nation on Info.Nation = Nation.Code --join on 的形式
2.联合查询
select Code,Name from Info
union
select Code,Name from Nation
3.子查询
一条SQL语句中包含两个查询，其中一个是父查询（外层查询）,另一个是子查询（里层查询）,子查询查询的结果作为父查询的条件。
--查询民族为汉族的所有人员信息
select * from Info where Nation = (select Code from Nation where Name = '汉族')

（1）无关子查询
子查询可以单独执行，子查询和父查询没有一定的关系
--查询系列是宝马5系的所有汽车信息
select * from Car where Brand =(select Brand_Code from Brand where Brand_Name = '宝马5系')

（2）相关子查询
--查找油耗低于该系列平均油耗的汽车
select * from Car where Oil<(该系列的平均油耗)
select avg(Oil) from Car where Brand = (该系列)

select * from Car a where Oil<(select avg(Oil) from Car b where b.Brand = a.Brand)

转载于:https://www.cnblogs.com/ShenG1/p/5740318.html