实验7 管理表、索引和其他模式对象_V2013

实验7 管理表、索引和其他模式对象

姓名： 计算机科学与技术 专业： 同组人：

无

学班

号级

： ：

实验日期：

【实验目的与要求】

掌握ORACLE数据表进行用户授权，取消权限等相关数据控制命令； 掌握对数据库表结构修改的方法：

变更表和约束 删除表和约束

【实验内容与步骤】

思考问题：如何使用SQL来创建数据库表？

7.1 准备工作：创建测试用表。

实验7-1-1 以scott/tiger 用户登录系统，给出创建如下的Customer表（客户表）的 SQL 语句，并运行创建实验用表。

Customer表

SQL> create table Customer(Cno varchar(5) primary key,Cname varchar(20),Company

varchar(30),City varchar(20),Tel varchar(15));

给出运行结果截图：

在Customer表中插入如下的记录：

insert into Customer values('C0006','Zhang Qing','Freightliner

LLC','Guangzhou','020-84713425');

insert into Customer values('C0007','Yang Jie','Freightliner LLC','Guangzhou','020-76543657');

insert into Customer values('C0008','Wang Peng','IBM','Beijing','010-62751231'); insert into Customer values('C0009','Du Wei','Honey Well','Shanghai','021-45326788');

insert into Customer values('C0010','Shan Feng','Oracle','Beijing','010-62751230');

实验7-2-1 给出建立订单表Orders的 SQL 语句，其结构如下表所示。

Orders表

请给出相应的语句： create table orders (ono char(5), order_date date, cno char(5), freight int,

shipment_date date, city char(20),

payment_tno char(1), status char(20), primary key(ono)); 运行结果截图：

在“Orders”表中插入下列值。

注意：(1)表中的日期需根据现有系统日期格式作出调整，建议用to_date( )函数，将指定格式的字符串转化为日期值；(2)日期值可根据实际情况，修改为较近时间；(3)建议在Toad可视化界面下输数据，以节省时间。

insert into orders(ono,order_date,cno,freight,shipment_date,city,payment_tno,status) values('O0001','2005-10-7','C0001',8,'2005-11-7','Beijing','1','Complete'); 实验7-3-3 创建Product表，使之可容纳如下数据（见下表）：

请给出相应的语句： create table product

(pno char(4) primary key, pname varchar(50), price varchar(20), tno char(20));

insert into product(pno,pname,price,tno) values('&pno','&pname','&price','&tno'); 运行结果截图：

7.2 修改表结构

SQL用ALTER TABLE语句来修改表结构。 1．添加新列

如果要向Customer表中存储其地址信息，就需要在Customer表中添加列。

语法如下：

Alter table <table_name> add (new_column_name datatype(size),…….);

实验7-2-1 给出如下所示的语句，在Customer表内添加地址（Address）字段，类型为Varchar2，大小为40。 给出相应语句与运行结果：

Alter table Customer add(ADDRESS varchar(40));

2．修改现有列

修改表中现有列的语法如下：

Alter table <tablename>

modify(existing_column_name datatype(size),…….);

实验7-2-2 将客户表中Address的数据类型改为长度为30的字符型。

给出相应语句与运行结果：

Alter table Customer modify(ADDRESS varchar(30));

练习7-1：

把下列操作的结果记录下来：

(1) 将Customer表城市列的大小增加到25。 给出相应的SQL语句和运行结果:

Alter table Customer modify(CITY char(25));

(2) 将Customer表电话列的大小减到 12。 给出相应的SQL语句和运行结果:

Alter table Customer modify(TEL char(12));

注意：电话列的宽度不能减小，因为“cannot decrease column length because some value is too big”。

3．删除列

删除表中列的语法如下：

Alter table <tablename>

Drop(existing_column_name);

实验7-2-3 删除 Customer表的Address 列。 给出相应语句与运行结果：

Alter table Customer Drop(ADDRESS);

7.3 创建对表的约束

约束允许定义向表中输入数据时必须遵循的某些验证或限制。 主键: 用来指定记录的唯一性。

CHECK约束: 用来将列限制为包含特定值或值的范围，即有条件输入详细信息。 非空值约束: 将不允许用户将列保留为空的。 外键约束：用于定义参照完整性。

1．创建对新表的约束

可在两个级别定义约束: 列约束和表约束。

实验7-3-1 创建如下的OrderItem表：

给出运行结果：

create table orderitem ( ono char(5), pno char(4), qty int,

discount number(4,2),

foreign key(ono) references orders(ono),

foreign key(pno) references product(pno));

练习7-3-1

(1) 在OrderItem表中插入一条Qty值小于0的记录，观察执行结果； 给出相应的SQL语句和运行结果

:

(2) 在OrderItem表中插入一条记录，其Ono的值为“0001”，观察执行结果； 给出相应的SQL语句和运行结果

:

2．对现有表创建约束

也可以对现有表添加约束。语法如下：

Alter table <table_name>

add constraint <constraint_name> <constraint>;

在上面的语法中，约束可以是主键或校验或外键约束。

实验7-3-2 给Orders表添加名为“cn2”如下的约束：规定Freight属性列的值必须介于0和100之间。

alter table orders add constraint cn2 check(freight>0 and freight<100);

实验7-3-3 给Orders表添加名为“cn1”如下的约束：Cno属性列的值非空。 给出相应的SQL语句和运行结果:

alter table orders add constraint cn1 check(cno <>null);

练习7-1-2

使用如下的CREATE TABLE语句创建Ewage表：

然后，在Ewage表上创建如下的约束： (1)定义Ewage表的主码是Eno; 给出相应的SQL语句和运行结果

:

(2) 规定奖金（Bonus）必须比基本工资（Salary）的3倍小；

3．完整性约束的删除 格式如下：

ALTER TABLE 〈tablename〉

DROP CONSTRAINT <constraint_name >;

实验7-3-4 删除前面定义的名为cn1和cn2的约束。 给出相应的SQL语句和运行结果: alter table orders drop constraint cn1;

alter table orders drop constraint cn2

;

7.4 复制表

使用如下语句可复制包含其结构和元组的整个表，语法如下：

CREATE TABLE new_tablename AS

<SELECT statement>

实验7-4-1 要将scott模式下的表emp复制为 emp_Bak，请给出相应的语句。 给出相应的SQL语句和运行结果: create table emp_bak as select * from emp;

练习：完成后，查询emp_Bak表中数据 请给出相应的语句和测试结果：

7.5 视图

视图是一个虚拟表，其内容是借助于查询从表中获取的。在这些表中所作的更改自动反映在视图中。语法如下：

CREATE VIEW viewname AS

SELECT <statement>；

注意：ORDER BY 不能与视图一起使用。

实验7-1 建立“上海”客户的视图，并取名“Customer_sh”。 请给出相应的语句和运行结果载图：

上面的查询创建一个名为“Customer_sh”的视图。创建视图之后，您可像查看任何表一样查看该视图。请给出如下所示的语句：

请给出运行结果载图：

实验7-2 基于“Orders”表，建立一个名为Order_Sh的包含所有上海客户订单信息的视图，要求在该视图中包括各客户的公司名称、订单代号和订购日期等属性列。

请给出相应的语句和运行结果载图：

实验7-3 删除名为 Customer_sh的视图。

请给出相应的语句和运行结果载图：

练习7-1：基于scott用户模式下的Emp表，建立一个名为Avg_sal的视图，用以统计各部门的平均公资，要求数据项包括“部门编号”和“平均工资”两列。

7.6 序列

序列用来生成可用作主键的唯一整数。语法如下：

CREATE SEQUENCE sequencename INCREMENT BY <n> START WITH <m>；

sequencename 是创建的序列的名称； <n> 是指定的递增数，默认值是 1； <m> 是序列的开始数。

实验7-4 创建名从3开始、步长为1、名为“seqno”的序列。

请给出相应的语句和运行结果载图：

上面的查询创建名为“seqno”的序列，我们也可以在创建序列之后插入值。格式如下： INSERT INTO tablename(sequence column number, columnnames) VALUES (sequence name.NEXTVAL, values)； Sequence column number 是您生成序列编号的列名称 Column names 是表的其他列。

实验7-5 假定数据库中有一个名为new_ptype的表（若无该表，请创建之），其结构和数据如下图所示：

请给执行下所示的语句：

完成后，查询New_Ptype表中数据。

请给出相应的语句和运行结果载图：

思考：对比前后两次查询数据的结果，理解序列的使用。

实验7-6 删除创建前面创建的名为seqno的序列。 请给出相应的语句和运行结果载图：

7.7 同义词

同义词是 Oracle 对象的别名。此对象可以是表、视图、程序、函数或另一个同义词。同义词不是实际对象，而是对对象的参考。同义词非常有用，这是因为它们隐藏参考的对象的身份。在重命名对象或修改对象的情况下，这十分有用，因为这样就只需要重新定义同义词。这有助于缩短在项目中所花费的重新编译和修改时间。

创建同义词的语法如下：

CREATE SYNONYM synonymname FOR tablename

实验7-7 创建一个名为“new”（新）的同义词，该同义词参考Customer表。请给出如下所示的语句：

上面的查询中创建的同义词可通过给出下列语句进行查看。 SELECT * from new;

请给出运行结果载图：

要删除上面创建的同义词。

请给出相应的语句和运行结果载图：

7.8 索引的创建与删除

1．索引的创建：

索引有助于更快地进入表中的列。索引还可以避免输入到列中的值产生重复现象。 语法如下：

CREATE INDEX indexname ON tablename(columnname)

可以为多个列创建索引。 这样的索引称为“Composite Indexes”（复合索引）。

实验 创建一个名为 idx 的、关于Customer表City字段的索引。

请给出相应的语句和运行结果载图：

练习：在Orders表中的Order_date列上创建一个名为 idx 索引，观察会发生什么情

况。

实验 在Customer表中的 City 列和 Company 列上创建一个名为“comp”的索引。 请给出相应的语句和运行结果载图：

2．索引的删除：

给出下列语句可删除索引。

DROP INDEX indexname

实验 删除创建的索引“idx”，请给出如下的语句：

3．基于函数的索引：

实验 基于函数的索引：执行下列语句，创建一个基于函数的索引，并删除之。

4．索引相关数据字典：

实验 使用索引相关数据字典：

(1) 对employees表创建索引于last_name字段：

(2) 查询索引信息：

给出查询的结果：

(3) 监视索引： a.打开索引监视状态：

alter index emp_last_name_idx monitoring usage; b. 监视索引emp_last_name_idx:

analyze index emp_last_name_idx validate structure;

Select br_rows,br_blks,lf_rows,del_lf_rows From index_stats

Where name=’ emp_last_name_idx’; 给出运行结果：

7.9 分区表的创建与使用

1.准备工作

创建数据存放表空间：（其中的NNN请用学号后三位代替）

create tablespace Ex07_NNN_space01 datafile 'C:\Ex07_NNN01.dnf' size 20M; create tablespace Ex07_NNN_space02 datafile 'C:\Ex07_NNN02.dnf' size 20M; create tablespace Ex07_NNN_space03 datafile

'C:\Ex07_NNN03.dnf'

size

20M;

2．范围分区与分区表的创建

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。

需求描述：

有一个物料交易表，表名：material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候可以使用序号分区三个区，每个区中预计存储三千万的数据，也可以使用日期分区，如每五年的数据存储在一个分区上。

根据交易日期分区建表：

create table Ex07_NNN_test (

transaction_id number primary key, item_id number(8) not null,

item_description varchar2(300),

transaction_date date not null )

partition by range (transaction_date) (

partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace Ex07_NNN_space01,

partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace Ex07_NNN_space02,

partition part_03 values less than(maxvalue) tablespace Ex07_NNN_space03 );

这样就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到制定的分区（表空间）中。

3. 分区表操作

以上完成了分区表的建表，下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。

(1)插入记录：

设定不同的日期时间，将数据插入到表中：

insert into Ex07_NNN_test values(1,12,'BOOKS',sysdate); insert into Ex07_NNN_test values(2,12, 'BOOKS',sysdate+30); insert insert insert insert commit;

into into into into

Ex07_NNN_test Ex07_NNN_test Ex07_NNN_test Ex07_NNN_test

values(3,12, values(4,12, values(5,12, values(6,12,

'BOOKS',to_date('2006-05-30','yyyy-mm-dd')); 'BOOKS',to_date('2007-06-23','yyyy-mm-dd')); 'BOOKS',to_date('2011-02-26','yyyy-mm-dd')); 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

按上面的建表结果，2006年前的数据将存储在第一个分区part_01上，而2006年到2010年的交易数据将存储在第二个分区part_02上，2010年以后的记录存储在第三个分区part_03上。

（2）查询分区表记录：

SQL> select * from Ex07_NNN_test partition(part_01);

请给出运行结果载图：

观察与比较插入数据行与查询结果，思考：为什么会有这样结果？

从查询的结果可以看出，插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询，当然也可以不指定分区，直接执行select * from Ex07_NNN_test查询全部记录。在也检索的数据量很大的时候，指定分区会大大提高检索速度。

(3)更新分区表的记录：

分别执行以下语句1和2，比较测试结果： 语句(1)：

语句

(2)

这里试图将ID=1的记录中的item_description字段更新为“DESK”，当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据。

第一个语句指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。

（4）删除分区表记录：

请删除了第二个分区part_02中的交易记录ID为4的一条记录。

请写出相应的语句并给出运行结果载图：

和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。

本文来源：https://www.bwwdw.com/article/6hdj.html