Oracle数据库上机.doc

1表空间create tablespace gcq datafile d:gcq1.dbf size 100m, d:gcq2.dbf size 100m;create tablespace gcq1 datafile d:gcql1.dbf size 100m, d:gcql2.dbf size 100m;create temporary tablespace gcqtemp tempfile d:tempgcq1.dbf size 100m, d:tempgcq2.dbf size 100m;create user gcq identified by 123 default tablespace gcq temporary tablespace gcqtemp;grant connect,resource to gcq;create table gcqtest(vid int,vname varchar(10);insert into gcqtest(vid,vname) values(1,king);2表管理create table gcq1(vid int,vname varchar(10);select * from gcq1;create table gcq2(vid int not null,vname varchar(10);insert into gcq2(vid,vname) values(1,king);insert into gcq2(vname) values(king);-插入失败select * from gcq2;create table gcq3(vid int not null, vname varchar(10) default 无);insert into gcq3(vid) values(1);insert into gcq3(vid,vname) values(1,king);select * from gcq3;/* primary key 主键*/create table gcq4(vid int primary key,vname varchar(10);insert into gcq4(vid,vname) values(1,king);insert into gcq4(vid,vname) values(1,king);select * from gcq4;create table gcq5(vid int, vname varchar(10), constraint pk_gcq5 primary key(vid);/*foreign key 外键*/create table class(classid int primary key, classname varchar(10);create table student(stdid int primary key, classid int references class(classid), stdname varchar(10);insert into class(classid,classname) values(1,class1);insert into student(stdid,classid,stdname) values(1,1,king);insert into student(stdid,classid,stdname) values(2,2,king1);create table student1(stdid int primary key, classid int, stdname varchar(10), constraint fk_class_student foreign key(classid) references class(classid);/*unique 唯一约束*/create table gcq6(vid int primary key, vname varchar(10) unique);insert into gcq6(vid,vname) values(1,king);3视图序列案例 create sequence gcqseq1; create sequence gcqseq2 start with 1 minvalue 1 maxvalue 10 nocache; create sequence gcqseq3 start with 1 minvalue 1 maxvalue 10 cycle nocache;select gcqseq1.nextval from dual; select gcqseq1.currval from dual; select gcqseq2.nextval from dual; select gcqseq2.currval from dual; create table gcqtest(vid1 int,vid2 int,vid3 int); insert into gcqtest(vid1,vid2,vid3) values(gcqseq1.nextval,gcqseq1.nextval,gcqseq1.nextval); insert into gcqtest(vid1,vid2,vid3) values(gcqseq1.nextval,gcqseq2.nextval,gcqseq3.nextval); select * from gcqtest; select gcqseq2.nextval from dual; select gcqseq3.nextval from dual;4用户管理create user frog identified by 123; default tablespace gcqspace temporary tablespace gcqtemp;grant connect,resource to frog; grant connect,resource to bear; create table gcqtest(vid int,vname varchar(10); insert into gcqtest(vid,vname) values(1,king); select * from gcqtest; create user snake identified by 123;-权限不足 /* (2)授予系统权限:create user,create table.(管理员权限) grant 系统权限1,.系统权限n to 用户;*/ grant create user to frog; create user snake identified by 123; grant connect,resource to snake; create table gcqtest(vid int,vname varchar(10); insert into gcqtest(vid,vname) values(1,king); select * from gcqtest; select * from frog.gcqtest;-没有权限/* (3)授予对象权限:select,delete,update,insert.(对象所有者权限) grant 对象权限1,.对象权限n on 对象 to 用户;*/ grant select on frog.gcqtest to snake; select * from frog.gcqtest; grant insert on frog.gcqtest to snake; insert into frog.gcqtest(vid,vname) values(2,queen); grant all on frog.gcqtest to snake; delete from frog.gcqtest; select * from frog.gcqtest; /*3.权限回收: (1)回收角色:角色是一组权限的集合，只要回收用户相应的角色，就相当于将一组权限回收。(管理员权限) revoke 角色1,.角色n from 用户; 注意:要使用户可以正常操作oracle数据库，至少需要connect,resource这两个角色。*/ revoke connect,resource from bear; /* (2)回收系统权限:create user,create table.(管理员权限) revoke 系统权限1,.系统权限n from 用户;*/ revoke create user from frog; create user bee identified by 123;-权限不足/* (3)回收对象权限:select,delete,update,insert.(对象所有者权限) revoke 对象权限1,.对象权限n on 对象 from 用户;revoke all on 对象 from 用户;*/* (3)授予对象权限:select,delete,update,insert.(对象所有者权限) grant 对象权限1,.对象权限n on 对象 to 用户;*/ grant select on frog.gcqtest to snake; select * from frog.gcqtest; grant insert on frog.gcqtest to snake; insert into frog.gcqtest(vid,vname) values(2,queen); grant all on frog.gcqtest to snake; delete from frog.gcqtest; select * from frog.gcqtest; /*3.权限回收: (1)回收角色:角色是一组权限的集合，只要回收用户相应的角色，就相当于将一组权限回收。(管理员权限) revoke 角色1,.角色n from 用户; 注意:要使用户可以正常操作oracle数据库，至少需要connect,resource这两个角色。*/ revoke connect,resource from bear; /* (2)回收系统权限:create user,create table.(管理员权限) revoke 系统权限1,.系统权限n from 用户;*/ revoke create user from frog; create user bee identified by 123;-权限不足/* (3)回收对象权限:select,delete,update,insert.(对象所有者权限) revoke 对象权限1,.对象权限n on 对象 from 用户;revoke all on 对象 from 用户;*/ revoke select on gcqtest from snake; select * from frog.gcqtest;-权限不足 insert into frog.gcqtest(vid,vname) values(1,king); select * from gcqtest; revoke all on gcqtest from snake; delete from frog.gcqtest;5匿名块/*匿名块:相当于sql server中的批处理,可以批量执行sql语句。 1.语法: declare -变量声明; begin -执行部分; -异常处理部分; end; 2.变量声明: (1)直接声明: 变量名 数据类型; i int;*/declarei int;begin i := 1; dbms_output.put_line(i);-相当于System.out.println()end;declarei,j int;-错误begin i := 1; dbms_output.put_line(i);-相当于System.out.println()end;/*(2)声明列变量: 变量名 表.列%type; vename emp.ename%type; 声明的列变量具有与表中对应列相同的数据类型。*/declarevename varchar(10);vename1 emp.ename%type;begin vename := smith; vename1 := smith; dbms_output.put_line(vename); dbms_output.put_line(vename1);end;/* (3)声明行变量: 变量名 表%rowtype; e emp%rowtype; 行变量可以保存表中一整行数据。如果要访问具体列，则使用变量名.列名。变量名.*为非法访问。*/declaree emp%rowtype;beginselect * into e from emp where empno = 7369;dbms_output.put_line(e.ename);dbms_output.put_line(e.sal);-dbms_output.put_line(e.*);错误，不可以使用*end;select * from emp;/* 3.变量赋值: (1)直接赋值: 变量名 := 值; i := 1;*/declarei int;begin i := 1; dbms_output.put_line(i);-相当于System.o