韩顺平oracle笔记个人总结版.docx

1、韩顺平oracle笔记个人总结版一、oracle 常用sql plus 命令(1) connect 用户名/密码网络as sysdba /sysoper (2) 一般情况下使用普通用户登录，除非需要更高权限时，在切换高级用户；(3) disconnect 断开连接，又不退出当前窗口；(4) quit/exit 完全退出；(5) show user 显示当前用户；(6) 管理员修改用户密码：alter user xxxx identified by yyyy;(7) Passwordpass 用户名；给自己修改密码，不需要带用户名;给别的用户修改，需要带用户名；创建临时表空间create tem

2、porary tablespaceuser_temptempfile d:user_temp.dbfsize 50mautoextend onnext 50m maxsize 1024mextent management local;创建表空间createtablespaceuser_tsdatafile d:user_ts.dbfsize 50m autoextend onnext 50m maxsize 1024mextent management local;创建用户create user test38 identified by test38default tablespaceuser

3、_tstemporary tablespaceuser_tempquota 5m on user_ts;给用户赋予权力grantdba to test38;给scott用户解锁alter user scott account unlock;用system 用户给scott修改密码Alter user scott identified by tiger;不常用的命令：linesize一行能显示多少个字符就换行了默认就只有80字符，所以会出现一个记录一行没有显示完全就换行了。Set linesize 120pagesize显示多少个记录就从新开始如图：前后差别Set pagesize 100二、o

4、racle 用户管理1. 创建用户（必须是具有DBA权限的才行）create user 名字 identified by 密码（密码不能以数字开始）defaulttablespacexxxx;temporary tablespacexxx2;quotaxmon xxxx;如果出现这样的情况，直接XX或者google一下。2.给用户分配权限grant create session to 用户名；3.管理的用户的机制4.综合案例方案（schema）（当创建一个用户的时候，只要这个用户创建了任何的数据对象（如表，索引，触发器等），那么DBMS就会创建一个与该用户名一样的一个方案。）赋权grant s

5、elect/update/insert/all on emp to 用户名；ps:如果想看一个用户有哪些数据对象，可以通过pl/sql development 查看。例子：小红查询scott的emp表select * from scott.emp;表空间参考：Oracle表空间之基本概念ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SY

6、STEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系个人阶段总结数据库实例、表空间（逻辑结构）、用户、方案、数据对象（表，过程，包等）的关系一个实例下可以有多个表空间。默

7、认是有一个system表空间。一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系。数据库建立一个用户，就建立了有且只有一个与该用户名字一样的方案来管理该用户建立的各种数据对象。如有两个表空间，一个是默认的system表空间，一个是新建的user_tasp表空间。 用户（wang）在system建立一个表t1，在user_tasp建立一个表t2，但是只有一个方案(wang)来管理这两个表，虽然表分布在不同的表空间上。也说明方案和表空间没有隶属关系，就像用户和表空间没有隶属关系一样。数据对象的操作一般是通过方案来实现操作的。如：Select * from s

8、cott.emp 这是完整的写法。scott是方案名-个人实验-建立临时表空间create temporary tablespace user_temptempfile f:datauser_temp.dbfsize 50mautoextend onnext 10m maxsize 100mextent management local-建立表空间create tablespace user_taspdatafile f:datauser_tasp.dbfsize 50mautoextend onnext 10m maxsize 100mextent management local-创建用户

9、create user wang identified by wang default tablespace system;grant connect,resource to wang;alter user wang quota 10m on user_tasp; /在表空间上给用户分配空间就能让用户在该表空间建立数据对象。注意不要指定分配空间在临时表空间上，不然会报错。-在默认表空间上建表create table t_test1(id number,name varchar2(20) 【tablespace system】;-在user_tasp 建表create table t_test2

10、(id number,name varchar2(20) tablespace user_tasp;Schema(方案)当一个用户被创建之后，只要它创建任何一个数据对象，那DBMS就会创建一个和它名字一样的方案与该用户对应。Ps:如果想看某个用户有什么数据对象，用pl/sql development 软件。Schema 的实际应用：xioanghong访问scott的emp表1. 连接scottconn soctt/tiger2. 给xiaohong赋权限grant select/update/delete/all on emp to xiaohong3.select * from scott

11、.emp;参考：1、with admin option 用于系统权限授权，with grant option 用于对象授权。2、给一个用户授予系统权限带上with admin option 时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create sessio

12、n的权限，即直接revoke create session from B.而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如：grant select on表名to A with grant option;，A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。相同点：- 两个都可以既可以赋予user 权限时使用，也可以在赋予role 时用不同点：- with admin

13、option 只能在赋予 system privilege 的时使用- with grant option 只能在赋予 object privilege 的时使用- 撤消带有with admin option 的system privileges 时，连带的权限将保留撤消带有with grant option 的object privileges 时，连带的权限将不被保留用户口令管理使用profile进行用户口令管理一个用户连续3次密码输入错误，那该账户锁定1.创建profile文件create profile文件名limitfailed_login_attempts次数password_lo

14、ck_time天数;2.把该文件（规则）分配给某个用户alter user用户名 profileprofile文件名;3.终止口令一个账号的密码最多能用几天的问题。Pl: 一个密码的使用时间为10天，宽限时间是2天create profile文件名limit password_life_time 10 password_grace_time 2;4.删除profiledrop profile文件名；windows下lsnrctl start oradimstartupsidorcl;unix/linux环境下（略）Oracle 的登陆认证Compmgmt.msc 进入计算机管理找回管理员的密码

15、1. 搜索PWD数据库实例名.ora2. 删除该文件，最好备份一份。3. 生成新的密码文件，在dos下输入命令。orapwd file=PWD数据库实例名.ora的具体路径 password=新密码 entries = 允许多少个人进入。Ps: orapwd file =D:oracleproduct10.2.0db_1databasePWDorcl.ora password = welcome entries10file 文件路径 password 新密码 entries 登陆sys最多的用户。4. 重新启动数据库实例。Oracle 数据类型Char 存放字符串，最大2000字符，定长Pl:

16、 name char(32), 只能存放32字符，多了，报错；少了，空格填满。ps: dump() 显示一个字段具体的情况varchar2变长字符，最大可存放4000字符。name varchar(16) name abc实际上只占3字符，其余的13个就回收第九课nchar定长，编码方式unicode，最大2000字符 Ps: char类型的，一种中文字符站两个字符，而nchar类型，一个中文字符只占一个字符number1. 可以存放小数，也可以是小数。2. number(p,s)p为有效位，s为小数位。范围：P1,38,s-84,127。 整数部分的个数为：p-sPS：如果单单就是numbe

17、r，后面没有任何数字，该数是多少就是多少。Pl: 123.456 number 结果就是：123.456原则：实际开发过程，如果number指定了小数位，那明确指定，如果没有指定，就直接用number。date 日期类型默认的是dd-mm-yyyy第十课Ps: 数据类型是number 插入的是字符串型的12345，oracle 会自动把这样的字符类型转化为数字类型。但是如果是asd123就不行了。2.插入空置的情况：插入null和情况是一样的。（中间有一个空格）并不是空。即=null3.字符和日期类型应该用包含起来。第十一课Ps:oracle 的sql不区分大小写，但是内容区分大小写；Ps:给

18、字段改名可以用as ,也可以不用；nvl() 处理null的字段。select ename,sal*12+nvl(comm,0) as all_sal from emp;oracle 中的字符连接符号： |设置保存点并回滚savepoint aa;rollback to aa;第十二课where 语句的用法like 操作符% 任意零个或者多个字符；_ 任意一个字符；条件为空与否的使用 is null 或者 is not null 处理；Order by 排序 默认是asc , desc 是降序。第十三课组函数max(),min(),sum(),count(),avg();ps:括号里面可以是字

19、段也可以是表达式avg(sal) 不会吧sal为空的记录统计进来。它只计算有值记录的平均值。求所有人的平均值，可以这样：select sum(sal)/count(*) from emp;group by查询的结果进行分组；having 限制分组显示结果。 第十四课多表查询1. emp ,salgrade 两张表的连接。select ename,sal,grade from emp join salgrade on sal between losal and hisal;自连接2. 显示FORD的上级select ename ,sal from emp where empno =(select

20、 mgr from emp where ename =FORD);3. 显示所有员工的名字，及其上级的名字；select t1.ename as worker,t2.ename as boss from emp t1 left join emp t2 on t1.mgr = t2.empno;第十五课子查询 显示与SMITH同一个部分的员工select ename from emp where deptno = ( select deptno from emp where ename = SMITH);（结果包含了SMITH,如果不想包含SMITH ,在加上and enameSMITH）;子查

21、询中使用allPl：显示工资比部分30的所有员工的工资还要高的员工姓名，工资，部门号select ename,sal,deptno from emp where sal all(select sal from emp where deptno =30);同样的写法：select ename,sal,deptno from emp where sal ( select max(sal) from emp where deptno =30);子查询中使用anyPl:第十六课在from中使用子查询显示各部分中高于本部分平均工资的员工信息。select emp.ename,sal,t.myavg,t.

22、deptno from emp join (select avg(sal) myavg,deptno from emp group by deptno) ton emp.deptno = t.deptno where sal t.myavg;各个部门工资最高人的详细信息。select emp.*,t.maxsal from emp join (select max(sal) maxsal,deptno from emp group by deptno) t on emp.deptno = t.deptno where emp.sal = t.maxsal;显示每个部门的信息和人员数1.显示各个

23、部分的人数select count(*) pernum, deptno from emp group by deptno；2.与表dept进行连接select dept.*,t.pernum from dept left join (select count(*) pernum, deptno from emp group by deptno) t on dept.deptno = t.deptno ;1.表本身的复制create table mytest as select * from emp;ps:把emp 表的结构和记录都复制到这个mytest表中2.自我复制进行插入操作：insert

24、 into mytest select * from mytest;Oracle 分页select ename, sal from (select ename, sal, rownum r from (select * from emp order by sal desc) where r 5 and r =sysdate;最经三个月入职的员工。同样可以应用bbs最近n个月的帖子。系统函数第二十三、二十四课事务Jdbc中设置oracle隔离机制conn.setTranscationIsolation(Connect.SERIALIZIABLE)方法第二十五、二十六课数据完整性1. 约束：not

25、 null，uinque，primary key，froeign key，check,defaultPl:create table worker(id number primary key,name varchar2(20) not null,salnumber check(sal3000 and sal8000),phonenum varchar(20) uique,address varchar2(50) references address(id),/address 另一张表sex char(2) check(sex in (男,女)default 男)constraintpl: alter table worker add constraint pk_address check(address in(北京)；添加约束：alter table 表明 add constraint 约束名约束种类（字段）；constraint fk_st