oracle 10g基础

操作表

 1创建新表
1.1从查询到的表创建表
 create table temp as select stuName,stuNo,stuSex from stuInfo where stuAge>25;

1.2创建新表
/*学生信息表*/
create table stuInfo(
    stuName varchar2(10) ,
    stuNo varchar2(10),
    stuSex varchar2(4),
    stuAge number(2),
    stuSeat number(10),
    stuAddress varchar2(400));
/*学生成绩表*/
create table stuMark(
    examNo varchar2(10),
    stuNo varchar2(10),
    writtenExam number(4),
    labExam number(4));

2 修改表
2.1 增加字段
alter table stuInfo add(Zip number(6));

2.2 删除字段
alter table stuInfo drop column Zip

2.3 修改字段类型
alter table stuInfo modify(Zip varchar2(6));

2.4修改字段大小
alter table stuInfo modify(Zip number(4));

2.5 删除表
    drop table stuInfo

3约束
3.1添加约束
alter table stuInfo add constraint PK_stuNo primary key(stuNo);
alter table stuInfo add constraint CK_stuSex check(stuSex in('男','女'));
alter table stuInfo add constraint CK_stuAge check(stuAge between 15 and 40);
alter table stuInfo add constraint CK_stuSeat check(stuSeat between 1 and 30);
    
alter table stuMark add constraint PK_ExamNo_stuMark  primary key(examNo);
alter table stuMark add constraint FK_stuNo_stuMark  foreign key(stuNo) references stuInfo(stuNo);
 select stuName,decode(stuSex,'男','男同志'),
    (stuSex,'女','女同志')
     from stuInfo;

alter table stuInfo modify(stuSex not null);

3.2删除约束
3.2.1删除普通约束
alter table stuInfo drop constraint CK_stuSex;

3.2.2删除被外键参照的主键约束
alter table stuInfo drop primary key PK_StuNo

4索引
4.1创建索引
create index stuName_index on stuInfo(stuName);

4.2删除索引
drop index stuName_index;

5创建序列
5.1 创建序列
create sequence stuSeat_identity
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 2
5.2触发器实现字段列自增长
create table stu(
 stuID number(10),
 stuName varchar2(20));

create sequence autoId start with 1 increment by 1 cache 200;

create or replace trigger getautoId
  before insert on stu  
  for each row
declare
  -- local variables here
begin
  select autoId.Nextval into :new.stuId from dual;
end getautoId;

insert into stu values(null,'s');
insert into stu values(null,'s1');
insert into stu values(null,'s2');
insert into stu values(null,'s3');

6视图
6.1 创建视图
create or replace view v_stuInfo as  select * from stuInfo;
6.2 删除视图
drop view v_stuInfo;

7同义词
7.1 创建同义词
create synonym st for System.stuInfo;
7.2 删除同义词
drop synonym st;

8 数据操作语句
8.1 插入数据
insert into stuInfo values('MARK','s25301','男',18,stuseat_identity.nextval,'北京海淀');
insert into stuInfo values('andy','s25303','女',22,stuseat_identity.nextval,'河南洛阳');
insert into stuInfo values('JACK','s25302','男',31,stuseat_identity.nextval,'武汉武昌');
insert into stuInfo values('zerolin','s25304','男',28,stuseat_identity.nextval,'新疆威武哈'); 

insert into stuMark values('s271811','s25303',90,56);
insert into stuMark values('s271813','s25302',58,90);
insert into stuMark values('s271816','s25301',87,82);
insert into stuMark values('s271819','s25304',66,48);

8.2 更新语句
update stuInfo set stuName='Oracle' where stuAge>27;

8.3 数据合拼语句
merge into depat_13_temp a
using department_13 b
on(a.department_id = b.department_id)
when matched then
  update set
     a.department_name = b.department_name,
     a.manager_id = b.manager_id,
     a.location_id = b.location_id
when not matched then
  insert(a.department_id, a.department_name, a.manager_id, a.location_id)
  values(b.department_id, b.department_name, b.manager_id, b.location_id);

9 查询语句
9.1 带算术表达式的select语句
select ExamNo,stuNo,writtenExam,labExam+100 from stuMark;

9.2 带连接表达式的select语句
 select ExamNo||'的学号是:'||stuNo||'笔记成绩是:'||writtenExam||'机试成绩是:'||labExam from stuMark;

9.3 字段别名
 select ExamNo "考号",stuNo "学号",writtenExam "笔记成绩是",labExam "机试成绩是" from stuMark;

9.4 比较操作符
between..and 
in
like
is null

9.5 比较操作的逻辑运算符
and  or
select * from stuInfo where stuSex='男' or stuSex='女';
select * from stuInfo where stuSex='男' and stuAge>27;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zerolsy/archive/2008/09/10/2908942.aspx