1.Windows下安装、卸载Oracle的相关事项:
(1).Oracle服务管理:
Oracle10g运行时需要800MB左右的内存,安装完Oracle后,会发现在Windows服务中自动开启了很多个Oracle相关的服务,其实Oracle一般只需要启动Oracle和Oracle监听两个服务就可以了,将其他的Oracle服务停止,可以减轻内存占用量,加快机器运行速度。
(2).卸载Oracle时注意事项:
a.停止所有的Oracle服务。
b.使用Oracle卸载工具oracle universal installer卸载Oracle。
c.修改windows注册表,删除Oracle相关信息:
Oracle软件相关键值:HKEY_LOCAL_MACHINE/SOFTWARE/Oracle。
Oracle服务相关键值:HKEY_LOCAL_MACHINE/SYSTEM/CurrentControl/Set/Services。
Oracle事件日志键值:HKEY_LOCAL_MACHINE/SYSTEM/CurrentControl/Set/EventLog/Application。
d.删除Oracle系统目录。
e.删除Oracle环境变量。
f.删除程序菜单项中的oracle项。
g.重启机器后删除oracle的工作目录。
注意:Windows环境下卸载Oracle一定要按上述步骤,负责有可能卸载失败,或者重新安装失败。
2.Oracle安装后默认是没有安装图形化操作工具的,如果使用图形化操作界面,可以安装:(1).Oracle Home Client(Oracle官方提供的一个用java swing做的图形化界面管理工具,比较庞大),侧重于Oracle日志归档、高级复制、方案优化等底层管理。
(2).Sql developer:Oracle提供一款也是基于Java开发的图形化界面工具,侧重于数据管理和Sql语句编写执行等。
(3).PL/SQL developer:和sql developer类似,侧重于Oracle的pl sql语句编写和执行。
(4).Navicat Lite:是一款比较优秀的图形化数据库管理软件,分为商业版和个人免费版,功能强大,即可提供底层管理,也同时提供了强大的数据管理、sql语句编写和执行等功能。
3.使用Oracle的plsql命令行执行注意事项:
(1).Oracle数据库插入、删除和更新操作后必须使用“commit”命令提交事务后,才能使数据库真正发生改变。
(2).Oracle数据库对SQL语句的大小写不敏感,同时也支持运算符。日期型的数据类型也可以进行算术运算,日期型数据类型直接和整数运算相当于加减多少天。
(3).查询语句中使用字符串和日期时,要用单引号(‘)括起来,字符串大小写敏感,日期类型格式敏感,缺省日期格式为:‘DD-MON-YY‘,获取当前缺省日期格式sql语句为:select sysdate from dual。
4.Oracle的字符串拼接符:
“||”是Oracle的字符串拼接符。相当于java中的字符串拼接符“+”。
5.Oracle查询排序:
使用order by 列名1,列名2……
默认按升序(ASC)排列,可以指定为降序(DESC)排列。
注意:升序(ASC)时null在最后,降序(DESC)时null在最前。
6.SQL语句通配符和运算符:
(1).”%”:表示0个或多个字符。
(2).”_”:表示1个字符。
(3).特殊字符使用ESCAPE进行转义,单引号(‘)使用两个单引号转义。
(4).运算符的优先级由高到低为:Not、And、Or。
7.Oracle数据库的函数:
Oracle数据库的函数都有返回值,
(1).单行函数:
指对每一返回行起作用的函数,包括字符、数值、日期转换和通用函数。
a.字符函数:
lower():将字符转换为小写。
upper():将字符转换为大写。
initcap():字符单词首字母大写。
concat():字符串连接。
instr():定位字符串。
lpad():左侧填充。
rpad():右侧填充。
trim():过滤首尾空格。
replace():字符替换。
b.数值函数:
abs():取绝对值。
round():四舍五入。
trunc():截断。
ceil():向上取整。
floor():向下取整。
sign():判断正负。
c.数值和字符串转换函数:
数字——>字符串:
to_char(number)
to_char(number, ‘模式串’)
字符串——>数字:
to_number()
to_number(char, ‘模式串’)
d.日期函数:
日期可以直接加减整数,两个日期之间也可以直接相减。
round():四舍五入到月。
日期——>字符串转换:
to_char()确实转换为“dd-mm-yy”格式。
to_char(date, ‘日期格式’)转换为模式串指定格式。
字符串——>日期:
to_date()
to_date(char, ‘日期格式’)按模式串指定格式进行解析。
e.通用函数:
nvl():将空值null替换为特定的缺省值,适用于字符、数字、日期等。
nvl(exp1, exp2):若exp1值为null,则返回exp2的值。
nvl2():用于实现条件表达式功能。
nvl2(exp1, exp2, exp3):若exp1的值不为null,则返回exp2的值,否则返回exp3的值。
nullif():用于数据等价性比较,并根据比较结果返回null或其中一个比较的数值。
nullif(exp1, exp2):若exp1与exp2相等,则返回null,否则返回exp1的值。
coalesce():用于实现数据接合功能。
coalesce(exp1, exp2):依次考察各参数表达式,遇到非null值即停止并返回该值。
(2).分组函数:
针对多行记录只返回一个结果(通常忽略空值)。
avg():计算平均值。
count():计算记录数。
max():计算最大值。
min():计算最小值。
sum():求和。
注意:过滤分组函数必须只能使用having而不能使用where,分组函数最多只可嵌套两层。
8.Oracle中实现多路分支条件:
(1).case表达式可以实现多路分支结构:
case exp when exp1 then return _exp1
when exp2 then return _exp2
……
else _expn
end
(2).decode函数和case表达式类似,也可以实现多路分支:
decode(exp1, search1, result1
,search2, result2
……
default)
9.Oracle中的连接:
(1).外连接(outer join):
外连接可以看到参与连接的某一方中不满足连接条件的记录。外连接运算符为”(+)”。
a.右外连接(right join):
如:where table1.column1(+) = table2.column2。
table1中不满足条件的也可以查询到。
b.左外连接(left join):
如:where table1.column1 = table2.column2(+)。
table2中不满足条件的也可以查询到。
c.交叉连接(cross join):
相当于连接时设置限制条件。
d.自然连接(natural join):
等值连接。
e.using(列名):
指定列自然连接。
f.on(exp1=exp2):
设置任意连接条件。
(2).内连接(inner join):
内连接只返回满足连接条件的数据。
10.TopN查询:
TopN查询是指查询数据库表中符合查询条件的前N条记录,Oracle通常采用子查询方式来实现TopN查询,具体语法:
select 字段列表
from (select 字段列表 from table order by 排列字段)
where rownum <=n;
注意:子查询中是临时表。
TopN查询常用于数据库分页,如查询表中第10到20条记录:
select * from
(select row num row, a.* from ((select * from table order by 排列字段) a)) where row >=10 and row <= 20;
11.数据库表操作:
(1).复制表数据:
insert into 表名1(列名1,……) select 列名1…… from 表名2;
(2).复制表结构:
Create table 表名1 as select * from 表名2 where 1 <> 1;
(3).数据合并:
merge关键字用于数据合并,若记录在目标表中已存在则更新,否则插入记录。语法:
merge into 表名 using(表/视图/子查询)
on (连接条件)
when matched then
update set ……
when not matched then
insert(列) values(值);
(4).保存点:
保存点savepoint相当于跳转标记,可以回退到指定保存点,实现事务的部分回滚。
(5).方案Schema:
Oracle中的方案Schema相当于其他数据库中的database。
(6).使用子查询创建表:
create table(字段列表) as select ……;
(7).修改表结构:
alter table add/modify/drop(……)。
注意:只能添加字段,或者修改约束条件,不能修改字段名称,若要修改字段名,则只能先删除再添加。
(8).修改表名称:
rename old_name to new_name;
<!--[if !supportLists]-->7. <!--[endif]-->删除表和数据:
(1).删除表数据:
a.delete from 表名;
b.truncate table 表名;
相同点:
只删除数据库,表结构保留。
区别:
delete是数据操作语言(DML),只清空表中数据,效率低,但是可以撤销回滚。
truncate是数据定义语言(DDL),清空表中所有数据,释放表的存储空间,一旦执行不可回滚撤销。
(2).删除表结构:
drop table 表名;
也是数据定义语言(DDL),数据表的结构和数据都被删除。
<!--[if !supportLists]-->8. <!--[endif]-->Oracle中查看表和对象:
Oracle中表、视图、存储过程、触发器等都是对象,可以通过以下方式查看:
(1).查看当前用户拥有的所有表名称:
Select table_name from user_tables;
(2).查看当前用户可以访问的所有表名称:
Select table_name from all_tables;
(3).查看当前用户拥有的所有对象的类型:
Select distinct object_type from user_objects;
(4).查看所有用户拥有的所有表名称:
Select table_name from dba_tables;
(5).查看所有对象:
Select * from user_catalog;
<!--[if !supportLists]-->9. <!--[endif]-->Oracle的5种约束:
(1).not null:字段值非空约束。
(2).unique:字段值唯一约束。
(3).primary key:主键约束。
字段级:字段名 类型 primary key;
表级:primary key(列名);
(4).foreign key:外键约束。
字段级:字段名 references 表名(字段名);
表级:foreign key(列名) references 表名(列名);
(5).check:自定义约束。
15.Oracle数据库中的动态替换变量:
动态替换变量是指在运行时由外部传入替换的变量。
(1).’&变量名’:
如,select * from emp where empno = &a;
注意:字符和日期类型的替换变量需要使用单引号(‘)引起来。
(2).使用define关键字预先定义动态变量:
语法:define 变量名 = ……
如,select * from emp where empno = &变量名;
(3).使用’&&变量名’可以重复引用动态变量,而不用再次定义。
16.视图:
数据库的视图是符合某种特定查询条件的逻辑表,而非物理存在的表结构,用视图可以方便地进行访问权限控制等。
(1).创建视图:
create view 视图名 as 查询字句;
注意:在创建视图时,加上with check option可以限制视图条件。
(2).修改视图:
create or replace view 视图名
17.序列:
Oracle数据库中,要使字段值自增,是通过一个叫做序列(sequence)来控制的。
(1).创建序列:
create sequence 序列名
increment by 1
start with 1;
(2).修改序列:
alter sequence 序列名……;
注意:序列的起始序号不能修改。
(3).删除序列:
drop sequence 序列名;
(4).使用序列:
select 序列名,nextVal from dual ;
18.同义词:
Oracle中同义词synonym是给某个对象名一个别名。
语法:create public/private synonym 同义词名 for ……
19.Oracle的权限管理:
(1).授权:
grant 权限1,权限, …… to user | role | public
[with admin option](权限可以传递)
(2).更改口令:
alter user 用户名
identified by 口令;
(3).授予对象权限:
grant 对象权限 on 对象 to user | role ……
[with grant option](传递权限)
(4).将某个表上所有权限授予某个用户:
grant all on 表名 to 用户名
(5).授予表中某列权限给某用户:
grant 权限(列名) on 表名 to 用户名
注意:只有插入和更新权限可以限制到列,其他权限不行。
(6).回收权限:
revoke 权限 on 对象 from user | role ……
20.使用Database links建立远端数据库连接:
create public database link
using ……
21.登录Oracle数据库几种方式:
(1).以默认的sys帐号登录:
sqlplus / as sysdba
(2).以某个用户登录:
sqlplus 用户名/密码
(3).不用用户名和密码方式登录:
sqlplus / nolog
注意:该种方式只能权限很低,几乎不能做什么操作。
(4).使用操作系统验证登录:
conn / as sysdba
22.Oracle数据库的三种验证机制:
(1).操作系统验证。
(2).密码文件验证。
(3).Oracle数据库验证。
注意:操作系统和密码文件都是用sys(dba)用户验证方式。
23.Oracle密码文件验证:
在Oracle的安装目录下的database子目录中,有一个PWDOracle.ora文件即为密码文件。
生成密码文件命令:orapwd file=文件路径 password=密码
注意:生成的密码文件名必须和原来的密码文件名一样。
24.Oracle系统管理员(dba)对用户的控制:
(1).创建用户:
create user 用户名 identified by 密码;
(2).修改用户:
alter user 用户名 identified by 密码;
(3).用户加锁:
alter user 用户名 Account lock;
(4).用户解锁:
alter user 用户名 Account unlock;
(5).用户口令立即失效:
alter user 用户名 password Expire;
(6).删除用户:
drop user 用户名 [cascade]
25.使用sqlplusw:
sqlplusw是Oracle数据库的操作窗口,其常用操作如下:
(1).编辑文件:
ed 文件名
(2).执行文件:
@文件名
(3).继续执行上一条操作命令:
/
26.Oracle数据库的文件结构:
(1). 物理结构:
参数文件——>控制文件——>数据文件、日志文件
(2).内存结构:
系统全局区(SGA)——>DB高速缓存池、共享池、Redo缓存池、大共享区、固定SGA。
DB告诉缓冲池——>默认缓存池、保持缓存池、再生缓存池。
共享池——>共享SQL区、库缓存区、字典缓冲区。
(3).逻辑结构:
块、盘区、段、表空间、数据文件。
26.Oracle的启动和停止命令:
(1).startup mount:只启动控制文件,不启动数据文件。
(2).startup nomount:不启动控制文件。
(3).shutdown immediate:停止数据库。
27.Linux/Unix下Oracle数据库启动过程:
(1).lsnrctl start:启动Oracle监听。
(2).sqlplus 用户名/密码 as sysdba。
(3).startup
28.子查询的限制:
(1).在子查询中使用in关键字时,子查询中不能使用”*”。
(2).在子查询中使用exists关键字时,可以使用”*”。
29.并集合交集运算:
(1).union:并集,将结果合并,并自动去掉重复行。
(2).intersect:交集,将两者共有的记录保留。