1. join
① inner join:两张表进行连接查询时,只保留两张表中完全匹配的结果集
② left join:在两张表进行连接查询时,只返回左表所有行,而右表中没有匹配的记录则会NULL
③ right join:只返回右表所有行,而左表中没有匹配的记录则会NULL
④ full join:返回坐标和右表中所有没有匹配的行
2. 集中排序窗口函数的区别
① row_number():相同值排名不同,序号连续
② rank():相同值排名相同,序号不连续(两个2,直接4)
③ dense_rank():相同值排名相同,序号连续(两个2,下一个3)
3. on vs. where
数据库在连接多张表返回记录时,都会生成一个中间连续表(虚表)。然后在将这张临时表返回给用户
在使用外连接(以 left join 为例):
① on是在生成临时表时使用的条件,不管on的条件是否为真,都会返回左表中的全部记录
② where是在临时表生成之后,再对临时表进行过滤的条件,这是已经没有left join 的含义了,条件不为真的即被过滤掉
4. having vs. where
① 出现位置:where 可以出现在增删改查任何语句中;having只能出现在select语句中
② 执行顺序:where 在 group by 之前,会在分组前执行;having 在分组后执行
③ 子句:where 子句中的条件表达式having都可以跟,而having子句中有些表达式where不可以跟
having 子句中可以用聚合函数(sum, count, avg, max, min),而 where 子句不可以
5. union vs. union all
union 和union all都可以将多个结果集合并,必须保证各个集合列数相同,且每列类型相同,列明无需相同。union 去除重复记录并进行默认规则排序,慢;union all 不去重不排序,快。
6. in vs. exists
in:确定给定的值是否与子查询或列表中的值相匹配。在查询的时候,首先查询子查询的表,然后将内表和外表做笛卡尔积,按照条件进行筛选。所以相对内表比较小的时候,in比较快
exists:指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内标的数据一样的,匹配上就将结果放入结果集中。
如果子查询得出的结果集记录较少,著查询的表较大且又有索引用 in;反之外层著查询记录较少,子查询中表大,又有索引使用 exists
7. 数据库中空字符串、0和NULL区别
NULL:没有输入,长度为NULL
空字符串:有输入,输入数据为空字符串;长度为0
0:有输入,输入时数字0;程度为1
① NULL 数据比空字符串优先排序。order by时首先显示NULL,如果指定降序则最后显示NULL
② 对于聚合函数,如count(),min(),sum(),会忽略NULL。count(*)是例外,会计行数而不是单独列值
③ 测试NULL要用is null / is not null / ifnull(); =, <>,<,>等运算符与NULL计算时结果仍为NULL
④ mysql中默认0和NULL表示假,其他任何值表示真
⑤ 使用distinct,group by,order by 时,所有null值将被视为等同的
8. count(1),count(*),count(列名)的区别
① 执行效果
count(1):忽略有所列,用1代表代码行,统计结果时,不忽略NULL
count(*):考虑所有列,相当于行数,不忽略NULL
count(列名):只包括列名那一列,会忽略NULL
② 执行效率
如果表没有主键,count(1) 比 count(*) 快;如果有主键,count(主键) 比 count(*) 快;
如果表只有一个字段 count(*) 最快
列名为主键,count(列名) 比 count(1) 快;列名不为主键,count(1) 比 count(列名) 快;
结论:count(可空字段) < count(非空字段) = count(主键) < count(1) = count(*)
③ 执行计划
count(1) 和 count(*) 效果一样。count(1) 可能更快一点,但差不了多少
在sql调优功能上,count(1) 和 count(*) 基本没差
9. sql字符串操作常用函数
① concat(str1,str2,...):返回结果为连接参数产生的字符串,任何一个参数为null则返回值为null
② concat_ws(separator,str1,tr2,...):返回字符串连接结果,可以一次性指定分隔符
③ group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator '分隔符']):将group by 产生的同一个分组的值连接器里,返回一个字符串结果
④ substr(str,pos):截取字符串,从pos位置开始截到最后
⑤ len(str):返回字符串长度
⑥ trim(str):删除字符串首尾空格