连接查询和分组查询

《升序》ASC  《降序》DESCrelationship


*** 分组查询：
** 使用 group by 进行分组查询
语句：《有列名：StudentID，CourseID，Scores》
select CourseID,avg(S cores) as 课程平均成绩 from Score group by CourseID



例 1：查询男女学生的人数各是多少。
select count(*) as 人数，Sex from Student group by Sex


例 2：查询每个年级的总人数。
select count(*) as 年级人数，SGrade from Student group by SGrade


例 3：查询每个科目的平均分，并且按照由高到低的顺序排列显示。
select CourseID，avg(Scores) as 平均分 from Student group by CourseID order by avg(Scores) desc


** 多列分组查询：

例：如果要统计每个学期的男女学生人数，则理论上先把每个学期分开，然后针对每个学期，把男女学生人数各自统计，也就是需要按照两个列进行分组。
《列名：Scode ， Sname ，SAddress ，SGrade ， SEmail ， SSex》
select  count(*) as 人数，SGrade as 年级，SSex as 性别
from Score
group by SGrade，SSex
order by SGrade
注意：列表中可以被指定的列是有限制的，仅允许以下几点：
1.被分组的列。
2.为每个分组返回一个值得表达式，如聚合函数计算出的列。






*** 使用 having 子句进行分组筛选：
用来对分组后的数据进行筛选，将‘分组’的列用来限定条件，其他的列不行 《只能在group by 里面用》


例1：查询年级总人数超过1的年级。
select count(*) as 人数 ，SGrade as 年级 from Student group by SGrade having count(*)>1


例2：查询平均分达到及格的课程信息。
select CourseID as 课程编号，avg(Scores) as 平局分 from Student group by CourseID having avg(Scores)>60




注意：having 和 where 可以在同一个 select 语句中一起用，使用的顺序是：
where-->group by-->having




例3：查询每门课程及格总人数和及格学生的平局分。
select count(*) as 人数，avg(Scores) as 课程平局分，CourseID as 课程 from Student
where Scores>=60 group by CourseID 




例4：查询没门课程及格总人数和及格平局分在70分以上的记录。
select count(*) as 人数，avg(Scores) as 课程平局分，CourseID as 课程 from Student
where Scores>=60 group by CourseID having avg(Scores)>=70




例5：在按照部门分类的员工表中，查询‘有两个及其以上员工的工资不低于2000的部门编号’。
select count(*) as 人数，bumen as 部门，Gongzi as 工资  from GongSi where G>=2000 group by A
having count(*)>=2






*** 多表连接查询
** 多表连接查询的分类：通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征


以下几种常用的连接查询方式：内连接、外连接。


1.内连接：最经典、最常用的连接查询，它根据表中共同的列来进行匹配。
《特别是两个表存在主外键关系时通常会使用内连接查询》
-- 内连接通常使用‘=’或‘<>’等比较运算符来判断两列数据值是否相等。
内连接使用 inner join .....on 或 where 来进行表之间的关联：
1.在 where 子句中指定连接条件:
select Students.SName,Score.CourseID,Score.Scores
from Students,Score
where Students.Scode = Score.StudentID


from 后面紧跟了两个表名，然后在字段列表中用‘表名.列名’来区分列，再where 条件中加以判断，要求学生编号信息相等


2.在 from 子句中使用 inner join ....on ：
1. select S.SName,C.CourseID,C.Score
  from Score as C
  inner join Students as S on C.StudentID = S.SCode


inner join 用来连接两个表
inner可以省略
on 用来设置条件
as 指定表的‘别名’ 。《如果查询的列名在用到的两个或多个表中不重复，则对这一列的引用不必用表名来限定》




2. select S.SName,C.CourseID,C.Score
  from Students as S
  inner join Score as C
  on  (C.StudentID = S.SCode)
  where C.Score>=60 and C.CourseID>1

where 用来限定查询条件



3.连接三个表或以上：
select S.SName as  学生姓名,CS.CourseName as 课程姓名,C.Score as 考试成绩
from Students as S
inner join Score as C on (C.StudentID = S.SCode)
inner join Course as CS on (CS.CourseID = C.CourseID)
注意：以上语句要用来自不同表的数据






2.外连接：至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一张表的记录。
《外连接可以是左外连接、右外连接》
-- 以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中：
  对那些不符合连接条件的列，将被填上 null 值后再返回到结果集中。


 1.左外连接查询：
左外连接查询的结果集包括left join 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，
若左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值


左连接查询使用 left join ...on 或 left outerjoin ...on 关键字来进行表之间的关联。


 例：
select S.SName,C.CourseID,C.Score
from Students as S
left join Scores as C on C.StudentID=S.SCode


若 C.StudentID=S.SCode 匹配成功 ，则返回记录集（S.SName,C.CourseID,C.Score的值）
若没有找到匹配的记录，则返回null值填充空值。






以下返回的结果是什么？<一样>
select CS.CourseName,C.StudentID,C.Score
from Course as CS
left outer join Scores as C on CS.CourseID = C.CourseID






2.右外连接查询：
与左外连接类似，只不过要包含右表中所有匹配的行，若右表中有的项在左表中没有对应的项，则以null填充。


右连接查询使用 right join ..on 或 right outer join ...on 关键字来进行表之间的关联。


例：表名 Titles 和 表名 Publishers
select Titles.Title_id,Titles.Title,Publishers.Pub_name
from titles
right outer join Publishers on Titles.Pub_id=Publishers.Pub_id




以下语句返回的结果是否相同？
1.
select CS.CourseName,C.StudentID,C.Score
from Course as CS
right outer join Scores as C on CS.CourseID = C.CourseID


2.
select CS.CourseNameC.StudentID,C.Score
from Course as CS
inner outer join Scores as C on CS.CourseID = C.CourseID   -- 报错