oracle简单操作

1. 
   
2. 
   
3. grant connect ,resource ,dba to house;
   
   
   create or replace view house_view
   as
   select house.*, street.name as street_name,district.name as district_name,typex.name as tname
   from house,street,district,typex
   where house.street_id=street.id
   and street.district_id=district.id
   and   house.type_id=typex.id
   
   
   select house_seq.nextval from house;
   insert into street(id,name) values(2000,'ddd');
   
   
   --6 为5视图的插入操作做一个instead of 触发器
   create or replace trigger pro_view_trig
   instead of insert on house_view
   
   
   begin
      insert into street(id,name) values(:new.id ,:new.name);
      insert into district(id,name) values(:new.id ,:new.name);
      insert into typex(id,name) values(:new.id ,:new.name);
     insert into house(id,user_id,type_id,title,description,price,area,pubdate,floorage,contact,street_id) values(:new.id ,:new.user_id,:new.type_id,:new.title,:new.description,:new.price,:new.area,:new.pubdate,:new.floorage,:new.contact,:new.street_id);
   
   
    
   end;
   
4. 
   
5. 
   
6. 
   
7. 
   
8. 
   
9. 
   
10. 
    
11. create table emp(

id number primary key ,
deptid int ,
ename varchar2(100),
sex char(5),
age number,
money number(7,2),
jointime date




)
drop table emp;
insert into emp(id,deptid,ename,sex,age,money,jointime) values(1,1,'x','n',33,3000,to_date('2012-12-12','yyyy-MM-dd'));


select * from emp


drop table QQUser






insert into QQUser(QQID,QQpassWord,LasLogTime,QQonLine,QQLevel) values(11111111,'1723232323',to_date('2012-12-12','yyyy-MM-dd'),'1','1');
insert into QQUser(QQID,QQpassWord,LasLogTime,QQonLine,QQLevel) values(44444444,'123123123123',to_date('2015-12-12','yyyy-MM-dd'),'1','1');
insert into QQUser(QQID,QQpassWord,LasLogTime,QQonLine,QQLevel) values(55555555,'343434343434',to_date('2017-12-12','yyyy-MM-dd'),'1','1');


create table BaseInfo(
QQID number(11,2) primary key ,
Nickname varchar2(20),
Sex char(2),
age number,
Province varchar2(100),
City varchar2(100),
Address varchar(20),
Phone  number
)
insert into BaseInfo values(44444444,'fgfgfg','m',23,'河南','郑州','高新区',121212);
insert into BaseInfo values(5555555,'xiongjialan','m',23,'河南','郑州','高新区',121212);
insert into BaseInfo values(33333333,'lanlan','m',23,'河南','郑州','高新区',121212);






create table relation(
OOID  number(11,2),
QQname varchar2(10),
state char(2)


)




rename QQUser to qquserxx,
truncate table qquserxx; 
select * from  qquserxx;
drop table qquserxx


select * from QQUser;
alter table QQUser add(Address varchar2(100))
alter table QQUser add(privace varchar2(100),City varchar2(20))  
alter  table QQUser drop column Address 


alter  table QQUser drop(privace,City)




alter  table QQUser modify(Address varchar2(500),City varchar2(20))


create table student(
id number primary key ,
sname varchar2(100)  unique not null,


sex char(5) check(sex='男' or sex='女'),
sn varchar2(20) check(length(sn)=5 or length(sn)=8) ,
age number,


birthday date default sysdate
)
drop table student;


insert into  student values(6,'腾涌','男','121212',22,to_date('2012-12-12','yyyy-MM-dd'));
insert into  student values(7,'李凯3','男','12121212',22,to_date('2012-12-12','yyyy-MM-dd'));
insert into  student(id,sname,sex,sn,age) values(9,'李凯4','男','12121212',22);
select * from student ,


drop table score;
create table score(


id number(10) primary key ,
stuid number references student(id) not null,
score number(20),
dotime date




)


select * from score;


insert into  score values(1,1,22,to_date('2012-12-12','yyyy-MM-dd'));


insert into  score values(2,null,22,to_date('2012-12-12','yyyy-MM-dd'));


insert into  score values(4,5,200,to_date('2012-12-12','yyyy-MM-dd'));






create table userinfo(
uid number(12) primary key not null,
userId varchaer2(64) check(length(userId)>0 annd length(userId)<10),
upassword varchar2(64) check(length(upassword)>0 and length(upassword))


)


drop table QQUser


create table QQUser(
QQID number(11,2),
QQpassWord  varchar2(20),
LasLogTime date,
QQonLine char(2),
QQLevel number


)


alter table QQUser  add constraint ck_pwd check(length(QQpassWord)>=6);
alter table QQUser add constraint ck_QQonLine check(QQonLine in(0,1,2));


create table relation(


QQID  number(11,2) primary key,
QQname varchar2(10),
state char(2)


)
drop table relation;


alter table relation add constraint ck_state check(state in(0,1,null))




alter table relation add constraint pk_QQID  foreign key(QQID) references QQUser(QQID)


create table BaseInfo(
QQID number(11,2) primary key ,
Nickname varchar2(20),
Sex char(2),
age number,
Province varchar2(100),
City varchar2(100),
Address varchar(20),
Phone  number
)
alter table BaseInfo add constant ck_age check(age>0 and age<100)