基于数据库技术及应用实验：实验三 多表查询（2）

实验三 多表查询（2）

一、实验目的

掌握两表、三表连接查询；

掌握子查询。

二、实验内容(格式10分，每题8分)

完成下面题目，代码复制到报告中并将结果截图。

1.查询2003年出生的学生信息。

select id,sno,sname,ssex,classno,major,birthday,damdate ,stel,enscore

from tms_student

where tms_student.birthday like '%2003%';

  

2.查询软件工程和网络工程专业女生的学号、姓名和性别。

select tms_student.sname,tms_student.sno,tms_student.ssex

from tms_student

where  tms_student.ssex='女'and (tms_student.major='软件工程' or tms_student.major='网络工程');

3.查询学生选课情况，显示学号、姓名、课程编号

select tms_student.sno,tms_student.sname,tms_sc.cno

from  tms_student inner join tms_sc on tms_student.sno=tms_sc.sno;

4.查询学生选课情况，显示学号、姓名、课程名称

select tms_student.sno,tms_student.sname,tms_course.cname

from tms_student inner join tms_sc on tms_student.sno=tms_sc.sno inner join tms_course on tms_sc.cno=tms_course.cno;

5.查询选修数据库课成绩在80分以上学生的学号、姓名、课程名称及成绩。

select tms_student.sno,tms_student.sname,tms_course.cname,tms_sc.score

from tms_student inner join tms_sc on tms_student.sno=tms_sc.sno inner join tms_course on tms_sc.cno=tms_course.cno

where tms_course.cname = '数据库' and tms_sc.score > 80;

6.统计每个专业的学生数量，显示专业名称、学生数量

select major as 专业名称,count(tms_student.sno) as 学生数量

from tms_student

group by  tms_student.major;

7.统计学生数量多余5人的专业，显示专业名称、学生数量。

select major as 专业名称,count(tms_student.sno) as 学生数量

from tms_student

group by  tms_student.major

having 学生数量 >5;

8.按专业和班级汇总学生数量，显示专业名称、班级编号、学生数量。

select tms_student.major as 专业名称, tms_student.classno as 班级编号,count(tms_student.sno) as 学生数量

from tms_student

group by  tms_student.major,tms_student.classno;

9.统计每个教师任教课程数，显示教师编号、教师姓名和任教课程数。

select tms_teacher.tno as 教师编号,tms_teacher.tname as 教师姓名,count(tms_tc.cno) as 任课教程数

from tms_teacher inner join tms_tc on tms_teacher.tno=tms_tc.tno

group by tms_teacher.tno,tms_teacher.tname;

10.查询和学号为“21140001”的同学在同一专业的学生信息。

select *

from tms_student

Where tms_student.major=(select tms_student.major from tms_student where tms_student.sno='21140001');

三、实验中遇到的问题及解决方法、总结（10分，写无或空着得0分）

在做第7题的时候，题目要求统计学生数量多余5人的专业，我一开始是这样写的select major as 专业名称,count(tms_student.sno) as 学生数  from tms_student where tms_student.sno > 5;但运行后发现根本行不通，后面也是想到了先对专业进行分组，在此基础上用having再进行筛选，需要注意的是一般group by 和having是一起使用的。

在做第8题的时候，一开始确实不知道怎么按专业和班级汇总学生数量，后面稍微做下功课，发现我们其实可以先对专业和班级进行统计数量，然后再用group by 语句对专业和班级进行分组，这样不就解决了按专业和班级汇总学生数量的问题吗，第6，7，8，9题的主体思想都其实差不多。

在做第10题的话，因为上次实验二的时候遇到过，采用的是子查询的方法，在查询同一专业的基础上再查询学号为21140001即可。

---

四、附件（创建代码及数据）

创建代码：

drop database tms;
create database tms;
use tms;
create table tms_student(
id smallint unsigned not null unique auto_increment comment '序号',
sno char(13) not null comment '学号',
sname varchar(4) not null comment '姓名',
ssex enum('男','女') not null comment '性别',
classno char(4) not null comment '班级编号',
major varchar(10) not null comment '专业',
birthday date not null comment '出生日期',
damdate datetime not null default current_timestamp comment '入学时间',
enscore smallint(3) unsigned not null comment '入学成绩',
stel char(11) comment '手机号',
primary key(sno)
);

create table tms_course(
id smallint unsigned  not null unique auto_increment comment '序号',
cno char(5) not null comment '课程编码',
cname varchar(10) not null comment '课程名称',
crehour tinyint(3) unsigned not null comment '课时',
credit tinyint(1) unsigned not null comment '学分',
primary key(cno)
);

create table tms_teacher(
id smallint unsigned not null unique auto_increment comment '序号',
tno char(6) not null comment '教师编号',
tname varchar(30) not null comment '姓名',
tsex enum('男','女') not null comment '性别',
endate date not null comment '工作时间',
polstatus varchar(10) not null comment '政治面貌',
education varchar(5) not null comment '学历',
title varchar(5) not null comment '职称',
department varchar(10) not null comment '系别',
tel char(11) not null comment '电话',
email char(20) not null comment '邮箱',
primary key(tno)
);

create table tms_sc(
id smallint unsigned not null unique auto_increment comment '序号',
sno char(10) not null comment '学号', 
acayear varchar(20) not null comment '学年',
term enum('1','2') not null comment '学期',
cno char(5) not null comment '课程编码', #数据类型与父表参照字段一致
score tinyint(3) not null comment '成绩',
foreign key(sno) references tms_student(sno),
foreign key(cno) references tms_course(cno)
);

create table tms_tc(
id smallint unsigned not null unique auto_increment comment '序号',
cno char(5) not null comment '课程编码', 
tno char(6) not null comment '教师编号', 
teatime char(15) not null comment '授课时间',
teaplace varchar(10) not null comment '授课地点',
foreign key(cno) references tms_course(cno),
foreign key(tno) references tms_teacher(tno)
);

load data local infile 'd:\\tms_student.txt' into table tms_student;
load data local infile 'd:\\tms_course.txt' into table tms_course;
load data local infile 'd:\\tms_teacher.txt' into table tms_teacher;
load data local infile 'd:\\tms_sc.txt' into table tms_sc;
load data local infile 'd:\\tms_tc.txt' into table tms_tc;

导入数据：