单表查询
单表查询是指仅涉及一个表的查询。参考书目:《数据库系统概论》第5版 王珊著 实验环境:ORACLE 实验语言:SQL
文章目录
前言
SELECT语句既可以完成简单的单表查询,也可以完成复杂的连接查询和嵌套查询。下面以学生-课程数据库为例说明SELECT语句在单表查询中的各种用法。一、选择表中的若干列
选择表中的全部或部分列即关系代数的投影运算。
1.查询指定列
可以通过在SELECT子句的<目标列表达式>中指定要查询的属性列。
【例】查询全体学生的学号与姓名。
select sno,sname
from student;
【例】查询全体学生的姓名、学号、所在系。
select sname,sno,sdept
from student;
<目标列表达式>中各个列的先后顺序可以与表中的顺序不一致。用户可以根据应用的需要改变列的显示顺序。
2.查询全部列
- 在SELECT关键字后列出所有列名。
- 列的显示顺序与其在基表中的顺序相同,则可以使用 * 作为目标表达式。
【例】查询全体学生的详细记录。
select *
from student;
等价于
select sno,sname,ssex,sage,sdept
from student;
3.查询经过计算的值
SELECT子句的<目标列表达式>可以是表中的属性列,也可以是表达式。
【例】查询全体学生的姓名及其出生年份。
select sname,2021-sage /*查询结果的第2列是一个算术表达式*/
from student;
查询结果如下:
<目标列表达式>不仅可以是算术表达式,还可以是字符串常量、函数等。
【例】查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。
select sname,'Year of Birth:',2021-sage,lower(sdept)
from student;
查询结果如下:
用户可以通过指定别名来改变查询结果的列标题。
【例】查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。
select sname,2021-sage birthday,lower(sdept) department
from student;
查询结果如下:
二、选择表中的若干元组
1.消除取值重复的行
【例】查询选修了课程的学生学号。
select sno from sc;
执行上方的SELECT语句后,查询结果将包含许多重复的行。
这是因为两个本来并不完全相同的元组在投影到指定的某些列上后,可能会变成相同的行。此时可以用DISTINCT消除取值重复的行。如果没有指定DISTINCT关键词,则默认为ALL,即查询结果中保留取值重复的行。
select distinct sno
from sc;
该SQL语句执行后,查询结果如下:
我在写SQL命令时,其实会更习惯使用UNIQUE关键词,通常情况下也能实现消除取值重复的行。但在课本中和课堂上,都没有提及UNIQUE。那么这两者有什么区别吗?查询了某度后,得到了以下结论:
UNIQUE 和 DISTINCT 的区别
在SQL语法里面,有UNIQUE和DISTINCT两个关键字,UNIQUE是DISTINCT的同义词,功能完全相同。
DISTINCT是标准语法,其他数据库 SQL Server,DB 2,ORACLE,SYBASE,MYSQL等都支持。
UNIQUE,INFORMIX数据库认识,其他数据库有的认识,有的不认识。
以后方便数据库移植,推荐使用DISTINCT。
2.查询满足条件的元组
查询满足指定条件的元组可以通过WHERE子句实现。
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>(不等于),!>,!<;NOT+上述比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIEK,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
多重条件(逻辑运算) | AND,OR,NOT |
2.1比较
【例】查询CS系全体学生的名单。
select sname
from student
where sdept='CS';
【例】查询所有年龄在20岁以下的学生姓名及其年龄。
select sname,sage
from student
where sage<20;
【例】查询考试成绩不及格的学生的学号。
select distinct sno
from sc
where grade<60;
2.2确定范围
BETWEEN <范围的下限> AND <范围的上限>
【例】查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
select sname,sdept,sage
from student
where sage between 20 and 30;
2.3确定集合
【例】查询既不是CS系、MA系学生,也不是IS系学生的姓名和性别。
select sname,ssex
from student
where sdept not in ('CS','MA','IS');
2.4字符匹配
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符串>’]
<匹配串>可以是一个完整的字符串,也可以含有通配符%和_。
- %:代表任意长度的字符串。如a%b表示以a开头,以b结尾的任意长度的字符串。如ab、acb、accb等都满足该匹配串。
- _:代表任意单个字符。如a_b表示以a开头,以b结尾的长度为3的字符串。如acb、adb等都满足该匹配串。
【例】查询学号为201215121。
select *
from student
where sno like '201215121';
等价于
select *
from student
where sno='201215121';
【例】查询所有不姓刘的学生的姓名、学号和性别。
select sname,sno,ssex
from student
where sname not like '刘%';
【例】查询姓“欧阳”却全名为三个汉字的学生的姓名。
select sname
from student
where sname like '欧阳_';
【例】查询名字中第二个字为“阳”的学生的姓名和学号。
select sname
from student
where sname like '_阳%';
当用户要查询的字符本身就含有通配符%或_,这时就要使用ESCAPE '<换码字符>'短语对通配符进行转义。
【例】查询DB_Design课程的课程号和学分。
select cno,ccredit
from course
where cname like 'DB\_Design' ESCAPE '\';
ESCAPE '\'表示为换码字符。这样匹配串中紧跟在“\”后面的字符“”不再具有通配符的含义,转义为普通的“”字符。
【例】查询以“DB_”开头,且倒数第三个字符为i的课程的详细情况。
select *
from course
where cname like 'DB\_%i__' ESCAPE '\';
2.5涉及空值的查询
【例】查询缺考考生的学号和对应的课程号。
select sno,cno
from sc
where grade is null;
2.6多重条件查询
【例】查询CS系年龄在20岁以下的学生姓名。
select sname
from student
where sdpet='CS' and sage<20;
IN谓词实际上是多个OR运算符的缩写。如
select sname,ssex from student where sdept in (‘CS’,‘MA’,‘IS’);
等价于
select sname,ssex
from student
where sdept='CS' or sdept='MA' or sdept='IS';
三、ORDER BY子句
关键词 | 作用 |
---|---|
DESC(默认) | 升序 |
ASC | 降序 |
【例】查询男学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
select *
from student
where ssex='男' order by sdept asc,sage desc;
四、聚集函数
函数 | 作用 |
---|---|
COUNT(*) | 统计元组个数 |
COUNT([ DISTINCT | ALL ] <列名>) | 统计一列中值的个数 |
SUM([ DISTINCT | ALL ] <列名>) | 计算一列值的总和(此列必须是数值型) |
AVG([ DISTINCT | ALL ] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX([ DISTINCT | ALL ] <列名>) | 求一列值中的最大值 |
MIN([ DISTINCT | ALL ] <列名>) | 求一列值中的最小值 |
如果指定DISTINCT短语,则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为默认值),则表示不取消重复值。
当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值。
WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
【例】查询学生总人数。
select count(*)
from student;
【例】查询选修了课程的学生人数。
select count(distinct sno)
from sc;
【例】查询学生201215012选修课程的总学分数。
select sum(ccredit)
from sc
where sno='201215012';
【例】计算选修1号课程的学生平均成绩。
select avg(grade)
from sc
where cno='1';
【例】查询选修1号课程的学生最高分数。
select max(grade)
from sc
where cno='1';
五、GROUP BY子句
GROUP BY子句将查询结果按某一列或多列的值分组,值相等的为一组。分组后聚集函数将作用于每一个组,即每一组都有一个函数值。
【例】求各个课程号及相应的选课人数。
select cno,count(sno)
from sc
group by cno;
该语句对查询结果按Cno的值分组,所有具有相同Cno值的元组为一组,然后对每一组作用聚集函数COUNT进行计算,以求得该组的学生人数。
如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING短语指定筛选的条件。
【例】查询选修了三门以上课程的学生学号。
select sno
from sc
group by sno
having count(*)>3;
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组;HAVING短语作用于组,从中选择满足条件的组。
【例】查询平均成绩大于等于90分的学生学号和平均成绩。
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90;
总结
以上就是本文的所有内容,简单总结了对数据库中的数据如何进行单表查询。