MSSQL之四 简单查询

作为数据库开发人员，你需要因各种目的经常抽取数据，例如创建报表。你可以使用SQL查询从数据库服务器抽取数据。 本章解释如何通过执行SQL查询从数据库表中抽取选择的数据。进一步，讨论如何使用函数以自定义查询返回的数据值。而且，本章解释如何从数据库表中抽取汇总和分组的数据 重点 Ø        数据库中的数据类型 Ø        使用数据库中自带的函数处理结果集 Ø        用select语句从数据库表中抽取数据 Ø        掌握分组查询 预习功课 Ø          数据库中数据类型 Ø          内置函数的使用 Ø          Select语句的语法格式 Ø          分组查询的要点

本节将从六个方面研究Transact-SQL语言的数据类型。

首先，分析数据类型的概念。

然后，讲述数字数据类型的主要内容和特点。

之后，描述字符数据类型的使用方式。

接下来，研究日期和时间数据类型的输入输出特点。

接着，分析二进制数据类型的特点。

最后，讲述其他数据类型的内容和特点。

Ø       数据类型的类型和特点

在Microsoft SQL Server 2008系统中，包含数据的对象都有一个数据类型。实际上，数据类型是一种用于指定对象可保存的数据的类型。例如，INT数据类型的对象只能包含整数型数据，DATETIME数据类型的对象只能包含符合日期时间格式的数据。

在Microsoft SQL Server 2008系统中，需要使用数据类型的对象包括表中的列、视图中的列、定义的局部变量、存储过程中的参数、Transact-SQL函数及存储过程的返回值等。

Microsoft SQL Server 2008系统提供了28种数据类型。这些数据类型可以分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。

Ø       数字数据类型

数字数据类型包括BIGINT、INT、SMALLINT、TINYINT、BIT、DECIMAL、NUMERIC、MONEY、SMALLMONEY、FLOAT和REAL 11种数据类型。

使用数字数据的数据类型被称为数字数据类型。这些数据类型的数字可以参加各种数学运算。我们还可以为这些数据类型继续进行分类。

从这些数字是否有小数，可以把这些数据类型分为整数类型和小数类型。

从这些数字的精度和位数是否可以明确地确定，可以把这些数据类型分为精确数字类型和近似数字类型。

从是否可以表示金额，可以分为货币数字类型和非货币数字类型。

下面，详细研究每一种数据类型的特点。

整数数据类型表示可以存储整数精确数据。

在Microsoft SQL Server 2008系统中，有4种整数数据类型即BIGINT、INT、SMALLINT、TINYINT。

可以从取值范围和长度两个方面理解这些整数数据类型之间的区别。

DECIMAL和NUMERIC数据类型都是带固定精度和位数的数据类型。

这两种数据类型在功能是等价的，只是名称不同而已。

在Microsoft SQL Server 2008系统中，把这两种数据类型实际上作为完全相同的一种数据类型来对待。

如果希望存储代表货币数值的数据，那么可以使用MONEY和SMALLMONEY数据类型。这两种数据类型的差别在于存储字节的大小和取值范围不同。

在Microsoft SQLServer 2008系统中，MONEY数据类型需要耗费8个存储字节，其取值范围是-922,337,203,685,477.5808至922,337,203,685,477.5807。SMALLMONEY数据类型只需要4个存储字节，取值范围是-214,748.3648至214,748.3647。

【例4-1】演示MONEY数据类型

如果希望进行科学计算，并且希望存储更大的数值，但是对数据的精度要求并不是绝对的严格，那么应该考虑使用FLOAT或REAL数据类型。

FLOAT或REAL数据类型是用于表示数值数的大致数据值的数据类型。

BIT是可以存储1、0或NULL数据的数据类型。这些数据主要是用于一些条件逻辑判断。也可以把TRUE和FALSE数据存储到BIT数据类型中，这时需要按照字符格式存储TRUE和FALSE数据。

Ø       字符数据类型

字符数据类型用于存储固定长度或可变长度的字符数据。

在Microsoft SQL Server 2008系统中，提供了CHAR、VARCHAR、TEXT、NCHAR、NVARCHAR和NTEXT 6种数据类型。

前3种数据类型是非Unicode字符数据，后3种是Unicode字符数据。

Ø       日期和时间数据类型

日期和时间数据类型包括DATETIME和SMALLDATETIME两种数据类型。

如果希望存储日期和时间数据，那么可以使用DATETIME或SMALLDATETIME数据类型。

这两种数据类型的差别在于其表示的日期和时间范围不同、时间精确度也不同。

DATETIME数据类型可以表示的范围是1753年1月1日至9999年12月31日，时间精确度是3.33毫秒。SMALLDATETIME数据类型可以表示的范围是1900年1月1日至2079年12月31日，时间精确度是1分钟。

建议用户在大型应用程序中不要使用SMALLDATETIME数据类型，避免出现类似千年虫的问题。因为2079年12月31日不是一个特别遥远的日期。

【例4-2】演示如何使用SET DATEFORMAT语句

Ø       二进制数据类型

二进制数据类型包括BINARY、VARBINARY和IMAGE 3种数据类型。

二进制数据类型包括BINARY、VARBINARY和IMAGE3种数据类型，可以用于存储二进制数据。其中，BINARY可以用于存储固定长度的二进制数据，VARBINARY用于存储可变长度的二进制数据。BINARY(n)和VARBINARY(n)的数据长度由n值来确定，n的取值范围是1至8000。IMAGE数据类型用于存储图像信息。但是，在Microsoft SQL Server 2008系统中，微软建议使用VARBINARY(MAX)代替IMAGE数据类型

【例4-3】演示BINARY数据类型 

图：4-3

Ø       其他数据类型

除上述常用类型外，还包括CURSOR、SQL_VARIANT、TABLE、TIMESTAMP、UNIQUEIDENTIFIER和XML 6种数据类型

内置函数的特点和类型

可以把Microsoft SQLServer 2008系统提供的内置函数分为14种类型，每一种类型的内置函数都可以完成某种类型的操作，这些类型的函数名称和主要功能如表3-8所示。

【例4-4】演示使用聚合函数

使用SELECT语句进行数据查询是数据库的核心操作。SQL Server提供了SELECT语句较完整的数据查询语句形式，该语句具有灵活的使用方式和丰富的功能。

Ø       打开一个数据库

在对数据库中的数据进行操作之前，必须使用USE命令打开该数据库，并使用GO语句作为结束行；

USE 要打开的数据库

GO

Ø       SELECT语句

SELECT语句主要用于查询数据，也可以用来向局部变量赋值。常用的SELECT语句的语法为：

SELECT 选择列表（查询所显示的内容）

FROM 表的列表（查询内容所在的表）

WHERE 查询的条件（查询内容的条件）

选择列表可以包括几个列名或者表达式，用逗号隔开，用来指示应该返回哪些数据。表达式可以是列名、函数或常数的列表；FROM子句包含提供数据的表或视图的名称。当选择列表中含有列名时，每一个SELECT子句必须带有一个FROM子句。WHERE子句用于给出查询条件。

【例4-9】：从数据库book中查询bookname、publish、pubdate、price，条件时当bookid等于4003的时候，则在查询窗口中运行如下命令：例一

use BookIofo

go

select bookname,publish,pubdate,price from book wherebookid=4003

运行结果如下：

下面详细介绍SELECT语句饿各种使用方法

Ø       使用星号（*）和列名

       如果在选择列表中使用星号（*），则从from子句指定的表或视图中查询并返回所有列。

       【例4-10】：查询book表中的所有信息，即所有行和所有列。

在查询窗口中运行如下命令：

select *from book

运行结果如下：

在SELECT之后使用DISTINCT关键字，会消除指定列的值都相同的那些行。

【例4-11】：从book表中查询图书的出版社，要求消除值相同的那些行。在查询窗口中运行如下命令：

select publish from book

select distinct publish from book

运行结果如下：

第一部分是要显示前三行，而第二部分则显示了3%也就是一行。

Ø       修改查询结果中列的标题（别名）

       在查询结果中可以看到显示结果的列标题就是表的列名字，是否可以将显示结果的列标题修改为其他直观易懂的标题呢？

       修改查询列标题有以下3中方法。

第一种方法：将要显示的列标题用单引号括起来后接等号，后接要查询的列名。

第二种方法：将要显示的列表题用单引号括起来后，写在列名后面，两者之间使用空格隔开。

第三种方法：将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字。

注意：这里修改的只是查询出的结果的列标题。表中的列名并没有改变，早输入SQL语句的时候没注意标点符号一定要在半角状态下输入。

【例4-13】：在查询窗口中运行如下命令：

select '出版社'=publish,'价格'=price from book

运行结果如下：

这是用第一种方法给查询结果取别名

使用第二种方法在SQL查询窗口中运行如下命令

select publish'出版社', price'价格' from book

使用第三种方法在SQL查询窗口中运行如下命令

select publish as '出版社', price as '价格' from book

Ø       在查询结果中显示字符串

       在一些查询中，经常需要在查询结果中增加一些字符串.

        在SELECT子句中，将要增加的字符串用单引号括起来，然后和列的名字写在一起，中间用逗号分隔开。

【例4-14】：在查询窗口中运行如下命令：

select bookname,'书的单价为：',price from book

运行结果如下：

Ø       使用WHERE子句给出查询的条件

       使用WHERE子句限制查询的范围，通常情况下，必须定义一个或多个条件限制查询选择的数据行。WHERE子句指定逻辑表达式（返回值为真或假的表达式），结果集将返回表达式为真的数据行。

       在WHERE子句中，可以包含比较运算符、逻辑运算符。比较运算符有=（等于）、<>（不等于）、!=（不等于）、>（大于）、>=（大于等于）、!>（不大于）、<（小于）、<= （小于等于）、!<（不小于）。

       逻辑运算符有AND（与）、OR（或）、NOT(非)，用来连接表达式。例如，“图书的价格在50以下并且出版社为科学出版社，可表示为：price<50OR publish='科学出版社'”

【例4-15】：在查询窗口中运行如下命令：

select publish,price from book whereprice<50.00 and publish='科学出版社'

运行结果如下：

    Publish Price

1 科学出版社 38.0

上面查询的是价格低于50.00并且出版社为：科学出版社的查询结果

Ø       查询所需要的列为表达式

       SELECT子句中的选项列表可以是要指定的表达式或列的列表，表达式可以是列名函数或常数的列表

【例4-16】：最大值。最小值和平均值需要使用MIN()、MAX()、AVG()函数，在括号内写上要计算的列名，即“定价”。

在查询窗口中运行如下命令：

select min(price) as 最低价格,max(price) as 最高价格from book

表达式为函数的查询。

Ø       使用ORDER BY 子句重新排列查询结果

       可以使用ORDERBY子句对查询结果重新排列，可以规定升序（从低到高）或降序（从高到低），方法是使用关键字ASC（升序）或DESC（降序），系统则默认为升序。可以在ORDER BY子句中指定多个列，查询结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2行排序…..依此类推，ORDERBY子句，要写在WHERE子句的后面。

【例4-17】：查询book表中的bookname和price，要求按price查询结果的降序排序。

在查询窗口中运行如下命令：

select bookname,price from book order byprice desc

运行结果如下：

使用ORDER BY降序排序

Ø       使用IN关键字

【例4-18】查询图书访问量为“9702”、“9708”、“9705”的bookname。

【实例分析】图书访问量为“9702”、“9708”、“9705”即为条件，用WHERE字句实现，可以表示为reader=‘9702’OR reader=‘9708’OR reader=‘9705’

在

在查询窗口中运行如下命令：

select bookname from book wherereader='9702'or reader='9708' or reader='9705'

.

而是用IN关键字进行查询比使用两个OR运算符进行查询更为简单，并且易于阅读和理解，使用IN关键字的SQL语句如下：

select bookname from book where readerin('9702','9708','9702')并且运行结果和上面的一样

Ø       使用LIKE关键字进行查询

       读者经常会碰到这样的问题：查询以某个字开头的书名，或者查询以某个字结尾的东东，查询与给定的某些字符串相匹配的数据可以使用LIKE关键字。LIKE关键字是一个匹配运算符，它与字符串表达式相匹配，字符串表达式由字符串和通配符组成。SQL的4个通配符为：

（1）%百分号，匹配包含0个或多个字符的字符串。

（2）_下划线，匹配任何单个的字符。

（3）[]排列通配符，匹配任何在范围或集合之内的单个字符，例如，[m-p]匹配的是m、n、o、p单个字符。

（4）[^]不在范围之内的字符，匹配任何不在范围或集合之内的单个字符，例如，[^mnop]或[^m-p]匹配的是除了m、n、o、p之外的任何字符。

通配符和字符串必须括在单引号中，例如；

LIKE’中%’匹配的是以“中”开始的字符串；LIKE’%大学’匹配的是以“大学”两个字结尾的字符串；LIKE’_人%’匹配的是第二个为“人”的字符串。

LIKE’n[^c]%’匹配所有以字母n开始并且第二个字母不是c的所有字符串。要查找通配符本身时，需要它们用方括号括起来。例如，

LIKE’5[%]’表示要匹配“5%”。

【例4-19】查询以“础”结尾的书名。

查询窗口中运行如下命令：

select * from book where bookname like'%础'

运行结果如下所示：

Ø       使用BETWEEN 关键字

BETWEEN关键字总是和AND一起使用，用来查询在一个指定范围内的信息。

【例4-21】查询price大于50.00大于40.00的所有图书，要求查询结果按升序排列。

查询窗口中运行如下命令：

select *from book where price between 40.00 and 50.00 orderby price asc

运行结果如下：

使用COMPUTEBY字句查询

Ø       使用GROUP BY

       奖查询结果按照GROUPBY后指定的列进行分组，该字句写在WHERE字句的后面，当在SELECT字句中包含聚合函数时，最适合使用GROUP BY字句。SELECT字句中的选项列表中出现的列，包含在聚合函数中或者包含在GROUP BY字句中，否则，SQLServer将返回如下错误提示消息：

       “表名.列名在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUP BY字句中”。

       【例4-23】按出版社统计每个出版社出版数的种类

查询窗口中运行如下命令：

select publish,count(publish)as'reader' from book group bypublish

Ø       HAVING子句

       HAVING子句用于限定组或聚合函数的查询条件。该子句常常用在GROUP BY子句之后，在结果集分组之后再进行判断。如果查询条件需要在分组之前被应用，则使用WHERE子句，其限制查询条件比使用HAVING子句更有效，这种技巧减少了要进行分组的行数。如果无GROUP BY子句，则HAVING子句仅在选择列表中用于聚合函数。在这种情况下，HAVING子句的作用与WHERE子句的作用相同。如果HAVING子句不是在这两种情况下使用的，则SQL Server将返回错误提示消息。

【例4-24】查询出版社为“科学出版社”所出书的平均价格。

查询窗口中运行如下命令：

selectpublish,avg(price)as'平均价格'from book  groupby publish  having publish='科学出版社'

select publish,avg(price)as'平均价格'from book where publish='科学出版社'group by publish

注意：如果HAVING子句的位置变化或WHERE子句的位置变化，则会出现错误消息提示。

运行结果如下：

Ø       使用UNION运算符

       UNION运算符用于将两个或多个查询结果合并成一个结果，当使用UNION运算符时，需要遵循以下两个规则:

(1)   所有查询中列数和列的顺序必须相同。

(2)   所有查询中按顺序对应列的数据类型必须兼容。

加入UNION运算符的SELECT语句中列举的与下面的方式对应；第一个SELECT语句的第一列将对应在每一个随后的SELECT语句的第一列，第二列对应在每一个随后的SELECT语句的第二列……

       另外，对应的列必须用于兼容的数据类型，这意味着两个对应列必须是相同的数据类型，或者SQL Server必须明确地从一种数据类型转换到另一种数据类型。

实践问题

1、下面哪个操作符被用于显示一系列包含在列中一个范围内的值得记录？

A、 AND    B、=   C、BETWEEN      D、%

2、一个表在不同的记录中包含列的重复值。你将如何从查询的输出中消除重复行

3、写出一个抽取EMPLOYEE 表中所有行的查询，其中以“p”字符开始并且包含“A”或“E”作为第二个字符

1、写出一个将计算SalesOrderFetail表中的每个SalesOrderID的订单数量总和的查询

2、写出将搜索Department表中以‘Pro’开始的所有部门的名称的查询

小结

在本章中，你将学习到：

1.      使用SELECT语句可以从数据库中抽取数据。

2.      使用SELECT（*）可以抽取所有列。

3.      必须基于条件抽取的数据是通过添加WHERE从句指定的。

4.      连接操作符被用于来连接字符串表达式。

5.      算术操作符被用来完成算术操作。

6.      在SELECT语句中使用逻辑操作符来抽取基于一个或满足条件的记录。逻辑操作符有AND和NOT.

7.      范围操作符基于范围抽取数据。有两种范围操作符BETWEEN和NOTBETWEEN.

8.      IN关键字允许在列表中满足任何一个值的值选择。

9.      NOT  IN 关键字限制满足列表中任何一个值的选择。

10.  LIKE 关键字被用来指定搜索模式。

11.  IS NULL关键字被用来抽取漏掉的值。

12.  ORDER BY 关键字被用来以特定的顺序抽取数据。

13.  TOP 关键字仅抽取行的前面集合，它可以是来自查询结果返回的数字或行百分比。

14.  DISTINCT 关键字消除重复行。

15.  聚合函数，例如 AVG,COUNT,MIN,MAX和SUM,被用来抽取汇总数据。

group by 从句被用于分组结果集。

欢迎打赏