Oracle使用笔记（二）

一、表空间

1、创建表空间

create  tablespace db_test --表空间名
datafile 'D:oracleproduct11.2.0dbhome_1oradataorcl	est.dbf' --物理文件 表空间数据文件存放路径
size 50m  --大小初始值
autoextend on  --自动扩展
next 50m maxsize 20480m  --每次扩展50m，最大为20480m（不能超过32G）
extent management local;

2、表空间扩容

（1）.先查询表空间在物理磁盘上存放的位置

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files where tablespace_name='USERS' ORDER BY tablespace_name ; 

查询所有表空间存放的物理路径（以sys的用户身份登录）：

select name from v$datafile;

（2）改变数据文件的大小

alter database datafile '/oracle/app/oradata/mytablespace/my_01.dbf' resize 256M;

①方法一：手动改变已存在数据文件的大小

ALTER TABLESPACE app_data ADD DATAFILE
'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' SIZE 20480M;

②方法二：允许已存在的数据文件自动增长

ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 32767M; 

（3）验证

select bytes/1024/1024, tablespace_name from dba_data_files where tablespace_name='USERS';

当数据库表空间A已经到达最大值时32G时，可以新建表空间B接上，使得表空间A满时进入表空间B：

新建表空间SDE_TBS_2：
create  tablespace SDE_TBS_2
datafile 'C:APPADMINISTRATORPRODUCT11.2.0DBHOME_1DATABASESDE_TBS_2.dbf'
size 50m  
autoextend on  
next 50m maxsize 32767m  
extent management local;

表空间SDE_TBS_2接上SDE_TBS：
alter tablespace SDE_TBS  
add datafile 'C:APPADMINISTRATORPRODUCT11.2.0DBHOME_1DATABASESDE_TBS_2.dbf' size 50m 
autoextend on next 50m maxsize 32767m;

二、用户

1、创建用户——create user 用户名 identified by 口令[即密码]；

create user tgb identified by test;

创建用户并关联表空间 ——  create user 用户名 identified by 密码 default tablespace 表空间表;

create user study identified by study default tablespace data_test;

2、更改用户—— alter user 用户名 identified by 口令[改变的口令];

alter user tgb identified by 123456;

3、删除用户—— drop user 用户名;

drop user tgb;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。—— drop user 用户名 cascade;

drop user tgb cascade;

4、用户角色

一般提供三种标准角色（role）:connect/resource和dba.

①connect role(连接角色)

②resource role(资源角色)

③dba role(数据库管理员角色)

（1）授予用户角色—— grant connect, resource to 用户名;

grant connect, resource to tgb;   ————授予用户连接角色和资源角色

（2）删除用户角色——  revoke connect, resource from 用户名;

revoke connect, resource from tgb;    ————删除用户连接角色和资源角色

（3）创建自定义角色——  revoke connect, resource from 用户名;

①创建角色——  create role 角色名;

create role testRole;

②删除角色——  drop role 角色名;

drop role testRole;

③自定义角色授权—— grant select on class to 角色名;

grant select on class to testRole;

 三、表

1、创建表

create table 表名(       

字段名1  字段类型(长度)   是否为空,        

字段名2  字段类型           是否为空 );

创建学生表：

create table t_stu(  
  stuid      number(10)   primary key,  
  stuname    varchar2(20) not null,  
  stusex     varchar2(2)  default '男' check(stusex in('男','女'))
);

创建成绩表：

create table t_score(  
  scoreid    number(10) primary key,  
  stuid      number(10) references t_stu(stuid),  
  couseid    number(10),  
  constraint fk_couseid foreign key(couseid)  
  references t_couse(couseid)  
  on delete cascade
); 

创建表及约束：

create table INFOS(

 STUID            varchar2(7) not null,               --学号 学号=‘S’+班号+2位序号

 STUNAME          varchar2(10) not null,              --姓名

 GENDER           varchar2(2) not null,               --性别 

 AGE              number(2) not null,                 --年龄

 SEAT             number(2) not null,                 --座号

 ENROLLDATE       date,                               --入学时间

 STUADDRESS       varchar2(50) default '地址不详',     --住址

 CLASSNO          varchar2(4) not null,               --班号 班号=学期序号+班级序号 

);

/  ①

alter table INFOS add constraint pk_INFOS primary key(STUID)  ②

/

alter table INFOS add constraint ck_INFOS_gender check(GENDER = '男' or GENDER = '女')  ③

/

alter table INFOS add constraint ck_INFOS_SEAT check(SEAT >=0 and SEAT <=50)  ④

/

alter table INFOS add constraint ck_INFOS_AGE check(AGE >=0 and AGE<=100)  ⑤

/

alter table INFOS add constraint ck_INFOS_CLASSNO check((CLASSNO >='1001' and CLASSNO<='1999') or

(CLASSNO >='2001' and CLASSNO<='2999'))  ⑥

/

alter table INFOS add constraint un_STUNAME unique(STUNAME)  ⑦

/

代码解析：

① 在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

② 创建一个主键约束。

③ 与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

 例子：

--建表
--student表+注释
create table student(
       sno   varchar2(3) not null,
       sname varchar2(9) not null,
       ssex  varchar2(3) not null,
       sbirthday date,
       sclass varchar2(5),
       constraint pk_student primary key(sno)
);
添加表注释：
COMMENT ON table student IS '学生信息表';
添加字段注释：
comment on column student.sno is '学号（主键）';
comment on column student.sname is '学生姓名';
comment on column student.ssex is '学生性别';
comment on column student.sbirthday is '学生出生年月日';
comment on column student.sclass is '学生所在班级';
--course表+注释
create table course(
       cno       varchar2(5) not null,
       cname     varchar2(15) not null,
       tno       varchar2(3) not null,
       constraint pk_course primary key(cno)
);
comment on column course.cno is '课程编号（主键）';
comment on column course.cname is '课程名称';
comment on column course.tno is '教工编号（外键）';
--score表+注释
create table score(
        sno   varchar2(3) not null,
        cno   varchar2(5) not null,
        degree   number(4,1),
        constraint pk_score primary key(sno,cno)
);
comment on column score.sno is '学号（主键）';
comment on column score.cno is '课程编号（主键）';
comment on column score.degree is '成绩';
--teacher表+注释
create table teacher(
       tno   varchar2(3) not null,
       tname varchar2(9) not null,
       tsex  varchar2(3) not null,
       tbirthday date,
       prof  varchar2(9),
       depart varchar2(15) not null,
       constraint pk_teacher primary key(tno)
);
comment on column teacher.tno is '教工编号（主键）';
comment on column teacher.tname is '教工姓名';
comment on column teacher.tsex is '教工性别';
comment on column teacher.tbirthday is '教工出生年月';
comment on column teacher.prof is '职称';
comment on column teacher.depart is '教工所在单位';
--添加外键
alter table course add constraint fk_tno foreign key(tno) references teacher(tno);
alter table score add constraint fk_sno foreign key(sno) references student(sno);
alter table score add constraint fk_cno foreign key(cno) references course(cno); 
--添加数据
--Student表
insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'曾华','男',to_date('1977-09-01','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'匡明','男',to_date('1975-10-02','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(107,'王丽','女',to_date('1976-01-23','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(101,'李军','男',to_date('1976-02-20','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(109,'王芳','女',to_date('1975-02-10','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(103,'陆君','男',to_date('1974-06-03','yyyy-mm-dd'),95031);
--teacher表
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(804,'李诚','男',to_date('1958/12/02','yyyy-mm-dd'),'副教授','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(856,'张旭','男',to_date('1969/03/12','yyyy-mm-dd'),'讲师','电子工程系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(825,'王萍','女',to_date('1972/05/05','yyyy-mm-dd'),'助教','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(831,'刘冰','女',to_date('1977/08/14','yyyy-mm-dd'),'助教','电子工程系');
--course表(添加外键后要先填teacher表中数据去满足外键约束)
insert into course(cno,cname,tno) values('3-105','计算机导论',825);
insert into course(cno,cname,tno) values('3-245','操作系统',804);
insert into course(cno,cname,tno) values('6-166','数字电路',856);
insert into course(cno,cname,tno) values('9-888','高等数学',831);
--score表（添加外键后要先填Student，course表中数据去满足外键约束）
insert into score(sno,cno,degree) values(103,'3-245',86);
insert into score(sno,cno,degree) values(105,'3-245',75);
insert into score(sno,cno,degree) values(109,'3-245',68);
insert into score(sno,cno,degree) values(103,'3-105',92);
insert into score(sno,cno,degree) values(105,'3-105',88);
insert into score(sno,cno,degree) values(109,'3-105',76);
insert into score(sno,cno,degree) values(101,'3-105',64);
insert into score(sno,cno,degree) values(107,'3-105',91);
insert into score(sno,cno,degree) values(108,'3-105',78);
insert into score(sno,cno,degree) values(101,'6-166',85);
insert into score(sno,cno,degree) values(107,'6-166',79);
insert into score(sno,cno,degree) values(108,'6-166',81);

2、创建主键与外键：

-增加主键     

alter table 表名 add constraint 主键名 primary key (字段名1);

-增加外键:     

alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);

在建立表格时就指定主键和外键：

create table T_SCORE(

 EXAM_SCORE             number(5,2), 

 EXAM_DATE              date,

 AUTOID                 number(10) not null,

 STU_ID                 char(5),

 SUB_ID                 char(3),

 constraint PK_T_SCORE primary key (AUTOID),

 constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID));

3、查询某表是否存在

select count(*) from user_tables where table_name =upper('表名')   //表名是区分大小写的，一定要与upper结合使用

其中：

dba_tables : 系统里所有的表的信息，需要DBA权限才能查询
all_tables : 当前用户有权限的表的信息（只要对某个表有任何权限，即可在此视图中看到表的相关信息）
user_tables: 当前用户名下的表的信息

所以以上3个视图中，user_tables的范围最小，all_tables看到的东西稍多一些，而dba_tables看到最多的信息

四、视图

视图是通过表生成的，当视图来源表中数据发生变化时，视图中的数据也会相应改变。

CREATE OR REPLACE VIEW 视图名称 查询条件 

CREATE OR REPLACE VIEW public.a_rk AS 
 SELECT xzqhlist.ms,
    xzqhlist.qx,
    xzqhlist.sm,
    xzqhlist.gc,
    xzqhlist.al,
    COALESCE(xzqh_hs.hs, 0::bigint) AS xzqh_hs,
    COALESCE(xzqh_rks.rks, 0::bigint) AS xzqh_rks,
    COALESCE(xzqh_rks_nan.rks_nan, 0::bigint) AS xzqh_rks_nan,
    COALESCE(xzqh_rks_nv.rks_nv, 0::bigint) AS xzqh_rks_nv
   FROM xzqhlist
     LEFT JOIN xzqh_hs ON xzqhlist.al = xzqh_hs.al
     LEFT JOIN xzqh_rks ON xzqhlist.al = xzqh_rks.al
     LEFT JOIN xzqh_rks_nan ON xzqhlist.al = xzqh_rks_nan.al
     LEFT JOIN xzqh_rks_nv ON xzqhlist.al = xzqh_rks_nv.al
  ORDER BY xzqhlist.al;