Oracle复习 随笔

1.模糊查询 like
% 表示零或多个字符
_ 表示一个字符
对于特殊符号可使用ESCAPE 标识符来查找
select * from emp where ename like '%*_%' escape '*'
上面的escape表示*后面的那个符号不当成特殊字符处理，就是查找普通的_符号

2.找出每个员工奖金和工资的总和
Select sal+comm,ename from emp;

3.字符函数

3.1   SELECT Upper ('abcde') FROM dual ;

3.2   SELECT lower('ABCDE') FROM dual ;

3.3   Select initcap(ename) from emp;  =>Ename

3.4   Concat
Select concat(„a‟,‟b‟) from dual;
Select „a‟ || „b‟ from dual;

3.5   Substr
Select substr(„abcde‟,length(„abcde‟)-2) from dual;
Select substr(„abcde‟,-3,3) from dual;

3.6   Instr
Select instr('Hello World','or') from dual; 8 indexof
3.7  Lpad
lpad('Smith',10,'*') 左侧填充 lpad() *****Smith
3.8  Rpad
rpad('Smith',10,'*') 右侧填充 rpad()Smith*****
3.9  Trim
trim(' Mr Smith ') 过滤首尾空格 trim() Mr Smith

4.  数值函数

Round select round(412,-2) from dual; select round(412.313,2) from dual;
Mod
Trunc select trunc(412.13,-2) from dual;

5.  日期函数

Months_between() select months_between(sysdate,hiredate) from emp;
Add_months() select add_months(sysdate,1) from dual;

 

Next_day() select next_day(sysdate,'星期一') from dual;
Last_day select last_day(sysdate) from dual;

 

6.   转换函数

 

To_char select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'fmyyyy-mm-dd') from dual; select to_char(sal,'L999,999,999') from emp;
select to_char(sysdate,’D’) from dual;//返回星期
To_number select to_number('13')+to_number('14') from dual;
To_date
Select to_date(„20090210‟,‟yyyyMMdd‟) from dual;

 

7. 通用函数

NVL()函数 select nvl(comm,0) from emp;
NULLIF()函数
如果表达式exp1与exp2的值相等则返回null，否则
返回exp1的值
NVL2()函数
select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;

CASE表达式
select empno, ename, sal,
case deptno
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'
end 部门
from emp;
DECODE()函数
和case表达式类似，decode()函数也用于实现多路分支结构
select empno, ename, sal,
decode(deptno, 10, '财务部',
20, '研发部',
30, '销售部',
'未知部门')
部门
from emp;

 

8.  使用子查询创建表 create table myemp as select * from emp; create table myemp as select * from emp where deptno=10;
create table myemp as select * from emp 1=2;

 

9.  添加字段
Alter table student add age number(5);

10. 修改字段
Alter table student modify age number(10); alter table table2 rename column result to result2;

 

11. 删除字段
Alter table student drop column age;

12.清空表中数据
Truncate table student;

正常情况下删除数据，如果发现删除错了，则可以通过rollback回滚。如果使用了截断表，则表示所有的数据不可恢复了.所以速度很快（更详细的说明可查看Oracle体系结构）

13. 创建索引 create index abc on student(sid,sname); create index abc1 on student(sname,sid);
这两种索引方式是不一样的
索引abc对Select * from student where sid=1; 这样的查询语句更有效
索引abc1对Select * from student where sname=‟louis‟; 这样的查询语句更有效
因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段放在组合字段的前面

索引的存储
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突。使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

 删除索引 drop index PK_DEPT1;

14.注意：
如果要创建用户只能在管理员下完成：
· 超级管理员：sys/sys
· 普通管理员：system/system
用户： ｓｃｏｔｔ／ｔｉｇｅｒ

14.1.创建用户
CREATE USERS 用户名 IDENTIFIED BY 密码。
|- create user test identified by admin
|- 用户名：test
|- 密码：admin

14.2  删除用户
Drop user test;
如果该用户下面已经存在表等一些数据库对象。则必须用级联删除
Drop user test cascade;

 

14.3  创建Session权限
一般在数据库中，一个用户的连接称为建立一个session，如果一个新的用户想访问数据库，则必须授予创建session 的权限 —— 用户授权
GRANT 权限 TO 用户。
给test用户以创建session的权限：GRANT create session TO test ;
以上用户虽然可以连接了，但是不能有任何的操作(比如创建表)

14.4  用户角色
角色是权限的集合
在Oracle中提供了两个角色，可以直接将这两个角色给用户：
· CONNECT角色：
· RESOURCE角色：
现在将这两个角色给test用户
GRANT CONNECT,RESOURCE TO test ;

14.5 锁住一个用户
· ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
|- ALTER USER test ACCOUNT LOCK ;
|- ALTER USER test ACCOUNT UNLOCK ;

14.6 密码失效
提示用户第一次连接的时候需要修改密码，让用户的密码到期
|- ALTER USER test PASSWORD expire ;

 

14.7  对象授权
以上的所有操作只针对于test用户。如果test要访问其他用户呢？例如，访问emp表
此时如果要想让其可以访问，则必须把scott用户下的emp表的查询权限给test。
GRANT 权限（select、update、insert、delete） ON schema.table TO 用户
|- GRANT select ON scott.emp TO test ;
|- Grant all on scott.emp to test; --将表相关的所有权限付给test
|- Grant update(ename) on emp to test; 可以控制到列(还有insert)

 

14.8 权限回收
REVOKE 权限 ON schema.table FROM 用户
|- REVOKE select ON scott.emp FROM test ;

 

14.9 查看权限 select * from user_sys_privs;

14.10  权限传递
Grant create session to test with admin option;(可以就可以实现权限传递)
Q：如果权限sys->test->test1 ，这时断掉test的权限， test1还会有权限吗？
A:在oracle9i是，答案是还会有。

14.11 角色
角色就是一堆权限的集合
Create role myrole;
Grant create table to myrole;
Drop role myrole; 删除角色

 

15. 备份 恢复 SQLLoader

在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法： 1. A 表的记录导出为一条条分号隔开的 insert 语句，然后执行插入到 B 表中 2. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where ...，或 insert into B select * from A@dblink where ... 3. exp A 表，再 imp 到 B 表，exp 时可加查询条件 4. 程序实现 select from A ..，然后 insert into B ...，也要分批提交 5. 再就是本篇要说到的 Sql Loader(sqlldr) 来导入数据，效果比起逐条 insert 来很明显
第 1 种方法在记录多时是个噩梦，需三五百条的分批提交，否则客户端会死掉，而且导入过程很慢。如果要不产生 REDO 来提高 insert into 的性能，就要下面那样做：
alter table B nologging;

 

sql loader的用法:

 

1. 只使用一个控制文件，在这个控制文件中包含数据(推荐) 2. 使用一个控制文件(作为模板) 和一个数据文件
Csv文件如下：（dept1.csv）
" ","DEPTNO","DNAME","LOC"
"1","10","ACCOUNTING","NEW YORK"
"2","20","RESEARCH","DALLAS"
"3","30","SALES","CHICAGO"
"4","40","OPERATIONS","BOSTON"
"5","50","sdsaf","adf"
"6","12","aaa","aaa"

 

Ctl文件如下：（dept1.ctl）

 

方式一：
Load data
Infile c:\dept1.csv
truncate
Into table dept1
(
Deptno position(1:2),
Dname position(3:5),
Loc position(6:8)
)

 

方式二：
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE "c:\dept1.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，
append --操作类型，用 truncate table 来清除表中原有记录
INTO TABLE dept1 -- 要插入记录的表
Fields terminated by "," -- 数据中每行记录用 "," 分隔
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时
trailing nullcols --表的字段没有对应的值时允许为空
(
virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号
deptno ,--"dept_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值
dname ,--"'Hi '||upper(:dname)",--,还能用SQL函数或运算对数据进行加工处理
loc
)

 

说明：在操作类型 truncate 位置可用以下中的一值： 1) insert --为缺省方式，在数据装载开始时要求表为空 2) append --在表中追加新记录 3) replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录 4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

执行命令
Sqlldr scott/tiger control=dept1.ctl
看看日志文件, 坏数据文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息

 

 

15.2