网站开发需要懂多少代码,怀仁网站建设,推广方案设计,提供网站建设方案/**//*
tp_orders表空间#xff0c;大小10M#xff0c;文件大小可自动增长#xff0c;允许文件扩展#xff0c;最大限度为无限制
创建A_oe用户的默认表空间为tp_orders
密码 bdqn
授予connect,resource权限党文a_hr用户的employee
*/
--创建表空间和用户#xff0c;并授予…/**//*
tp_orders表空间大小10M文件大小可自动增长允许文件扩展最大限度为无限制
创建A_oe用户的默认表空间为tp_orders
密码 bdqn
授予connect,resource权限党文a_hr用户的employee
*/
--创建表空间和用户并授予访问数据库的权限
create tablespace tp_orders
datafile E:\E盘\tp_orders01.dbf
size 10M
autoextend on;--创建用户
create user A_oe
identified by bdqn
default tablespace tp_orders--赋予权限
grant connect,resource to A_oe;
grant select on test.employee to A_oe;
grant select on test.bumen to A_oe;select * from test.employee;/*使用序列生成部门编号的值
*/
/*
从60开始间隔是10最大值是10000的序列的对象dept_seq
*/
select * from bumen;
--创建一个序列
create sequence dept_seq
start with 60
increment by 10
maxvalue 10000
--插入数据
insert into bumen values(dept_seq.nextval,学术部);
insert into bumen values(dept_seq.nextval,学术部1);
--数据迁移前的工作
drop sequence dept_seq;create sequence dept_seq
start with 80
increment by 10
maxvalue 10000;
create table deptBak as
select * from bumen;select * from deptBak;
--测试插入数据
insert into deptBak values(dept_seq.nextval,人事部);/*创建A_oe模式下dept表的公有同义词可以允许任何能够连接上数据库的用户访问
*/--创建一个测试dept表
create table dept
as select * from test.bumen;select * from dept;--创建同义词
create public synonym p_sy_dept for a_oe.dept;
--赋予权限
grant select on test.customers to A_oe;
grant create public synonym to A_oe;
select * from p_sy_dept;/*
切换用户操作使用test用户
*/
--查看并且操作employee表select * from customers;
--为客户编号创建反向建索引
create index index_reverse_customer_id on customers (customer_id) reverse;
--为地域列创建位图索引
create bitmap index index_nls_territory on customers (nls_territory);
--为名和姓氏列创建组合索引
create index index_cus on customers(cust_fiest_name,cust_last_name);/*
根据订单表创建范围分区表
*/
--1已完成
--2创建分区
create table rangeOrders
(
order_id number(12) primary key, --订单编号
order_date date not null, --订货日期
order_mode varchar2(8) not null , --订货模式
customer_id number(6) not null, --客户编号
order_status number(2), --订单状态
order_total number(8,2), --总定价
sales_rep_id number(6), --销售代表id
promotion_id number(6) --推广员id
)
partition by range (order_date)
(
partition part1 values less than (to_date(2013-01-01 , yyyy-mm-dd)),
partition part2 values less than (to_date(2014-01-01 , yyyy-mm-dd)),
partition part3 values less than (to_date(2015-01-01 , yyyy-mm-dd)),
partition part4 values less than (to_date(2016-01-01 , yyyy-mm-dd)),
partition part5 values less than (to_date(2017-01-01 , yyyy-mm-dd)),
partition part6 values less than (maxvalue)
)--插入测试数据
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(1,2017-02-09,网上下单,2,1,323.23,1,2);insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(2,2016-11-09,上门购买,1,2,56.00,2,1);
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(3,2017-12-20,熟人推荐,3,1,6000,1,2);
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(4,2015-12-02,网上下单,5,2,365,2,2);
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(5,2017-12-09,上门购买,3,1,3210,1,2);
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(6,2014-11-11,网上下单,3,1,630,2,2);
insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(7,2017-01-01,上门购买,2,1,6300,1,2);--查看表中的数据
select * from rangeOrders
--查询每一个分区中的数据
select * from rangeOrders partition (part1);
select * from rangeOrders partition (part2);
select * from rangeOrders partition (part3);
select * from rangeOrders partition (part4);
select * from rangeOrders partition (part5);
select * from rangeOrders partition (part6);--查看分区情况
select table_name,partition_name from user_tab_partitions;--把已存在的表改为分区表
create table rangeOrder
partition by range (order_date)
(
partition part1 values less than (to_date(2013-01-01 , yyyy-mm-dd)),
partition part2 values less than (to_date(2014-01-01 , yyyy-mm-dd)),
partition part3 values less than (to_date(2015-01-01 , yyyy-mm-dd)),
partition part4 values less than (to_date(2016-01-01 , yyyy-mm-dd)),
partition part5 values less than (to_date(2017-01-01 , yyyy-mm-dd)),
partition part6 values less than (maxvalue)
)
as select * from orders;/*
间隔分区自动化
*/
--创建分区表(按照一年分一个表)
create table sales_interval1
partition by range (order_date)
interval (numtoyminterval(1,year)) --按照一年分区一个表
(partition part1 values less than (to_date(2017/01/01,yyyy/mm/dd)))
as select * from orders;--查看分区情况
select table_name,partition_name,tablespace_name from user_tab_partitions
where table_nameupper(sales_interval1);
--插入一条测试数据
insert into sales_interval1(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id)
values(98,2018/01/03,熟人推荐,3,1,96582,2);/*注意刚刚加的那条记录现在肯定没有所以
1.先执行
select table_name,partition_name,tablespace_name from user_tab_partitions
where table_nameupper(sales_interval1);
查看下一个分区是多少
2.复制刚刚查询出来的分区eg:SYS_P21
3.执行select * from sales_interval1 partition (SYS_P21);
*/
select * from sales_interval1 partition (SYS_P21);--添加分区
alter table rangeOrder
add partition part7 values less tahn(to_date(2018-01-01,yyyy-mm-dd));
--删除分区
alter table rangeOrder
drop partition part3;
--移动分区
alter table rangeOrder
move partition part1 tablespace works01; --works01是表空间名称、/*
1.创建一个单独的表空间
2.把分区的数据移动到这个表空间里面去
3.让这个表空间作为只读
*/--以system的身份登陆上
create tablespace tb_name
datafile e:\oracle\tbdb.dbf
size 10M;
--授权
alter user test quota unlimited on tb_name;
--移动
alter table rangeOrder
move partition part1 tablespace tb_name;
--设置为只读
alter tablespace tp_name read only;
--设置为读写
alter tablespace tp_name read write;/*
课后简答题
*/
--(1)在test用户下创建一个表Stock_Received
create table Stock_Received
(
Stock_ID number,
Stock_Date date,
Cost varchar2(50)
)--插入数据
insert into Stock_Received values (myseq.nextval,2017/03/05,描述一);
insert into Stock_Received values(myseq.nextval,2017/01/05,描述二);
insert into Stock_Received values (myseq.nextval,2017/02/05,描述三);
insert into Stock_Received values(myseq.nextval,2017/04/05,描述四);
insert into Stock_Received values(myseq.nextval,2017/05/05,描述五);
insert into Stock_Received values(myseq.nextval,2017/06/05,描述六);
insert into Stock_Received values(myseq.nextval,2017/05/05,描述七);
insert into Stock_Received values(myseq.nextval,2017/04/05,描述八);
insert into Stock_Received values(myseq.nextval,2017/02/05,描述九);
insert into Stock_Received values(myseq.nextval,2017/01/05,描述十);
insert into Stock_Received values(myseq.nextval,2017/08/05,描述十一);
--创建一个名为myseq的序列
create sequence myseq
start with 1000
increment by 10
maxvalue 1100
cycle--(2)创建一个公有的同义词
create public synonym p_Stock_received for Stock_Received
--给a_oe赋予一个可以查看Stock_Received的权限
grant select on p_Stock_received to a_oe;--用a_oe登陆测试能不能查看Stock_Received表
select * from p_Stock_received;
--↑测试成功--3在Stock_Received中根据Stock_Date列创建3个范围分区
create table range_Stock_Received
partition by range(Stock_Date)
(
partition p1 values less than(to_date(2017/01/01,yyyy-mm-dd)),
partition p2 values less than(to_date(2017/03/01,yyyy-mm-dd)),
partition p3 values less than(to_date(2017/05/01,yyyy-mm-dd)),
partition p4 values less than(maxvalue)
)
as select * from Stock_Received--查看每个分区里面的数据
select * from range_Stock_Received partition (p1);
select * from range_Stock_Received partition (p2);
select * from range_Stock_Received partition (p3);--(4)在表的id上创建一个逐渐索引列
create index index_Stock_ID on Stock_Received (Stock_ID);
