Oracle自学笔记.doc

Oracle命令一.连接命令1. conn system/adminsaOrcl as sysdba(sysoper)2. disc3. passw4. show user;5. exit二.文件操作1. start D:query.sql | D:query.sql2. edit D:query.sql3. spoolspool D:query.sql;select * from emp;spool off;三.交互命令1. Select * from emp where ename=&ename;四.环境变量1. set linesize 80;2. set pagesize 5;Oracle用户管理1. 创建用户(需要管理员用户权限)create user username identified by password;2. 给用户修改密码(需要管理员用户权限)password username3. 删除用户(需要管理员用户权限)drop user username注:如果要删除的用户已经创建表,需加参数cascade ,将同时删除表4. 授权用户(需要管理员用户权限)grant connect to username;注:Oracle权限分为两种 a.系统权限:用户对数据库的相关权限,如登录,建库表索引修改密码等140 b.对象权限:用户对其他用户的数据对象的访问的权限25数据对象:表 视图 存储过程等角色是对用户权限的批量授权角色分为两种 a.预定义角色 b.自定义角色三种比较重要的角色1.connect2.dba3.resource对象权限的授权grant select on tableName to username;-只授权select对象权限给某用户select * from scott.emp;-涉及到方案的概念对象权限的传递grant select on tableName to username with grant option;系统权限的传递grant connect to username with admin option;5. 锁定和解锁用户alter user scott account lock;alter user scott account unlock;6. 撤销授权revoke select on tableName from username;注:使用传递授权时 如果上级权限被撤销时 下级的权限同时被撤销 形象比喻为株连政策7. 查看表结构desc tableName8. 使用profile管理用户的密码(1).create profile profilename limit failed_login_attempts 3 password_lock_time 1;alter user scott profile Account.lock;注:创建名为profilename的配置文件,内容为登录失败尝试次数3 密码锁定 1 天.(2).create profile profilename limit password_life_time 27 password_grace_time 3;注:创建名为profilename的配置文件,内容为强制27天修改一次密码 宽限3天(3).create profile profilename limit password_life_time 27 password_grace_time 3 password_reuse_time 30;注:启用password历史(4).drop profile profilename cascade;注:删除用户配置文件 加参数cascade同时删除相关约束Oracle表管理1. 表的操作(1). 创建表create table student( studentId number(4), studentName varchar2(12), studentSex char(2), studentBirthday date, studentSalary number(7,2) );(2). 添加表字段alter table student add(studentAdd varchar2(50);(3). 修改表字段alter table student modify(studentAdd varchar2(100);(4). 删除表字段alter table student drop column(studentSalary number(7,2);(5). 表重命名rename student to studentTable;(6). 表删除drop table student;注: 表中有数据时 修改表字段 删除表字段和修改表名操作尽量避免 有风险(7). 插入记录Insert into scott.student (studentID,studentName,sex,studentBirthday) values (1,祁连山,男,1986-5-21);(8). 修改记录update student set sex=男,studentBirthday=1986-5-20 where studentID=1;(9). 删除记录delete from student;-删除student表中所有数据truncate table student;-删除student表中所有数据注:以上两者的区别在于delete删除时写日志 以便回滚 回滚操作见下面第十条 truncate删除时不写日志 当数据量很大时可明显提高速度delete from student where studentID10;-删除学号大于10的所有记录(10). 使用Oracle的写日志功能来恢复数据savepoint pointname;delete from student;rollback to pointname;注:在delete操作之前先做好回滚标记 可在delete操作之后使用rollback to pointname回滚2. 表的查询(1). 查询表结构desc student;(2). 查询表所有记录select * from student;(3). 查询并计算有空值的列 以scott.emp表计算雇员的年工资select ename 姓名,sal*12+nvl(comm,0)*12 年工资 from scott.emp;(4). 其他一些查询示例select * from emp where sal between 2000 and 2500;select * from emp where sal2000 and sal(select avg(sal) from emp);用上面的理解方法来理解这个查询也很简单3). 这样理解就可以写出更复杂一些的查询了,再看看怎么给工资低于平均工资并且雇用日期在1981-6-1之前的员工工资上调10%.select ename 姓名,sal 原工资,sal*1.1 调整后工资 from scott.emp where sal(select avg(sal) from scott.emp) and hiredate(to_date(1981-6-1,yyyy-mm-dd);(2). group by和having子句group by:用来对查询结果的分组统计having:用于限制分组显示的结果1). 计算各个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;注:分组字段必须出现在查询中,不然会出错.2). 查询各个部门里各种职位的最低,最高和平均工资select min(sal),max(sal),avg(sal),deptno,job from emp group by deptno,job;3). 查询部门平均工资低于2000的部门编号和平均工资select deptno,avg(sal),max(sal),min(sal) from emp group by deptno having avg(sal)all(select sal from emp a1 where deptno=30);还有另一种表示如下select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.sal(select max(sal) from emp a1 where deptno=30);4). 查询工资比部门编号为30的部门中所有员工工资还高的员工信息select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.salany(select sal from emp a1 where deptno=30);同上还有另一种表示select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.sal(select min(sal) from emp a1 where deptno=30);5). 查询与SMITH所在的部门和职位完全相同的员工信息(多列子查询)select * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH);同样,要是把查询分开来也是可以的,只是比起来要麻烦很多!select * from emp where deptno=(select deptno from emp where ename=SMITH) and job=(select job from emp where ename=SMITH);6). 显示各部门工资高于本部门平均工资水平的员工信息select a1.ename,a1.sal,a1.deptno,a2.avgsal from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.avgsal;6. Oracle的分页查询 分三种1). rowid分页 执行速度最快2). rownum分页 执行速度较快查询emp表的4-10条记录 这个查询很简单吧 写出来看看select * from emp where rownum=4 and rownum=10;奇怪 怎么查出来没有结果呢 道理想来想去就是这样没错 但是这个查询Oracle应该这样写select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum=4;注:好好想想为什么要这样写,看了好多遍还是没有想明白,这个查询只要改一下就查不出结果来,实在费解!3). 分析函数分页 执行速度最慢7. 合并查询(集合操作符union,union all,intersect,minus) 有人说速度比and和or快的多1). union求两个集合的并集(去重复)select ename,sal from emp where sal2500 union select ename,sal from emp where job=MANAGER;2). union all求两个集合的并集(不去重复)select ename,sal from emp where sal2500 union all select ename,sal from emp where job=MANAGER;3). intersect求两个集合的交集select ename,sal from emp where sal2500 intersect select ename,sal from emp where job=MANAGER;4). minus求两个集合的差集select ename,sal from emp where sal2500 minus select ename,sal from emp where job=MANAGER;8. 用查询结果创建新表create table empnew (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;Oracle数据库的创建和删除1. 使用Oracle Database Configuration Assistant来创建新的或删除已有的数据库Oracle的事务处理数据一致 银行转账只读事务 定时统计set transaction read only;Oracle函数1. 字符函数lower(char) upper(char) length(char) substr(char) replace(char,a,A)把a换成A instr(china,in)2. 数学函数round()/四舍五入round (1.553)-2round (1.553,1)-1.6round (1.553)-1.55trunk()/截取数字 trunk(123.123)-123trunc(123.123,1)-123.1trunc(123.123,-2)-100mod()/取模floor()/向下取整ceil()/向上取整cos()cosh()exp()ln()log()sin()sinh()sqrt()tan()tanh()acos()asin()atan()abs()power(m,n)/m的n次幂exp(n)/e的n次幂atan()/反正切函数3. 日期函数to_date(1986-6-29,yyyy-mm-dd)sysdateadd_months(d,n)last_day(n)/返回指定日期所在月份的最后一天应用举例查找已经八个月多的员工信息select * from emp where sysdateadd_months(hiredate,8);查询每个员工入职的天数select ename,sal,trunc(sysdate-hiredate) hiredays from emp;找出各月倒数第三天入职的员工select * from emp where last_day(hiredate)-hiredate=2;4. 转换函数to_char()查看当前系统时间(带时分秒)select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;查看所有12月份入职的员工信息select * from emp where to_char(hiredate,mm)=12;to_dateinsert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7796,MICCO,MANAGER,7782,to_date(1983-9-10,yyyy-mm-dd),2000,null,10);5. sys_context()terminallanguagedb_namenls_date_formatsession_usercurrent_schema(当前会话默认方案名)host用法select sys_context(userenv,db_name) from dual;/红色部分固定Oracle管理员的基本职责1. 安装和升级Oracle数据库2. 建库、表空间、表、视图、索引等3. 制定并实施备份与恢复计划4. 数据库权限管理、优化和故障排除5. 高级DBA 要求能参与项目开发 会编写SQL语句 存储过程 触发器 规则 约束 包等用户sys和 system 用户的权限区别sys用户角色dbasysdbasysopersystem用户角色dbasysdbasys用户登录必须指定是以sysdba身份还是sysoper身份登录系统 不能以Normal方式登录Oracle数据库的备份与恢复1. 数据库的逻辑备份(1). 导出表(结构+数据)exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp;(2). 导出其他用户的表exp userid=scott/tigerOrcl tables=(scott.emp,scott.dept) file=D:scott.dmp;(3). 导出表结构exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp rows=n;(4). 直接导出 速度上会有优势 但是要求源和目标字符集一致exp userid=scott/tigerOrcl tables=(emp,dept) file=D:scott.dmp direct=y;(5). 导出方案(针对用户)导出整个数据库exp userid=scott/tigerOrcl owner=(system,scott) file=D:scott.dmp;(6). 数据库的完全备份exp userid=system/managerOrcl full=y inctype=complete file=D:full.dmp;2. 数据库的导入(1). 导入自己的表imp userid=scott/tigerOrcl tables=(emp) file=D:emp.dmp;(2). 导入其他用户的表imp userid=system/managerOrcl tables=(emp) file=D:emp.dmp touser=scott;(3). 只导入表结构imp userid=scott/tigerOrcl tables=(emp) file=D:scott.dmp rows=n;(4). 导入数据imp userid=scott/tigerOrcl tables=(emp) file=D:emp.dmp ignore=y;(5). 导入自身方案imp userid=scott/managerOrcl file=D:scott.dmp;(6). 导入其他用户方案imp userid=system/manager file=D:scott.dmp fromuser=system touser=scott;(7). 导入数据库imp userid=system/manager full=y file=D:full.dmp;数据字典、数据字典基表和动态性能视图数据字典记录了数据库的系统信息，它是只读表(数据字典基表)和视图(数据字典视图)的集合。其所有者是sys。用户只能在数据字典上执行查询操作，其维护和修改是由系统自动完成的。数据字典由数据字典基表和数据字典视图组成，基表存储数据库的基本信息，数据字典视图是基于数据字典基表建立的视图，普通用户不能访问数据字典的基表，但可能查询数据字典视图取得相关系统信息。数据字典视图的命名主要有user_ tables、all_ tables和dba_tables三种。user_tables属于当前用户的表all_ tables所有当前用户可以访问的表dba_tables(dba才可以查询)所有方案的表用户、权限、角色对应的数据字典用户dba_users系统权限dba_sys_privs角色dba_role_privs对象权限dba_tab_privs列权限dba_col_privs查询SCOTT用户的角色select * from dba_role_privs where GRANTEE=SCOTT;查询Oracle中的所有角色select * from dba_roles;查询Oracle中的所有系统权限select * from system_privilege_map;查询Oracle中的表空间select distinct privilege from dba_tab_privs;管理表空间和数据文件表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件(最大500MB)中，从逻辑上讲，数据库则是存放在表空间中。表空间是由一个或多个数据文件组成。默认表空间为SYSTEM，该表空间不能更改状态。Oracle中逻辑结构包括表空间、段、区和块。表空间分为段、段分为区、区又分为块。1. 创建表空间create tablespace ext datafile D:ext.dbf size 20M unoform size 128K;2. 使用表空间create table newexttab(userid number(4),username varchar2(8),usersex char(2),passwd varchar2(16) tablespace ext;3. 改变表空间状态脱机/联机/只读/读写alter tablespace ext offline/online/read only/read write;4. 改变表空间大小(1). 增加数据文件alter tablespace ext add datafile D:exts.dbf size 20M;(2). 改变数据文件大小alter tablespace ext D:ext.dbf resize 20M;(3). 设置数据文件自动增长alter tablespace ext D:ext.dbf autoextend on next 10M maxsize 500M;5. 查看表空间中的表select * from all_tables where tablespace_name=ext;6. 查看表所在表空间select tablespace_name,table_name from user_tables where table_name=emp;7. 删除表空间drop tablespace ext including contents and datafiles;8. 移动数据文件(1). 确定数据文件所在的表空间select * from dba_data_files;(2). 脱机正在使用的表空间alter tablespace ext offline;(3). 物理移动数据文件到指定位置host move D:ext.dbf F:ext.dbf(4). 逻辑移动数据文件alter tablespace ext rename datafile D:ext.dbf to F:ext.dbf;(5). 联机表空间alter tablespace ext online;Oracle数据完整性约束、触发器和应用程序(过程、方法)约束:not nulluniqueprimary keyforeign keycheck例如商店售货系统表的设计如下:1. 创建商品表create table goods(goodsId char(8) primary key,-主键 goodsName varchar2(30), uniquePrice number(7,2) check (uniquePrice 0), category varchar2(8), provider varchar2(30);2. 创建客户表create table customer(customerId char(8) primary key, name varchar2(20) nor null, sex char(2) default 男 check (sex in (男,女), address varchar2(50), email varchar2(50) unique, cardId char(18);3. 创建销售表create table purchase(customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(2) check (nums between 1 and 30);4. 增加goods表中goodsName的非空约束alter table goods modify goodsName not null;5. 增加客户表中的cardId唯一约束alter table customer add constraint cardUnique unique(cardId);6. 增加客户表address的check约束alter table customer add constraint addressCheck check(address in(address1,address2,address3);增加约束时应注意，除not null用modify外，其他四种约束都用add constraint constraintName来定义。7. 删除约束alter table customer drop constraint constraintName;8. 删除主键约束时，需要使用cascade选项。如删除goods表中的goodsId字段时，应使用alter table goods drop primary key cascade;9. 约束的表级定义和列级定义(1). 表级定义表定义完后再定义约束的形式。create table emp(empno char(4),empName varchar2(12),deptno char(4), constraint pk_emp primary key (empno), constraint fk_dept foreign key(deptno) references dept(deptno);(2). 列级定义定义完一列后马上定义约束。create table emp(empno char(4) constraint pk_emp primary key, empName varchar2(12),deptno char(4), constraint pk_emp primary key (empno);Oracle索引1. 单列索引create index enameIndex on emp(ename);2. 复合索引create index name_job on emp(ename,job);3. 索引的分类按存储方式B*树(适用于重复性少的列)、反向索引、位图索引(重复值多的列)按索引列数单列索引、复合索引按列唯一性唯一索引、非唯一索引此外还有函数索引、全局索引、分区索引等。管理权限和角色1. 权限(前面也有讲过权限，可前后结合起来。)(1). 授权系统权限grant create session,create table to scott with admin option;(2). 授权角色权限grant select on emp to scott with grant option;grant update on emp(sal) to scott;注意:系统权限的传递用with admin option，而对象权限的传递用with grant option；系统权限的传递者的系统权限被revoke后，传递者传递后的系统权限依然可用，而对象权限的传递者对象权限被revoke后，传递者传递后的对象权限同时被revoke了。2. 角色常用预定义角色connect、resource、dba(不含启动和关闭数据库权限)创建角色(不验证:以后修改角色时不需要验证身份)create role roleName not identified;-创建不验证角色create role roleName identified by adminsa;-创建带验证的角色角色授权-例:有三个用户，现在要授权给这三个用户登录数据库和查、删、改scott.emp的权限。SQLcreate user user1 identified by adminsa;SQLcreate user user2 identified by adminsa;SQLcreate user user3 identified by adminsa;SQLgrant create session to roleName with admin option;SQLgrant select,update,delete on scott.emp to roleName;SQLgrant roleName to user1;SQLgrant roleName to user2;SQLgrant roleName to user3;注意:SQLdrop role roleName;后三个用户将不再具有该角色对应的权限。PL/SQL基础1. 创建存储过程(1). 简单的存储过程create or replace procedure scott_upd(uname varchar2,upass varchar2) isbeginupdate scott set passwd=upass where name=uname;end;(2). 块declare-声明变量 v_ename varchar2(5); v_sal number(7,2);begin-执行部分 select ename,sal into v_ename,v_sal from scott.emp where empno=&empno; dbms_output.put_line(The salary of |v_ename| is |v_sal|.);-例外处理 exception when no_data_found then dbms_output.put_line(Sorry,the number you typed can not be found!);end;2. 调用存储过程使用关键词call或exec;3. 函数-创建函数create function GetSal(inName varchar2)return number isyearSal number(7,2);begin-执行部分select (sal+nvl(comm,0)*12 into yearSal from emp where ename=inName;return yearSal;end;-sqlplusw中调用函数-定义接收函数返回值的变量var income number;-调用函数call GetSal(SCOTT) into:income;4. 包-创建包规范create package packOne isprocedure update_sal(name varchar2,newsal number);function GetSal(name varchar2) return number;end;-创建包体create package body packOne isprocedure update_sal(name varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name;end;function GetSal(name varchar2)return number isyearSal number(7,2);beginselect (sal+nvl(comm,0)*12 into yearSal from empwhere ename=name;return yearSal;end;end;5. PL/SQL中定义并使用常量和变量(1). 标量类型(scalar) 定义一个变长字符串v_ename varchar2(10); 定义一个小数 范围-9999.99-9999.99v_sal number(6,2); 定义一个小数并赋给初始值v_sal number(6,2):=5.4注:在PL/SQL中赋值号:= 定义一个日期类型变量v_hireDate date; 定义一个布尔变量 初始值为False且不能为空v_valid boolean not null default false; 输入员工编号，显示员工姓名、工资、个人所得税(税率为3%)。declarec_taxrate number(3,2):=0.03; -v_ename的类型和scott方案下的emp表中的ename字段的类型一致v_ename scott.emp.ename%type;v_sal number(5,2);v_taxsal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;v_taxsal:=v_sal*c_taxrate;dbms_output.put_line(姓名:|v_ename|,工资:|v_sal|,应缴税款:|v_taxsal);end;(2). 复合类型(composite) pl/sql记录 类似高级语言中的结构体declaretype emp_record_type is record(name scott.emp.ename%type,salary scott.emp.sal%type,jobname scott.emp.job%type)emp_record emp_record_type;beginselect ename,sal,job into emp_record from scott.emp where empno=&no;dbms_output.put_line(姓名:|emp_);end; pl/sql表 类似高级语言中的数组(下标可以为负数)declaretype emp_table_type is table of scott.emp.ename%typeindex by binary_integer;-下标为整数emp_table emp_table_type;beginselect ename into emp_table(0) from emp where empno=&no;dbms_out.put_line(姓名:|emp_table(0);end; 嵌套表 varray(动态数组)(3). 参照类型(reference) 游标变量(ref cursor)输入部门编号,显示所有该部门员工的姓名和工资declare-定义一个游标类型type emp_cursor_type is ref cursor;-定义一个游标变量emp_cursor emp_cursor_type;v_ename scott.emp.ename%type;v_sal scott.emp.sal%type;begin-执行-把emp_cursor和一个select结合open emp_cursor for select ename,sal from scott.emp where deptno=&no;-循环取出loopfetch emp_cursor into v_ename,v_sal;-判断emp_cursor是否为空exit when emp_cursor%notfound;dbms_output.p