SQL 学习笔记<四> MySQL嵌套查询，集合操作

嵌套查询

SELECT Sname FROM Student WHERE Sdept IN (“Computer”, ”Management”); //括号里的斜体表是一个小的集合，如果把括号里面的集合换成一个查询语句就变成嵌套查询了

SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = ‘1024’); //变成嵌套集合了

嵌套操作符(=, <, <=, >, >=, <>)：

x IN S	如果x是S的成员，则返回真，否则返回假
x > S	如果x与S中的唯一元素满足>条件，则返回真，否则返回假
x < ALL S	如果x与S中的所有元素满足>条件，则返回真，否则返回假
x >= SOME S	如果x与S中的某些(某个)元素满足>条件，则返回真，否则返回假
EXISTS S	集合S为非空集，则返回真，否则返回假
NOT EXISTS S	集合S为空集，则返回真，否则返回假

 

IN

SELECT Sno, Sname FROM Student WHERE Sno IN  // 3. 最后在Student关系中取出Sno和Sname

           SELECT Sno FROM SC WHERE Cno IN  // 2. 然后在SC关系中找出选修了111课程号的学生们的学号

                      SELECT Cno FROM Course WHERE Cname=’Management’; // 1. 先找出名称为Management的课程号，假设为111

以上查询出可以用连接查询实现

SELECT Sno, Sname FROM Student, SC, Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = ‘Management’;

 

比较运算符

SELECT Sdept FROM Student WHERE Sno = ‘200323’;

SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = (

            SELECT Sdept FROM Student WHERE Sname = ‘Jeff’);

 

SOME, ALL, EXISTS

SELECT Sname, Sage FROM Student WHERE Sge < SOME (

            SELECT Sage FROM Student WHERE Sdept = ‘Management’) AND Sdept <> ‘Management’;

SELECT Sname, Sage FROM Student WHERE Sge < ALL (

            SELECT Sage FROM Student WHERE Sdept = ‘Management’) AND Sdept <> ‘Management’;

SELECT Sname FROM Student S WHERE EXISTS (

            SELECT * FROM SC WHERE Cno = ‘1003’ AND Sno = S.Sno);

 

集合操作 - 并操作(UNION), 交操作(INTERSECT), 差操作(EXCEPT)

SELECT Sno FROM Student WHERE Sdept = ‘Computer’

UNION

SELECT Sno FROM  SC, Course C WHERE SC.Cno = C.Cno AND C.Cname = ‘Management’;