sql where实现除法的语句执行过程

sql where实现除法的语句执行过程

• 环境：mysql 5.7

数据库中没有提供全称量词的操作，需要使用否定存在量词实现全称量词的效果以实现数据库除法操作。

否定存在量词sq:lwhere not exists

Schema (MySQL v5.7)

create table student(
  sno int,
  sname varchar(20),
  primary key(sno)
  );
  
 create table course(
   cno int,
   cname varchar(20),
   primary key(cno)
   );
   
 create table sc(
   sno int,
   cno int,
   grade int
  );
  
alter table sc
add primary key(sno,cno);

---

Query #1

insert into student values
(121,'leo'),
(122,'jack'),
(123,'alice'),
(124,'bob');

There are no results to be displayed.

---

Query #2

insert into course values
(1,'database'),
(2,'math'),
(3,'it'),
(4,'c');

There are no results to be displayed.

---

Query #3

insert into sc values
(121,1,99),
(121,2,90),
(121,3,88),
(121,4,87),
(122,2,90),
(122,3,98);

There are no results to be displayed.

---

Query #4

select * from student;

sno	sname
121	leo
122	jack
123	alice
124	bob

---

Query #5

select * from course;

cno	cname
1	database
2	math
3	it
4	c

---

Query #6

select * from sc;

sno	cno	grade
121	1	99
121	2	90
121	3	88
121	4	87
122	2	90
122	3	98

---

• 查询选修了所有课程的学生姓名

Query #7

select sname
from student
where not exists
	( select * 
     from course
     where not exists
     	( select *
         from sc
         where sno=student.sno
         and cno=course.cno));

sname
leo

---

• 查询选修了所有课程的学生学号

Query #8

select sno
from sc sc1
where not exists
	( select * 
     from course
     where not exists
     	( select *
         from sc
         where sno=sc1.sno
         and cno=course.cno));

sno
121
121
121
121

---

View on DB Fiddle

运行流程分析：

运行流程可以用c语言三层循环实现。
query #7分析：

设i,j,k三个变量，分别代表三个表，
三层循环嵌套执行，若第三层层循环执行完只要有一个true,则返回第二层false,若第二层全为false,则第一层为true，表示输出此元组，进而一直迭代下去，得到最终结果。

所以 query #8 有四条结果，可以使用distinct去重。