ORACLE数据库实验.docx
《ORACLE数据库实验.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库实验.docx（26页珍藏版）》请在冰豆网上搜索。
ORACLE数据库实验
ORACLE数据库实验
PMQ2015-10-29
2015-11-3第一次修改
2016-06-14第二次修改现在发现，无论是什么软件。
都离不开数据库的操作。
又需要重新学习数据库。
这次更新加了目录。
第一章Oracle基本知识与SQL*PLUS环境
1、创建数据库
开始→程序→Oracle-OraDb11g-home1→配置和移植工具→DatabaseConfigurationAssistant数据库名称和密码需要设置以外，其他的选项都是直接“下一步”。
2、打开运行程序
开始→运行→sqlplus/nolog
3、连接数据库
conn/assysdba;
4、解锁scott用户、修改其密码（这一步只是熟悉操作，做不做没关系）
Alteruserscottaccountunlock;
connscott/tiger;
5、修改语言
美式英语：
altersessionsetnls_language='AMERICAN';
简体中文：
altersessionsetnls_language='SIMPLIFIEDCHINESE';
修改语言主要是日期的格式的不一致，可以用命令
selectsysdatefromdual;
查看当前日期，并且比较两种语言的不同。
这个知识在接下来实验中对表插入日期数据会涉及到。
6、两张表——DEPT、EMP
这两张表是接下来几次实验要用的，所以要先创建好。
做实验过程中，要多数据处理，我们知道表的内容就能更好地操作了。
注意：
在插入数据之前一定要写命令
altersessionsetnls_language='AMERICAN';
因为插入的日期是美式日期。
DROPTABLEDEPT;
CREATETABLEDEPT
（DEPTNONUMBER
（2）CONSTRAINTPK_DEPTPRIMARYKEY,
DNAMEVARCHAR2（14）,
LOCVARCHAR2（13））;
DROPTABLEEMP;
DROPTABLEEMP;
CREATETABLEEMP
（EMPNONUMBER（4）CONSTRAINTPK_EMPPRIMARYKEY,
ENAMEVARCHAR2（10）,
JOBVARCHAR2（9）,
MGRNUMBER（4）,
HIREDATEDATE,
SALNUMBER（7,2）,
COMMNUMBER（7,2）,
DEPTNONUMBER
（2）CONSTRAINTFK_DEPTNOREFERENCESDEPT）;
INSERTINTODEPTVALUES（10,'ACCOUNTING','NEWYORK'）;
INSERTINTODEPTVALUES（20,'RESEARCH','DALLAS'）;
INSERTINTODEPTVALUES（30,'SALES','CHICAGO'）;
INSERTINTODEPTVALUES（40,'OPERATIONS','BOSTON'）;
INSERTINTOEMPVALUES
（7369,'SMITH','CLERK',7902,to_date（'17-12-1980','dd-mm-yyyy'）,800,NULL,20）;
INSERTINTOEMPVALUES
（7499,'ALLEN','SALESMAN',7698,to_date（'20-2-1981','dd-mm-yyyy'）,1600,300,30）;
INSERTINTOEMPVALUES
（7521,'WARD','SALESMAN',7698,to_date（'22-2-1981','dd-mm-yyyy'）,1250,500,30）;
INSERTINTOEMPVALUES
（7566,'JONES','MANAGER',7839,to_date（'2-4-1981','dd-mm-yyyy'）,2975,NULL,20）;
INSERTINTOEMPVALUES
（7654,'MARTIN','SALESMAN',7698,to_date（'28-9-1981','dd-mm-yyyy'）,1250,1400,30）;
INSERTINTOEMPVALUES
（7698,'BLAKE','MANAGER',7839,to_date（'1-5-1981','dd-mm-yyyy'）,2850,NULL,30）;
INSERTINTOEMPVALUES
（7782,'CLARK','MANAGER',7839,to_date（'9-6-1981','dd-mm-yyyy'）,2450,NULL,10）;
INSERTINTOEMPVALUES
（7788,'SCOTT','ANALYST',7566,to_date（'13-JUL-87'）-85,3000,NULL,20）;
INSERTINTOEMPVALUES
（7839,'KING','PRESIDENT',NULL,to_date（'17-11-1981','dd-mm-yyyy'）,5000,NULL,10）;
INSERTINTOEMPVALUES
（7844,'TURNER','SALESMAN',7698,to_date（'8-9-1981','dd-mm-yyyy'）,1500,0,30）;
INSERTINTOEMPVALUES
（7876,'ADAMS','CLERK',7788,to_date（'13-JUL-87'）-51,1100,NULL,20）;
INSERTINTOEMPVALUES
（7900,'JAMES','CLERK',7698,to_date（'3-12-1981','dd-mm-yyyy'）,950,NULL,30）;
INSERTINTOEMPVALUES
（7902,'FORD','ANALYST',7566,to_date（'3-12-1981','dd-mm-yyyy'）,3000,NULL,20）;
INSERTINTOEMPVALUES
（7934,'MILLER','CLERK',7782,to_date（'23-1-1982','dd-mm-yyyy'）,1300,NULL,10）;
第二章数据表的创建
1．创建如下三个基表：
S（S#，SNAME，AGE，SEX）对应的中文为：
[学生（学号，姓名，年龄，性别）]
SC（S#，C#，GRADE）对应的中文为：
[学习（学号，课程号，成绩）]
C（C#，CNAME，TEACHER）对应的中文为：
[课程（课程号，课程名，任课教师）]
注：
以后的实验要用到这三个基本表
createtables
（
snumber（10）notnullprimarykey,
snamevarchar2（10）,
agenumber（3）,
sexvarchar2（10）
）;
describes;
createtablec
（
cnumber（10）notnullprimarykey,
cnamevarchar2（10）,
teachervarchar2（10）
）;
describec;
createtablesc
（
snumber（10）notnullreferencess（s）,
cnumber（10）notnullreferencesc（c）,
gradenumber（3）,
primarykey（s,c）
）;
describesc;
2．生成一个数据表PROJECTS，其字段定义如下，其中PROJID是主键并且要求
P_END_DATE不能比P_START_DATE早。
字段名称数据类型长度
PROJIDNUMBER4
P_DESCVARCHAR220
P_START_DATEDATE
P_END_DATEDATE
BUDGET_AMOUNTNUMBER7,2
MAX_NO_STAFFNUMBER2
createtableprojects
（
projidnumber（4）notnullprimarykey,
p_descvarchar2（20）,
p_start_datedate,
p_end_datedate,
budget_amountnumber（7,2）,
max_no_staffnumber
（2）,
check（p_start_date）;
describeprojects;
3．生成一个数据表ASSIGNMENTS，其字段定义如下，其中PROJID是外键引自PROJECTS数据表，EMPNO是数据表EMP的外键，并且要求PROJID和EMPNO不能为NULL。
字段名称数据类型长度
PROJIDNUMBER4
EMPNONUMBER4
A_START_DATEDATE
A_END_DATEDATE
BILL_RATENUMBER4,2
ASSIGN_TYPEVARCHAR22
createtableassignments
（
projidnumber（4）notnullreferencesprojects（projid）,
empnonumber（4）notnullreferencesEMP（EMPNO）,
a_start_datedate,
a_end_datedate,
bill_ratenumber（4,2）,
assign_typevarchar2
（2）,
primarykey（projid,empno）
）;
describeassignments;
4、用DESCRIBE命令查看1和2题定义的字段。
describes;
describec;
describesc;
describeprojects;
5、给1题中的PROJECTS数据表增加一个COMMENTS字段，其类型为LONG。
给2题中的ASSIGNMENTS数据表增加一个HOURS字段，其类型为NUMBER。
altertableprojectsadd（commentslong）;
altertableassignmentsadd（hoursnumber（6））;
第三章数据插入、修改和删除
1、用INSERT命令输入数据
表3-1基本表S的数据
S1WANG20M
S2LIU19M
S3CHEN22M
S4WU19M
S5LOU21F
S8DONG18F
表3-2基表C的数据
C2MATHSMA
C4PHYSICSSHI
C3CHEMISTRYZHOU
C1DBLI
C5OSWEN
表3-3基本表SC的数据（空格为未选修）
C#S#S1S2S3S4S5S8
C1808590757090
C270NULL8560NULL
C38595NULL8090
C490NULL70
C57065NULL
insertintosvalues（'1','WANG','20','M'）;
insertintosvalues（'2','LIU','19','M'）;
insertintosvalues（'3','CHEN','22','M'）;
insertintosvalues（'4','WU','19','M'）;
insertintosvalues（'5','LOU','21','F'）;
insertintosvalues（'8','DONG','18','F'）;
insertintocvalues（'2','MATHS','MA'）;
insertintocvalues（'4','PHYSICS','SHI'）;
insertintocvalues（'3','CHEMISTRY','ZHOU'）;
insertintocvalues（'1','DB','LI'）;
insertintocvalues（'5','OS','WEN'）;
insertintoscvalues（'1','1','80'）;
insertintoscvalues（'2','1','85'）;
insertintoscvalues（'3','1','90'）;
insertintoscvalues（'4','1','75'）;
insertintoscvalues（'5','1','70'）;
insertintoscvalues（'8','1','90'）;
insertintoscvalues（'1','2','70'）;
insertintoscvalues（'2','2',NULL）;
insertintoscvalues（'3','2','85'）;
insertintoscvalues（'5','2','60'）;
insertintoscvalues（'8','2',NULL）;
insertintoscvalues（'1','3','85'）;
insertintoscvalues（'3','3','95'）;
insertintoscvalues（'4','3',NULL）;
insertintoscvalues（'5','3','80'）;
insertintoscvalues（'8','3','90'）;
insertintoscvalues（'1','4','90'）;
insertintoscvalues（'2','4',NULL）;
insertintoscvalues（'4','4','70'）;
insertintoscvalues（'1','5','70'）;
insertintoscvalues（'5','5','65'）;
insertintoscvalues（'8','5',NULL）;
select*froms;
select*fromc;
select*fromsc;
2、对S、C、SC表进行操作：
1）、把C2课程的非空成绩提高10%。
2）、在SC表中删除课程名为PHYSICS的成绩的元组。
3）、在S和SC表中删除学号为S8的所有数据。
updatescsetgrade='100'
wherec='2'andgrade>=91;
updatescsetgrade=1.1*grade
wherec='2'andgrade<91;
deletefromscwherec='4';
deletefromscwheres='8';
deletefromswheres='8';
3、在PROJECTS数据库表中增加下列记录：
PROJID12
P_DESCWRITEC030COURSEPROOFREADNOTES
P_START_DATE02-JAN-8801-JAN-89
P_END_DATE07-JAN-8810-JAN-89
BUDGET_AMOUNT500600
MAX_NO_STAFF11
COMMENTSBRCREATIVEYOURCHOICE
altersessionsetnls_language='AMERICAN';
insertintoprojectsvalues（'1','WRITEC030COURSE','02-JAN-88','07-JAN-88','500','1','BRCREATIVE'）;
insertintoprojectsvalues（'2','PROOFREADNOTES','01-JAN-89','10-JAN-89','600','1','YOURCHOICE'）;
4、在ASSIGNMENTS数据库表中增加下列记录：
PROJID112
EMPNO736979027844
A_START_DATE01-JAN-8804-JAN-8801-JAN-89
A_END_DATE03-JAN-8807-JAN-8810-JAN-89
BILL_RATE50.0055.0045.50
ASSIGN_TYPEWRWRPF
HOURS152030
insertintoassignmentsvalues（'1','7369','01-JAN-88','03-JAN-88','50.00','WR','15'）;
insertintoassignmentsvalues（'1','7902','04-JAN-88','07-JAN-88','55.00','WR','20'）;
insertintoassignmentsvalues（'2','7844','01-JAN-89','10-JAN-89','45.50','PF','30'）;
5、把ASSIGMENTS表中ASSIGNMENTTYPE的WR改为WT，其他的值不变
updateassignmentssetassign_type='WT'whereassign_type='WR';
6、在PROJECTS和ASSIGNMENTS插入更多的记录。
7、删除自己随意插入的记录。
注意：
在插入数据到表assignments的时候一定要保证员工号是已经存在，不然的话需要自己插入数据到表EMP。
下面的7788这个员工号是已经存在的。
insertintoassignmentsvalues（'1','7788','20-JAN-88','26-JAN-88','50.00','WR','10'）;
deletefromassignmentswhereprojid='1'andempno='7788';
第四章数据查询
对Oracle数据库基本表EMP和和DEPT操作：
1、查询一个在部门10中的所有工作岗位的唯一列表，在输出中包括部门的地点。
selectdistinctJOB,LOCfromDEPT,EMP
whereEMP.DEPTNO='10'andDEPT.DEPTNO=EMP.DEPTNO;
2、列出工资在1000到2000之间的所有员工的ENAME，DEPTNO，SAL。
selectENAME,DEPTNO,SALfromEMP
whereSALbetween1000and2000;
3、显示DEPT表中的部门号和部门名称，并按部门名称排序。
selectDEPTNO,DNAMEfromDEPTorderbyDNAME;
注：
虽然题目没有要求按照升序或者降序排列，但是默认是升序。
在此句的末尾加上desc就是指明降序排列，acs是升序排列。
4、显示所有不同的工作类型。
selectdistinctJOBfromEMP;
5、列出部门号在10到20之间的所有员工，并按名字的字母排序。
selectENAMEfromEMP
whereDEPTNObetween10and20orderbyENAME;
6、列出部门号是20，工作是职员的员工。
selectENAMEfromEMP
whereDEPTNO='20'andJOB='CLERK';
7、显示名字中包含TH和LL的员工名字。
selectENAMEfromEMP
whereENAMElike'%TH%'orENAMElike'%LL%';
8、显示所有员工的名字（Ename）和报酬（Remuneration）。
selectENAME,SALfromEMP;
9、显示在1983年中雇佣的员工。
--第一种写法，网上找的，其实不太正确
selectENAMEfromEMPwhereHIREDATElike'%83';
--第二种写法
altersessionsetnls_language='AMERICAN';
selectENAMEfromEMPwhereHIREDATE>='01-Jan-1983'andHIREDATE<'01-Jan-1984';
10、查询每个部门的平均工资。
SELECTJOB,AVG（SAL）fromEMPGROUPBYJOB;
11、查询出每个部门中工资最高的职工。
SELECTENAME,SAL,DEPTNOFROMEMP
WHERESALIN
（SELECTMAX（SAL）FROMEMPGROUPBYDEPTNO）;
12、查询出每个部门比平均工资高的职工人数。
SELECTDEPTNO,COUNT（SAL）FROMEMPWHERESAL>（SELECTAVG
（SAL）FROMEMP）GROUPBYDEPTNO;
第五章视图、索引、序列和权限设置
对基本表S、C和和SC操作
1、建立男学生的视图，属性包括学号、姓名、选修课程和成绩。
createviewm_stu（sno,sname,cname,grade）
asselects.s,s.sname,ame,sc.gradefroms,c,sc
wheres.s=sc.sandc.c=sc.cands.sex='M';
select*fromm_stu;
2、在男学生视图中查询平均成绩大于80分的学生学号和姓名。
selectdistinctsno,sname,AVG（grade）avg_gradefromm_stu
wheresnoin（selectsnofromm_stu）
groupbysno,snamehavin
升级会员 