SQL课堂笔记多表查询

      2017.11.14

二:最基础的连接查询----多表查询
 1.使用内部连接
  内部连接也叫自然连接,最常见的连接形式
   语法: 
    select 选择列表 from 表名1 ,表名2 where 表名1.列名1=表名2.列名2
         或select 选择列表 from 表名1 join 表名2 on 表名1.列名1=表名2.列名2

 例1:查询每个学生及其选修课程情况:
  select student.*,sc.* from student,sc where student.sno=sc.sno
  select student from student,sc where studnet.sno=sc.sno
  select student.sno,sname,cno from student,sc where studnet.sno=sc.sno(唯一列可以不写来源)

 例2:查询所有学生的学号,姓名和成绩:
  select x.sno,x.sname,y.grade from student as x, sc as y where x.sno=y.sno
      或select x.sno,sname,grade
 
 自身连接(一个表和自己进行连接):

 例3:查询所有比'刘晨'大的学生的姓名和年龄:
  select X.sname,X.sage from student as X,student as Y where X.sage>Y.sage and Y.sname='刘晨'

三:复合条件连接(where子句中含多个连接条件)
 
 例4:查选修2号课程且成绩在90分以上的所有学生的学号,姓名:
  select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90
 
 例5:查询2号课程的成绩在前3名学生的姓名:
  select top 3 sname as 前3名学生 from student,sc where student.sno=sc.sno and sc.cno=2 order by sc.grade desc
 
 例6:查询每个学生的学号,姓名,选修的课程名及成绩:
  select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno

五:以join关键字指定的连接:
 从多个表合并数据,并涉及多个表之间的连接,多表间的连接可分为内连接,外连接和交叉连接.被连接的表可以来自同一或不同的数据库

 1.内连接:
  select 列名 from <数据源> join <数据源> on 连表条件
  说明:(1)内连接按照on指定的连接条件合并两个表,返回满足条件的行
       (2)作用等价利用where子句进行连表查询

  1.在学生数据库中,查询学号,姓名,成绩
   select student.sno,sname,grade from student join sc on student.sno=sc.sno
   select x.sno,sname,grade from
 多表连接:
  from(表1 join 表2 on 表1.字段号=标. 字段号 join 表3 on 表1.字段号=表3.字段号)
  例6:查询每个学生的学号,姓名,选修的课程名及成绩:
   select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
   select studnet.sno,sname,cname,grade from (student join sc on student.sno=sc.sno) join course on sc.cno=course.cno

 2.外连接:
  3种类型:
   1.左外连接 left out join 返回所有匹配的行并从关键值join左边的表中返回
   2.右外连接
   3.完全外连接
  语法:
   select 列名 from <数据源> [left][right][full]oueter join <数据源> on 连表条件

  1.显示所有学生的学号,姓名,考试成绩(使用左连接):
   select x.sno,sname,grade from student x left outer join sc y on x.sno=y.sno
  
  2.显示所有学生的学号,姓名,考试成绩(使用右连接):
   select y.sno,sname,grade from sc x right outer join studnet y on x.sno=y.sno

  3.显示所有学生的学号,姓名,考试成绩(使用完全连接):
   select x.sno,sname,grade from student x full outer join sc y on x.sno=y.sno
 
 3.交叉连接(非限制连接,将两个表不加任何限制的组合起来,没有where子句的交叉连接将产生连接所涉及的表的笛卡儿积):
  select 列名 from <数据源> cross join <数据源>
  
  查询所有学生选课的可能情况:
   select sno,sname,cno,cname from student cross join course
       或 select sno,sname,cno,cname from student,course

 (一般不加条件,非加的话用where)加条件:select sno,sname,cno,cname from student cross join course where sdept='计算机'and student.sno=sc,sno
 
 4.联合查询(把多个select语句返回结果集组合到一个结果集):
  select 语句1 union [all] select 语句2

  注意:
   1.union运算符连接的查询必须有类似的数据类型,相同的字段数目,并且在字段列表中字段顺序相同
   2.默认情况下,SQL server2008将自动删除结果集中重复的记录.如果想要返回所有的数据,可以在union后增加all选项
   3.第一个select语句的字段名决定了整个结果集的字段名,如果想要改变结果集里显示
   4.
  查询学校所有师生的信息:
   select sno 编号,sname 姓名 from studnet union select tno,tname from teacher

练习:

use students
go
--1、查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。
select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90

--2、查询所有学生的学号、姓名、选修的课程名及成绩。
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
 
--、用自身连接查询所有比“张立”小的学生的姓名和年龄。
select X.sname,x.sage from student as X ,student as Y where X.sage<Y.sage and Y.sname='张立'

--4、查询1号课程的成绩在前2名学生的姓名 。
select top 2 sname as 前2名学生 from student,sc where student.sno=sc.sno and sc.cno=1 order by sc.grade desc

--5、查询所有计算机专业的学生的选课情况（包括姓名，课程号及成绩）。
select student.sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and  sdept like '计算机'

--6、查询选修课成绩在80分以上的学生姓名。
select student.sname from student,sc where student.sno=sc.sno and sc.grade>80

--7、统计男女同学选修课的平均分和最高分。
select ssex,AVG(grade) as 平均分,MAX(grade) as 最高分 from sc,student.sno=sc.sno group by ssex
或:select ssex,AVG(grade) as 平均分,MAX(grade) as 最高分 from student join sc on student.sno=sc.sno group by ssex

--8、用自然连接查询电子专业女生的姓名、选修的课程名及成绩。
select sname,sdept,grade from student a join sc b on a.sno=b.sno join course c on b.cno =c.cno where a.sdept like '电子'  and a.ssex ='女'
或:select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and ssex='女'and sdept='电子'
或:select student.sno,sname,cname,grade from(student join sc on student,sno=sc.sno)join course......
--9、用内连接查询电子专业男生的学号、姓名、选修的课程名及成绩。
select a.sno,a. sname,a.sdept,b.grade from student a join sc b on a.sno=b.sno join course c on b.cno=c.cno where a.sdept like '电子' and a.ssex='男'

--10、用外连接查询所有学生的选课情况，包括没有选课的情况。
select x.sno,sname,cno from student x full outer join sc y on x.sno=y.sno

--11、使用交叉连接查询所有学生选课的可能情况。
select sno,sname,cno,cname from student cross join course

--12、使用UNION联合查询查询学校所有师生的信息。（在数据库中创建“teacher表”,字段有tno,tname,ssex。数据类型参考student表。“teacher表”数据自由添加5行）。
select sno 编号,sname 姓名,ssex 性别 from student union select tno,tname,tsex from teacher