Collect Query

1 /*
2 集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。
3 UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNION ALL：
4 将多个查询结果合并起来时，保留重复元组，其它的类似。
5 1、并操作UNION
6 2、交操作INTERSECT
7 3、差操作EXCEPT
8   */
9 -- [例48] 查询计算机科学系的学生及年龄不大于岁的学生。-------------------------------------------
10 SELECT * -- -方法一
11 FROM Student
12 WHERE Sdept = ' CS '
13 UNION
14 SELECT *
15 FROM Student
16 WHERE Sage <= 19 ;
17 SELECT * -- -方法二
18 FROM Student
19 WHERE Sdept = ' CS ' OR Sage <= 19 ;
20 SELECT * FROM Student;
21

23 -- [例49] 查询选修了课程或者选修了课程的学生。--------------------------------------------------
24 SELECT Sno
25 FROM SC
26 WHERE Cno = ' 1 '
27 UNION
28 SELECT Sno
29 FROM SC
30 WHERE Cno = ' 2 ' ;
31 SELECT * FROM SC; -- 系统会自动去掉重复元组
32
33 -- [例50] 查询计算机科学系的学生与年龄不大于20岁的学生的交集------------------------------------
34 SELECT *
35 FROM Student
36 WHERE Sdept = ' CS '
37 INTERSECT -- 在SQL SERVER2005 下适用
38 SELECT *
39 FROM Student
40 WHERE Sage <= 20 ;
41 SELECT * FROM Student;
42
43 -- [例51] 查询选修课程1的学生集合与选修课程2的学生集合的交集-------------------------------------
44 SELECT Sno
45 FROM SC
46 WHERE Cno = ' 1 '
47 INTERSECT
48 SELECT Sno
49 FROM SC
50 WHERE Cno = ' 2 ' ;
51 SELECT Sno -- 方法二：用IN 谓词
52 FROM SC
53 WHERE Cno = ' 1 '
54 AND Sno IN
55 (
56 SELECT Sno
57 FROM SC
58 WHERE Cno = ' 2 '
59 ) ;
60 SELECT * FROM SC
61
62 -- [例]查询计算机科学系的学生与年龄不大于19岁的学生差集------------------------
63 SELECT *
64 FROM Student
65 WHERE Sdept = ' CS '
66 EXCEPT
67 SELECT *
68 FROM Student
69 WHERE Sage <= 19 ;
70 SELECT * -- 方法二
71 FROM Student
72 WHERE Sdept = ' CS ' AND Sage >= 19 ;
73 SELECT * FROM Student;
74
75 -- 【题】在样例数据库pubs 中，对表publishers 和authORs 做交叉查询。------------
76 USE pubs
77 SELECT pub_name，au_fname, au_lname
78 FROM publishers CROSS JOIN authORs
79 ORDER BY au_lname;
80

 

转载于:https://www.cnblogs.com/FYSUIT/archive/2010/12/04/1896466.html