Oracle对象的创建与管理

Author Avatar
Euan 9月 29, 2019
  • 在其它设备中阅读本文章

Oracle对象的创建与管理

一、实验目的

1掌握Oracle数据库表的创建与管理；

2掌握Oracle数据库索引的创建与管理；

二、实验要求（具体步骤进行截屏展示并配以文字说明）

1完成表管理操作；包括表的创建，修改表；查询表信息；删除表。

（1）创建新的表空间MYTABLESAPCE，MYINDX创建以下表存于MYTABLESAPCE。

REGIONS

字段名 数据类型 长度 约束 说明
region_id NUMBER PRIMARY KEY 区域编号 NUMBER PRIMARY KEY 区域编号
region_name VARCHAR2 25 NOT NULL 区域名称

COUNTRIES

字段名 数据类型 长度 约束 说明
country_id CHAR 2 PRIMAR KEY 国家编号
country_name VARCHAR2 40 NOT NULL 国家名称
region_id NUMBER FOREIGN KEY 所属区域编号

LOCATIONS

字段名 数据类型 长度 约束 说明
location_id NUMBER 4 PRIMAR KEY 位置编号
street_address VARCHAR2 40 街道
postal_code VARCHAR2 12 邮编
city VARCHAR2 30 NOT NULL 城市名称
state_province VARCHAR2 25 州或省名称
country_id CHAR 2 FOREIGN KEY 所属国家编号

DEPARTMENTS

字段名 数据类型 长度 约束 说明
department_id NUMBER 4 PRIMAR KEY 部门编号
department_name VARCHAR2 30 NOT NULL 部门名称
manager_id NUMBER 6 部门管理者工号
location_id NUMBER 4 FOREIGN KEY 位置编号

JOBS

字段名 数据类型 长度 约束 说明
job_id VARCHAR2 10 PRIMAR KEY 职位编号
job_title VARCHAR2 35 NOT NULL 职位名称
min_salary NUMBER 6 职位最低工资
max_salary NUMBER 6 职位最高工资

EMPLOYEES

字段名 数据类型 长度 约束 说明
employee_id NUMBER 6 PRIMARY KEY
first_name VARCHAR2 20
last_name VARCHAR2 25 NOT NULL
email VARCHAR2 25 NOT NULL
phone_number VARCHAR2 20
hire_date DATE NOT NULL
job_id VARCHAR2 10 NOT NULL,FOREIGN KEY
salary NUMBER 8,2 大于0
commission_pct NUMBER 2,2
manager_id NUMBER 6 FOREIGEN KEY
department_id NUMBER 4 FOREIGN KEY

JOB_HISTORY

字段名 数据类型 长度 约束 说明
employee_id NUMBER 6 PRIMARY KEY
start_date DATE PRIMARY KEY
end_date DATE NOT NULL, end_date>start_date
job_id VARCHAR2 10
department_id NUMBER 4 FOREIGN KEY

SAL_GRADES

字段名 数据类型 长度 约束 说明
grade NUMBER 2 PRIMAR KEY 工资等级
min_salary NUMBER 6 NOT NULL 职位最低工资
max_salary NUMBER 6 NOT NULL 职位最高工资

（2）为DEPARTMENTS添加一个列headcount，数据类型为number,可以为空；为EMPLOYEES添加一个列gender数据类型为char(1)，约束是该列的取值只能是M或者F; 通过查询相关视图检验操作是否成功。

（3）为EMPLOYEES表的email列添加唯一性约束（UNIQUE），通过查询相关视图检验操作是否成功。

（4）在EMPLOYEES表的（first_name,last_name）上创建一个B树索引，要求索引值为大写字母并存于MYINDX表空间；在gender上创建位图索引。通过查询相关视图检验操作是否成功。

三、实验步骤和结果

1完成表管理操作；包括表的创建，修改表；查询表信息；删除表。

（1）创建新的表空间MYTABLESAPCE，MYINDX创建以下表存于MYTABLESAPCE。

1）首先查询表空间信息，输入

SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, CONTENTS FROM DBA_TABLESPACES;

出现

KTOWlT.png

2）找出打开数据库的时候报错的地方

SQL> select open_mode from v$database;

KTOgf0.png

3）抛出的错误：

SQL> alter database open;

KTORpV.png

后分析为上次实验二的残留文件还未删除

4）删除残留文件

SQL> alter database datafile ‘C:\USERS\901\DESKTOP\DATAFILE2.DBF’ offline drop;

KTOcYq.png

5）删除完查看状态

SQL> alter database open;

KTO6kn.png

6）查看表空间信息成功

SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, CONTENTS FROM DBA_TABLESPACES;

KTOf6U.png

7）创建MYTABLESAPCE表空间

SQL> CREATE TABLESPACE MYTABLESAPCE DATAFILE ‘D:\Test\MYTABLESAPCE.DBF’ SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT MANUAL;

KTOhXF.png

8）创建MYINDX表空间

SQL> CREATE TABLESPACE MYINDX DATAFILE ‘D:\Test\MYINDX.DBF’ SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT MANUAL;

KTO5m4.png

9）创建REGIONS表

1
2
3
4
SQL> create table REGIONS(
           region_id  NUMBER primary key,
           region_name VARCHAR(25) not null
           );

number加括号不加参数会显示错误

KTOI0J.png

加括号后成功

KTOo79.png

number默认参数为p（精度）为38，小数点位数的合法值为-84~127

到第10步发现把VARCHAR2看成VARCHAR类型，并查找VARCHAR和VARCHAR2的区别，后删除表

SQL> DROP TABLE DEPARTMENTS;

SQL> DROP TABLE COUNTRIES;

KTO7kR.png

重新创建

1
2
3
4
SQL> create table REGIONS(
region_id  NUMBER primary key,
region_name VARCHAR2(25) not null
);

KTOHt1.png

10）创建COUNTRIES表

1
2
3
4
5
SQL> create table COUNTRIES(
country_id CHAR(2) primary key,
country_name VARCHAR2(40) not null，
region_id number references REGIONS (region_id )
);

到注释添加时失败回过头来看发现字段名称打错

KTObfx.png

修改表字段名称

SQL> alter table COUNTRIES rename column classid to region_id;

KTOLp6.png

11）创建LOCATIONS表

1
2
3
4
5
6
7
8
SQL> create table LOCATIONS (
location_id NUMBER(4) primary key,
street_address VARCHAR2(40)，
postal_code VARCHAR2(12),
city VARCHAR2(30) not null,
state_province VARCHAR2(25),
country_id CHAR references COUNTRIES (country_id)
);

KTOO1K.png

12）创建DEPARTMENTS表

1
2
3
4
5
6
SQL> create table DEPARTMENTS (
department_id NUMBER(4) primary key ,
department_name VARCHAR2(30) not null，
manager_id NUMBER(6),
location_id NUMBER(4) references LOCATIONS (location_id)
);

KTOX6O.png

13）创建JOBS表

1
2
3
4
5
SQL> create table JOBS (
job_id VARCHAR2(10) primary key ,
job_title VARCHAR2(35) not null，
min_salary NUMBER(6),max_salary NUMBER(6)
);

KTOjXD.png

14）创建EMPLOYEES表

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create table EMPLOYEES (
employee_id  NUMBER(6) primary key,
first_name VARCHAR2(20),
last_name VARCHAR2(25) not null,
email VARCHAR2(20) not null,
phone_number VARCHAR2(20) ,
 hire_date DATE not null ,
job_id VARCHAR2(10) not null references JOBS (job_id),
salary NUMBER(8,2) CHECK (salary > 0),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4) references DEPARTMENTS (department_id)
);

KTOxne.png

于是查看DEPARTMENTS表中查看manage_id字段，他并不是主键，所以不能使用外键来进行约束，可能老师您题目出错了。
去掉manage_id的外键约束

KTOz0H.png

成功。

15）创建JOB_HISTORY表

1
2
3
4
5
6
7
8
9
SQL> create table JOB_HISTORY (
employee_id  NUMBER(6) not null references employees(employee_id), 
start_date DATE not null,
end_date DATE not null,
job_id VARCHAR2(10),
department_id NUMBER(4) references DEPARTMENTS (department_id),
CONSTRAINT jhist_date_interval CHECK (end_date > start_date),
CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date)
);

一开始按自己思路写，后显示错误，意识到表里不能有两个主键

KTXS7d.png

修改为课本案例，修改后的CONSTRAINT的意思为添加约束，成功

KTX9AA.png

16）创建SAL_GRADES表

1
2
3
4
5
SQL> create table SAL_GRADES (
grade  NUMBER(2) PRIMARY KEY,
min_salary NUMBER(6) not null,
max_salary NUMBER(6) not null
);

KTXCtI.png

17）给所有表中的字段添加注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> COMMENT ON COLUMN REGIONS.region_id IS '区域编号';
SQL> COMMENT ON COLUMN REGIONS.region_name IS '区域名称';
SQL> COMMENT ON COLUMN COUNTRIES.country_id IS '国家编号';
SQL> COMMENT ON COLUMN COUNTRIES.country_name IS '国家名称';
SQL> COMMENT ON COLUMN COUNTRIES.region_id IS '所属区域编号';
SQL> COMMENT ON COLUMN LOCATIONS.location_id IS '位置编号';
SQL> COMMENT ON COLUMN LOCATIONS.street_address IS '街道';
SQL> COMMENT ON COLUMN LOCATIONS.postal_code IS '邮编';
SQL> COMMENT ON COLUMN LOCATIONS.city IS '城市名称';
SQL> COMMENT ON COLUMN LOCATIONS.state_province IS '州或省名称';
SQL> COMMENT ON COLUMN LOCATIONS.country_id IS '所属国家编号';
SQL> COMMENT ON COLUMN DEPARTMENTS.department_id IS '部门编号';
SQL> COMMENT ON COLUMN DEPARTMENTS.department_name IS '部门名称';
SQL> COMMENT ON COLUMN DEPARTMENTS.manager_id IS '部门管理者工号';
SQL> COMMENT ON COLUMN DEPARTMENTS.location_id IS '位置编号';
SQL> COMMENT ON COLUMN JOBS.job_id IS '职位编号';
SQL> COMMENT ON COLUMN JOBS.job_title IS '职位名称';
SQL> COMMENT ON COLUMN JOBS.min_salary IS '职位最低工资';
SQL> COMMENT ON COLUMN JOBS.max_salary IS '职位最高工资';
SQL> COMMENT ON COLUMN SAL_GRADES.grade IS '工资等级';
SQL> COMMENT ON COLUMN SAL_GRADES.min_salary IS '职位最低工资';
SQL> COMMENT ON COLUMN SAL_GRADES.max_salary IS '职位最高工资';

KTXPht.png

KTXF9P.png

一开始出现3处备注错误

修改后成功

K73W8A.png

K73wg1.png

K730jx.png

（2）为DEPARTMENTS添加一个列headcount，数据类型为number,可以为空；为EMPLOYEES添加一个列gender数据类型为char(1)，约束是该列的取值只能是M或者F; 通过查询相关视图检验操作是否成功。

1）添加列headcount

SQL> ALTER TABLE DEPARTMENTS

ADD(headcount number);

K73d3R.png

2）添加列gender

SQL> ALTER TABLE EMPLOYEES

ADD(gender char(1));

K73aC9.png

3)添加gender检查约束

SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT gender CHECK (gender in (‘F’,’M’));

K73Du6.png

4)查询相关视图

SQL> select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘DEPARTMENTS’;

K73rDK.png

SQL> select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘EMPLOYEES’;

K73sHO.png

select table_name,constraint_name,constraint_type from user_constraints where table_name=’EMPLOYEES’;

K736ED.png

（3）为EMPLOYEES表的email列添加唯一性约束（UNIQUE），通过查询相关视图检验操作是否成功。

1)添加约束

SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT E_UK UNIQUE(email);

K73cUe.png

2)查询检验

SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name=’EMPLOYEES’;

K73hvt.png

（4）在EMPLOYEES表的（first_name,last_name）上创建一个B树索引，要求索引值为大写字母并存于MYINDX表空间；在gender上创建位图索引。通过查询相关视图检验操作是否成功。

1）前提准备

之前知道什么是索引，但是不知道B树索引，于是寻找相关概念

K78YsP.png

2）创建B树索引

不知道索引值的概念，网上也查不到，我觉得老师您的意思应该是索引名吧

SQL> CREATE INDEX B ON EMPLOYEES (first_name,last_name) TABLESPACE MYINDX;

K73g4H.png

3）创建位图索引

SQL> CREATE BITMAP INDEX emp_gender_index ON EMPLOYEES (gender) TABLESPACE MYINDX;

K73RCd.png

4)查询检验

1
2
3
4
5
6
SELECT
b.uniqueness, a.index_name, a.table_name, a.column_name
FROM all_ind_columns a, all_indexes b
WHERE a.index_name=b.index_name
AND a.table_name = upper('EMPLOYEES')
ORDER BY a.table_name, a.index_name, a.column_position;

一开始复制题目的EMPLOYEE结果总是出现

K78JMt.png

查询网上发现大部分情况为表为空，明明创了表怎么可能为空

结果发现之前表明是EMPLOYEES，题目少了S

修改语句中的表明

K735KP.png

成功

本文使用 CC BY-NC-SA 3.0 中国大陆 协议许可
具体请参见 知识共享协议
本文链接：https://zyhang8.github.io/2019/09/29/oracle-exp2/