数据库作业：SQL练习5 - SELECT（嵌套查询EXISTS、集合查询、基于派生表的查询）

【3.60】查询所有选修了1号课程的学生姓名。

	SELECT Sname
	FROM Student
	WHERE EXISTS
		  (SELECT *
		   FROM SC
		   WHERE Sno=Student.Sno AND Cno='1');

带有EXIST谓词的子查询。带IN谓词，ANY和ALL 谓词的子查询都能用EXIST子查询等价替换但是有些EXIST子查询不能被其他形式子查询替代。
此查询是相关子查询。在学生表中依次取元组的Sno值，在SC表中查询Sno值等于Student表中的Sno值，并且Cno = ‘1’的元组，如果查询到了，那WHERE就返回true，这个元组就符合条件放入结果集，没有查询到就是False。然后继续下一个元组。

【3.61】查询没有选修1号课程的学生姓名。

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS
		  (SELECT *
		   FROM SC
		   WHERE Sno=Student.Sno AND Cno='1');

NOT EXIST 作用与 EXIST 相反。

【3.62】

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS
			  (SELECT *
			   FROM Course
			   WHERE NOT EXISTS
						(SELECT *
						 FROM SC
						 WHERE Sno=Student.Sno
						       AND Cno=Course.Cno
						  )
				);

查询选修了全部课程的学生姓名。SQL不支持全称量词，所以要用用EXISTS/NOT EXISTS实现全称量词.也就是：没有一门课是他不选修的
过程如下：
相关子查询。从Student最外层依次取每一个学生的元组；
进入第二层嵌套，取第一门课的元组；
进入第三层嵌套，从SC表中进行查询，学号为第一层的学生学号，课程号为第二层课程的课程号，若查询到，则这个结果是TRUE，但由于是NOT EXIST,则反应到第二层就是一个false；
回到第二层，取第二个元组，判断，直至课程表全部课程元组查询完；在第二层查询到的结果取 或 的关系，则最终第二层的结果是TRUE，NOT EXIST，所以反应到第一层是False，这个不符合，不放到结果表。
再取第一层中下一个元组，做重复。

其实相当于一个循环，一个学生要想进入结果表，那反应到第一层的就得是TRUE，又因为是NOT EXIST，那第二层的结果就得是FALSE，第二层要进行好多次（课程数），因为是或的关系，那得保证每一次都得是FALSE，那第三层循环又是NOT EXIST，所以进入到第三层循环的都得是TRUE。所以可以得出，这个进入结果表的学生满足所有的第三层，也就是每个课程里都有他，也就是他选修了所有的课程。

【3.63】

	SELECT DISTINCT Sno
	FROM SC SCX
	WHERE NOT EXISTS
			(SELECT *
			 FROM SC SCY 
			 WHERE SCY.Sno='201215122' AND
					 NOT EXISTS
					(SELECT *
					 FROM SC SCZ
					 WHERE SCZ.Sno=SCX.Sno AND
						   SCZ.Cno=SCY.Cno
					 )
			 );

查询至少选修了学生201215122选修的全部课程的学生号码。这是一个蕴含的关系。用EXISTS/NOT EXISTS实现逻辑蕴涵，变换后应为：不存在这样的课程y，学生201215122选修了y，而学生x没有选。
相关子查询。最外层给课程表起个别名SCX，从里边取出一个元组。
进入第二层，第二层中又起了个别名SCY，要求查询SCY中学号是201215122的并且第三层返回true的元组，这里先取第二层中第一个满足学号201215122的学生的元组。
进入第三层，又起个别名SCZ，查询SCZ学号和SCX取出来的元组学号相同，并且课程号和SCY里边一样的元组，要是有，那就说明从最外层取出来的那个元组选修了第二层有着201215122学号的学生的一个课程号。由于NOT EXIST，则返回第二层一个false，否则返回true。
返回第二层后，继续取满足学号201215122的学生下一个元组，再进入第三层，重复。
第二层满足学号201215122的学生的元组全部取完后，取或的关系，将它返回到最外层里，若返回后是true，则将这个元组放入结果表。
继续下一个元组。

【3.64】查询计算机科学系的学生及年龄不大于19岁的学生。

    SELECT *
    FROM Student
    WHERE Sdept= 'CS'
    
    UNION 
    
    SELECT *
    FROM Student
    WHERE Sage<=19;

在两个语句段之间加上INTERSECT就是做他们的并集。
集合查询 ：各查询结果的列数必须相同，对应项的数据类型必须相同。
并 UNION：将多个查询结果合并不保留重复元组；
UNION ALL：合并并且保留重复元组。

【3.65】 查询选修了课程1或者选修了课程2的学生。

	SELECT Sno
    FROM SC
    WHERE Cno='1'
    
    UNION
    
    SELECT Sno
    FROM SC
    WHERE Cno= '2';

结果：

【3.66】查询计算机科学系的学生与年龄不大于19岁的学的交集。

	SELECT *
	FROM Student
	WHERE Sdept='CS' 
	
	INTERSECT
	
	SELECT *
	FROM Student
	WHERE Sage<=19 

交-INTERSECT
在两个语句段之间加上INTERSECT就是做他们的交集。

【3.67】

	SELECT Sno
    FROM SC
    WHERE Cno=' 1 ' 
    
    INTERSECT
    
    SELECT Sno
    FROM SC
    WHERE Cno='2 ';

【3.68】

  	SELECT *
    FROM Student
    WHERE Sdept='CS'
    
    EXCEPT
    
    SELECT  *
    FROM Student
    WHERE Sage <=19;

查询计算机科学系的学生与年龄不大于19岁的学生的差集。其实就是查询计算机科学系中大于19岁的学生。
差集-EXCEPT

改写【3.57】找出每个学生超过他自己选修课程平均成绩的课程号
原：

	SELECT Sno, Cno
	FROM    SC  x
	WHERE Grade >=(	SELECT AVG(Grade) 
					FROM  SC y
					WHERE y.Sno=x.Sno);

改后：

	SELECT Sno, Cno
    FROM SC, (SELECT  Sno, Avg(Grade) 
              FROM   SC 
    		  GROUP BY   Sno)
              AS   Avg_sc(avg_sno,avg_grade)
    WHERE SC.Sno = Avg_sc.avg_sno
          AND SC.Grade >=Avg_sc.avg_grade

基于派生表的查询，子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
改写之后的查询对象不只是SC表，还有一个临时派生表Avg_sc，有Sno和平均值两列，查询条件是让SC表的学号于临时表的学号匹配，就是同一个人，并且SC中成绩大于临时表中的成绩（平均成绩）。

改写【3.60】查询所有选修了1号课程的学生姓名。

	SELECT Sname
    FROM     Student,  (SELECT Sno 
	                    FROM SC 
	                    WHERE Cno='1') 
	                    AS SC1
    WHERE  Student.Sno=SC1.Sno;

与原语句执行结果一致。
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。
这里SC1默认的属性列为Sno。
查询对象是Student表和选修1号课程的学生学号临时表。查询条件是学号相同。

SELECT 总结：

一、单表查询

1、 SELECT FROM WHERE 是基本形式；

2、LIKE 与 = 等价，NOT LIKE就是不等于；

3、模糊查询：

（1）、% （百分号）：代表了任意长度（可以是零）的字符串；
（2）、_ (下横线) :代表任意单个字符或者单个汉字（本机如此）；

4、换码字符：要查询的字符串中本身就有_或者%，为了把他们区分开，就要用ESCAPE ‘ <换码字符>’ 对通配符进行转义，表示紧跟在\后面的字符只是普通字符；

5、IS NULL, IS NOT NULL（“ IS 不能写为 = ”）；

6、AND ，与，用于连接多个查询条件。OR ，或（IN其实是多个OR的运算符的缩写）；

7、 ORDER BY：将查找出的数据按照一个属性进行排序。ASC为升序，DESC为降序，没有说明则为ASC。当ORDER BY 后面有两个属性列时，指的是先按第一个属性列进行排序，第一个属性值相同的元组们再按照第二个属性列进行排序，将结果显示出来；

8、聚集函数（不能跟在where后）：

（1）COUNT(*)是指查询有多少元组（行）；
（2）COUNT(DISTINCT 列名），统计一列中值的个数，这里是不重复的值的个数；
（3）AVG(列名）计算一列值的平均值；
（4）MAX(列名）计算一列中的最大值；
（5）SUM（列名），计算一列值的总和；
（6）MIN（列名），计算一列中的最小值；

9、GROUP BY：按照GROUP BY 后面的列名进行分组，值相等的分成一组。

10、HAVING ：作用于组上，记录有多少个组。它要跟着GROUP BY。

二、连接查询：同时涉及两个以上的表的查询

1.等值与非等值连接查询 ：

（1）等值连接：连接运算符为=；
（2）自然连接；

2.自身连接：一个表与其自己进行连接；

3.外连接：不仅可以输出满足查询条件的元组，也可以输出不满足条件的元组。 左外连接是列出左边关系中所有的元组 ，右外连接是列出右边关系中所有的元组 ；

4.多表连接：两个以上的表进行连接。可以对多张表进行操作；

三、嵌套查询：

相关子查询：子查询的查询条件依赖于父查询；（子查询不能使用ORDER BY子句） 不相关子查询：子查询的查询条件不依赖于父查询；

1、带有IN谓词的子查询；

2、带有比较运算符的子查询：当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）；

3、带有ANY（SOME）或ALL谓词的子查询；

ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值
!=(<>) ANY 不等于查询结果中的某个值
!=(<>) ALL 不等于查询结果中的任何一个值

4、带有EXISTS谓词的子查询：带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”；全称量词和逻辑蕴涵可用EXIST表示；

四、集合查询：并-UNION；交-INTERSECT；差-EXCEPT；

五、基于派生表的查询：

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。