4.1 T-SQL 查询基础
4.1.1 查询和记录集
在讲解查询之前,有必要说明一下查询的机制和查询的结果。
查询是针对表中已经存在的数据行而言的,可以简单地理解为“筛选”,其过程如图。
学员编号 | 学员姓名 | 地址 | 所在班级 | ··· |
001 | 张三1 | 湖南长沙 | S201 | |
002 | 李四1 | 湖北宜昌 | S203 | |
003 | 赵五1 | 甘肃天水 | S202 | |
004 | 王六1 | 山东菏泽 | S204 | |
005 | 张三2 | 台湾新竹 | S205 | |
006 | 李四2 | 香港九龙 | S206 | |
007 | 赵五2 | 北京顺义 | S207 | |
008 | 王六2 | 北京朝阳 | S208 | |
009 | 张三3 | 四川绵阳 | S209 | |
010 | 李四3 | 陕西临潼 | S210 | |
011 | 赵武3 | 新疆噶什 | S211 |
查询结果如图
004 | 王六1 | 山东菏泽 | S204 | |
005 | 张三2 | 台湾新竹 | S205 | |
006 | 李四2 | 香港九龙 | S206 | |
007 | 赵五2 | 北京顺义 | S207 |
数据表在接受查询请求的时候,可以简单地理解为它将逐行选取,判断是否符合查询的条件。如果符合就提取出来,然后把所有的被选择的行组织在一起,形成另外一个类似于表的结构,这便是查询的结果,通常叫做记录集(RecordSet)。
由于记录集的结构实际上和表的结构是相同的,都是由多行组成的,因此,在记录集上依然可以进行再次查询。
4.1.2 使用 Select 语句进行查询
查询使用Select语句,最简单的查询语句的格式可以表示为:
Select <列名> From <表名> [Where <查询条件表达式>] [Order by <排序的列名> [ASC 或 DESC]]
其中,Where及条件是可选的,如果不限制,则查询返回所有行的数据项。
Order By 是用来排序的,数据表中的记录是无序的,并不按照一定的次序存储,例如要按照学员的考试成绩排序以看到高分的情况,则需要按照分数列的值进行排序。
查询语句一般都在SQL Server Management Studio 的查询窗口中进行调试和运行,以下分别举例说明最基本的查询的不同情况。
1. 查询所有的数据行和列
把表中的所有行和列都列举出来比较简单,这时候需要使用“*”通配符来表示所有的列:
Select * from Students
2. 查询部分行列---条件查询
查询部分列需要例举不同的列名,而查询部分行需要使用Where子句进行条件限制,例如:
Select Scode, Sname, Saddress From Students where Saddress = '河北'
以上的查询语句,将只查询地址为“河北”的学员,并且只显示编号、姓名和地址列。
同理,以下的查询语句,则只要不是河北的学员都显示出来。
Select Scode, Sname, Saddress From Students where Saddress <> '河北'
3. 在查询中使用列名
AS 子句可以用来改变结果集列的名称,也可以为组合或者计算出的列指定名称,还有一种情况是让标题列的信息更为易懂,例如把Scode列名查询后显示为“学员编号”。
在T-SQL中重新命名列名可以使用AS子句,例如:
Select Scode as 学员编号, Sname as 学员姓名, Saddress as 学员地址 From Students Where Saddress <> '河北'
还有一种情况是使用计算、合并得到新列的命名,例如,在查询NorthWind数据库的Employees表中的数据时,需要把FirstName和LastName字段合并成一个叫做“姓名”的字段,可以执行以下查询语句:
Select FirstName + '.' + LastName AS '姓名' From Employees
重新命名列名还有一种方法,就是采用“=”来命名,例如:
Select '姓名' = FirstName + '.' + LastName From Employees
4. 查询空行
在SQL语句中采用“Is Null”或者“Is Not Null”来判断是否为空行,因此,如果要查询学员信息表中没有填写E-mail 信息的学员,可以使用以下查询语句:
Select Sname From Students Where SEmail Is Null
5. 在查询中使用常量列
有时候,需要将一些常量的默认信息添加到查询输出中,以方便统计或计算,例如查询学员信息的时候,学校名称统一都是“河北”,查询输出的语句为:
Select 姓名=SName, 地址=SAddress, '河北' AS 学校名称 From Students
查询输出多了一行"学校名称",该列的所有的数据都是“河北”
6. 查询返回限制的行数
一些查询需要返回限制的行数,例如在测试的时候,如果数据库中有上万条记录,而只要检查前面10行数据是否有效就可以了,没有必要查询输出全部的数据,以提高查询速度,这时候就要用到限制返回行数的查询。
在T-SQL中,限制行数使用Top关键字来约束,例如要查询返回5位女生的姓名和地址信息。
Select top 5 Sname, Saddress From Students Where Sex = 0
还有一种情况是需要从表中按一定的百分比提取记录,这时候还需要用到Percent关键字来限制,例如要提取20%的女生数据。
Select top 20 Percent Sname, Saddress from Students Where Sex = 0
4.2 查询排序
如果需要按照一定的顺序排列查询语句选中的行,则需要使用Order by子句,并且排序可以是升序(Asc)或者降序(Desc)。如果不指定Asc或者Desc,记录集按Asc升序排列。
上面讲述过的SQL语句,都可以在其后面再加上Order By来进行排序。
例如,查询学员成绩的时候,如果把所有成绩都降低10%后加5分,再按照及格成绩的高低来进行排序,SQL语句如下:
Select StudentID as 学员编号, (Score * 0.9 + 5) as 综合成绩 From Score Where (Score * 0.9 + 5) Order by Score
以下是查询Pubs数据库中的作者表和雇员表,然后合并查到的所有姓名信息,并按照姓名降序排列。
Select Au_Lname + '.' + Au_fName AS Emp From Authors Union
Select fName + '.' + LName as Emp From Employee Order by Emp Desc
还可以按照多个字段进行排序。例如要在学员成绩的基础上,再按照课程ID进行排序的语句如下:
Select StudentID As 学员编号, Score As 成绩 From Score Where Score > 60 Order by Score, CourseID
4.3 在查询中使用函数
于Java语言的包类似,SQL Server 也提供了一些内部函数,不同类型的函数可以和SQL Server的Select 语句联合使用,也可以于 Update 和 Insert 一起使用。
以下把函数分为4累,分别是字符串函数、日期函数、数字函数、系统函数。
4.3.1 字符串函数
字符串函数用于控制返回给用户的字符串,这些功能仅用于字符型数据。
函数名 | 描述 | 举例 |
CharIndex | 用来寻找一个指定的字符串在另一个字符串中的起始位置 | Select Charindex('Accp', 'My Accp Course', 1) 返回:4 |
Len | 返回传递给它的字符串长度 | Select Len('SQL Server 课程') |
Upper | 把传递给它的字符串转换为大写 | Select Upper('sql server 课程') |
Ltrim | 清除字符左边的空格 | Select Ltrim(' 周杰伦 ')返回:周杰伦 (后面的空格保留) |
Rtrim | 清除字符右边的空格 | Select Rtrim(' 周杰伦 ') |
Right | 从字符串右边返回指定数目的字符 | Select Right('买买提·图尔松', 3) 返回:图尔松 |
Replace | 替换一个字符串中的字符 | Select Replace ('莫勒克切。杨克',‘克’,'兰') 返回:'莫勒克切。杨兰 |
Stuff | 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串 | Select Stuff(‘ABCDEFG’,2,3,'我的音乐 我的世界') 返回:A我的新月我的世界EFG |
4.3.2 日期函数
日期函数用于操作日期值,我们不能直接对日期运用数学函数。例如,如果执行一个诸如“当前日期+1”的语句,SQL Server无法理解要增加的是一日、一月还是一年。
日期函数帮助提取日期值中的日、月以及年,以便分别操作它们。
函数名 | 描述 | 举例 |
GetDate | 取得当前的系统日期 | Select GetDate() 返回:今天的日期 |
DateAdd | 将指定的数值添加到指定的日期部分后的日期 | Select DateAdd(mm,4'01/01/99') 返回:以当前的日期格式返回05/01/99 |
DateDiff | 两个日期之间的指定日期部分的区别 | Select DateDiff(mm, '01/01/99','05/01/99') 返回:4 |
DateName | 日期中指定日期部分的字符串形式 | Select DateName(dw, '01/01/2000') 返回:Saturday |
DatePart | 日期中指定日期部分的整数形式 | Select DatePart(day, '01/05/2000') 返回:15 |
4.3.3 数学函数
数学函数用于对数值进行代数运算。
函数名 | 描述 | 举例 |
Abs | 取数值表达式的绝对值 | Select ABS(-43) 返回:43 |
Ceiling | 取大于或等于指定数值,表达式的最小整数 | Select Ceiling(43.5) 返回:44 |
Floor | 取小于或等于指定表达式的最大整数 | Select Floor(43.5) 返回:43 |
Power | 取数值表达式的幂值 | Select Power(5,2) 返回:25 |
Round | 将数值表达式四舍五入为指定精度 | Select Round(43.543,1) 返回43.5 |
Sign | 对于正数返回+1,对于负数返回-1,对于0则返回0 | Select Sign(-43) 返回:-1 |
Sqrt | 取浮点表达式的平方根 | Select Sqrt(9) 返回:3 |
4.3.4 系统函数
系统函数用来获取有关SQL Server中对象和设置的系统信息。
函数名 | 描述 | 举例 |
Convert | 用来转变数据类型 | Select Convert(Varchar(5),12345) 返回:字符串12345 |
Current_User | 返回当前用户的名字 | Select Current_User 返回:你登录的用户名 |
DataLength | 返回用于指定表达式的字节数 | Select Datalength('中国A联盟') 返回:5 |
Host_Name | 返回当前用户所登录的计算机名字 | Select Host_Name() 返回:你所登录的计算机的名字 |
System_User | 返回当前所登录的用户名称 | Select System_User 返回:你当前所登录的用户名 |
User_Name | 从给定的用户ID返回用户名 | Select User_Name(1) 返回:从任意数据库中返回“dbo” |
上面所有这些函数,可以在T-SQL中混合使用,得到符合特殊要求的查询输出。