数据库原理及应用mysql版陈业斌实验三
🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客
🌅主页：猫咪-9527-CSDN博客“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”
目录
实验三多表查询
1.实验数据如下
student 表（学生表）
course 表（课程表）
teacher 表（教师表）
score 表（成绩表）
2. 插入数据
student 表中的数据
course 表中的数据
teacher 表中的数据
score 表中的数据
3-1 查询选修了严敏老师的数学分析课程的学生的姓名、课程名、教师名和成绩。
等值连接查询：
自然连接查询：
自身连接查询：
3-2 查询年龄大于刘东明的年龄的所有学生的姓名与出生日期。
自身连接查询：
子查询：
3-3 查询未选修任何课程的学生的学号和姓名。
外连接查询：
子查询：
3-4 查询比数学系中全体学生年龄大的学生的姓名和系别。
子查询
子查询
3-5 相关子查询EXISTS 查询选修了004号课程的学生的姓名和系别。
3-6 相关子查询NOT EXISTS 查询选修了全部课程的学生的学号。
3-7 相关子查询 NOT EXISTS 查询选修了刘东明同学选修的全部课程的学生的学号。
实验三多表查询
实验目的：
通过实验掌握数据库系统单表查询的方法
1.实验数据如下
student 表（学生表）
CREATE TABLE student (sno CHAR(5) PRIMARY KEY,snme VARCHAR(20) NOT NULL, sdept VARCHAR(20) NOT NULL, sclass CHAR(2) NOT NULL, ssex CHAR(1), birthday DATE, totalcredit DECIMAL(4,1)
);
course 表（课程表）
CREATE TABLE course (cno CHAR(3) PRIMARY KEY,cname VARCHAR(50), ctime DECIMAL(3,0), credit DECIMAL(3,1)
);
teacher 表（教师表）
CREATE TABLE teacher (tno CHAR(6) PRIMARY KEY, tname VARCHAR(20), tsex CHAR(1), tdept VARCHAR(20)
);
score 表（成绩表）
CREATE TABLE score (sno CHAR(5), cno CHAR(3), tno CHAR(6), grade DECIMAL(5,1), PRIMARY KEY (sno, cno, tno), CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno),CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno)
);
2. 插入数据
student 表中的数据
INSERT INTO student VALUES('96001', '马小燕', '计算机', '01', '女', '2000/01/02', 0);
INSERT INTO student VALUES('96002', '黎明', '计算机', '01', '男', '2000/03/05', 0);
INSERT INTO student VALUES('96003', '刘东明', '数学', '01', '男', '2000/10/05', 0);
INSERT INTO student VALUES('96004', '赵志勇', '信息', '02', '男', '2000/08/08', 0);
INSERT INTO student VALUES('97001', '马蓉', '数学', '02', '女', '2001/03/04', 0);
INSERT INTO student VALUES('97002', '李成功', '计算机', '01', '男', '2001/09/10', 0);
INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0);
INSERT INTO student VALUES('97004', '李丽', '计算机', '02', '女', '2002/01/05', 0);
INSERT INTO student VALUES('96005', '司马志明', '计算机', '02', '男', '2001/11/23', 0);
course 表中的数据
INSERT INTO course VALUES('001', '数学分析', 64, 4);
INSERT INTO course VALUES('002', '普通物理', 64, 4);
INSERT INTO course VALUES('003', '微机原理', 56, 3.5);
INSERT INTO course VALUES('004', '数据结构', 64, 4);
INSERT INTO course VALUES('005', '操作系统', 56, 3.5);
INSERT INTO course VALUES('006', '数据库原理', 56, 3.5);
INSERT INTO course VALUES('007', '编译原理', 48, 3);
INSERT INTO course VALUES('008', '程序设计', 32, 2);
teacher 表中的数据
INSERT INTO teacher VALUES('052501', '王成刚', '男', '计算机');
INSERT INTO teacher VALUES('052502', '李正科', '男', '计算机');
INSERT INTO teacher VALUES('052503', '严敏', '女', '数学');
INSERT INTO teacher VALUES('052504', '赵高', '男', '数学');
INSERT INTO teacher VALUES('052505', '刘玉兰', '女', '计算机');
INSERT INTO teacher VALUES('052506', '王成刚', '男', '信息');
INSERT INTO teacher VALUES('052507', '马悦', '女', '计算机');
score 表中的数据
INSERT INTO score VALUES('96001', '001', '052503', 77.5);
INSERT INTO score VALUES('96001', '003', '052501', 89);
INSERT INTO score VALUES('96001', '004', '052502', 86);
INSERT INTO score VALUES('96001', '005', '052505', 82);
INSERT INTO score VALUES('96002', '001', '052504', 88);
INSERT INTO score VALUES('96002', '003', '052502', 92.5);
INSERT INTO score VALUES('96002', '006', '052507', 90);
INSERT INTO score VALUES('96005', '004', '052502', 92);
INSERT INTO score VALUES('96005', '005', '052505', 90);
INSERT INTO score VALUES('96005', '006', '052505', 89);
INSERT INTO score VALUES('96005', '007', '052507', 78);
INSERT INTO score VALUES('96003', '001', '052504', 69);
INSERT INTO score VALUES('97001', '001', '052504', 96);
INSERT INTO score VALUES('97001', '008', '052505', 95);
INSERT INTO score VALUES('96004', '001', '052503', 87);
INSERT INTO score VALUES('96003', '003', '052501', 91);
INSERT INTO score VALUES('97002', '003', '052502', 91);
INSERT INTO score VALUES('97002', '004', '052505', NULL);
INSERT INTO score VALUES('97002', '006', '052507', 92);
INSERT INTO score VALUES('97004', '005', '052502', 90);
INSERT INTO score VALUES('97004', '006', '052501', 85);
注：把上面的实验数据添加上再开始实验。
3-1 查询选修了严敏老师的数学分析课程的学生的姓名、课程名、教师名和成绩。
-
等值连接查询：
select snme,cname,tname,grade from student
join score on score.sno=student.sno
join teacher on score.tno=teacher.tno
join course on score.cno=course.cno where tname='严敏' and cname='数学分析';
-
自然连接查询：
select snme,cname,tname,grade from student natural join course
natural join score natural join teacher where tname='严敏' and cname='数学分析';
-
自身连接查询：
select a.snme,a.cname,a.tname,a.grade from
(select snme,cname,tname,grade from student natural join course
natural join score natural join teacher where tname='严敏') a join
(select snme,cname,tname,grade from student natural join course
natural join score natural join teacher where cname='数学分析')b on a.snme=b.snme ;
3-2 查询年龄大于刘东明的年龄的所有学生的姓名与出生日期。
自身连接查询：
select b.snme,b.birthday from student a
join student b on b.birthday<a.birthday
where a.snme='刘东明' ;
-
子查询：
select snme,birthday from student where
birthday<(select birthday from student where snme='刘东明');
3-3 查询未选修任何课程的学生的学号和姓名。
外连接查询：
select student.sno,snme from student left join score on
student.sno=score.sno where score.sno is null;
子查询：
select b.sno,b.snme from
(select student.sno,snme from student left join score on student.sno=score.sno where score.sno is null) b;
3-4 查询比数学系中全体学生年龄大的学生的姓名和系别。
-
子查询<ALL：
select snme,sdept from student where
birthday < (select min(birthday) from student where sdept='数学');
-
子查询<MIN：
select snme,sdept from student where
birthday < all (select birthday from student where sdept='数学');
3-5 相关子查询EXISTS 查询选修了004号课程的学生的姓名和系别。
select snme,sdept from student a where exists
(select *from score where cno='004' and a.sno=sno);
3-6 相关子查询NOT EXISTS 查询选修了全部课程的学生的学号。
select sno from student where not exists
(select 1 from course where not exists
(select 1 from score where student.sno=sno and cno=course.cno));
3-7 相关子查询 NOT EXISTS 查询选修了刘东明同学选修的全部课程的学生的学号。
select sno from student a where snme<>'刘东明' and
not exists( select cno from (select *from student natural join score where snme='刘东明') b
where not exists(select 1 from score where a.sno=sno and b.cno=cno));