一个巨坑 mysql 不区分大小写
所以声明变量时，如果题目给了参数N，切记不可自己DECLARE n来声明变量！！！
连接
inner join：2表值都存在
outer join：附表中值可能存在null的情况。
总结：
①A inner join B：取交集
②A left join B：取A全部，B中没有对应的值，则为null
③A right join B：取B全部，A中没有对应的值，则为null
④A full outer join B：取并集，彼此没有对应的值为null (Mysql不支持）
上述4种的对应条件，在on后填写。
上面所谓的对应是on 后面的关键词对应
ifNull limit order 用法
limit(a,b) ： 跳过a条数据读取b条数据
参数： desc 代表降序，asc 代表升序；如果不写，默认是升序
select ifNull(
(select distinct salary
from Employee
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;
max distinct
select max(distinct 成绩)
from 成绩表
where 课程=‘语文’ and
成绩 < (select max(distinct 成绩)
from 成绩表
where 课程=‘语文’);
rank类的函数 rank, dense_rank, row_number
rank() over
1 1 3 4 5 5 7dense_rank() over
1 1 2 3 4 5 5 6row_number() over
1 2 3 4 5 6
使用小提示
dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。
注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排!!!。
group having
select email from person group by email having count(email) > 1
mysql 判空是 where b.id is null; 不是等号是is
mysql group in
– select b.name as Department, a.name as Employee, a.salary as Salary
– from Employee as a left join Department as b
– on a.departmentid = b.id
– where a.salary = (select max(salary) from Employee as aa where aa.departmentid = a.departmentid)
select b.name as Department , a.name as Employee , a.salary as Salary
from Employee as a left join Department as b
on a.departmentid = b.id
where (b.id, a.salary) in (select departmentid, max(salary) from Employee group by Employee.departmentid)
