数据库练习

从不订购的客户 简单

select 
    a.Name as 'Customers'
from 
    Customers as a left join Orders as b on a.Id=b.CustomerId
where b.CustomerId is null;

删除重复的电子邮箱
delete的使用

delete p1 
from 
    (person as p1 left join person as p2 on p1.email = p2.email)
where 
    p1.id > p2.id

查找重复的电子邮箱
having count的使用

select Email from Person 
group by Email
having count(Email) > 1

大的国家
where的使用

select name, area, population from World 
where 
    area > 3000000 or population > 25000000

交换工资
update的使用

Update salary 
set sex = case sex
            when 'f' then 'm'
            else 'f'
            end

有趣的电影
各种表达式的使用以及如何排序

select * from cinema
where
    mod(id, 2) = 1 and description != "boring"
order by rating desc

组合两个表
连接的使用

select FirstName, LastName, City, State 
from Person left join Address
on Person.PersonId = Address.PersonId

超过经理收入的员工
学会如何进行每列的比较，搞成两个表就可以了

select 
    a.Name as "Employee"
from
    Employee as a,//这里逗号不能少
    Employee as b
where
    b.Id = a.ManagerId and a.Salary > b.Salary

重新格式化部门表

select id,
sum(case month  when 'Jan' then revenue end) Jan_Revenue,
sum(case month  when 'Feb' then revenue end) Feb_Revenue,
sum(case month  when 'Mar' then revenue end) Mar_Revenue,
sum(case month  when 'Apr' then revenue end) Apr_Revenue,
sum(case month  when 'May' then revenue end) May_Revenue,
sum(case month  when 'Jun' then revenue end) Jun_Revenue,
sum(case month  when 'Jul' then revenue end) Jul_Revenue,
sum(case month  when 'Aug' then revenue end) Aug_Revenue,
sum(case month  when 'Sep' then revenue end) Sep_Revenue,
sum(case month  when 'Oct' then revenue end) Oct_Revenue,
sum(case month  when 'Nov' then revenue end) Nov_Revenue,
sum(case month  when 'Dec' then revenue end) Dec_Revenue
from Department
group by id;

上升的温度
cross join与datediff的使用

select a.Id as "Id"
from weather as a cross join weather as b 
     on datediff(a.RecordDate, b.RecordDate) = 1
where a.Temperature > b.Temperature;

超过5名学生的课
避免重复情况

select class from courses 
group by class
having count(distinct student)>=5

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据
题目要求，如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况

select ifnull(
    (select distinct Salary 
    from Employee
    order by Salary desc
    limit 1,1),null) 
as SecondHighestSalary

牛客mysql练习
对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
表里有多少行，可以用count(*) 计算出来

select e1.first_name from employees as e1
where
(select count(*) from employees as e2
where e1.first_name <=e2.first_name)%2=1;

本文地址：https://blog.csdn.net/qq_32468785/article/details/107141531