文理学院数据库技术应用实验报告9

文理学院数据库技术应用实验报告9

实验名称	数据库子查询	实验日期	2024年11月15日
课程名称	数据库技术应用	实验项目	数据库子查询的使用

一、实验目的

要求，通过sql脚本导入数据库，并在stuinfo数据库中对数据进行查询。使用select子查询进行查询操作。

stuinfo数据库脚本如下：

create database stuinfo;
USE stuinfo;
CREATE TABLE student (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sNo CHAR(10) NOT NULL,
sName VARCHAR(20) NOT NULL,
sex CHAR(2),
birthday DATE,
deptName VARCHAR(30),
remark VARCHAR(80)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE course (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cNo CHAR(5) NOT NULL,
cName VARCHAR(30) NOT NULL,
credit TINYINT UNSIGNED,
remark VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE score (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sId INT UNSIGNED NOT NULL,
cId INT UNSIGNED NOT NULL,
grade TINYINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO student (id, sNo, sName, sex, birthday, deptName, remark) VALUES
(1, '1308013101', '陈斌', '男', '1993-03-20', '软件131', NULL),
(2, '1308013102', '张洁', '女', '1996-02-08', '软件131', NULL),
(3, '1308013103', '郑先超', '男', '1994-04-25', '软件131', NULL),
(4, '1308013104', '徐孝兵', '男', '1994-08-06', '软件131', NULL),
(5, '1308013105', '王群', '女', '1995-03-27', '软件131', NULL),
(6, '1309122501', '刘威', '男', '1994-07-13', '网络131', NULL),
(7, '1309122502', '沈雁斌', '男', '1994-05-28', '网络131', NULL),
(8, '1309122503', '杨群', '女', '1995-10-18', '网络131', NULL),
(9, '1309122504', '蒋维维', '男', '1994-10-19', '网络131', NULL),
(10, '1309122505', '杨璐', '女', '1995-09-26', '网络131', NULL),
(11, '1312054901', '王林林', '男', '1994-04-16', '机电131', NULL),
(12, '1312054902', '杨一超', '男', '1994-08-27', '机电131', NULL),
(13, '1312054903', '张伟', '男', '1995-01-03', '机电131', NULL),
(14, '1312054904', '田翠萍', '女', '1994-10-20', '机电131', NULL),
(15, '1312054905', '周伟', '男', '1995-09-10', '机电131', NULL);

INSERT INTO course (id, cNo, cName, credit, remark) VALUES
(1, '01001', 'C语言程序设计', 5, '计算机类专业课程'),
(2, '01002', '数据结构', 4, '计算机类专业课程'),
(3, '01003', 'Java程序设计', 4, '计算机类专业课程'),
(4, '02001', '网络基础', 3, '计算机类专业课程'),
(5, '02002', '数据库原理及应用', 4, '计算机类专业课程'),
(6, '02003', '操作系统', 4, '计算机类专业课程'),
(7, '09001', '机械设计基础', 5, NULL),
(8, '09002', '机械制造基础', 4, NULL),
(9, '09003', '机械制图', 4, NULL);


INSERT INTO score (id, sId, cId, grade) VALUES
(1, 1, 1, 72),
(2, 1, 2, 56),
(3, 1, 3, 77),
(4, 2, 1, 85),
(5, 2, 2, 73),
(6, 2, 3, 90),
(7, 3, 1, 79),
(8, 4, 1, 82),
(9, 5, 1, 63),
(10, 6, 4, 84),
(11, 6, 5, 92),
(12, 6, 6, 71),
(13, 11, 7, 87),
(14, 11, 8, 90),
(15, 11, 9, 95);

二、实验原理

Select子查询

三、实验设备、材料

mysql和Navicat软件

四、实验步骤

1、查询课程编号（cno在course表），课程名称（cname在course表）及对应的成绩（grade在score表）

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;

SELECT 
    course.cNo, 
    course.cName, 
    score.grade
FROM 
    course
JOIN 
    score ON course.id = score.id;

2、查询学生姓名(sname在student表)、课程名称(cname在course表)及对应的成绩(grade在score表)；

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;

SELECT 
    student.sName, 
    course.cName, 
    score.grade
FROM 
    score
JOIN 
    student ON score.id = student.id
JOIN 
    course ON score.id = course.id;

3、查询选修了’计算机类专业课程（remark）’学生姓名(cName)，学生编号，课程名称及对应成绩

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;

SELECT 
    st.sName,
    st.sNo,
    co.cName,
    sc.grade
FROM 
    score sc
JOIN 
    student st ON st.id = sc.sId
JOIN 
    course co ON co.id = sc.cId
WHERE 
    co.remark = '计算机类专业课程';

4、查询在1994年后出生的学生姓名，出生日期、及成绩；

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;

SELECT student.sName,student.birthday,score.grade
FROM student JOIN score ON student.id=score.sId
WHERE YEAR(student.birthday) >'1994';

5、查询网络131班学生学号，姓名，班级 ，课程id和成绩；

SELECT student.sNo,student.sName,student.deptName
FROM score JOIN student ON score.sId = student.id
WHERE student.deptName = '网络131';

子查询1：

1、查询成绩最低分及对应学生姓名和课程名称

SELECT student.sName,course.cName,score.grade
FROM score JOIN student ON student.id=score.sId
JOIN course ON course.id=score.sId
WHERE score.grade=(SELECT min(grade)FROM score);

2、查询与学号sno=1308013101的同学，在同一个班级的学生名单。

SELECT * FROM student
WHERE deptname = (SELECT deptname FROM student WHERE sno = '1308013101');

3、查询选修了01001课程且成绩高于该课程平均分的信息：学生学号，姓名，班级，课程编号、名称和成绩。

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;

SELECT 
    student.sNo,
    student.sName,
    student.deptName,
    course.cNo,
    course.cName,
    score.grade
FROM 
    score
JOIN 
    student ON student.id=score.sId 
JOIN 
    course ON course.id=score.cId
WHERE 
    course.cNo = '01001' 
    AND score.grade > (SELECT AVG(grade) FROM score WHERE cNo = '01001');

子查询2：

1、查询选修了课程编号con=01001的学生名单。

SELECT * FROM student WHERE id in(
SELECT sid FROM score JOIN course
on cid=course.id WHERE cno='01001')

2、查询学号=1308013101 的学生选修的课程信息

SELECT 
    course.*
FROM 
    course
JOIN 
    score ON course.id = score.cId
JOIN 
    student ON score.sId = student.id
WHERE 
    student.sNo = '1308013101';

3、查询没有选修课的女同学名单。

SELECT * FROM student WHERE sex='女' AND
id not in (SELECT sid FROM score);

4、查询有选修课的女同学名单

SELECT * FROM student WHERE sex='女' AND
EXISTS ( SELECT * FROM score WHERE student.id=sid);

5、查询比1308013101同学任何科目分低的全部学生、课程、成绩信息。

SELECT DISTINCT 
    s2.sName AS studentName,
    c2.cName AS courseName,
    sc2.grade AS Grade
FROM 
    student s2
JOIN 
    score sc2 ON s2.id = sc2.sId
JOIN 
    course c2 ON sc2.cId = c2.id
WHERE 
    EXISTS (
        SELECT 1 
        FROM score sc1 
        JOIN student s1 ON sc1.sId = s1.id
        WHERE s1.sNo = '1308013101'
          AND sc1.cId = sc2.cId
          AND sc1.grade > sc2.grade
    );

6、查询比1308013101所有成绩低的全部学生、课程、成绩信息。

SELECT 
    s.sName AS StudentName, 
    c.cName AS CourseName, 
    sc.grade AS Grade
FROM 
    student s
JOIN 
    score sc ON s.id = sc.sId 
JOIN 
    course c ON sc.cId = c.id
WHERE 
    s.sNo <> '1308013101'
    AND NOT EXISTS (
        SELECT 1
        FROM score sc_A
        WHERE sc_A.sId = (SELECT id FROM student WHERE sNo = '1308013101')
          AND sc_A.cId = sc.cId
          AND sc_A.grade <= sc.grade
    );