oracle学习总结

1.oracle安装

一.安装成功
1.安装分为客服端和服务端2块，具体见教程。

二.安装失败
1.百度，看看怎么把东西删除干净
2.如果删除不干净就重装系统，如果系统重装失败就换一台电脑。

2.oracle基础概念+用户密码+sql语言

一.概念
1.数据库：Oracle数据库是由美国甲骨文公司提供的一种关系型数据库。

2.数据库实例：实例也称为SID，类似于java中通过new关键字创建的对象，我们主要操作的也就是实例，一个数据库下可以有多个实例。

3.表空间：Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有多个表空间。

4.表：表是用来存储数据的，一个表空间下可以有多张表。

小结：一个数据库可以有多个实例，一个实例可以有多个表空间，一个表空间可以有多张表。

二.用户与密码
登录数据库需要对应的用户名和密码，所以需要创建用户与对应的密码：
锁定用户：alter user 用户名account lock
解锁用户：alter user 用户名account unlock
创建用户：create user 用户名 identified by 密码
删除用户：drop user 用户名 [cascade]

授予权限
–grant dba to meitao;
授予权限：grant 权限名 to 用户名
授予登录权限：grant create session to 用户名
授予建表权限：grant create table to 用户名
授予表空间权限：grant unlimited tablespace to 用户名
授予用户增删改查权限：
grant select | insert | update | delete | all on 表名 to 用户名

撤销权限：revoke 权限名 from 用户名

断开用户的连接：
1.先查找当前用户的进程：select sid,serial# from v$session where username=‘用户名’
2.再删除该进程：alter system kill session ‘sid,serial#’

3.sql语言+语法+注意事项+模糊查询

一.注意事项
1.sql中除了数据的值外，其余部分均不区分大小写，sql中的字符串类型只能用单引号。
2.nchar和nvarchar类型使用了unicode字符集，表示每个字符的长度都是两个字节(包括英文字符)；
带有var前缀的类型为可变长度(varchar)，不带的为固定长度(char)。

二.sql语言
Sql语言分为4大类：
1.数据定义语言(DDL)
如：create、alter、drop、truncate
2.数据操纵语言(DML)
如：select、insert、update、delete
3.数据控制语言(DCL)
如：grant、revoke
4.事物控制语言(TCL)
如：commit、rollback

数据操纵语言(DML)：
select语法：
select 字段名 from 表名 [where 过滤条件 group by 分组 having 过滤分组 order by 排序]

insert语法：
insert into 表名[(字段名)] values(字段值)

update语法：
update 表名 set 字段名=字段值 [where 过滤条件]

delete语法：
delete from 表名 [where 过滤条件]

注：
1.对日期类型操作时必须使用单引号，oracle中的日期格式默认为’DD-M月-YYYY’，分别表示日-月-年。如’17-12月-1980’，表示1980年12月17日。
2.如希望使用习惯的日期格式，则可以使用to_date函数进行格式化，如格式’yyyy-mm-dd hh24:mi:ss’。
to_date(’yyyy-mm-dd hh24:mi:ss’)

drop table 表名、truncate table 表名、delete from 表名间的区别：

drop table 表名：不仅删除全部数据，并且把表也一并删除，不能选择删除数据，不需要使用事物处理。
truncate table 表名：删除全部数据，表结构还在，速度快效率高，不能选择删除数据，不需要使用事物处理。
delete from 表名：既能删除全部数据，也能选择删除，需要使用事物处理。

distinct：去掉重复，若后面有多个字段，则表示所有字段值的组合不能重复。
select distinct 字段 from 表名

like运算符：用于模糊查询，与通配符%和_配合使用。
%：表示零或多个任意字符。
select * from 表名 where 字段 like ‘C%’
：单个任意字符。
select * from 表名 where 字段 like ‘%’
Oracle10g以上版本支持正则表达式的函数：regexp_like

escape：指定转义字符，用于查询含有特殊字符，如查询含有%的数据。
select * from 表名where 字段 like ‘%%%’ escape ‘’

between和in：在某个范围内查找数据。
select * from 表名 where 字段 between 最小值 and 最大值
select * from 表名 where 字段 in(值1,值2…)

别名：
select 字段 as 字段别名 from 表名
select 字段 字段别名 from 表名
select * from 表名 表别名

order by：用于排序，只能作为最后一个子句，也是唯一一个可以使用别名的子句。
select * from 表名 order by 字段 asc默认升序|desc降序

4.聚合函数

一.聚合函数
sum:返回选取结果集中所有值的总和
max:返回选取结果集中所有值的最大值
min：返回选取结果集中所有值的最小值
avg：返回选取结果集中所有值的平均值
count：返回选取结果集中行的数目
注：均统计为非null的字段

group by:分组子句，将相同数据分为一组
select 字段 from 表名 group by 字段

having:用于过滤分组，与分组字段或聚合函数配合使用
注：1.where子句不能与聚合函数配合使用
2.若查询字段中有聚合函数和非聚合字段时，必须使用group by
子句，且非聚合字段必须使用group by子句进行分组

5.表连接查询

1.cross join （交叉连接）
表A如图：

表B如图：

cross join(交叉连接)：返回笛卡尔积，即返回的行为左表乘右表的积。
如：select * from a cross join b

2.inner join（内连接）
inner join(内连接)：先返回笛卡尔积，再根据on子句返回左表和右表的匹配数据。
如：select * from a inner join b on a.aid=b.bid

3.left join（左连接）
left join(左连接)：先返回笛卡尔积，再根据on子句返回左表和右表的匹配数据，最后返回左表未匹配上的数据，即先返回左表数据，再根据on子句返回右表的匹配数据，匹配不上返回NULL。
如：select * from a left join b on a.aid=b.bid

4.right join（右连接）
right join(右连接)：先返回笛卡尔积，再根据on子句返回左表和右表的匹配数据，最后返回右表未匹配上的数据，即先返回右表数据，再根据on子句返回左表的匹配数据，匹配不上返回NULL。
如：select * from a right join b on a.aid=b.bid

5.full join（全连接）
full join(全连接)：先返回笛卡尔积，再根据on子句返回左表和右表的匹配数据，最后返回左表和右表未匹配上的数据，即左连接+右连接的并集。
如：select * from a full join b on a.aid=b.bid

6.子查询

一.子查询
按返回行数分为单行子查询和多行子查询；
按关联性又分为相关子查询和非相关子查询。
子查询可用于 select、from 、where、update、insert、delete
子句中，定义在小括号（）中

1.1非相关子查询
独立于外部主查询而存在的子查询，称为非相关子查询。
非相关子查询仅执行一次，并将子查询的执行结果传递给外部子查询

1.2非相关子查询执行过程
遵循“由里及外”原则，即先执行最内层的子查询语句，然后将执行结果与外层的语句进行合并，依次逐层向外扩展并最终形成完整的sql语句。

2.1相关子查询
依赖于外部主查询的子查询称为相关子查询，相关子查询的执行次数等于主查询的执行次数。

2.2相关子查询执行过程
先从主查询中取出一条记录，然后将该行记录相关列的值传递给子查询，再执行子查询，得到子查询执行完后的值，最后依据子查询返回的结果得到满足提示条件的行

7.分页查询

注：rowid称为”伪列”，数据库表中的每一行数据都有一个唯一的rowid，oracle用它来访问数据，如select t.,rowid from area t；rownum称为”伪列”，它会根据返回记录生成一个序列化的数字，rownum始终都是从1开始生成，如select t.,rownum from area t。

oracle分页：
select * from (select t.,rownum rn from (select * from 表名) t) where rn between 当前页码每页显示记录数-每页显示记录数+1 and 当前页码每页显示记录数
如当前页码从1开始，每页显示3条记录：
select * from (select t.,rownum rn from (select * from product) t) where rn between 1 and 3

8.函数和连接符（不熟悉部分）

函数和连接符：
nvl：若参数1的值为null则返回参数2的值，否则返回参数1的值。
select t.*,nvl(p_areaid,0) from area t

nvl2：若参数1的值为null则返回参数3的值，否则返回参数2的值。
select t.*,nvl2(p_areaid,p_areaid,0)from area t

sysdate：获取当前时间。
select sysdate from dual

to_char：将指定数据类型转换成指定格式字符串。
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual

to_date：将指定格式的日期字符串转换成日期类型。
select to_date(‘2012-12-12 23:59:59’,‘yyyy-mm-dd hh24:mi:ss’) from dual

||：连接字符串。
select ‘hello’||’ world’ from dual

9.序列+同义词+视图+索引

序列：一个命名的顺序编号生成器，以串行的方式生成一系列顺序整数。
nextval：返回序列的下一个值，select 序列名.nextval from dual
currval：返回序列的当前值，select 序列名.currval from dual

同义词：对象的别名，优点是当原始对象修改后，也只需对同义词进行修改和编译，而不用去修改程序中的代码，分公用同义词和私有同义词两种。

视图：是由select语句产生的逻辑表，只有定义而无数据，主要用于查询。优点如提供各种数据的表现形式、隐藏敏感数据、简化复杂查询等。

索引：提高查询效率，降低增删改效率，类似于目录。
索引分为：单列索引、复合索引、唯一(unique)索引、非唯一索引、标准索引(B树索引)、位图(bitmap)索引和函数索引。
创建索引原则：
1． 对于取值范围较大的列，应该创建标准索引(B树索引)，对于取值范围较小的列，应该创建位图索引。
2． 对于连接查询中的关联列应该创建索引。
3． 主键列和唯一约束列会自动创建索引，但外键列不会自动创建索引。
4． 不用对所有表或所有列创建索引，也不是索引创建的越多越好，按需创建。

10.执行存储过程

一.pl/sql：由定义部分、执行部分和异常处理部分组成。
定义部分：定义变量、常量、游标、异常和复杂数据类型等。
执行部分：用于实现应用模块功能。
异常处理部分：用于处理执行部分可能出现的错误。
语法(其中declare和exception为可选部分)：
declare
定义部分
begin
执行部分
exception
异常处理部分
end;

二.:=：赋值运算符(静态赋值)，v_pi number(3,2):=3.14
select into：查询赋值(动态赋值)，select 列名 into 变量名 from 表名
dbms_output.put_line：用于输出，dbms_output.put_line(‘hello’)

三.数据类型：
%type：参考某列数据类型，如v_ename emp.ename%type
%rowtype：参考某行数据类型，如v_emp emp%rowtype

四.存储过程：用于执行一个指定的操作，类似于java中的方法。
语法：
create [or replace] procedure 过程名[(参数类型)]
as [声明部分]
begin执行部分
[exception异常处理部分]
end;

1．创建无参过程
create or replace procedure proc_no_param
as
begin
dbms_output.put_line(‘hello’);
end;
调用过程：call proc_no_param()

2．创建带in(输入)参数过程(参数默认为in参数)
create or replace procedure proc_in_param(p_arg varchar2)
as
begin
dbms_output.put_line(p_arg);
end;
调用过程：call proc_in_param(‘hello’)

3．创建带out(输出)参数过程(输出参数必须用out标识)
create or replace procedure proc_out_param(p_arg out varchar2)
as
begin
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) into p_arg from dual;
end;
调用过程：
declare
v_arg varchar2(19);
begin
proc_out_param(v_arg);
dbms_output.put_line(v_arg);
end;

11.序列

sequence