*** 分组查询: ** 使用 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) 注意:以上语句要用来自不同表的数据
右连接查询使用 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 -- 报错
《升序》ASC 《降序》DESCrelationship*** 分组查询:** 使用 group by 进行分组查询语句:《有列名:StudentID,CourseID,Scores》select CourseID,avg(S cores) as 课程平均成绩 from Score group by CourseID例 1:查询男女学生的人数各是多