MYSQL

 1. Update salary set sex=if(sex='m','f','m') ;

重复数据筛选用groupby,having
 2. select Email from Person group by Email having count(Email)>1;
 
 
 4. select a.FirstName,a.LastName,b.City,b.state from person as a left outer join Address as b on
a.PersonId=b.personid;

 5. select a.Name as Employee from Employee as a inner join Employee as b on a.ManagerId=b.Id 
 where a.Salary>b.Salary;
 

 7. SELECT DISTINCT id AS "id",
SUM(IF (month = "Jan", revenue, null)) AS "Jan_Revenue",
SUM(IF (month = "Feb", revenue, null)) AS "Feb_Revenue",
SUM(IF (month = "Mar", revenue, null)) AS "Mar_Revenue",
SUM(IF (month = "Apr", revenue, null)) AS "Apr_Revenue",
SUM(IF (month = "May", revenue, null)) AS "May_Revenue",
SUM(IF (month = "Jun", revenue, null)) AS "Jun_Revenue",
SUM(IF (month = "Jul", revenue, null)) AS "Jul_Revenue",
SUM(IF (month = "Aug", revenue, null)) AS "Aug_Revenue",
SUM(IF (month = "Sep", revenue, null)) AS "Sep_Revenue",
SUM(IF (month = "Oct", revenue, null)) AS "Oct_Revenue",
SUM(IF (month = "Nov", revenue, null)) AS "Nov_Revenue",
SUM(IF (month = "Dec", revenue, null)) AS "Dec_Revenue" 
FROM Department GROUP BY id


取外连接的剩余部分

6.select a.Name as Customers from Customers as a left outer join Orders as b on a.id = b.CustomerId 
where b.CustomerId is NULL;

DATA之间的差异用DATEDIFF函数

7.select b.id from weather as a inner join weather as b on DATEDIFF(a.RecordDate, b.RecordDate) = -1 
where a.temperature<b.temperature; 


去重后统计数字，Every derived table must have its own alias---------注意biao需要命名别名
8.select class from (select distinct student,class from courses )as a group by class having count(class)>=5;


例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

9.select ifnull((select distinct Salary  from employee order by salary desc limit 1,1),null)as SecondHighestSalary;

奇偶互换

select 
case  when (id%2=0) 
then id-1 
when id=(select max(id) from seat) 
then id 
else id+1 
end as id,student 
from seat 
order by id;

11.分数排名

select a.score,
(select count(distinct score) from scores as b  where b.score>=a.score) as rank
from scores as a
order by score desc;

12.多表找最高

select b.name as Department,a.name as Employee,a.salary  
from Employee as a  inner join Department as b on a.DepartmentId=b.id 
where (a.DepartmentId,a.salary) in (select DepartmentId,max(salary) from Employee group by DepartmentId) ;