Oracle11g教案.doc

《Oracle11g教案.doc》由会员分享，可在线阅读，更多相关《Oracle11g教案.doc（37页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流Oracle11g教案【精品文档】第 37 页Oracle11g教案一. Oracle入门1. 安装Oracle11g2. 验证Oracle11g是否安装成功在控制面板中查看2个服务是否已启动3. 登录SQL*PLUSSQL*PLUS是最常用的用于连接和使用Oracle数据库的实用程序。SQL*PLUS包括在Oracle Database11g服务器软件和客户机软件中。在服务器或客户机上安装了SQL*PLUS软件之后，登录服务器或客户机并启动一个SQL*PLUS会话是一个非常的过程。由于每个SQL*PLUS会话都涉及与数据库的连接，只要提供一个合法的用户

2、名/密码组合就可以启动SQL*PLUS会话并连接数据库。sqlplus 账号/密码例：sqlplus system/密码(此种方式会泄密)连接指定数据库例：sqlplus usernameconnect_identifier使用具有sysdba或sysoper权限的身份登录例：sqlplus username/password as sysdba注意：as子句允许有sysdba或sysoper系统权限的用户连接SQL*PLUS 4. 用connect命令进行连接 登录到SQL*PLUS后，SQL*PLUS的connect命令将帮助你以一个不同的用户身份进行连接。在使用connect命令连接一个

3、数据库后，还可以登录到另一个不同的数据库。 connect newuser/newpasswdorcl1 5.用/nolog的无连接SQL*PLUS会话 在启动一个新SQL*PLUS会话时，使用sqlplus命令以及/nolog选项，可以不连接数据库而只启动sql*plus会话。 Sqlplus /nolog6. 在SQL*PLUS中进行操作 在登录到SQL*PLUS界面后，可以输入任何SQL*PLUS、SQL或PL/SQL命令。 一条SQL语句由一个;或一个/结束，一个PL/SQL块由一个/结束，SQL*PLUS命令以一个换行符结束。如果输入的是一个SQL*PLUS命令，SQL*PLUS客户

4、机程序将处理它，如果是一条SQL或PL/SQL语句，则数据库服务器会进行处理。7. 用于查看当前登录用户show user;8. 清屏clear screen;9. 退出SQL*PLUS 输入exit或quit可以退出SQL*PLUS。 注意：如果在SQL*PLUS中输入exit(quit)命令正常地退出会话，则事务将立即被提交。否则不提交事务。 10. 修改SQL提示符 set sqlprompt _user_content_identifier;11. DESCRIBE命令describe命令描述或列出表的列和列的说明。12. 将SQL缓冲区内容保存到文件中save c:abc.sql a

5、ppendreplace13. 在SQL*PLUS中执行SQL脚本run c:abc.sql/ c:abc.sql c:abc.sql14. 在SQL*PLUS中进行编辑当SQL语句出现错误时，通过edit指令调用编辑器修改语句，然后在SQLPLUS中用“/”执行刚刚修改过的SQL语句。二. 创建Oracle数据库 1. 通过Database Configuration Assistant创建数据库 2. 查看当前连接的Oracle实例名 select instance_name from v_$instance;3. 修改账号密码alter user system identified by

6、 123456;4. 解锁scott用户alter user scott account unlock;5. 切换登录用户conn scott/tigger;6. 创建用户create user abc identified by 123;7. 授予用户权限新创建的用户还不能登录到Oracle系统，必须对其授予权限后才能登录。一般给用户授予2个系统内置的角色权限，分别是connect和resource。grant connect,resource to ;8. 撤销用户权限revoke resource from ;注意：当开发完毕不需要再建表时，可以将resource角色撤销。二. 创建Or

7、acle表1. 建表时字段常用的数据类型数据类型英文描述类型名称说明字符char定长表示固定长度的字符串，最大长度为2000个字符，1个汉字按2个字符处理，如char（10）中存放Hello，这个字符串是5个字符，存放到char(10)的字段后，会后补5个空格使得字符的长度为10。varchar2变长存放长度不确定的字符串，最大长度为4000字符，如varchar2(10)中存放Hello，放入字段后实际长度就是5个字符。Long超长字符串最大长度2G数字integer整形小的整数number任意精度数字number可以表示任意精度的整数和小数。number(n)n位长度的整数只能存放最大长度

8、为n的整数，例如number(5)存放的最大整数是99999。number(n,m)有效数字位数为n,小数位为m存放n位有效数字和m位小数的小数类型，例如number(5,2)存放的最大小数位999.99。日期date含有年月日时分秒的完整日期系统日期可以用函数sysdate来获得。大对象clob文本大对象存放的字符串的最大长度为4G。blob二进制大对象存放的二进制数据的最大容量为4G。示例表结构EMP（雇员表）字段编号字段名称字段描述字段类型1EMPNO雇员编号NUMBER(4)2ENAME雇员姓名VARCHAR2(10)3JOB工作职位VARCHAR2(9)4MGR雇员的领导编号NUMB

9、ER(4)5HIREDATE雇佣日期DATE6SAL工资NUMBER(7,2)7COMM奖金或佣金NUMBER(7,2)8DEPTNO部门编号NUMBER(2)DEPT（部门表）字段编号字段名称字段描述字段类型1DEPTNO部门编号NUMBER(2)2DENAME部门姓名VARCHAR2(14)3LOC部门位置VARCHAR2(13)2. 语句创建数据库表create table dept( -创建部门表deptno number(2) primary key,dname varchar2(14) not null,loc varchar2(13) not null)tablespace ;c

10、reate table emp(empno number(4) primary key,ename varchar2(10) not null,job varchar2(9),mgr number(4), hiredate date,sal number(7,2),comm number(7,2),deptno number(2) constraint dept_fk references dept(deptno) tablespace ;在指定表空间建表 3. 添加表列 alter table emp add sal number(7,2); 4. 删除表列 alter table emp

11、drop column sal;5. 重命名表列 alter table emp rename column comm to banus; 6. 修改字段类型alter table emp modify 7. 重命名表alter table emp rename to emmp;8. 清空表中所有数据truncate table emp;9. 删除表通过使用drop table table_name命令可以删除一个表。但是在使用此命令时，表不会立即被删除，Oracle只是简单地重命名此表并将其存储在回收站中(回收站实际上是一个简单的数据字典表)。这样，还可以恢复被意外删除的表。恢复被删除的表f

12、lashback table tb_name to before drop;恢复被删除的表的能力称为闪回删除特性。特别注意：在默认的表空间创建的表将无法被闪回。彻底删除数据表drop table tb_name purge;删除具有关联性的表在删除一个表时，该表上的所有索引也将被删除。如果要删除的表中包含主键或由其他表的外键引用的唯一键，则必须在drop table命令中包括cascade子句，以便将约束一并删除。drop table emp cascade constraints;10. 创建表空间(tablespace)create tablespace ora2 datafile d:o

13、raoracle2.orasize 100m;在创建表空间时，相对应的目录必须存在。11. 将数据导出(1) 导出单表expsystem/managermyoraclefile=d:daochu.dmp tables=(table1); system是用户名，manager 是密码，myoracle 是数据库名。(2) 导出整个数据库exp system/managermyoracle file=d:daochu.dmp full=y;12. 利用脚本文件向表中插入数据脚本文件（create.sql）create sequence seq_banji start with 10 increme

14、nt by 10;-创建序列insert into banji values(seq_banji.nextval,A01);-粗体字代表获取序列内容insert into banji values(seq_banji.nextval,A02);执行脚本文件SQL c:create.sql13. 导入单表数据imp 用户名/密码 tables= ignore=y file=文件名14. 导入整个数据库imp 用户名/密码 tables= ignore=y file=文件名注意：数据备份时，要脱离当前Oracle的环境，退到DOS文件系统内。15. 相关语句(1) 查询表结构desc banji;

15、(2) 查询已存在的序列select * from user_sequences;(3) 删除序列drop sequence seq_banji;(4) 查询当前用户下的表select * from tab;16. 练习按照以下表结构建表部门表（dept）deptnodnameloc10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON员工表（emp）empnoenamejobmgrhiredateSalcommdeptno7369SMITHCLERK79021980-12-171000207499ALLENSA

16、LESMAN76981981-02-201800300307521WARDSALESMAN76981981-02-221450500307566JONESMANAGER78391981-04-023175207654MARTINSALESMAN76981981-09-2813501400307698BLAKEMANAGER78391981-05-013050307782CLARKMANAGER78391981-06-092650107788SCOTTANASTLY75661981-12-093200207839KINGPRESIDENT1981-11-175200107844TURNERSAL

17、ESMAN76981981-09-0817000307876ADAMSCLERK77881983-01-121300207900JAMESCLERK76981981-12-031150307902FORDANASTLY75661981-12-033200207934MILLERCLERK77821982-01-23150010三. 数据库完整性约束关系数据库中的完整性约束可以容易地且自动地在数据库表中实施重要的业务规则。1. 主键约束主键是表的非常重要的一类约束。如果一个列值要被唯一标识，可以为该列值创建主键。定义为主键的列必须是唯一且非空的。一个表只有一个主键，可以在创建表时创建主键。cre

18、ate table dept(deptno number primary key);alter table dept add primary key(deptno);alter table dept drop primary key;alter table dept drop constraint pk_a由于上例中的约束没有分配名字，因此Oracle将分配一个系统生成的约束名。如果想要自定义一个约束名，可以使用以下方式：alter table dept add constraint dept_pk primary key(deptno);注意：如果主键中有多个列(即是一个组合键)，则不能在表

19、创建中对列名指定主键名称。必须在create table命令的结尾并且在列出所有列之后，作为一个单独项指定主键列。2. 非空约束一个表通常有一个或多个列不允许为空，即没有值。可以在表的创建阶段使用not null选项，强制用户必须为此列输入值。create table dept(dname varchar2(30) not null);alter table dept modify dname not null;3. 检查约束可以使用检查约束确保列中的数据在某个指定的参数范围内。create table dept(comm varchar2(15) check(comm144;例：查询在当月倒

20、数第三天参加工作的员工。select empno,ename,hiredate from emp where last_day(hiredate)-2=hiredate;例：查询每个员工的工作天数select empno,ename,hiredate,trunc(sysdate-hiredate) from emp;trunc函数：截取小数位例：显示系统时间是xxxx年xx月xx日，是一年中的第几天，是星期几。select to_char(sysdate,yyyy”年”mm”月”dd”日”DDD DAY)from dual;(3) 数字函数abs(n)返回n的绝对值ceil(n)该函数又称“天

21、花板”，返回值是大于等于n的最小整数。floor(n)该函数又称“地板”，返回值是小于等于n的最大整数。mod(m,n)返回m被n除后的余数。然而如果n为0，则返回m。round(value,precision)round将“value”中给定的数值舍入到“precision”中指定的小数位，舍入规则存在“四舍五入”的要求。sqrt(n)返回n的平方根。n值不能为负。trunc(n,m)返回截尾取整到m小数位的n，截取位数时不进行“四舍五入”。例：按每月30天计算员工的日薪金，要求计算结果四舍五入到小数点后2位。select ename,round(sal/30,2) from emp;例：计

22、算每个员工已经工作了多少个月，要求忽略小数部分。select ename,trunc(months_between(sysdate,hiredate) from emp;(4) 转换函数to_char(d,fmt)将DATE数据类型的“d”转换为varchar2数据类型的值，格式由日期格式“fmt”所指定。如果省略了“fmt”，则将d按照默认的日期格式转换为varchar2值。to_char(n,fmt)使用可选的数字格式“fmt”将number数据类型的“n”转换成为varchar2数据类型的值。如果省略了“fmt”，则将n转换为长度刚好为其有效数字位数的varchar2值。模板9：带有指定

23、位数的值。select empno,ename,sal,to_char(sal,9,999.99)from emp;模板0：前导零的值。 , （逗号）：, （逗号）。to_number(char,fmt)将包含一个数字的char或varchar2数据类型的“char”转换为格式“fmt”所指定的格式，即number数据类型。select to_number(123.456,9999.999) from dual;to_date(char,fmt)把字符串按fmt的格式转换为日期数据类型。to_date(char,fmt)的fmt格式描述如下：日期格式元素说明AD或A.D.带有或不带有句号的AD

24、标记BC或B.C.带有或不带有句号的BC标记D一周中的天（1-7）DAY天的名称（星期天 - 星期六）DD一月中的天（1-31）DDD一年中的天（1-366）HH一天中的小时（1-12）HH24一天中的小时（0-23）MI分钟（0-59）MM月（01-12）MON月名称的缩写MONTH月的名称SS秒（0-59）YYYY4位数字表示的年例：按年和月的格式显示员工参加工作的时间select ename,to_char(hiredate,yyyy)年,to_char(hiredate,mm)月 from emp;例：查询在1987年2月到1987年5月之间（包括2月和5月）参加工作的员工。selec

25、t ename,hiredate from emp where hiredate=to_date(19870201,yyyymmdd) and hiredate 900;例：统计每个部门工资在1400元以上的所有员工的工资总额。select deptno,sum(sal) from emp where sal1400 group by deptno;例：统计不同工作的个数。select count(distinct job) from emp;3. 多表查询(1) 等连接等连接是指在2个或多个表之间，某些字段的内容表示相同的含义，那么这些字段是能够相等的。写多表连接的SQL语句的时候，有这样一

26、条定律必须遵守：当N张表进行连接查询的时候，必然有N-1个连接条件。例如：查询所有员工的姓名和所在部门的名称select ename,dname from EMP a,DEPT d where a.deptno=b.deptno;(2) 外连接外连接是指在等连接的基础上，同时也找出不满足等连接条件的记录。例如：查询所有部门名称和员工姓名，包括没有员工的部门名称也显示。左外连接select dname,ename from dept a left outer join emp b on a.deptno = b.deptno;右外连接select dname,ename from emp a r

27、ight outer join dept b on a.deptno = b.deptno;(3) 自连接自连接是指查询的时候需要把一张表使用2次，使得2次所表示的表发生自身的连接。例如：查询员工姓名及其直接上级的姓名。select a.ename as 员工姓名,b.ename as 上级姓名 from emp a,emp b where a.mgr=b.empno;(4) 子查询子查询是对连接查询的重要补充，分为非关联子查询和关联子查询。非关联子查询是指子查询中不会引用主查询中的字段，那么这种子查询的执行顺序是先子查询，后主查询。例如：查询工资高于公司平均工资的所有员工。select *

28、from emp where sal(select avg(sal) from emp);另一种子查询是关联子查询，是指子查询中会引用主查询中的字段，这种子查询的执行顺序是先主查询，后子查询。相当于一个双重循环，可能带来性能问题，使用时要谨慎。例如：查询工资高于部门平均工资的所有员工。select * from emp a where sal (select avg(sal) from emp where deptno=a.deptno);4. 特殊查询(1) 前几行查询在Oracle中使用ROWNUM的伪列来表示，例如：查询emp表的第13行。select * from emp where

29、rownum=1 and rownum=4的表示方法。select * from (select rownum num,a.* from emp a where rownum=4;5. 综合练习(1) 查询员工编号，姓名和所在部门的名称。select empno,ename,dname from emp a, dept b where a.deptno=b.deptno;(2) 查询部门的名称以及该部门的人数，要求没有员工的部门也要现实。select dname,count(empno) from emp a,dept b where a.deptno(+) = b.deptnogroup b

30、y dname;(3) 查询员工姓名及其直接上级的姓名。select 员工.ename,经理.ename from emp 员工,emp 经理where 员工.mgr = 经理.empno(+);(4) 查询工资高于平均工资的员工姓名。select ename from emp where sal(select avg(sal) from emp);(5) 查询工资高于本部门平均工资的员工。select ename from emp a where sal(select avg(sal) from emp where deptno = a.deptno);(6) 查询每个部门中拥有最高工资的员

31、工的信息。select * from emp a where (select count(*) from emp where deptno=a.deptno and sala.sal)=0;(7) 显示员工信息中的第3行到第6行的信息。select * from (select rownum num,a.* from emp a where rownum=3; (8) 找出各月倒数第3天受雇的所有员工。(9) 找出早于12年前受雇的员工。(10) 以首字母大写的方式显示所有员工的姓名。(11) 显示正好为5个字符的员工的姓名。(12) 显示不带有“R”的员工的姓名。(13) 显示所有员工姓名的

32、前三个字符。(14) 显示所有员工的姓名，用a替换所有“A”。(15) 显示满10年服务年限的员工的姓名和受雇日期。(16) 显示员工的详细资料，按姓名排序。(17) 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。(18) 显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序。(19) 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。(20) 显示在一个月为30填的情况所有员工的日薪金、忽略余数。(21) 找出在（任何年份的）2月受聘的所有员工。(22) 对于每个员工显示其加入公司的天数。(23)

33、显示姓名字段的任何位置包含“A”的所有员工的姓名。(24) 以年月日的方式显示所有员工的服务年限（大概）。(25) 列出至少有一个员工的所有部门。(26) 列出薪金比“SMITH”多的所有员工。(27) 列出所有员工的姓名及其直接上级的姓名。(28) 列出受雇日期早于其直接上级的所有员工。(29) 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(30) 列出所有“CLERK”（办事员）的姓名及其部门名称。(31) 列出最低薪金大于1500的各种工作。(32) 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(33) 列出薪金高于公司平均薪金的所有员工。(34) 列出与“SCOTT”从事相同工作的所有员工。(35) 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(36) 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(37) 列出在每个部门工作的员工数量、平均工资和平均服务期限。(38) 列出所有员工的姓名、部门名称和工资。(39) 列出所有部门的详细信息和部门人数。(40) 列出各种工作的最低工资。(41) 列出各个部门的MANAGER（经理）的最低薪金。(42) 列出所有员工的年工资，按年薪从低到高排序。(43) 列出工资最高的员工信息。