目录
关于Oracle中的关闭和启动数据库的几种方式(五)
Oracle中含替换变量的查询(二)
选择运算是通过一定条件把需要的数据检索出来。
例如:下面从学生表XSB中将计算机专业的学生输出作为一个新表。
学号 | 姓名 | 专业 |
151101 | 张磊 | 计算机 |
151102 | 李昊 | 通信工程 |
151103 | 晨曦 | 计算机 |
将计算机专业的学生输出作为一个新的表。
学号 | 姓名 | 专业 |
151101 | 张磊 | 计算机 |
151103 | 晨曦 | 计算机 |
投影运算也是单目运算,也就是只是选择表中指定的列,这样可以在查询结果时减少显示的数据量,提高了查询的效果。
学号 | 姓名 | 专业 |
151101 | 张磊 | 计算机 |
151102 | 李昊 | 通信工程 |
151103 | 晨曦 | 计算机 |
对学号和专业进行投影。
学号 | 专业 |
151101 | 计算机 |
151102 | 通信工程 |
151103 | 计算机 |
提示:选择运算是对表的行进行分割,而投影算则是对表进行列的分割。
连接就是将两个表中的行按照给定的条件进行拼接,从而形成新的表。
学号 | 专业 |
151101 | 计算机 |
151102 | 通信工程 |
151103 | 计算机 |
姓名 |
张磊 |
李昊 |
晨曦 |
将上面的表1和表2进行拼接。
学号 | 专业 | 姓名 |
151101 | 计算机 | 张磊 |
151102 | 通信工程 | 李昊 |
151103 | 计算机 | 晨曦 |
SELECT <选择的列>
FROM <表名或者视图名>
[WHERE<条件表达式>]
[GROUP BY<分组表达式>]
[HAVING<分组条件表达式>]
[ORDER BY<排序表达式>][ASC | DESC]
SELECT [ALL | DISTINCT]<列名列表>
SELECT <列名1>[,<列名2>[,...n]]
FROM<表名或者视图名>
WHERE <条件表达式>
例如:选择XSB表中的专业为计算机的学号和姓名
select sid,sname
from XSB
where profession='计算机';
SELECT <表达式>[,<表达式>]]
FROM<表名或者视图名>
如果对表中的数据只选择某些列的时候,容易出现重复的行,比如学生的学号是不会重复的,但是姓名是容易重复的,所以学号作为标识学生唯一的主码。
SELECT DISTINCT <列名>[,<列名>...]
FROM<表名或者视图名>
select DISTINCT sid,sname
from XSB;
=,<,<=,>,>=,<>(不等于),!=(不等于)。
比较运算符的格式:
<表达式1>{= | < | <= | > | >= | <> | !=}<表达式2>
例如:从学生表选择学分大于50分的学生作为输出。
select *
from XSB
WHERE score>50;
LIKE谓词用于指出一个字符串是否与指定的字符串相匹配,其运算对象可以是char类型,varchar2类型和date类型的数据,返回值为TRUE或者FALSE。
LIKE谓词表达式:<字符串表达式1>[NOT] LIKE <字符串表达式2>[ESCAPE'<转义字符>']
注:ESCAPE指定的转义字符必须为单个字符;当模式串中含有与通配符相同的字符时,通过转义字符指明其为模式串中的一个匹配字符。
LIKE的通配符
- “%”和“_”。
- %:通配符的LIKE进行字符串比较,模式字符串中的所有字符都有意义,包括起始或者尾随空格
- _:在模糊条件中表示一个字符
- NOT LIKE和LIKE的作用相反
例子:
- 例如:从中过滤出含有专业含有“通信”的:
- SELECT * FROM XSB WHERE profession LIKE '%通信%';
- 例如:从过滤出姓“张”的(姓名中含两个字的,如果有三个字的话,就需要再加一个“_”)
- SELECT * FROM XSB WHERE trim(sname) LIKE '张_';
- 注:使用trim是为去掉尾部的空格。
- 范围比较使用两个关键字:BETWEEN和AND
- 语法格式:<表达式> [NOT] BETWEEN <表达式1> AND <表达式2>
- 注:当表达式中的值在表达式1和表达式2之间,则返回TRUE,否则返回FALSE
- 例如:SELECT * FROM XSB WHERE score BETWEEN 50 AND 60;
- 使用关键字IN指定一个值表,值表中列出了所有可能的值,当表达式与值表中的任意一个值匹配时,即返回TRUE,否则FALSE。
- 格式:<表达式> IN (<表达式>[,...n])
- 例如:查找学分在50到60之间的学生信息。
- SELECT * FROM WHERE score IN(50,60)
注:从输出结果来说,其实in后面的其实是一个要查找值的列表,如果查找的时候在列表中存在,则输出。
- 当需要判定一个表达式的值是否为空的时候,使用IS NULL关键字
- 格式:<表达式> IS [NOT] NULL
- 例如:查询转义列中为空(null)的学生信息;
- select * from xsb where profession is null;
子查询
- 在查询条件中,可以使用另一个查询的结果作为条件的一部分,如判断列值是否与某个查询的结果集中的值相等,作为查询条件一部分的查询称为子查询
- 格式:<表达式> [NOT] IN (<子查询>)
- 例如:要求从学生成绩表中选择学习的课程为“计算机组成原理”的学生并且在学生表中的学生的信息。
- select *
from xsb
where sid in(
select sid
from xcjb
where lession='计算机组成原理'
);
EXIST子查询
- EXIST谓词用于测试子查询的结果是否为空表,若子查询的结果集不为空表,则EXIST返回TRUE,否则返回FALSE。
- 例如:要求从学生成绩表中选择学习的课程为“计算机组成原理”的学生并且在学生表中的学生的信息。
- select *
from xsb
where exists (
select *
from xcjb
where lession='计算机组成原理' and sid=xsb.sid
);
- 连接是二元运算,可以对两个表或者多个表进行查询,结果通常是含有参加连接运算的两个(或多个)表的指定列的表
- 例如:在学生成绩数据库中查询选修了某个课程号课程的学生姓名,该课课程名和成绩,所需要来自学生表XSB,课程表KCB和成绩表CJB,需要将三个连接起来才能进行查询
- 连接谓词
- 在SELECT语句的WHERE子句中,使用比较运算符给出的连接条件对表进行连接
- 例如:SELECT XSB.sid,XSB.sname FROM XSB,CJB WHERE XSB.sid=CJB.sid
例子:查询课程表中成绩大于80的,并且在学生表中的学生输出相关信息。
select xsb.sid as 学号,xsb.sname as 姓名,xcjb.lession as 课程
from xsb,xcjb
where xsb.sid=xcjb.sid and xcjb.ach>80;
JOIN连接看起来有点多,必须通过实际来理解,主要以例子来理解JOIN连接。
- 格式:<表名><连接类型><表名> ON <条件表达式> | <表名> CROSS JOIN <表名> | <连接表>
- 连接类型::=[INNER | {LEFT | RIGHT | FULL}[ OUTER ] CROSS JOIN]
- 其中:INNER表示内连接,OUTER表示外连接,CROSS JOIN表示交叉连接
- 内连接
- 内连接按照ON所指定的连接条件合并两个表,且返回满足条件的行
- 例如:SELECT * FRMO XSB INNER JOIN CJB ON XSB.sid=CJB.sid;
例子1:将出现在两个表中的学生信息使用“内连接”连接起来并输出结果。
select *
from xsb inner join xcjb
on xsb.sid=xcjb.sid;
例子2: 将出现在两个表中的学生信息使用“内连接”连接起来,并且在成绩表中的学生成绩大于80分以上的学生。
select *
from xsb inner join xcjb
on xsb.sid=xcjb.sid
where xcjb.ach>80;
提示:这里的inner关键字可以去掉,因为系统默认的就是inner。并且inner连接也可以使用在多个表中。
- 外连接
- 外连接的结果表不但包含满足连接条件的行,还包含相应表中的所有行
- 左外连接(LEFT OUTER JOIN):结果表中除了包括满足连接条件的行,还包含左表的所有行
- 右外连接(RIGHT OUTER JOIN):结果表中除了包括满足连接条件的行,还包含右表的所有行
- 完全连接(FULL OUTER JOIN):结果表中除了包括满足连接条件的行,还包含两个表的所有行
- 例如:SELECT XSB.sid FROM XSB LEFT OUTER JOIN CJB ON XSB.sid=CJB.sid
例子1:查询所有学生的学习课程的情况,如果没有学习的课程,那么使用null。
--左外连接
select xsb.*,xcjb.lession,xcjb.ach
from xsb left outer join xcjb
on xsb.sid=xcjb.sid;
例子2:查询已经学习课程的学生情况。
右外连接
select xsb.*,xcjb.lession,xcjb.ach
from xsb right outer join xcjb
on xsb.sid=xcjb.sid;
从左外连接和右外连接的结果来看。对于左外连接来说,如果其中包含未选择课程的学生,依然是要输出的,只不过是用null替代;而对于右外连接来说呢,如果其中包含未选择课程的学生,那么这部分学生的信息将未NULL,也就是没有输出。
提示:外连接只能对于两个表。
- 交叉连接
- 实际上将两个表进行笛卡尔积运算,结果表是由第1个表的每一行与第2个表的每一行拼接后形成的表,所以其行数等于两个表的行数之积
例子:交叉连接的结果其实就是一个表中的行和另一个行进行两两配对,产生的结果,二这些结果中有些是有意义的,而有些是没有意义的,最终产生的表的行数为:NxM.
select xsb.*,xcjb.lession,xcjb.ach
from xsb cross join xcjb;
这部分大家完全可以自己实验一下。
统计函数
- SUM函数和AVG函数
- 格式:SUM/AVG ([ALL | DISTINCT]<表达式>)
- 例如:SELECT AVG(score) AS 课程 101 平均成绩 FROM CJB WHERE K_id='101';
- MAX函数和MIN函数
- 格式:MAX/MIN([ALL | DISTINCT<表达式>])
- 例如:SELECT MAX(学分) AS 学分 101 的最高分 ,MIN(学分) AS 学生 151101 的最低分 FROM XSB WHERE sid='1511101';
- COUNT函数
- 统计组中满足条件的行数或者总行数
- 格式:COUNT({[ALL | DISTINCT]<表达式>}| *)
- 例如:SELECT COUNT(*) AS 人数 FROM XSB;
- 用于对表或者视图中的数据按字段分组
- 格式:GROUP BY [ALL <分组表达式>[,...n]]
- 例如:SELECT profession FROM XSB GROUP BY profession;
例子:按学生的学号进行分组
select sid
from xsb
group by sid;
例子2:求各个专业的学生人数
select profession as 专业,count(*) as 学生数
from xsb
group by profession;
HAVING子句
- 使用GROUP BY子句和统计函数对数据进行分组后,还可以使用HAVING子句对分组数据做进一步的筛选
- 格式:HAVING <条件表达式>
- 例如:SELECT sid,AVG(score) AS 平均成绩 FROM XSB GROUP BY sid HAVING AVG(score)>40
--按学号分组之后,平均学分大于50的学生
select sid as 学号
from xsb
group by sid
having avg(score)>50;
提示:HAVING和WHERE区别,HAVING可以使用统计函数,而WHERE不可以使用。
- 对查询的结果进行排序
- 格式:[ORDER BY] {<排序表达式> [ASC | DESC] }[,...n]
- 其中ASC表示升序;DESC表示降序。
- SELECT * FROM XSB WHERE profession='计算机' ORDER BY score;
例子:将专业为计算机的学生按学分从小到大进行排序。
select *
from xsb
where profession='计算机'
order by score;
- 将两个或者多个SELECT 查询结果合并成一个结果集合
- 格式:
- 例如:SELECT * FROM XSB WHERE score>50 UNION SELECT * FROM XSB WHERE sid>'151201';
- 注意
- 所有查询中列数和列的顺序必须相同
- 数据类型必须兼容
- ALL关键字表示合并的结果中包含所有行,不去除重复行;不使用ALL表示结果中去除重复的行。
例子:将学分大于50和学号大于151201的学生进行合并。
SELECT *
FROM XSB
WHERE score>50
UNION
SELECT *
FROM XSB
WHERE sid>'151201';