sql数据库第五课

elect * from stumark
select * from courses

select avg(score) 平均分 from stumark
group by courseID

--分组统计每种课程的平均分
--通常集合函数配合分组来统计数据的
--在使用聚合函数时，所指定的列必须是聚合函数，或分组所指定的列
select courseID 课程编号,avg(score) 平均分 ,sum (score) 总分,
max (score) 最高分,min (score) 最低分,count (*) 总人数 from stumark
group by courseID

select * from stumark
insert into stumark values (75,56,1)
insert into stumark values (70,55,1)

--多列分组
select courseID 课程编号 ,stuID 学员编号,avg(score) 平均分 from stumark
group by courseID,stuID

--分组后筛选信息
select courseID 课程编号 ,stuID 学员编号,avg(score) 平均分 from stumark
group by courseID,stuID having count(score)>1

--内联接 inner join
select students.SName as 姓名,
stumark.Score as 成绩 from students
inner join stumark on students.ID=stumark.stuID

--内联接 inner join:(为表取别名)
select s.SName as 姓名,
m.Score as 成绩 from students as s
inner join stumark as m on s.ID=m.stuID

--内联接 join(单独用join)
select s.SName as 姓名,
m.Score as 成绩 from students as s
join stumark as m on s.ID=m.stuID

--内联接：where
select s.SName as 姓名,
m.Score as 成绩 from students as s, stumark as m
where s.ID=m.stuID

--三表联接查询
select s.SName as 姓名,m.score as 成绩,c.courseName as 科目
from students as s inner join stumark as m
on s.ID=m.stuID inner join courses c
on c.ID=m.courseID

--左外联接(如果左表存在的数据在右表中不存在以空的方式显示)
select s.SName,m.score from students as s left join stumark as m
on s.ID=m.stuID

select * from students
select * from stumark
--右外联接(如果右表存在的数据在左表中不存在以空的方式显示)
select s.SName,m.score from students as s right join stumark as m
on s.ID=m.stuID

转载于:https://www.cnblogs.com/xyb1117/archive/2012/03/05/2380045.html