转：SQL Server 基础之《学生表-教师表-课程表-选课表》
SQL Server 基础之《学生表-教师表-课程表-选课表》
数据库表结构及数据
建表
View Code测试数据
View Code数据库多表查询之 where & INNER JOIN
在多表查询中，一些SQL开发人员更喜欢使用WHERE来做join，比如：
SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;
WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。
缺点：在上面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。
为了避免创建笛卡尔积，应该使用INNER JOIN ：
SELECT a.ID, b.Name, b.Date FROM Customers a INNER JOIN Sales b ON a.ID = b.ID;
优点：如上面语句，使用inner join 这样数据库就只产生等于ID 的1000条目标结果。增加了查询效率。
练习题目
1.查询“001”课程比“002”课程成绩高的所有学生的学号；
|
1
2
3
4
|
select * from(select S#,Score from SC where C#='001') a,(select S#,Score from SC where C#='002') bwhere a.S#=b.S# and a.Score>b.Score |
2.查询平均成绩大于60分的同学的学号和平均成绩；
方法一：
|
1
2
3
4
|
select * from(select S#,AVG(score) as av from scgroup by S# ) Swhere S.av>60 |
方法二：
|
1
2
3
4
|
select S#,AVG(Score) as AvgScorefrom SCgroup by S#having AVG(Score)>60 |
3.查询所有同学的学号、姓名、选课数、总成绩；
|
1
2
3
4
5
|
select S.S#,S.Sname,COUNT(C#) as CourseCount, SUM(score) as ScoreSumfrom Student as S left join Sc as Con S.S#=C.S#group by S.S#,S.Snameorder by S.S# |
4.查询姓“李”的老师的个数；
|
1
|
select count(distinct Tname) as count from Teacher where Tname like '李%' |
5.查询没学过“叶平”老师课的同学的学号、姓名；
|
1
2
3
4
|
select S.S#,S.Snamefrom Student as Swhere S.S# not in(select distinct(sc.S#) from SC sc,Course c,Teacher t where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平') |
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
--解法一：求交集
select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='001'
intersect
select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='002'
--解法二：使用exists
select s.S#,s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C#='001' and exists
(
select * from SC sc2 where sc.S#=sc2.S# and sc2.C#='002'
)
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。那么，这里我们来看一下in和exists的区别：
①in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
②一直以来认为exists比in效率高的说法是不准确的。
-->如果查询的两个表大小相当，那么用in和exists差别不大。
-->如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名；
--方法一
select s.S#,s.Sname
from Student s
where s.S# in
(
select S# from Sc sc where sc.C# in
(
select C# from Teacher t
inner join Course c on t.T#=c.T#
where t.Tname='叶平'
)
)
--方法二
select s.S#,s.Sname
from Student s
where s.S# in
(
select sc.S#
from SC sc,Course c,Teacher t
where c.C#=sc.C# and c.T#=t.T# and t.Tname='叶平'
group by sc.S#
having COUNT(sc.C#)=
(
select COUNT(c1.C#)
from Course c1,Teacher t1
where c1.T#=t1.T# and t1.Tname='叶平'
)
)
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select s.S#,s.Sname from Student s, (select sc1.S#,sc1.Score from SC sc1 where sc1.C#='002') a, (select sc2.S#,sc2.Score from SC sc2 where sc2.C#='001') b where s.S#=a.S# and s.S#=b.S# and a.S#=b.S# and a.Score<b.Score
（9）查询有课程成绩小于60分的同学的学号、姓名；
select s.S#,s.Sname from dbo.Student s left join dbo.Sc sc on s.S#=sc.S# where sc.score < 60 group by s.S#,s.Sname
select s.S#,s.Sname
from Student s
where s.S# in
(
select distinct(sc.S#) from SC sc
where s.S#=sc.S# and sc.Score<60
)
（10）查询没有学全所有课的同学的学号、姓名；（可以从学全的学生中取反）
select s.S#,s.Sname
from Student s
where s.S# not in
(
select sc.S# from SC sc
group by sc.S#
having COUNT(distinct sc.C#)= --按照学号分组计算每个学号总的课程数
(
select COUNT(distinct c.C#) from Course c --计算总得课程数
)
)
（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
--方法一
select s.S#,s.Sname from dbo.Student s
where s.S# in
(
select distinct(sc.S#) from dbo.Sc where sc.C#
in(select distinct(sc.C#) from dbo.Sc sc where sc.S# = '001')
)
--方法二
select distinct(s.S#),s.Sname from dbo.Student s
inner join dbo.Sc
on s.S# =sc.S# and s.S# in
(
select distinct(sc.S#) from dbo.Sc where sc.C#
in(select distinct(sc.C#) from dbo.Sc sc where sc.S# = '001')
)
--方法三
select distinct(s.S#),s.Sname
from Student s,SC sc
where s.S#=sc.S# and sc.C# in
(
select distinct(sc2.C#) from SC sc2
where sc2.S#='001'
)
order by s.S# asc
（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；（感觉跟11题有重叠）
select distinct(s.S#),s.Sname
from Student s,SC sc
where s.S#=sc.S# and s.S#!='001' and sc.C# in
(
select distinct(sc2.C#) from SC sc2
where sc2.S#='001'
)
order by s.S# asc
（13）把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update SC set Score=
(
select AVG(score) from SC sc,Course c,Teacher t
where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平'
)
where C# in
(
select distinct(sc.C#) from SC sc,Course c,Teacher t
where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平'
)
查询“叶平”老师教的课
select * from dbo.Sc sc where sc.C# in
(
select c.C# from dbo.Teacher t
inner join dbo.Course c
on t.T#= c.T#
where t.Tname='叶平'
)
--（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
select s.S#,s.Sname
from Student s
where s.S#!='002' and s.S# in
(
select distinct(S#) from SC
where C# in (select C# from SC where S#='002')
group by S#
having COUNT(distinct C#)=
(
select COUNT(distinct C#) from SC
where S#='002'
)
)
（15）删除学习“叶平”老师课的SC表记录；
delete from SC where C# in
(
select c.C# from Course c,Teacher t
where c.T#=t.T# and t.Tname='叶平'
)
（16）向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
insert into SC
select s.S#,'002' C#,(select AVG(sc.score) from dbo.Sc sc where sc.C# = '002') score
from dbo.Student s where s.S# not in
(
select distinct(sc.S#) from dbo.Sc sc where sc.C# ='002'
)
（17）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select t.S# as '学生ID', (select Score from SC sc where sc.S#=t.S# and sc.C#='002') as '语文', (select Score from SC sc where sc.S#=t.S# and sc.C#='003') as '数学', (select Score from SC sc where sc.S#=t.S# and sc.C#='004') as '英语', COUNT(t.C#) as '有效课程数', AVG(t.Score) as '有效平均分' from SC t group by t.S# order by AVG(t.Score)
sql中自连接的使用
select sc2.S# as '学生ID', (select Score from SC sc1 where sc1.S#=sc2.S# and sc1.C#='002') as '语文' from SC sc2 group by sc2.S#
http://www.cnblogs.com/edisonchou/p/3878135.html
浙公网安备 33010602011771号