第十一章 连接查询和分组查询

1.分组查询原理

        比如：

                 一个表中又三个学期的人数考试信息，现在我们要查询每个学期的人数，这时我们用select----from---where会产生代码冗余，也就是我们通常说的垃圾代码，这个时候我们就会用到group by 关键字。

2.单列分组查询

     语法：                                                                                 eg：

               select  列名，聚合函数 from  表名                                     select  count(*)as 人数,grade as 年级  

               where  限制条件                                                                 from  Student

               group  by  被分组的列                                                        group  by  grade

               order  by  列名

注意：

        select表中只能包含：

                                          （1）被分组的列

                                          （2）为每个分组返回一个值的表达式，如聚合函数

3.多列分组查询

       语法：                                                                                       eg：

                select  列名1，列名2，聚合函数 from  表名                            select  count(*) as 人数,grade as 年级,sex  as 性别

                where  限制条件                                                                       from  Student

                group  by 被分组的列名1，列名2                                             group  by grade,sex

                order  by  被排序的列名                                                            order  by----

      注意：

                （1）单列查询不需要 order by      多列查询尽量添加 order  by

                （2）多列查询分组排序，默认情况以后边一个进行排序

4.分组筛选（having）：

             语法:                                                                eg:

                       select -----from  表名                                      select count(*) as 人数,grade as 年级

                       where ------                                                     from  Student

                       group by ------                                                 group  by  grade

                       having -------                                                    having count(*)>15

           

5.如何获得总人数超过15人的年级,地址中含有上海

        select  count(*) as 人数,grade as 年级 from  Student

        where  address  like'%上海%'

        group  by  grade

        having  count(*)>15

6.where和having的区别：

      （1）位置：

                              where可以用于select、update、delete和insert into values(select * from table where ..)语句中。

                              having只能用于select语句中

     （2）执行顺序：

                                 

                                    where的搜索条件是在执行语句进行分组之前应用

                                    having的搜索条件是在分组条件后执行的

                                    即如果where和having一起用时，where会先执行，having后执行

     （3）子句的区别：

                                       where子句中的条件表达式having都可以跟，而having子句中的有些表达式where不可以跟；

                                       having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以

     

     总结：

                   1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
                   2.GROUP BY 子句用来分组 WHERE 子句的输出。
                   3.HAVING 子句用来从分组的结果中筛选行

7.内连接（inner join on）:                                       

                          使用比较运算符根据每个表的通用列中的值匹配两个表中的行

           eg：                                                                                                               

                  select Result.studentName,Subject.subjectID,Subject.studentName                

                  from  Result,Subject          会出现交叉连接多条数据                                          

                where  Result.id=Subjec.subjectId     主外键相等

 

 

        如： select  *  from  Result,Subject   会产生n多个数据

 

        语法：

                  select  表.列名,表1.列名

                  from  表1,表2

                  where-------

          等价于：

                           语法：                                                                   eg：

                                     select  表.列名,表1.列名                                       select  * from  Result

                                     from   表1                                                             inner  join  Subject

                                     inner  join  表2                                                      on  Result.id=Subject.subjectId

                                     on-------

8.下面的查询语句会返回多少行记录？

      SELECT S.SName,C.CourseID,C.Score FROM Students AS S

      INNER JOIN Score AS C ON C.StudentID <> S.SCode

      <>:查到的就是除了=以外的数据

9.三表内连接

          eg：

                 select Result.id,Result.SubjectName,Subject.SubjectName,Grade.GradeName

                 from  Result

                 inner  join Subject on Subject.Id=Result.Id

                 inner  join Grade on Grade.GradeId=Subject.gradeID

       注意：from表后与join 后的表名必须要又联系

10.内连接：（inner  join  on）

                   典型的连接运算，使用像   =   或   <>   之类的比较运算符,包括相等连接和自然连接。 内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行 

          eg: 

                  select Result.id,Result.SubjectName,Subject.SubjectName,Grade.GradeName

                 from  Result

                 inner  join Subject on Subject.Id=Result.Id

                 inner  join Grade on Grade.GradeId=Subject.gradeID

11.左外连接：（left outer join/left  join）

                      是左边表中的数据为基准，若左表有数据右表没有数据，否则显示左表中的数据右表中的数据显示为空 

                 select grade.name,grade.id,result.studenResult

                 from  Grade  left  join  Result  on  grade.id=Result.id

          主表（左表）students中的数据逐条匹配表score中的数据：

                （1）匹配，返回到结果集

                （2）不匹配，null值返回到结果集

12.右外连接：（right outer join/right join）

                  是右边表中的数据为基准，若右表有数据左表没有数据，否则显示右表中的数据左表中的数据显示为空 

                 select grade.name,grade.id,result.studenResult

                 from  Grade  left  join  Result  on  grade.id=Result.id

        右表与左表原理相同，右表逐条去匹配记录，否则null补充

 课外拓展：

           1.全连接（完整外部连接）： 

                      关键字（full  join 或 full   outer  join）完整外部连接返回左表和右表的所有行，当某行再另一个表中没有匹配行时，则另一个表的选择列表列包含空值（null），如果表之间有匹配行时，则整个结果集行包含基表的数据值

         总的来说，就是讲内，左，右外连结果全部显示

    eg：

           select  S.name,C.courseID,C.score

           from  Students   as  S

           full   join  score as  c

           on  S.scode=C.studentID

          2.交叉连接（关键字：cross  join）不带where条件的

          eg1:

                   select  * from table1  cross  join  table2

         eg2:

                  select  *from  book as  a cross  join  stu  as  b  order  by  a.id

          概念：

                     没有where子句交叉连接将产生连接所涉及的表的笛卡尔积

                     第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小

                   （table1和table2交叉连接产生3*3=9条记录）