表修改
创建表(含主键/外键约束)
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%’;
索引操作
创建B树索引
create index idx1 on StudentInfo(studentName);
创建位图索引
create bitmap index idx2 on StudentInfo(DepartmentCode);
查询索引的使用情况
alter index idx1 monitoring usage;//开启索引监控
select * from v$object_usage;//查看索引使用情况