一、交查询（INTERSECT）
 
①交查询查出的数据是两个数据表中的哪块数据呢？
 
 
A,B数据的交集C部分为交查询最后的查询结果。
 
②我想要查询两个数据表之间的交查询，是不是把两个表用intersect连接起来就行了呢？
 
 
看来不是这样的，报错提示：UNION(联合查询)，INTERSECT(交查询)与EXCEPT(差查询)必须在目标列表中有相同的数目表达式，具体什么是数目表达式，我们前面的博客中有实例说明，不再赘述。
 
我们找数据表达式相同的两个数据表来进行交查询。
 
 
结果是可行的。
 
③如果我们在intersect前面的数据表中加上order by是什么样的结果呢？
 
 
显示语法错误，显然我们得出的结论是：intersect前面的表格中不能添加order by语句
 
④那么在intersect后面的表格中添加会是什么样呢？
 
 
放到后面就没有问题了，但有时会有影响，那么放到子查询语句中来避免就可以了。
 
二、差查询（EXCEPT）
 
①差查询是与交集相对立的补集么？
 
不是补集，而是相对补集
 
②什么是相对补集
 
 
B中A的相对补集为蓝色部分，而差查询，则是B中的数据减去A与B相交数据剩下的部分
 
 
此查询的结果正是‘旧员工信息’数据表中的数据，减去两个表格相交的数据。
 
三、思维导图（在前三篇博客基础上的拓展与总结）
 
 
到此数据库视频的第七章已梳理完毕，下一站，走起^_^

 
 
 
 1：
 
 
use db_sqlserver2
select 姓名, 工资, 面积, 金额, (工资+金额/1000) as 实发工资 from 职工,仓库, 订购单 
   where 职工.职工号=订购单.职工号 and 职工.仓库号=仓库.仓库号
 
 
 
 
 
 
 
 
 
 
 2：
 
 
select 姓名,工资,金额 from 职工,订购单 where 姓名 like '%平%' and 职工.职工号 = 订购单.职工号 order by 工资 desc
   
 
 
 
 3：
 
 
select 姓名,工资,金额 from 职工,订购单 where 姓名 like '%平%' and 职工.职工号 = 订购单.职工号 order by 工资 desc, 金额 desc

   
 
 
 
 
 
 
 
 
 
 
 
 4：
 
 
select 姓名, 工资, 城市, (select AVG(工资) from 职工) as 平均工资 , (工资-(select AVG(工资) from 职工)) as 与平均工资之差
from 职工, 仓库 where 仓库.仓库号=职工.仓库号
 
 
 
 
 
 
 
 
 
 
 
 
 5：带保存功能的多表连接查询
 
 
     在SQL语句中，利用into语句可以把查询到的结果保存成一张新表，然后再对新表进行数据信息查询。
 
 
     
 
 
select 仓库.仓库号, 城市, 面积, 姓名, 工资, 金额 into 多表连接产生的新表 from 仓库, 职工, 订购单 
where 仓库.仓库号=职工.仓库号 and 职工.职工号=订购单.职工号
 
 
 
select * from 多表连接产生的新表
 
 
 
 
 
 
 
 
 
 
 //查看不同仓库中的所有职工的仓库号、平均销售金额、最大销售金额、最小销售金额、最大销售金额与最小销售金额之差的信息
 
 
select 仓库号, AVG(金额) as 平均销售金额, MAX(金额) as 最大销售金额, MIN(金额) as 最小销售金额, 
(MAX(金额) - MIN(金额)) as 最大金额与最小金额之差 from 多表连接产生的新表 group by 仓库号;
 
 
 
 
 
 
 
 
 
 
 可以把分组查询结果再生成一张新表
 
 
select 仓库号, AVG(金额) as 平均销售金额, MAX(金额) as 最大销售金额, MIN(金额) as 最小销售金额, 
(MAX(金额) - MIN(金额)) as 最大金额与最小金额之差 into 分组查询产生的新表
 from 多表连接产生的新表 group by 仓库号;
 
 select * from 分组查询产生的新表
 
 
 
 
 
 
 6： 内连接查询(inner join)
 
 
     使用比较运算符对表之间的某些数据进行比较，并列出这些表中与连接条件相匹配的数据行。
 
 
     
 
 
  select 姓名, 城市 from 仓库 inner join 职工 on 职工.仓库号=仓库.仓库号
 
 
 
 
 
 
    多表的内连接查询
 
 
    
 
 
select 城市,面积, 姓名, 工资, 金额 from 仓库   
  inner join 职工 on 职工.仓库号=仓库.仓库号
  inner join 订购单 on 职工.职工号=订购单.职工号
  and 工资>1800 and 面积<1000 and 金额 != 16600
    
 
 
 
 
 
 7：左连接查询(left join)
 
 
       除满足连接条件的记录显示外，第一张表中不满足条件的记录也显示在结果集中。
 
 
     
 
 
select 姓名, 城市 from 仓库 
   left join 职工 on 职工.仓库号=仓库.仓库号 and 城市 is not null and 姓名 like '%王%'
 
 
 
 
 
 
 
 
select 城市, 面积, 姓名, 工资, 金额 from 仓库 
   left join 职工 on 职工.仓库号 = 仓库.仓库号
   left join 订购单 on 职工.职工号=订购单.职工号
   and 工资>1800 and 面积<1000 and 金额!=16600
   
 
 
 
 
 
 
   在第一个left join左连接中，第一张表是仓库表，第二张表是职工表，在第二个left join左连接中，第一张表是职工表，第二张表是订购单表
 
 
 
 
 
 
 8：右连接查询
 
 
       除满足连接条件的记录显示外，第二张表中不满足条件的记录也显示在查询结果集中
 
 
      
 
 
select 姓名, 城市 from 仓库 
right join 职工 on 职工.仓库号=仓库.仓库号 where 城市 is not null and 姓名 like '%王%'
 
 
 
 
   
 
 
select 城市, 面积, 姓名, 工资, 金额 from 仓库 
   right join 职工 on 职工.仓库号=仓库.仓库号
   right join 订购单 on 职工.职工号=订购单.职工号
   and 工资>1500 and 面积<1000 and 金额!=16600
 
 
 
 
 
 
 
 
 
 
select 城市, 面积, 姓名, 工资, 金额 from 仓库 
   right join 职工 on 职工.仓库号=仓库.仓库号
   right join 订购单 on 职工.职工号=订购单.职工号
   where 工资>1500 and 面积<1000 and 金额!=16600
把and关键字换为where关键字后的效果图，会发现那些无用的数据没有了 
 
 
 
 
 
 
 
 
 
 
 9：全连接查询
 
 
     除满足连接条件的记录显示外，两张表中的不能满足条件的记录也显示在查询结果集中
 
 
     
 
 
select 姓名,城市 from 仓库 full join 职工 on 职工.仓库号=仓库.仓库号 and 城市 is not null and
姓名 like '%王%';
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 集合的交、并、差运算查询
 
 
 为了进行并、交、差运算，要求运算的两个查询结果具有相同的字段个数，并且对应的字段的值要出自同一个值域，即具有相同的数据类型和取值范围
 
 
  10：并运算(union)
 
 
          主要将两个或者更多个查询的结果组合为单个结果集，该结果集包含联合查询中的全部查询的全部行
 
 
         
 
 
select 仓库号 from 仓库 where 城市='北京'
union
select 仓库号 from 职工 where 工资>2000
 
 
 
select 仓库号 from 仓库 where 城市='北京'
union
select 仓库号 from 职工 where 工资>2000

select distinct 仓库.仓库号 from 仓库, 职工 where 仓库.仓库号=职工.仓库号 and (城市='北京' or 工资>2000)
 
 
 
 
 
 使用union all 保留重复行
 
select 仓库号 from 仓库 where 城市='北京'
union all
select 仓库号 from 职工 where 工资>2000

 
 
  
 
 
 
 
 
 11：交运算(intersect)
 
 
   可以将两个select语句的查询结果通过交运算合并成一个查询结果
 
 
    
 
 
select 仓库号 from 仓库 where 城市='北京'
intersect
select 仓库号 from 职工 where 工资>2000
 
 
 
select distinct 仓库.仓库号 from 仓库, 职工 where 城市='北京' and 仓库.仓库号=职工.仓库号 and 工资>2000
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 12：差运算(except)
 
 
      可以计算两个select查询结果之间的数据差，即返回在一个查询结果中存在，但在另一个查询结果中不存在的所有行。
 
 
     
 
 
select 仓库号 from 仓库 where 城市='北京' 
except 
select 仓库号 from 职工 where 工资>2900
 
 
 
select 仓库号 from 仓库 where 城市='北京' and 仓库号 not in(select 仓库号 from 职工 where 工资>2900)

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

 
 
 use db_sqlserver2  
 select 姓名, 工资, 面积, 金额, (工资+金额/1000) as 实发工资 from 职工,仓库, 订购单   
    where 职工.职工号=订购单.职工号 and 职工.仓库号=仓库.仓库号  
 
 

 


 
 
 
 
 2：
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名,工资,金额 from 职工,订购单 where 姓名 like '%平%' and 职工.职工号 = 订购单.职工号 order by 工资 desc  
      
 
 

 
 3：
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名,工资,金额 from 职工,订购单 where 姓名 like '%平%' and 职工.职工号 = 订购单.职工号 order by 工资 desc, 金额 desc  
   
      
 
 

 
 
 
 
 
 
 
 4：
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名, 工资, 城市, (select AVG(工资) from 职工) as 平均工资 , (工资-(select AVG(工资) from 职工)) as 与平均工资之差  
 from 职工, 仓库 where 仓库.仓库号=职工.仓库号  
 
 


 
 
 
 
 
 
 
 5：带保存功能的多表连接查询
 
     在SQL语句中，利用into语句可以把查询到的结果保存成一张新表，然后再对新表进行数据信息查询。
 
     
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库.仓库号, 城市, 面积, 姓名, 工资, 金额 into 多表连接产生的新表 from 仓库, 职工, 订购单   
 where 仓库.仓库号=职工.仓库号 and 职工.职工号=订购单.职工号  
 
 

 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select * from 多表连接产生的新表  
 
 

 


 
 
 
 
 //查看不同仓库中的所有职工的仓库号、平均销售金额、最大销售金额、最小销售金额、最大销售金额与最小销售金额之差的信息
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号, AVG(金额) as 平均销售金额, MAX(金额) as 最大销售金额, MIN(金额) as 最小销售金额,   
 (MAX(金额) - MIN(金额)) as 最大金额与最小金额之差 from 多表连接产生的新表 group by 仓库号;  
 
 

 


 
 
 
 
 可以把分组查询结果再生成一张新表
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号, AVG(金额) as 平均销售金额, MAX(金额) as 最大销售金额, MIN(金额) as 最小销售金额,   
 (MAX(金额) - MIN(金额)) as 最大金额与最小金额之差 into 分组查询产生的新表  
  from 多表连接产生的新表 group by 仓库号;  
    
  select * from 分组查询产生的新表  
 
 
 
 
 
 6： 内连接查询(inner join)
 
     使用比较运算符对表之间的某些数据进行比较，并列出这些表中与连接条件相匹配的数据行。
 
     
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名, 城市 from 仓库 inner join 职工 on 职工.仓库号=仓库.仓库号  
 
 
 
 
 
    多表的内连接查询
 
    
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 城市,面积, 姓名, 工资, 金额 from 仓库     
   inner join 职工 on 职工.仓库号=仓库.仓库号  
   inner join 订购单 on 职工.职工号=订购单.职工号  
   and 工资>1800 and 面积<1000 and 金额 != 16600  
 
 
    
 
 
 
 7：左连接查询(left join)
 
       除满足连接条件的记录显示外，第一张表中不满足条件的记录也显示在结果集中。
 
     
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名, 城市 from 仓库   
    left join 职工 on 职工.仓库号=仓库.仓库号 and 城市 is not null and 姓名 like '%王%'  
 
 


 
 
 
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 城市, 面积, 姓名, 工资, 金额 from 仓库   
    left join 职工 on 职工.仓库号 = 仓库.仓库号  
    left join 订购单 on 职工.职工号=订购单.职工号  
    and 工资>1800 and 面积<1000 and 金额!=16600  
      
 
 

 


 
   在第一个left join左连接中，第一张表是仓库表，第二张表是职工表，在第二个left join左连接中，第一张表是职工表，第二张表是订购单表
 
 
 
 
 8：右连接查询
 
       除满足连接条件的记录显示外，第二张表中不满足条件的记录也显示在查询结果集中
 
      
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名, 城市 from 仓库   
 right join 职工 on 职工.仓库号=仓库.仓库号 where 城市 is not null and 姓名 like '%王%'  
 
 


 
   
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 城市, 面积, 姓名, 工资, 金额 from 仓库   
    right join 职工 on 职工.仓库号=仓库.仓库号  
    right join 订购单 on 职工.职工号=订购单.职工号  
    and 工资>1500 and 面积<1000 and 金额!=16600  
 
 

 


 
 
 
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 城市, 面积, 姓名, 工资, 金额 from 仓库   
    right join 职工 on 职工.仓库号=仓库.仓库号  
    right join 订购单 on 职工.职工号=订购单.职工号  
    <span style="color:#ff0000;">where</span> 工资>1500 and 面积<1000 and 金额!=16600  
 
 
把and关键字换为where关键字后的效果图，会发现那些无用的数据没有了 
 
 
 
 
 
 
 
 9：全连接查询
 
     除满足连接条件的记录显示外，两张表中的不能满足条件的记录也显示在查询结果集中
 
     
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 姓名,城市 from 仓库 full join 职工 on 职工.仓库号=仓库.仓库号 and 城市 is not null and  
 姓名 like '%王%';  
 
 

 
 
 
 
 
 
 
 
 
 
 集合的交、并、差运算查询
 
 为了进行并、交、差运算，要求运算的两个查询结果具有相同的字段个数，并且对应的字段的值要出自同一个值域，即具有相同的数据类型和取值范围
 
  10：并运算(union)
 
          主要将两个或者更多个查询的结果组合为单个结果集，该结果集包含联合查询中的全部查询的全部行
 
         
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京'  
 union  
 select 仓库号 from 职工 where 工资>2000  
 
 

 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京'  
 union  
 select 仓库号 from 职工 where 工资>2000  
   
 select distinct 仓库.仓库号 from 仓库, 职工 where 仓库.仓库号=职工.仓库号 and (城市='北京' or 工资>2000)  
    
 
 
 
 
 
使用union all 保留重复行 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京'  
 union all  
 select 仓库号 from 职工 where 工资>2000  
 
 

 

 
 
 
 
 11：交运算(intersect)
 
   可以将两个select语句的查询结果通过交运算合并成一个查询结果
 
    
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京'  
 intersect  
 select 仓库号 from 职工 where 工资>2000  
 
 

 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select distinct 仓库.仓库号 from 仓库, 职工 where 城市='北京' and 仓库.仓库号=职工.仓库号 and 工资>2000  
 
 

 
 
 
 
 
 
 
 
 
 
 12：差运算(except)
 
      可以计算两个select查询结果之间的数据差，即返回在一个查询结果中存在，但在另一个查询结果中不存在的所有行。
 
     
 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京'   
 except   
 select 仓库号 from 职工 where 工资>2900  
 
 

 
 
 
 
  
 
   [sql] 
   view plain
    copy 
   
 
   
 
   
  
 
 
 
 
 select 仓库号 from 仓库 where 城市='北京' and 仓库号 not in(select 仓库号 from 职工 where 工资>2900)  
 
 

 


 

 
 

4.1、查询的类型
 
declare @value  as int
 
set @value  = 50
 
select  'age:'as age,2008 years,@valueas va
 
--这种查询时跟表没有关系的
 
select  'age:'as age,2008 years,@valueas vafrom student
 
--这种查询涉及到表，表有多少行就查询出多少行，但是没有涉及到表的列
 
select 'age:' age,Sage,2008 years,@valueas va from Student
 
--这种是外面的列与表中的列的综合
 
 
 
4.2、单表查询 
 
4.2.1、列名的别名和表名的别名
 
select Sno, Sname,2008-Sagefrom student
 
--2008-Sage可运算，但无列名
 
select Sno xuehao, Snameas xingming,2008-Sage shengrifrom student 
 
--给列名取别名既可以加as也可以不加
 
select Sno,Sname,2008-Sageas shengrifrom studentorderby shengri--where shengri > 1983
 
--shengri字段即属性的别名是不能用来作为判断条件使用的,但是可以排序
 
--原列名既可以作为判断条件，也可以排序；列名的别名只能用作排序，不能用作判断条件
 
select Sno,lower(Sdept) sdfrom student--lower(Sdept)，但无列名
 
select 'age:' age,Sage,2008 yearsfrom Student
 
select ss.Sno, Sname,2008-Sagefrom student as sswhere ss.Sno='95001'
 
select ss.Sno, Sname,2008-Sage agefrom student ss where ss.Sno='95001'
 
select ss.Sno, Sname,2008-Sagefrom student as ssorder  by ss.Sno
 
--select ss.Sno as n, Sname ,2008-Sage from student as ss where ss.n='95001'这个SQL语句是错误的.
 
--select ss.Sno as n, Sname ,2008-Sage from student as ss order by ss.n
 
--这种排序是错误的.
 
--表名也可以有别名，给表名取别名既可以加as也可以不加；用表名或者表明的别名来引用列名，既可以排序也可以作为判断条件；用表名或者表明的别名来引用列名的别名，既不可以排序也不可以作为判断条件
 
4.2.2、消除重复行distinct
 
select distinct Snofrom SC
 
--消除取值重复的行，关键字是distinct，默认缺损值是ALL，这个是针对某一行而不是某一个字段，也就是说各行之间的各个字段完全相同才算是重复的，而不是只有某一个字段相同就算重复了
 
4.2.3、比较运算符、not运算符
 
select Sname  from Student where sdept  = 'CS'
 
select Sname  ,Sage from Student  where Sage<=20
 
select distinct Snofrom SCwhere not grade < 60
 
--比较运算符：> >= < <= = != <> !> !<
 
--not运算符：是针对某一个逻辑表达式（bool表达式）的，是对逻辑表达式取反的，而不是针对一个符号，比如grade not< 60，所以运算符NOT可以与比较运算符同用，对条件求非，比如not grade < 60
 
4.2.4、确定取值范围between...and...
 
select Sname,Sagefrom studentwhere sagenot between 22 and 20
 
--确定取值范围：between...and...，not between...and...
 
--注意：between A  and  B 这个相当于>= A  and<= B, 顺序不能错！不会对A,B的大小进行检查
 
4.2.5、确定集合:in、not in
 
select Sname,Sage,sdeptfrom studentwhere sdeptin ('CS','ma')
 
--确定集合:in,not in , in可以用= OR =等等来替代，not in 可以用！= and  ！=
 
--注意：加了括号的
 
4.2.6、模糊查询like
 
select *from studentwhere sno= '95001'
 
select *from studentwhere snolike '95001'
 
select *from studentwhere snamelike '刘%'
 
select *from studentwhere snamelike '刘_'
 
select *from studentwhere snolike '9500_'
 
select *from studentwhere snamelike '_呈%'
 
select *from studentwhere snamenot like '刘%'
 
SELECT *FROM COURSE
 
select *from coursewhere cnamelike 'DB\_M' escape  '\'
 
select *from coursewhere cnamelike 'MA\%S' escape  '\'
 
select *from coursewhere cnamelike 'MA%K__'
 
--字符匹配[not]like % _ escape
 
--当like后面的字符串没有包含通配符的时候，like就是=
 
--  %：通配符，代表任意长度的字符串包括0.
 
--  _:  代表一个字符.默认每一个字符（数值，字母，汉字）采用ascii进行编码的.但是
 
管用什么方式进行编码，_代表的是一个字符，而不是一个字节.
 
--escape 定义某一个字符，这个字符后面紧跟的通配符是不起通配作用的，当like比较的时候会忽略字符串中的escape定义某一个字符，而把其后的通配符当成一个普通的字符。
 
4.2.9、涉及空值null的查询
 
select *from studentwhere sname= ''
 
select *from studentwhere ssexis null
 
select *from studentwhere snameis not null
 
--涉及空值的查询：is not null
 
--当是空值的时候只能用IS来判断，其他的都不可以.
 
--null表示什么都没有，而''表示的是长度为零的字符串
 
--null与任何类型的值进行算术运算的结果都为null
 
4.2.10、多重条件查询and、or
 
select *  from student where sdept  = 'cs' and sage< 22
 
--多重条件查询and or
 
--and 表示的是并且，而or表示的是或者
 
--and 的优先级比or高，但是（）可以改变优先级别
 
4.2.11、对查询结果进行排序
 
select *from SCwhere cno= '3' order  by gradedesc
 
select *  from SC order by sno,grade
 
--排序order by asc/desc
 
--asc 是升序，是默认的缺损值，desc是降序的
 
--order by a，b，c，d像这种按照多个字段进行的排序，先按照第一个字段排序，再按照第二个字段排序，以此类推，但是第二个字段的排序是在第一个的基础上进行的，它不会去打乱第一个排序的，第一个排序其实就已经分成组了，第二个排序是在第一个排序的各个小组里面进行的排序.
 
4.2.12、集函数（count、avg、sum、max、min）
 
select *from student
 
select  count(*)from student
 
select  count(all sno) numfrom student
 
select  count(distinct sno) numfrom student
 
--select count(distinct *)from student--为什么*的时候不能加distinct？
 
--select count(all *)from student--为什么*的时候不能加all？
 
select  avg(distinct sage)from student--必须是数字，非null值的加和除以非null值的个数
 
select  sum(distinct sage)from student--必须是数字，非null值的加和
 
select  max(distinct sno)from student--只要是可以比较大小的就可以，如果该列全是null，那么结果也是null
 
select  min(distinct sno)from student--只要是可以比较大小的就可以，如果该列全是null，那么结果也是null
 
--对于这些集函数,dinstinct表示消除了重复的行，而默认缺损为all
 
4.2.13、分组查询group by
 
select *from sc
 
select cno  from sc group by cno
 
—-分组之后，一组中只取一个值，而不会取出多个重复的值，哪怕没有distinct也是这样的，所以如果要加其他的列，这些列在一组中要保证只有一个值。所以每一组只会有一行。
 
select cno,snofrom scgroup by cno,sno--分组是不会排序的
 
select cno,snofrom scgroup by cno,snoorderby cno
 
select cno,count(sno)from scgroup by cno
 
select sno,count(cno) cnoNumfrom scgroup by sno
 
having  count(distinct cno)>=3
 
select cno,count(sno)from scwhere cno>1groupby cno,sno 
 
having  count(sno)>0orderby cno
 
--注意上面这个sql语句的顺序,执行的顺序是先分组，然后查询计算，然后在查询
 
--的结果中判断where和having条件，最后去排序.
 
--分组与不分组的区别是：不分组的时候，（如果是多表的话，先要去连接）先查询，
 
--然后在查询结果中按照where子句的条件来筛选。而分组的时候，
 
--（如果是多表的话，先要去连接）是先分组，然后再执行查询，然后在查询结果中
 
--按照where子句和having子句的条件来筛选。Having后面的集函数是对每一最小组(按照group by后面的所有列进行的分组，是所有列而非某一列分组)的那些行进行运算的。另外，having后面也可以对某列进行非集函数的条件判断，这种判断也是针对每一最小组的那些行。
 
select top 10 a.sno,count(distinct cno) cno_num
 
from student  as a,sc as b
 
where a.sno= b.snoand a.sno ='95001'
 
group by a.sno
 
having  count(distinct cno)>= 2
 
order by a.sno
 
查询的执行步骤：先连接，然后按照条件去分组，然后按照组查询，先查询第一组（第一行），然后用where子句和having子句的条件来筛选，满足条件就保留，否则就舍去，然后查询第二行......，最后在查询出的结果中排序，然后在排好序的结果中去求top n。
 
小结：
 
1、分组是按照某个列来进行分组的，最后在该列中列值相同的就是一组了，group by a,b,c先按照a进行分组，在按照a分好的每一组里面，再按照b进行分组，即保留了最开始的组，然后在按照b分好的组里面再按照c进行分组，以此类推......
 
2、分组是不会排序的,排序和分组是两个独立的过程，排序是在分组查询之后，在最终的满足各种条件的查询结果里面来排序.
 
3、如果在分组的时候要查询某些字段，那么一定要保证在最小组中的该字段是绝对唯一的，因为每一个最小组最后只会有一行记录，有时候，用户知道是绝对的唯一的，但是计算机不知道，所以在这种情况下必要的时候可以再按照该字段进行一次分组，或者使用集函数.在使用集函数进行运算的时候，是对对最小的组进行计算.
 
4、没有分组的时候判断条件用where，分组之后对于某一组的集函数结果的判断条件是having，一般用于集函数count，分组后对于某一个字段的判断还是用where
 
2008-07-13
 
4.3、多表连接查询
 
4.3.1、连接查询概述                  
 
在关系数据库的查询中，单表查询是有很大的局限性的，因为多数情况下各个表之间都是有关联的，所以绝大多数情况下都要多表连接查询：
 
 
 
 
 
 
1、无条件连接
 
无条件连接其实就是广义笛卡尔乘积，即一个表（m行）中的每一行都与另外表（n行）中的每一行联合，最终构成了一个m*n行的一个表。
 
2、有条件连接
 
有条件连接顾名思义就是两个表之间的连接是有条件的，其中一个表的某一个字段与另外一个表中的某一个字段以某一种条件联系起来，连接的过程就是先扫面表1的第一行，去与表二中的每一行对比，满足条件的就选择出来，然后拿表一中的第二行去做相同的操作，一直到最后完成。很显然，这个连接后的表其实是广义笛卡尔乘积的子集。这种条件如果是不相等的话，那么就是非等值连接，反之就是等值连接。非等值连接用的比较少，主要是等值连接。
 
3、等值连接
 
当一个表的某一个字段与另外一个表中的某一个字段的联系条件是相等的时候，那么这种连接就是等值连接。在等值连接中，如果消除了重复的列，那么这样的等值连接就是自然连接。在自然连接中，满足条件的才会被选出来成为新的行，不满足条件的是不会被选择出来的。其实自然连接就是内连接。
 
4、外连接
 
在两个表之间，如果某一张表的行与另外一张表对应的时候，出现了根据条件去筛选另外一张表的全部行，而前一张表仍有未对应的行，那么这些行是不会成为新表中的行的，但是如果想把这些没有对应的行显示出来，就要用到外连接了。如果表一和表二连接，在表一中存在没有连接的行，表二中全部用NULL来补充，缺损的就是NULL，所以此时称为左外连接，反之则是右外连接。
 
左外连接：就是以左边的行为主，左边的全部都要显示出来，右边与之对应的要显示出来，没有与之对应的就补null。至于右边是否对应完了，不用考虑。
 
右外连接：就是以右边的行为主，右边的行要全部显示出来，左边与之对应的显示出来，没有对应的补null。至于左边是否对应完了，不用考虑。
 
4.3.2、连接查询的列名
 
对于查询时候的字段名字，某个字段如果在两个表中都是唯一的，没有重复的，那么就直接用字段名字，当然，如果用表名.列名更稳妥，但是建议只用字段名；对于那些在两个表中有相同的字段的，那么一定要用表名.列名，此时的列名一般就是按照例名来编号，这样显然不好，一般要用别名比较好，那么这样处理后在应用程序中引用的时候就非常方便，也不会出错了。
 
注意：只要使用了别名，显示出来的就是别名，而且应用程序中也是要通过这个别名来引用才不会出错的。使用函数的时候一般也要用到别名。
 
4.3.3、两张表以上的连接查询
 
对于两张表以上的内连接或者外连接，可以认为先让前面两张表连接起来，构成一个记录集，然后让把这个记录集当成新的表与后面的表相连接。
 
4.3.4、连接查询实例
 
4.3.4.1、内连接查询实例
 
方法一：用 =号内连接
 
use zwj_study
 
select *from student
 
select *from sc
 
select *from course
 
select student.*,sc.*,sc.Gradefrom student,scwhere student.sno= sc.sno
 
--表名.*表示的是选择了这个表中的所有列，所有列名都用了表名.列名
 
select student.*,course.*from student,sc,course
 
where student.sno= sc.snoand sc.cno = course.cno
 
--不管最终查询的列是否在某个表中，但是只要这个查询的条件中涉及到了这张表，那么就一定要在from中添加进来这张表
 
 
 
方法二：用 inner join内连接
 
select student.*,sc.*from studentinnerjoin sc on student.sno= sc.sno
 
select student.*,sc.*,course.*from
 
student inner  join sc on student.sno= sc.sno
 
inner join courseon sc.cno= course.cno
 
--以上是内连接的第二种写法.这个连接的过程是前面两张表先连接好了，然后再来连接第三张表。连接的条件已经在from里面了．当然还可以用where来添加新的条件
 
 
 
 
 
--select student.* ,sc.* from student inner join sc
 
--where  student.sno = sc.sno
 
--
 
--select student.*,sc.*,course.* from student inner join sc inner join course
 
--where  student.sno = sc.sno and sc.cno = course.cno
 
--
 
--以上的这两种写法是完全错误的，不能个这样写.
 
 
 
4.3.4.2、外连接查询实例
 
select student.*,sc.*
 
FROM   Student  left outer JOIN SCON Student.Sno= SC.Sno
 
Where ……
 
select student.*,sc.*
 
FROM   Student  right outer JOIN SCON Student.Sno= SC.Sno
 
 
 
select student.*,sc.*,course.*
 
FROM   Student  left outer JOIN SCON Student.Sno= SC.Sno
 
       right  outer join course  on SC.cno = course.cno
 
 
 
--连接的条件已经在from里面了．当然还可以用where来添加新的条件．
 
 
 
--select student.* ,sc.*
 
--FROM   Student left outer JOIN SC where Student.Sno = SC.Sno
 
--
 
--select student.* ,sc.*,course.*
 
--FROM   Student left outer JOIN SC
 
--       right outer join course
 
--where  Student.Sno = SC.Sno and SC.cno = course.cno
 
--
 
--以上这两种方式是错误的
 
 
 
 
 
4.3.4.3、内外混合连接查询实例
 
select student.*,sc.*,course.*
 
FROM   Student  left outer JOIN SCON Student.Sno= SC.Sno
 
       inner  join course on SC.cno= course.cno
 
2008-07-14
 
4.4、嵌套查询
 
在关系数据库的查询中，单表查询是有很大的局限性的，因为多数情况下各个表之间都是有关联的，所以绝大多数情况下都要多表连接查询，多表连接查询只有一个select，但是有时候也用嵌套查询来代替连接查询，使得查询之间的层次关系更加清晰。所谓嵌套查询，就是在一个查询语句中出现了多个select表达式。
 
4.4.1、子查询作为主查询的条件
 
4.4.1.1、带IN谓词的子查询
 
select s1.*,s2.*from studentas s1,studentas s2
 
where  s1.sdept= s2.sdeptand s1.sname ='刘呈'--与刘呈同系（单表连接）
 
 
 
select s1.*from studentas s1,studentas s2
 
where  s1.sdept= s2.sdeptand s2.sname ='刘呈'--与刘呈同系（单表连接）
 
 
 
select *from student
 
where sdept  in (select sdeptfrom studentwhere sname= '刘呈')--与刘呈同系
 
 
 
select *from student
 
where sdept  in (select sdeptfrom studentwhere sname=
 
(select snamefrom studentwhere sno= '95002')
 
)
 
 
 
select student.sno,snamefrom student,sc,course
 
where student.sno= sc.snoand sc.cno = course.cno
 
 and cname  = '信息系统'--选修了信息系统
 
 
 
select sno,snamefrom student
 
where sno  in
 
(select snofrom sc
 
where cno  in
 
(select cnofrom course
 
where cname  = '信息系统')
 
)--选修了信息系统
 
 
 
select sno,snamefrom student
 
where sno  in
 
(select snofrom sc
 
where cno  in
 
(select cnofrom course
 
where cname  = '信息系统'
 
)
 
)and ssex  = '女'--选修了信息系统,而且性别为女
 
小结：
 
1、嵌套查询中的IN,表示某一个字段在某一个集合里面，集合中可能有多个也可能只有一个值，但是只会有一个字段
 
2、任何一个子查询都是select块，一定要加上（），否则就会报错.
 
3、任何一个子查询都不能用order by来排序，只能对最终的查询结果进行排序
 
4、子查询的结果只是作为父查询的某一个条件而已，还可以用AND,OR等追加更多的条件
 
 
 
4.4.1.2、带比较运算符(>,>=,<,<=,=,!=,<>)的子查询
 
select *from student
 
where sdept  = (select sdeptfrom studentwhere sname= '刘呈')
 
--当确定了一个子查询的结果是单值的时候，父查询的查询条件就可以使用比较运算符，子查
 
询必须在比较运算符的后面，而不能是前面
 
 
 
4.4.1.3、exists谓词
 
if notexists(select sdeptfrom studentwhere sage>=30)
 
select *from student
 
if exists(select*from student where sage<30)
 
select *from student
 
--exists，not exists只能针对一个select语句，它返回的是一个逻辑值：true或者false，它主要应用于if语句的判断的，特别是在插入某张表之前来判断某条记录是否存在的，它还可以作为where判断条件
 
 
 
小结：以上的子查询作为主查询的判断条件，有些时候，子查询与主查询时关联的，有时候是不关联的。
 
 
 
4.4.2、子查询作为主查询的列
 
4.4.2.1、case表达式作为主查询的列
 
select sno,cno,grade,
 
(case
 
when sno  = (select snofrom studentwhere sname= 'james')
 
then 'james'else'others' end)as name
 
from sc 
 
--上面这个查询是一个不相关子查询，即子查询与父查询之间没有关联，执行的步骤是先查询第一行的sno，cno，grade，case语句，然后查询第二行的sno，cno，grade，case，第三行......
 
--case是一个表达式，它会返回单值
 
 
 
select sno,cno,grade,
 
(case
 
when sno  = (select snofrom studentwhere sno= sc.sno)
 
then 'one'else'others' end)as name
 
from sc
 
--上面这个查询是一个相关子查询，即子查询与父查询之间有关联，执行的步骤是先查询第一行的sno，cno，grade，case语句，然后查询第二行的sno，cno，grade，case，第三行......
 
--case是一个表达式，它会返回单值
 
4.4.2.2、查询表达式作为主查询的列
 
select sno,cno,grade,(select snamefrom student where sno= sc.sno )as  name from sc
 
--当子查询的结果只有一行一列的时候，还可以直接将子查询作为主查询的列，作为主查询的列
 
--子查询与父查询相关联
 
 
 
小结：以上两种查询的列都出现了特殊的列，而不是一个普通的列，这个特殊列就是一个表达式，这个表达式永远都只会返回一行一列，即返回单值。每查询一行都会执行一次特殊列表达式，得到一个列出来。子查询与主查询可能是关联的，也可能是非关联的。
 
 
 
4.4.3、子查询作为主查询的from对象
 
select a.sno,sname, gradefrom
 
student a ,(select sno,cno,gradefrom sc) b
 
where a.sno= b.sno
 
 
 
select  count(*) from
 
(select clsbdh,count(*)as su from z_hgzzzgroupby clsbdh having count(*)> 1) a
 
--在from中使用子查询，子查询的结果就是一个新的表，一定要给新表加一个别名，否则会出错。这种在from中使用子查询，是把子查询作为from的对象了，这种子查询与主查询是永远不会有关联的，否则就不要把子查询作为主查询的from对象了。
 
 
 
4.5、关联子查询
 
子查询的查询条件不依赖于父查询时，我们称之为不相关子查询，反之，则成为相关子查询。相关子查询只会发生在嵌套查询中，但是在嵌套查询中的一种情况下是不会发生相关子查询的，这就是子查询作为主查询的from对象的时候。
 
下面以子查询作为主查询的where条件来讲解关联子查询，关联子查询的执行过程如下，每一个子查询被外部查询的每一行执行一次：
 
 
 
例1：
 
 
 
 
执行过程：先查询第一行，然后执行where的判断，如果满足条件，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的outer.department_id就是父查询中这一行的department_id的值，然后查询第二行、第三行......
 
 
 
例2：
 
 
执行过程：先查询第一行，然后执行where的判断，如果满足条件，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的e.employee_id就是父查询中这一行的employee_id的值，然后查询第二行、第三行......
 
 
 
 
 
例3：
 
 
执行过程：先查询第一行，然后执行where的判断，如果子查询查出来确实存在，那么exist就会返回true，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的outer.employee_id就是父查询中这一行的employee_id的值，然后查询第二行、第三行......
 
 
 
例4：
 
 
执行过程：先查询第一行，然后执行where的判断，如果子查询查出来确实不存在，那么not exist就会返回true，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的d.employee_id就是父查询中这一行的employee_id的值，然后查询第二行、第三行......
 
2008-07-20
 
4.6、拼sql字符串的查询
 
在存储过程中，有些时候，要动态的查询一些列，动态的变换条件，那么就要拼sql语句：
 
    declare @sqlSelect  as varchar(5000)
 
    set @sqlSelect  = 'select 1,1,1,null from #zwj1'
 
拼完之后，可以用exec命令去执行，但是执行结果是一个记录集，哪怕只有一个值也是记录集，执行的结果只能用一种方式取得，即定义一个与查询结果相同的表，然后将结果插入到表中，该表可以是临时表也可以不是，这个表一定要之前就定义好，列固定：
 
insert into zwjexec(@sqlSelect)
 
以下的用法都是错误的：
 
select *fromexec('select 1,1,1,null from #zwj1')
 
select *into #vfrom exec('select 1,1,1,null from #zwj1')
 
declare @k  as int
 
set @k =exec('select count(*) from #zwj1')
 
update zwj  set s = exec('select count(*) from #zwj1')
 
 
 
4.7、交叉查询
 
在存储过程中，有时候想把记录集的行变成列查询出来，那么就要用到交叉查询，下面举例说明交叉查询：
 
例一：使用case表达式来实现交叉查询：
 
每个行列转换，都要经过三个逻辑处理阶段，扩展阶段是新增多个列，这样将导致新增了一些行；分组阶段将按照未转换的列分组；聚合阶段将对每个组进行处理，从而实现行的减少得到最终的结果。
 
以下是一张记录表，phaseType这一列只有四个值，phase1、phase2、phase3、phase4，现在要把phaseValue这一列分成phase1、phase2、phase3、phase4四列的形式显示出来：
 
 
那么就要交叉查询：
 
select dmcode,klcode,phaseType,
 
case when phaseType='phase1'then phasevalueelse 0end phase1 ,
 
case when phaseType='phase2'then phasevalueelse 0end phase2 ,
 
case when phaseType='phase3'then phasevalueelse 0end phase3,
 
case when phaseType='phase4'then phasevalueelse 0end phase4
 
from PhaseInfo
 
查询结果如下：
 
 
 
 
对以上查询结果再进行一次转换：
 
select dmcode,klcode,
 
sum(phase1) phase1,sum(phase2) phase2,
 
sum(phase3) phase3,sum(phase4) phase4
 
from 
 
(
 
select dmcode,klcode,phaseType,
 
case when phaseType='phase1'then phasevalueelse 0end phase1 ,
 
case when phaseType='phase2'then phasevalueelse 0end phase2 ,
 
case when phaseType='phase3'then phasevalueelse 0end phase3,
 
case when phaseType='phase4'then phasevalueelse 0end phase4
 
from PhaseInfo
 
) A
 
group by dmcode,klcode
 
order by dmcode,klcode
 
查询结果如下：
 
 
 
 
2010-01-22