(八)：连接查询

需要同时从两个或两个以上的表中检索数据
--内连接
/*
在内连接中使用inner join 连接运算符，并使用on关键字指定连接的条件
如果join关键字前面没有明确指定是那种类型，则默认为内连接
*/
/*
内连接语法：
select select_list
from 表1 inner join 表2
on 连接条件

或

select select_list
from 表1，表2
where 连接条件

注意：连接条件的格式为：
[表1名].<列名1><比较运算符>[表2].<列2>
*/
--从学生表和选课表中查出每个学生的姓名、课程号、成绩
select * from Student
select * from SC 
--格式一：
select Sname as 姓名,Cno as 课程号,Grade as 成绩
from Student ,SC 
where Student.Sno = SC.Sno
--格式二：
select Sname as 姓名,Cno as 课程号,Grade as 成绩
from Student inner join SC 
on Student.Sno = SC.Sno


--从学生表、选课表、课程表三个表中查出每个学生的姓名、课程名、成绩
--
select * from Course
--
select Sname as 姓名,Cname as 课程名,Grade as 成绩
from Student ,SC ,Course
where Student.Sno = SC.Sno and SC.Cno = Course.Cno


--从学生表、选课表、课程表三个表中查出每个19岁学生的姓名、课程名、成绩、总成绩、平均成绩
select Sname as 姓名,Cname as 课程名,Grade as 成绩
from Student ,SC ,Course
where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Student.Sage=19
order by Sname

compute sum(Grade) ,avg(grade) by Sname 

--
--自连接（特殊的内连接）
/*
自连接：操作在同一张表上，将一张表上的不同行连接起来
同一张表上需要进行多次查询，且需要将多次查询的结果进行比较处理后得出最后的目标记录数据
或者同一张表上需要进行多次查询，但只需要显示其中的一个查询结果时，需要用到自连接构造副本
以示区别
*/
--
--如果只需要在一张表上进行多次查询，并将多次查询的结果整合成一张表时，直接用逻辑关系词or就可以
select * from SC
--查询成绩大于90分所有学生和学号为003的学生的学号、课程号、分数，并将记录按照成绩的降序排列
select Cno as 课程号,Sno as 学号,Grade as 成绩
from SC
where (Grade>=90 ) or Sno = '003'
order by Grade desc

--查询所有选修了C++课程且成绩大于李四的所有学生的姓名、学号、成绩
--
select * from Student
select * from SC 
select * from Course
--先插入一组数据
insert into SC (Cno,Sno,Grade)
values ('111','005',89)
--
--在进行自连接之前需要先构造一张表的两个副本以示区别
select m.Sname ,b.Sno ,b.Grade 
from Student n ,Student m,Course,SC a,SC b
where n.Sname = '李四' and a.Sno = n.Sno 
and b.Grade >a.Grade and b.Cno = Course.Cno and Course.Cname = 'C++'
and m.Sno = b.Sno
--
/*
内连接、自连接都可以运用where格式连接两个以上的表
但是外连接则只能用于连接两个不同的表，不能用于连接多个表
---------------------
但是：连接是可以嵌套的，用此特性可以解决多个表的连接问题
---------------------
*/

--外连接
/*
在外连接中不仅包含那些满足条件的数据而且默写表中不满足条件的数据也会显示在结果集
外连接只能用在两个不同的表中
*/
/*
外连接的分类：
1: left outer join  : 左外连接是对连接条件中的左边的表不加限制
2: right outer join  : 右外连接是对连接条件中的右边的表不加限制
3: full outer join  : 全外连接对两个表都不加限制，所有两个表的行都会显示
*/
/*
    外连接语法：
    
    1：左外连接
    select select_list 
    from 表1 left [outer] join 表2
    on 表1.列1 = 表2.列2


2：右外连接
select select_list 
    from 表1 right [outer] join 表2
    on 表1.列1 = 表2.列2
    
    3: 全外连接
    select select_list 
    from 表1 full [outer] join 表2
    on 表1.列1 = 表2.列2
*/
--外连接举例
select * from Student
select * from SC 
select * from Course

--左外连接
--查询每个学生及其选修课程的成绩情况（包含未选课的学生）
 select Student.*,Cno,Grade 
 from Student left outer join SC 
 on  Student.Sno = SC.Sno
 
 --右外连接
 --查询每门选修课的基本信息及选修该门课的学生成绩、学号、学生姓名(包含没有学生学修的课程信息)
 select Course.*,SC.Sno,Sname,Grade
 from SC right join Course 
 on Course.Cno = SC.Cno 
 left join Student
 on Student.Sno = SC.Sno
 /*
  连接特性说明：
  使用 inner / right / left join 格式的连接都只能连接两个表，但是有其他方法可以连接多个表
  方法一：对于内连接和自连接，可以使用from where的格式解决连接多个表
  方法二：对于外连接和join格式连接，可以使用连接嵌套来解决该问题，即：在一个连接的on条件表达式后面再添加 join ..on .语句
 */
 --
 select Course.*,SC.Sno,Grade
 from SC right join Course 
 on Course.Cno = SC.Cno 
 
 
 --全外连接
 --
--全外连接
 /*
全外连接一般是连接三个表，显示最左边个最右边的表的全部记录信息
且这种情况是不能用left join 和right join套用代替的
 */
 --在Student库中查询每个学生及其选修课程的成绩情况（包含未选课的学生信息及为被选修的课程信息）
 
 use Student
 go 
 select Course.* ,SC.Grade,Student.Sname,Student.Sno
 from Course full join SC 
 on Course.Cno = SC.Cno
 full join  Student
 on Student.Sno = SC.Sno

//
--查询成绩在75分以上的所有学生的学号、姓名、选修课的课程号、课程名、成绩
 
 --写法一：
 select Student.Sno , Sname , SC.Cno ,Cname ,Grade 
 from SC ,Student ,Course
 where SC.Grade >= 90 and Student.Sno = SC.Sno and Course.Cno = SC.Cno
 select * from SC 
 
 --写法二：
 select A.Sno ,A.Sname,B.Cno,C.Cname,B.Grade
 from Student as A join SC B
 on B.Cno >=90 and A.Sno = B.Sno
 join Course C
 on C.Cno = B.Cno

//
--交叉连接
 --cross join 关键字的使用
 /*
交叉连接也就是笛卡尔乘积
在检索的结果中包含了所有连接的两个表中的所有行的全部组合
 */

 --查询所有学生可能选课的情况
select Student.* , Cno ,Cname
from Student cross join Course