关系数据库设计及数据库标准语言SQL（多表查询）

最新推荐文章于 2022-06-18 15:25:12 发布

K_soo

最新推荐文章于 2022-06-18 15:25:12 发布

阅读量2.2k

点赞数 6

分类专栏： database 文章标签：数据库 sql database

本文链接：https://blog.csdn.net/qq_53474200/article/details/121337150

版权

database 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

实验原理、内容及实验步骤：
实验原理
一、SQL查询的基本语法
select 语句是SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称之为结果集（result set）。
SQL语句的语法：
【语句格式】
SELECT [ ALL | DISTINCT ] select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition]
[ ORDER BY order_expression [ASC | DESC] ]
[limit count]

二、实验内容及步骤
1、在navicate中完成以下表的创建和数据的插入操作。
/创建大学数据库/
CREATE DATABASE college default character set utf8 collate utf8_general_ci;

/使用大学数据库/
use college;

/创建学生表/
CREATE table student(
studentNo char(12) comment ‘学号’,
sname char(8) comment ‘姓名’,
sex char(2) comment ‘性别’,
birthdate date comment ‘出生日期’,
entrance int comment ‘入学成绩’,
phone char(11) comment ‘电话号码’,
email VARCHAR(20) comment ‘邮件号码’,
departmentname varchar(20) comment ‘所在系名’
);

/课程表/
CREATE table course(
courseno char(6) comment ‘课程号’,
cname varchar(20) comment ‘课程名’,
type char(8) not null comment ‘课程类型’,
cpno char(6) comment ‘先行课程’,
period int(2) not null comment ‘总学时’,
exp int(2) not null comment ‘实验学时’,
term int(2) not null comment ‘开课学期’,
primary key(courseno)
);

/成绩表/
CREATE table score(
studentno char(12) comment ‘学号’,
courseno char(6) comment ‘课程号’,
daily float(3,1) default 0 comment ‘平时成绩’,
final float(3,1) default 0 comment ‘期末成绩’,
primary key(studentno,courseno)
)

/教师表/
CREATE TABLE teacher(
teacherno char(6) not null comment’教师号’,
tname char(8) not null comment ‘教师姓名’,
major char(10) comment ‘专业’,
prof char(10) comment ‘职称’,
department char(16) comment ‘部门’,
primary key(teacherno)
);

/teach_course表/
CREATE TABLE teach_course
(teacherno char(6) not null,
courseno char(6) not null,
teachtime datetime,
primary key(teacherno,courseno,teachtime)
);

/学生表数据插入/
INSERT into student values
(‘18122221324’,‘何白露’,‘女’,‘2001/12/4’,879,‘13786112345’,‘heyy@sina.com’,‘机电院’),
(‘18125111109’,‘敬横江’,‘男’,‘2000/3/1’,789,‘15678945623’,‘jing@sina.com’,‘计数院’),
(‘19125121107’,‘王伟’,‘男’,‘1999/9/12’,790,‘13786188347’,‘3267888@QQ.com’,‘计数院’),
(‘19137156732’,‘吴英’,‘女’,‘2000/10/19’,888,‘13055568618’,‘17865@QQ.com’,‘经管院’);

/课程表数据插入/
insert into course VALUES
(‘c05103’,‘电子技术’,‘选修’,null,48,16,2),
(‘c05107’,‘程序设计基础’,‘必修’,null,48,16,4),
(‘c05109’,‘C语言’,‘必修’,‘c05108’,48,16,4),
(‘c05127’,‘Java语言’,‘必修’,‘c05109’,64,16,4),
(‘c05138’,‘软件工程’,‘必修’,‘c05109’,48,8,5),
(‘c05124’,‘经济学’,‘必修’,null,64,16,4),
(‘c05108’,‘数据库原理’,‘必修’,‘c05107’,48,16,3),
(‘c05222’,‘会计实务’,‘必修’,null,48,8,2),
(‘c05223’,‘UML_SOFT’,‘选修’,‘c05224’,48,8,2),
(‘c05224’,‘UML设计’,‘选修’,‘c05138’,64,8,2);

/成绩表数据插入/
insert into score VALUES
(‘18122221324’,‘c05103’,87.0,92.0),
(‘18122221324’,‘c05109’,85.0,90.0),
(‘18122221324’,‘c05127’,95.0,93.0),
(‘18125111109’,‘c05127’,91.0,88.0),
(‘18125111109’,‘c05138’,80.0,80.0),
(‘18125111109’,‘c05108’,76.0,78.0),
(‘19125121107’,‘c05108’,88.0,72.0),
(‘19125121107’,‘c05127’,81.0,76.0),
(‘19125121107’,‘c05138’,75.0,74.0),
(‘19137156732’,‘c05124’,89.0,88.0),
(‘19137156732’,‘c05222’,81.0,79.0);

/教师表数据插入/
insert into teacher values
(‘t05001’,‘苏超然’,‘软件工程’,‘教授’,‘计数院’),
(‘t05002’,‘常斌’,‘会计学’,‘助教’,‘经管院’),
(‘t05003’,‘孙石安’,‘网络安全’,‘教授’,‘计数院’),
(‘t05011’,‘卢先’,‘软件工程’,‘副教授’,‘计数院’),
(‘t05013’,‘李明’,‘机械制造’,‘讲师’,‘机电院’),
(‘t05014’,‘王石’,‘软件工程’,NULL,‘计数院’);

/教师授课表数据插入/
insert into teach_course values
(‘t05001’,‘c05109’,‘2021-02-08 10:00:00’),
(‘t05001’,‘c05127’,‘2021-03-09 8:00:00’),
(‘t05001’,‘c05109’,‘2021-03-10 14:00:00’),
(‘t05003’,‘c05124’,‘2021-03-11 16:00:00’),
(‘t05011’,‘c05127’,‘2021-03-13 16:00:00’),
(‘t05011’,‘c05127’,‘2021-03-15 10:00:00’);

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM teacher;
SELECT * FROM teach_course;

二、按要求完成以下1-10题的查询，在每道题目的下面粘贴好SQL语句及运行效果截图。完成其中的12道题及格，16道题良好，20道题优秀。
/1、找出至少一门课程的期末成绩在90分以上的女学生的姓名。/

SELECT DISTINCT sname
FROM student INNER JOIN score 
ON student.studentno = score.studentno
WHERE sex = '女' and final>90;

/2、查询选修课程名为Java语言课程且期末成绩在90分以上的学生的学号。/

SELECT studentno
From score INNER JOIN course
ON score.courseno = course.courseno
WHERE final > 90 AND cname = 'Java语言';

/3、查询选修课程号为c05103的学生的学号、姓名和期末成绩/

SELECT student.studentNo,sname,final
FROM student,score
WHERE student.studentno = score.studentno and courseno = 'c05103';

在这里插入图片描述
/4、统计选课门数超过两门的学生的学号及姓名/

SELECT student.studentno,sname
FROM student,score
WHERE student.studentno = score.studentno
GROUP BY score.studentno
having count(*)>=3;

/5、查询选修了姓“苏”的老师的课程的学生的学号。/

SELECT DISTINCT score.studentno
FROM teacher,teach_course,score
WHERE teacher.teacherno = teach_course.teacherno AND teach_course.courseno = score.courseno AND tname LIKE '苏%'

/6、求每个学生所选修课程的期末平均分，要求显示学生的学号，姓名，及所选修课程的期末平均成绩（期末平均成绩作为别名显示，且运用round函数使期末平均成绩保留一位小数），并按期末平均成绩从高到低排序。/

SELECT student.studentno,sname,round(avg(final),1) as '期末平均成绩'
FROM student,score
WHERE student.studentno = score.studentno
group by score.studentno
order by avg(final) desc;

/7、查询19级学生的学号、姓名、课程名、期末成绩及学分（要求以别名学分显示所求学分，其中学分等于总学时除以16）/

SELECT student.studentno,sname,cname,final,round(period/16,1)
FROM score join student on student.studentno = score.studentno
           join course on course.courseno = score.courseno
where student.studentno like '19%';

/8、查询与“王伟”在同一个系学习的学生的基本信息/

SELECT *
FROM student
WHERE departmentname IN(
                   SELECT departmentname
                   FROM student 
                   WHERE sname = '王伟');

/9、查询选修了“数据库原理"课程的学生的学号和姓名/

SELECT studentno
FROM student
WHERE EXISTS(
             SELECT *
             FROM score
             WHERE EXISTS(
                          SELECT * 
                          FROM course
                          WHERE score.courseno = course.courseno AND cname = '数据库原理') AND student.studentno = score.studentno);

/10、查询期末成绩大于等于90分、总评成绩高于85分的学生的学号、课程号和总评成绩（要求以别名总评成绩显示总评成绩，其中总评成绩=平时成绩30%+期末成绩70%)/

SELECT student.studentno,score.courseno,0.2*daily+0.8*final as  总评成绩
FROM student,score
where student.studentno = score.studentno and final>=90 and 0.2*daily+0.8*final>85;

/11、查询期末成绩比选修课程平均期末成绩低的学生的姓名、课程号和期末成绩/

SELECT student.studentno,sname,courseno,final
FROM student,score as A
WHERE student.studentno = A.studentno
  AND final < (SELECT AVG(final)
               FROM score B
               WHERE A.studentno = B.studentno
               GROUP BY B.studentno);

/12、查询期末成绩中含有高于90分的学生的学号、姓名、电话及课程名/

SELECT student.studentno,sname,phone,cname
FROM (SELECT *
      FROM score
      WHERE final>90) as T,student,course
WHERE student.studentno = T.studentno and T.courseno = course.courseno;

/13、查找score表中所有比c05103课程期末成绩都高的学生的学号、姓名和期末成绩/

SELECT student.studentno,sname,final
FROM student,score 
WHERE student.studentno = score.studentno and final  > ALL (SELECT final
                                                            FROM score
                                                            WHERE courseno = 'c05103');

/14、查询每一课程的间接先行课(即先行课的先行课)。/

SELECT A.courseno,B.cpno 
FROM course as A,course as B
WHERE A.cpno = B.courseno;

/15、找出每个学生期末成绩超过他选修课程期末平均成绩的学生的学号及课程号/

SELECT studentno,courseno
FROM score A
where final > (SELECT AVG(final)
               FROM score B
               WHERE A.studentno = B.studentno);

/16、查询年龄低于所有计数院学生的学生姓名、所在系、年龄。/

select sname,departmentname,year(now())-year(birthdate) as '年龄'
from Student
where year(now())-year(birthdate)<=all(select year(now())-year(birthdate)
                          from Student
                          where departmentname='计数院') and departmentname<>'计数院';

/17、查询选修了全部课程的学生姓名。/

SELECT sname
FROM student
WHERE NOT EXISTS(
                 SELECT *
                 FROM course
                 WHERE NOT EXISTS (
                                   SELECT *
                                   FROM score
                                   WHERE course.courseno = score.courseno
                                   AND score.studentno = student.studentNo));

/18、查询至少选修了学生18125111109选修的全部课程的学生学号,注意查询结果中不应该含有18125111109学生/

SELECT DISTINCT studentno
FROM score AS A
WHERE NOT EXISTS(
                 SELECT *
                 FROM score AS B
                 WHERE studentno = '18125111109' AND NOT EXISTS(
                                                  SELECT *
                                                  FROM score AS C 
           WHERE B.courseno = C.courseno
           AND C.studentno = A.studentno)) and studentno <>'18125111109';

/19、查询2021年3月份苏姓老师的授课信息，包括教工号，教师姓名，课程名，授课时间。/

select teacher.teacherno,tname,cname,teachtime
from teacher,course,teach_course
where teacher.teacherno = teach_course.teacherno and  teach_course.courseno = course.courseno
and year(teachtime)='2021' AND month(teachtime)='3' and tname like '苏%';

/20、查询没有学生选修的课程的课程号/

SELECT course.courseno
From course
LEFT OUTER JOIN score
ON course.courseno = score.courseno
WHERE studentno IS NULL;

/21、查询计数院所有老师（含没有授课）的姓名和所授所有课程的平均分（期末成绩），并根据平均分从高到低的顺序排列。/

select tname,courseno,avg(final)  from teacher left JOIN (select teacherno,final,score.courseno from teach_course,score 
where teach_course.courseno=score.courseno) a on teacher.teacherno=a.teacherno and department='计数院' group by a.teacherno,tname,courseno  
order by avg(final) desc

/22、查询苏超然教授的每一门课程的成绩已经该门课程的平均分（期末成绩）/

select * from 
(select score.courseno,studentno,final  from teacher,teach_course,score 
where teacher.teacherno=teach_course.teacherno and teach_course.courseno=score.courseno and tname='苏超然' 
union
select score.courseno,'小计',avg(final)  from teacher,teach_course,score 
where teacher.teacherno=teach_course.teacherno and teach_course.courseno=score.courseno and tname='苏超然' group by courseno
) a order BY courseno,studentno

/23、列出所有老师的信息以及他所受课程的编号，如果要显示课程名称呢？如果还要加上这门课程的平均分（期末成绩）呢？/

select teacher.*,courseno,cname,avg(final)  from teacher left join (select cname,period,teacherno,final,score.courseno  from course,teach_course,score 
where course.courseno=score.courseno and teach_course.courseno=course.courseno) a on teacher.teacherno=a.teacherno group by teacher.teacherno,a.courseno,cname;

/24、查询不同职称老师所授课程的总学分和平均分（期末成绩）/

select prof,sum(period),avg(final) from teacher left join (select period,teacherno,final,score.courseno  from course,teach_course,score 
where course.courseno=score.courseno and teach_course.courseno=course.courseno) a on teacher.teacherno=a.teacherno group by prof;

/25、列出不同学院老师所授课程的总学分和平均分（期末成绩）/

select department,sum(period),avg(final) from teacher left join (select period,teacherno,final,score.courseno  from course,teach_course,score 
where course.courseno=score.courseno and teach_course.courseno=course.courseno) a on teacher.teacherno=a.teacherno group by department;

/26、列出每一位同学的必修课总学分、选修课总学分、所有课程的平均分（期末成绩）/

select studentno,sum(period*if(type='选修',1,0)) as 选修课总学分,sum(period*if(type='选修',0,1)) as 必修课总学分,avg(final) as 平均分  from course,score
 where score.courseno=course.courseno group by studentno;

K_soo

关注

6
点赞
踩
29

收藏

觉得还不错? 一键收藏
1
评论
关系数据库设计及数据库标准语言SQL（多表查询）

实验原理、内容及实验步骤：实验原理一、SQL查询的基本语法select 语句是SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称之为结果集（result set）。SQL语句的语法：【语句格式】SELECT [ ALL | DISTINCT ] select_listFROM table_source[ WHERE search_condition ][ GROUP BY group_by_expr
复制链接

扫一扫