`
aixingjuele
  • 浏览: 1308 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类

Oracle 学习笔记

阅读更多
oracle 学习笔记
一、用户管理(第一天)
1.sys和system的最大区别为sys有创建数据库的权力,system没有!
2.对数据库的维护一般用system足够!
3.disc   断开连接。
4.执行 D: 一个  aa.sql   文件,start d:\aa.sql;。  
5.编辑D:的一个aa.sql 文件。 edit d:\aa.sql;。
6要把一段sqlplus 中的数据写入到一个文件 1.  spool d:\bb.sql;
然后查询数据。2. select * form emp;     3.  spool  off;
7.改其他用户的密码要 system  或 sys, 要改自己的密码 用  passw。
8.select * from emp where ename='&name'; 会让你自己输入一个条件!
9.设置行宽。   set linesize 50;
10.分页 pagesize; 设置每页显示多少条, set pagesize 6;
11.创建用户 需要 dba 的权限。
例: create user xiaoming(用户名) identified by system(密码);
12.给用户修改密码。  要dba的 权限。
例: password 用户名;
13.删除用户: 要dba 的权限, drop user 用户名;
(再删除用户时,注意,如果要删除的用户,已经创建了表,
那么就需要在删除时 带一个参数 cascade;)
14.给用户赋权限, grant connect,resource to xiaoming;
15.oracle的权限有两种:(1.系统权限:用户对数据库的相关权限。
2.对象权限:用户对其他用户的数据对象操作的权限。)
16.角色:多种权限的集合。
(角色分两种:1.预定义角色如(connect角色)。2.自定义角色。)
17.授予对象权限例,
对象权限(insert,delete,update,select,all(所有))
如scott 用户将 emp的查询权限赋给xiaoming;
grant select on emp to xiaoming;
xiaoming 查询:
select * from scott.emp;
18. 授予对象的权限的传递,scott 将 emp的select 赋给 xiaoming,
希望xiaoming又可以将 emp的select 权限 赋给别人!
scott 应该这赋权:
grant select on emp to xiaoming with grant option;
当xiaoming 又再一次把这种权限 传给xiaohong 那么 当scott 向xiaoming
收回权限时,xiaohong的对象权限会失去,但是我测试了,xiaohong的系统权限不会丢失,任然会存在!
xiaohong的对象权限会受到株连九族的影响,也会丧失scott.emp的权限。但拥有的connect的权限任然会有,就算connect权限是xiaoming赋给xiaohong的!
(例,省长倒台, 省长提拔的市长统统倒台!古代刑法。)
如果要授予系统权限的传递为:
grant connect to xiaoming with admin option;
19.收回权限(scott 希望收回xiaoming对emp的select 权限):
revoke select on emp from xiaoming;
20.使用profile管理用户命令(一般要使用dba的身份)。
例:让一个用户只能输入3次密码,如果3次密码不正确,就锁定这个用户2天。
create profile lock_account(名字) limit
failed_login_attemps 3((只能输3次)) password_lock_time 2(天(锁定时间));
21.将profile 文件 作用于 用户:
例: alter user xiaoming profile lock_account(profile的名字);
22.解锁(给账号解锁)
alter user xiaoming(用户名) account unlock;
23.可以让用户终止口令,强制让用户隔一段时间修改密码(创建profile文件需要dba的身份):
例:
create profile mm(profile名) limit password_life_time
10(10天改一次)
password_grace_time 2(宽限期2天);
这个命令作用于用户:
alter user xiaoming profile mm(profile名);
24.口令历史:
如用户修改密码时不能使用原密码:创建profile管理
create profile pp(profile名) limit password_life_time
10(口令每10天改一次) password_grace_time 2(宽限期两天)
password_reuse_time 10;
password_reuse_time 10(指定口令可重用的时间是10天后可以重用)
25.删除profile。此profile 约束的用户统统解放。
drop profile profile名。


二.oracle 表的管理(第二天)
1.char型:定长,最大2000,如果你的char定的为200,不管你的字段用了多长,
其他将会用空格占用,但char的查询效率非常高,如果哪个字段是定长的,
那么就一定用char,比如身份证!

2.日期类型:Date 或 timestamp(精度很高).

3.二进制数据:blob  图片,声音

4.给表添加一个字段。
alter table student add (classid number(2));
5.修改字段的长度。
alter table student modify (classid number(20));
6.修改字段的类型、或数字(不能有数据)
alter table student modify (classid varchar2(2));
alter talbe student modify (xx number(20));
7.删除一个字段。
alter table student drop column classid;
8.修改表的名字。
rename student to stu;
9.删除表
drop talbe student;

10.修改oracle的日期格式。
alter session set nls_date_format='yyyy-mm-dd';
或者自己转日期格式
to_date('2010-08-11','yyyy-MM-dd');

11.在查询的时候,如果要查询的某个字段为空!应该这样!
select * form student where birthday is null; 或 is not null

12.要想取消刚才的操作。
首先 插入  保存点  savepoint a;
然后执行增删改查   等等等的操作。
当需要回滚时   就执行  rollback to a;

13.删除数据:
delete from 表;
删除所有记录,表机构还在,可以回复
drop  table 表名;

truncate table 表名;(删除数据,表机构还在,无法找回删除的记录!)

14.distinct 取消重复行
select distinct * from emp;

15.查询 切忌  总用 select *     查询某个字段 要比 select * 快

16.set timing on;  打开 显示 每个操作的时间;

17.循环插入同一条数据:
insert into text(id,name) select * from text;

18.函数 nvl() 的用法。
select sal*13+comm*13 "年工资",ename,job from emp;
//查询 每个员工的年工资,   年工资 =sal月工资+月奖金comm;  但是这样查的话,如果月奖金等于空,则年工资也为空。
select sal*13+nvl(comm,0)*13 "年工资",ename,job  from emp;
//  nvl(comm,0):当 comm 为空时, 则用 0 代替, 如果 comm 不为空,则用  comm  值。

19.select 语句  ,like
%  代表任意多个字符,  _    代表任意 单个字符

20. select * from emp where (sal>500  or job='MANAGER')and ename like 'J%';
//查询 工资大于500 或 岗位 为 MANAGER    并且 ename 以 J 开头的员工!


21.select * from emp order by deptno asc,sal desc;
部门 号升序, 工资降序


Oracle 第二次开始
1.sys用户帮scott用户解锁:
alter user scott account unlock;

2.当你不小心删除一个表 emp 时,你 没有设置 savepoint保存点 ,所以你无法 rollback to 保存点。
那么 你可以用 FLASHBACK TABLE emp(表名)TO BEFORE DROP

3. set timing on; 显示操作时间!

4.查询出和 SMITH 是 相同部门的 相同岗位的 员工:
select * from emp  where(deptno,job)=(select deptno,job from emp a1 where a1.ename='SMITH');

5.查询所有比自己部门 平均工资高的 员工!(--内嵌试图--)
select * from emp a1,(select deptno,avg(sal) mysal from emp group by deptno)a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal;

Oracle 分页:
Oracle分页一共有 3 种:
1.rownum 分页。
select * from
(select a1.*,rownum rn from
(select * from emp where emp.sal>1500 order by emp.sal desc)a1 where rownum<=10)where rn>=6; --查询 rn 第 6条 到  第 10 条的记录!
所有的 查询 条件写在 最里层 的 select 语句里就可以了!

7、快速创建新表的方法:
create table mytable(id,name,sal,job,deptno)
as select empno,ename,sal,job,deptno from emp;

8、union 联合查询! 
union 回取消结果集的重复行。
union all 不会取消结果集的重复行
intersect 取交集!
minus 取 差集, 前面的查询集合 减去 后面的查询集合!

select count(distinct deptno) from emp;

备份数据库

1-----backup scott

      cmd
      exp

2      --创建 user xxw 分配在 表空间 users ,并且最多存储10M的数据
      create user xxw identified by System123 default tablespace users quota 10M on users
      --授权
      grant create session , create table , create view to xxw;

3     导入备份的数据文件 
      在 cmd imp


select * from user_tables;
desc user_tables

select * from user_views;
desc user_views


select * from user_constraints;
desc user_constraints;

desc user_indexes;
select * from user_indexes;

select * from dictionary;
数据字典表

Oracle 声明变量

declare v_num number :=0;
begin
    v_num :=2/v_num;
dbms_output.put_line(v_num);
exception
     when others then
dbms_output.put_line('error');
end;
/

和表一起声明变量
declare

v_empno2 emp.empno%type;
v_empno3 emp.empno%type;

-- table 变量类型  数组
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_emp_empno;
begin 
v_empnos(0):=6666;
v_empnos(1):=8888;
v_empnos(-1):=8866;
end;

--record  类变量类型
declare

type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
)
v_temp type_record_dept;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaaa';
v_temp.loc:='bj';
end

record 另一种声明方式, 属性随便的列增加减少自动变动
declare
      v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaaa';
end


plsql变量赋值
select deptno int v_deptno from emp2 where empno='7369';
select count(*) into v_count from emp2;
dbms_output.put_lin(sql%rowcount||"条记录被影响");
sql%rowcount 上一条sql语句影响了多少记录

执行一个sql语句
begin
    execute immediate 'create table t(nnn varchar2(20) default''aa'')';
end;

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal<1200) then
dbms_output.put_line("low");
elsif(v_sal<2000)then
dbms_output.put_line("middle");
else
dbms_output.put_line("many");
end if;
end;

循环: do-while
declare
i binary_integer:=1;
begin
loop
   dbms_output.put_line(i);
   i:=i+1;
   exit when(i>=11);
end loop;
end;

while 循环
declare
j binary_integer :=1;
begin
while j<11 loop
dbms_output.put_line(j);
j:=j+1;
end loop;
end;

for 循环 1-10
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
end;

for 循环 10-1
begin
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;

exception plsql中也有多重异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno =10;
exception
when to_many_rows then
dbms_output.put_line("太多记录了");
when no_data_found then
db,s_output.put_line("没数据");
when others then
dbms_output.put_line("error");
end;

创建 sequence
create sequence seq_errorlog_id start with 1 increment by 1;

--oracle 记录出错信息
crate table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1028),
errdate date
);
declare
v_deptno dept.detano%type:=10;
v_errcode number;
v_errmsg varchar2(1028);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
v_errcode := SLQCODE;
v_errmsg:=SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval,v_errcode,errmsg,sysdate);
commit;
end;

定义游标
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;

--循环游标 do-while
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;

--循环游标 while
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;

--循环游标 for
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;


--循环游标 for 带参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
select * from emp where deptno=v_deptno and job=v_job;
v_emp c%rowtype;
begin
for v_emp in c(30,'CLERK') loop
dbms_output.put_line(v_emp.ename);
end loop;
end;


--更新 游标
declare
cursor c  is
select * from emp2 for update;
v_emp c%rowtype;
begin
for v_emp in c loop
if(v_emp.sal<2000) then
    dbms_output.put_line('2000='||sql%rowcount);
    update emp2 set sal=sal*2 where current of c;
elsif(v_emp.sal=6000)then
    dbms_output.put_line('5000='||sql%rowcount);
    delete from emp2 where current of c;
else
    
     dbms_output.put_line('88000='||sql%rowcount);
end if;
end loop;
commit;
end;


--创建存储过程
create or replace procedure porc_new
is
cursor c  is
select * from emp2 for update;
v_emp c%rowtype;
begin
for v_emp in c loop
if(v_emp.sal<2000) then
    dbms_output.put_line('2000='||sql%rowcount);
    update emp2 set sal=sal*2 where current of c;
elsif(v_emp.sal=6000)then
    dbms_output.put_line('5000='||sql%rowcount);
    delete from emp2 where current of c;
else
    
     dbms_output.put_line('88000='||sql%rowcount);
end if;
end loop;
commit;
end;


带参数的存储过程
v_a 传入参数
v_ret传出参数
v_b 默认传入参数
v_temp 既可以传入,也可以传出
create  or replace procedure proc_para
(v_a in number,v_b number,v_ret out number ,v_temp in out number)
is
begin
if(v_a>v_b)then
v_ret:=v_a;
else
v_ret:=v_b;
end if;
v_temp:=v_temp+1;
end;

--调用带参存储过程
declare
v_a number :=3;
v_b number :=4;
v_ret number;
v_temp number :=5;
begin proc_para(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;

show errors;--展示存储过程创建时的错误。

--创建函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal<3000)then
return 0.10;
elsif(v_sal<3900)then
return 0.15;
else
return 0.20;
end if;
end;
select lower(ename),sal_tax(sal)from emp; --调用

触发器
create table emp2_log
(
uname varchar2(20),
action varchar(10),
atime date
);

create or replace trigger trig
after insert or delete or update on emp2
--after insert or delete or update on emp2 for each row
--befor insert or delete or update on emp2 for each row
begin
if inserting then
   insert into emp2_log values(USER,'insert',sysdate);
elsif updating then
   insert into emp2_log values(USER,'update',sysdate);
elsif deleting then
   insert into emp2_log values(USER,'delete',sysdate);
end if;
end;

create or replace trigger trig2
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deptno=:OLD.deptno;
end;
--test 
update dept set deptno=99 where deptno=10;
select * from emp;
分享到:
评论

相关推荐

    Oracle学习笔记精华版

    Oracle学习笔记精华版是针对数据库管理系统Oracle的一份重要学习资源,涵盖了从基础概念到高级特性的全面知识。Oracle,作为全球广泛使用的大型企业级数据库系统,对于IT专业人员尤其是数据库管理员(DBA)来说,是...

    Oracle学习笔记 PDF

    ### Oracle学习笔记知识点详解 #### 一、Oracle简介 Oracle是一家知名的软件公司,以其数据库管理系统闻名全球。该公司成立于1977年,总部位于美国加利福尼亚州。Oracle不仅提供数据库解决方案,还涉及中间件、...

    Oracle学习笔记

    Oracle学习笔记 Oracle学习笔记是李兴华老师编写的Oracle从入门到精通的学习笔记,涵盖了 Oracle 的多表查询、连接、组函数和分组统计等知识点。在本篇笔记中,李兴华老师详细介绍了多表查询的基本语法、左右连接...

    Oracle学习笔记.pdf

    在Oracle学习笔记中,对安装卸载和配置的详尽讲解,不仅为学习者提供了操作指导,而且还涉及到了数据库管理的一些基础知识点。这些内容对于数据库管理员和开发人员来说都是十分重要的,因为它们是操作Oracle数据库的...

    Oracle学习笔记.doc

    Oracle学习笔记 以下是我这一周学习oracle整理的笔记,包括课堂的内容和自己看额外看的视频补充的一些内容,基本上囊括了所有oracle的基本知识。主要的形式是例子代码加代码解释加运行结果,我个人认为对于没有学习...

    Oracle学习笔记-日常应用、深入管理、性能优化

    资源名称:Oracle学习笔记-日常应用、深入管理、性能优化内容简介:Oracle学习笔记-日常应用、深入管理、性能优化Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛...

    oracle学习笔记整理

    以下是对Oracle学习笔记整理的主要知识点的详细说明: 1. **数据库选择**: 在决定使用哪种数据库时,通常需要考虑项目的规模、性能需求、安全性要求以及可用资源。Oracle数据库因其稳定性、可扩展性和高性能而被...

    oracle学习笔记下载

    ### Oracle 学习笔记知识点概览 #### 一、Oracle 数据库系统参数查询与管理 在 Oracle 数据库的学习过程中,了解如何查看和管理数据库的系统参数是非常重要的。这些参数直接影响着数据库的性能和稳定性。 ##### ...

    ORACLE学习笔记:日常应用、深入管理、性能优化.part1/2

    ORACLE学习笔记:日常应用、深入管理、性能优化.part1

    oracle学习笔记-入门基础

    Oracle数据库是世界上最流行的数据库管理系统之一,它提供了丰富的特性和功能来优化数据管理和查询性能。本文主要探讨Oracle数据库的入门基础知识,特别是与索引相关的概念。 首先,我们要理解ROWID的概念。ROWID是...

    全网最全的oracle学习笔记

    全网最全的oracle学习笔记,oracle学习笔记,oracle,### 4、oracle的七个服务 ```sql 1、Oracle ORCL VSS Writer Service Oracle卷映射拷贝写入服务,VSS(Volume Shadow Copy Service)能够让存储基础设备(比如...

    Oracle学习笔记 Oracle学习笔记

    根据提供的信息,我们可以总结出以下Oracle数据库学习的关键知识点: ...以上是基于提供的内容整理出的Oracle学习笔记中的关键知识点。通过理解这些基础知识,可以更好地管理和操作Oracle数据库。

    Oracle学习笔记——日常应用、深入管理、性能优化 示例代码

    Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛应用。本书设计了大量的应用情景,介绍了数据库管理员和开发人员常用的管理、维护和优化Oracle 11g数据库的技术和...

    MSDN oracle学习笔记

    在“MSDN Oracle学习笔记”中,我们可以期待找到关于Oracle数据库的详细讲解和实践指导。 首先,Oracle数据库的基础知识是必不可少的。这通常涵盖数据库系统的基本概念,如SQL(结构化查询语言)的使用,数据类型,...

    oracle 学习笔记

    oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记oracle 学习笔记

Global site tag (gtag.js) - Google Analytics