首页 > WEB开发 > 数据库 > Oracle Select语句深入学习
2014
11-17

Oracle Select语句深入学习

关于数据库的基础知识请先阅读《MySQL(SQL入门)》一文,本文会在《SQL入门》一文的基础上讲解Oracle数据库。注:本文不讲解Oracle数据库的安装。

一、Oracle Database中的概念

1、Oracle数据库和Oracle实例

一个Oracle DB服务器由一个Oracle数据库和一个Oracle实例组成。(如果是在Oracle集群中,一个Oracle数据库对应多个实例)

Oracle数据库(磁盘):位于硬盘上实际存放数据的文件,这些文件组织在一起,成为一个逻辑整体,即为 Oracle 数据库。因此在 Oracle 看来,“数据库”是指硬盘上文件的逻辑集合,必须要与内存里实例合作,才能对外提供数据管理服务。

Oracle实例(内存):位于物理内存里的数据结构. 它由一个共享的内存池和多个后台进程所组成, 共享的内存池可以被所有进程访问. 用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过实例才能实现, 不能直接读取硬盘上的文件。

区别和联系:实例可以操作数据库; 在任何时刻一个实例只能与一个数据库关联; 大多数情况下, 一个数据库上只有一个实例对其进行操作。

由Oracle数据库和实例组成的Oracle DB的体系结构如下图所示:

03. Oracle Select语句深入学习565

注:

1)Oracle实例的内存区域分为PGA和SGA两个部分,客户端直接操作的是PGA部分,把结果汇总到SGA后(内存操作,速度快)一次性读写到Oracle数据库中(磁盘IO)。这样做可以减少磁盘IO次数,提升效率。

2)在一个Oracle集群中,多个Oracle实例对应一个Oracle数据库。集群的优点有:负载均衡(load balance)和失败迁移(fail over)。有时候会看到RAC(Real application cluster),它指的也是Oracle集群。

2、Sql & Sql*Plus

oracle select 01

Oracle提供了两种工具(sqlplus和isqlplus)执行sql或sqlplus命令,前者是基于命令行的,后者是基于B/S架构的。下图是isql*plus的界面。

03. Oracle Select语句深入学习1031

二、深入学习Select语句

本节主要是利用scott账户下的emp/dept/salgrade表讲解各种各样的select语句。这些操作是在sqlplus命令行下完成的。

03. Oracle Select语句深入学习1120

在正式练习之前,请先了解下面这些sqlplus命令行下的操作技巧:

  • host *:可以在sqlplus环境下使用操作系统提供的命令。如:在Windows系统中的清屏命令为cls,在sqlplus中可以使用“host cls”清屏。
  • 修改Sql语句:在sqlplus中,如果不小心将from关键字写成了form,可以使用c命令(change)或ed命令(edit)对错误的地方进行修改,而不必重新编写。用法如下图(推荐ed命令):

03. Oracle Select语句深入学习1338

  • 修改sqlplus中查询结果展示样式:① 修改列宽:col ename for a8,修改ename列宽度为8(针对字符串类型的列);col empno for 99999,修改empno列宽度为5(针对数字类型的列) ② 修改行宽:set linesize 80,设置行宽为80个字符 ③ 设置分页大小:set pagesize 20,sqlplus查询出来的结果是按照pagesize=11展示的。
  • 提高sql语句的可读性:① 子句另起一行写(如from、where、order by等等) ②适当使用缩进
  • 可以使用Oracle提供的伪表“dual”测试函数和表达式,如select round(45.96,1) from dual。

另外还有下面的注意点:

sql语句中字符(串)、日期必须用单引号括起来,用双引号括起来的是别名。

1、基本Select语句

语法:

select * | {[distinct] column | expression [alias], …}

from tablename;
关于基本select语句可以参考《SQL入门》一文。此外还有以下几点说明:

1)列的别名:

  • 别名紧跟列名,也可以在列名和别名之间加入关键字“as”。
  • 别名可以不使用双引号。区别在于使用双引号时在别名中可以包含空格或特殊字符。

2)连接符:||

把列与列、列与字符连接在一起,作用类似于java中的“+”连接符。

2、过滤和排序

1)过滤:使用WHERE 子句,将不满足条件的行过滤掉。注:WHERE子句紧随FROM子句。几点说明:

  • 字符大小写敏感,日期格式敏感,默认的日期格式是“DD-MON-RR”,形如“14-11月-90”。
  • 在使用like运算符进行模糊查询时,如果要匹配“_”或“%”,要进行转义,可以使用escape关键词。如where ename like “%\_%” escape ‘\’,表示匹配名称中间有下划线的ename。

2)排序:使用ORDER BY子句排序,默认为升序(ASC),也可以指定为DESC(降序)。注:ORDER BY子句在SELECT语句的结尾。关于ORDER BY的总结如下:

  • ORDER BY后面可以跟列名、列的别名、表达式,还可以用列在select后面出现的顺序表示。
  • 多列排序:排序规则是先按照第一列排序,如果相同,则按照第二列排序,以此类推。如下:

03. Oracle Select语句深入学习2351

3、单行函数

03. Oracle Select语句深入学习2360

1)字符函数

Oracle select 02

2)数字函数(了解)

Oracle select 03

3)日期函数(了解,使用时再看)

Oracle中的日期类型数据包含日期和时间(等同于MySql中的DateTime),默认的日期格式是DD-MON-RR。

  • sysdate:数据库服务器所系统时间
  • 日期的数学运算:两个日期相减返回日期之间相差的天数。
  • 日期函数:① months_between,两个日期相差的月数 ② add_months,向指定日期中加上若干月数 ③ next_day,指定日期的下一个日期 ④ last_day,本月的最后一天 ⑤ 还可以用round、trunc对日期进行四舍五入和截断。

4)转换函数

  • 隐式类型转换:Oracle可以完成字符串和数字、日期类型的自动转换。隐式转换的前提:被转换对象是可以转换的。
  • 显示类型转换:to_char、to_number、to_date

Oracle select 04

5)通用函数

Oracle select 05

6)条件表达式

即在SQL语句中加入if-else的逻辑。在Oracle中有两种用法:

  • CASE 表达式:SQL99的语法,比较繁琐
  • DECODE 函数:Oracle自己的语法,比较简洁

Demo:公司开始涨薪水了,President涨1000,Manager涨800,其他员工涨500。

方式一:Case表达式:

03. Oracle Select语句深入学习3741

方式二:decode函数

03. Oracle Select语句深入学习3756

4、分组函数

分组函数:作用于一组数据,并对一组数据返回一个值。常用的组函数有avg、count、max、min、sum。

使用组函数时需要注意:

1)组函数会自动滤空。

① count计数问题:count(expr)返回expr不为空的记录总数(滤空)。

03. Oracle Select语句深入学习3888

② avg函数的滤空:

03. Oracle Select语句深入学习3902

③ 有些情况下不需要滤空,使用分组函数时做一些小小的改动即可:

03. Oracle Select语句深入学习3936

2)何时需要Group By子句

默认情况下,所有查询出来的结果为一组,可以使用Group By子句将结果分为若干组。在使用分组函数时,一定要注意:在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中,但包含在 GROUP BY子句中的列不必包含在SELECT 列表中。

当使用多个列进行分组时:

Demo:查看每个部门的每个工种下的所有员工的工资(对多个列进行分组):

03. Oracle Select语句深入学习4141

3)使用Having子句过滤分组

Having子句只能跟在Group By子句后面,这很容易理解,如果要对分组进行过滤,你必须要先对结果进行分组。当满足Having子句中条件的分组将被显示。

Demo:显示每个部门中的最高工资,如果最高工资连1500都没有,就不要给我看了:

03. Oracle Select语句深入学习4281

注:不能在Where子句中使用分组函数。

4)Group By语句的增强(生成报表):

Demo:按照部门统计各部门不同工种的工资情况,并生成报表:

03. Oracle Select语句深入学习4358

根据上图的结果,你可以认为group by rollup(a,b) = group by a,b + group by a + group by null。


留下一个回复

你的email不会被公开。