关于oracle的sql命令操作

表修改

创建表（含主键／外键约束）

create table CourseInfo(couserID varchar2(8) primary key,courseName varchar2(32) not null,departmentCode varchar2(5) not null,credit numeric(3,1) not null,resume varchar2(255) null,foreign key(departmentCode) references DepartmentInfo(departmentCode));

增加属性列

alter table classInfo add(temp varchar2(20));

修改属性名

alter table ClassInfo rename column teacherID to FK_Teacher;

增加外键约束

alter table ClassInfo add foreign key(FK_Teacher) references TeacherInfo(teacherID);

设置空值

alter table StudentInfo modify studentName null;

删除约束

alter table ClassInfo drop constraint courseID;

基本查询

范围查询

select * from CourseInfo where credit>=1 and credit<=3;
select * from CourseInfo where credit between 1 and 3;

降序排列

select * from ClassInfo order by classID desc;

表复制

Create table CourseInfoTemp as select * from CourseInfo;

查询时修改部分属性值

select teacherID,teacherName,case sex when ‘f’ then ‘female’ when ‘m’ then ‘man’ else ‘unknown’ end from TeacherInfo;

分组查询

select studentID,studentName,departmentCode from StudentInfo where studentID in (select studentID from SelectiveInfo group by studentID having count(studentID)>2);

查询平均值

select classID,avg(grade) from SelectiveInfo1 group by classID;

左右连接

Select TeacherInfo.teacherID,TeacherInfo.teacherID,ClassInfo1.classID from TeacherInfo,ClassInfo1 where TeacherInfo.teacherID=ClassInfo1.teacherID(+);

创建视图

Create view showSelective as select StudentInfo.studentID,StudentInfo.studentName,departmentInfo.departmentName,ClassInfo.className,CourseInfo.courseName,CourseName.credit,TeacherInfo.teacherName where StudentInfo.departmentCode=departmentInfo.departmentCode and StudentInfo.studentID=SelectiveInfo.studentID and SelectiveInfo.classID=ClassInfo.classID and ClassInfo.courseID=CourseInfo.courseID and ClassInfo.teacherID=TeacherInfo.teacherID;

模糊查询

select * from StudentInfo where studentName like ‘%he%’;

索引操作

创建Ｂ树索引

create index idx1 on StudentInfo(studentName);

创建位图索引

create bitmap index idx2 on StudentInfo(DepartmentCode);

查询索引的使用情况

alter index idx1 monitoring usage;//开启索引监控
select * from v$object_usage;//查看索引使用情况