Oracle 11g 数据库 学习笔记


1 / 16 笨蛋一休 兰州交通大学 Oracle 11g 笔记 Oracle 数据库 学习笔记 常见的数据库 小型数据库 access(微软) 中型数据库 mysql(瑞典MySql AB) sql server(美 微软) informix(美IBM) 大型数据库 sybase(美 sybase) oracle(美 oracle) db2(美 IBM) Oracle 基本操作 一、Oracle 数据库基本命令: 1.SQL->show user; 显示用户 2.SQL->conn scott/tiger;用户连接 3.SQL->disc; 断开连接 4. SQL->passw; 修改密码 5. SQL->exit; 退出 6. SQL->start D:\test.sql; 运行 D 盘下 test.sql 脚本 7. SQL->edit D:\test.sql; 编辑 D 盘下 test.sql 脚本 8. SQL->spool D:\test.sql; SQL->select * from emp; SQL->spool off; 二、Oracle 管理用户 1.创建用户(以 DBA 身份) SQL->create user name identified by password(以字母开头) 注意:新建用户默认是没有任何权限的,需要 DBA 赋予权限(1、系统权限:对数据库的相关权限;2、对象权限:用户对其他用 户数据对象(表、视图等)操作的权限) 2. grant 赋予用户权限 SQL->grant connect to username;(把角色 connect 赋予 username(dba 、resource)) SQL->grant select on emp to username;(emp 表的用户 scott 或 sys、system 把 select 权限赋予 username) username 用户可以进行 select 操作了,SQL->select * from scott.emp;(查询 emp 表的内容) SQL->grant select on emp to username;(把全部权限赋予) 3.revoke 回收用户权限 SQL->revoke select on emp from username; (scott 收回用户 username 对用户 scott 表 emp 的 select 权限(必须是 scott 赋予 username 的权限)) 4.权限的传递 对象权限:SQL->grant select on emp to username with grant option; 系统权限:SQL->grant select on emp to username with admin option; 5.给用户修改密码 SQL->password username;(password scott ;sys 修改 scott 密码) SQL->password;(scott 修改自己密码) 6.使用 profile 管理用户口令 账户锁定 SQL->create profile lockname limit failed_login_attempts 3 password_lock_time 2;(以 dba 身份)创建配置文件,名字为 lockname。 SQL->alter user username profile lockname; 账户解锁: SQL->alter user username account unlock; 删除 profile :SQL->drop profile lockname; Oracle 表的管理 一、表和列的命名规则 • 必须以字母开 • 不能使用oracle的保留字 • 只能使用A-Z,a-z,0-9,$,#等 • 长度限制为30个字符内 二、 oracle的数据类型 1.字符型 char() 定长 特点:查询速度快,效率高 ;浪费空间 最大:char(2000) varchar2()可变长度 特点:节省空间,效率低 最大varvhar(4000) clob()字符型大对象 最大4G 2.数字类型 number 可以表示整数和小数 number(5,2)一个小数有5位有效数,2位小数-999.99~999,99 number(5)一个5位整数-99999~99999 3.日期类型 date 包含年月日时分秒 4.图片类型 blob 二进制数据 可以存放图片、声音 三、表的操作 1.创建表 SQL> create table student ( 2 sID number(4), 3 sName varchar2(20), 4 sSex char(2), 5 sBirthday date, 6 sPay number(7,2) 区别于sql server 无逗号。 7 ); Table created SQL> create table class( 2 cId number(2), 3 cName varchar2(20) 4 ); Table created 2.修改表 添加字段 SQL> alter table student add (cId number(2)); 删除某个字段 SQL> alter table student drop column cId; Table altered 查看表结构 SQL> desc student; 修改字段长度 SQL> alter table student modify (cId number(5); 删除表 SQL> drop table student; 修改表名字 SQL>rename student to stu; 3.添加数据 SQL>insert into student values(1,'小明','男','11-5月-2011',12345.1,12); 1 row inserted 日期格式默认为DD-MON-YY 天-月份-年 可以修改默认日期格式 SQL> alter session set nls_date_format ='yyyy-mm-dd'; Session altered 修改一个字段 SQL>update student set sSex='男' where sId='2'; 1 row updated 添加空值 SQL> insert into student (sId,sname,sbirthday) values (4,'小明',NULL); 1 row inserted 查询时注意:SQL> select * from student where sBirthday is null; 删除数据 SQL>delete from student;删除所有记录,表结构还在,如果再删除之前设置回滚点还可以回滚。 回滚操作 SQL>savepoint a; SQL>delete from student; SQL>rollback to a; • SQL>drop table student;删除表结构和数据 • SQL>delete from student where sId=1;删一条记录 • SQL>truncate table student;删除所有记录,表结构还在,速度快对于大型表适用 oracle 表的基本查询 SQL> set timing on;显示执行时间 SQL> desc dept; 查看表结构 一、单表查询 取消重复行 SQL> select distinct deptno ,job from emp; NULL处理 SQL>select sal+nvl(comm,0) from emp;当comm为null时变为0 SQL Server中为select sal+isnull(comm,0) from emp; where子句 SQL>select ename,hiredate from emp where hiredate>'1-1月-1989'; like语句 SQL>select ename,sal from emp where ename like 'S%'; %:零个或多个字符 _:任意单个字符 逻辑操作符 or and order by 子句 SQL>select * from emp order by sal;默认为升序 SQL>select * from emp order by sal asc;升序 SQL>select * from emp order by sal desc;降序 使用列的别名排序 SQL>select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪" desc; Oracle复杂查询 max(),min(),avg(),sum(),count() group by用于对查询结果分组统计 having子句用于限制分组显示结果 SQL>select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal) asc; 二、多表查询 1.笛卡尔积 SQL>select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno; 2.自连接 自连接指在同一张表的连接查询 SQL>select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno; 3.子查询(嵌套查询) 嵌入在其他sql语句中的select语句。 单行子查询 SQL>select * from emp where deptno=(select depno from emp where ename='SMITH'); 多行子查询 SQL>select * from emp where job in (select distinct job from emp where deptno = 10 ); 多行子查询中使用all SQL>select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30); 等价 SQL>select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30); 多行子查询中使用any SQL>select ename,sal,deptno from emp where sal>any (select sal from emp where deptno=30); 等价 SQL>select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30); 多列子查询 SQL>select ename,sal from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH'); SQL>select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal; 注意表取别名是不加as,列可以。 SQL> select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2 join (select deptno,avg(sal) as mysal from emp group by deptno) a1 on a2.deptno= a1.deptno where a2.sal>a1.mysal ; 4.分页查询 SQL> select * from (select a.*,rownum rn from (select * from emp) a where rownum<=10 ) where rn>=6; SQL> select * from (select a.*,rownum rn from (select ename,sal from emp order by sal) a where rownum<=10 ) where rn>=6; 5.查询结果创建新表 SQL>create table mytable (id,ename,sal) as select empno,ename,sal from emp; 合并查询 集合操作符union union all intersect minus 差集 6.创建新的数据库 数据库配置助手Database Configuration Assistant oracle事务处理 sql函数 字符函数 lower()字符串转换为小写 upper()字符串转换为大写 length()求字符串长度 SQL> select lower(ename) from emp where length(ename)=5; substr( ,m,n) m起始位置 n长度 select substr(ename,1,3) from emp; SQL> select lower(ename) from emp where length(ename)=5; Replace(char,serachstring,replacestring) SQL> select replace(ename,'A','abcde') from emp; 总体应用 SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; 数学函数 round()四舍五入 SQL>select round(sal,2) from emp; trunc()截取 SQL>select trunc(comm,1) from emp; mod()模运算mod(10,3)=1 floor(n)小于或等于n的最大整数 ceil(n)大于或等于n的最小整数 日期函数 sysdate系统时间 last_day() 转换函数 to_char() 数据库管理 逻辑备份 cmd 导出表: bin>exp userid=scott/tiger@myoracle tables=(emp,dept) file=d:\*.dmp; 方案: bin>exp userid=system/manager@myoracle owner=scott file=d:\*.dmp; 导出数据库 bin>exp userid=system/manager@myoracle full=y inctype=complete file=d:\*.dmp; 导入表: bin>imp userid=scott/tiger@myoracle tables=(emp) file=d:\*.dmp; 方案: bin>imp userid=scott/tiger file=d:\*.dmp; 导出数据库 bin>imp userid=system/manager full=y file=d:\*.dmp; 物理备份 数据字典 记录数据库的系统信息,所有者sys,用户只能在数据字典上进行查询操作。 基表,存放静态数据 动态性能视图,动态数据 SQL>select table_name from user_tables;显示当前用户所拥有的所有表 SQL>select all_name from user_tables;显示当前用户可以访问的所有表 SQL>select table_name from dba_tables; 管理表空间和数据文件 数据库 表空间 区 段 块 建立表空间 create tablespace 改变表空间状态 SQL>alter tablespace 表空间名 offline; SQL>alter tablespace 表空间名 online; SQL>alter tablespace 表空间名 read only SQL>alter tablespace 表空间名 read write; 维护数据的完整性 约束 not null(非空) unique(唯一) primary key(主键)foreign key(外键) 表级定义/列级定义 check SQL>create table goods (goodsId char(8) primary key, goodsName() varchar2(30), unitprice number(10,2) check (unitprice>0), category varchar2(8), provider varchar2(30) ); SQL>create table custumer (custumerId char(8) primary key, name varchar2(50) not null, provider varchar2(50), email varchar2(50) unique, sex char(2) default '男' check (sex in ('男','女')). cardId char(18) ); SQL>create table purchase ( custumerId char(8) references custumer(custumerId), goodsId char(8) references goods(goodId), nums number(10) check (nums between 1 and 30) ); SQL>alter table goods modify goodsName not null; SQL>alter table customer add constraint un_check unique(cardId); SQL>alter table customer add constraint un_check check (address in ('','','')); 删除约束 SQL> alter table customer drop constraint un_check; 索引 加速数据存取 单列索引 SQL>create index nameindex on customer(name); 复合索引 SQL>create index nameindex on emp(name,job); 使用原则: 大表使用索引,经常使用的列建索引 触发器 应用程序(过程、函数) 权限 SQL> select * from system_privilege_map order by name; 角色 SQL> select * from dba_roles; connect resource dba .ect 系统权限(权限不级联回收)SQL>grant create session to sb with admin option; 对象权限(权限级联回收)SQL>grant select on scott.emp to sb with grant option; PL/SQL procedural language/sql 过程化语言 1.过程,函数,触发器是pl/sql编写 2.过程,函数,触发器是在oracle中 3.pl/sql是非常强大的数据库过程语言 4.过程,函数可以在java程序中调用 创建一个简单的表 SQL>create table mytest(name varchar2(20),passwd varchar2(10)); 创建过程 SQL>create or replace procedure proname is begin --执行部分 insert into mytest values('123','123'); end; / SQL>show error;--显示错误 调用该过程 SQL>exec 过程名(参数值1,参数值2,....); SQL>call 过程名(参数值1,参数值2,....); 块编程 三部分组成:定义部分,执行部分,例外处理 declare(可选) 定义部分:定义常量,变量,游标,例外.... begin 执行部分:要执行的pl/sql语句和sql语句 exception(可选) 例外处理部分:处理运行的各种错误 end; SQL> set serveroutput on;--打开输出选项 SQL> SQL> begin 2 dbms_output.put_line('hello world'); 3 end; 4 / hello world PL/SQL procedure successfully completed SQL>declare --定义变量 2 v_ename varchar2(10); 3 begin--执行部分 4 select ename into v_ename from emp where empno=&no; 5 dbms_output.put_line('用户名'||v_ename); 6 end; 7 / 预定义异常no_data_found declare --定义变量 v_ename varchar2(10); begin--执行部分 select ename into v_ename from emp where empno=&no; dbms_output.put_line('用户名'||v_ename); exception when no_data_found then dbms_output.put_line('未发现该编号'); end; 过程 用于执行特定的操作,当建立过程时,既可以指定输入参数(in)也可以指定输出参数(out)。 使用create procedure命令建立过程 存储过程 SQL> create procedure pro_select(proname varchar2,newsal number) is 2 begin 3 update emp set sal=newsal where ename=proname; 4 end; 5 / Procedure created 函数 SQL> create function fun (funName varchar2) return number is toalsal number(7,2); 2 begin 3 select sal *12+nvl(comm,0)*12 into toalsal from emp where ename=funName; 4 return toalsal; 5 end; 6 / Function created SQL>var abc number; SQL> call fun('SCOTT') into:abc; Method called abc --------- 36000 包 在逻辑上组合过程和函数 create or replace package pack as procedure pro_sal(name varchar2,newsal number) is begin update emp set sal=newsal where ename=name; end; function fun_income(name varchar2) return number is annual_salary number; begin select sal*12+nvl(comm,0) into annual_salary from emp where ename=name; return annual_salary ; end; end; 标量(scalar) 定义并使用标量(scalar) v_ename varchar2(10);变长字符串 v_sal number(6,2);定义一个小数 v_sal number(6,2):=5.4;定义一个小数并初始化 v_hiredate date;定义一个日期类型 v_valid boolean not null default false;定义一个布尔变量,不能为空,初始值为false declare c_taxrate number(3,2):=0.03; v_name varchar2(10); v_sal number(7,2); v_taxsal number(7,2); begin select ename,sal into v_name,v_sal from emp where empno=&npo; v_taxsal:=v_sal*c_taxrate; dbms_output.put_line('姓名'||v_name||'工资'||v_sal||'交税'||v_taxsal); end; SQL> set serveroutput on; SQL> SQL> declare 2 c_taxrate number(3,2):=0.03; 3 v_name varchar2(10); 4 v_sal number(7,2); 5 v_taxsal number(7,2); 6 begin 7 select ename,sal into v_name,v_sal from emp where empno=&npo; 8 v_taxsal:=v_sal*c_taxrate; 9 dbms_output.put_line('姓名'||v_name||'工资'||v_sal||'交税'||v_taxsal); 10 end; 11 / 姓名SCOTT工资3000交税90 PL/SQL procedure successfully completed declare c_taxrate number(3,2):=0.03; v_name emp.ename%type; v_sal emp.sal%type;--与表中数据类型保持一致 v_taxsal number(7,2); begin select ename,sal into v_name,v_sal from emp where empno=&npo; v_taxsal:=v_sal*c_taxrate; dbms_output.put_line('姓名'||v_name||'工资'||v_sal||'交税'||v_taxsal); end; 复合变量(composite) pl/sql记录 类似于高级语言种结构体 SQL> declare 2 type emp_record_type is record(name emp.ename%type,sal emp.sal%type); 3 reco emp_record_type; 4 begin 5 select ename,sal into reco from emp where empno=7788; 6 dbms_output.put_line('姓名'||reco.name||'工资'||reco.sal); 7 end; 8 / 姓名SCOTT工资3000 PL/SQL procedure successfully completed pl/sql表 相当于高级语言的数组 SQL> declare 2 type table_type is table of emp.ename%type index by binary_integer;--表示下标是整数 3 ename_table_type table_type; 4 begin 5 select ename into ename_table_type(0) from emp where empno=7788; 6 dbms_output.put_line('员工姓名'||ename_table_type(0)); 7 end; 8 / 员工姓名SCOTT PL/SQL procedure successfully completed 参照变量 游标变量(ref cursor) SQL> declare 2 type emp_cursor is ref cursor;--游标变量 3 emp_cursor_test emp_cursor; 4 v_emame emp.ename%type; 5 v_sal emp.sal%type; 6 begin 7 open emp_cursor_test for select ename,sal from emp where deptno=&no; 8 loop 9 fetch emp_cursor_test into v_emame,v_sal; 10 exit when emp_cursor_test%notfound; 11 dbms_output.put_line('姓名'||v_emame||'工资'||v_sal); 12 end loop; 13 end; 14 / 姓名CLARK工资2450 姓名KING工资5000 姓名MILLER工资1300 PL/SQL procedure successfully completed PL/SQL进阶 控制结构 1.条件分支 if-then if-then-else if-then-elsif-else SQL> create or replace procedure pro_select(proname varchar2) is 2 v_sal emp.sal%type; 3 begin 4 select sal into v_sal from emp where ename=proname; 5 if v_sal<4000 then 6 update emp set sal=sal*1.1 where ename=proname; 7 end if; 8 end; 9 / Procedure created create or replace procedure pro_select(proname varchar2) is v_comm emp.comm%type; begin select comm into v_comm from emp where ename=proname; if v_comm<>0 then update emp set comm=comm+100 where ename=proname; else update emp set comm=comm+100 where ename=proname; end if; end; 循环语句 loop-endloop; create or replace procedure pro_select(proname varchar2) is v_num number:=1; begin loop insert into users(,) values(v_num,proname); exit when v_num=10; v_num:=v_num+1; end loop; end; while循环 create or replace procedure pro_select(proname varchar2) is v_num number:=11; begin while v_num<=20 loop insert into users(,) values(v_num,proname); v_num:=v_num+1; end loop; end; for循环 goto语句 declare i number :=1; begin loop dbms_output.put_line('i='||i); if i=10 then goto loop_end; end if; i:=i+1; end loop; <> dbms_output.put_line('loop end'); end; null SQL> declare 2 v_sal emp.sal%type; 3 begin 4 select sal into v_sal from emp where empno=&empno; 5 if v_sal<4000 then 6 update emp set sal=sal*1.1 where ename=proname; 7 else 8 null; 9 end if; 10 end; 11 / 分页 create or replace procedure pro_select(proempno in number,proname out varchar2) is begin select ename intoproname from emp where empno=prompno; end; SQL>select * from (select emp1.*,rownum rn from (select * from emp) emp1 where rownum <=10) where rn>=6; SQL> create or replace package cursorpack as 2 type cursorpoint is ref cursor; 3 end cursorpack; 4 / Package created SQL> create or replace procedure fenye 2 (tablename in varchar2,--表名称 3 recordnum in number,--一页显示记录数 4 pagenow in number,--当前页码 5 recordcount out number,--总记录数 6 pagecount out number,--总页数 7 p_cursor out cursorpack.cursorpoint--游标 8 ) is 9 v_sql varchar2(1000);--sql语句 10 v_begin number:=(pagenow-1)*recordnum+1; 11 v_end number:=pagenow*recordnum; 12 begin 13 v_sql:='select * from (select emp1.*,rownum rn from (select * from emp) emp1 where rownum <='||v_end||') where rn>='||v_begin; 14 open p_cursor for v_sql; 15 v_sql:='select count(*) from '||tablename; 16 execute immediate v_sql into recordcount; 17 if mod(recordcount,recordnum)=0 then 18 pagecount:=recordcount/recordnum; 19 else 20 pagecount:=recordcount/recordnum+1; 21 end if; 22 close p_cursor; 23 end; 24 / Procedure created 例外 预定义例外 declare v_name emp.ename%type; begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('姓名'||v_name); exception when data_not_found then dbms_output.put_line('没有发现该项'); end; case_not_found cursor_already_open 游标已经存在 dup_val_on_index 唯一索引值所对应的列上插入重复值 invaild_cursor 在不合法的游标上执行操作。例:游标没有打开而提取数据 invaild_number 输入数据不合法 too_many_rows 返回超过一行 zero_divide 除数为零 value_error变量的长度不足以存储返回值 其他预定义例外 login denide 用户非法登录 not logged on 无用户登录 time on resource 链接资源超时 自定义例外 create or replace procedure excep(excepNo number) is myexcep exception; begin update emp set sal=sal+1000 where empno=excepNo; if sql%notfound then raise myexcep; end if; exception when myexcep then dbms_output.put_line('没有更新用户'); end; Oracle视图 视图是一个虚拟表,内容有查询定义。 表需要占用空间,视图不需要 视图不能建立索引 视图有利于提高安全性 create view myview as select * from emp where sal<1000; select * from myview;
还剩15页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

pe24

贡献于2013-10-18

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf