虽然 Oracle 和 sql sever 是不同的数据库,但其一些基础语法还是比较一致的,但细微也有点区别,这里就简单介绍下 Oracle 数据库中 SQL 语言的使用。
目录
用户模式
检索数据
1、简单查询
2、筛选查询
3、多表关联查询
用户模式
在 SQL *Plus 环境中编写较长的 SQL 语句时,按回车键即可实现换行。需要注意的是,在按回车键之前不要输入分号,分号表示 SQL 语句的结束。
在 Oracle 数据库中,为了方便管理用户所创建的数据库对象(如数据表、索引、视图等),引入了模式的概念,这样某个用户所创建的数据库对象就都属于该用户模式。模式是一个数据库对象的集合,为一个数据库用户所有,并且具有与该用户相同的名称,如 system 模式、scott 模式等。在一个模式内部不可以直接访问其他模式的数据库对象,即使在具有访问权限的情况下,也需要指定模式名称才可以访问其他模式的数据库对象。
模式对象是由用户创建的逻辑结构,用以存储或引用数据。简单地说,模式与模式对象之间的关系就是拥有和被拥有的关系,即模式拥有模式对象,而模式对象被模式拥有。一个不属于某个用户所拥有的数据库对象就不能称之为模式对象,如角色、表空间及目录等数据库对象。
这里我们再介绍一个典型的示例模式——scott 模式,该模式及其所拥有的模式对象在后面经常使用到,Oracle 数据库提供的 scott 模式的目的就是为了给用户提供一些示例表和数据来展示 Oracle 数据库的一些特性。scott 模式拥有的模式对象(都是数据表)如下:
emp (员工信息表) |
|
dept (部门表) |
|
empno |
number (4)
|
deptno |
number (2) |
ename |
varchar (10) |
dname |
varchar (14) |
job (职位) |
varchar (9) |
loc (所在位置) |
varchar (13) |
mgr (经理) |
number (4) |
|
|
hiredate (入职日期) |
date |
|
|
sal (基本工资) |
number (7,2) |
|
|
comm (奖金) |
number (7,2) |
|
|
deptno (部门编号) |
number (2) |
|
|
salgr(工资等级表) |
|
bonus (工资表) |
|
grade (工资等级) |
number |
ename |
varchar (10) |
losal (该等级下的最低工资) |
number |
job |
varchar (9) |
hisal (该等级下的最高工资) |
number |
sal |
number |
|
|
comm |
number |
该模式演示了一个很简单的公司人力资源管理的数据结构,它也是 Oracle 的各个版本中一直在沿用的示例模式。
我们可以连接到 scott 用户模式,查询数据字典视图 user_tables 可以获得该模式所包含的数据表,如下:

我们也可以在 system 模式下查询 scott 模式所拥有的数据表,但要求使用 dba_tables 数据表,如下:

别忘了模式名要大写!!
检索数据
这部分和 sql sever 中数据查询的语法差不多一致,这里就简述一下,不过多详细介绍,如果有什么地方不清楚的话,看我前面关于 SQL 语言的博客吧。
1、简单查询
只包含 select 子句和 from 子句的查询就是简单查询,select 子句和 from 子句是 select 语句的必选项,就是说,每个 select 语句都必须包含这两个子句。

上面的查询若要在 system 模式下执行,则需要在表 dept 前面加上 scott ,即 “ scott.dept ”,另外 from 子句的后面可以指定多个数据表,每个数据表名之间用逗号 (,) 分隔开。

使用星号(*) 来检索所有的列,此时的数据将按照表结构的自然顺序来显示。用户也可以指定查询表中的某些列而不是全部列,并且被指定列的顺序不受控制,指定部分列也称作投影操作,这些列名紧跟在 select 关键字后面,每个列名之间用逗号隔开,而使用 select 指定列的好处就是可以改变列在查询结果中的默认显示顺序。
在 Oracle 数据库中,有一个标识行中唯一特性的行标识符,该标识符的名称为 rowid 。行标识符 rowid 是 Oracle 数据库内部使用的隐藏列,由于该列实际上并不是定义在表中的,所以也称为伪列。伪列 rowid 是长度为18的字符,包含了该行数据在 Oracle 数据库中的物理地址。用户使用 describe 命令是无法查到 rowid 列的,但是可以在 select 语句中检索到该列。

在使用 select 语句时,对于数字数据和日期数据都可以使用算术表达式。在 select 语句中可以使用算术运算符,包括 +、-、*、/和括号,另外在 select 语句中不仅可以执行单独的数学运算,还可以执行单独的日期运算以及与列名关联的运算。
在很多的数据表中,其列名都是一些英文的缩写,用户为了方便查看检索结果,可以为这些列指定别名。在 Oracle 系统中,为列指定别名既可以使用 as 关键字,也可以不使用任何关键字而直接指定。

在默认情况下,结果集中包含所有符合查询条件的数据行,这样在结果集中就有可能出现重复数据,而在实际的应用中,这些重复的数据除了占据较大的显示空间外,可能不会给用户带来太多有价值的东西,这样就需要除去重复记录,保留唯一的记录即可。在 select 语句中,同样使用 distinct 关键字来限制在查询结果显示不重复的数据,该关键字用在 select 子句的列表前面。

2、筛选查询
在 select 语句中使用 where 子句可以实现对数据行的筛选操作,只有满足 where 子句中判断条件的行才会显示在结果集中,而那些不满足 where 子句判断条件的行则不包括在结果集中。这种筛选是非常有意义的,通过筛选数据,可以从大量的数据中得到用户所需要的数据。在 select 语句中,where 子句位于 from 子句之后。
1)、比较查询
在 where 子句中使用比较运算符来筛选数据,基本的比较筛选操作主要有一下6种情况:A=B、A!=B或A<>B(都是比较A与B是否不相等)、A<B、A>B、A>=B、A<=B。
另外,除了基本的比较筛选操作外,还有两个特殊的比较筛选操作,具体如下:A {operator} any (B) 表示 A 与 B 中任何一个元素进行 operator 运算符的比较,只要有一个比较值为 true ,就返回数据行;A={operator} all (B) 表示 A 与 B 中的所有元素进行 operator 运算符的比较,只有与所有元素比较值都为 true 时,才返回数据行。

另外需要注意的是:在进行比较筛选的过程中,字符串和日期的值必须使用单引号标识,否则 Oracle 会提示 “标识符无效”。
2)、特殊关键字查询
SQL 语言提供了 like、in、between 和 is null 等关键字来筛选数据,这些关键字的功能分别是匹配字符串、查询目标值、限定值的范围和判断值是否为空等,下面简单来说一下。
使用 like 关键字查询数据的方式也称字符串模式匹配或字符串模糊查询,like 关键字需要使用通配符在字符串内查找指定的模式,所以需要了解常用的通配符。常用的通配符有下划线 “ _ ” ,它代表一个字符;百分号 “ % ” ,它代表任意数量的字符。比如,“ K% ” 表示以字母 K 开头的任意长度的字符串, “ %M% ” 表示包含字母 M 的任意长度的字符串,“ _MRKJ ” 表示5个字符长度且后面4个字符是 MRKJ 的字符串。另外可以在 like 关键字前面加上 not 表示否定的判断,如果 like 为真,则 not like 为假,其他的如 in、between 和 is null 等关键字前面均可通过加上 not 来表示否定判断。

当测试一个数据值是否匹配一组目标值中的一个时,通常使用 in 关键字来指定列表搜索条件。in 关键字的格式是 in(目标1,目标2,目标3,... ),目标值的项目之间必须使用逗号分隔,并且括在括号中。

当需要返回某一个数据值是否位于两个给定的值之间,可以使用范围条件进行检索。通常使用 between...and 和 not between ... and 来指定范围条件。使用 between ... and 查询条件时,指定的第一个值必须小于第二个值,因为 between ... and 实质是查询条件 “大于等于第一个值,并且小于等于第二个值” 的简写形式,等价于比较运算符 (>= ... <=)。

空值(null)从技术上来说就是未知的、不确定的值,但空值与空字符串不同,因为空值是不存在的值,而空字符串是长度为 0 的字符串。因为空值代表的是未知的值,所以并不是所有的空值都相等。比如,student 表中有两个学生的年龄未知,但无法证明这两个学生的年龄相等,这样就不能用 “=” 运算符来检测空值,所以 SQL 引入了一个 is null 关键字来检测特殊值之间的等价性。 is null 关键字通常在 where 子句中使用。

逻辑筛选是指在 where 子句中使用逻辑运算符 and 、or 和 not 进行数据筛选操作,那么这些逻辑运算符可以把多个筛选条件组合起来,以便于用户获取更加准确的数据记录。

3)、分组查询
数据分组的目的是用来汇总数据或为整个分组显示单行的汇总信息,通常在查询结果集中使用 group by 子句对记录进行分组。在 select 子句中,group by 子句位于 from 子句之后,也在 where 条件子句之后。group by 子句可以基于指定某一列的值将数据集合划分为多个分组,同一组内所有记录在分组属性上具有相同值,也可以基于指定多列的值将数据集合划分为多个分组。
group by 子句经常与聚集函数一起使用,如果 select 子句中包含聚集函数,则计算每组的汇总值,当用户指定 group by 时,选择列表中任一非聚集表达式内的所有列都应包含在 group by 列表中,或者 group by 表达式必须与选择列表表达式完全匹配。

having 子句可以对 group by 子句选择出来的结果进行再次筛选,最后输出符合 having 子句中条件的记录,having 子句的语法与 where 子句的语法比较相似,不同的是,having 子句中可以包括聚合函数,如count、avg、sum等,而 where 子句中不可使用。
4)、排序查询
在检索数据时,如果把数据从数据库中直接读取出来,这时查询结果将按照默认顺序排列,但往往这种默认排列顺序并不是用户所需要看到的。尤其返回数据量较大时,用户查看自己想要的信息非常不方便,因此需要对检索的结果集进行排序。在 select 语句中,可以使用 order by 子句对检索的结果集进行排序,该子句位于 from 子句之后(where 之后,以及 group by 子句之后)。
关键字 asc 表示按升序排列,也是默认的排序方式,desc 表示降序排列。另外 order by 子句也可以根据查询结果中的一个列或多个列对查询结果进行排序,并且第一个排序项是主要的排序依据,其次那些是次要的排序依据。

3、多表关联查询
1)、表的别名
在多表关联查询时,如果多个表之间存在同名的列,则必须使用表名来限定列的引用。例如,在 scott 模式中,dept 表和 emp 表都有 deptno 列,那么当用户使用该列关联查询两个表时,就需要通过指定表名来区分这两个列的归属,但随着查询变得越来越复杂,语句就会因为每次限定列必须输入表名而变得冗长乏味,。对于这种情况,SQL 语言提供了设定表别名的机制,通过使用简短的表别名就可以替代原有较长的表名称,这样就可以大大缩减语句的长度。

需要注意的是,一旦在 from 子句中为表指定了别名,则必须在剩余的子句中都使用表的别名,而不允许再使用原来的表名称。
2)、内连接
内连接是一种常用的多表关联查询方式,一般使用关键字 inner join 来实现。其中 inner 关键字可以省略,但只使用 join 关键字时,语句只表示内连接操作。在使用内连接查询多个表时,必须在 from 子句之后定义一个 on 子句,该子句用来指定两个表实现内连接的 “连接条件” 。需要注意的是,在内连接的检索结果中,所有记录行都是满足连接条件的。其连接的语法格式如下:
select columns_list
from table_name1 [inner] join table_name2
on join_condition ;
// columns_list :字段列表
// table_name1 和 table_name2 :两个要实现内连接的表
// join_condition :实现内连接的条件表达式

3)、外连接
使用内连接进行多表查询时,返回的查询结果中只包含符合查询条件和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行,而外连接扩展了内连接的结果集,除了返回有匹配的行外,还会返回一部分或全部不匹配的行,这主要取决于外连接的种类。外连接通常有以下三种:1)、左外连接:关键字为 left outer join 或 left join ;2)、右外连接:关键字为 right outer join 或 right join ;3)、完全外连接:关键字为 full outer join 或 full join 。
与内连接不同的是,外连接不只列出与连接条件匹配的行,还能够列出左表(左外连接时)、右表(右外连接时)或两个表(完全外连接时)中所有符合搜索条件的数据行。
左外连接的查询结果中不仅包含了满足连接条件的数据行,而且还包含左表中不满足连接条件的数据行。

上面例子首先是使用 insert 语句在 emp 表中插入新纪录(注意没有为 deptno 和 dname 列插入值,即它们的值为 null),然后实现 emp 表和 dept 表之间通过 deptno 列进行左外连接,从查询结果我们也可以看出,虽然插入数据行的 deptno 列值为 null ,但该记录仍然出现在查询结果中,这说明左外连接的查询结果会包含左表中不满足 “连接条件” 的数据行。
同样,右外连接的查询结果中不仅包含了满足连接条件的数据行,而且还包含右表中不满足连接条件的数据行。见下:

上面的查询结果中,虽然部门编号为 40 的部门现在在 emp 表中还没有员工记录,但它却出现在查询结果中,这说明右外连接的查询结果会包含右表中不满足 “连接条件” 的数据行。
而在执行完全外连接时,Oracle 会执行一个完整的左外连接和右外连接查询,然后将查询结果合并,并消除重复的记录行。

4)、自然连接
自然连接是指在检索多个表时,Oracle 会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。在自然连接中,用户不需要明确指定进行连接的列,这个任务由 Oracle 系统自动完成,自然连接使用 “natural join” 关键字。

自然连接强制要求表之间必须具有相同的列名称,这样容易在设计表时出现不可预知的错误,所以在实际应用系统开发中很少使用到自然连接。但这毕竟是一种多表关联查询数据的方式,在某些特定情况下还是有一定的使用价值。另外需要注意的是,在使用自然连接时,不能为列指定限定词(即表名或表的别名),否则 Oracle 系统将会提示自然连接中使用的列不能有限定词的错误提示。
5)、自连接
在应用系统开发中,用户可能会拥有 “自引用式” 的外键。“自引用式” 外键是指表中的一个列可以是该表主键的一个外键。例如,在 scott 模式下,emp 表中某一行的 mgr 列值(管理者列)可能是另一行的 empno 列值(员工列),因为管理者本身也是公司的员工,这样用户就可以通过 mgr 列与 empno 列的关系,实现查询某个管理者所管理的下属员工信息。

6)、交叉连接
交叉连接实际上就是不需要任何连接条件的连接,它使用 cross join 关键字来实现,其执行结果是一个笛卡儿积,而且这种查询结果是非常冗余的,但可以通过 where 子句来过滤出有用的记录信息。
