Y2 基于SSH框架的企业级应用开发 第二章 上机+课后

--上机1

--创建表空间,大小为10M,文件大小可自动扩展,允许文件扩展的最大限度为无限制
create tablespace tp_orders
datafile 'E:\作业\Y2\第一学期\第二章\tb.dbf'--注意！！！！,这个一旦创建就不要删，删除后你就登陆不了Oracle了
size 10M autoextend on ;

--创建用户,密码bdqn,，默认表空间tp_orders
create user A_oe
identified by bdqn
default tablespace tp_orders;

--授予connect,resource权限和访问某用户的表
grant connect,resource to A_oe;
grant select on system.employee to A_oe;


--上机2
序列在Sequences文件夹看

--创建新表,命名为deptBak 
create sequence dept_seq
       start with 60    --从60开始
       increment by 10  --间隔10
       maxvalue 10000;  --最大值10000
       
insert into dept (deptno,dname) values(dept_seq.nextval,'sdf');--使用序列将数据插入到dept表中

drop sequence dept_seq;--删除序列

--创建deptBak表将dept的数据插入到deptBak中
create table deptBak
  as
select * from dept;

create sequence dept_seq
       start with 60
       increment by 10
       maxvalue 10000;
       
insert into deptBak (deptno,dname) values(dept_seq.nextval,'vc'); 

select dept_seq.currval  from dual--查看序列当前值
--上机3

create public synonym p_sy_dept for dept;--创建公有同义词
grant select on dept to A_oe;--授予A_oe查看该表的权限
select * from p_sy_dept;--在A_oe用户下查看公有同义词



--上机4
create unique index  index_cid on customers(customers_id); --唯一索引
create bitmap index index_terr on customers(nls_territory); --位图索引
create unique index index_name on customers(cust_last_name,cust_first_name);--组合索引

--上机5

--范围分区
create table rangeOrders
(
 rDate Date not null
)
partition  by range (rDate)
(
 partition p1 values less than (to_date('2005/01/01','yyyy-mm-dd')),
 partition p2 values less than (to_date('2006/01/01','yyyy-mm-dd')),
 partition p3 values less than (to_date('2007/01/01','yyyy-mm-dd')),,
 partition p4 values less than (to_date('2008/01/01','yyyy-mm-dd')),
 partition p5 values less than (to_date('2009/01/01','yyyy-mm-dd'))
 partition p6 values less than (maxvalue)
);

select * from rangeOrders partition(p3);--查看第三个分区的数据
delete from rangeOrders partition(p3);--删除第三个分区的数据
insert into rangeOrders values(to_date('2013/01/01','yyyy/mm/dd'))--插入数据


--上机6

--间隔分区
create table intervalOrders
(
  oDate date not null
)
partition by range(oDate)
 interval(numtoyminterval(1,'YEAR'))--每1年为一个分区
 (partition p1 values less than (to_date('2005/01/01','yyyy-mm-dd')));
 --插入数据
 insert into intervalOrders values(to_date('2003/12/01','yyyy-mm-dd'))
 --查询分区情况
 select table_name,partition_name 
 from  user_tab_partitions
 where table_name=upper('p1');
--查询分区数据
select * from  intervalOrders partition(p1);
--删除分区
ALTER TABLE intervalOrders TRUNCATE PARTITION p1;
--删除全部分区
ALTER TABLE 表名 DROP PARTITION 分区名;



--简答2
1)
 create table Stock_Received
 (
   Stock_ID number(4) not null,
   Stock_Date date ,
   Cost  varchar2(20)
 )
 truncate table Stock_Received--删除表
 --创建序列
 create sequence myseq
         start with 1000
         increment by 10
         maxvalue 1100
         cycle 
         
2)
grant  create public synonym to A_oe --授予A_oe创建同义词的权限
grant initial to 
create public synonym p_Stock_Received for Stock_Received;--在A_oe用户下创建公有同义词
drop public synonym  p_Stock_Received;--删除公有同义词
select * from p_Stock_Received;--查看公有同义词
3)

create table Stock
(
   Stock_ID number(4) not null,
   Stock_Date date ,
   Cost  varchar2(20)
)
partition  by range (Stock_Date)
(
 partition p1 values less than(to_date('2008/01/01','yyyy-mm-dd')),
 partition p2 values less than(to_date('2009/01/01','yyyy-mm-dd')),
 partition p3 values less than(to_date('2010/01/01','yyyy-mm-dd')), 
 partition p6 values less than (maxvalue)
) 

alter user A_oe default tablespace tp_orders;

create unique index  index_cid on Stock(Stock_ID); --唯一索引