Oracle基本语句(史上上机考试最全总结).doc

Oracle基本语句一、开机前的准备工作sqlplus /nologConn stud/stud as sysdba;drop user ben cascade;create user ben identified by 12345;grant dba to ben;conn ben/12345;show user ; c:createUser.sql;二、创建表的相关语句1、创建表- 创建学生表create table student( studid char(6) primary key, studname varchar2(16) not null, gender char(4) check(gender in(男,女), addr varchar2(256);- 创建课程表create table course( courseid char(6) primary key, coursename varchar2(32) not null unique, teacher varchar2(16) , score number check(score in (1,3,5);- 创建选课表create table courSelc( recid number(4) primary key, studid char(6), courseid char(6), score number(2) check(score =0 and score =100), constraint fk_stud foreign key(studid) references student(studid), constraint fk_cour foreign key(courseid) references course(courseid);2、插入数据insert into student values(100001,李白,男,和平区);insert into student values(100002,杜甫,男,河西区);insert into student values(100003,李清照,女,河北区);insert into student values(100004,蔡琰,女,红桥区);insert into course values(M1001,数学,奥巴马,3);insert into course values(J2002,军事理论,拿破仑,5);insert into course values(S3003,摔跤,普京,1);insert into courSelc values(1,100001,M1001,70);insert into courSelc values(2,100001,J2002,60);insert into courSelc values(3,100001,S3003,80);insert into courSelc values(4,100002,M1001,75);insert into courSelc values(5,100002,J2002,85);insert into courSelc values(6,100002,S3003,95);insert into courSelc values(7,100003,M1001,50);insert into courSelc values(8,100003,J2002,55);insert into courSelc values(9,100003,S3003,45);insert into courSelc values(10,100004,M1001,10);insert into courSelc values(11,100004,J2002,15);insert into courSelc values(12,100004,S3003,25);3、修改表语句-alter Alter table student add tel char(15); -增加一列tel Alter table student modify tel barchar(17); -修改tel列的属性 Alter table student drop column tel; -删除tel列4、修改表中数据语句-update Update student set studid=100008 where studname=李白;5、删除表语句-drop Drop table student；三、创建函数的相关语句1、创建函数（以实验五为例）create or replace function GetCodeByName(v_name in varchar2)return varchar2 isv_idprex char(16);begin v_idprex := ; case v_name when 李白 then v_idprex := LB_; when 杜甫 then v_idprex := DF_; when 李清照 then v_idprex := LQZ_; when 蔡琰 then v_idprex := CY_; else dbms_output.put_line(error); end case; return v_idprex; exception when CASE_NOT_FOUND then dbms_output.put_line(没有发现匹配选项); when NO_DATA_FOUND then dbms_output.put_line(没有发现数据); when TOO_MANY_ROWS then dbms_output.put_line(返回数据过多);end;/2、调用函数语句select GetCodeByName(studname) from d_student where studname=李白;3、查看函数语句select object_name,object_id,object_type from user_procedures order by object_type;-查看函数select name,line,text from user_source where name= GetCodeByName;-查看源脚本4、删除函数Drop function GetCodeByName;注意：会灵活的将结构控制语句运用到函数中。四、创建游标的相关语句1、创建游标（以实验五为例）declare cursor stud_curis select studid from student;sstudid student.studid%type;begin open stud_cur; loop fetch stud_cur into sstudid; exit when stud_cur%notfound; case sstudid when 100001 then insert into d_student values(LB_100001,李白,男,和平区); when 100002 then insert into d_student values(DF_100002,杜甫,男,河西区); when 100003 then insert into d_student values(LQZ_100003,李清照,女,河北区); when 100004 then insert into d_student values(CY_100004,蔡琰,女,红桥区); else dbms_output.put_line(error); end case; end loop; close stud_cur; exception when CASE_NOT_FOUND then dbms_output.put_line(没有发现匹配选项); when NO_DATA_FOUND then dbms_output.put_line(没有发现数据); when TOO_MANY_ROWS then dbms_output.put_line(返回数据过多);end;/2、学会使用bulk collect和for结合使用的游标（参考书上P166页例四）3、学会使用 cursor for loop的游标（参考书上P167页例五）4、学会将游标的属性和结构控制语句结合使用（参考书上P168-P170页）五、创建视图的相关语句1、创建视图（以实验四为例）create view VCourSelc as select a.studid,studname,gender,addr,b.courseid,coursename,teacher,c.score,recid,b.score as courscore from student a inner join courselc b on a.studid = b.studid inner join course c on c.courseid = b.courseid;2、学会创建单表视图（P185例1）、多表视图（P185例2）、视图的视图（P187例3）3、删除视图Drop view VCourSelc;4、书中例8、9、10需要认真分析六、创建存储过程的相关语句1、创建存储过程之前先执行下面语句set serveroutput on;2、创建存储过程（以实验五为例）create or replace procedure stud_proascursor stud_curis select studid from student;sstudid student.studid%type;begin open stud_cur; loop fetch stud_cur into sstudid; exit when stud_cur%notfound; case sstudid when 100001 then insert into d_student values(LB_100001,李白,男,和平区); when 100002 then insert into d_student values(DF_100002,杜甫,男,河西区); when 100003 then insert into d_student values(LQZ_100003,李清照,女,河北区); when 100004 then insert into d_student values(CY_100004,蔡琰,女,红桥区); else dbms_output.put_line(error); end case; end loop; close stud_cur; exception when CASE_NOT_FOUND then dbms_output.put_line(没有发现匹配选项); when NO_DATA_FOUND then dbms_output.put_line(没有发现数据); when TOO_MANY_ROWS then dbms_output.put_line(返回数据过多);end;/3、执行存储过程exec stud_pro;4、查看存储过程使用相应的select语句查看5、删除存储过程Drop procedure stud_pro;七、Select检索的相关语句1、学会在select语句中使用数据排序-order by.asc|desc（P61-P65）2、学会在select语句中使用检索条件-where（P66-P70）3、学会在select语句中使用分组查询-group by和having（P70-P72）4、学会在select语句中使用子查询（P73-P75）5、学会在select语句中使用连接查询（P76-P81），其中内连接和外连接重点复习6、学会在select语句中使用集合函数（P104-P106）八、表空间、用户、权限、角色、概要文件的相关语句1、掌握表空间的创建（P344）、设置（P346-P347）、删除（P349）、数据文件的移动（P356）2、掌握用户的创建（P362）、设置（P364）、删除（P365）3、掌握权限的授予（P369）、撤销（P70）、查询（P371）（参考实验三）4、掌握角色的创建（P37