use TestDB

--select <列名> from 表名
--[where 条件表达式]--进行数据源的过滤
--[group by 分组字段]--将结果集进行分组,与聚合函数一起实现分组统计查询。
--[having 包含聚合函数的条件表达式] --对分组后的结果进行统计
--order by 排序字段 [desc|asc] --按照某个字段将结果集进行排序

use TestDB;
go
----------------------------------
--连接查询
--1.内连接:inner join 
--2.外部连接:left/right/full outer join
--3.交叉连接 :cross join
----------------------------------
-----------------------------------------------------
--简单的条件连接查询：在两表查询语句后面加上where条件语句，实现两表的连接查询，其条件为两表的关联字段。
-----------------------------------------------------
--查询学生的学号，姓名，所属班级编号和班级名称信息
select StuId,StuName,StuInfo.ClassId,ClassName from StuInfo,ClassInfo where StuInfo.ClassId=StuInfo.ClassId


--1.内连接：内连接仅仅返回存在字段匹配的记录,即符合连接条件的数据，无法满足的数据被过滤。
--------------------------------------------------------
--查询学生的班级信息：班级编号和班级名称，学号，姓名
select ClassInfo.ClassId,ClassName,StuId,StuName from ClassInfo
inner join StuInfo on StuInfo.ClassId=ClassInfo.ClassId
--查询学生的成绩信息：学号，姓名，课程编号，成绩
select StuInfo.StuId ,stuname,classid,Score from StuInfo
inner join Scores on Scores.StuId=StuInfo.StuId
--查询学生选修的课程信息：学号，姓名，选修课程名称
select distinct StuInfo.StuId 学号,StuName 姓名,CourseName 选修的课程名称  from StuInfo
inner join Scores on Scores.StuId=StuInfo.StuId
inner join CourseInfo on Scores.CourseId=CourseInfo.CourseId


--查询学生的成绩信息：学号，姓名，课程名称，成绩
select StuInfo.StuId,stuname,ClassInfo.ClassName,Score from Scores
inner join StuInfo on StuInfo.StuId=Scores.StuId
inner join ClassInfo on ClassInfo.ClassId=Scores.CourseId
--查询选修了‘计算机基础’的学生信息：学号，姓名，课程名称，成绩
select distinct StuInfo.StuId 学号,StuName 姓名,CourseName 选修的课程名称  from StuInfo
inner join Scores on Scores.StuId=StuInfo.StuId
inner join CourseInfo on Scores.CourseId=CourseInfo.CourseId


---------------------------------------------------
--2.外联接：外部连接参与连接的表有主从之分，以主表的每行记录去匹配从表的记录，
--			符合连接条件的数据将直接返回结果集中，不符合连接条件的列将被填充NULL值后再返回结果集中。
---------------------------------------------------
--查询所有班级的学生信息：班级编号和班级名称，学号，姓名（有些班级可能没有学生）

--查询所有班级的学生人数：班级名称，人数(没有学生的班级人数显示为0)

--查询所有班级的男女生人数：班级名称，性别，人数(没有学生的班级人数显示为0)

--查询所有学生的成绩信息：学号，姓名，课程编号，成绩（有些学生没有成绩）

--查询所有学生的学号、姓名、选课总数、所有课程的总成绩,并按照总成绩的降序排列(没成绩的显示为 null )

--查询所有课程的课程编号、课程名称、选修的学生数量，并按照学生数量的降序排列（没有成绩信息的课程，学生数为0）

---------------------------------------
--3.交叉连接：将一张表的每条记录与另一张表的所有记录进行两两连接，
--			  结果集的记录数为两表记录数的乘积,也叫笛卡尔积
-----------------------------------------
--将班级信息表与学生表进行交叉连接(用cross join )



-----------------------------------------------------------------------------------------
--4.自连接查询：如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。
----------------------------------------------------------------------------------------
----查询课程编号"1 "比课程编号" 2 "成绩高的学生的信息及课程成绩：学号，姓名，课程1,课程1的成绩，课程2，课程2的成绩

select * from Scores a where a.CourseId=1 and Score>(select Score from Scores b where a.StuId=b.StuId and CourseId=2) 
select * from Scores