Oracle学习笔记.doc

Oracle学习笔记一Oracle的卸载1）软件环境： 1、Windows xp+ORACLE 8.1.7 2、ORACLE安装路径为：C:ORACLE 2）实现方法： 1、 开始设置控制面板管理工具服务停止所有Oracle服务。 2、 开始程序Oracle-OraHome81Oracle Installation ProductsUniversal Installer。卸装所有Oracle产品，但Universal Installer本身不能被删除。3、 运行regedit，选择HKEY_LOCAL_MACHINESOFTWAREORACLE，按del键删除这个入口。 4、 运行regedit，选择HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices，滚动 这个列表，删除所有Oracle入口。 5、 运行refedit，选择HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication，滚动这个列表，删除所有Oracle入口。6、 我的电脑-属性-高级-环境变量。删除环境变量CLASSPATH和PATH中有关Oracle的设定。删除Oracle在该值中的内容。注意:path中记录着一堆操作系统的目录，在windows中各个目录之间使用分号（; ）隔开的，删除时注意。删除PATH环境变量中关于Oracle的值时，将该值全部拷贝到文本编辑器中，找到对应的Oracle的值，删除后，再拷贝修改的串，粘贴到PATH环境变量中，这样相对而言比较安全。7、 重新启动计算机，重起后才能完全删除Oracle所在目录。8、 从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标。9、 删除Program FilesOracle目录。10、删除与Oracle有关的文件，选择Oracle所在的缺省目录C:Oracle，删除这个入 口目录及所有子目录，并从Windows 2000目录（一般为C:WINNT）下删除以下文 件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。 11、WIN.INI文件中若有ORACLE的标记段，删除该段。12、如有必要，删除所有Oracle相关的ODBC的DSN。13、到事件查看器中，删除Oracle相关的日志。3）说明： 1、如果有个别DLL文件无法删除的情况，则不用理会，重新启动，开始新的安装， 2、安装时，选择一个新的目录，则，安装完毕并重新启动后，老的目录及文件就可以删除掉了。 3、oracle没有装好，建议重新装过，最好先删除以前装 的oracle数据库。在win里面删除oracle 有点复杂 ，如有安装客户端也应该在服务里有这样的服务存在OracleMTSRecoveryService。步骤如下：Win下完全删除Oracle 4、可以完整地删除Oracle products, Oracle services和注册表信息（当然是很少情况下才想到删除oracle，只有确定真的不用的时候才做）。如果需要请备份所有相关的文件。 二oracle启动方法 oracle自带的工具软件，主要用于执行spl语句,plspl块1、开始程序Oracle - OraDb10g_home1应用程序开发spl plus 2、开始运行sqlplusw3、开始运行sqlplus三oracle登录登录名：system 密码：lin四spl*plus常用命令连接命令（1）connect 说明：conn 用户名/密码网络服务名 as sysdba/as sysoper,当用特权用户身份连接时，必须带上as sysdba或是as sysoper。 案例：conn system/lin; 切换登录用户名（2）disconnect 说明：该命令用于断开与当前数据库的连接。（3）password 说明：该命令用于修改用户的密码，如果想要修改其他用户的密码，需要用sys/system登陆。（4）show user 说明：显示当前用户名。（5）exit 说明：该命令用于断开与数据库的连接，同时退出sql*plus。文件操作命令（1）start和 说明：运行sql脚本 案例：sql d:a.sql; 或者 sqlstart d:a.sql;（2）edit 说明：编辑指定的sql脚本 案例：sqledit d:a.sql;（3）spool 说明：该命令可以将sql*plus屏幕上的内容输入到指定的文件中去。 案例：sqlspool d:b.sql 在d盘新建一个文件b.sql sqlselect * from emp; sqlspool off;交互式命令（1）& 说明：可以替代变量，而该变量在执行时，需要用户输入。 sqlselect * from emp where job=&job;显示和设置环境变量概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以 去修改glogin.sql脚本。（1）linesine 说明：设置显示行的宽度，默认是80个字符。 案例：sqlshow linesize; Sqlset linesize 90;（2）pagesize 说明：设置每页显示的行数目，默认是14行。 案例：sqlshow pagesize; Sqlset pagesize 5;五Oracle用户的管理 创建用户概述：在Oracle中要创建一个新的用户使用create user语句，一般是具有dba（数据库 管理员）的权限才能使用。（1） create user用户名identified by密码 说明：创建新用户。 案例：sqlconn system/lin; sqlcreate user xiaoming identified by m123;修改用户密码概述：如果给其他用户修改密码，需要具有dba的权限，或是拥有alter user的系统权限， 如system用户。 案例：sqlconn system/lin; sqlpassword xiaoming; 或 sqlalter user 用户名 identified by 新密码 删除用户概述：一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有 drop user的权限，如system用户。 案例：drop user 用户名cascade 注意：在删除用户时，如果用户已经创建了表，那么在删除时需加参数cascade。综合案例概述：创建的新用户没有任何权限，甚至无法登录数据库，这时需要其他用户（如sys、system) 为其指定相应的权限。赋予权限命令grant，收回权限命令revoke。（1）grant权限to用户名 案例：sqlconn system/lin; sqlgrant connect to xiaoming;（2）revoke权限to用户名 案例：sqlconn system/lin; sqlrevoke connect from xiaoming;权限（1）系统权限：connect（登录）、dba（所有）、resource（建表）（2）对象权限：select（查询）、insert、update（修改）、delete、all等。概述：用户xiaoming查询system的emp表案例：sqlconn system/lin; sqlgrant select on emp to xiaoming;概述：用户xiaoming修改system的emp表案例：sqlconn system/lin; sqlgrant update on emp to xiaoming;概述：用户xiaoming对system的emp表所有权限案例：sqlconn system/lin; sqlgrant all on emp to xiaoming;概述：用户xiaoming查询scott的emp表，同时可以把这个权限赋予其他用户，如果是 对象权限，就在后面加上with grant option；如果是系统权限，就在后面加上with admin option。案例：sqlconn scott/lin; sqlgrant select on emp to xiaoming with grant option; sqlconn system/lin; sqlcreate user xiaohong identified by m123;（新建用户xiaohong） sqlgrant connect to xiaohong; （赋予xiaohong登录权限） sqlconn xiaoming/m123; sqlgrant select on scott.emp to xiaohong;使用profile管理用户口令概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称 为default的profile。当建立用户没有指定的profile选项，那么oracle就会将default 分配给用户。（1） 账户锁定 概述：指定用户登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天）， 一般用dba的身份去执行该命令。 案例：指定xiaoming最多只能尝试登录3次，锁定时间为2天。 sqlconn system/lin; sqlcreate profile myprofile limit failed_login_attempts 3 password_lock_tome 2; sqlalter user xiaoming profile myprofile;（2） 账户解锁 案例：给xiaoming解锁 sqlconn system/lin; sqlalter user xiaoming account unlock;（3） 终止口令 概述：为了让用户定期修改密码可以使用终止口令的指令来完成，同时这个命令也 需要dba身份来操作。 案例：指定xiaoming每隔10天修改登录密码，宽限期为2天。 sqlconn system/lin; sqlcreate profile myprofile limit password_life_time 10 password_grace_tome 2; sqlalter user xiaoming profile myprofile;（4） 历史口令 概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用历史口令。 这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码 时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就会提示用户 重新输入密码。 案例：1）建立profile sqlconn system/lin; sqlcreate profile password_history limit password_life_time 10 password_grace_tome 2 password_reuse_time 10; 其中password_reuse_time 指定口令可重用时间即10天后就可以重用。 2）分配给某个用户 sqlalter user xiaoming profile myprofile;（5） 删除profile 概述：当不需要某个profile文件时，可以删除该文件。 案例：sqlconn system/lin; sqldrop profile password_historycascade;六Oracle表的管理表名和列的命令规则1） 必须以字母开头2） 长度不能超过30字符3） 不能使用oracle的保留字4） 只能使用如下字符AZ、az、09、$、#等 支持的数据类型（1）字符型 1）char 定长、最大2000字符。 2）varchar2（20） 变长、最大4000字符 3）clob（character large objiect） 字符型大、对象最大4G（2） 数字型 1）number 范围：-10的38次方10的38次方，可以表示整数，也可以表示小数。 2）number（5,2） 范围：-999.99999.99，表示一个小数有5位有效数，2位小数。 3）number（5） 范围：-9999999999，表示一个五位整数。（3） 日期类型 1）date 包含年月日和时分秒 2）timestamp 这是oracle9i对date数据类型的扩展（4） 图片类型 1）blob 二进制数据，可以存放图片/声音4G创建表（1）建表 - 学生表 案例：sqlcreate table student ( -表名 xh number(4)， -学号 xm varchar2(20)， -姓名 sex char (2)， -性别 birthday date， -出生日期 sal number（7,2） -奖学金 ); （2） 建表 - 班级表 案例：aqlcreate table class ( classID number(2)， cname varchar2（40） );修改表（1） 添加一个字段 sqlalter table student add (classID number(2);（2） 修改字段的长度 sqlalter table student modify (xm varchar2(30);（3） 修改字段的类型和名字（不能有数据） sqlalter table student modify (xm char(30); 类型 sqlalter table student rename column 字段1 to 字段2; 名字（4）删除一个字段 sqlalter table student drop column sal;（5）修改表的名字 sqlrename student to stu;（6）删除表 sqldrop table student; （7） 查询表 sqldesc student; 或 sqlselect * from student; 添加数据概述：oracle中默认的日期格式为DD-MON-YYYY,其中DD表示日（天）；MON表示 月份；YYYY表示年份。如21-1月-2011表示2011年1月21号。（1） 所有字段都插入 sqlinsert into student values(1,小明,男,11-12月-1997,2345.6,12); sqlselect * from student;（2） 修改默认的日期格式 sqlalter session set nls_date_format = YYYY-MM-DD; sqlinsert into student values(1,小明,男,1997-12-11,2345.6,12);（3） 插入部分字段 sqlinsert into student(xh,xm,sex) values(1,小明,男);（4） 插入空值 sqlinsert into student(xh,xm,sex,birthday) values(1,小明,男，null); sqlselect * from student where birthday is null; 查询空值 sqlselect * from student where birthday is not null; 查询非空值（5） 修改一个字段 sqlupdate student set sex=女 where xh=1; 案例：将所有男性的奖学金减半 sqlupdate student set sal=sal/2 where sex=男;（6） 修改多个字段 sqlupdate student set sex=男,birthday=1980-04-01where xh=1;（7） 修改含有null值的数据删除数据（1） 删除所有记录，表的结构还在，可写日志，可恢复；速度慢。 sqldelete from student; 案例：删除student表中的所有记录，再找回。 sqlsavepoint student; 建立一个保护点 sqldelete from student; 删除记录 sqlrollback to student; 回滚到保护点（2） 删除表的结构和数据。 sqldrop from student;（3）删除一条记录 sqldelete from student where xh=1;（4） 删除表中所有的记录，表的结构还在，不可写日志，无法找回删除的记录，速度快。 sqltruncate from student;7 Oracle表基本查询 简单的查询语句emp表中各个字段的含义：empno(员工编号)、ename(员工姓名)、job(工作岗位)、 mgr(上级编号)、hiredate(入职日期)、sal(员工工资)、comm(奖金)、depno(员工部门)（1） 查询表的结构 sqldesc dept;（2） 查询所有列 sqlselect * from dept;（3） 查询指定列 sqlselect ename , sal , job ,deptno from emp;（4） 取消重复行 sqlselect distinct deptno , job from emp;（5）案例：查询SMITH的薪水、工作及所在部门？ sqlselect deptno , job , sal from emp where ename=SMITH; 注意：sql语句中不分大小写，而查询人的姓名需分大小写。（6） 算数表达式 案例：查询每个员工的年工资，有奖金的员工加上奖金。 sqlselect sal*12+nvl(comm,0)*12 年工资,ename , comm from emp; （7） 使用列的别名 sqlselect ename 姓名 ,sal*12 as 年工资 from emp; 将ename改为姓名，sal*12 改为年工资。（8） 如何处理null值 使用nvl函数来处理，参照（6）。（9） 如何连接字符串（|） sqlselect ename | is a | job from emp;（10） 使用where子句 案例：1.查询工资高于3000的员工情况？ sqlselect ename,sal from emp where sal3000; 2.查询1982.1.1后入职的员工情况？ sqlselect ename,hiredate from emp where hiredate1-1月-1982; 3.查询工资在2000到2500之间的员工情况？ sqlselect ename,sal from emp where sal=2000 and salselect ename,sal from emp where ename like S%; 2.查询第二个字符为大写O的所有员工姓名和工资？ sqlselect ename,sal from emp where ename like _O%;（12） 在where条件中使用in 案例：查询empno为7499,345,800.的员工？ sqlselect * from emp where empno in(7499,345,800);（13） 使用is null操作符 案例：查询没有上级的员工？ sqlselect * from emp where mgr is null;（14） 使用逻辑操作符号 案例：查询工资高于500或是岗位为MANAGER的员工，同时还要满足他们的姓名 首写字母为大写的J？ sqlselect * from emp where (sal500 or job=MANAGER) and ename like J%;（15） 使用order by 子句 案例：1.按照工资从低到高的顺序显示员工的信息？ sqlselect * from emp order by sal (asc); 默认从低到高的顺序 sqlselect * from emp order by sal desc; 从高到低的顺序 2.按照员工部门升序而员工的工资降序排列员工的信息？ sqlselect * from emp order by deptno , sal desc;（16） 使用列的别名排序 案例：按照员工的年薪排序？ sqlselect ename , (sal+nvl(comm,0)*12 as年薪 from emp order by 年薪 asc;复杂的查询语句概述：在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，所以这 就要求我们学习较为复杂的select语句。（1） 数据分组 max，min，avg，sum，count 案例:1.查询所有员工中最高的工资和最低的工资？ sqlselect max(sal),min(sal) from emp; * 2.查询所有员工中最高的工资及是谁？ sqlselect ename,sal from emp where sal=（select max(sal) from emp); 3.查询所有员工的平均工资和工资的总和？ sqlselect avg(sal),sum(sal) from emp; 4.查询总共有多少员工？ sqlselect count(sal) from emp; 5.查询工资最高员工的名字及工作岗位？ sqlselect job,sal from emp where sal=(select max(sal) from emp); 6.查询工资高于平均工资的员工信息？ sqlselect * from emp where sal(select avg(sal) from emp);（2） 使用group by和having子句 概述：group by用于对查询的结果分组统计，having用于限制分组显示的结果。 案例：1.查询每个部门的平均工资和最高工资？ sqlselect avg(sal),max(sal),deptno from emp group by deptno; 2.查询每个部门的每种岗位的平均工资和最低工资？ sqlselect avg(sal),min(sal),deptno,job from emp group by deptno,job; 3.查询平均工资高于2000的员工部门和它的平均工资、最高工资？ sqlselect avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000;（3） 数据分组的总结 1.分组函数只能出现在选择列表、having、order by子句中。 2.如果在select语句中同时包含有group by、having、order by，那么他们的 顺序是group by，having，order by。 如：sqlselect avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg(sal); 3.在选择列种时，如果有列、表达式和分组函数，那么这些列和表达式必须有一个 出现在group by子句中。 如：sqlselect avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000; 这里deptno一定要出现在group by中。 多表的查询概述：多表的查询时指基于两个或是两个以上的表或是视图的查询。在实际应用中，查询 单个表的可能无法满足需求（如：查询sales部门位置和其员工的姓名），在这种 情况下需要使用到dept表和emp表。案例：1.查询员工姓名、员工工资及其所在的部门名字？ 笛卡尔集规定：多表查询的条件个数至少不能少于表的个数-1。 salselect a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno; 2.查询部门号为10的部门名、员工姓名和工资？ salselect a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10; 3.查询各个员工的姓名、工资及其工资的级别？ salselect a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal; 4.查询员工姓名、员工工资及其所在的部门名字，并按部门排序？ salselect a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;（1）自连接 概述：自连接是指在同一张表的连接查询。 案例：查询某个员工的上级领导的姓名？比如查询FORD的上级？ salselect worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=FORD;子查询概述：子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。（1） 单行子查询 概述：单行子查询指只返回一行数据的子查询语句。 案例：查询与SMITH同一部门的所有员工？ salselect * from emp where deptno=(select deptno from emp where ename=SMITH);（2） 多行子查询 概述：多行子查询指返回多行数据的子查询语句。 案例：查询和部门10的工作相同的员工姓名、岗位、工资和部门号？ salselect * from emp where job in (select distinct job from emp where deptno=10);（3） 在多行子查询中使用all操作符 案例：查询工资比部门30所有员工的工资高的员工姓名、工资和部门号？ salselect ename,sal,deptno from emp where salall (select sal from emp where deptno=30); 或者salselect * from emp where sal(select max(sal) from emp where deptno=30);(4) 在多行子查询中使用any操作符 案例：查询工资比部门30任意一个员工工资高的员工姓名、工资、部门号？ salselect ename,sal,deptno from emp where salany (select sal from emp where deptno=30); 或者salselect * from emp where sal(select min(sal) from emp where deptno=30);（5）多列子查询 概述：多列子查询指查询返回多个列数据的子查询语句。 案例：查询与SMITH的部门和岗位相同的所有员工？ salselect * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH); （6） 在from子句中使用子查询 案例：查询高于自己部门平均工资的员工？ salselect 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.sala1.mysal; 说明：当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此 叫做内嵌视图；当在from子句中使用子查询时，必须给子查询指定别名。（7） 分页查询 1.rownum分页 (select * from emp) 2.显示rownum （oracle分配的），表中多出一列rn。 select a1.*,rownum rn from (select * from emp) a1; 3.查询rnselect a1.*,rownum rn from (select * from emp) a1 where rownum=10; 查询6=rnselect * from (select a1.*,rownum rn from (select * from emp) a1 where rownum=6; 4.查询rn610的员工姓名和工资？ salselect * from (select a1.*,rownum rn from (select ename,sal from emp) a1 where rownum=6; 5.查询rn610的员工姓名和工资、工资要按升序排列？ salselect * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum=6;（8） 用查询结果创建新表 532205244185 概述：在创建新表的同时数据也将带入新表。 案例：用emp表中员工编号、姓名和工资创建一张新表emp2？ salcreate table emp2 (a,b,c) as select empno,ename,sal from emp;合并查询概述：有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union、union all、intersect、minus。（1） Union 概述：该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果 集中重复行。 案例：salselect ename,sal,job from emp where sal2500 union select ename, sal,job from emp where job=MANAGER;（2） union all 概述：该操作与union相似，但是它不会取消重复行，而且不会排序。该操作符 用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重 复行。 案例：salselect ename,sal,job from emp where sal2500 union all select ename,sal,job from emp where job=MANAGER;（3） intersect 概述：该操作符用于取得两个结果集的交集。 案例：salselect ename,sal,job from emp where sal2500 intersect select ename,sal,job from emp where job=MANAGER;（4） minus 概述：该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不 存在第二个集合中的数据。 案例：salselect ename,sal,job from emp where sal2500 minus select ename,sal,job from emp where job=MANAGER;8 在Oracle中操作数据使用特定的格式插入日期值（1） 使用to_date函数 案例：如何插入列带有日期的表，并按照年-月-日的格式？ sqlinsert into student values(1,小明,男, to_date(1997-12-11,yyyy-mm-dd),2345.6,12);使用子查询插入数据概述：当使用values子句时，一次只能插入一行数据，当使用子查询插入数据时，一条 Insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库 时，可以使用子查询来插入数据。案例：新建一张表，把emp表中的部分数据导入新表中？ 1.建表kkk sqlcreate table kkk (myID number(4),myname varchar2(50),myDept number(5); 2.导入数据 sqlinsert into kkk (myID,myname,myDept) select empno,ename,deptno from emp where deptno=10;使用子查询更新数据概述：使用update语句更新数据时，即可以使用表达式或者数据直接修改数据，也可以 使用子查询修改数据。案例：把员工scott的岗位、工资、补助改为与员工smith一样？ sqlupdate emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=SCOTT;9 Oracle中事务处理（1） 事务 概述：事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml 语句要么全部成功，要么全部失败。 例如：网上转账就是典型的要用事务来处理，用以保证数据的一致性。 （2） 事务和锁 概述：当执行事务操