数据库总结
- 大的国家 (easy)
- 查找重复的电子邮箱 (easy)
- 交换工资 (easy)
- 有趣的电影(easy)
- 组合两个表(easy)
- 超过经理收入的员工(easy)
- 从不订购的客户(easy)
- 删除重复的电子邮箱(easy)
- 上升的温度(easy)
- 超过5名学生的课(easy)
- 第二高的薪水(easy)
- 换座位(medium)
- 分数排名(medium)

数据库总结

大的国家

这里有张 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

解答

# Write your MySQL query statement below
SELECT name,population,area FROM World 
WHERE population >= 25000000 
OR area >= 3000000;

查找重复的电子邮箱

LeetCode中文

LeetCode英文

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

**说明：**所有电子邮箱都是小写字母。

解答

GROUP BY 根据Email进行分组，然后利用HAVING过滤COUNT(Email) > = 2的分组。

# Write your MySQL query statement below
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) >= 2;

交换工资

LeetCode中文

LeetCode英文

给定一个 salary表，如下所示，有m=男性和 f=女性的值。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

例如:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

运行你所编写的查询语句之后，将会得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

解答

方法1

使用IF语句

# Write your MySQL query statement below
update salary set sex=IF(sex='m','f','m');

方法2

使用case when...then...else...end语句。

# Write your MySQL query statement below
#update salary set sex=IF(sex='m','f','m');
update salary set sex =
(
    case 
    when sex = 'm' then 'f' 
    else 'm' 
    end
);

有趣的电影

LeetCode中文

LeetCode英文

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

对于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

解答

# Write your MySQL query statement below
SELECT * FROM cinema WHERE description != 'boring' AND id % 2 = 1 
ORDER BY rating DESC;

组合两个表

LeetCode中文

LeetCode英文

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

解答

考察左连接 LEFT JOIN 的用法

# Write your MySQL query statement below
SELECT FirstName,LastName,City,State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;

超过经理收入的员工

LeetCode中文

LeetCode英文

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

解答

方法1

对同一个表进行连接，过滤条件是 e1.ManagerId = e2.Id AND e1.Salary > e2.Salary。

# Write your MySQL query statement below
SELECT e1.name AS Employee FROM Employee AS e1,Employee AS e2 
WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;

方法2

思路同方法1，连接方式变为join...on...。

# Write your MySQL query statement below
select a.name as employee from employee a join employee b
on a.managerid=b.id and a.salary>=b.salary;

从不订购的客户

LeetCode中文

LeetCode英文

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解答

方法1

利用子查询过滤，找出表Customers中的Id不在表Orders的CustomersId中的那些行。

# Write your MySQL query statement below
#select a.name as Customers from customers a join orders b on a.id = b.customerid;
select name as Customers from customers 
where id not in 
(select customerid from orders);

方法2

对Customers和Orders两个表进行左连接，连接条件是Customers.Id = Orders.CustomersId，那么连接之后Orders表中Id列为空的那些行即为所求。

# Write your MySQL query statement below
select c.name as Customers from customers as c left join orders as o
on c.id = o.customerid
where o.id is null;

删除重复的电子邮箱

LeetCode中文

LeetCode英文

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

解答

对于重复的邮箱，保留Id最小的，相当于是以Email作为分组，保留每一组Id最小的那些行。

注意：由于 MYSQL 不能先select一个表的记录，然后在按此条件进行更新和删除同一个表的记录删除数据。因此创建临时表进行过渡。

# Write your MySQL query statement below
delete from person where id not in 
(select * from (select min(id) as id from person group by email) as temptable);

上升的温度

LeetCode中文

LeetCode英文

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

例如，根据上述给定的 Weather 表格，返回如下 Id:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

解答

对同一个表Weather创建两个临时表a和b进行连接，连接条件是：a的recorddate比b的recorddate大1并且a的temperature大于b的temperature。

方法1

利用datediff函数

# Write your MySQL query statement below
select a.id from weather as a join weather as b
on datediff(a.recorddate,b.recorddate) = 1 and a.temperature > b.temperature;

方法2

利用subdate函数。

# Write your MySQL query statement below
select a.id from weather a join weather b
on (a.temperature > b.temperature and subdate(a.RecordDate,1) = b.RecordDate);

超过5名学生的课

LeetCode中文

LeetCode英文

有一个courses 表，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+

Note: 学生在每个课中不应被重复计算。

解答

以课程class进行分组，过滤出学生数不小于5的即可。

注意：加上distinct关键字过滤重复的学生。

# Write your MySQL query statement below
select class from courses group by class having count(distinct student) >= 5;

第二高的薪水

LeetCode中文

LeetCode英文

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

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

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

解答

方法1

先找出最高薪水，然后在收入小于最高薪水的那些行中找到最大值就是第二高的薪水。

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee 
where Salary < (select max(Salary) from Employee);

方法2

先将表按照薪水降序排序，利用DISTINCT剔除重复值，然后检索排序后的第1行(起始行是第0行)的Salary列。

注意：为了在不存在第二高的薪水是返回null，添加IFNULL函数来处理。

# Write your MySQL query statement below
select 
ifnull((select distinct salary from employee order by salary desc limit 1,1),null) as SecondHighestSalary;

换座位

LeetCode中文

LeetCode英文

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

解答

方法1

使用语句case when...then..else..end进行相邻行的交换，然后根据id进行排序。

# Write your MySQL query statement below
select (
case 
    when id % 2 = 0 then id-1
    when id % 2 = 1 and id != (select count(*) from seat) then id+1
    else id
end
) as id,
student
from seat order by id;

方法2

使用UNION合并多个查询，再根据id排序。

# Write your MySQL query statement below
select * from
(
select id-1 as id,student from seat where mod(id,2) = 0
union
select id+1 as id,student from seat where mod(id,2) = 1 and id !=(select count(*) from seat)
union
select id,student from seat where mod(id,2) = 1 and id = (select count(*) from seat)
) as temp 
order by id;

分数排名

LeetCode中文

LeetCode英文

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

解答

将表Score作为两个临时表a和b，从表a中返回两列，第一列是Score，第二列是针对a的每一个分数，表b中不小于这个分数的不重复Score的数量，即为Rank，最后以Rank进行排序。

# Write your MySQL query statement below
select a.Score,(select count(distinct b.Score) from Scores as b where b.Score >= a.Score) as Rank 
from Scores as a 
order by Rank;

Files

数据库.md

Latest commit

History

数据库.md

File metadata and controls

数据库总结

大的国家

解答

查找重复的电子邮箱

解答

交换工资

解答

方法1

方法2

有趣的电影

解答

组合两个表

解答

超过经理收入的员工

解答

方法1

方法2

从不订购的客户

解答

方法1

方法2

删除重复的电子邮箱

解答

上升的温度

解答

方法1

方法2

超过5名学生的课

解答

第二高的薪水

解答

方法1

方法2

换座位

解答

方法1

方法2

分数排名

解答