发布时间 : 星期二 文章oracle考试参考资料更新完毕开始阅读
INSERT INTO consume VALUES('C001','H001',to_date('2015-05-13','yyyy-mm-dd'),'阿司匹林',2,5.2);
INSERT INTO insurance VALUES(to_date('2015-05-12','yyyy-mm-dd'),'C001','10','B001');
--4.(2)
CREATE TABLE staff_sql2 ( sno CHAR(5) PRIMARY KEY, sname CHAR(20) NOT NULL,
ssex CHAR(2) CHECK(ssex IN('男','女')), sbirthday date, sadress CHAR(20),
stel CHAR(15) UNIQUE NOT NULL, cno CHAR(15), bno CHAR(10),
CONSTRAINT staff_sql2_card FOREIGN KEY (cno) REFERENCES card (cno),
CONSTRAINT staff_sql2_business FOREIGN KEY (bno) REFERENCES business (bno) );
INSERT INTO staff_sql2 VALUES('S0001','李宏','男',to_date('1994-03-08','yyyy-mm-dd'),'37','8208208820','C001','B001');
INSERT INTO staff_sql2 VALUES('S0002','王明','男',to_date('1994-03-09','yyyy-mm-dd'),'38','8208208821','C001','B001');
INSERT INTO staff_sql2 VALUES('S0003','张丽','男',to_date('1994-03-10','yyyy-mm-dd'),'39','8208208822','C001','B001');
INSERT INTO staff_sql2 VALUES('S0004','钱强','男',to_date('1994-03-11','yyyy-mm-dd'),'40','8208208823','C001','B001');
栋栋栋栋
INSERT INTO staff_sql2 VALUES('S0005','孙五','男',to_date('1994-03-12','yyyy-mm-dd'),'41栋','8208208824','C001','B001'); SELECT * FROM staff_sql2;
--4.(3)
CREATE TABLE staff_sql3 AS SELECT * FROM staff_sql2; SELECT * FROM staff_sql3; --4.(4)
CREATE TABLE staff_sql4 AS SELECT * FROM staff_sql3 WHERE SNO = 'S0001'; SELECT * FROM staff_sql4; --5.
update staff set bno = (select bno from staff where sname = '张超') where sname = '李博'; --6.(1)
select * from business; select * from hospital; select * from card; select * from staff; select * from see;
select * from consume;
select * from insurance; --6.(2)
selectsname, sbirthday from staff; --6.(3)
select distinct * from business b where b.bno in (select bno from staff); --6.(4)
select sname, ssex, sbirthday from staff where sadress = '黄山路' and ssex = '女'; --6.(5)
select sno, sname, ssex from staff where sname like '李%'; --6.(6)
select * from staff order by sbirthdaydesc; --6.(7)
select * from staff order by bno ASC, sbirthdaydesc; --6.(8)
select count(*) from staff group by bno; --6.(9)
selectbno, count(*) from staff group by bno having count(*) > 1; --6.(10)
select sno as \员工编号\姓名\--6.(12)
selectb.bno, b.bname, s.sno, s.sname from business b, staff s where b.bno = s.bno; --6.(13)
selectb.bno, b.bname, co.MNAME, co.MNUM from business b, staff s, see se, consume co where s.bno = b.bno and se.sno = s.sno and se.hno = co.hno; --6.(14)
select sno, sname from staff where bno = (select bno from staff where sname = '李博') and sname != '李博'; --7.修改表数据
--(1)delete from business where bno = 'b01'; --(2)delete from HOSPITAL where hno = 'h01'; --(3)drop table staff_sql2; --(4)delete from consume;
--(5)delete from see where sno = 's01'; --(6)rollback --8.删除表结构
--(1)drop table staff_sql3; --(2)drop table staff_sql2; ------------------------------------- delete from BUSINESS; delete from hospital; delete from card; delete from staff; delete from see;
delete from consume;
delete from insurance; ------------------------------------ select * from BUSINESS; select * from hospital; select * from card; select * from staff; select * from see;
select * from consume; select * from insurance;
--oracle实验三管理索引和视图 --1.创建索引 --(1)
create index hospital_name_index on hospital (hnamedesc) tablespace ts_130202021038; select * from user_indexes where index_name = 'HOSPITAL_NAME_INDEX'; --select index_name from user_indexes where table_name='HOSPITAL'; --(2)
create index staff_into_index on staff (snameasc, ssexdesc, sbirthdaydesc)tablespace ts_130202021038;
select * from user_indexes where index_name = 'STAFF_INTO_INDEX'; --select index_name from user_indexes where table_name = 'STAFF'; --2.查看索引
--(2)select * from USER_INDEXES ;
--(3)select * from DBA_INDEXES where index_name = staff_into_index;
--3.删除索引
--(1)drop index hospital_name_index; --(2)drop index staff_into_index; --4.创建视图
--(1)create view ygbx_card_view as
select card.cno, staff.bno
from card, staff, business where card.cno = staff.cno and staff.bno = business.bno;
select * from ygbx_card_view; --(2)
create view consume_view as
selectcard.cno , staff.sno
from card, staff, see, consume where consume.cno = staff.cno and see.sno = staff.sno and consume.cno = card.cno;
select * from consume_view ;
--(3)
create view insurnce_view as
selecti.idate, i.imoney, i.bno, c.ctype, c.cmoney from insurance i, card c where c.cno = i.cno;
select * from insurnce_view; --(4)
create view business_view as
select * from business;
--5.查看视图
--(2)select view_name, text, read_only from user_view where view_name = 'consume_view'; --(3)select view_name, text, read_only from user_view where view_name = 'card_view';
--(4)select view_name, text, read_only from user_view where view_name = 'insurance_view'; --6.视图数据的更新
--(1)insert into business_view (bno, bname, btype, badderess, btel) values ('B1997010287', '格林制药', '企业', '鸭绿江街98号', '84692315');
--(2)select view_name, text, read_only from user_view where view_name = 'business_view'; --7.删除视图
--(1)drop view business_view; --(2)drop view card_view; --(3)drop view consume_view; --(4)drop view insurnce_view;
---oracle实验四创建同义词 --1.创建同义词
--(1)create public synonym qyb for business; --(2)create public synonym ybk for card; --2.查询同义词
--(1)select * from business; select * from qyb; --(2)select * from card; select * from ybk; --3.删除同义词
--(1)drop public synonym qyb; --(2)drop public synonym ybk; --4.创建序列 --(1)
create sequence ygbx_seql increment by 1 minvalue 60