第3章 关系数据库标准语言SQL——3.4 数据查询（上）

3.4  数据查询（上）

     数据查询是数据库的核心操作。SQL提供了select语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：

select [all|distinct] <目标列表达式> [,<目标列表达式>]···
from <表名或视图名> [,<表名或视图名>···]|(<select语句>)[as]<别名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc|desc]];

     整个select语句的含义是，根据where子句的条件表达式从from子句指定的基本表、视图或派生表中找出满足条件的元组，再按select子句中的目标列表达式选出元组中的属性值形成结果表。

     如果有group by子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果group by子句带having短语，则只有满足指定条件的组才予以输出。

     如果有order by子句，则结果表还要按<列名2>的值的升序或降序排序。

3.4.1  单表查询

     单表查询是指仅涉及一个表的查询。

01 选择表中的若干列

     选择表中的全部或部分列即关系代数的投影运算。

（1）查询指定列

例3.16  查询全体学生的学号与姓名。

select Sno,Sname
from Student;

例3.17  查询全体学生的姓名、学号、所在系。

select Sname,Sno,Sdept
from Student;

       <目标列表达式>中各个列的先后顺序可以与表中的顺序 不一致。 用户可以根据应用的需要改变列的显示顺序。

（2）查询全部列

     将表中的所有属性列都选出来有两种方法，一种方法就是在select关键字后列出所有列名；如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为*。

例3.18  查询全体学生的详细记录。

select * from Student；

等价于

select Sno,Sname,Ssex,Sage,Sdept from Student;

（3）查询经过计算的值

     select子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式。

例3.19  查询全体学生的姓名及其出生年份。

select Sname,2014-Sage from Student; 
            /*查询结果的第2列是算术表达式*/

例3.20  查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

select Sname,’Year of Birth:’,2014-Sage,LOWER(Sdept) from Student;

     用户可以通过指定别名来改变查询结果的列标题，例如对于例3.20可以定义如下列别名：

select Sname NAME,’Year of Birth:’ BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT from Student;

02 选择表中的若干元组

（1）消除取值重复的行

       两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行。可以用distinct消除它们。

例3.21  查询选修了课程的学生学号。

select distinct Sno from SC;

     如果没有指定distinct关键词，则默认为all，即保留结果表中取值重复的行。

select Sno from SC;

等价于                

select all Sno from SC;

（2）查询满足条件的元组

     查询满足指定条件的元组可以通过where子句实现。where子句常用的查询条件如表3.6所示。

表3.6  where子句常用的查询条件

     ①比较大小

     用于进行比较的运算符一般包括=（等于），>（大于），<（小于），>=（大于等于），<=（小于等于），!=或<>（不等于），!>（不大于），!<（不小于）。

例3.22  查询计算机科学系全体学生的名单。

select Sname from Student where Sdept = ‘CS’;

例3.23  查询所有年龄在20岁以下的学生姓名及其年龄。

select Sname,Sage from Student where Sage < 20;

例3.24  查询考试成绩不及格的学生的学号。

select distinct Sno from SC where Grade < 60;

     ②确定范围

     谓词between···and···和not between···and···可以用来查找属性值在（或不在）指定范围内的元组，其中between后是范围的下限（即低值），and后是范围的上限（即高值）。

例3.25  查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

select Sname,Sdept,Sage from Student where Sage between 20 and 23;

例3.26  查询年龄不在20~23岁之间的学生姓名、系别和年龄。

select Sname,Sdept,Sage from Student where Sage not between 20 and 23;

    ③确定集合

     谓词in可以用来查找属性值属于指定集合的元组。

例3.27  查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

select Sname,Ssex from Student where Sdept in (‘CS’,’MA’,’IS’);

例3.28  查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

select Sname,Ssex from Student where Sdept not in (‘CS’,’MA’,’IS’);

     ④字符匹配

     谓词like可以用来进行字符串的匹配。其一般语法格式如下：

[not] like ‘<匹配串>’ [escape ‘<换码字符>’]

     其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：

>>>>  %（百分号）代表任意长度（长度可以为0）的字符串。
      >>>>  _（下横线）代表任意单个字符。

例3.29  查询学号为201215121的学生的详细情况。

select * from Student where Sno like ‘201215121’;

等价于

select * from Student where Sno = ‘201215121’;

     如果like后面的匹配符中不含通配符，则可以用=（等于）运算符取代like谓词，用！=或<>（不等于）运算符取代not like谓词。

例3.30  查询所有姓刘的学生的姓名、学号和性别。

select Sname,Sno,Ssex from Student where Sname like ‘刘%’;

例3.31  查询姓“欧阳”且全名为三个汉字的学生的姓名。

select Sname from Student where Sname like ‘欧阳_’;

      注意：数据库字符集为ASCII时一个汉字需要两个_；当字符集为GBK时只需要一个_。

例3.32  查询名字中第二个字为“阳”的学生的姓名和学号。

select Sname,Sno from Student where Sname like ‘_阳%’;

例3.33  查询所有不姓刘的学生的姓名、学号和性别。

select Sname,Sno,Ssex from Student where Sname not like ‘刘%’;

      如果用户要查询的字符串本身就含有通配符%或_，这时就要使用escape ‘<换码字符>’短语对通配符进行转义了。

例3.34  查询DB_Design课程的课程号和学分。

select Cno.Ccredit from Course where Cname like ‘DB\_Design’ escape ‘\’;

      escape ‘\’表示“\”为换码字符。这样匹配串中紧跟在“\”后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符。

例3.35  查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况。

select * from Course where Cname like ‘DB\_%i_­_’ escape ‘\’;

     ⑤涉及空值的查询

例3.36  某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

select Sno,Cno from SC where Grade is null;

     注意：这里的“is”不能用等号（=）代替。

例3.37  查所有有成绩的学生学号和课程号。

select Sno,Cno from SC where Grade is not null;

     ⑥多重条件查询

     逻辑运算符and和or可用来连接多个查询条件。and的优先级高于or，但用户可以用括号改变优先级。

例3.38  查询计算机科学系年龄在20岁以下的学生姓名。

select Sname from Student where Sdept = ‘CS’and Sage < 20;

03 order by子句

       用户可以用order by子句对查询结果按照一个或多个属性列的升序（asc）或降序（desc）排列，默认值为升序。

例3.39  查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排序。

select Sno,Grade from SC where Cno = ‘3’order by Grade desc;

      对于空值，排序时显示的次序由具体系统实现来决定。例如按升序排，含空值的元组最后显示；按降序排，空值的元组则最先显示。各个系统的实现可以不同，只要保持一致就行。

例3.40  查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

select * from Student order by Sdept,Sage desc;

04 聚集函数

     为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有：

count(*)                               统计元组个数
count([distinct|all]<列名>)     统计一列中值的个数
sum([distinct|all]<列名>)      计算一列值的总和（此列必须是数值型）
avg([distinct|all]<列名>)      计算一列值的平均值（此列必须是数值型）
max([distinct|all]<列名>)      求一列值中的最大值
min([distinct|all]<列名>)      求一列值中的最小值

     如果指定distinct短语，则表示在计算时要取消指定列中的重复值。如果不指定distinct短语或指定all短语（all为默认值），则表示不取消重复值。

例3.41  查询学生总人数。

select count(*) from Student；

例3.42  查询选修了课程的学生人数。

select count(distinct Sno) from SC;

例3.43  计算选修1号课程的学生平均成绩。

select avg(Grade) from SC where Cno = ‘1’;

例3.44  查询选修1号课程的学生最高分数。

select max(Grade) from SC where Cno = ‘1’;

例3.45  查询学生201215012选修课程的总学号数。

select sum(Ccredit) from SC,Course
where Sno = ‘201215012’ and SC.Cno = Course.Cno;

      当聚集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值。count(*)是对元组进行计数，某个元组的一个或部分列取空值不影响count的统计结果。

      注意：where子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于select子句和group by中的having子句。

05 group by子句

     group by子句将查询结果按某一列或多列的值分组，值相等的为一组。

     对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

例3.46  求各个课程号及相应的选课人数。

select Cno,count(Sno) from SC group by Cno;

       如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用having短语指定筛选条件。

例3.47  查询选修了三门以上课程的学生学号。

select Sno from SC group by Sno having count(*) > 3;

       where子句与having短语的区别在于作用对象不同。where子句作用于基本表或视图，从中选择满足条件的元组。having短语作用于组，从中选择满足条件的组。

例3.48  查询平均成绩大于等于90分的学生学号和平均成绩。

select Sno,avg(Grade) from SC group by Sno having avg(Grade) >= 90;

3.4.2  连接查询

     前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询时关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

01 等值与非等值连接查询

     连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

     其中比较运算符主要有=、>、<、>=、<=、!=(或<>)等。

     此外连接谓词还可以使用下面形式：

[<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>

     当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。

       连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

例3.49  查询每个学生及其选修课程的情况。

select Student.*,SC.* from Student,SC where Student.Sno = SC.Sno;

     若在等值连接中把目标列中重复的属性列去掉则为自然连接。

例3.50  对例3.49用自然连接完成。

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC where Student.Sno = SC.Sno;

     一条SQL语句可以同时完成选择和连接查询，这时where子句是由连接谓词和选择谓词组成的复合条件。

例3.51  查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

select Student.Sno,Sname from Student,SC
where Student.Sno = SC.Sno and SC.Cno = ‘2’ and SC.Grade > 90;

02 自身连接

     连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

例3.52  查询每一门课的间接先修课（即先修课的先修课）。

select first.Cno,second.Cpno from Course first,Course second
where first.Cpno = second.Cno;

03 外连接

     把舍弃的元组也保存在结果关系中，而在其他属性上填null，叫做外连接。其一般格式为

select <列名> from <表名1>  [outer] join <表名2> on <连接条件>

例3.53

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join SC on (Student.Sno = SC.Sno);

    左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。

04 多表连接

     连接操作除了可以是两表连接、一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。

例3.54  查询每个学生的学号、姓名、选修的课程名及成绩。

select Student.Sno,Sname,Cname,Grade from Student,SC,Course
where Student.Sno = SC.Sno and SC.Cno = Course.Cno;