郭德纲是第几代:查询数据库语句

一、Select语句：67页

    select语句除了可以查看数据库中的表格和视图的信息外,还可以查看SQL Server的系统信息、复制、创建数据表，其查询功能强大，是SQL语言的灵魂语句，也是SQL中使用频率最高的语句。

基本select语句：

   一个基本的select语句可以分解成三个部分：查找什么数据、从哪里查找、查找的条件是什么。
                                            
                                           select  ...... from...... where.......

select 语句的一般格式如下：

select  <目标列表达式列表>     
 [into 新表名]             
 from  表名或视图名     
 [where <条件>]                    
 [group by <分组表达式>]             
 [having <条件>]                
 [order by <排序表达式>[ASC|DESC]]    

二、单表查询：指仅涉及一个表的查询)(P67：基本查询)

(一)查询指定的列
  
1.查询表中所有列：在select语句指定列的位置上使用*号时，表示查询表的所有列。

    格式：select * from 表名

例1.在xs表中查询全体学生的信息。 

use xsbook           
 
select * from xs

2.查询表中指定的列。查询多列时，列名之间要用逗号隔开。

  格式：  select 列名1[,列名2,...]
     from 表名
 
例2.在xsbook数据库的xs表中查询学生的姓名、专业名、借书数信息。
   
select 姓名,专业名,借书数
    from xs
go

3.指定查询结果中的列标题

    通过指定列标题（也叫列别名）可使输出结果更容易被人理解。指定列标题时，可在列名之后使用AS子句；也可以使用:列别名=<表达式>的形式指定列标题。

    AS子句的格式为：列名或计算表达式  [AS]  列标题  

    其中：AS可省略。

例3：给列加显示标题示例：

select 借书证号 as cardno,姓名 as name, 借书数 as cnt
    from xs
go
           
   或采用列别名=<表达式>的形式指定列标题，如上例可改为：

select cardno=借书证号,name=姓名,cnt=借书数
   from xs
go
                                               
                                                               
4.查询经过计算的列（即表达式的值）：

    使用select对列进行查询时，不仅可以直接以列的原始值作为结果，而且还可以将列值进行计算后所得值作为查询结果，即select子句可以查询表达式的值,表达式可由列名、常量及算术运算符组成。查询结果计算列显示“无列名”,一般要给计算列加列标题。

    其中：表达式中可以使用的运算符有：加+、减-、乘*、除/、取余%
  
例4：查询所有书名、单价及8折之后的图书价格

select 书名,单价,八折=单价*0.8
    from book
go
 
(二)、选择行：选择表中的部分行或全部行作为查询的结果：(P72:筛选查询)

格式: select [all|distinct] [top n[percent]]<目标列表达式列表>
          from 表名

1. 消除查询结果中的重复行

    对于关系数据库来说，表中的每一行都必须是不同的(即无重复行)。但当对表进行查询时若只选择其中的某些列，查询结果中就可能会出现重复行。在select语句中使用distinct关键字可以消除结果集中的重复行，其格式为：

    select distinct 列名1[,列名2,...]   from 表名

    其中：select语句中使用distinct的含义是对结果集中的重复行只选择一个，以保证行的唯一性(注意：强调的是“行”，而不是某一列）。

例5：查询所有专业名

select distinct 专业名
   from xs
go

注意：与使用Distinct关键字相反，当使用关键字All时，将保留结果集中的所有行(默认值为All)

例如：select all 姓名,性别
          from xs

2. 限制查询结果中的返回行数

    使用top选项可限制查询结果的返回行数，即返回指定个数的记录数。
 
    其中：n是一个正整数，表示返回查询结果集的前n行；若带percent关键字，则表示返回结果集的前n%行。

例6：查询xs表的前5个记录信息

select top 5 * from xs
  
再如：select top 50 percent * from xs   --查询前50%的学生信息

(三)查询满足条件的行: 用where子句实现条件查询:(P70:where条件查询)

    通过where子句实现,该子句必须紧跟在From子句之后。

    格式为：

     select [all|distinct] [top n[percent]]<目标列表达式列表>
         from 表名
         where <条件>
 
    说明：在查询条件中可使用以下运算符或表达式：P134

运算符   运算符标识

比较运算符  <=，<,=,>,>=,!=,<>,!>,!<                                                                                                             
范围运算符  between... and,not between... and

列举运算符  in,not in

模糊匹配运算符  like,not like

空值运算符  is null,is not null

逻辑运算符  and,or,not

1.使用比较运算符：P70

例7：查询xsbook数据库xs表中借书数在2本以上的学生情况（含2本）。

select *
     from xs
     where 借书数>=2          --或为：where 借书数!<2

2.指定范围：P72

    用于指定范围的关键字有两个：between...and和 not between...and。用于查找字段值在（或不在）指定范围的行。

    格式为：表达式 [not] between <表达式1> and <表达式2>

其中：between关键字之后的是范围的下限（即低值）,and关键字之后的是范围的上限（即高值）

例8：查询xs表中1980 至1985年出生的学生情况。

select *
    from xs
    where 出生时间 between '1980-01-01' and '1985-12-31' 

再如：查询xs 表中非1980年出生的学生信息。

select *
    from xs
    where 出生时间 not between '1980-01-01' and '1980-12-31'

3.使用列举:

      使用in关键字可以指定一个值的集合，集合中列出所有可能的值，当表达式的值与集合中的任一元素个匹配时，即返回true，否则返回false。格式为：<表达式> [not]  in(值1,值2,...,值n)
 

例9：查询xs表中专业名为'计算机'、'信息工程'、'英语'或'自动化'的学生的借书证号,姓名,借书数,专业名。

select 借书证号,姓名,借书数,专业名
    from xs
    where 专业名 in('计算机','信息工程','英语','自动化')

注意：与in相对的是 not in，用于查找表达式的值不属于指定集合的行。

例如：查询xs表中非'计算机'、'信息工程'、'英语'专业的学生情况：

select *
    from xs
    where 专业名 not in('计算机','信息工程','英语')

4.使用通配符进行模糊查询:P70

    可用like 子句进行字符串的模糊匹配查询，like子句将返回逻辑值（true或False）。

    like子句的格式为： <字符型字段> [not] like <匹配串>

    其含义是：查找指定字段值与匹配串相匹配的记录。匹配串中通常含有通配符%和_（下划线）。

    其中:

        %：代表任意长度（包括0）的字符串

  例：a%c:表示以a开头，以c结尾的任意长度的字符串，如：asc、abccccccccc、aabbc、ac

 _：代表任意一个字符

  例：a_c表示以a开头，以c结尾，长度为3的字符串，如:abc、asc 

 Like子句中使用通配符的查询也称模糊查询。
                                                                              
例10：查询xs表中姓“王”且姓名为两个字的学生情况

select *
    from xs
    where 姓名 like '王_'

再如：查询book表中书名含有“数据”字样的图书情况：

select *
    from book
    where 书名 like '%数据%'

注意：所有通配符都必须在like 子句中才有意义，否则将被当作普通字符处理；且like子句中的匹配串也可以是一个不含通配符的完整的字符串（如下例）。

例11：查询xs表中计算机专业的学生情况

select *
    from xs
    where 专业名 like '计算机'    

查询相反的情况：则使用以下语句：

select *
    from xs
    where 专业名 not like '计算机'   --查询xs表中非计算机专业的学生情况

即：如果like后面的匹配串中不含通配符，那么可以用“=”（等号）运算符来替代like。即上例中的where子句等价于：where 专业名='计算机'。

5.使用null的查询

     当需要判定一个表达式的值是否为空值时，使用 is null关键字。格式为：<表达式> is [not] null

     当不使用not时，若表达式的值为空值，则返回true，否则返回false；当使用not时，结果刚好相反。

例12：查询xs表中专业名尚不确定的学生情况。

select *
    from xs
    where 专业名 is null

再如:查询xs表中专业名已确定的学生的姓名,专业名。

select 姓名,专业名
    from xs
    where 专业名 is not null
        
6.多重条件查询：使用逻辑运算符（P70）

    逻辑运算符and（与：两个条件都要满足）和or（或：满足其中一个条件即可）可用来联接多个查询条件。and的优先级高于or,但若使用括号可以改变优先级。

例13：查询xs表中借书数在3本以下的计算机专业学生的姓名和借书证号。

select 借书证号,姓名
     from xs
     where 专业名='计算机' and 借书数<3

再如：查询计算机和英语专业学生的基本情况：

select *
     from xs
     where 专业名='计算机' or  专业名='英语'

(四)对查询结果排序:P73

    order by子句可用于对查询结果按照一个或多个字段的值（或表达式的值）进行升序（ASC）或降序（DESC）排列，默认为升序。

    格式：order by {排序表达式[ASC|DESC]}[,...n]

   其中：排序表达式既可以是单个的一个字段（如例14），也可以是由字段、函数、常量等组成的表达式（如例15），或一个正整数。

例14：查询xs表中的全体学生信息并按出生时间排序，要求晚出生的学生排在前面。

select *
    from xs
    order by 出生时间  desc  

思考：若本例改为以下格式的语句，其功能是什么？

select top 5 *
    from xs
    order by 出生时间  desc  

例15:请查询图书的书名、六折后的书价，并将结果按六折后的书价的降序排列。
 
select 书名,单价*0.6 as 书价六折
    from  book
    order by  单价*0.6  desc      

说明：本例中，order by子句也可写为: order by 2，这里2代表排序字段或表达式（即：单价*0.6）在select子句中出现的顺序号。

注意：排序表达式可有多个，例如：将xs表的学生按性别的降序排序，同性别的按出生时间的升序排序：

select *
    from xs
    order by 性别 desc,出生时间 asc   

    注意：对于null值，若为升序则排在最前面，若为降序则排在最后。

(五)使用统计函数:又称集函数，聚合函数  P75

    在对表进行检索时，经常需要对结果进行计算或统计，T-SQL提供了一些统计函数（也称集函数或聚合函数），用来增强检索功能。统计函数用于计算表中的数据，即利用这些函数对一组数据进行计算，并返回单一的值。

            常用统计函数表

函数名   功能

AVG   求平均值

count   求记录个数，返回int类型整数

max   求最大值

min   求最小值

sum   求和

1. SUM和AVG

功能：求指定的数值型表达式的和或平均值。
  
例16：查询计算机专业学生所借图书的平均数、借书总数。

select avg(借书数) as 平均借书数,sum(借书数) as 借书总数
    from xs
    where 专业名='计算机'

2. Max和Min

功能：求指定表达式的最大值或最小值。

例17：在xs表中查询学生最多和最少的借书数

select max(借书数) as 最多借书数,min(借书数) as 最少借书数
    from xs

3. count:

    该函数有两种格式：count(*)和count([all]|[distinct] 字段名），为避免出错，查询记录个数一般使用count(*)，而查询某字段有几种取值用count(distinct 字段名）。

(1).count(*):

功能：统计记录总数。

例18：查询学生总数

select count(*) as 学生总数 
   from xs

再如：查询已借阅了图书的学生人数。

select count(*) as 借阅了图书的学生数
    from xs
    where 借书数<>0
   
思考：查询计算机专业的学生总数。

(2).count([all]|[distinct] 字段名）

功能：统计指定字段值不为空的记录个数，字段的数据类型可以是text、image、ntext、uniqueidentifier之外的任何类型。
  
例19：查询学生总数

select count(借书证号) as 总人数
    from  xs

说明：

    A.本例中由于“借书证号”字段值所有记录都不为空，故这一统计结果即为总记录个数，即和count(*)的统计结果一样；

    B.distinct表示：先消除指定字段取值重复的记录，然后再统计指定字段值不为空的记录个数；而all 则表示所有指定字段值不为空的全部记录都参加统计计数。默认为all。

例20：查询出版社数。

select  count(distinct 出版社) as 出版社数
   from book

注意:若select 列表中使用了统计函数,则列表中不允许再指定任何字段名，用group by子句指定的字段除外。如：以下操作将会产生错误：

select 借书证号,count(*)
    from xs
   
(六):对查询结果分组：P76

    group by子句用于将查询结果表按某一列或多列值进行分组，列值相等的为一组，每组统计出一个结果。该子句常与统计函数一起使用进行分组统计。格式为：

    group by 分组字段[,...n][having <条件表达式>]

例21：统计男、女生各多少人。

select 性别,count(*) as 人数
    from xs
    group by 性别

再如：查询各专业人数及各专业的总借书数。

select 专业名,count(*) as 各专业人数,sum(借书数) as 各专业的借书总数
    from xs
    group by 专业名

说明：在使用group by子句时应注意以下几点：P77

1.在使用group by子句后，select列表中只能包含：group by子句中所指定的分组字段及统计函数。

2.having子句的用法:

   having子句必须与group by 子句配合使用，用于对分组后的结果进行筛选（筛选条件中常含有统计函数）。

例22：查询学生人数在2人以上（含2人）的专业的专业名以及其学生人数

select 专业名,count(*) as 人数
    from xs
    group by 专业名
    having count(*)>=2

3. 分组查询时：不含统计函数的条件，通常使用where子句；含有统计函数的条件,则只能用having子句。

例如：查询“体育”专业男、女生各有多少人。

select 性别,count(*) as 人数
    from xs
    where 专业名='体育'
    group by 性别
      
再如：查询平均借书数在2本以上（含）的专业及其学生人数。

select 专业名,count(*) as 人数
     from xs
     group by 专业名
     having avg(借书数)>=2

(七)compute子句: P77

   格式：{compute 统计函数名}[,...n][by 分组表达式[,...n]

   功能：compute子句将产生一个单独的汇总行，用以显示统计信息。相当于在select 语句的结果后面加了一个汇总行，这样既查询出了源表中一个一个的详细记录（即具体的字段值），又查询出了统计某些列数据所产生的统计信息（即：细节+统计）。

例23：查询计算机专业学生的借书证号,姓名,出生时间及学生人数。

select 借书证号,姓名,出生时间
    from xs
    where 专业名='计算机'
    compute count(借书证号)     
 
    其中：汇总行的列标题：cnt表示：count函数汇总
            avg表示：avg函数汇总
     sum表示：sum函数汇总
 
再如：查询所有图书的书名，单价，出版社及均价。

select 书名,单价,出版社
    from book
    compute avg(单价)       

说明：使用compute子句时应注意以下几点：

1.compute <统计函数名> 子句中所涉及到的字段是select 字段名列表中已列出的字段名。如：以下操作将产生错误，思考一下为什么？

select 书名,单价,出版社
    from book
    compute sum(库存量) 

2.同时使用compute...by子句和order by子句也可实现分组统计查询，但compute...by子句必须紧跟在order by 子句之后；compute ...by子句中的分组字段必须是order  by子句中的第一个排序字段。

例24：查询各专业学生的姓名，性别，专业名及各专业的学生人数。

select 姓名,性别,专业名
    from xs
    order by 专业名
    compute  count(姓名) by 专业名  
　
再如：查询各专业学生的姓名，性别，专业名,借书数及其总借书数。

select  姓名,性别,专业名,借书数
    from xs
    order by 专业名
    compute sum(借书数) by 专业名
       
总结:group by:可用于解决分组统计的问题。即只能显示分组字段值及统计函数值,且每组只有一行统计数据。

     compute:可用于解决既要显示具体的记录数据又要显示统计信息的问题,并且不分组。即不需分组，既显示字段值又要显示统计函数值。

     compute...by：可用于解决既要显示具体的记录数据又要分组显示统计信息的问题。即分组统计，既要显示除分组字段以外的其它字段值又要显示统计函数值。

(八)、select语句的其它功能：（选学）

1、复制或创建表（into子句）：P88

into子句功能：将select语句查询所得的结果保存到一个新建的表中。

注意：into 子句应放在select 字段名列表之后。into子句不能与compute子句一起使用。
 
例25：由xs表创建'计算机专业学生借书证'表，包括借书证号和姓名。

select 借书证号,姓名  into 计算机专业学生借书证            
    from xs
    where 专业名='计算机'    

再如：将xs表数据全部复制到表xs4中。

select *  into xs4     
    from xs        

又例: 只复制xs表的结构，不复制记录，即新表为空表。

select *  into xs7           
    from xs
    where 1=2         /*这里指定一个永远为假的条件*，以使无记录被复制/
 
2、合并结果集（Union子句）：可以将两个或多个select查询的结果合并成一个结果集，即合并两个或多个查询结果的记录。

   格式为： select 语句1   {union  [all]  select  语句2}[...n]

   说明：(1).用来合并的各select 语句字段列表中的列数、各对应列的数据类型、列的顺序必须一致。

         (2).all表示：合并的结果中保留两个查询结果的所有行，不去除重复行。若省略all则在合并的结果中将去除重复行。

         (3).合并后结果集中的列名来自第一个seleect 语句。

例26:合并xsbook库中表xs1、xs2、xs3的全部记录（假设这三个表的结构相同）。

select *
    from xs1
union
    select *
        from xs2
union
    select *
        from xs3

3、显示变量或表达式的值（无数据源查询）：

   select语句还可显示常量、变量或表达式的值，也就是说用select语句可查询输出不在数据表中的数据，故又称为无数据源查询。

如：  select 'we are friends'

又如：select 123.56,'中国','沈阳'

      select 45*23-1
 
注意：这里也可以为要输出的值指定列显示标题，如： select '中国' as 国家,'沈阳' as 城市

4.用select 语句向表中插入多行记录。
 
格式：insert into 表名[(字段列表)]
         select 语句

注意：insert into表名[(字段列表)]和select查询结果集的列数、列序和对应列的数据类型必须一致。

例27:将表xs2中全体学生的记录数据追加到表xs1中。

insert into xs1
    select * from xs2

再如：将表xs3中女生的记录追加到表xs1中，要求只追加借书证号，姓名，性别，出生时间字段值。

insert into xs1(借书证号,姓名,性别,出生时间)
    select 借书证号,姓名,性别,出生时间 
    from xs3
    where  性别=0

注意：一个select 语句中各子句的顺序如下：

select---[记录范围]---字段名列表---[into]---[from]---[where]---[group by]---[having]---[order by]---[compute---[by]] 

练习一：以下所有操作均针对xsbook库中的表xs或表book进行。其中37-44为选做

1、查询xs表中学生的借书证号，姓名，借书数。

2、查询book表中所有图书信息。

3、查询book表中的所有出版社名称。

4、查询book表中书名、单价、库存量及各种库存图书的总价值（即总金额）。

5、查询xs表中前3名学生的姓名，性别，出生时间。

6、查询xs表中前30%学生的相关信息。

7、查询xs表中计算机专业学生的相关信息。

8、查询xs表中1979年后出生的学生的信息。

9、查询book表中图书单价在30-50之间的所有图书的书名、单价。

10、查询xs表中1980年出生的学生姓名，性别、出生时间。

11、查询book表中图书单价高于50元（不含50元）或低于30元（不含30元）的图书的书名、单价。

12、查询xs表中计算机、英语、体育、政治专业的学生的信息。
 
13、查询xs表中非计算机、英语、体育专业的学生姓名、性别。
 
14、查询book表中所有图书名称中包括了“政治”字样的图书的书名、单价。

15、查询xs表中所有“王”姓同学的姓名、性别、出生时间。

16、查询xs表中所有非计算机专业学生的信息。

17、查询xs表中专业已确定的学生的借书证号、姓名。

18、查询xs表中专业尚不确定的学生的信息。
 
19、查询xs表中英语专业全体女生的姓名，出生时间。

20、查询xs表中借书数在2本以上的全体男生的相关信息。

21、查询xs表中计算机专业或者英语专业的学生姓名和借书证号。

22.查询计算机专业的借书证号，姓名，性别，并按学生借书数的降序排列记录。

23.将xs表的所有记录排序：先按性别排序，同性别的同学再按出生日期的升序排列记录。

24.按库存图书的价值的升序排序查询所有图书的ISBN，书名及库存图书的价值（即单价*库存量）。

25.查询出生较早的三名同学的相关信息。

26.查询计算机专业的学生总数。

27.查询全体学生的总借书数、平均借书数、最多借书数和最少借书数。

28.查询book表中有几种图书。

29.查询xs表中有几个专业。

30.查询各专业的平均借书数。

31.查询计算机和英语专业分别有多少学生。

32.查询学生人数在2人以上（含）的专业及其学生人数。

33.查询计算机专业学生的借书证号,姓名,出生时间,借书数及平均借书数。

34.查询所有图书的书名，单价，库存量及库存总量。

35.查询各出版社所出版图书的书名，单价，出版社及平均价格。

36.查询各专业学生的借书证号,姓名,出生时间,专业名及其学生人数。

37.由xs表创建'英语专业学生'表，其中包括英语专业学生的全部信息。

38.复制xs表的结构保存在表xin中，表xin中含有借书证、姓名、性别、出生时间字段。

39.将表xs1,xs2,xs3中的记录合并到表women中，women中只包括女同学的借书证号,姓名,出生时间。注意：每个select语句都要加where子句。

40.用下列形式显示字符串：“中国”，“大连”，“170001”。

   国家   城市  邮编

   中国   大连  170001
  
41.显示计算表达式123190*3/45-2321的值。

42.显示系统的当前日期和时间。注意：用函数getdate()获取系统当前日期日间。

43.将表xs2中80年之前出生的学生记录数据追加到表xs1中。

44.将表xs3中借书数不为0的记录追加到表xs1中，要求只追加借书证号，姓名，性别，出生时间字段值。