SELECT语句中的数学问题

6.2SELECT语句中的数学问题

6.2.1 SQL Server中的变量

Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。

1. 局部变量

局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。

其说明形式如下：DECLARE @variable_name datatype [, @variable_name datatype…]

在Transact-SQL中不能像在一般的程序语言中一样使用“@variable_name=value”来给变量赋值，必须使用SELECT或SET命令来设定变量的值。其语法如下：

SELECT @variable_name = value

SET @variable_name = value

【10-1】声明一个长度为8个字符的变量@id，并赋值。

DECLARE @id CHAR(8)

SELECT @id =’10010001’

2. 全局变量

全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQL Server的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。使用全局变量时应该注意以下几点：

⑴全局变量不是由用户的程序定义的，它们是在服务器级定义的。

⑵用户只能使用预先定义的全局变量。

⑶引用全局变量时，必须以标记符“@@”开头。

⑷局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。

3. 注释符

在Transact-SQL中可使用两类注释符：

⑴ ANSI标准的注释符“--”用于单行注释。

⑵与C语言相同的程序注释符号，即“/*……*/”，/* 用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。

6.2.2 SQL Server的运算符

运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQL Server 2000中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串连接运算符。如表6.l所示。

表6.l SQL Server的运算符

种类	运算符	说明	种类	运算符	说明
算术运算符	%, **	取模，指数	关系运算符	=	等于
	*, /	乘、除		<>, ! =	不等于
	+, -,	加、减		>, <	大于、小于
逻辑运算符	NOT	取相反的逻辑值		<=, >=	小于等于、大于等于
	AND	两个值为真则结果为真		BETWEEN…AND…	检索两值之间的内容
	OR	只要一个值为真结果就为真		IN	检索匹配列表中的值
位运算符	&	按位与(两个操作数)		LIKE	检索匹配字符样式的数据
	|	按位或OR(两个操作数)		IS NULL	检索空数据
	^	按位异或(两个操作数)	赋值运算符	=	将数据值指派给特定的对象
字符串运算符	+	将两个字符串连接起来

算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数值数据分类的任何数据类型。算术运算符包括加(+)、减(—)、乘(*)、除(/)、指数(**)和取模(%)。

赋值运算符(=)能够将数据值指派给特定的对象。

位运算符能够在整型数据或者二进制数据(image数据类型除外)之间执行位操作。

比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE(表示表达式的结果为真)、FALSE(表示表达式的结果为假)以及UNKNOWN。除了text、ntext或image数据类型的之外，比较运算符可以用于其它数据类型的数据的比较。

逻辑运算符可以把多个关系表达式连接起来。逻辑运算符包括AND、OR和NOT。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。

字符串运算符允许通过加号(+)进行字符串连接，这个加号即被称为字符串连接运算符。例如，对于语句SELECT ’made in’+’china’，其结果为made in china。

用运算符将常量、变量、函数连接起来的式子为表达式。在一个表达式中，算术运算的优先级由高到低是：指数、乘除、求模、加减，在同一级别中则按从左到右的顺序执行。逻辑运算的优先级由高到低是：NOT、AND、OR。SQL Server运算符的优先级别如图6-1所示。

6.2.3 SQL Server中的函数

1. 数学函数

SQL Server的数学函数主要用来对数值表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数值数据(decimal、integer、float、real、money、smallmoney、smallint和tinyint)进行处理。常用的数字函数如表6.2所示。

表6.2 常用数字函数

函数	描述	函数	描述
ASIN (n)	反正弦函数ASIN(n)为以弧度表示的角度值。	ABS (n)	求n的绝对值。
ACOS (n)	反余弦函数ACOS(n)为以弧度表示的角度值。	EXP (n)	求n的指数值。
ATAN (n)	反正切函数ATAN(n)为以弧度表示的角度值。	MOD (m,n)	求m除以n的余数。
SIN (n)	求正弦函数，n为以弧度为单位的角度。	CEILING (n)	返回大于等于n的最小整数。
COS (n)	求余弦函数，n为以弧度为单位的角度。	FLOOR (n)	返回小于等于n的最大整数。
TAN (n)	求正切函数，n为以弧度为单位的角度。	ROUND(n,m)	对n做四舍五入处理，保留m位。
DEGREES (n)	弧度单位的角度转换为度数为单位的角度。	SQRT (n)	求n的平方根。
RADIANS (n)	度数单位的角度转换为弧度为单位的角度。	LOG10(n)	求以10为底的对数。
PI	PI 的常量值3.14159265358979。	LOG(n)	求自然对数。
RAND	返回0~1之间的随机值。	POWER(n,m)	求n乘指定次方m的值。
SIGN(n)	求n的符号，正(+1)、零(0)或负(-1)号。	SQUARE(n)	求n的平方。

【例6.1】在查询分析器中使用CEILING()、FLOOR()、ROUND()函数。

SELECT ceiling(13.4), floor(13.4), round(13.4567,3)

在查询分析器中运行上述语句的结果为：14 13 13.4570

【例6.2】求几个数值ROUND(45.923,2)、ROUND(45.923,0)、ROUND(45.923,-1)的近似值。

SELECT round(45.923,2), round(45.923,0), round(45.923,-1)

在查询分析器中运行上述语句的结果为：45.920 46.000 50.000

2. 字符串函数

字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。此外，某些字符串函数还能够处理text、ntext、image数据类型的数据。常用的字符串函数如表6.3所示。

【例6.3】先使用SUBSTRING函数从字符串中获取指定的子字符串，然后使用LOWER函数、UPPER函数对子字符串进行转换，且把UPPER函数嵌套在LOWER函数内或把LOWER函数嵌套在UPPER内。在查询分析器中输入以下语句：

DECLARE @string_lower_upper varchar(40)

SET @string_lower_upper = ’Information Model Creation Issues.’

SELECT LOWER(SUBSTRING(@string_lower_upper, 1, 11)) AS Lower,

UPPER(SUBSTRING(@string_lower_upper, 13, 5)) AS Upper,

LOWER(UPPER(SUBSTRING(@string_lower_upper, 19, 8))) As LowerUpper,

UPPER(LOWER(SUBSTRING(@string_lower_upper, 28, 7))) As UpperLower

语句运行结果如图6-2所示。

表6.3 常用字符串函数

种类	函数名	参数	说明
基本字符串函数	UPPER	char_expr	小写字符串转换为大写字符串
	LOWER	char_expr	大写字符串转换为小写字符串
	SPACE	integer_expr	产生指定个数的空格组成字符串
	REPLICATE	char_expr, integer_expr	指定的次数重复字符串
	STUFF	char_expr1,start,length, char_expr2	在char_expr1字符串中从start开始，长度length的字符串用char_expr2代替
	REVERSE	char_expr	反向字符串表达式char_expr
	LTRIM	char_expr	删除字符串前面的空格
	RTRIM	char_expr	删除字符串后面的空格
字符串查找函数	CHARINDEX	char_expr1, char_expr2 [,start]	在串2中搜索char_expr1的起始位置
	PATINDEX	’%pattern%’, char_expr	在字串中搜索pattern出现的起始位置
长度和分析函数	SUBSTRING	char_expr, start, length	从start开始，搜索length长度的子串
	LEFT	char_expr, integer_expr	从左边开始搜索指定个数的子串
	RIGHT	char_expr, integer_expr	从右边开始搜索指定个数的子串
转换函数	ASCII	char_expr	字符串最左端字符的ASCII代码值
	CHAR	integer_expr	ASCII代码值转换为字符
	STR	float_expr[,length[,decimal]]	数值数据转换为字符型数据

【例6. 4】使用LTRIM函数删除字符变量中的起始空格。

DECLARE @string_to_trim varchar(60)

SET @string_to_trim = ’ Five spaces are at the beginning of this string.’

SELECT ’Here is the string without the leading spaces: ’ + LTRIM(@string_to_trim)

在查询分析器中运行上述语句的结果为：

Here is the string without the leading spaces: Five spaces are at the beginning of this string.

【例6.5】使用LEFT函数返回字符串abcdefg最左边的4个字符。

SELECT LEFT(’abcdefg’,4)

在查询分析器中运行上述语句的结果为：abcd

【例6.6】将指定的字符串的排列顺序颠倒。

SELECT REVERSE(123), REVERSE(’abc’)

在查询分析器中运行上述语句的结果为：321 cba

【例6.7】在第一个字符串(abcdef)中删除从第二个位置(字符b)开始的三个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。

SELECT STUFF(’abcdef’, 2, 3, ’ijklmn’)

在查询分析器中运行上述语句的结果为：aijklmnef

3. 日期和时间函数

日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。在SQL Server 2000中，日期和时间函数如表6.4所示表中列出了日期和时间函数的名称、参数以及相关解释。

表6.4 日期和时间函数

函数名	参数	说明
DATEADD	(datepart, number, date)	以datepart指定的方式，给出date与number之和
DATEDIFF	(datepart, date1, date2)	以datepart指定的方式，给出date2与date1之差
DATENAME	(datepart, date)	给出date中datepart指定部分所对应的字符串
DATEPART	(datepart, date)	给出date中datepart指定部分所对应的整数值
GETDATE	( )	给出系统当前的日期的时间
DAY	(date)	从date日期和时间类型数据中提取天数
MONTH	(date)	从date日期和时间类型数据中提取月份数
YEAR	(date)	从date日期和时间类型数据中提取年份数

【例6.8】从GETDATE函数返回的日期中提取月份数。

SELECT GETDATE() ’Current Date’,DATEPART(month,GETDATE()) AS ’Month Number’

在查询分析器中运行上述语句的结果如图6-3所示。

【例6.9】从指定日期03/12/2005中返回月份数、天数和年份数。

SELECT MONTH(’03/12/2005’), DAY(’03/12/2005’),YEAR(’03/12/2005’)

在查询分析器中运行上述语句的结果如图6-4所示。

4. 转换函数

SQL Server能够自动处理某些数据类型的转换。例如，char和varchar、smallint和int，SQL Server可以将它们自动转换，这种转换被称为隐性转换。但是，无法由SQL Server自动转换的或者是SQL Server自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数CONVERT和CAST允许用户把某种数据类型的表达式转换为另一种数据类型。CAST和CONVERT提供相似的功能，如表6.5所示。

表6.5 转换函数

函数名	参数	说明
CAST	expression AS data_type	将表达式expression转换为指定的数据类型data_type
CONVERT	data_type[(length)],expression [,style]	date_type为expression转换后的数据类型 Length表示转换后的数据长度 Style(不带纪元和带纪元)

【例6.10】将数值型数据248.57转换为字符型后，又转换成为货币型数据。

DECLARE @myval decimal (5, 2)

SET @myval = 248.57

SELECT CAST(CAST(@myval AS varchar(20)) AS money)

如果使用CONVERT，将提供和CAST相似的功能。

SELECT CONVERT(money, CONVERT(varchar(20), @myval))

【例6.11】以PI/2弧度的角度返回以度数的角度。

SELECT ’The number of degrees in PI/2 radians is: ’ +

CONVERT(varchar, DEGREES((PI()/2)))

此例对以弧度为单位的角度(PI/2)，将返回相应的以度数为单位的角度(90)。

【例6.12】写一个SELECT语句，取出所有学生的学号、姓名、出生日期和入学成绩，出生日期必须是在1986年10月3日之前的。

USE student

SELECT stud_id,name,birthday,mark

FROM stud_info

WHERE birthday <= CONVERT(datetime,’10/03/1986’)

此例中WHERE子句的搜索条件需要使用CONVERT函数，将用字符串表达的日期转换成日期时间型的。

5. 系统函数

表6.6所示的系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。它可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。

表6.6 系统函数

函数名	参数	说明
DB_ID, DB_NAME	DB_ID(name), DB_NAME(id)	获得指定数据库的ID号或名称
HOST_ID,HOST_NAME	HOST_ID(name),HOST_NAME(id)	获得指定主机的ID号或名称
OBJECT_ID,OBJECT_NAME	OBJECT_ID(name),OBJECT_NAME(id)	获得指定对象的ID号或名称
SUSER_ID,SUSER_NAME	SUSER_ID(name),SUSER_NAME(id)	获得指定登录的ID号或名称
USER_ID, USER_NAME	USER_ID(name), USER_NAME(id)	获得指定用户的ID号或名称
COL_NAME	table_id, column_id	获得表标识号table_id和列标识号column_id所对应的列名
COL_LENGTH	table, column	获得指定表列的定义长度
INDEX_COL	table, index_id, key_id	获得指定表、索引ID和键ID的索引列名称
DATALENGTH	expression	获得指定表达式占用的字节数

【例6.13】查询stud_info表中学号为0401010811的address列的定义长度和数据长度。

SELECT COL_LENGTH(’stud_info’, ’address’) AS 定义长度,

DATALENGTH(address) AS 数据长度

FROM stud_info

WHERE stud_id=’0401010811’

其中：COL_LENGTH将获得指定表stud_info的address列定义长度，而不是列中存储的任何单个字符串的长度。用DATALENGTH函数来确定特定值中的字符总数。

【例6.14】返回student数据库的stud_info表中的第二列的名称。

SELECT COL_NAME(OBJECT_ID(’stud_info’), 2)

在查询分析器中运行上述语句的结果为：name

6. 集合函数

集合函数可以针对整个或者几个列或者一个列进行数据汇总，它常用来计算SELECT语句查询结果集的统计值。例如，求一个结果集合的最大值、最小值、平均值和所有元素和等。SQL Server提供的集合函数如表6.7所示。

表6.7 集合函数

函数	描述
AVG	计算一列值的平均值
COUNT	统计一列中值的个数
MAX	求一列值中的最大值
SUM	计算一列值的总和
MIN	求一列值中的最小值

【例6.15】求“JAVA程序设计”课程的平均成绩，具体命令如下。

SELECT AVG (grade) FROM stud_grade WHERE course_id=’0401010102’

在查询分析器中运行上述语句将可以在学生成绩表中查询到“JAVA程序设计”课程(课程号course_id为“0401010102”)的平均成绩。

集合函数是从SELECT语句中计算一个“返回列的数据”，其结果是所选数据列的计算结果。例如：SELECT AVG(salary) FROM teacher_info语句将返回单一的结果，即teacher_info表中所有salary列数据的平均值。又如：SELECT AVG(salary) FROM teacher_info WHERE tech_title = ’讲师’语句将返回teacher_info表中所有tech_title列为“讲师”的平均工资。而语句SELECT count (*) FROM teacher_info中使用集合函数跟上面的例子有点不同，因为没有一个具体的列被指定给COUNT函数，这条语句实际上将返回teacher_info表的行数。