flyleer

//創建臨時表空間(日志文件)
create temporary tablespace test_temp
tempfile 'E:\oracle\test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//創建數據表空間
create tablespace test_data
logging
datafile 'E:\oracle\test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//創建用戶并指定表空間
create user 用戶名 identified by 密碼
default tablespace test_data
temporary tablespace test_temp;

//給用戶授予權限

grant connect,resource to 用戶名

————————————————————————————————————

create cluster s_t_cluster(tid number(8));--創建索引簇
----------------------------------------------------1
--建立簇表stu
create table stu(
       tid number(8),
       sname varchar2(50),
       sinfo varchar2(50),
       constraint pk_tid primary key(tid)
)cluster s_t_cluster(tid)
--建立簇表sc
create table sc(
       tid number(8),
       score number(8),
       constraint fk_tid foreign key(tid) references stu(tid)
)cluster s_t_cluster(tid)
------------------------------------------------------2
--建立簇索引
create index s_t_idx on cluster s_t_cluster;
------------------------------------------------------3
--插入數據
insert into stu(tid,sname,sinfo)values(1,'haha','usa');
insert into stu(tid,sname,sinfo)values(2,'gaga','Japan');
insert into sc values(1,90);
insert into sc values(2,85);
--查詢
select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid
--建立序列
create sequence stu_SEQ
minvalue 1
maxvalue 99999999
start with 3
increment by 1
cache 20;

--用命令插入N條數據往表stu
declare
x number;
begin
  for x in 1..100000 loop
    insert into stu values(stu_seq.nextval,'名字'||x,'地址'||x);
  end loop;
end;
---------------------------------------------
--用命令插入N條數據往表sc
declare
x number;
begin
  for x in 1..10000 loop
    insert into sc values(sc_seq.nextval,x+50);
  end loop;
end;
---------------------------------------------
--查詢
select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid--未加索引時的普通查詢太慢了
--使用索引簇查詢
select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid

————————————————————————————————————————

//創建表，序列號(sequence)

create table test1(
       tid number(8),
       tname varchar2(50),
       tbd date,
       constraint pk_tid primary key(tid)
)
select * from test1
==============================================================
create sequence test1Seq --自定義的序列名
increment by 1           --每次加幾個，即遞增的間隔
start with 1             --從1開始計數
nomaxvalue               --不設置最大值
nocycle                  --一直累加，不循環
cache 10;
==============================================================
insert into test1(tid,tname,tbd)values(test1Seq.Nextval,'ccc',sysdate);