五、数据查询
文章目录
(〇)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='人民邮电出版社'
)