--创建表空间
create tablespace yyspace
datafile ’d:\yyspace.dbf’
size 10m
autoextend on;
--创建临时表空间
create temporary tablespace yytemp
tempfile ’d:\yytemp.dbf’
size 10m
autoextend on;
--创建用户
create user yangrs
identified by yangrs;
alter user yangrs
default tablespace yyspace;
alter user yangrs
temporary tablespace yytemp;
--赋权
grant connect,resource to yangrs
--connect
connect yangrs/yangrs;
--建表
--删表
drop table stuInfo
create table stuInfo
(
s_id number(4),
s_name varchar2(10),
s_sex char(2),
s_age number(3),
s_birthday date default(sysdate),
s_note varchar2(50)
);
create table stuScore
(
stuid number(4),
scoreid varchar2(10),
score number(3)
);
drop table stuScore;
--加约束
--主键
alter table stuInfo
add constraint PK_s_id primary key(s_id);
--检查
alter table stuInfo
add constraint CK_s_sex check(s_sex in (’男’,’女’));
alter table stuInfo
add constraint CK_s_age check(s_age>0 and s_age<100);
--加默认的不行
alter table stuInfo
add constraint DK_s_birthday default(systimestamp );
--外键
alter table stuScore
add constraint FK_stuid foreign key(stuid) references stuInfo(s_Id);
--insert
insert into stuInfo(s_id,s_name,s_age,s_Sex,s_Note) values(1000,’刘德华’,20,’男’,’just do it’);
insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1001,’yangrs’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1002,’yangrs2’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1003,’yangrs3’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1004,’yangrs4’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
insert into stuInfo(s_id,s_name,s_age,s_Sex,s_Note) values(1005,’华仔’,20,’男’,’just do it’);
insert into stuScore(Stuid,scoreid,Score) values(1001,’1’,100);
insert into stuScore(Stuid,scoreid,Score) values(1001,’1’,100);
insert into stuScore(Stuid,scoreid,Score) values(1000,’1’,100);
insert into stuScore(Stuid,scoreid,Score) values(1000,’1’,100);
责任编辑：小草
您现在的位置： 