数据库上机实验一

题目：

1.创建数据库表、增加记录（包含完整性约束）

2.从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ，

包括学生姓名、课程名和成绩

3.在学生表中按学号建立索引、再删除该索引

4.在教师表中增加住址列

1.找出年龄在 20 ～ 23 岁之间的学生的学号、姓名和年龄，并按年龄升序排序

2.找出年龄小于 23 岁、籍贯是湖南或湖北的学生的姓名和性别

3.找出籍贯为山西或河北，成绩为 90 分以上的学生的姓名、籍贯和成绩

4.找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩

5.查询没有选任何课程的学生的学号和姓名；找出选修了全部课程的学生的姓名；

 

实验步骤： 

1.打开datagrip进入mysql，创建一个Schema test。

在Schema中建立5张table表。以创建任课表(teaching)为例，如下图：

 

 mysql语句：

create table teaching
(
   CNO char(2),
   CLASS char(5),
   TNO char(5),
   SNUM tinyint 
);

创建表的格式:create table 表名(字段名1 数据类型,字段名2 数据类型);

2.把样例元组添加到表中：

以students为例，‘+’输入数据。

输入完成后点击DB保存。

3.添加完整性约束，设置主键和外键。

mysql语句：

（1）students

alter table students
   add constraint students_pk
      primary key (SNO);

 （2）courses

alter table courses
   add constraint courses_pk
      primary key (CNO);

 （3）enrolls

alter table enrolls
   add constraint enrolls_students_SNO_fk
      foreign key (SNO) references students (SNO);
alter table enrolls
   add constraint enrolls_courses_CNO_fk
      foreign key (CNO) references courses (CNO);
alter table enrolls
   add constraint enrolls_pk
      primary key (SNO, CNO);

（4）teachers

alter table teachers
   add constraint teachers_pk
      primary key (TNO);

 （5）teaching

alter table teaching
   add constraint teaching_courses_CNO_fk
      foreign key (CNO) references courses (CNO);
alter table teaching
   add constraint teaching_teachers_TNO_fk
      foreign key (TNO) references teachers (TNO);
alter table teaching
   add constraint teaching_pk
      primary key (CNO, CLASS);

 

4.从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ， 包括学生姓名、课程名和成绩。

输入以下语句并运行：

create view GRADE_TABLE as select SNAME,CNAME,GRADE from students,courses,enrolls where students.SNO=enrolls.SNO and  courses.CNO=enrolls.CNO;

运行后可以找到库中增加了views的文件夹，展开找到已经创建好了的grade_table

 

 

 

5.在学生表中按学号建立索引、再删除该索引

运行语句：

create unique index st on students(SNO);

 

删除索引：

可以在modify table中手动删除，也可以用语句：

drop index st on students;

6. 在教师表中增加住址列

语句：

alter table teachers add (addr char(20));

运行后在teachers表中观察到addr已经被创建出来。

7. 找出年龄在 20 ～ 23 岁之间的学生的学号、姓名和年龄，并按年龄升序排序

select SNO,SNAME,AGE
from  students
where AGE between 20 and 23 order by AGE;

运行结果：

 

 不加限制默认为ASC升序，可以指定为DESC降序：

select SNO,SNAME,AGE
from  students
where AGE between 20 and 23 order by AGE DESC;

 

8.找出年龄小于 23 岁、籍贯是湖南或湖北的学生的姓名和性别

可以通过谓词in增加限制条件，语法为 <属性> in （集合）

select SNAME,SEX
from  students
where AGE < 23 and BPLACE in ('湖北','湖南');

结果：

 

9.找出籍贯为山西或河北，成绩为 90 分以上的学生的姓名、籍贯和成绩

select SNAME,BPLACE,GRADE
from  students,enrolls
where students.SNO=enrolls.SNO and GRADE>=90 and BPLACE in ('山西','河北');

结果：

 

10.找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩

select CNO,AVG(GRADE)
from  enrolls
group by CNO having count(*)>=3;

 结果：

11.查询没有选任何课程的学生的学号和姓名；找出选修了全部课程的学生的姓名

（1）查询没有选任何课程的学生的学号和姓名：

（用嵌套查询比较方便）

select SNO,SNAME
from  students
where not exists(select * from enrolls where students.SNO=enrolls.SNO);

结果：

 

（2）找出选修了全部课程的学生的姓名

嵌套查询，enroll中找出某一个学生课程数量等于所有课程数量的学生学号。

select SNAME
from  students
where SNO in (select SNO from enrolls group by SNO having count(*)=(select count(*) from courses));

也可以用两层嵌套查询：

select SNAME from students
where not exists(
    select * from courses
    where not exists(
        select * from enrolls where enrolls.SNO = students.SNO and enrolls.CNO = courses.CNO
    )
)

运行结果：

 

 

 

由于都不存在，所以看不到选的结果，可以将enroll表中的第7行SNO改为990027，再运行代码。

结果：