SQL Server 基础之《学生表-教师表-课程表-选课表》（二）

表结构

--学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）

--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）

--成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）

--教师表tblTeacher（教师编号TeaId、姓名TeaName）

CREATE TABLE tblStudent 
  ( 
     StuId   INT, 
     StuName nvarchar(32), 
     StuAge  INT, 
     StuSex  nvarchar(8) 
  ) 

CREATE TABLE tblCourse 
  ( 
     CourseId    INT, 
     CourseName nvarchar(32), 
     TeaId    INT 
  ) 

CREATE TABLE tblScore 
  ( 
     StuId   INT, 
     CourseId    INT, 
     Score INT 
  ) 

CREATE TABLE tblTeacher 
  ( 
     TeaId    INT, 
     TeaName nvarchar(16) 
  )

  
  insert into tblStudent select 1,N'刘一',18,N'男' union all
 select 2,N'钱二',19,N'女' union all
 select 3,N'张三',17,N'男' union all
 select 4,N'李四',18,N'女' union all
 select 5,N'王五',17,N'男' union all
 select 6,N'赵六',19,N'女' 

 
 insert into tblTeacher select 1,N'叶平' union all
 select 2,N'贺高' union all
 select 3,N'杨艳' union all
 select 4,N'周磊'
 
 insert into tblCourse select 1,N'语文',1 union all
 select 2,N'数学',2 union all
 select 3,N'英语',3 union all
 select 4,N'物理',4
 
 insert into tblScore 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 select 6,4,71

数据库多表查询之 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、查询“1”课程比“2”课程成绩高的所有学生的学号；

   select st.StuId from tblStudent as st where 
   (select sc1.Score from tblScore as sc1  where sc1.StuId=st.StuId and  sc1.CourseId  = '1') >
   (select sc2.Score from tblScore as sc2  where sc2.StuId=st.StuId and  sc2.CourseId  = '2')

2、查询平均成绩大于60分的同学的学号和平均成绩；

 第一种

 select t.StuId,t.avgScore from 
    (select sc.StuId,AVG(sc.Score) as avgScore from tblScore as sc  group by sc.StuId ) as t
    where t.avgScore>60

第二种

    Select StuId,Avg(Score) as AvgScore From tblScore
    Group By StuId
    Having Avg(Score)>60

3、查询所有同学的学号、姓名、选课数、总成绩；

第一种

    select st.StuId,st.StuName,count(*) as courseCount,sum(sc.Score) as totalScore from tblStudent as st
    inner join tblScore as sc on st.StuId=sc.StuId
    group by st.StuId,st.StuName

第二种

    Select StuId,StuName,
    SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),
    SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)  
    From tblStudent s1

4、查询姓“李”的老师的个数； 

 Select Count(*) From tblTeacher Where TeaName like '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名；

select st.StuId,st.StuName from tblStudent as st 
    where st.StuId not in
    (
        select sc.StuId from tblScore as sc 
        inner join tblCourse as c on c.TeaId = sc.CourseId
        inner join tblTeacher as t on t.TeaId = c.TeaId
        where TeaName ='叶平' 
    )

 

 

 资料

http://www.cnblogs.com/edisonchou/p/3878135.html

https://blog.csdn.net/sujiacheng_123/article/details/53928474