实验7 数据查询（2）

2024-07-11 13:56:03
开发
24

一、实验目的

学习SQL语言的定义、操纵功能
熟悉通过SQL语言对数据库进行查询操作，包括单表查询、多表查询、嵌套查询、集合查询

二、实验软件

	MySQL

三、实验内容和要求

给定四个关联表，其定义和数据加载如下：

学生表 Student

create table Student(Sid varchar(6), Sname varchar(10), Sdate datetime, Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1999-01-01' , '男');
insert into Student values('02' , '钱电' , '1999-12-21' , '男');
insert into Student values('03' , '孙风' , '1999-05-20' , '男');
insert into Student values('04' , '李云' , '1999-08-06' , '男');
insert into Student values('05' , '周梅' , '2000-12-01' , '女');
insert into Student values('06' , '吴兰' , '2001-03-01' , '女');
insert into Student values('07' , '郑竹' , '1998-07-01' , '女');
insert into Student values('08' , '王菊' , '1999-01-20' , '女');

选课表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

课程表 Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

四张表之间的关联很简单：
在这里插入图片描述

试按以下操作要求完成SQL语言设计。

查询" 01 “课程比” 02 “课程成绩高的学生的信息及对应” 01 “和” 02 "课程分数。要求输出结构如下图所示：

SELECT s.Sid, s.Sname, s.Sdate,s.Ssex,sc1.score AS score_01, sc2.score AS score_02
FROM Student s
JOIN SC sc1 ON s.Sid = sc1.Sid AND sc1.Cid = '01'
JOIN SC sc2 ON s.Sid = sc2.Sid AND sc2.Cid = '02'
WHERE sc1.score > sc2.score;

查询平均成绩大于等于 60 分的学生信息，输出其编号Sid、姓名Sname和平均成绩Avg_score三列内容。

SELECT s.Sid, s.Sname, AVG(sc.score) AS Avg_score
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname
HAVING AVG(sc.score) >= 60;

3.查询在 SC 表存在成绩的学生信息，输出Sid、Sname、Sdate和Ssex四列内容。

SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid;

查询Student表中所有同学的学生编号Sid、学生姓名Sname和对应选课总数、所有课程的总成绩(没成绩的显示为 null )。要求输出结构如下图所示：

SELECT s.Sid, s.Sname, COUNT(sc.Cid) AS '选课总数', SUM(sc.score) AS '总成绩'
FROM Student s
LEFT JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname;

查询「李」姓老师的数量。

SELECT COUNT(*) FROM Teacher t WHERE t.Tname LIKE '李%';

6.查询学过「张三」老师授课的学生信息，输出其Sid、Sname、Sdate和Ssex四列内容。

SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
JOIN Course c ON sc.Cid = c.Cid
JOIN Teacher t ON c.Tid = t.Tid
WHERE t.Tname = '张三';

7.查询选课表SC内没有选修课程表Course里所列所有课程的学生信息，输出其Sid、Sname、Sdate和Ssex四列内容。

SELECT S.Sid, S.Sname, S.Sdate, S.Ssex
FROM Student S
WHERE S.Sid NOT IN (
    SELECT DISTINCT SC.Sid
    FROM SC
    WHERE SC.Cid NOT IN (
        SELECT C.Cid
        FROM Course C
    )
);

8.查询和" 01 "号学生学习的课程完全相同的其他学生信息，输出其学号Sid内容。

WITH CourseList_01 AS (
    SELECT Cid FROM SC WHERE Sid = '01'
)
SELECT DISTINCT s2.Sid
FROM Student s2
JOIN SC sc2 ON s2.Sid = sc2.Sid
WHERE sc2.Cid IN (SELECT Cid FROM CourseList_01)
GROUP BY s2.Sid
HAVING COUNT(DISTINCT sc2.Cid) = (SELECT COUNT(*) FROM CourseList_01)
AND s2.Sid != '01';

9.查询至少有一门课与学号为" 01 "的同学所学相同的学生信息，输出其Sid、Sname、Sdate和Ssex四列内容。

SELECT DISTINCT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
WHERE EXISTS (
    SELECT 1 
    FROM SC sc_ref
    WHERE sc_ref.Sid = '01' AND sc_ref.Cid = sc.Cid
)
AND s.Sid != '01';

10.查询没学过"张三"老师讲授的任一门课程的学生姓名。

SELECT s.Sname
FROM Student s
WHERE s.Sid NOT IN (
    SELECT sc.Sid
    FROM SC sc
    JOIN Course c ON sc.Cid = c.Cid
    JOIN Teacher t ON c.Tid = t.Tid
    WHERE t.Tname = '张三'
);

11.检索" 01 "课程分数小于 60并按分数降序排列的学生信息，输出其Sid、Sname、Sdate和Ssex四列内容。

SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
WHERE sc.Cid = '01' AND sc.score < 60
ORDER BY sc.score DESC;

按平均成绩从高到低显示学生的课程成绩以及平均成绩。要求输出结构如下图所示：

SELECT 
    s.Sid,
    MAX(CASE WHEN sc.Cid = '01' THEN sc.score ELSE NULL END) AS score_01,
    MAX(CASE WHEN sc.Cid = '02' THEN sc.score ELSE NULL END) AS score_02,
    MAX(CASE WHEN sc.Cid = '03' THEN sc.score ELSE NULL END) AS score_03,
    AVG(sc.score) AS 'avg(score)'
FROM 
    Student s
JOIN 
    SC sc ON s.Sid = sc.Sid
GROUP BY 
    s.Sid
HAVING 
    COUNT(sc.Cid) > 0  
ORDER BY 
    AVG(sc.score) DESC;

13.查询出只选修两门课程的学生学号和姓名两列信息

SELECT s.Sid, s.Sname
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname
HAVING COUNT(DISTINCT sc.Cid) = 2;

14.查询本月过生日的学生信息，输出其Sid、Sname、Sdate和Ssex四列内容。

SELECT Sid, Sname, Sdate, Ssex
FROM Student
WHERE MONTH(Sdate) = MONTH(CURRENT_DATE()) AND YEAR(Sdate) = YEAR(CURRENT_DATE());

四、实验出现的问题及解决方案

执行时间过长，尤其是涉及到大量数据的查询。
—— 尽量避免全表扫描，使用更精确的筛选条件。
在尝试进行一些复杂集合查询时，如查询选修了某一特定组合课程的学生，发现直接用SQL实现逻辑较为复杂且效率低下。

原文地址:https://blog.csdn.net/weixin_46186903/article/details/140346623 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1811278256155004928.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部

实验7 数据查询（2）

一、实验目的

二、实验软件

三、实验内容和要求

四、实验出现的问题及解决方案

相关推荐

最近更新

热门阅读