原始数据的建表语句:
create table if not exists student
(
SNO varchar(20) primary key,
SNAME varchar(20) character set gbk,
AGE int,
SEX char(2) character set gbk CHECK(SEX IN('男','女'))
);
insert into student values('1','李强',23,'男');
insert into student values('2','刘丽',22,'女');
insert into student values('5','张友',22,'男');
create table if not exists course
(
CNO varchar(20) primary key,
CNAME varchar(20) character set gbk,
TEACHER varchar(20) character set gbk
);
insert into course values('K1','C语言','王华');
insert into course values('K5','数据库原理','程军');
insert into course values('K8','编译原理','程军');
create table if not exists sc
(
SNO varchar(20) NOT NULL,
CNO varchar(20) NOT NULL,
SCORE int NOT NULL,
primary key (SNO,CNO),
foreign key (SNO) references student(SNO),
foreign key (CNO) references course(CNO)
);
insert into sc values('1','K1',83);
insert into sc values('2','K1',85);
insert into sc values('5','K1',92);
insert into sc values('2','K5',90);
insert into sc values('5','K5',84);
insert into sc values('5','K8',80);
检索至少选修"程军"老师所授全部课程的学生姓名(SNAME);
本题的另外一种说法就是,检索选修了“程军”老师所授全部课程的学生姓名.
本查询可以理解为:查询这样一些学生,没有一门课程是他不选修的。
select sname from student
where not exists
(
select * from course
where teacher='程军' and not exists
(
select * from sc
where sc.sno=student.sno and sc.cno=course.cno
)
);
这里的检索结果是:
+-------+
| sname |
+-------+
| 张友 |
+-------+
1 row in set (0.00 sec)
参考:
1、SQL语句大全(包含用两个NOT EXISTS表肯定的SQL语句):https://blog.csdn.net/qq_41734797/article/details/92803538
2、【MySQL】数据库原理复习——SQL语言:https://www.cnblogs.com/linjiaxin/p/7747944.html
网友评论