Oracle作业

1.根据表格创建数据表

学生表（School_Students）

列名	数据类型	长度	是否允许为空	说明
stu_id	nvarchar2	20	否	账号、主键约束
stu_name	nvarchar2	20	否	姓名
stu_class_id	nvarchar2	20	否	班级编号

--创建学生表school_students
create table school_Students
(
  stu_id nvarchar2(20) not null,
  stu_name nvarchar2(20) not null,
  stu_class_id nvarchar2(20) not null,
  constraint pk_stu_id primary key(stu_id)
);

班级表(school_class)

列名	数据类型	长度	是否允许为空	说明
class_id	nvarchar2	20	否	班级编号、主键约束
class_name	nvarchar2	20	否	班级名称

--创建班级表(school_class)
create table school_class
(
  class_id nvarchar2(20) not null,
  class_name nvarchar2(20) not null,
  constraint pk_class_id primary key(class_id)
);

2.添加数据

班级编号	班级名称
20163001	16播音与主持艺术1班
20163002	16播音与主持艺术2班
20163003	16传播学
20163004	16地理科学

--往班级表(school_class)插入数据
insert into school_class
values ('20163001','16播音与主持艺术1班');
insert into school_class
values ('20163002','16播音与主持艺术2班');
insert into school_class
values ('20163003','16传播学');
insert into school_class
values ('20163004','16地理科学');

学号	姓名	班级
201632218002	曹浒琳	20163001
201632218003	曹睿	20163001
201632218004	陈浩	20163001
201632218028	李松鸿	20163002
201632218029	李唯郡	20163002
201632218030	李文博	20163002
201632218031	李昕原	20163003
201632218041	潘睿	20163003
201632218042	戚婉秋	20163003
201632218051	王进凤	20163004
21632218052	王连超	20163004
201632218054	王祎	20163004

--往学生表(school_students)添加数据
insert into school_students values ('201632218002','曹浒琳','20163001');
insert into school_students values ('201632218003','曹睿','20163001');
insert into school_students values ('201632218004','陈浩','20163001');
insert into school_students values ('201632218028','李松鸿','20163002');
insert into school_students values ('201632218029','李唯郡','20163002');
insert into school_students values ('201632218030','李文博','20163002');
insert into school_students values ('201632218031','李昕原','20163003');
insert into school_students values ('201632218041','潘睿','20163003');
insert into school_students values ('201632218042','戚婉秋','20163003');
insert into school_students values ('201632218051','王进凤','20163004');
insert into school_students values ('201632218052','王连超','20163004');
insert into school_students values ('201632218054','王祎','20163004');

3.给学生表添加性别（stu_sex），默认值为男

alter table school_students
add stu_sex nvarchar2(2) default '男';

4.给学生表添加已修学分（stu_credits） 默认为0分

alter table school_students
add stu_credits number(2) default 0;

5.给班级表修改班级编号字段的默认值，默认值为“2016”

alter table school_class
modify class_id default '2016';

6.修改学号“201632218002”的同学，性别为女，学分为12，

update school_students
set stu_sex='女',stu_credits=12
where stu_id='201632218002';

7.修改学号“201632218042”的同学，性别女 ，学分为15，

update school_students
set stu_sex='女',stu_credits=15
where stu_id='201632218042';

8.修改学号“201632218051”的同学，性别女 ，学分为13，

update school_students
set stu_sex='女',stu_credits=13
where stu_id='201632218051';

9.修改班级编号为“20163003”的同学，学分为16，

update school_students
set stu_credits=16
where stu_class_id='20163003';

10.复制表school_students的结构和数据到表school_students2中

create table school_students2
as select * from school_students;

11.删除班级编号为“20163004”的School_Students2表中的数据

delete from school_students2
where stu_class_id='20163004';

12.创建表School_Students3

列名	数据类型	长度	是否允许为空	说明
stu_id	nvarchar2	20	否	账号、主键约束
stu_name	nvarchar2	20	否	姓名
stu_sex	nvarchar2	20	是	默认值男
stu_credits	number	2	是	默认值12
stu_class_id	nvarchar2	20	否	班级编号

--创建表School_Students3
create table school_students3
( 
 stu_id nvarchar2(20) not null,
 stu_name nvarchar2(20) not null,
 stu_sex nvarchar2(20) default '男',
 stu_credits number(2) default 12,
 stu_class_id nvarchar2(20） not null,
 constraint pk_stu_id3 primary key(stu_id)
);	

13.复制School_Students2到School_Students3（注意顺序）

insert into school_students3
(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)
select
stu_id,stu_name,stu_sex,stu_credits,stu_class_id
from school_students2;

14.把School_Students的数据合并到School_Students3（注意顺序,school_students3已经存在的数据不合并）

merge into school_students3
using school_students on(school_students3.stu_id=school_students.stu_id) 
when not matched then
insert
(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)
values
(school_students.stu_id,school_students.stu_name,school_students.stu_sex,school_students.stu_credits,school_students.stu_class_id);

15.修改School_Students表中的数据，把班级编号(stu_class_id)为“20163002”的同学，性别修改为“女”，学分修改为20

update school_students
set stu_sex='女',stu_credits=20
where stu_class_id=20163002;

16.把School_Students表中的数据更新到School_Students3

merge into school_students3
using school_students 
on(school_students3.stu_id=school_students.stu_id)
when matched then
update set 
school_students3.stu_name=school_students.stu_name,
school_students3.stu_sex=school_students.stu_sex,
school_students3.stu_credits=school_students.stu_credits,
school_students3.stu_class_id=school_students.stu_class_id
when not matched then
insert
(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)
values
(
 school_students.stu_id,
 school_students.stu_name,
 school_students.stu_sex,
 school_students.stu_credits,
 school_students.stu_class_id
);

17.给School_Students表创建学生姓名索引(index_test)，事务初始条目为2，最大并发数200

create index index_test   //创建索引index_test
on school_students(stu_name)   
initrans 2    //设置事务初始条目为2
maxtrans 200;  //设置最大并发数为200

18.修改索引名为“index_School_Students_stu_name”

//修改索引名
alter index index_test
rename to index_school_students_stu_name;

19.给School_Students表创建学生性别索引(index_sex)，事务初始条目为2，最大并发数200

create index index_sex
on school_students(stu_sex)
initrans 2
maxtrans 200;

20.删除性别索引

drop index index_sex;