Oracle对象的创建与管理
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 | |
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;
出现
2)找出打开数据库的时候报错的地方
SQL> select open_mode from v$database;
3)抛出的错误:
SQL> alter database open;
后分析为上次实验二的残留文件还未删除
4)删除残留文件
SQL> alter database datafile ‘C:\USERS\901\DESKTOP\DATAFILE2.DBF’ offline drop;
5)删除完查看状态
SQL> alter database open;
6)查看表空间信息成功
SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, CONTENTS FROM DBA_TABLESPACES;
7)创建MYTABLESAPCE表空间
SQL> CREATE TABLESPACE MYTABLESAPCE DATAFILE ‘D:\Test\MYTABLESAPCE.DBF’ SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT MANUAL;
8)创建MYINDX表空间
SQL> CREATE TABLESPACE MYINDX DATAFILE ‘D:\Test\MYINDX.DBF’ SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT MANUAL;
9)创建REGIONS表
1 | SQL> create table REGIONS( |
number加括号不加参数会显示错误
加括号后成功
number默认参数为p(精度)为38,小数点位数的合法值为-84~127
到第10步发现把VARCHAR2看成VARCHAR类型,并查找VARCHAR和VARCHAR2的区别,后删除表
SQL> DROP TABLE DEPARTMENTS;
SQL> DROP TABLE COUNTRIES;
重新创建
1 | SQL> create table REGIONS( |
10)创建COUNTRIES表
1 | SQL> create table COUNTRIES( |
到注释添加时失败回过头来看发现字段名称打错
修改表字段名称
SQL> alter table COUNTRIES rename column classid to region_id;
11)创建LOCATIONS表
1 | SQL> create table LOCATIONS ( |
12)创建DEPARTMENTS表
1 | SQL> create table DEPARTMENTS ( |
13)创建JOBS表
1 | SQL> create table JOBS ( |
14)创建EMPLOYEES表
1 | SQL> create table EMPLOYEES ( |
于是查看DEPARTMENTS表中查看manage_id字段,他并不是主键,所以不能使用外键来进行约束,可能老师您题目出错了。
去掉manage_id的外键约束
成功。
15)创建JOB_HISTORY表
1 | SQL> create table JOB_HISTORY ( |
一开始按自己思路写,后显示错误,意识到表里不能有两个主键
修改为课本案例,修改后的CONSTRAINT的意思为添加约束,成功
16)创建SAL_GRADES表
1 | SQL> create table SAL_GRADES ( |
17)给所有表中的字段添加注释
1 | SQL> COMMENT ON COLUMN REGIONS.region_id IS '区域编号'; |
一开始出现3处备注错误
修改后成功
(2)为DEPARTMENTS添加一个列headcount,数据类型为number,可以为空;为EMPLOYEES添加一个列gender数据类型为char(1),约束是该列的取值只能是M或者F; 通过查询相关视图检验操作是否成功。
1)添加列headcount
SQL> ALTER TABLE DEPARTMENTS
ADD(headcount number);
2)添加列gender
SQL> ALTER TABLE EMPLOYEES
ADD(gender char(1));
3)添加gender检查约束
SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT gender CHECK (gender in (‘F’,’M’));
4)查询相关视图
SQL> select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘DEPARTMENTS’;
SQL> select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘EMPLOYEES’;
select table_name,constraint_name,constraint_type from user_constraints where table_name=’EMPLOYEES’;
(3)为EMPLOYEES表的email列添加唯一性约束(UNIQUE),通过查询相关视图检验操作是否成功。
1)添加约束
SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT E_UK UNIQUE(email);
2)查询检验
SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name=’EMPLOYEES’;
(4)在EMPLOYEES表的(first_name,last_name)上创建一个B树索引,要求索引值为大写字母并存于MYINDX表空间;在gender上创建位图索引。通过查询相关视图检验操作是否成功。
1)前提准备
之前知道什么是索引,但是不知道B树索引,于是寻找相关概念
2)创建B树索引
不知道索引值的概念,网上也查不到,我觉得老师您的意思应该是索引名吧
SQL> CREATE INDEX B ON EMPLOYEES (first_name,last_name) TABLESPACE MYINDX;
3)创建位图索引
SQL> CREATE BITMAP INDEX emp_gender_index ON EMPLOYEES (gender) TABLESPACE MYINDX;
4)查询检验
1 | SELECT |
一开始复制题目的EMPLOYEE结果总是出现
查询网上发现大部分情况为表为空,明明创了表怎么可能为空
结果发现之前表明是EMPLOYEES,题目少了S
修改语句中的表明
成功
本文使用 CC BY-NC-SA 3.0 中国大陆 协议许可
具体请参见 知识共享协议
本文链接:https://zyhang8.github.io/2019/09/29/oracle-exp2/