oracle数据库学习总结

数据库：以一定方式存储，能为多个用户分享，具有尽可能小的数据冗余特点，与应用程序彼此独立的数据集合。长期存储在计算机内，有组织的管理，具有较小冗余，可供多个用户共享的数据集合。

普通用户连接：Conn Scott/tiger

超级管理员连接：Conn "sys/sys as sysdba"

断开连接：Disconnect;

把SQL存到文件：Save c:\1.txt

编辑SQL语句：Ed c:\1.txt

运行SQL语句：@ c:\1.txt

描述Emp结构：Desc emp;

查看该用户下所有对象：select * from tab;

显示当前用户：Show user;

DML语句：数据操作语言，包括Insert、Update、Delete、Merge

DDL语句：数据定义语言，Create、Alter、Drop、Truncate

DCL语句：数据控制语言，Grant、Revoke

事务控制语句：Commit、Rollback、Savepoint

initcap（）：使每个单词的首字母大写，其余字母小写

左外联接：select a.*,b.* from aa a,bb b where a.aid=b.bid(+);

bb b left outer join aa a on

右外联接：select a.*,b.* from aa a,bb b where a.aid(+)=b.bid;

bb b right outer join aa a on

满外联接：Full Outer Join

内联接：Inner Join

集合：

UNION：并集，所有内容都查询，重复的只显示一次

UNION ALL：并集，所有内容都显示，包括重复的

INTERSECT:交集，只显示重复的

MINUS：差集，只显示对方没有的

coalesce()：依次考察各参数表达式，遇到非null值即停止并返回该值。

case表达式：用来针对某一列数据生成新列。

create or replace view V_A as select b.a1,b.a2,b.a3,

case b.a1

when '1' then 'a'

when '2' then 'b'

else 'c'

end a4

from table b;

decode:同样是对某一列数据生成新列

select b.a1,b.a2,b.a3,

decode(b.a1,'1','a',

'2','b',

'c') a4 from table b;

count(*):若数据表中没有数据，返回的不是null，而是0.

分组函数avg,sum会自动省略列中的空值，如果不想跳过空值，可以使用NVL（）函数强制分组函数处理空值。

出现在select列表中的字段或order by后的字段，如果不在分组函数中，那么该字段必须同时在group by子句中出现。oracle中不允许一列数据匹配一个数据。可以使用where限定查询条件，order by指定排序方式。若没有group by子句，select中不允许出现字段（单行函数）和分组函数混用的情况。

where语句中不允许使用分组函数。

笛卡尔集：select * from table_a,table_b;

等值连接：select * from table_a,table_b where table_a.m=table_b.m;

非等值连接：select * from table_a,table_b where id between m and n;

自连接：select * from table_a a,table_b b where a.id=b.uid;

多行子查询：

select * from table_a a where a.id > any(select avg(a.name) from table_a a group by a.class);

这里的any也可以换成all，in

Top N查询：where rownum<=5

in 和exists性能区别：若子查询得出的结果集记录较小，主查询中的表较大且又有索引时应用in，反之，如果外层的主查询记录较少，子查询中的表达，又有索引时应用exists。

随机排序：order by dbms_random.value():为结果集的每一行计算一个随机数，dbms_random.value是结果集的一列，然后根据该列排序，得到的顺序就是随机的了。value返回的是number类型，返回的值在1和0之间，而random返回的是BINARY_INTEGER类型。

查询某用户下的所有表

select table_name from all_tables where owner="";

查询表中所有字段（列）

select * from all_tab_columns where table_name=' ';

列出表的索引列

select * from all_ind_columns where table_name=' ';

列出表中约束

select * from all_contraints where table_name=' ';

oracle中描述数据字典视图

select table_name,comments from dictionary where table_name like '%TABLE%';

char：固定长度，速度比varchar2快得多

varchar2适用于英文和数字，Nvarchar2适用于中文和其他字符。N:unicode常量，解决多语言字符集之间的转换问题

Number(6,4):6是总长度，4是小数位长度

blob:binary 二进制流的大对象

clob:文件大对象

体系结构（DBA）：

数据库：一系列物理文件（数据文件、控制文件、联机日志）的集合或与之对应的逻辑结构（表空间、段）被称为数据库。

数据库的实例：oracle内存结构（SGA、PGA）和后台进程

创建表：create table student(

sid number(10),

sname varchar2(10)

) tablespace tt;

tablespace:非必须，不写创建在当前用户的表空间。

使用子查询创建

create table a as select * from b;

添加字段：

Alter table a add age number(5);

修改字段：

Alter table a modify age number(10);

删除字段：

Alter table a drop column age;

清空表中数据：

Truncate table student //不可回滚

删表

Drop table a;

重命名表：Rename a to a1;

DML(改变数据结构)

insert语句：表间数据拷贝

insert into dept1(id,name) select deptno,dname from dept;

update语句：update myemp set ( )=( )

merge语句：(合并）

merge into test2 using test1 on (test1.eid=test2.eid)

when matched then

update set name=test1.name,birth=test1.birth

when not matched then

insert(eid,name,birth) values(test1.eid,test1.name,test1.birth);

建立外键时指定级联删除

create table book (

name varchar2(10),

CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE);

视图：封装了各种复杂查询的语句，叫视图

crease or replace view V_A as

视图建立时的两个参数：

1、with read only:只读，不可修改

2、with check option：保护视图的创建规则

若视图的基表有多行查询，(group by,distinct)，那么也是只读的

查看视图：select * from V_A

索引类型：1、B树索引：默认，平衡B树算法 2、位图索引

优化器：1、CBO：根据表及索引的状态信息选择计划

2、RBO：根据内部设置的一些规则决定选择计划

尽量不用IN、NOT IN 、<>、！=

尽量使用重复的SQL代码，利用SGA共享池，避开parse阶段，提高效率

尽量使用packages

尽量使用cached sequences 来生成primary key

SGA：共享池，数据缓冲区，日志缓冲区

loop循环：

loop

循环的语句

exit when 终止条件：

循环条件必须更改；

end loop;

while循环：

while(判断循环的条件) loop

循环的语句；

循环条件的改变；

end loop;

for循环：

for 变量名称 in 变量初始值....结束值 loop

循环语句；

end loop；

if语句：

if 条件 then

满足条件时，执行语句

end if；

if...else语句：

if 条件 then

语句

else

语句

endif；

end；

if...elseif...else语句：

if 条件 then

语句；

elseif 条件 then

语句；

else

语句；

endif；

end；

ROWTYPE：把一行的数据都装进来

delete truncate:

delete可以有条件删除，truncate将表数据全部删除

delete是DML语句，可以回退，truncate是DDL语句，立即生效，不能回退

若全部删除表记录，且数据量较大，delete语句效率比truncate低

删除全部记录：delete from myemp; truncate table myemp;

空引用null只能被转换成引用类型，不能转换成基本类型，不能把一个null值赋给基本数据类型的常量。