oracle体验实验,Oracle实验三

1、实验目的

(1)掌握表的创建与管理。

(2)掌握索引的创建与管理。

(3)掌握视图的创建与管理。

(4)掌握序列的创建与应用。

2、实验环境

Win10 以及Oracle 11g

3、实验要求

(1)为图书销售系统创建表。

(2)在图书销售系统适当表的适当列上创建适当类型的索引。

(3)为图书销售系统创建视图。

(4)为图书销售系统创建序列。

4、实验内容

(1)打开SQL Plus，以system用户登录BOOKSALES数据库。

(2)按下列方式创建一个用户bs，并给该用户授权。

(3)使用bs用户登录数据库，并进行下面的相关操作。

(4)根据图书销售系统关系模式设计，创建表1至表6。(见P299-P300)

(5)在CUSTOMERS表的name列上创建一个B-树索引，要求索引值为大写字母。

(6)在BOOKS表的title列上创建一个非唯一性索引。

(7)在ORDERitem表的ISBN列上创建一个唯一性索引。

(8)创建一个视图customers_book，描述客户与订单的详细信息，包括客户编号、客户名单、订购图书的ISBN、图书名称、图书数量、订货日期、发货日期等。

(9)创建一个视图customers_gift，描述客户获得礼品的信息，包括客户名称、图书总价、礼品名称。

(10)定义序列seq_customers，产生客户编号，序列起始值为1，步长为1，不缓存，不循环。

(11)定义序列seq_orders，产生订单编号，序列起始值为1000，步长为1，不缓存，不循环。

(12)定义序列seq_promotion，产生礼品编号，序列起始值为1，步长为1，不缓存，不循环。

5、实验过程与结果

(1)打开SQL Plus，以system用户登录BOOKSALES数据库。

sys/Aa123456 as sysdba

(2)按下列方式创建一个用户bs，并给该用户授权。

create user bs identified by bs default tablespace users;

grant resource,connect,create view to bs;

(3)使用bs用户登录数据库，并进行下面的相关操作。

conn bs/bs@wangwen

(4)根据图书销售系统关系模式设计，创建表1至表6。(见P299-P300)

create table customers(

customer_id number(4) primary key,

name char(20) not null,

phone varchar2(50) not null,

email varchar2(50),

address varchar2(200),

code varchar2(10)

);

create table publishers(

publisher_id number(2) primary key,

name varchar2(50),

contact char(10),

phone varchar2(50)

);

create table books(

ISBN varchar2(50) primary key,

title varchar2(50),

author varchar2(50),

pubdate date,

publisher_id number(2),

cost number(6,2),

retail number(6,2),

category varchar2(50),

foreign key(publisher_id) references publishers(publisher_id)

);

create table orders(

order_id number(4) primary key,

customer_id number(4),

orderdate date not null,

shipdate date,

shipaddress varchar2(200),

shipcode varchar2(10),

foreign key(customer_id) references customers(customer_id)

);

create table orderitem(

order_id number(4),

item_id number(4),

ISBN varchar2(50) not null,

quantity number(4),

primary key(order_id,item_id),

foreign key(order_id) references orders(order_id)

);

create table promotion(

gift_id number(2),

name char(20) primary key,

minretail number(5,2),

maxretail number(5,2)

);

(5)在CUSTOMERS表的name列上创建一个B-树索引，要求索引值为大写字母。

create index cus_name_index on customers(upper(name)) tablespace users;

(6)在BOOKS表的title列上创建一个非唯一性索引。

create index book_title_index on books(title) tablespace users;

(7)在ORDERitem表的ISBN列上创建一个唯一性索引。

create unique index oitem_isbn_index on orderitem(ISBN) tablespace users;

(8)创建一个视图customers_book，描述客户与订单的详细信息，包括客户编号、客户名单、订购图书的ISBN、图书名称、图书数量、订货日期、发货日期等。

create view customers_books

as

select c.customer_id,name,b.isbn,title,quantity,orderdate,shipdate

from customers c,books b,orderitem o1,orders o2

where c.customer_id=o2.customer_id and o2.order_id=o1.order_id and o1.ISBN=b.ISBN

(9)创建一个视图customers_gift，描述客户获得礼品的信息，包括客户名称、图书总价、礼品名称。

create view customers_gift(customers_name,book_allcost,gift_name)

as

select cname,allcost,p.name

from(select c.name cname,sum(quantity*retail)allcost

from customers c join orders o on c.customer_id=o.customer_id

join orderitem oi on o.order_id=oi.order_id

join books b on oi.ISBN=b.ISBN

group by c.name) orderinfo

join promotion p on allcost between minretail and maxretail;

(10)定义序列seq_customers，产生客户编号，序列起始值为1，步长为1，不缓存，不循环。

create sequence seq_customers start with 1 increment by 1 nocache nocycle;

(11)定义序列seq_orders，产生订单编号，序列起始值为1000，步长为1，不缓存，不循环。

create sequence seq_orders start with 1000 increment by 1 nocache nocycle;

(12)定义序列seq_promotion，产生礼品编号，序列起始值为1，步长为1，不缓存，不循环。

create sequence seq_promotion start with 1 increment by 1 nocache nocycle;

(13)分区索引：

分区索引：分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。

创建分区索引：

SQL> create table customers2

2 (

3 cust_id number primary key,

4 cust_name varchar2(200),

5 rating varchar2(1) not null

6 )

7 partition by list(rating)

8 (

9 partition pA values(‘A’),

10 partition pB values(‘B’)

11 );

表已创建。

SQL> create table sales(

2 salse_id number primary key,

3 cust_id number not null,

4 sales_amt number,

5 constraint fk_sales_01 foreign key(cust_id) references customers2

6 )

7 partition by reference(fk_sales_01);

表已创建。

SQL> select partition_name, high_value

2

SQL> from user_tab_partitions

SP2-0734: 未知的命令开头 “from user_…” - 忽略了剩余的行。

SQL>

SQL> select partition_name, high_value from user_tab_partitions where table_name = ‘SALES’;

PARTITION_NAME

HIGH_VALUE

PA

PB

SQL> select table_name, partitioning_type, ref_ptn_constraint_name

2 from user_part_tables

3 where table_name in (‘CUSTOMERS’,‘SALES’);

TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME

SALES REFERENCE FK_SALES_01

SQL> select table_name, partitioning_type, ref_ptn_constraint_name

2 from user_part_tables

3 where table_name in (‘CUSTOMERS’);

未选定行

SQL> select table_name, partitioning_type, ref_ptn_constraint_name

2 from user_part_tables

3 where table_name =upper(‘CUSTOMERS’);

未选定行

SQL> select table_name, partitioning_type, ref_ptn_constraint_name

2 from user_part_tables

3 where table_name in (‘CUSTOMERS2’,‘SALES’);

TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME

CUSTOMERS2 LIST

SALES REFERENCE FK_SALES_01

SQL> spool

当前正假脱机至 G:/test003.txt

SQL> spool off