Oracle使用笔记（二）

2022-10-22 11:16:06

一、表空间

1、创建表空间

create  tablespace db_test --表空间名

datafile 'D:\oracle\product\11.2.0\dbhome_1\oradata\orcl\test.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:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 20480M;

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

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.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:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SDE_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:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SDE_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 >='' and CLASSNO<='') or

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

/

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;

码农公寓