欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库练习

程序员文章站 2022-11-30 16:17:48
从不订购的客户 简单select a.Name as 'Customers'from Customers as a left join Orders as b on a.Id=b.CustomerIdwhere b.CustomerId is null;...

从不订购的客户 简单

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