MySQL 练习＜3＞

MySQL 练习

大家好呀，我是小笙，今天我来分享一些 Leetcode 上的MySQL的练习

181. 超过经理收入的员工

编写一个SQL查询来查找收入比经理高的员工

# 新建表
Create table If Not Exists Employee (
    id int, 
    name varchar(255), 
    salary int, 
    managerId int
)
    
# 插入数据
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

内连接

select worker.name as 'Employee' from Employee worker,Employee boss
    where worker.managerId =  boss.id and boss.salary < worker.salary;

使用 JOIN 语句

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary

---

182. 查找重复的电子邮箱

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

# 创建表
Create table If Not Exists Person (
    id int, 
    email varchar(255)
)

# 插入数据
insert into Person (id, email) values ('1', 'a@b.com')
insert into Person (id, email) values ('2', 'c@d.com')
insert into Person (id, email) values ('3', 'a@b.com')

内连接

select distinct Email 
	from Person p1,Person p2
	where p1.id <> p2.id and p1.Email = p2.Email;

分组操作

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

---

197. 上升的温度

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

# 创建表
Create table If Not Exists Weather (
    id int, 
    recordDate date, 
    temperature int
)

# 插入数据
insert into Weather (id, recordDate, temperature) values ('1', '2015-01-01', '10')
insert into Weather (id, recordDate, temperature) values ('2', '2015-01-02', '25')
insert into Weather (id, recordDate, temperature) values ('3', '2015-01-03', '20')
insert into Weather (id, recordDate, temperature) values ('4', '2015-01-04', '30')

本题主要的难点是日期的比较相差一天如何比较，使用 datediff 函数

注意: MySQL 和 SQL Serve之间 datediff 函数有区别

• MySQL的是 datediff(今天或者过去, 过去或者今天)
• sqlServer 的是 datediff (时间单位, 过去或者今天, 今天或者过去)

内连接

select w1.id from Weather w1,Weather w2
    where datediff(w1.recordDate,w2.recordDate) = 1
    and w1.temperature > w2.temperature

---

595. 大的国家

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

• 面积至少为 300 平方公里（即，3000000 km2），或者
• 人口至少为 2500 万（即 25000000）

编写一个 SQL 查询以报告 大国 的国家名称、人口和面积

# 新建表
Create table If Not Exists World 
(
    name varchar(255), 
    continent varchar(255), 
    area int, 
    population int, 
    gdp int
)

# 插入数据
insert into World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000')
insert into World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000')
insert into World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000')
insert into World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000')
insert into World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000')

示例：

输入：
World 表：
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
输出：
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

代码实现

select name,population,area from World
        where area >= 3000000  or population >= 25000000;
        
-- 使用 or 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or

SELECT name, population, area FROM world
			WHERE area >= 3000000
UNION
SELECT name, population, area FROM world 
			WHERE population >= 25000000

---

596. 超过5名学生的课

编写一个SQL查询来报告 至少有5个学生 的所有班级

# 创建表
Create table If Not Exists Courses (
    student varchar(255), 
    class varchar(255)
)

# 插入数据
insert into Courses (student, class) values ('A', 'Math')
insert into Courses (student, class) values ('B', 'English')
insert into Courses (student, class) values ('C', 'Math')
insert into Courses (student, class) values ('D', 'Biology')
insert into Courses (student, class) values ('E', 'Math')
insert into Courses (student, class) values ('F', 'Computer')
insert into Courses (student, class) values ('G', 'Math')
insert into Courses (student, class) values ('H', 'Math')
insert into Courses (student, class) values ('I', 'Math')

代码实现

# 通过 class　分组并查询每组不重复的学生数量是否大于等于　５
select class from Courses
    group by class
    having COUNT(DISTINCT student) >= 5