整理Mysql 琐碎知识点(持续更新)

一.Mysql join联表操作中on和where的区别

1.结论

1.on的优先级是高于where。on条件是在生成临时表时候执行，where是在临时表生成后对数据进行筛选的。

2.当inner join联结两个表时，筛选条件放在on和where后面，结果是一样的。

3.当left join 联结两个表时，就有区别了：筛选条件跟在on后面，无论on条件是否成立，都会返回左表（主表）的值；筛选条件在where后面，只有满足条件的记录才会返回。

2.测试

表1 table1

id	No
1	n1
2	n2
3	n3

表1 table2

No	name
n1	aaa
n2	bbb
n3	ccc

这时候执行查询语句：

select a.id,a.No,b.name from table1 a left join table2 b on (a.No = b.No and b.name='aaa');
select a.id,a.No,b.name from table1 a left join table2 b on (a.No = b.No) where b.name='aaa';

第一条sql语句得到的结果集：

|id |No |name|
|---|---|---|
|1  |n1 |aaa|
|2  |n2 |(Null)|
|3  |n3 |(Null)| 

可以看出将筛选条件放到on后面，因为left join的缘故，会将主表所有数据都返回，但on后面加的的条件 b.name='aaa’也起了作用，其余行的name都为null。因为这条sql语句流程是这样的，首先找到b表的name为aaa的记录行（on (a.No = b.No and b.name=’aaa’) ），然后再去联结a表，获得相应的数据。

第二条sql语句得到的结果集：

|id |No |name|
|---|---|---|
|1  |n1 |aaa|

二. where和having的区别

总结：where 后面的字段是去数据库中去筛选，而having后面的字段是必须是select后面要查询的字段。

1.where和having都适用的场景

select students_id,students_name,students_score from students where students_score > 90;
select students_id,students_name,students_score from students having students_score > 90;

因为students_score这个字段既存在于数据库中，又是select后面要查询的字段，所以where和having都适用。

2.只能用where，不能用having的场景

select students_id,students_name from students where students_score > 90;
select students_id,students_name from students having students_score > 90;//报错

第二个sql语句报错的原因就是students_score字段并不是select之后需要查询的字段。

3.只能用having，不能用where的场景

select students_id,avg(students_score)as ac from students group by students_id having ac > 90;
select students_id,avg(students_score)as ac from students group by students_id where ac > 90;//报错

where 后面的字段必须存在于数据库中。

三.去重group by 和distinct的区别

1.group by 和distinct都可以在查询时用来去除重复数据。

select distinct name from user;
select name from user group by name;

2.distinct可以用于去除多个字段重复：

select distinct name,id from user;

name和id必须都完全一样时，才会被剔除。

3.distinct必须放在查询字段的最前面。

select id,distinct name from user;//报错

4.大部分情况下，group by 运行效率比distinct要高。