【T-SQL】五、数据查询

五、数据查询

（〇）select语句

语法格式

SELECT [ALL|DISTINCT] [TOP n PERCENT ] 表达式列表
[ INTO 新表名 ] 
FROM 基本表|视图名列表 
[ WHERE条件查询 ] 
[ GROUP BY 分组列名表 ] 
[ HAVING 逻辑表达式] 
[ ORDER BY 排序列名表 [ ASC | DESC ] ]

（一）基本语句

0.格式

SELECT [ALL|DISTINCT] [TOP n PERCENT ] 表达式1，表达式2，…，表达式n

说明

• 用逗号分隔的表达式列表，用来描述查询结果集的列。
• 表达式可以由列名、常量、函数和运算符构成。
• 其它选项在实例中讲解。

1.按照列名对表进行投影查询

-- 从教务管理数据库EDUC的学生表Student中查询出男生的编号、姓名和性别三列的记录
use EDUC
select SID,Sname,Sex
from Student
where Sex='男'

2．TOP关键字限制返回行数

格式 TOP n [PERCENT]

-- 从图书管理数据库Library的图书表Book中查询出前5条纪录。
use Library
select top 5 BID,Bname,Author
from Book

3．PERCENT关键字返回结果集行的百分比

格式 TOP n PERCENT

-- 从教务管理数据库EDUC的学生表Student中查询出前20%的数据行。
use EDUC
select top 20 percent SID,Sname,Sex
from Student

4．是否消除重复数据行

格式 All| Distinct

说明

• All：检出全部信息（默认）
• Distinct：去掉查询结果中重复的数据行。

-- 从教务管理数据库EDUC的学生表Student中查询出专业Specialty的名称。
use EDUC
select distinct Specialty
from Student

5．使用通配符*投影所有列

格式： *

说明：

• 要投影表中所有的列并且不需要改变其顺序，可不必列出表中所有列名，用通配符“*”替代所有字段。

-- 从图书管理数据库Library的读者类型表ReaderType中查询所有记录
use Library
select *
from ReaderType

6．使用表达式计算列值

格式 表达式1, 表达式2, …,表达式n

说明

• 在SELECT子句中可以使用加（+）、减（-）、乘（*）、除（/）、取模（%）和字符连接（+）等运算符及各种函数构成表达式，通过对表达式的计算来获取查询结果的列值。

提示

• 对表达式列中的计算只影响查询结果，不会改变表中的数据。

-- 从图书管理数据库Library的图书表“Book”中查询所有图书折价90%后的价格
use Library
select BID,Bname,Author,PubComp,Price,Price*0.9
from Book

-- 查询出折价后的数据保持小数点2位，并在其后添加单位“元”
use Library
select BID,Bname,Author,PubComp,Price,str(Price*0.9,6,2)+'元'
from Book

备注：Str(浮点表达式,长度,小数)函数，该函数返回由数字数据转换来的保留指定小数位数的字符数据

7．使用单独常量作为投影表达式

-- 从图书管理数据库Library中读者类型表ReadType中查询出所有数据。
use Library
select TypeID,Typename,LimitNum,'册',LimitDays,'天'
from ReaderType

说明：SELECT语句中的‘册’、‘天’均为字符串常量

8．自定义列名

格式 ’指定的列标题’=列名 或 列名 AS 指定的列标题

-- 在上例中用中文显示列名
use Library
select TypeID as 类型编号,Typename as 类型名称,LimitNum as 限借数量,LimitDays as 限借天数
from ReaderType

说明：

• 自定义列标题后，在查询结果的标题位置将显示指定的列标题，而不是表中定义的列名，指定的列标题是一个字符串，可以用单引号括起来，也可不用。
• 关键字“AS”也可省略。
• 对于表达式计算出的列，如果没有指定列标题，则以“无列名”标识，这样的情况可以为查询结果重新指定列标题。

（二）聚集函数

SELECT中的列名表达式处还可以使用聚集函数（也叫列函数）。包括如下：

• 求和：SUM
• 平均：AVG
• 最大：MAX
• 最小：MIN
• 统计：COUNT

格式：函数名（[All| Distinct] 列名表达式|*）

-- 从图书管理数据库Library中图书表Book中统计出高等教育出版社出版的图书数量
use Library
select count(*) as 册数
from Book
where PubComp='高等教育出版社'

-- 从图书管理数据库Library中图书表Book中统计出版社的个数
use Library
select count(distinct (PubComp)) as 出版社个数
from Book

-- 从图书管理数据库Library中图书表Book中查询出中图书的总册数、最高价、最低价、总价值、折扣后的总价值和平均价
use Library
select count(BID) as 总册数, max(Price) as 最高价, min(Price) as 最低价, sum(Price) as 总价值, str(sum(Price*0.9),8,2) as 折扣后的总价值, str(avg(Price),6,2) as 平均价
from Book

（三）where子句

格式 WHERE逻辑表达式

1．关系运算符

=	等于	<>	不等于
>	大于	>=	大于等于
<	小于	<=	小于等于

-- 从学生表中查询出“杨静”的信息
use EDUC
select *
from Student
where Sname='杨静'

-- 从学生表中查询出女生的信息
use Library
select *
from Student
where Sex='女'

-- 从学生表中查询出到2020年满31岁的学生的信息，假设系统日期为2020年
use EDUC
select * 
from Student
where datepart(year,getdate())-datepart(year,Birthday)+1>31

2．逻辑运算符

not	非
and	与
or	或

-- 从学生表中查询出到2020年满31岁的女生的信息，假设系统日期为2020年
use EDUC
select * 
from Student
where datepart(year,getdate())-datepart(year,Birthday)+1>31 and Sex='女'

-- 从学生表中查询出到2020年不满33岁的男生的信息，假设系统日期为2020年
use EDUC
select *
from Student
where not(datepart(year,getdate())-datepart(year,Birthday)+1>33) and Sex='男'

3．范围运算符

格式 列名 [not] between 开始值 and 结束值

说明：

• 指定列名是否在开始值和结束值之间
• between 开始值 and 结束值：等效于（列名>=开始值 and 列名<=结束值）
• not between 开始值 and 结束值：等效于（列名<开始值 or 列名>结束值）

-- 从图书表中查询出定价在20元到25元之间的图书信息
use Library
select *
from Book
where Price between 20 and 25
-- 等价于 Price>=20 and Price <=25

4．模式匹配运算符

语法 [NOT] LIKE 通配符

说明：

• 通配符_：一个任意字符；
• 通配符％：任意多个任意字符
• 模式匹配运算符LIKE用于实现对表的模糊查询。

-- 在学生表中查询出姓“王”的所有学生的信息
use EDUC
select *
from Student
where Sname like '王%'

-- 从图书管理数据库Library中图书表Book中查询出有关SQL方面的图书
use Library
select *
from Book
where Bname like '%SQL%'

5．列表运算符

语法 表达式〔NOT〕IN (列表|子查询)

说明

• 表达式的值（不在）在列表所列出的值中，子查询的应用在后面会介绍

-- 查询学号为2009051001和2009051002的学生的信息
use EDUC
select *
from Student
where Sname in ('2009051002','2009051001')

6．空值判断符

格式 IS [NOT] NULL

说明

• 在数据库的表中，除了必须具有值得列不允许为空外，许多列可以没有输入值，则该列的值为空（NULL）

-- 表中查询出没有还书的读者信息
use Library
select Borrow.RID,Rname,BID
from Borrow,Reader
where ReturnDate is null and Borrow.RID=Reader.RID

（四）group by 子句

格式 GROUP BY 列名
功能：与列名或列函数配合实现分组统计。
注意：投影列名必须出现相应的GROUP BY列名。

-- 从图书表中查询各出版社图书的总价
use Library
select PubComp as 出版社, sum(Price) as 总价
from Book
group by PubComp

-- 从学生表中查询各专业的学生总数，要求查询结果显示专业名称和人数两个字段
use EDUC
select Specialty,count(SID)
from Student
group by Specialty

-- 从选课表中查询每位学生的总成绩，要求查询结果显示学生学号（SID）、姓名和总成绩
use EDUC
select SC.SID,Student.Sname,sum(Grade)
from SC,Student
where SC.SID=Student.SID
group by SC.SID,Student.Sname

（五）having子句

格式 HAVING 逻辑表达式

功能：与GROUP BY选项配合筛选（选择）统计结果。

说明：通常用列函数作为条件，列函数不能放在WHERE中

-- 从选课表中查询总分超过150分的学生的学号、姓名和总成绩
use EDUC
select SC.SID,Student.Sname,sum(Grade) as 总成绩
from SC,Student
where SC.SID=Student.SID
group by SC.SID,Student.Sname
having sum(grade)>150

（六）order by子句

格式 ORDER BY 列名表达式表 ASC/ DESC

功能：按列名表升序（ASC）或降序（DESC）排序。

说明：

• 只能在外查询中使用。
• 如果ORDER BY子句后不是一个列名表达式，而是多个列名表达式表，则系统将根据各列的次序决定排序的优先级，再排序。
• 如果指定了SELECT DISTINCT（去掉重复行），那么ORDER BY子句中的项就必须出现在SELECT子句的列表中。

-- 从选课表SC和学生表Student中统计出每位同学的总成绩，并将结果按照总成绩降序排序
use EDUC
select SC.SID,Student.Sname,sum(Grade) as 总成绩
from SC,Student
where SC.SID=Student.SID
group by SC.SID,Student.Sname
order by sum(Grade) desc

-- 从教务管理数据库“EDUC”中查询出每个学生的选课门数并按选课门数的多少进行升序排序
use EDUC
select SID as 学号,count(*) as 选课门数
from SC
group by SID
order by count(*) asc

-- 从图书表Book中查询图书信息，并按照出版社名称升序和价格降序排列
use Library
select BID,PubComp,Price
from Book
order y PubComp asc,Price desc

（七）谓词连接

格式 FROM基本表名1|视图1，基本表名2|视图2，…基本表名n|视图n

功能：

• 指定要查询的基本表或视图，如果指定了一个以上的基本表或视图，则计算他们之间的笛卡儿积，与WHERE子句等值条件配合实现连接查询。

1.指定基本表

-- 从教务管理数据库EDUC中查询出学生的学号、姓名、所选课程名和成绩信息
use EDUC
select Student.SID,Sname,Cname,Grade
from Student,SC,Course
where Student.SID=SC.SID and SC.CID=Course.CID

2．为基本表指定临时别名

格式 基本表名 [AS] 别名

功能：简化表名，实现自连接

-- 同上例，为基本表Student,Course,SC指定别名为X、Y和Z
use EDUC
select X.SID,Sname,Cname,Grade
from Student as X,SC as Y,Course as Z
where X.SID=Y.SID and Y.CID=Z.CID

（八）join链接

格式

SELECT 列名列表 FROM ｛表名1[连接类型] JOIN 表名2 ON 连接条件｝ WHERE 逻辑表达式

说明：

FROM…JOIN…ON实现表与表的两两连接，表1和表2连接后还可以连接表3……表n，最多可连接64个表或视图。

连接条件放在ON关键字后，其中的连接类型如下：

• INNER JOIN：内连接
• LEFT [OUTER] JOIN：左外连接
• RIGHT [OUTER] JOIN：右外连接
• CROSS JOIN：交叉连接

1．内连接

格式 from 表名1 INNER JOIN 表名2 ON 连接表达式

内连接又分为等值连接和自然连接两种

（1）等值连接

-- 查询每个读者的详细信息（读者信息以及借阅图书信息），允许有重复列
use Library
select Reader.*,Borrow.*
from Reader inner join Borrow
on Reader.RID=Borrow.RID

（2）自然连接

-- 查询每个读者的详细信息（读者信息以及借阅图书信息），不允许有重复列
use Library
select Reader.RID,Reader.RID,ReaderType.TypeID,ReaderType.Typename,BID,LendDate,ReturnDate
from Reader
inner join Borrow on Reader.RID=Borrow.RID
inner join ReaderType on Reader.TypeID=ReaderType.TypeID

2．外连接

外连接返回FROM子句中指定的至少一个表或视图中的所有的行，只要这些行符合任何WHERE选择或者HAVING限定条件。

外连接又分为左外连接、右外连接和全外连接。

• 左外连接对连接中左边的表不加限制；
• 右外连接对连接中右边的表不加限制；
• 全外连接对两个表都不加限制，两个表中的所有行都会包含在结果集中。

（1）左外连接

格式 from 表名1 left outer join 表名2 on 连接表达式

连接结果保留表1中没形成连接的行，表2项应的各列为NULL。

-- 左外连接：从表Reader和表Borrow中查询出读者的借阅情况，包括没有借书的读者情况
use Library
select Reader.*,Borrow.RID,BID
from Reader left outer join Borrow
on Readre.RID=Borrow.RID

（2）右外连接

格式 from 表名1 right outer join 表名2 on 连接表达式

连接结果保留表2中没形成连接的行，表1项应的各列为NULL。

-- 读者和借阅右外连接:从表book和表Borrow中查询出图书被借阅的情况，包括没有被借阅的图书情况
use Library
select *
from Borrow right outer join Book
on Borrow.BID=Book.BID

（3）全外连接

格式 from 表名1 full outer join 表名2 on 连接表达式

加入表1没形成连接的元组，表2列为NULL，

加入表2没形成连接的元组，表1列为NULL。

-- 借阅和读者全外连接
use Library
select *
from Borrow full outer join Reader
on Reader.RID=Borrow.RID

3．自连接

表可以通过自连接实现自身的连接运算。

自连接可以看作是一张表的两个副本之间的联系。

在自连接中，必须为表指定两个不同的别名，使之在逻辑上成为两张表。

-- 自连接:从数据库EDUC中的选课表SC中查询出选了至少两门课程的学生的学号
use EDUC 
select X.SID
from SC as X,SC as Y
where X.SID=Y.SID and X.CID<>Y.CID

4.交叉连接

交叉连接也叫非限制连接，它将两个表不加任何限制地组合起来。没有WHERE子句的交叉连接将产生连接所指定的表的笛卡儿积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的行数，因此可能产生庞大的结果集。

交叉连接的语法形式：

格式 from 表名1 cross join 表名2 on 连接表达式

-- 读者和图书借阅交叉连接，显示查询结果的前5行
use Library
select top 5 *
from Reader cross join Borrow

（九）嵌套查询

嵌套查询指在一个SELECT查询语句的WHERE子句中包含另一个SELECT查询语句，或者将一个SELECT查询语句嵌入在另一个语句中成为其中的一部分。

在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，可描述复杂的查询条件，也称为嵌套查询。

1.in关键字

格式 列名 [not] in (常量表)|(子查询)

说明：

• 列值被包含或不（not）被包含在集合中。
• 等价：列名=any(子查询)
• 当没有用EXISTS引入子查询时，在子查询的选择列表中只能指定一个表达式。

-- 查询借阅“高等教育出版社”出版的图书的读者编号（不包括重复的列）
use Library
select distinct RID
from Borrow
where BID in(
	select BID
	from Book
	where PubComp='高等教育出版社'
)

-- 查询没有借过书的读者的信息
use Library
select *
from Reader
where RID not in(
	select distinct RID
	from Borrow
)

2.比较运算符

格式 列名 比较符 ALL（子查询）

说明：子查询中的每个值都满足比较条件。

-- 查询SC表中分数最高的学生课程情况
use EDUC
select *
from SC
where Grade>=all(
	select Grade from SC
)

3.any或some操作符

格式 列名 比较符 ANY|SOME（子查询）

说明：

• 子查询中的任何一个值满足比较条件，表达式即为真。
• 比较运算符为“=”时，“列名=ANY（子查询）”和“列名IN（子查询）”所描述的条件是一致的。
• ANY和SOME的用法相同。

-- 查询选修C程序设计课程的学生
use EDUC
select Sname,Grade
from Student as X inner join SC as Y
on X.SID=Y.SID
where CID=any(
	select CID 
	from Course
	where Cname='C语言程序设计'
)

4.exists操作符

格式 [NOT]EXISTS（子查询）

说明：

• EXISTS表示存在量词，当查询的结果不为空时，返回真。
• NOT EXISTS与EXISTS相反。
• 在EXISTS引入子查询时，在子查询的选择列表中可以指定多个表达式。

-- 查询选修至少一门课（此学号在选修表中存在）的学生情况
use EDUC
select *
from Student
where exists(
	select *
	from SC
	where Student.SID=SC.SID
)

-- 从图书管理数据库“Library”中，用EXISTS子查询来实现,查询借阅了人民邮电出版社的图书的读者编号
USE Library
select distinct RID
from Borrow
where exists(
	select *
	from Book
	where Borrow.BID=Book.BID and PubComp='人民邮电出版社'
)