transact---sql高级查询 (转贴)

transact---sql高级查询（上）
1:多表的查询和笛卡儿积
2:表格别名的用法
3:使用sql server的统计函数
4:用group by子句实现分组的查询

A:多表查询和笛尔儿积
到目前为止,我们所用的都是单个表查询,但是在更多的情况的下,需要对多个表进行同时查询,这时可以把多个表的名字全部填写在from子句中.
  比如:查询出每个职工的姓名,学历,所在部门名称.由于我们需要的结果来自于两个表,所以必须用多表查询
       select 姓名,学历,部门名称,负责人 from work,部门 [分析为什么是错误的]
  原因:问题出在对表格连接条件的限制上.在上面的语句中,没能对表格连接条件作任何限制,所以sql会在work表中每取出一条记录,就与部门表中的所有记录组合一次,那么假设work表有m条记录,而部门表中有n条记录,则得出的结果为m*n条记录这就是笛尔儿积,所以笛尔儿积返回的大多数的结果是冗余的、无用的,所以应该避免笛尔儿积的产生.
  解决笛尔儿积的方法:事实上由于笛尔儿积是因为两个表的连接条件没有限制造成的,所以只要我们对两个表的连接进行条件限制,就可以避免笛尔儿积的产生.可以通过一个where子句,来连接两个表的公共的字段就可以了.
  所以将上面的语句改成:select 姓名,学历,部门名称,负责人 from work,部门 where work.部门编号=部门.部门编号

B:使用表格的别名
  A:当使用多个表进行查询时,如果有两个表中有相同的列,应该指明选中的是哪个表中的列.
    比如:在work表检索出在address表中都有的职工的职工号,姓名,学历,基本工资
         select 职工号,姓名,学历,基本工资 from work,address where work.职工号=address.职工号
         上面的语句是错误的,原因是对于work和address表都有职工号,姓名列,所以应该指明是哪个表的职工号和姓名.
    改成:select work.职工号,work.姓名,学历,基本工资 from work,address where work.职工号=address.职工号
    或者:select address.职工号,address.姓名,学历,基本工资 from work,address where work.职工号=address.职工号
    想一想:为什么对于学历,基本工资没有指明表名:即:work.学历,work.基本工资[只有一个表有这些列]
  B:允许使用别名来访问表.
    格式:1:表名 as 别名
         2:表名 别名
    例如:上面的语句可改写成:
         select w.职工号,w.姓名,学历,基本工资 from work as w,address as a where w.职工号=a.职工号 
上面的语句中在from中引用两个表,并且为表work指明了别名w,为表address指明了别名a,所以就可以用w来代表work表,用a来代表address表.或者省略as直接改成:select w.职工号,w.姓名,学历,基本工资 from work w,address a where w.职工号=a.职工号 
  C:如果使用了别名,则以后所有查询语句中,都必须使用别名列
    比如:select work.职工号,work.姓名,学历,基本工资 from work w,address a where w.职工号=a.职工号 [是错误的]

C:使用统计函数:
  sql跟我们提供了以下几个统计函数:
     sum:返回一个数字列的总和
     avg:对一个数字列求平均值
     min:对一个数字列求最小值
     max:对一个数字列求最大值
     count:返回满足select语句中指定的条件的记录个数
      举列:1:求出work表中所有男职工的基本工资的和
             select sum(基本工资) as 性别为男的基本工资 from work where 性别=\'男\' 
           2:求出work表中所有职称是经理的最高工资和最低工资,平均工资
             select max(基本工资) as 最高工资,min(基本工资) as 最低工资,avg(平均工资) as 平均工资 from work    
           3:与统计函数一起使用distinct关键字[通常只与count函数使用]
             例:1:检索出work表中学历的个数
                  select count(学历) from work 
                2:检索出work表中学历的种类的个数
                  select count(distinct 学历) from work
                  试一试:select distinct count(学历) from work 可行否?
                3:有work和部门表,检索出在销售部工作的员工的个数
                  select \'销售部的人数\'=count(职工号) from work a,部门 b 
                  where a.部门编号=b.部门编号 and b.部门名称=\'销售部\'
                4:在work表中检索出其基本工资小于职工平均工资的人数
                  select count(职工号) as 人数 from work 
                  where 基本工资<(select avg(基本工资) from work)
                5:有学科表和学费表,从学费表检索出有多少个学网页设计的人
                  select count(学号) as 网页设计的人数 from 学费 a,学科 b 
                  where a.所学专业代号=b.课程编号 and b.课程名称=\'网页设计\'

D:使用group by子句对结果进行分类[只用于统计函数] 
    举列:1:检索出work表各职称的人数.
           select 职称,count(职称) as 职称人数 from work group by 职称
         2:检索出各学历的平均工资.
           select 学历,avg(基本工资) from work group by 学历
         3:有学科表和学费表,要求统计出各学科的学生数目.
           select 所学专业代号,count(所学专业代号) as 人数 into #abc from 学费 group by 所学专业代号
           select 课程名称,人数 from #abc,学科 where 所学专业代号=课程编号
         4:有职工表和商品销售表,要求检索出每个职工的职工号,姓名,销售总量.
           select 职工号,sum(销售量) as 销售总量 into #abcd from 商品销售 group by 职工号
           select 职工.职工号,姓名,销售总量 from 职工,#abcd where #abcd.职工号=职工.职工号
         5:查询出每个部门最高的基本工资,显示部门名称和最高基本工资
           select 部门名称,max(基本工资) from work group by 部门名称
           说明:1:在group by中不支持对列名的分配的别名
                   select 学历 as 职工学历,count(学历) from work group by 职工学历 [错错] 
                   改为:select 学历 as 职工学历,count(学历) from work group by 学历
                2:select后面每一列数据除了在统计函数中的列以外都必须在group by子句出现 
                   比如:select 学历,性别,sum(基本工资) from work group by 学历[错错] 
                   改为:select 学历,性别,sum(基本工资) from work group by 学历,性别      
                   意义:各学历各性别的基本工资之和

transact---sql高级查询（下）
5:使用having关键字来筛选结果
6:使用compute和compute by子句
7:使用嵌套查询
8:分布式查询

E:使用having关键字来筛选结果
    当完成对数据结果的查询和统计后,可以使用having关键字来对查询和计算的结果进行一步的筛选
      例:检索出work表中学历是大专或者是中专的人数
          select 学历,count(学历) from work group by 学历 having 学历 in(\'大专\',\'中专\')
          说明:1:having关键字都与group by用在一起.
               2:having不支持对列分配的别名
          例如:select 学历,\'大于5的人数\'=count(学历) from work group by 学历 having 大于5的人数>5 [错错]
          改为:select 学历,\'大于5的人数\'=count(学历) from work group by 学历 having count(学历)>5

F:使用compute和compute by
  使用compute子句允许同时观察查询所得到各列的数据的细节以及统计各列数据所产生的汇总列
      select * from work [查询所得到的各列的数据的细节]
      compute max(基本工资),min(基本工资) [统计之后的结果]
  这个例子中没有使用by关键字,返回的结果是最后添加了一行基本工资的最大值和最小值,也可增加by关键字.
        例:select * from work order by 学历
           compute max(基本工资),min(基本工资) by 学历
        比较:select 学历,max(基本工资),min(基本工资) from work group by 学历
        说明:1:compute子句必须与order by子句用在一起
             2:compute子句可以返回多种结果集.一种是体现数据细节的数据集,可以按分类要求进行正确的分类；另一种在分类的基础上进行汇总产生结果.
             3:而group by子句对每一类数据分类之后只能产生一个结果,不能知道细节

G:使用嵌套查询
  查询中再查询,通常是以一个查询作为条件来供另一个查询使用
      例:有work表和部门表
         A:检索出在部门表中登记的所有部门的职工基本资料
           select * from work where 部门编号 in [not in](select 部门编号 from dbo.部门) 
         B:检索出在work表中每一个部门的最高基本工资的职工资料
           select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称) 
           说明:由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.
           相当于:select * from work,(select 部门名称,max(基本工资) as 基本工资 from work group by 部门名称 as t) where work.基本工资=t.基本工资 and work.部门名称=t.部门名称
         C:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料 
           select * from 嵌套work where 职工号 in (select 职工号 from 嵌套部门) and 姓名 in (select 姓名 from 嵌套部门) [察看结果,分析原因] 
           改:select * from 嵌套work a,嵌套部门 b where a.职工号=b.职工号 and a.姓名=b.姓名 
           改:select * from 嵌套work where 职工号=(select 职工号 from 嵌套部门) and 姓名=(select 姓名 from 嵌套部门) [行吗?为什么,分析原因?] 

在嵌套中使用exists关键字[存在]
例:1:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料 
     select * from 嵌套work a where exists (select * from 嵌套部门 b where a.姓名=b.姓名 and a.职工号=b.职工号)
   2:在work表检索出在部门表没有的职工
     select * from work where not exists (select * from 部门 where 部门.部门编号=work.部门编号)
     能否改成:select * from work where exists (select * from 部门 where 部门.部门编号<>work.部门编号)
                       
在列清单中使用select
例:1:在work1表和部门表中检索出所有部门的部门名称和基本工资总和
    select 部门名称,(select sum(基本工资) from work1 b where a.部门编号=b.部门编号) from 部门 a
   2:检索各部门的职工人数
    select 部门编号,部门名称,(select count(职工号) from work1 a where a.部门编号=b.部门编号) as 人数 from 部门 b
   3:在商品表和销售表中查询每一职工的姓名,所属部门,销售总量                   
    select 姓名,所属部门,(select sum(销售量) from 商品销售 a where a.职工号=b.职工号) as 销售总量 from 嵌套部门 b

H:分布式查询
我们以前的查询都只是基于一个服务器中的一个数据库的查询,如果一个查询是要跨越一个服务器,像这样的查询就是分布式查询,那么我们以看到分布查询就是数据源自于两个服务器.要进行分布式查询必须先创建一个“链接服务器”,以便让本地的用户能够映射到过程服务器.
“链接服务器”的创立
     A:在“链接服务器”里面输入以后为了方便访问该链接服务器的名称[任意]
     B:在“提供程序名称”里面选择“Microsoft OLE DB Provider for SQL Server”
     C:在“数据源”里面输入服务器的网络名
     D:本地登录,远程用户和远程密码里面分别输入一个本地登录用户,远程登录和远程密码以便让本地SQL Server登录映射为链接服务器上的用户
     E:访问方法:格式:链接服务器的名称.数据库名.dbo.表名
       链接服务器有两个特点:
           1:通过链接服务器不能删除链接源服务器的任何对像.
           2:能过链接服务器可以对链接源服务器的表进行insert,updae,delete操作.

转载于:https://www.cnblogs.com/raymond19840709/archive/2008/02/26/1082357.html