Oracle学习心得

一、Oracle基础
1、Oracle简介
Oracle（中文名称叫甲骨文）是殷墟（yīn Xu）出土的甲骨文（oracle bone inscriptions）的英文翻译的第一个单词，在英语里是“神谕”的
意思。Oracle公司成立于1977年，总部位于美国加州.
Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中（比如：微软的SQL Server，IBM的DB2等），Oracle
的主要用户涉及面非常广，包括：银行、电信、移动通信、航空、保险、金融、电子商务和跨国公等。
WebLogic是美国bea公司出品的一个application server确切的说是一个基于Javaee架构的中间件，BEA WebLogic是用于开发、集成、部署
和管理大型分布式Web应用、网络应用和数据库应用的Java应用服务器。2008年1月16日，全球最大的数据库软件公司甲骨文（Oracle）宣布已
经同BEA达成协议，以85亿美元收购BEA。
2008年1月16日，Sun宣布已经与MySQL AB达成协议，以大约10亿美元收购MySQL AB。
2009年04月20日，甲骨文宣布，该公司将以每股9.5美元的价格收购Sun。该交易价值约为74亿美元。

Oracle的官方网站：http://www.oracle.com
2、Oracle相关参考文档
文档：http://www.oracle.com/technetwork/database/database10g/documentation/index.html
在线：http://www.oracle.com/pls/db102/homepage
Linux上安装 Oracle 10g：http://69520.blog.51cto.com/59520/91156
Oracle数据库监听配置：http://blog.csdn.net/tianlesoftware/article/details/4861572
3、Oracle中的一些概念
1）Oracle数据库
位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑整体，即为 Oracle 数据库。因此在Oracle 看来，“数据库”是指硬
盘上文件的逻辑集合，必须要与内存里实例合作，才能对外提供数据管理服务。
2）Oracle实例
位于物理内存里的数据结构。它由一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问。用户如果要存取数据库(也
就是硬盘上的文件) 里的数据，必须通过实例才能实现，不能直接读取硬盘上的文件。
3）Oracle服务器
一个Oracle服务器:是一个数据管理系统（RDBMS）,它提供开放的,全面的，近乎完整的信息管理。由一个Oracle实例和一个Oracle数据库组
成实例可以操作数据库，在任何时刻一个实例只能与一个数据库关联。大多数情况下，一个数据库上只有一个实例对其进行操作（也可以有多
个实例）

4）数据库的逻辑和物理结构

>> 表空间由多个数据文件组成
>> 数据文件只能属于一个表空间
>> 表空间为逻辑概念，数据文件为物理概念
>> 段存在于表空间中
>> 段是区的集合
>> 区是数据块的集合
>> 数据块会被映射到磁盘块

二、搭建Oracle环境（安装与配置）
1、系统需求
内存需求：1GB
磁盘空间需求：Oracle软件需要1.5GB到3.5GB
操作系统：XP/WIN7/Vista/Linux/虚拟机
2、安装Oracle服务器端
全局数据库名是数据库在服务器网络中的唯一标识。
Oracle安装完成后有三个默认用户：
管理员用户：SYS和SYSTEM，默认不锁定，可直接使用
普通用户：SCOTT默认为锁定状态，不能直接使用
Oracle 数据库服务启动后它会占有大量的内存和CPU资源。如果不想让Oracle数据库自动启动，可做如下设置：

虽然一个Oracle数据库服务器中可以安装多个数据库，但是一个数据库需要占用非常大的内存空间，因此一般一个服务器只安装一个数据库。每
一个数据库可以有很多用户，不同的用户拥有自己的数据库对象（比如：数据库表），一个用户如果访问其他用户的数据库对象，必须由对方用
户授予一定的权限。不同的用户创建的表，只能被当前用户访问。因此在Oracle开发中，不同的应用程序只需使用不同的用户访问即可。

3、操作Oracle的工具
>> Sqlplusw.exe，命令行程序
>> iSql*Plus，Web程序。访问地址：http://localhost:5560/isqlplus/
>> 其他图形化工具
4、基本SQL*PLUS命令
说明：命令不区分大小写，注意区分开SQL*PLUS命令与SQL语句，SQL语言大小写也不敏感
SQL*PLUS命令不能改变数据库中的数据的值，而使用SQL语句可以控制数据库中的表的定义信息和表中的数据
（1）登录普通用户
说明：用户名不区分大小写，密码区分
方式一：执行 sqlplus --> 输入用户名 --> 输入密码。
方式二：执行 sqlplus {用户名} --> 输入密码。
方式三：执行 sqlplus {用户名}/{密码}。
例子：sqlplus --> 输入scott --> 输入tiger。
sqlplus scott --> 输入密码。
sqlplus scott/tiger。
（2）登录管理员
执行 sqlplus / as sysdba
（3）退出：exit
（4）用户锁定、解锁、修改密码
>> 解锁用户：alter user 用户名 account unlock;
>> 锁定用户：alter user 用户名 account lock;
>> 修改密码：alter user 用户名 identified by 新密码;
>> 修改管理员密码：alter user sys identified by 新密码;
（5）加载脚本文件
例：@c:/a.sql
（6）查看与设置参数
>> 查看参数：show （show选项），例如：show pagesize，其作用为显示参数目前的值
>> 设置参数的值：set（set选项）（新值），例如：set pagesize 100，其作用为设置参数的值，只对本次登录有效
如果希望能永久保存设置的参数，可以去修改文件：oracleHome\product\10.2.0\db_2\sqlplus\admin\glogin.sql
（7）中止正在执行的命令
在命令行的SqlPlus中，中止一个正在执行的命令是 Ctrl+/, Ctrl + C，如果直接按Ctrl+C会退出SqlPlus程序。
在sqlplus.exe（单独运行的程序）中，中止一个正在执行的命令是 Ctrl + C。
（8）Oracle的启动和关闭
必须是sys用户，命令为：
启动：startup open
关闭：shutdown immediate

[sql] view plaincopy
<span style="font-family:Microsoft YaHei;">SQL> show linesize --显示行宽
SQL> show pagesize --显示一页显示的条数
SQL> show user --显示当前用户
SQL> conn[ect] scott/tiger --切换用户
SQL> / --执行上一条sql语句
SQL> set linesize 150 --设置行宽,默认为80
SQL> set pagesize 100 --设置页面大小（一页显示的数据）, 默认为14
SQL> host cls --清屏，Windows中
SQL> host clear --清屏，Linux中
SQL> col[umn] 列名 for[mat] a15 --设置某列的宽度，a15，表示15个字符的宽度，a表示该字段是字符显示
SQL> col[umn] 列名 for[mat] 9999 --设置某列的宽度，9表示一位数字的，有几个，就是表示最大几位数字
SQL> ed[it] --修改上一条SQL语句，修改语句后，后面不要带分号</span>

5、基本SQL语句
[sql] view plaincopy
<span style="font-family:Microsoft YaHei;">SQL> drop table aa purge--删除表
SQL> select * from tab--查看当前用户有哪些表：
SQL> desc dept--查看表结构

-- 注释：
-- 单行注释：--
-- 多行注释：/* */
</span>


6、Oracle相关的服务
（1）OracleService + 服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。
（2）OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到
服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。
（3）OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录
并进行数据库操作了。访问地址：http://localhost:5560/isqlplus/

三、查询数据（基本查询）
1、简单查询（select ... from ...）
（1）虚表DUAL
如果只查询一个表达式，没有用到任何表的数据，这时也必须得写from...
可以写成 from dual
dual是一个虚表，本身就存在的，可以直接使用
如：
select 3+2//错误
select 'Hello' || 'World' from dual;//正确
（2）列的别名
别名如果使用双引号，可以在别名中包含空格或者特殊字符并区分大小写，不加双引号就全部大写显示
在使用别名时，如果要使用引号，必须使用双引号；使用字符串时必须使用单引号
（3）字符串、连接符
连接符是把列与列，列与字符连接在一起。用"||"表示，可以用来合成列。或者也可以使用concat( )函数实现
字符串必须用单引号
[sql] view plaincopy
<span style="font-family:Microsoft YaHei;">SQL> select ename || '的薪水是' || sal from emp;

--查询结果:
ENAME||'的薪水是'||SAL
-------------------------------
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975
MARTIN的薪水是1250
BLAKE的薪水是2850


--也可以使用函数 concat()：
SQL> select concat('Hello', 'World') from dual;

--查询结果:
CONCAT('HELLO','WORLD')
--------------------
HelloWorld
</span>
（4）删除重复行
在SELECT子句中使用关键字“DISTINCT”，可删除重复行
//TODO DISTINCT有个需要注意的地方，加上例子进行说明
2、模糊查询
使用LIKE运算实现模糊查询
%：代表零个或多个字符（任意个字符）
_：代表一个字符
[sql] view plaincopy
<span style="font-family:Microsoft YaHei;">--示例1：
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

--示例2：‘%’和‘-’可以同时使用。
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

--示例3：可以使用 ESCAPE 标识符选择‘%’和 ‘_’ 符号。
--回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘ escape ‘\‘;</span>

四、运算符
1、比较运算
（1）简单运算符

（2）其他运算符


2、逻辑运算

3、优先级（可以使用括号改变优先级顺序）


五、排序ORDER BY
1、格式：ORDER BY 列名,[列名]...，
列名可以是数字、日期、字符串，排序的列名可以使用列名，表达式，别名，序号（表示select中的第几个列）
[sql] view plaincopy
--（1）按别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

--（2）多个列排序，规则是先按照第一列排序，如果相同，则按照第二列排序；以此类推。
--ASC（ascend）: 升序（可以省略）；DESC（descend）: 降序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

--（3）可以按照select语句中的列名的顺序值排序
--可以使用不在SELECT 列表中的列排序。
2、order by与null，见下面

六、关于空值（NULL）
1、空值（null）
>>空值是无效的，未指定的，未知的或不可预知的值
>>空值不是空格或0
>>包含空值的数学表达式的值都为空值，也就是说空值进行加减乘除都为空
>>含有null的表达式结果为null
>>null 不等于null
2、空值的处理
（1）对null值的判断
is null
is not null
（2）滤空函数nvl
格式：nvl(表达式, 当表达式为空时使用的值)
[sql] view plaincopy
select empno, ename, sal, sal * 12 as 年薪, nvl(comm, 0), (sal * 12 + nvl(comm,0) ) 总收入
from emp
（3）IN与NOT...IN对NULL的处理
where .. in (.., .., .., ...) ，IN 是OR的关系，如果里面包含NULL，没有影响
例：查询所有是经理的员工
SQL> select * from emp where empno in (select mgr from emp);
where .. not in (.., .., ...) 如果含有null，则不返回任何结果。（并且的关系，一个为false，整个就为false）
例：查询所有不是经理的员工
SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
（4）ORDER BY对null的处理
当order by所在的列中有null，会：升序时，null的在下面，降序时，null的在上面。
我们希望，不管升序还是降序，null值的始终在下面
[sql] view plaincopy
--方式一：
select * from emp order by comm desc nulls last;
--方式二：
select empno, ename, job, hiredate, sal, nvl(comm, 0)
from emp
order by 6 desc --desc和asc只修饰当前列，如order by 6,name desc >> desc不修饰6，6默认为asc

七、去重DISTINCT
[sql] view plaincopy
--DISTINCT作用是去掉重复的行
-- 作用于一列
select distinct job from emp;
-- 作用于多个列，所有列的值加一起重复才算重复的记录
select distinct job, deptno from emp;

八、日期
1、Date
Date可以保存年月日时分秒，只是显示格式的问题，date类型在数据库中的存储固定为7个字节
（1）修改日期的格式
[sql] view plaincopy
--SQL> select * from v$nls_parameters;
SQL> alter session set nls_date_format='yyyy-mm-dd'; // 只对当前session有效
（2）to_date与to_char
[sql] view plaincopy
SQL> select * from emp where hiredate>to_date('1981-12-31', 'yyyy-MM-dd');
SQL> select * from emp where to_char(hiredate, 'yyyy-MM-dd') > '1981-12-31';

Oracle数据库系统工程师培训视频教程(内存结构、物理结构、备份和恢复、安全审计、性能调优)
下载地址：链接: http://pan.baidu.com/s/1qWExusW 密码: gcav