oracle考试参考资料

---Exp1

createtablespace ts_130202021038

datafile 'C:\\oracledata\\orcl11g\\ts_130202021038001.dbf' size 10M reuse autoextend on next 64K maxsize 50M;

altertablespace ts_130202021038

adddatafile 'C:\\oracledata\\orcl11g\\ts_130202021038002.dbf' size 10M reuse autoextend on next 64K maxsize 50M;

alter database

datafile 'C:\\oracledata\\orcl11g\\ts_130202021038002.dbf' offline drop;

---Exp2

create table business( bno char(10) primary key, bname char(50) not null,

btype char(4) check(btype in ('企业', '事业', '私有')), badderss char(20), btel char(13)

)tablespace ts_130202021038;

select * from business; desc business;

create table hospital( hno char(5) primary key, hname char(40) not null, haddress char(60)

)tablespace ts_130202021038;

select * from hospital; desc hospital;

create table card(

cno char(15) primary key,

ctype char(8) check (ctype in ('企业', '事业', '灵活就业')), cmoney number(7, 2) not null )tablespace ts_130202021038;

select * from card; desc card;

create table staff(

sno char(5) primary key,

sname char(20) not null,

ssex char(2) check (ssex in ('男','女')), sbirthday date, saddress char(20), stel char(15) unique, cno char(15) , bno char(10) ,

constraintstaff_card foreign key (cno) references card(cno),

constraintstaff_business foreign key (bno) references business(bno) )tablespace ts_130202021038;

select * from staff; desc staff;

create table see( sno char(5) , hno char(5), sdate date,

primary key(sno, hno, sdate),

constraintstaff_see foreign key(sno) references staff(sno) on delete cascade, constraintstaff_hospital foreign key(hno) references hospital (hno) )tablespace ts_130202021038;

select * from see; desc see;

create table consume( cno char(15) , hno char(5),

csdate date not null, mname char(20), mnumint not null,

csmoney number(7,2) not null, primary key (cno, hno, csdate),

constraintcno_consume foreign key(cno) references card(cno) on delete cascade, constraintconsume_hospital foreign key(hno) references hospital (hno) )tablespace ts_130202021038;

select * from consume; desc consume;

create table insurance( idate date, cno char(15),

imoney number(5, 2) not null, bno char(10) ,

primary key(idate, cno),

constraintinsurance_card foreign key (cno) references card(cno),

constraintinsurance_business foreign key (bno) references business(bno) )tablespace ts_130202021038;

select * from insurance; desc insurance;

select * from ts_130202021038; descuser_cons_columns; descuser_constraints;

select * from user_constraints; select * from user_cons_columns;

alter user stu_130202021038 quota unlimited on ts_130202021038; select * from user_tablespaces;

alter user stu_130202021038 default tablespace ts_130202021038;

select * from BUSINESS; select * from hospital; select * from card; select * from staff; select * from see;

select * from consume; select * from insurance; --2 查看表结构 --(2)

--SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'BUSINESS'; --SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'HOSPITAL'; --SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'CARD'; --SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'STAFF'; --SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'SEE';

--SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'CONSUME'; --SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'INSURANCE'; --(3)

--SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'BUSINESS'; --SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'HOSPITAL'; --SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'CARD'; --SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'STAFF'; --SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'SEE';

--SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'CONSUME';

--SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'INSURANCE'; --3.修改表结构

--(1)复制表“staff”,新表的名称为“staff_sql” create table staff_sql as select * from STAFF; select * from STAFF_SQL;

--(2)为表“staff_sql”添加字段“age INT”,并用desc命令查询该字段的信息 alter table staff_sql add age int ; descstaff_sql;

--(3)继续为表添加字段（列）

alter table staff_sql add salary number(5, 2) ; alter table staff_sql add salary_add number(3, 1); descstaff_sql;

--(4)为表添加唯一性约束

alter table staff_sql add unique(sname);

--用数据字典查看约束信息

--SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'STAFF_SQL'; --(5)为表删除刚才建立的约束

alter table staff_sql drop unique(sname); --用数据字典查看约束信息

--SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'STAFF_SQL';

--(6)为表删除步骤(2)添加的字段 alter table staff_sql drop column age; descstaff_sql;

--(7)为表删除步骤(3)添加的字段

alter table staff_sql drop (salary, salary_add); descstaff_sql;

--(8)为表修改某字段的属性

alter table staff_sql modify sname char(30); descstaff_sql; --4.(1)

select * from staff;

INSERT INTO business VALUES('B001','景田','私有','珠海','123456'); INSERT INTO business VALUES('B002','乐福','私有','珠海','654321'); INSERT INTO hospital VALUES('H001','第一人民医院','珠海'); INSERT INTO card VALUES('C001','事业',0.1); INSERT INTO card VALUES('C002','事业',0.1); INSERT INTO card VALUES('C003','事业',0.1);

INSERT INTO staff VALUES('S0001','赵四','男',to_date('1994-03-08','yyyy-mm-dd'),'37栋','8208208820','C001','B001');

INSERT INTO staff VALUES('S0002','李博','男',to_date('1994-03-08','yyyy-mm-dd'),'37栋','8208208821','C002','B001');

INSERT INTO staff VALUES('S0003','张超','男',to_date('1994-03-08','yyyy-mm-dd'),'37栋','8208208822','C003','B002');

INSERT INTO see VALUES('S0001','H001',to_date('2015-05-13','yyyy-mm-dd'));