Oracle 9i基本SQL操作

Oracle 9i基本SQL操作

---------重命名表------
Rename HR_BASE_CATEGORY To HR_TEMP_TABLE;

----------把一个表的数据拷到另一个相同结构的表
Insert Into HR_BASE_CATEGORY Select
 CATEGORY_ID,
 CATEGORY_NAME,
 CATEGORY_ALIAS,
 CATEGORY_DESC,
 MODULE_ID,
 MODULE_NAME,
 SORT_ORDER
From HR_TEMP_TABLE;

---------创建表----------

-- 创建班级表

Create table class
(
 class_id  varchar2(10) not null primary key, --主键
 class_name varchar2(10) not null,
 class_create date default sysdate,
 class_number number(2,0),
 class_remark  varchar2(50)
)

-- 删除表class

drop table class cascade constraint;

-- 创建学生表
create table student
(
 stu_id varchar2(10) not null primary key,
 stu_name varchar2(8),
 stu_gender integer default 0,
 stu_birthday date,
 stu_from varchar2(20),
 stu_tel varchar2(14),
 stu_dorm varchar2(8),
 stu_class_id varchar2(10)
)

--创建课程表
create table course
(
course_id varchar2(6) not null primary key,
course_name varchar2(16),
course_book varchar2(30)
)

drop table course cascade constraint;

--创建成绩表
create table score
(
score_stu_id varchar2(10) not null ,
score_course_id varchar2(6) not null,
score_pingshi  number(5,2),
score_final number(5,2),
score_Total number(5,2) --总成绩
)

--------修改表---------

alter table student add  email varchar2(30);         ------在学生表中增加字段：email地址；-------

alter table course modify course_book varchar2(36);  ------增加课程表字段"教科书"的长度；-------

alter table score modify score_Total default 0;    ------设置成绩表的总评成绩字段缺省值为0；-----

--------创建视图----------
如果你在创建视图时使用了DISTINCT语句，那么你就不能插入或更新这个视图中的记录
create view student_view(stu_name,course_name,score_info)
as
select student.STU_NAME , course.COURSE_NAME , score.score_Total
from student,score,course
where student.STU_ID = score.SCORE_STU_ID
    and course.COURSE_ID = score.SCORE_COURSE_ID;

--------创建索引----------

create index score_index on score(score_stu_id,score_course_id);

--------约束条件的创建-------

--------创建主键---------

--如果在表创建时已创建主键，可不再单独创建主键

alter table class add constraint pk_class_id primary key;

alter table student add constraint pk_stu_03 primary key;

alter table score add constraint pk_score_03 primary key;

----------创建外键---------

alter table student add constraint fk_class foreign key (stu_class_id) references class(class_id);

alter table score add constraint fk_stu_id foreign key(score_stu_id) references student(stu_id);

alter table score add constraint fk_course_id foreign key(score_course_id) references course(course_id);

----------创建check约束---------

alter table student add constraint ck_stu_gender check( stu_gender in (0,1) );

----------插入数据 和 事务控制语句------------

insert into class values(031,jsj,to_date(2003-09-01,yyyy-mm-dd,30,good);
insert into class values(032,xg1,to_date(2003-09-01,yyyymm-dd,26,good);

insert into student
values(001,xinhe,0,to_date(1977-09-22,yyyy-mm-dd),hunan,135,e208,031wxinhe2004@126.com);

insert into student values(002,huarong,0,to_date(1977-09-22,yyyy-mm-dd),hunan,135,e209,032,wxin004@126.com);

insert into course values(001,c++,jixietushu);
insert into course values(002,shujuku,qinghuatushu);

insert into score values(001,001,90.00,90.00,90.00);
insert into score values(002,001,80.00,80.00,80.00);

commit;

----------修改数据 和 事务控制语句------------

update course set coursename=cyuyan where coursename=c++;
update student set name=wangxinhe where name=xinhe;
update class   set appendxi=average where classid=032;
update score set totalscore=95.0 where totalscore=90.00;

commit;

----------修改数据 和 事务控制语句------------
delete course where coursename=c++;
rollback;

delete student;

delete score;

delete class;

commit;

------------多表连接查询语句-----------

1）查询学生名称、编号、性别、生日（按yyyy-mm-dd显示）、所在班级的名称，其中，0显示为“男”，1显示为“女”(decode函数)；

  select a.stu_name ,a.stu_id , decode(a.stu_gender,0,男,1,女) 性别,
       to_char(a.stu_birthday,yyyy-mm-dd) 出生日期, b.class_name
  from student a,class b
  where a.stu_class_id=b.class_id;

2）查询学生的各门功课的平时成绩、期末成绩和总评成绩；

   select a.stu_id,a.stu_name, b.course_name, c.score_pingshi 平时,c.score_final 期末,c.score_Total 总成绩
   from student a,course b, score c
   where  a.stu_id = c.score_stu_id
       and b.course_id = c.score_course_id

3）查询姓名XX所在班级所有学生信息(嵌套查询)；

   select * from student where stu_class_id =
       (select stu_class_id from student where stu_name=xinhe);

4）查询课程编号为“xxx”和“xxx”的学生成绩信息(集合查询)；

   select stu_id,score_Total  from score  where score_course_id = 001 
   union
   select stu_id,score_Total  from score  where score_course_id = 002;

5） 查询各班每门功课的最好成绩；
   
   select A.stu_class_id, B.score_course_id, max(score_Total)
   from student A,score B
   where A.stu_id=B.score_stu_id
   group by A.stu_class_id, B.score_course_id