oracle中创建选课表约束,数据库学习之实验一+Oracle数据库的基本操作

三、实验内容：

(一) 数据定义

一、建立基本表

创建学生表(student)、学生选课表(SC)、课程表(course)

1)·学生表：Student_学号后四位 (Sno, Sname, Ssex, Sdept)其中学号Sno主码，其中sno为number，sname为varchar2(10)，ssex为char(2)，sdept为varchar2(10)

CreatetableStudent_4128(

Snonumberconstraintpkk_snoprimarykey,

Snamevarchar(10),

Ssexvarchar(2),

Sdeptvarchar(10));

--注释

2)·课程表：Course_学号后四位(Cno, Cname, Cpno, Ccredit)其中课程号Cno主码；先行课为外码参照Course表中Cno字段。其中cno为number，cname为varchar2(10),cpno为number,ccredit为number(2)。

方法1：

createtableCourse_4128(

Cnonumberconstraintpk_cnoprimarykey,

Cnamevarchar(10),

cpnonumber,

ccreditvarchar(2),

constraintfk_cpnoforeignkey(Cpno)referencesCourse_4128(Cno)

);

方法2：

createtableCourse_4128(

Cnonumberconstraintpk_cnoprimarykey,

Cnamevarchar(10),

cpnonumber(10)constraintfk_cpnoreferencesCourse_4128(Cno),

ccreditvarchar(2)

);

3)·学生选课表：SC_学号后四位(Sno, Cno, Grade)其中(Sno、Cno)为主码；Sno为外码参照Student表中sno字段；Cno为外码参照Course表中cno字段。

createtableSC_4128(

Snonumber(10) ,

Cnonumber(10),

Gradenumber(10,2),

constraintpk_keyprimarykey(Sno,Cno),

constraintfk_snoforeignkey(Sno)referencesStudent_4128 (Sno),

constraintfk_cnoforeignkey(Cno)referencesCourse_4128 (Cno)

);

二.修改基本表

1)在Student表中加入属性Sage(number型)。

altertableStudent_4128addSagenumber(10);

2)修改某个表的属性的数据类型。

修改Student 表中Sdept的数据类型varchar-àvarchar2

altertableStudent_4128modifySdeptvarchar2(10);

3)给表student的sex列添加一个自定义约束sex只能取’男’,’女’两个值。

altertableStudent_4128addconstraintS_sexcheck(Ssexin('男','女'));

三、索引操作

1.建立索引

1)在Student表上建立关于Sname的唯一索引stusnam+学号后四位

createuniqueindexstusnam_4128onStudent_4128 (Sname);

2)在SC表上建立关于Sno升序、Cno降序的唯一索引i_sc+学号后四位

createuniqueindexi_4128onSC_4128(SnoASC,CnoDESC);

2.删除索引

1)删除Student表上的索引stusnam+学号后四位

dropindexstusnam_4128;

2)删除Course表上的索引i_sc+学号后四位

dropindexi_4128;

四.删除基本表

1) 删除基本表Student

drop table student_4128;

drop table student_4128

ORA-02449:表中的唯一/主键被外键引用

2)删除基本表SC

drop table sc_4128;

Table dropped

结果如何，先执行2)，在执行1)结果如何。

drop table student_4128;

Table dropped

五、单表查询

运行如下sql代码：

Create table student as select * from scott.student;

Create table course as select * from scott.course;

Create table sc as select * from scott.sc;

再执行如下的查询：

1.求数学系学生的学号和姓名。

select Sno,Sname from student where Sdept='MA';

SNO SNAME

--------- --------

20070001李佳

20070003王添

20070006张力

2.求选修了课程的学生学号。

select distinct Sno from SC where cno is not null;

SNO

---------

20070003

20070001

20070002

20070005

3.求选修课程号为‘2’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

select sno,Grade from SC where cno='2' order by Grade DESC,Sno ASC;

SNO GRADE

--------- -----

20070002    90

20070003    90

20070001    85

4.求选修课程号为’2’且成绩在80～90之间的学生学号和成绩，并将成绩乘以0.8输出。

select sno,Grade from SC where cno='2' and Grade>=80 and Grade<=90;

SNO GRADE

--------- -----

20070001    85

20070002    90

20070003    90

select Sno,0.8*Grade from SC where cno='2' and Grade>=80 and Grade<=90;

SNO  0.8*GRADE

--------- ----------

20070001         68

20070002         72

20070003         72

5.求数学系或计算机系姓张的学生的信息。

select * from student where sname like '张%' and Sdept='MA' or sname like '张%' and Sdept='IS';

SNO SNAME    SSEX SAGE SDEPT

--------- -------- ---- ---- --------------------

20070004张力女21 IS

20070006张力男19 MA

6.求缺少了成绩的学生的学号和课程号。

select sno,cno from sc where grade is null;

SNO   CNO

--------- -----

7.查询各个课程号与相应的选课人数。

select cno,count(*) from sc group by cno;

CNO   COUNT(*)

----- ----------

1          3

6          1

2          3

4          3

5          2

3          3

7          1

7 rows selected

select cno,count(*) num from sc group by cno order by cno ASC;

CNO        NUM

----- ----------

1          3

2          3

3          3

4          3

5          2

6          1

7          1

7 rows selected