ORACLEA数据库操作.doc

第四章 ORACLEA数据库操作一、表空间1、创建CREATE TABLESPACE MYSPDATAFILE F:appAdministratoradminmydata01.dbf SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 200m2、修改，添加数据文件mydata02.dbfALTER TABLESPACE MYSPADD DATAFILE F:appAdministratoradminmydata02.dbf SIZE 100M REUSEAUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED二、创建表1、学生表create table student(sno char(15) primary key , sname char(8) not null, ssex char(2), sage int default 18, sdept varchar(20),constraint ck_s1 check (sage between 15 and 35),constraint ck_s2 check (ssex=男 or ssex=女)tablespace MYSP2、课程表create table course(cno char(8) primary key,cname char(30) not null unique,ccredit number(2)tablespace MYSP3、成绩表create table score(sno char(15),cno char(8),grade int,primary key(sno,cno),constraint fk_sc1 foreign key(sno) references student(sno),constraint fk_sc2 foreign key(cno) references course(cno)tablespace MYSP三、录入数据1、学生表insert into studentselect 0803010126,高天,男,20,计算机 from dual unionselect 0601020212,张新,女,21,自动化 from dual unionselect 0802020230,黎明,男,18,材料 from dual unionselect 0803020101,张铁,男,19,采矿 from dual unionselect 0702010205,刘芳,女,22,自动化 from dual unionselect 0603010114,李振刚,男,23,采矿 from dual unionselect 0803020109,杨云,女,17,材料 from dual unionselect 0801010215,张扬,男,18,材料 from dual unionselect 0703020216,张书贺,男,21,计算机 from dual unionselect 0601010127,张庚源,男,23,自动化 from dual unionselect 0602010223,余杰,女,22,自动化 from dual unionselect 0701010132,刘一,女,19,采矿 from dual unionselect 0802020133,安然,女,20,计算机 from dual unionselect 0802010206,于多,男,19,材料 from dual unionselect 0703010111,王艳,女,21,采矿 from dual unionselect 0803020107,刘明浩,男,18,计算机 from dual 2、课程表insert into courseselect A001,计算机导论,2 from dual unionselect A002,C语言 ,3 from dual unionselect A003,电子技术,4 from dual unionselect A004,数据库,3 from dual unionselect A005,工程力学,2 from dual unionselect B001,接口原理,3 from dual unionselect B002,无线技术 ,4 from dual unionselect C001,采矿学 ,2 from dual unionselect C002,焊接技术,3 from dual 3、成绩表insert into scoreselect 0601020212,B001,89 from dual unionselect 0803010126,A002,45 from dual unionselect 0601020212,B002,91 from dual unionselect 0702010205,A003,95 from dual unionselect 0702010205,A002,76 from dual unionselect 0803020101,C001,68 from dual unionselect 0803020101,A005,81 from dual unionselect 0802020230,C002,58 from dual unionselect 0703020216,A001,75 from dual unionselect 0703020216,A004,88 from dual unionselect 0702010205,B002,71 from dual unionselect 0803020107,A004,86 from dual unionselect 0802020133,B002,94 from dual unionselect 0802020133,A001,51 from dual unionselect 0701010132,A005,74 from dual unionselect 0701010132,C001,39 from dual 4、向表中插入数据时应注意的问题insert into student values(0802020133,李军,男,18,材料) 违反主键约束insert into student values(111111,李军,男,14,材料) 违反检查约束insert into student values(111111,李军,男,shi,材料) 输入数据的类型与字段的数据类型不匹配insert into student values(111111,李军,男,18) 字段个数与value值的个数不匹配insert into student values(111111,李军,男,18,材料) 正确执行insert into score values(06010,B001,89) 外键所引用的表中没有与该学号对应的学生四、修改数据表1、修改表结构，学生表添加字段宿舍号“dorm”和家庭地址“address”属性列alter table studentadd(address char(30),dorm char(5)2、修改表结构，删除学生表“宿舍号dorm”字段alter table studentdrop column dorm 3、为SCORE表增加约束条件，成绩为0-100alter table score add(constraint ck_sc check(grade=0) 4、删除表drop table score 5、更新表中数据，将采矿的王艳同学更改为自动化专业update student set sdept=自动化 where sname=王艳 and sdept=采矿6、将成绩表中不及格的C语言的成绩全部改为60分select * from score where cno in(select cno from course where cname=C语言)update score set grade=60 where cno in(select cno from course where cname=C语言) and grade= 45、查询与高天在同一个系学习的学生的姓名SELECT S2.Sname FROM Student S1 , Student S2 Where S1.sdept= S2.sdept and S1.Sname = 高天 and S2.Sname != 高天;6、使用oracle内连接inner join查询每个人的选修课成绩情况select sname,cno,grade from student inner join score on student.sno=score.sno7、使用oracle外连接out join查询每个人的选修课成绩情况左外连接：select student.sno,sname,grade from student left outer join score on student.sno=score.sno右外连接：select student.sno,sname,grade from student right outer join score on student.sno=score.sno8、查询自动化和材料这两个学院的所有学生select * from student where sdept=自动化union select * from student where sdept=材料9、查询选修了c语言的学生学号和姓名Select sno,sname from student Where sno in(select sno from score Where cno in(select cno from course where cname=c语言)六、视图1、创建视图（使用sys用户登录，否则权限不足）(1)使用OEM创建每个学生的所有成绩视图CREATE OR REPLACE VIEW v_scjk AS SELECT student.sno,sname, cname,grade FROM student,course,score WHERE student.sno=score.sno AND o=o(2)使用SQL语句创建接口原理成绩视图CREATE OR REPLACE VIEW v_scjk AS SELECT student.sno,sname, cname,grade FROM student,course,score WHERE student.sno=score.sno AND o=o AND ame=接口原理WITH CHECK OPTION2、向视图中插入数据时应注意的问题(1)每门课程的平均成绩视图create or replace view v_scpj as select cname,avg(grade) pjcj from course,score where o=o group by cnameupdate v_scpj set pjcj=60 where cname=采矿学 /不能对经过计算的列赋值(2)每个学生的成绩视图update v_scst set grade=60 where cname=C语言 and grade60 /成功update v_scst set grade=150 where cname=C语言 /不成功，违反约束insert into v_scst(sno,cname,grade) values(0803030101,C语言，86) /多表插入，不能执行，且触犯SCORE表的主键约束(3)所有女生的基本情况视图create view v_stfas select sno,sname,sage,sdept from student where student.ssex=女with check option /检查插入数据的性别insert into v_sf(sno,sname) values(08010227,王立) / 不使用with check option时可以插入，否则不能插入，输入数据时检查性别/ 注意学号不能重复,不能丢失七、同义词在Oracle中对用户的管理是使用权限的方式来管理的，也就是说，如果我们想使用数据库，我们就必须得有权限，但是如果是别人将权限授予了我们，我们也是能对数据库进行操作的，但是我们必须要已授权的表的名称前键入该表所有者的名称，所以这就是比较麻烦的，遇到这种情况，我们该怎么办呢？创建个Oracle同义词吧！这样我们就可以直接使用同义词来使用表了。Oracle数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，数据库管理员都可以根据实际情况为他们定义同义词。Oracle同义词有两种类型，分别是公用Oracle同义词与私有Oracle同义词。(1)公用Oracle同义词由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。(2)私有Oracle同义词它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。1、使用sys用户登录，创建公用、私有同义词，再用system用户检验公用同义词：select * from student /不成功create public synonym student for scott.student select * from student /使用system用户查询，成功私有同义词：select * from course /不成功create synonym course for scott.courseselect * from course /使用system用户查询，不成功删除同义词：drop synonym course2、使用OEM为scott用户的score表创建同义词八、索引1、索引特点在oracle中，索引是一种供服务器在表中快速查找一个行的数据库结构