【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='人民邮电出版社'
)
  • 5
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
实验数据库的建立和维护实验 基于实验一建立的 “图书读者数据库”(Book_Reader_DB),使用SQL Server 2000企业管理器和在SQL Server 2000查询分析器的Transact-SQL语句输入区输入Transact-SQL语句的两种方法,实现对Book_Reader_DB中 “图书”、“读者”和“借阅”三个表的数据插入、修改和删除。 1、使用SQL Server 2000企业管理器对三个表进行数据更新 在“图书读者数据库”(Book_Reader_DB)中,点击“表”结点对应的表,然后点击鼠标右键中的“打开表”→“返回所有行”,如图1所示,将显示对应表中的所有数据,如图2所示: 图1 SQL Server 2000控制台界面 图2 SQL Server 2000中显示表中数据 根据上述打开表的步骤,将对应的表打开后,在表中输入或修改下列数据,并删除一些过时的数据图书表 书号 类别 出版社 作者 书名 定价 数量 购买日期 备注 0001 计算机 清华大学出版社 严蔚敏 数据结构 15.00 10 1999-05-08 0002 计算机 清华大学出版社 单建魁 数据库系统实验指导 15.00 30 2000-03-01 0003 环境 南京大学出版社 王小容 环境化学 32.00 20 2000-06-09 0004 环境 清华大学出版社 何强 环境学导论 35.00 20 2000-06-12 0005 计算机 电子工业出版社 傅清祥 算法与数据结构 36.70 10 2000-07-15 0006 计算机 清华大学出版社 蔡自兴 人工智能及其应用 28.00 10 2000-09-17 …… …… …… …… …… …… …… …… …. 读者表 编号 姓名 单位 性别 电话 0001 张红 电气学院 女 01032324565 0002 杨小鹏 信息学院 男 01032333667 0003 王俊 信息学院 男 01043546789 0004 刘建 建筑学院 男 01023242526 …… …… …… …… …… 借阅表 书号 读者编号 借阅日期 还书日期 0001 0002 2001-9-2 2001-10-1 0002 0002 2001-9-12 2001-10-11 0006 0002 2002-1-12 2002-2-1 0003 0001 2001-7-10 2001-8-7 0004 0001 2001-7-2 2001-7-22 0005 0001 2001-9-15 2001-9-15 0002 0003 2001-10-15 2001-11-1 0006 0003 2002-2-10 2002-2-20 0003 0004 2001-8-17 2001-8-29 0004 0004 2001-7-25 2001-8-15 …… …… …… …… 2、使用SQL Server 2000中的查询分析器的Transact-SQL语句输入区输入Transact-SQL语句对三个表进行数据更新。 打开SQL Server 2000的查询分析器(注意要对数据库Book_Reader_DB进行数据更新),在Transact-SQL语句输入区中输入Transact-SQL语句实现对三个表进行数据更新。输入的数据如下所示表所示。在插入、删除或修改表中数据时,特别要注意SQL语句的格式、输入数据的类型,插入、删除或修改的数据要确保数据之间的关联以及在实验一建立表时所设定的一些约束。 上述每项实验内容相应的实验步骤必须进行详细的记录,并将其整理后写在实验报告中。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值