sql中的区别

目录

1 where 和having

区别

实例

总结

2 where和on

3 where in和inner join

4 in 和 =

5 几种去重方式：distinct，group by，窗口函数

distinct:

group by 

窗口函数

---

1 where 和having

区别

（1）“where”是一个约束声明，在查询数据库的结果返回之前，对数据库中的查询条件进行约束，即在结果返回之前起作用，且“where”后面不能写“聚合函数”（where后面不能使用聚合函数的原因是where的执行顺序在聚合函数之前）。

（2）“having”是一个过滤声明，是在查询数据库结果返回之后，进行过滤，即在结果返回值后起作用，并且“having”后面可以写“聚合函数”。

（3）where、聚合函数、having在from后面的执行顺序：
    where>聚合函数(sum,min,max,avg,count)>having

select 查询结果[学号,姓名]
from 从哪张表中查找数据[学生表:student]
where 查询条件[用到运算符in]
group by 分组[没有]
having 对分组结果指定条件[没有]
order by 对查询结果排序[没有]
limit 从查询结果中取出指定行[没有];

实例

(1) where和having均可使用：

select score，name from student where score>60;

select score，name from student having score>60;

可用having的原因是已经筛选出score字段，在这种情况下是和where等效的。

(2) 只能用where，不能用having

select score，name from student where sex="man";  正确的

select score，name from student having sex="man";  错误的，因为前面没有筛选sex。

(3) 只能用having，不能用where

select goods_category_id,avg(goods_price) as ag from goods_table where ag>100 group by goods_category;  错误的

select goods_category_id,avg(goods_price) as ag from goods_table where avg(goods_price)>100 group by goods_category;  错误的

select  goods_category_id,avg(goods_price) as ag from goods_table group by goods_category having ag>100; 正确的

总结

where只能接表里有的字段，having只能接select后面查询出的字段

2 where和on

首先我们要明确：on和where后都表示查询的条件

on用于连接查询和where用于连接查询什么区别？

外连接必须用on，内连接on和where都可以。on只能用于连接查询，where可以用其他条件。

用于内连接查询时，on和where是一样的。

（1）on只能用于连接查询（内连接、外连接、交叉连接），在其他情况下使用on会报错

select * from stu on id=1
上面这样写就是不行的，只能用where进行条件查询。

（2）在进行外连接是必须使用on，不然会报错。

select *from stu1 left join stu2 on stu1.id = stu2.id

（3）在进行内连接时on和where都可以使用。

select *from stu1 inner join stu2 on stu1.id = stu2.id
select *from stu1 inner join stu2 where stu1.id = stu2.id

on and 和 on where什么区别？

on and有外连接后的结果，on where会对外连接后的结果过滤

表stu1

id	name
1	Rose
2	Tom
3	Amy

表stu2

id	name
2	xiaoming
5	xiaohong

（1）select* from stu1 left join stu2 on stu1.id = stu2.id and stu1.id = 2;

第一个语句结果

id	name	id	name
1	Rose	null	null
2	Tom	2	xiaoming
3	Amy	null	nul

（2）select* from stu1 left join stu2 on stu1.id = stu2.id where stu1.id = 2;

第二个语句结果

id	name	id	name
2	Tom	2	xiaoming

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。

        在两表联接查询时用on，在一个表的时候，就只有where跟having比较了。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。

3 where in和inner join

inner join 是内连接，where 是条件判断语句，用where语句可以实现内连接的功能，但区别在于where 语句还可以加上其他条件，而inner join则不可以了。因为on加上and还是全部的结果。
例如：
select a.*,b.* from a inner join b on a.id = b.id
等同于
select a.*,b.* from a,b where a.id = b.id
但如果用语句二的还可以接着写条件，语句一则要新添where语句

4 in 和 =

 = 后面这能是某一个特定值，而 in 可以是一个结果集。

如果结果集只有一个值时，“=” 等价于 “in” ，如果结果集有多个值时，in 等价于 多个 “=”或。

5 几种去重方式：distinct，group by，窗口函数

distinct:

select distinct 访客id ,浏览时间 
     from 淘宝日销售数据表;

（1）distinct语法规定对单字段、多字段去重，必须放在第一个查询字段前;

（2）如果对表中多列字段进行去重，去重的过程就是将多字段作为整体去重，比如上面的例子，我们将访客id和浏览时间为整体去去重，而不是对访客id单独去重后再对姓名单独去重，所以会出现相同的访客id对应不同的浏览时间。

group by 

select 访客id ,浏览时间
     from 淘宝日销售数据表
group by 访客id ,浏览时间;

（1）group by对访客id 和浏览时间进行分组，分组汇总后改变了表的行数，一行只有一个类别;

（2）这里使用group by后会将访客id 和浏览时间作为一个类别保留，重复的就会聚合；

（3）group by是聚合后的结果。

窗口函数

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

select 访客id ,浏览时间 ,row_number()over(partition by 访客id ,浏览日期
order by 浏览时长（秒）) as 排名
     from 淘宝日销售数据表
where 排名=1；

（1）窗口函数不会减少原表中的行数，而是对字段进行分组后排序，增加一列序号

（2）row_number()函数可以为每条记录添加递增的顺序数值序号,即使值完全相同也依次递增序号,不会重复

（3）窗口函数查询按照每个客户和浏览日期分组，如果同一天有几次浏览，会根据浏览时长排序，筛选排名为1，即可得出每个访客和对应的浏览日期。

6 substring和splict