1分组查询
1.1使用GROUP BY 进行分组查询
SELECT CourseID,AVG(SCore) AS 课程平均成绩
FROM Score
GROUP BY CourseID
几个实例
(1)查询男女学生的人数各是多少
首先按照性别列进行分组:GROUP BY SSex
其次对每个组进行总数的统计,用到聚合函数COUNT()
完整的T-SQL语句如下:
SELECT COUNT(*) AS 人数,SSex FROM Students
GROUP BY SSex
(2)查询每个年纪的总人数
SELECT COUNT(*) AS 年纪人数, SGrade FROM Students
GROUP BY SGrade
(3)查询每个科目的平均分,并且按照由低到高的顺序排列显示
SELECT CourseID , AVG(Score) AS 课程平均成绩 FROM Score
GROUP BY CourseID
ORDER BY AVG(Score) DESC
1.2 多列分组查询
需要统计每个学期的男女生人数.
SELECT COUNT(*) AS 人数 ,SGrade AS 年级 , SSex AS 性别 FROM Students
GROUP BY SGrade,SSex
ORDER BY SGrade
使用GROUP BY 关键字时,在SELECT列表中可以指定的列是有限制的,仅允许出现以下几项
a.被分组的列
b.为每个分组返回一个值的表达式,例如聚合函数计算出的列
1.3使用HAVING子句进行分组筛选
HANING子句用来对分组后的数据进行筛选,将"组"看做"列"来限定条件
(1)查询年纪总人数超过15的年纪
SELECT (*) AS 人数,SGrade AS 年级 FROM Students
GROUP BY SGrade
HAVING COUNT(*) > 15
(2)查询平均分及格的课程信息
SELECT CourseID AS 课程编号, AVG(Score) AS 课程平均成绩
FROM Score
GROUP BY CourseID
HAVING AVG(Score) >= 60
注意:
在SELECT语句中,WHERE,GROUP BY, HAVING子句和聚合函数的执行次序如下:WHERE子句从数据源中去掉不符合其搜索条件的数据;GROUP BY 子句收集数据行到各个组中,统计函数为各组计算统计值;HAVING 子句去掉不符合起组搜索条件的各组数据行
(3)查询每门课成绩及格总人数和及格学生的平均分
SELECT COUNT(*) AS 人数,AVG(SCore) AS 平均分 , CourseID AS 课程 FROM Score
WHERE Score >= 60
GROUP BY CourseID
(4)查询每门课程及格总人数和及格平均分在80分以上的记录
SELECT COUNT(*) AS 人数,AVG(SCore) AS 平均分, CourseID AS 课程 FROM Score
WHERE Score >= 60
GROUP BY CourseID
HAVING AVG(Score) >= 80
(5)在按照部门分类的员工工资不低于2000的部门编号
SELECT 部门编号,COUNT(*) FROM 员工信息表
WHERE 工资 >= 2000
GROUP BY 部门
HAVING COUNT(*) > 1
2.多表联接查询
2.1 多表联接查询的分类
(1)内联接
内联接是最典型,最常用的联接查询,它根据表中共同的列来进行匹配,特别是两个表存在主外键关系时通常会使用到内联接查询
(2)外联接
外联接是至少返回一个表中的所有记录,根据匹配条件有选择性的返回另一张表的记录,外联接可以是左外联接,右外联接
2.2内联接查询
内联接查询通常会使用像"=""<>"等比较运算符来判断两列数据值是否相等.
内联接使用INNER JOIN...ON 关键字或WHERE 子句来进行表之间的关系, 内联接查询可以通过两种方式实现
(1)在WHERE 子句中指定的联接条件
SELECT Students.SName, Score.CourseID,Score.Score
FROM Students,Score
WHERE Students.SCode = SCore.StudentID
(2)在FROM子句使用INNER JOIN..ON
SELECT S.SName,C.Course,C.Score
FROM Student AS S
INNER JION Score AS C ON(S.SCode = C.StudentID)
在上面的内联接查询中
INTER JOIN 用来联接两个表
INNER可以省略
ON 用来设置条件
AS指定表的别名,如果查询的列名在一个或者多个表中不重复,则对这一列的引用不必用表名来限定
内联接查询通常不仅仅联接两个表,有时候还会牵涉到三个表或者更多表.可以用以下三表联接查询的T-SQL语句来实现
SELECT S.SName AS 学生姓名,CS.CourseName AS 课程名称, C.Score AS 考试成绩
FROM Students AS S
INNER JOIN Score AS C ON (S.Scode = C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)
2.3外联接查询
在内联接中,参与联接表的地位是平等的,在外联接中参与联接的表有主从之分,以主表的每行数据匹配从表的数据列,将符合联接条件的数据直接返回到结果集中,对那些不符合联接条件的列.将被填上NULL后在返回到结果集去
(1)左外联接查询
左外联接的结果集包括LEFT JOIN子句中指定的左表的所有行,而不仅仅是联接列所匹配的行,如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列均为空值
左外联结使用LEFT JOIN...ON 或 LEFT OUTER JOIN...ON关键字来进行表之间的关联.例如:
SELECT S.SName, C.CourseID,C.Score
FROM Students AS S
LEFT OUTER JOIN Score AS C ON S.Code = C.StudentID;
(2)右外联接查询与左外联接查询类似,只不过要包含右表中所有匹配的行,如果右表中有的项在左表中没有对应的项,则以NULL值填充
右外联结使用RIGHT JOIN...ON 或RIGHT OUTER JOIN...ON关键字来进行表之间的关联.例如:
SELECT Titles.Title_id,Titles.Title, Publishers.Pub_name
FROM titles
ROGHT OUTER JOIN Publishers ON Titles.Pub_id = PuBlishers.Pub_id