第六天 -- 《2014-07-12 SQL进阶》1 -- CASE、子查询、分页、表连接

一、上午《03、CASE函数用法》

 1、CASE函数用法:

--（1）case用来做结果集字段值的替换的
--（2）它可以生成一个新列
--（3）相当于switch...case 和 if..else

用法一：

--case 表达式/字段 --如果case后面接表达式或者字段，那么这种结构只能做等值判断.且不能判断null值
-- when 值.then..自定义值
-- 。。。
-- else   如果上面的when都不满足就满足else
--end

如：

select StudentNo,StudentName,Sex,
  case  ClassId
     when 1   then '一期班'
     when 2   then '2期班'
     when 3   then '3期班'
     when '4'  then '4期班'  
    else  '我不知道' 
  end as 班级名称
from Student

用法一的不足：不能满足复杂的条件判断。

 

用法二：使用场合更多一些

--第二种使用方式：
--Case --如果case后面没有接表达式或者值，那么这种结构就相当于if...else可以做范围判断.它可以做null值判断
-- when 条件表达式 then 自定义值
-- 。。。。
-- else 值
--end

如：

select StudentNo,StudentName,
case 
  when  BornDate>'2000-1-1'   then '小屁孩'
  when  BornDate >'1990-1-1'  then '小青年'
  when  BornDate >'1980-1-1'  then '大叔'
  when  BornDate is null then  '不知道'
  when  Sex='男' then '我是男的'
  else   '中年'
end
 from Student

--再来个小例子：百分制转换为素质教育
select StudentNo,StudentResult,
case
when StudentResult>=90 then 'A'
when StudentResult>=80 then 'B'
when StudentResult>=70 then 'C'
when StudentResult>=60 then 'D'
when StudentResult IS NULL then '没有参加考试'
else 'E'
end
from Result 

 

 

二、上午《04、子查询》--《06、子查询的三种使用方式》--做where条件、做from数据源、做select字段值

 1、子查询做为where条件：当查询需要一个外部值做为条件的时候，可以使用一个独立的查询先得到这个值，再进行条件的判断

　　注意1：使用子查询做为条件的时候只能返回 一个字段（单列）
　　select * from Student where BornDate < (select * from Student where StudentName='冯晨旭')  -- 报错，因为是多列。而且如果子查询结果集是多行，也会出错。

　　注意2：子查询返回的值不止一行。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。但可以使用in/ not in
　　select * from Student where BornDate in  (select BornDate from Student)  -- 在此多个行可以，多个列绝对不行。

　　注意3：如果选择多列值，那么只能使用exists/ not exists
　　            实际上exists和not exists只能返回bool值，所以子查询select选择多少个列，它并不关心。

　　如：if exists (select * from sysdatabases)

       又如：select * from Student where exists (select * from Teacher where classid = 2)

 　　注意4：ntext、text、 image数据类型不能用在子查询的选择列表中。

 

 

2、子查询做为数据源：记得一定要为子查询获取的结果集添加 别名
--查询七期班的男生信息

select * from Student where ClassId=6 and Sex='男'            --下一行与本行等效
select * from (select * from Student where ClassId=6) as temp where Sex='男'

 

3、子查询做为字段的值
select   (select studentname from student where StudentNo=Result.StudentNo),  StudentResult from Result

 

 

 

三、上午《07、使用子查询实现分页》 --   分页

1、 简单分页--效率较差

--第一页（设定每页5行的话）
　　select top 5 * from Student
--第三页
　　select top 5 *  from Student where StudentNo not in (select   top  10  Studentno  from  Student)

--第N页
7 　　select top 5 *  from Student where StudentNo not in (select   top  5*(n-1)  Studentno  from  Student)

SQLServer中，这种分页方式效率很差。

因为子查询涉及到多次检索（先执行子查询，再从原表找出不在子查询结果集中的前5条），

而且使用top的话，最好还要排序（不排序，DBMS不保证顺序的）等等。

 

 

2、使用ROW_NUMBER()函数获得有序的连续行号，进行分页。（SQL Server2005 之后才有的函数）

--ROW_NUMBER() over(排序字段)：可以根据指定的字段排序，对排序之后的结果集的每一行添加一个不间断的行号
--注意：如果使用生成行号的结果集做为子查询，那么必须为结果集命别名，同时为行号添加名称

--前提工作：在查询结果集前面加一个列（连续的行号）
select ROW_NUMBER() over(order by studentno),  *   from Student

--实现分页： 以上面（带行号）结果集作为数据源，再查询一次。本例查询第2页（即从第六行到第十行）。
select * from (select ROW_NUMBER() over(order by studentno) as  id,  * from Student)  as  temp where id between 6 and 10

 

 

3、在MySQL中的分页查询，非常简单。如：

-- 第1页:  从第0行开始，取5条记录
select * from Student  limit  0 , 5

-- 第n页:  从第5*(n-1)行开始，取5条记录
select * from Student  limit  5*(n-1)  , 5

 

 

四、上午《08、子查询的练习》 --   子查询的小结

1、写子查询的时候，先写好直接结果查询的框架，再写子查询填入框架。

--例1： 查询年龄比“廖杨”大的学员，显示这些学员的信息
--第一步：先写出从student表检索学员信息，where条件先空着。
select * from Student where BornDate  <  (  )
--第二步：再写出父查询的条件，即要查询“廖杨”的出生日，完成上一步填空。
select * from Student where BornDate<(select BornDate from Student where StudentName='廖杨')


--例2：查询二期班开设的课程
--第一步：先写出从课程Subject表查询的课程信息，where条件先空着。
select * from Subject where ClassId = ( )
--第二步：再写出父查询的条件，即要根据“二期班”名称查询ClassId，完成上一步填空。
select * from Subject where ClassId=(select ClassId from grade where classname='二期班')



--例3：查询参加最近一次“office”考试成绩最高分和最低分
--第一步：先写出从Result成绩表查询最高分和做低分。where条件为科目ID即SubjectId先空着，还有条件考试日期（要最近的）也先空着。
select MAX(StudentResult) as 最高分,  MIN(StudentResult) as  最低分 from Result where SubjectId = ( )  and  ExamDate=( )
--第二步：从Subject科目表根据“office”查询到subjectid 填充上一步第一个空。再从Result成绩表查询office科目的最近开考试日期。subjectid参考前面。
select MAX(StudentResult) 最高分, MIN(StudentResult) 最低分 from Result where SubjectId=(
    select subjectid from Subject where SubjectName='office'
        ) and ExamDate=(
            select MAX(examdate) from Result where SubjectId=
                (select subjectid from Subject where SubjectName='office'
                )
            )

 

2、繁琐的子查询，效率低下。有的查询情景可以考虑表连接。

 

3、子查询分类和用法总结

--子查询：一个查询中还包含着另外N个查询
--分类：独立子查询（可以直接运行子查询语句）、相关子查询(子查询中使用了父查询中的结果)
--使用方法：
--     1.子查询做为where筛选条件：如果跟在> < >= <= ...后面，要保证子查询只返回一个值
--       A.如果查询返回了一行多列值，可以使用exists /not exists，否则报错
--       B.如果返回了多行一列，可以使用in /not in 
--     2.子查询可以做from数据源：
--       A.如果需要使用到子查询中的字段，必须保证字段有名字（需要为新添加的字段命名）
--       B.子查询做结果集必须命名
--     3.子查询做为列的值出现在select后    

 

 

五、上午《09、表连接》

1、简单连接

 

--查询所有学生的姓名、年龄及所在班级--
select Student.StudentName,  Student.BornDate,  grade.classname
from Student,  grade
where Student.ClassId = grade.ClassId

 

这种连接写起来比较简单，也很好理解。但是要格外注意where条件不可缺（缺了不报错）。忘写效果就等同交叉连接。

 

 

2、join连接

表连接有五种：内连接（inner join），左连接（left join），右连接（right join），全连接（full join），交叉连接cross join（效果等同上例忘写where）。

常用的连接就前三种。

 

3、内连接（inner join）

 （1）select中指定检索列，或者select * 检索所有列

select Student.StudentName,  Student.BornDate,  grade.classname
from Student
inner join grade on Student.ClassId = grade.ClassId

--它可以得到两个表中建立关联字段值相等的记录（获得两张表的所有列 --缺点是主外键列重复如下图）
select * from PhoneNum
inner join PhoneType on PhoneNum.pTypeId = PhoneType.ptId 

 

 

（2）避免出现主外键重复（表名.*）

select Student.* , School.name as 学校,  Area.name  as  地区    --如果还像上例一样select * 实际上等效于 select Student.* ,School.* , Area.*
from Student
inner join School                 --内连接School表                   
on Student.sch_id = School.id
inner join Area　　　　　　　　　　　--再内连接Area表
on School.AreaId = Area.id

上例中主要想查看学生信息，所以使用  Student.* 

 

 

4、左连接（left join）、右连接（right join）

（1）左表是指写在连接关键字前面的表。左连接可以得到左表的所有记录，如果建立关联的字段值在右表中不存在,那么右表中的字段值就以null值替代
　　select * from PhoneNum
　　left join PhoneType on PhoneNum.pTypeId=PhoneType.ptId
（2）右表是指写在连接关键字后面的表。右连接可以得到右表的所有记录，如果建立关联的字段值在左表中不存在,那么左表中的字段值就以null值替代
　　select * from PhoneNum
　　right join PhoneType on PhoneNum.pTypeId=PhoneType.ptId

5、交叉连接 （cross join）

　　返回连接表中所有的数据行的笛卡尔积，其结果集中的数据行数等于a表符合条件行数与b表符合条件行数的乘积。 （实际上我们很少需要这种结果，在1简单连接中应该避免忘写where子句。）

 

6、表连接的总结

另外连接还包括交叉连接，效果等同于没写where子句。

 

 

六、上午《09、表连接》--练习

总结：多表连接：先写出你需要的字段、使用表、方式。再考虑它来自于那一些表， 再考虑这些表如何建立关联

 

--案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级
select  Student.StudentName,
        DATEDIFF(yyyy,student.borndate,getdate()),
        grade.classname
from Student 
inner join grade 
on Student.ClassId=grade.ClassId 
where DATEDIFF(yyyy,student.borndate,getdate()) >= 20

--案例3：查询学生姓名、年龄、班级及成绩
select   Student.StudentName,
         DATEDIFF(yyyy,student.borndate,getdate()),
         grade.classname,Result.StudentResult
from Student 
inner join grade 
on Student.ClassId=grade.ClassId
inner join Result 
on  Student.StudentNo=Result.StudentNo

--案例5：请查询出所有没有参加考试（在成绩表中不存在的学生）的学生的姓名。
select Student.StudentName,  Student.StudentNo
from Student
left join Result 
on Student.StudentNo = Result.StudentNo 
where Result.StudentNo is null

--练习6：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格
select    Student.StudentName,
             DATEDIFF(YYYY,Student.BornDate,GETDATE()) as  年龄,
             case
                  when Result.StudentResult<60 then '不及格'
                  when Result.StudentResult is null then '缺考'
                  else cast(Result.StudentResult as CHAR(3))
             end  as  考试结果
from Student
left join Result 
on Student.StudentNo= Result.StudentNo