SQL语句之分组查询

#SQL支持并操作,等价于或操作，但是或操作会出现重复项
SELECT * FROM student WHERE Sdept='CS' UNION SELECT * FROM student WHERE Sage <=10;
#
SELECT * FROM student WHERE Sdept='CS' OR  Sage<=10;

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

SELECT DISTINCT Sno FROM sc WHERE Cno='1' OR Cno='2';

#交集相当于and,mysql不支持交集
SELECT Sno From student WHERE Sdept='CS';
#表连接实现交集
SELECT DISTINCT sc1.Sno,sc2.Sno FROM sc sc1,sc sc2
WHERE sc1.Cno=1 and sc2.Cno=2 and sc1.Sno=sc2.Sno;
#子查询实现交集
SELECT sno From sc Where cno=1
and sno in(SELECT sno from sc Where cno=2);

#差集,mysql不支持差集

#基于派生表的查询
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;

#SELECT语句的一般形式
/* SELECT 【ALL|DISTINCT】 <目标列表达式>[别名][,<目标列表达式>[别名]].... 
FROM <表名或视图名>[别名] [, <表名或视图名>[别名] ]|(SELECT)[AS]<别名>
[WHERE<条件表达式>]
[GROUP BY<列名>[HAVING<条件表达式>]]
[GROUP BY<>[ASC|DESC]]
*/
/*
目标表达式的可选格式
*/
#集合函数

#Where字句的条件表达式