数据库练习
程序员文章站
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
上一篇: IO,File对象-构造函数和常用方法
下一篇: 定制型网站具有什么特点?有哪些优势?