SQL子句详解

web前端 2507 0 8年前

where与having

1.作用的对象不同。WHERE 子句作用于表和视图,HAVING 子句作用于组(group)。

eg:SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

2.WHERE 在分组和聚集计算之前选取输入行(因此,它控制哪些行进入聚集计算), 而 HAVING 在分组和聚集之后选取分组的行。因此,WHERE 子句不能包含聚集函数; 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反,HAVING 子句总是包含聚集函数。(严格说来,你可以写不使用聚集的 HAVING 子句, 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。)

在前面的例子里,我们可以在 WHERE 里应用城市名称限制,因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效,因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。

以下示例使用的数据库是MySQL 5。
数据表:student
表结构:
Field Name DataType Len
id                int           20
name           varchar    25
major           varchar    25
score           int           20
sex              varchar    20

表数据:
编号/姓名/专业/学分/性别
id   name major     score sex
1    jak    Chinese    40    f
2    rain    Math        89    m
3    leo    Phy          78    f
4    jak    Math         76    f
5    rain    Chinese   56    m
6    leo    Math         97    f
7    jak    Phy          45    f
8    jak    Draw         87    f
9    leo    Chinese    45    f

现在我们要得到一个视图:
要求查询性别为男生,并且列出每个学生的总成绩:
SQL:
select s.*,sum(s.score) from student s where sex='f' group by s.name

Result:
id   name major     score sex sum(s.score)
1    jak    Chinese    40    f       248
3    leo    Phy         78     f       220

可以看到总共查到有两组,两组的学生分别是jak和leo,每一组都是同一个学生,这样我们就可以使用聚合函数了。
只有使用了group by语句,才能使用如:count()、sum()之类的聚合函数。

下面我们再对上面的结果做进一步的筛选,只显示总分数大于230的学生:
SQL:
select s.*,sum(s.score) from student s where sex='f' group by s.name having sum(s.score)>230

Result:
id   name major     score       sex   sum(s.score)
1    jak    Chinese    40          f       248

可见having于where的功能差不多。


on与where

数据库在通过连接两张或多张表来返回记录时,都会先生成一张中间的临时表(由from部分生成),然后再将这张临时表返回继续处理。

在使用left jion时,on和where条件的区别如下:

1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

假设有两张表:

表1:tab2 id size
1 10
2 20
3 30
表2:tab2 size name
10 AAA
20 BBB
20 CCC


两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

第一条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)

2、再对中间表过滤
where 条件:
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

第二条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的关联条件(如:table1.a = table2.a) 是否为真,都会返回主表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。
on与where区别较大,当语句之中使用了on之后就应该考虑where以后的语句是否合理有效,应该在where中使用对主表的筛选语句而避免使用对副表的的筛选语句,对副表的筛选语句应该放置于on中进行实现。