oracle基本操作

分享一些oracle基本操作：

create table numtest(v1 number(5,2))    #建立一个表
create table numt(v1 number(5,2),v2 varchar(10),v3 number(3,5))

insert into numtest values(12.45)    #插入数据
insert into numt values(124.5,12,345)

select sysdate from dual    #查询系统日期

delete from numt    #删除表中所有数据
delete from numt where name='刘飞'    #删除表中某条数据

update girl set age=25,id=G010 where name='刘飞'    #修改表中的数据

drop table numt    #删除表
alter table pay add(comments varchar(10))   #增加列
alter table pay drop cloumn card    #删除列
alter table pay drop(comments,pay_date)
alter table pay rename column payid to pay_id    #修改列名
alter table pay modify(pay_fee number(2,0))    #修改列数据类型

desc pay    #查看数据库表详细信息

create table mycon1(ID number(5,2),score number(5,2),primary key(ID))    #主键约束
create table mycon1(ID number(5,2) not null,score number(5,2))    #非空约束
create table mycon1(ID number(5,2),score number(5,2),unique(ID))    #唯一约束
create table mycon1(ID number(5,2),score number(5,2),check(ID in(100,101,102)))    #检查约束
create table province(proname varchar(10),loc varchar(10),primary key(proname))
create table person(IDC varchar(10),zuji varchar(10),foreign key(zuji) references province(proname))    #外键约束

alter table person add(primary key(IDC),unique(num),check(sex in('F','M')),foreign key(zuji) references province(proname))    #增加约束
alter table person modify(age not null)    #增加/修改约束

select * from emp where rownum <=4    #查看数据表的前4行
select count(*) from emp    #统计数据表有多少行数据

select * from emp where sal>(select sal from emp where ename='FORD')    #查询工资高于ford的员工信息

select * from emp order by sal desc    #降序排列
select * from emp order by sal asc    #升序排列

select * from score,course    #多表联合查询，查询所有数据
select * from score,course where score.cid=course.cid    #多表联合查询，条件查询
select * from score,student course where score.sid=student.sid and score.cid=course.cid    
select student.* from student,score where score.cid='SCH01' and student.sid=score.sid    #多表联合查询
select * from score inner join course on score.cid=course.cid    #内连接方式
select * from score left outer join student on score.sid=student.sid    #左连接
select * from score right outer join student on score.sid=student.sid    #右连接