SQL笔记

1. SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT COUNT(DISTINCT Country) FROM Customers;

2. Not equal: <>/!=
3. UNION selects only distinct values. Use UNION ALL to also select duplicate values

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• YEAR - format YYYY or YY

5. Self join

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

e.g. leetcode 181:
收入超过经理的员工

select a.name as Employee
from Employee a, Employee b
where a.Managerid = b.id and a.salary>b.salary

6. Having 的用法
   having vs where
   The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies the condition to the groups after the rows are grouped into groups.

e.g. leetcode 182
找出重复的email， 要用having

select email from person group by email having count(email)>1

7. 删除表格中重复且id大的：
   leetcode 196

delete a 
from Person a, Person b 
where a.email = b. email and a.id > b.id

8. 时间差
   mysql 函数
   datediff(被减时间，减时间）

9. 笛卡尔积/直积(Cartesian product)
   cross join: returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.

10. 整除:div
    5 div 2 = 2;
    取余:mod
    5 mod 2 = 1;
    四舍五入:round
    round(1.5) = 2;