oracle 9i 学习 过程

1. 启动

 

之前已经安半了数据 oracle 9i ，很久没发，用 plsql 连，出现：ORA-12514 。在网上看了。

是：C:\oracle\ora92\network\admin\tnsnames.ora 文件，没有配置本地的数据，通过C:\oracle\ora92\network\admin\listener.ora 看到信息在tnsnames.ora 文件增加了配置如下：

 

oracle9i =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =baobao)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle9i)
    )
  )

 

就OK了。

 

 

2.Oracle9i创建表空间

用sys帐号登陆，连接身份选SYSDBA，执行如下语句

// 创建表空间
drop tablespace test;

create tablespace test

DATAFILE 'D:\oracle\oradata\test\test.dbf'
size 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

alter user scott quota unlimited on test;
commit;

创建了一个名为test的表空间,数据库文件创建 在D:\oracle\oradata\test\test.dbf,并给scott 帐号授权使用表空间。

//创建scott用户 并分配表空间

drop user scott cascade;
create user scott
identified by tiger
default tablespace test
quota 100M on test;
commit;

//创建完毕后授权

Grant connect,resource,DBA to scott;

 

//sql plus 中执行.sql 文件

@d:\xzsp_hrb_oracle_doc.sql

//创建一个名为manager的角色

create role manager;

//给manager这个角色授于相应的系统权限

grant create table,create view,create session to manager;

//授予针对某个对象的权限如查询某个表的权限

grant select on 表名字 to manager; //可以把一个权限同时赋予多个角色或者用户，但不能把多个权限同时赋予多个角色或者用户，需分开执行。

//把manager这个角色分配给scott,xzsp两个帐号。

grant manager to scott,xzsp;

//修改帐号scott密码为tiger

alter user scott identified by baobao;

//scott用户建的表，如果想给其他用户权限则必须用scott用户登陆后执行如下语句,即使你是DBA也不把   

一个用户（scott）所创建的表赋予给另外一个用户(xzsp)操作的权限,除非scott用户给DBA授权。

grant select on 表名字 to xzsp;

grant select on employees to scott,xzsp;

//用scott用户登陆 给system授权

grant select on student to system with grant option;

//用system 登陆,再把查询student的权限授予另外一个用户。

grant select on scott.student to xzsp;

//system同时可取消xzsp用户的该权限。

revoke select on scott.student from xzsp;

权限传递的级联:scott用户把权限给A（同时允许A传递给其他用户 with grand option）,A把权限再传递给B，如果scott用户撤消A的权限，则B也会失去相应的权限。

grant update (department_name,location_id) on departments to scott,xzsp; //授予用户修改某个表中指定字段的权限。

grant select,insert on departments to scott with grant option; //把查询，增加departments表的权限给scott用户并允许他把权限授予给其他拥护。

grant select on scott.departments to public; //授予public 角色查询scott下departments表的权限。

//访问其他数据库对象(database links)

create public database link hq.acme.com using 'sales';

select * from emp@hq.acme.com      //emp为表名

 

CREATE USER 创建一个用户（通常由DBA来完成）。

GRANT 给于其他用户使用你自己对象的权限。

CREATE ROLE 创建一个权限的集合，即角色。

ALTER USER 修改一个用户的密码。

REVOKE 撤消一个用户在某个对象上的权限，如 表 视图等。

 

3.导入导出

 

exp scott/123456 owner=scott file=d:/scott.dmp log=d:/scott.log

exp asxa/123456@192.168.1.132 owner=mmsa file=d:/scott.dmp log=d:/scott.log

 

imp scott/123456 fromuser=asxa touser=scott file=d:/scott.dmp

 

USERID 用户名/口令
FULL 导出整个文件 (N)
BUFFER 数据缓冲区的大小
OWNER 所有者用户名列表
FILE 输出文件 (EXPDAT.DMP)
TABLES 表名列表
COMPRESS 导入一个范围 (Y)
RECORDLENGTH IO 记录的长度
GRANTS 导出权限 (Y)
INCTYPE 增量导出类型
INDEXES 导出索引 (Y)
RECORD 跟踪增量导出 (Y)
ROWS 导出数据行 (Y)
PARFILE 参数文件名
CONSTRAINTS 导出限制 (Y)
CONSISTENT 交叉表一致性
LOG 屏幕输出的日志文件
STATISTICS 分析对象 (ESTIMATE)
DIRECT 直接路径 (N)
TRIGGERS 导出触发器 (Y)
FEEDBACK 显示每 x 行 (0) 的进度
FILESIZE 各转储文件的最大尺寸
QUERY 选定导出表子集的子句

下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TABLESPACES 将传输的表空间列表

 

 

USERID 用户名/口令
FULL 导入整个文件 (N)
BUFFER 数据缓冲区大小
FROMUSER 所有人用户名列表
FILE 输入文件 (EXPDAT.DMP)
TOUSER 用户名列表
SHOW 只列出文件内容 (N)
TABLES 表名列表
IGNORE 忽略创建错误 (N)
RECORDLENGTH IO 记录的长度
GRANTS 导入权限 (Y)
INCTYPE 增量导入类型
INDEXES 导入索引 (Y)
COMMIT 提交数组插入 (N)
ROWS 导入数据行 (Y)
PARFILE 参数文件名
LOG 屏幕输出的日志文件
CONSTRAINTS 导入限制 (Y)
DESTROY 覆盖表空间数据文件 (N)
INDEXFILE 将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)
ANALYZE 执行转储文件中的 ANALYZE 语句 (Y)
FEEDBACK 显示每 x 行 (0) 的进度
TOID_NOVALIDATE 跳过指定类型 id 的校验
FILESIZE 各转储文件的最大尺寸
RECALCULATE_STATISTICS 重新计算统计值 (N)

下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户

 

 

 

 

 

 

 

 

 

 

 

 

 

------------------------------查看用户权限--------------------------------------------------------------

1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
4.查看所有角色：
select * from dba_roles;
5.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle 9i基本SQL操作

---------重命名表------
Rename HR_BASE_CATEGORY To HR_TEMP_TABLE;

----------把一个表的数据拷到另一个相同结构的表
Insert Into HR_BASE_CATEGORY Select
 CATEGORY_ID,
 CATEGORY_NAME,
 CATEGORY_ALIAS,
 CATEGORY_DESC,
 MODULE_ID,
 MODULE_NAME,
 SORT_ORDER
From HR_TEMP_TABLE;

---------创建表----------

-- 创建班级表

Create table class
(
 class_id  varchar2(10) not null primary key, --主键
 class_name varchar2(10) not null,
 class_create date default sysdate,
 class_number number(2,0),
 class_remark  varchar2(50)
)

-- 删除表class

drop table class cascade constraint;

-- 创建学生表
create table student
(
 stu_id varchar2(10) not null primary key,
 stu_name varchar2(8),
 stu_gender integer default 0,
 stu_birthday date,
 stu_from varchar2(20),
 stu_tel varchar2(14),
 stu_dorm varchar2(8),
 stu_class_id varchar2(10)
)

--创建课程表
create table course
(
course_id varchar2(6) not null primary key,
course_name varchar2(16),
course_book varchar2(30)
)

drop table course cascade constraint;

--创建成绩表
create table score
(
score_stu_id varchar2(10) not null ,
score_course_id varchar2(6) not null,
score_pingshi  number(5,2),
score_final number(5,2),
score_Total number(5,2) --总成绩
)

--------修改表---------

alter table student add  email varchar2(30);         ------在学生表中增加字段：email地址；-------

alter table course modify course_book varchar2(36);  ------增加课程表字段"教科书"的长度；-------

alter table score modify score_Total default 0;    ------设置成绩表的总评成绩字段缺省值为0；-----

--------创建视图----------
如果你在创建视图时使用了DISTINCT语句，那么你就不能插入或更新这个视图中的记录
create view student_view(stu_name,course_name,score_info)
as
select student.STU_NAME , course.COURSE_NAME , score.score_Total
from student,score,course
where student.STU_ID = score.SCORE_STU_ID
    and course.COURSE_ID = score.SCORE_COURSE_ID;

--------创建索引----------

create index score_index on score(score_stu_id,score_course_id);

--------约束条件的创建-------

--------创建主键---------

--如果在表创建时已创建主键，可不再单独创建主键

alter table class add constraint pk_class_id primary key;

alter table student add constraint pk_stu_03 primary key;

alter table score add constraint pk_score_03 primary key;

----------创建外键---------

alter table student add constraint fk_class foreign key (stu_class_id) references class(class_id);

alter table score add constraint fk_stu_id foreign key(score_stu_id) references student(stu_id);

alter table score add constraint fk_course_id foreign key(score_course_id) references course(course_id);

----------创建check约束---------

alter table student add constraint ck_stu_gender check( stu_gender in (0,1) );

----------插入数据 和 事务控制语句------------

insert into class values(031,jsj,to_date(2003-09-01,yyyy-mm-dd,30,good);
insert into class values(032,xg1,to_date(2003-09-01,yyyymm-dd,26,good);

insert into student
values(001,xinhe,0,to_date(1977-09-22,yyyy-mm-dd),hunan,135,e208,031wxinhe2004@126.com);

insert into student values(002,huarong,0,to_date(1977-09-22,yyyy-mm-dd),hunan,135,e209,032,wxin004@126.com);

insert into course values(001,c++,jixietushu);
insert into course values(002,shujuku,qinghuatushu);

insert into score values(001,001,90.00,90.00,90.00);
insert into score values(002,001,80.00,80.00,80.00);

commit;

----------修改数据 和 事务控制语句------------

update course set coursename=cyuyan where coursename=c++;
update student set name=wangxinhe where name=xinhe;
update class   set appendxi=average where classid=032;
update score set totalscore=95.0 where totalscore=90.00;

commit;

----------修改数据 和 事务控制语句------------
delete course where coursename=c++;
rollback;

delete student;

delete score;

delete class;

commit;

------------多表连接查询语句-----------

1）查询学生名称、编号、性别、生日（按yyyy-mm-dd显示）、所在班级的名称，其中，0显示为“男”，1显示为“女”(decode函数)；

  select a.stu_name ,a.stu_id , decode(a.stu_gender,0,男,1,女) 性别,
       to_char(a.stu_birthday,yyyy-mm-dd) 出生日期, b.class_name
  from student a,class b
  where a.stu_class_id=b.class_id;

2）查询学生的各门功课的平时成绩、期末成绩和总评成绩；

   select a.stu_id,a.stu_name, b.course_name, c.score_pingshi 平时,c.score_final 期末,c.score_Total 总成绩
   from student a,course b, score c
   where  a.stu_id = c.score_stu_id
       and b.course_id = c.score_course_id

3）查询姓名XX所在班级所有学生信息(嵌套查询)；

   select * from student where stu_class_id =
       (select stu_class_id from student where stu_name=xinhe);

4）查询课程编号为“xxx”和“xxx”的学生成绩信息(集合查询)；

   select stu_id,score_Total  from score  where score_course_id = 001 
   union
   select stu_id,score_Total  from score  where score_course_id = 002;

5） 查询各班每门功课的最好成绩；
   
   select A.stu_class_id, B.score_course_id, max(score_Total)
   from student A,score B
   where A.stu_id=B.score_stu_id
   group by A.stu_class_id, B.score_course_id