数据库总结［之二］(SQLServer)

 

15．查询语句的统计功能：

    用户经常需要对查询结果集进行统计，例如求和、平均值、最大值、最小值和个数等，这些统计可以通过以下3种方法实现。

1）使用聚合函数。

 

函数名	语法格式	功能说明
AVG	AVG([ALL|DISTINCT] 列名)	计算一个数值列的平均值
SUM	SUM([ALL|DISTINCT] 列名)	计算一个数值列的总和
MAX	MAX([ALL|DISTINCT] 列名)	返回指定列中的最大值
MIN	MIN([ALL|DISTINCT] 列名)	返回指定列中的最小值
COUNT	COUNT([ALL|DISTINCT] 列名|*)	统计查询结果集中记录的个数

 

上表语法格式中的DISTINCT表示去掉指定列中的重复值，ALL表示不取消重复值，默认是ALL

select sumage=sum(mage),avgmage=avg(mage),maxage=max(mage),

minage=min(mage),总人数=count(mid) from mytable

 

2）使用group by子句。

   group by按“列名”指定的列进行分组，将该列列值相同的记录组成一组，对每一组进行汇总计算。每一组生成一条记录。若有“having 条件表达式”选项，则表示对生成的组进行筛选。

   select msex,总人数=count(msex) from mytable where msex='男' group by msex

   select msex,总人数=count(msex) from mytable group by msex having msex='男'

 

3）使用compute子句。

   compute子句对查询结果集进行汇总统计，并显示参加汇总记录的详细信息。

       compute by子句与group by子句的功能类似，都可以对查询结果集进行分组统计，不同的是，compute  by子句不仅显示汇总数据，还分组显示参加汇总的记录的详细信息，而group by子句仅显示汇总数据。

       select * from mytable order by mclass compute count(mclass) by mclass

 

16.数据的连接：检索多个表

   1）交叉联接：cross join

      交叉联接又称非限制联接（广义笛卡尔积），它将两个表不加任何约束地组合在一起，也就是将第一个表的所有记录分别与第二个表的每条记录拼接组成新记录，联接后结果集的行数就是两个表的行的乘积，结果集的列数就是两个表的列数之和。

      交叉联接有以下两种语法格式：

 

1））select 列名列表 from 表名1 cross join 表名2

2））select 列名列表 from 表名l，表名2

     create table t1(

读者编号 int,

姓名 varchar(10))

 

create table t2(

读者编号 int,

已借册数 int)

 

insert into t1 values(001,'张')

insert into t1 values(002,'王')

insert into t1 values(003,'李')

 

insert into t2 values(002,3)

insert into t2 values(003,8)

insert into t2 values(004,5)

 

select * from t1,t2

select * from t1 cross join t2

        在实际应用中，使用交叉联接产生的结果集一般没有什么意义，但在数据库的数学模式上有重要的作用。

2）内联接 :inner join

   内联接是将两个表中满足联接条件的记录组合在一起。在实际应用中，联接条件通常采用“on 主键=外键”的形式。

 

   内联接有以下两种语法格式。

第一种格式：select列名列表from 表名l [inner] join 表名2 on 表名1.列名=表名2.列名

第二种格式：select列名列表 from 表名l,表名2 where 表名1.列名=表名2.列名

 

含有重复列：

select * from t1,t2 where t1.读者编号=t2.读者编号

select * from t1 inner join t2 on t1.读者编号=t2.读者编号

select * from t1 join t2 on t1.读者编号=t2.读者编号

 

不含重复列：

select t1.读者编号,姓名,已借册数 from t1,t2 where t1.读者编号=t2.读者编号

select t1.读者编号,姓名,已借册数 from t1 inner join t2 on

t1.读者编号=t2.读者编号

select t1.读者编号,姓名,已借册数 from t1 join t2 on t1.读者编号=t2.读者编号

 

还可设置新的表名后再查询：

select a.读者编号,姓名,已借册数 from t1 as a join t2 as b on

a.读者编号=b.读者编号 （as可省略，见下面那条语句）

select a.读者编号,姓名,已借册数 from t1 a join t2 b on

a.读者编号=b.读者编号

 

3）外联接：outer join

外联接又分为左外联接、右外联接、全外联接3种。外联接的结果集中不但包含满足联接条件的记录，还包含相应表中的不满足联接条件的记录。

1））左外联接：

左外联接的语法格式为：

select 列名列表 from 表名l left [outer] join 表名2 on

 表名1.列名=表名2.列名

 

     左外联接的结果集中包括了左表的所有记录，而不仅仅是满足联接条件的记录。如果左表的某记录在右表中没有匹配行，则该记录在结果集行中属于右表的相应列值均为null.

     select * from t1  a left outer join t2  b on a.读者编号=b.读者编号

2））右外联接:

右外联接的语法格式为：

select 列名列表 from 表名l right [outer] join 表名2 on

 表名1.列名=表名2.列名

    

     右外联接的结果集中包括了右表的所有记录，而不仅仅是满足联接条件的记录。如果右表的某记录在左表中没有匹配行，则该记录在结果集行中属于左表的相应列值均为null.

     select * from t1  a right outer join t2  b on a.读者编号=b.读者编号

3））全外联接 :

全外联接的语法格式为：

select 列名列表 from 表名l full [outer] join 表名2 on

 表名1.列名=表名2.列名

 

        全外联接的结果集中包括了左表和右表的所有记录。当某记录在另一个表中没有匹配记录时，则另一个表的相应列值为null.

        select * from t1 a full outer join t2 b on a.读者编号=b.读者编号

4）自联接 :join

   自联接就是将一个表与它自身进行联接，可看作一个表中的两个副本之间的内联接。若要在一个表中查找具有相同列值的行，则可以使用自联接。使用自联接时，必须为表指定两个不同的别名，使之在逻辑上成为两个表。

create table t(

tid int identity(1,1) primary key,

tname varchar(10),

tclass varchar(30),

tage int)

 

insert into t values('A','2',20)

insert into t values('B','1',25)

insert into t values('E','3',23)

insert into t values('F','4',28)

 

insert into t values('A','1',28)

insert into t values('B','2',27)

insert into t values('C','1',20)

insert into t values('D','2',20)

 

select a.tname,a.tclass,a.tage from t a join t b on a.tname=b.tname

where a.tclass!=b.tclass

 

5）合并结果集 :union

合并结果集的基本规则如下:

1))union合并的各结果集的列数必须相同，对应的数据类型也必须兼容。

2))默认情况下系统将自动去掉合并后的结果集中重复的行。

3))最后结果集中的列名来自第一个select语句。

union操作常用于归档数据，例如归档月报表为年报表，归档各部门数据等。

注意union还可以与order by、group by子句一起使用，用来对合并所得的结果集进行分组或排序。

 

17.子查询：

   1）相关子查询：

          在相关子查询中，子查询的执行依赖于外部查询，多数情况下是在子查询的WHERE子句中引用了外部查询的表。相关子查询的执行过程与前面所讲的查询完全不同，前面介绍的子查询在整个查询过程中只执行一次，而相关子查询中的子查询需要重复地执行。

          相关子查询的执行过程是：子查询为外部查询的每一行执行一次，外部查询将子查询引用的外部字段的值传给子查询，进行子查询操作；外部查询根据子查询得到的结果或结果集返回满足条件的结果行；外部表的每一行都将做相同的处理。

      select tage from t a where tage>(select avg(tage) from t)

      1））在子查询中，还可以通过运算符EXISTS来判断子查询的结果是否为空表。如果子查询的结果集不为空，EXISTS返回TRUE；否则返回FALSE。使用NOT EXIST时其返回值与EXISTS刚好相反。

           select * from t a where exists (select * from t b

where a.tage=b.tage and b.tage>25)

 

           select * from t a where tage in (select tage from t b where b.tage>25)

 

               另外，使用联接也可以实现上例中的查询，而且联接还可以同时显示来自多个表中的字段。

           select distinct a.tid,a.tname,a.tclass,a.tage from t a join t b on a.tage=b.tage where b.tage>25

 

      2))联接和子查询可能都要涉及两个或多个表，它们之间的区别如下:

联接可以合并两个或多个表中的数据，而带子查询的select语句的结果只能来自一个表，子查询的结果只是用来作为选择结果数据时进行参照的。

                有的查询既可以使用子查询来表达，也可以使用联接表达。通常使用子查询表示时可以将一个复杂的查询分解为一系列的逻辑步骤，条理清晰，而使用联接表示有执行速度快的优点。因此，在实际应用中，读者应根据具体情况来决定使用哪种方法。

   2）嵌套子查询

          嵌套子查询的执行不依赖于外部查询。这类子查询的执行过程是：首先执行子查询，子查询得到的结果不被显示出来，而是传递给外部查询，作为外部查询的条件来使用，然后执行外部查询，并显示整个查询结果。

      嵌套子查询一般可分为两种：

1))返回单个值 :

   该单值常被外部查询用来进行比较操作。

   select tage from t a where tage>(select avg(tage) from t)

2))返回一个值列表

             如果子查询返回一个值列表，则该列表常和in、not in、any和all逻辑运算符一起构成外部查询的查询条件。

1))）in和not in 运算符

         IN和NOT IN运算符用来将一个表达式的值与子查询返回的一列值进行比较。使用IN运算符时，如果该表达式的值与此列中的任何一个相等，则IN测试返回TRUE；如果该表达式的值与此列中的任何一个值都不相等，则返回FALSE。使用NOT IN时结果相反。

     select * from t a where tage in (select tage from t b where b.tage>25)

        2))）any运算符

                 ANY运算符通过比较运算符将一个表达式的值与子查询返回的一列值中的每一个进行比较。只要有一次比较的结果为TRUE，则ANY测试返回TRUE；若每一次比较的结果均为FALSE，则ANY测试返回FALSE。

                 如表达式“>ANY(1,2,3)”与“>1”等价。由于比任何一个数大表达式就成立，所以只要比最小数大即可。

再如，表达式“〈ANY(1,2,3)”与“〈3”等价。由于比任何一个数小表达式就成立，所以只要比最大数小即可。

             select * from t a where tage >any(select tage from t b)

        3)))all运算符

                ALL运算符通过比较运算符将一个表达式的值与子查询返回的一列值中的每一个进行比较。若每一次比较的结果均为TRUE，则ALL测试返回TRUE；只要有一次比较的结果为FALSE，则ALL测试返回FALSE。

               如表达式“>ALL(1,2,3)”与“>3”等价。由于比所有数都大表达式才成立，所以只要比最大数大即可。

再如，表达式“〈ALL(1,2,3)”与“〈1”等价。由于比所有数都小表达式才成立，所以只要比最小数小即可。

            select * from t a where tage <=all(select tage from t b)