LeetCode数据库刷题笔记 - 简单

176. 第二高的薪水

Q：为什么OFFSET 1，1 不可以？
A：如果所查询的表 或 where条件筛选后得到的结果集为空，那么聚合函数sum() 或 avg()的返回值为NULL；count()函数的返回值为0

 

182.查找重复电子邮箱

Q：What's the difference between COUNT(1), COUNT(*), COUNT(Email) ?

A：COUNT(*) will count the number of rows, while COUNT(expression) will count non-null values in expression and COUNT(column) will count all non-null values in the column.

Since both 0 and 1 are non-null values, COUNT(0)=COUNT(1) and they both will be equivalent to the number of rows COUNT(*). It's a different concept, but the result will be the same.

Q：MySQL中列名也不区分大小写吗？表名是否区分大小写？

A：On Windows, table names, database names and table aliases are not case sensitive. 

Field (column) names are case-insensitive regardless of the operating system. And you should not refer to a given database or table using different cases within the same statement. 

 

196. 删除重复邮箱

Q：

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

delete will only delete the corresponding records? not the whole table?

A：