select语句(T-SQL), 多表查询总结;(连接查询，集合查询，子查询)

1.材料

 

学生表 (学号，姓名，性别，出生日期,专业，班级，总学分)

课程表 (课程号，课程名，学分，教师编号)

教师表 (教师编号，姓名，性别，出生日期，职称，学院)

成绩表(学生编号，课程编号，成绩)

1.连接查询

 通过连接运算可以查询多个表中不同实体的信息， 把多个表按照一定的关系连接起来,

 在用户开来就好像是查询一个表一样.

   1.使用连接谓词指定的连接,连接谓词又称连接条件;

       eg:查询学生的情况和选修课程的情况;

   

 select    s.sno as 学号,  s.sname as 姓名,   sc.cno  as  课程号  ,  course.cname as 课程名, sc.grade as 成绩

         from    student as s  ,  score as sc   , course

         where   sc.sno  = s.sno  and  sc.cno = course.cno ;

    2.  自然连接 :在目标中去除相同的字段名，

    3. 自连接: 将同一个表进行连接    

         eg:查询选修了'1201'的课程的成绩高于学号为'121002'的成绩的学生的学号，和成绩

           select  a.sno  as 学号  , a.grade as 成绩

         from score as a , score as b

     where a.cno='1201'  and a.grade > b.grade  and b.sno='121002'  and b.cno='1201'

    order by a.grade desc;

  使用join 关键字指定的连接

     <表名><连接类型><表名>  on <条件表达式>

      1.内连接: 是按照on指定的条件合并两个表, 返回满足条件的行;

     eg:  查询学生的情况和选修课程的情况

             select *   from  student   inner join    score  on  student.sno = score.cno;

            内连接是系统默认的 , 可以省略inner;

eg: 查询选修了数据库系统课程且成绩在84分以上的学生情况

     select  a.*
     from student as a join score  as b on a.sno = b.sno   join    course as c  on   c.cno = b.cno  
     where b.grade > 84 and c.cname='数据库系统';

    2. 外连接:不但返回满足条件的行，还包括相应表中的所有行.外连接只能连接两个表

•         左外连接（left outer join）:   包括满足条件的行, 保括左表的所有行,
•        右外连接(right outer join):       包括满足条件的行,    包括右表的所有行,
•        完全外连接(full  outer join):      包括满足条件的行, 包含两个表的所有行,

             eg:  分别采用左外连，右外连接，全连接 接查询教师任课情况

      

     select  teacher.tname, course.cname

                      from teacher  left   join           course     on  teacher.tno = course.tno;

select  teacher.tname, course.cname

                      from teacher  right   join           course     on  teacher.tno = course.tno;

select  teacher.tname, course.cname

                      from teacher  full   join           course     on  teacher.tno = course.tno;

3.交叉连接:  采用交叉连接，查询教师和课程所有可能组合

 select teacher.tname, course.cname

from teacher cross join course;

 

2.子查询

 一个select-from-where 称为一个语句块.在where 子句或having子句中，可以使用另一个查询块的结果作为条件的一部分.

子查询的处理过程是由内向外，即有子查询到父查询;

   1.   in子查询 

        语法：  <表达式>[not ]  in （子查询）

 eg: 查询选修某课程的学生人数多于4人的教师姓名

      select   teacher.tname as  教师姓名

        from  teacher

        where   tno  in (

       select  course.tno

        from  course join score on   course.cno   = score.cno

        group by  course.tno

        having count(*) > 4

      );

eg: 查询在 '计算机' 专业任课的教师情况

           1)   select * from teacher

                  where tno  in

                 (   select a.tno

                      from teacher a , course b , score c , student d

                       where a.tno = b.tno  and b.cno = c.cno and  c.sno = d.sno  and d.speciality = '计算机'

                    );

               2) 

select  distinct   teacher.*

from student join score on student.sno=score.sno  join course on  course.cno = score.cno join  teacher on teacher.tno = course.tno

 where  student.speciality='计算机';

       比较子查询

             是指父查询与子查询之间用比较运算符关联

        语法: <表达式>{  <  |  <=  |   =  |   >  |  >=   |   !=   | <>  } { ALL  | SOME | ANY}  (子查询)  ;

ALL :  当表达式与子查询结果集中每个值都满足比较关系，返回true,

SOME和ANY:   当表达式与子查询结果集中的某一个满足比较关系, 返回true

       eg: 查询比所有 '计算机'  专业  学生年龄都小的学生

 1) select  *

from student

where    student.sbirthday   >    ALl (select  sbirthday from student where speciality='计算机');

2)  select  * from student

 where  student.sbirthday   >  (select  max(sbirthday) from student where speciality='计算机');

 

exists 子查询

  当子查询返回一个或多个行，exists 返回true, 否则返回false;

    not exists 相反；

eg:  查询选修1004课程的学生姓名;

 select student.sname as 学生姓名

 from  student

where exists(select  * from score  where student.sno =score.sno and score.cno='1004' )

子查询和连接查询往往都要涉及两个表或多个表，区别:

   连接查询： 可以合并两个或多个表的数据,

  子查询: select 语句的结果只能来自一个表.

 

集合查询

将两个或多个SQL语句的查询结果集合并起来,成为一个表 , 完成查询任务.

   union : 并运算， 返回两个结果集的所有行, 不包括重复行

  union  all : 并运算,  返回两个结果集的所有行， 包括重复行

  intersect : 交运算, 返回两个结果集都有的行,

   except( PL/SQL : minus) :  差运算， 返回第一个结果集中有的而在第二个结果集中没有的行

 语法格式:

<select语句1>

  {       union | union ALL    |        intersect  |  minus }

<select语句2>

  集合查询的规则:

     列的数目和数据类型 兼容

     不能再单独的查询语句中使用order by 子句，

     eg:  查询性别为女 及 选修了课程号为 4002的学生

    select  s.sno ,  s.sname , s.ssex

    from  student as s

where  s.ssex='女'

union   

  select  s.sno , s.sname, s.ssex

  from student as s ,  score as b

 where    s.sno = b.sno  and b.cno='4002'

eg: 查询既选修了英语又未选修数字电路的学生的选修；

select  a.*
from student as a , score as b , course as c
where   a.sno = b.sno and b.cno = c.cno   and c.cname='英语'
except
select  a.*
from student as a , score as b , course as c
where   a.sno = b.sno and b.cno = c.cno   and c.cname='数字电路'

 

如果查询条件对同一个子段有两个或多个要求, 使用集合查询