一、Oracle概述
1、Oracle发展简介
Oracle的官方网站：http://www.oracle.com
Oracle（中文名称叫甲骨文），成立于1977年，总部位于美国加州. Oracle创始人：Ed Oates、Bruce Scott、Bob Miner、Larry Ellison。
Oracle主要版本有：Oracle8 / Oracle8i、Oracle9i、Oracle10g、Oracle11g、Oracle12g，其中i表示的是internet，意味着Oracle开始向网络发展；g表示的是网格计算。
2008年1月16日，全球最大的数据库软件公司甲骨文（Oracle）宣布已 经同BEA达成协议，以85亿美元收购BEA。
2008年1月16日，Sun宣布已经与MySQL AB达成协议，以大约10亿美元收购MySQL AB。
2009年04月20日，甲骨文宣布，该公司将以每股9.5美元的价格收购Sun。该交易价值约为74亿美元。
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
二、Oracle体系结构
Oracle是关系型数据库管理系统（RDBMS），它由Oracle数据库和Oracle实例组成。
Oracle认证：OCA（助手）、OCP（专家）、OCM（大师）
1、Oracle数据库
位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑整体，即为 Oracle 数据库。因此在Oracle 看来，“数据库”是指硬盘上文件的逻辑集合，必须要与内存里实例合作，才能对外提供数据管理服务。
2、Oracle实例
位于物理内存里的数据结构。它由一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问。用户如果要存取数据库(也就是硬盘上的文件) 里的数据，必须通过实例才能实现，不能直接读取硬盘上的文件。实际上，Oracle实例就是平常所说的数据库服务（service）
3、Oracle数据库与实例的区别
实例可以操作数据库；
在任何时刻一个实例只能与一个数据库关联，访问一个数据库；
而同一个数据库可由多个实例访问（RAC），一般一个数据库上只有一个实例对其进行操作。
4、表空间
（1）用户的默认表空间
表空间是数据库的逻辑存储设备，它把数据库信息组织成物理存储空间。表空间由数据文件组成，用户的各种模式对象（如表，索引，过程，触发器等）都是放在表空间中。每个数据库用户，都可以设置一个默认表空间，当用户创建一个新的数据库对象（如表），并且不明确地为此对象指定表空间时，Oracle会把所创建的新数据库对象存放到用户默认的表空间USERS中
（2）用户的临时表空间
一般，SQL语句在完成任务时需要临时工作空间，例如：一个用来连接和排序大量的查询需要临时工作空间来存放结果，除非另外指定，一般情况下，用户的临时表空间是TEMP表空间。若数据库中没有创建TEMP表空间，则用户的临时表空间为SYSTEM表空间。因为SYSTEM表空间是用来保存数据库系统信息（数据库自身信息的内部系统表和视图，如数据字典、所有PL/SQL程序的源代码、函数、触发器等）。如果用户大量使用此表空间存储自己的数据，将会影响系统的执行效率。因此一般不建议用户使用SYSTEM表空间
>> 数据文件只能属于一个表空间
>> 表空间为逻辑概念，数据文件为物理概念
>> 段存在于表空间中
>> 段是区的集合
>> 区是数据块的集合
>> 数据块会被映射到磁盘块
5、Oracle相关的服务
（1）OracleService + 服务名（服务名是安装时SID的名称），数据库的实例服务，每一个数据都有一个数据库实例服务，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。
（2）OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。Oracle中提供了一个监听状态检查的命令：在BIN目录下的找到LSNRCTL.EXT >> 双击打开输入status，即可查看Oracle当前的网络环境配置
如果有程序要操作数据库就必须启动该服务
（3）OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录并进行数据库操作了。访问地址：http://localhost:5560/isqlplus/。前俩个服务启动后，Oracle就可以正常使用了
//Oracle配置文件：E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN下有俩个重要的配置文件：
listener.ora（配置监听服务）和tnsnames.ora（配置数据库）
6、常见的数据库对象
表：基本的数据存储集合，由行和列组成
视图：从表中抽出的逻辑上相关的数据集合
序列：提供有规律的数值
索引：提高查询的效率
同义词：给对象起别名
7、表名和列名命名规则
必须以字母开头；
必须在1-30个字符之间；
必须只能包含A-Z，a-z，0-9，_，$和#；
必须不能和用户定义的其他对象重名
必须不能是Oracle的保留字
8、数据类型
9、用户介绍
Oracle主要使用以下四个用户：
（1）超级管理员：sys/change_on_install
（2）普通管理员：system/manager
（3）普通用户：scott/tiger（默认是被锁定的）
（4）大数据用户：sh/sh
//前三个的密码默认都设置为斜杠后的密码
三、SQL*PLUS命令
1、帐号登录与退出
用户名不区分大小写，密码区分
SQL> sqlplus -> 回车 -> 输入用户名scott -> 回车 -> 输入密码tiger。
SQL> sqlplus scott -> 回车 -> 输入密码。
SQL> sqlplus scott/tiger -> 回车
SQL> sqlplus / as sysdba --本地登录超级管理员帐号
SQL> sqlplus 用户名/密码 as sysdba --以超级管理员身份登录
SQL> exit --退出当前登录帐号以上登录都是通过sqlplus.exe命令来登录，如果是通过sqlplusw.exe来进行登录，其中主机字符串为数据库名，如果不填会找到默认的数据库实例
2、用户锁定、解锁、修改密码
SQL> alter user 用户名 account unlock; --解锁用户
SQL> alter user 用户名 account lock; --锁定用户
SQL> alter user 用户名 identified by 新密码; --修改密码
SQL> alter user sys identified by 新密码; --修改管理员密码3、其他命令
SQL> select * from tab --查看当前用户下的所有表
SQL> show user --显示当前用户
SQL> conn[ect] scott/tiger --切换登录用户
SQL> conn[ect] as sysdba --超级管理员登录
SQL> show linesize --显示当前行宽
SQL> show pagesize --显示一页显示的条数
SQL> set linesize 150 --设置行宽,默认为150
SQL> set pagesize 100 --设置分页大小（一页显示的数据）, 默认为14
SQL> col[umn] 列名 for[mat] a15 --设置某列的宽度，a15，表示15个字符的宽度，a表示该字段是字符显示
SQL> col[umn] 列名 for[mat] 9999 --设置某列的宽度，9表示一位数字的，有几个，就是表示最大几位数字
SQL> @c:/a.txt --执行文件里的命令，如果文件是sql为后缀则命令可以不用写后缀名
SQL> ed[it] --修改上一条SQL语句，修改语句后，后面不要带分号
SQL> / --执行上一条sql语句
SQL> startup open --数据库的启动，必须是sys
SQL> shutdown immediate --立即关闭数据库
SQL> host cls --清屏，Windows中
SQL> host clear --清屏，Linux中在sqlplus.exe（单独运行的程序）中，中止一个正在执行的命令是 Ctrl + C。
永久保存一些配置，需要在Oracle的安装目录下E:\oracle\product\10.2.0\db_1\sqlplus\admin\glogin.sql的最后加上设置的命令即可
注意：
（1）一旦用sys连接之后，不能直接查询其他用户的表，需要加上用户名.表名才行。例如：SELECT * FROM scott.EMP
（3）sqlplusw.exe也可以调用windows的系统命令，使用HOST作为前缀，例如：
SQL> HOST COPY D:\demo.sql D:\demo.txt
说明：命令不区分大小写，注意区分开SQL*PLUS命令与SQL语句，SQL语言大小写也不敏感
SQL*PLUS命令不能改变数据库中的数据的值，而使用SQL语句可以控制数据库中的表的定义信息和表中的数据
SQL*PLUS是一个工具，SQL是一个语言
四、SQL基础
1、SQL语言的分类
SQL语句分为三种类型：DML、DDL、DCL
（1）DML（Data Manipulation Language）数据操作语言，用于查询与修改数据记录，比如增删改查的SQL语句
（2）DDL（Data Definition Language）数据定义语言，用于定义数据库的结构，比如对表的增删改，建立或删除索引
（3）DCL（Data Control Language）数据控制语言，用来控制数据库的访问，比如事务提交、回滚。还包括如下SQL语句：
GRANT：授予访问权限
REVOKE：撤销访问权限
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定
2、运算符和优先级
3、DUAL
dual是一个虚表，本身就存在的，可以直接使用
4、别名
别名如果使用双引号，可以在别名中包含空格或者特殊字符并区分大小写，不加双引号就全部大写显示
在使用别名时，如果要使用引号，必须使用双引号，加上双引号后别名不会改变大小写，可以包含空格；
--别名格式
select 字段 [as] 别名 from 表名 [as] 别名5、字符串
字符串必须用单引号，Oracle只有在别名中才使用双引号
6、连接符
连接符是把列与列，列与字符连接在一起。用"||"表示，可以用来合成列。或者也可以使用concat( )函数实现
7、DISTINCT
在SELECT子句中使用关键字“DISTINCT”，可删除重复行
//TODO DISTINCT有个需要注意的地方，加上例子进行说明
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
--DISTINCT作用是去掉重复的行
-- 作用于一列
select distinct job from emp;
-- 作用于多个列，所有列的值加一起重复才算重复的记录
select distinct job, deptno from emp;
select count(distinct dept_id) from employees;8、模糊查询
使用LIKE运算实现模糊查询
%：代表零个或多个字符（任意个字符）
_：代表一个字符select last_name,department_id,salary
from employees
where last_name like '_a%';
--查询员工中名字的第二位是字符a的员工有哪些
--"_"下划线表示单个字符，"%"表示任意个字符
select last_name
from employees
where last_name like '%a%e%' or last_name like '%e%a%';
--查询姓名中有字母a和e的员工的姓名9、ESCAPE（转义字符）
select last_name
from employees
where last_name like '%#_%' escape '#'
--查询last_name中包含下划线的员工
--将#定义为转义字符
SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT\_%' escape '\';
--查询job_id中有IT_的job_id
--将\定义为转义字符 （1）、格式：ORDER BY 列名,[列名]...，
列名可以是数字、日期、字符串，排序的列名可以使用列名，表达式，别名，序号（表示select中的第几个列）
--（1）按别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
--（2）多个列排序，规则是先按照第一列排序，如果相同，则按照第二列排序；格式：<span style="font-size:12px;">ORDER BY 一级排序列 ASC/DESC，二级排序列 ASC/DESC;</span>
--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，见下面
1、空值（null）
>>空值是无效的，未指定的，未知的或不可预知的值
>>空值不是空格或0
>>包含空值的数学表达式的值都为空值，也就是说空值进行加减乘除都为空
>>含有null的表达式结果为null
>>null 不等于null
2、空值的处理（1）对null值的判断
is null
is not null
（2）滤空函数nvl
格式：nvl(表达式, 当表达式为空时使用的值)
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值的始终在下面
--方式一：
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
select last_name,department_id,slary
from employees
where commission_pct is null
select last_name,department_id,slary
from employees
where commission_pct is not null
select last_name,salary
from employees
where salary not between 5000 and 12000 九、ROWNUM
rownum说明：
rownum是个伪列，是随着结果集生成的，返回的第一行分配的是1，第二行是2等等，生成的结果是依次递加的，没有1就不会有2。
注意，不返回的就不算，第一条返回的结果的rownum为1。
rownum一旦生成，就不会变化了，所以要注意，指定排序时rownum就是打乱的了。
rownum的比较操作
我们说rownum不支持>, >=, =, between and，只支持<, <=等。
虽说不支持，但并不会报错，只是返回的数据为空，这是因为根本不能满足这样的where条件。
如where rownum>2：
取回第1条数据的rownum为1，不满足，就舍弃这条记录。
看下一条，然后取第2条数据的rownum还是为1，还是不满足，再舍弃。
以此类推，最终舍弃了所有的数据，这就是所谓不支持的原因。
于是我可以推出：
where rownum=1 是只有一条结果的（要求至少有一条结果）。
where rownum>=1 是返回所有结果的（要求至少有一条结果）。
where rownum<9 是有8条结果的（要求至少有8条结果）。
where rownum!=9 是有8条结果的（要求至少有8条结果）。
where rownum between 6 and 10 是没有结果的。
where rownum between 1 and 5 是有前5条结果（要求至少有5条结果）
实现rownum>n (n>0) 的效果：
rownum本身是不行的，所以就可以先查询出一个结果集合。
再从这个集合中查询，这时集合中的行号就是一个普通的字段了，可以做任何比较。
即使用子查询方法来解决。
注意问题：
1，查询时rownum可以直接使用，不要求一定要在select中出现，如：select id,name from user where rownum<=5;
2，在本查询中，where子句中要直接写rownum，前面不能加表别名，也不能使用使用select子句中为rownum定义的别名。
在order by子句中是可以使用为rownum定义的别名的。
3，这样的操作在大数据集中会影响速度。
分页：
（内层排序外层选，需要三层查询）
内：排序。
中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。
外：去掉前m条结果。
5、基本SQL语句
drop table 表名 purge; --删除表，不能回滚；普通表删除后，会到回收站，加上purge会直接删除，不经过回收站
truncate table 表名; --清空表，不能回滚
delete from 表名; --清空表，可以回滚，delete属于DML
rename 表名 to 新表名; --修改表名
<pre name="code" class="sql">desc 表名--查看表结构select table_name from user_tables;--查看用户创建的表名
select * from user_catalog;--查看用户自己的数据库对象
--注释：--单行注释：----多行注释：/* */
191
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
