MySQL习题整理

查询

产品销售分析 III

销售表 Sales：

±------------±------+
| Column Name | Type |
±------------±------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
±------------±------+
sale_id 是此表的主键。
product_id 是产品表的外键。
请注意，价格是按每单位计的。
产品表 Product：

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| product_id | int |
| product_name | varchar |
±-------------±--------+
product_id 是此表的主键。

编写一个 SQL 查询，选出每个销售产品的 第一年 的 产品 id、年份、数量 和 价格。

查询结果格式如下：

Sales table:
±--------±-----------±-----±---------±------+
| sale_id | product_id | year | quantity | price |
±--------±-----------±-----±---------±------+
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
±--------±-----------±-----±---------±------+

Product table:
±-----------±-------------+
| product_id | product_name |
±-----------±-------------+
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
±-----------±-------------+

Result table:
±-----------±-----------±---------±------+
| product_id | first_year | quantity | price |
±-----------±-----------±---------±------+
| 100 | 2008 | 10 | 5000 |
| 200 | 2011 | 15 | 9000 |
±-----------±-----------±---------±------+

SELECT PRODUCT_ID, YEAR AS FIRST_YEAR, QUANTITY, PRICE
FROM SALES
WHERE (PRODUCT_ID, YEAR) IN (SELECT PRODUCT_ID, MIN(YEAR)
                             FROM SALES
                             GROUP BY PRODUCT_ID);

项目员工I

项目表 Project：

±------------±--------+
| Column Name | Type |
±------------±--------+
| project_id | int |
| employee_id | int |
±------------±--------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
员工表 Employee：

±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
主键是 employee_id。

请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。

查询结果的格式如下：

Project 表：
±------------±------------+
| project_id | employee_id |
±------------±------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
±------------±------------+

Employee 表：
±------------±-------±-----------------+
| employee_id | name | experience_years |
±------------±-------±-----------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
±------------±-------±-----------------+

Result 表：
±------------±--------------+
| project_id | average_years |
±------------±--------------+
| 1 | 2.00 |
| 2 | 2.50 |
±------------±--------------+
第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50

SELECT PROJECT_ID, ROUND(AVG(EXPERIENCE_YEARS), 2) AS AVERAGE_YEARS
FROM PROJECT AS P
INNER JOIN EMPLOYEE AS E
ON P.EMPLOYEE_ID = E.EMPLOYEE_ID
GROUP BY 1;

项目员工II

Table: Project

±------------±--------+
| Column Name | Type |
±------------±--------+
| project_id | int |
| employee_id | int |
±------------±--------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
Table: Employee

±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
主键是 employee_id。

编写一个SQL查询，报告所有雇员最多的项目。

查询结果格式如下所示：

Project table:
±------------±------------+
| project_id | employee_id |
±------------±------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
±------------±------------+

Employee table:
±------------±-------±-----------------+
| employee_id | name | experience_years |
±------------±-------±-----------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
±------------±-------±-----------------+

Result table:
±------------+
| project_id |
±------------+
| 1 |
±------------+
第一个项目有3名员工，第二个项目有2名员工。

SELECT PROJECT_ID
FROM PROJECT
GROUP BY 1
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM PROJECT
                   GROUP BY PROJECT_ID
                   ORDER BY 1 DESC
                   LIMIT 1);

项目员工 III

项目表 Project：

±------------±--------+
| Column Name | Type |
±------------±--------+
| project_id | int |
| employee_id | int |
±------------±--------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键
员工表 Employee：

±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
employee_id 是这个表的主键

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

查询结果格式在以下示例中：

Project 表：
±------------±------------+
| project_id | employee_id |
±------------±------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
±------------±------------+

Employee 表：
±------------±-------±-----------------+
| employee_id | name | experience_years |
±------------±-------±-----------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
±------------±-------±-----------------+

Result 表：
±------------±--------------+
| project_id | employee_id |
±------------±--------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
±------------±--------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

select project_id, Project.employee_id
from Project inner join Employee on Project.employee_id=Employee.employee_id
where (project_id,experience_years) in (
    select project_id,max(experience_years)
    from Project inner join Employee on Project.employee_id=Employee.employee_id
    group by project_id
);

销售分析 I

产品表：Product

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
±-------------±--------+
product_id 是这个表的主键.
销售表：Sales

±------------±--------+
| Column Name | Type |
±------------±--------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
±----- ------±--------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.

编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

查询结果格式如下所示：

Product 表：
±-----------±-------------±-----------+
| product_id | product_name | unit_price |
±-----------±-------------±-----------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
±-----------±-------------±-----------+

Sales 表：
±----------±-----------±---------±-----------±---------±------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
±----------±-----------±---------±-----------±---------±------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
±----------±-----------±---------±-----------±---------±------+

Result 表：
±------------+
| seller_id |
±------------+
| 1 |
| 3 |
±------------+
Id 为 1 和 3 的销售者，销售总金额都为最高的 2800。

select seller_id
from Sales
group by seller_id
having sum(price)>=all(
    select sum(price)
    from Sales
    group by seller_id
);

销售分析 II

Table: Product

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
±-------------±--------+
product_id 是这张表的主键
Table: Sales

±------------±--------+
| Column Name | Type |
±------------±--------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
±----- ------±--------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

查询结果格式如下图表示：

Product table:
±-----------±-------------±-----------+
| product_id | product_name | unit_price |
±-----------±-------------±-----------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
±-----------±-------------±-----------+

Sales table:
±----------±-----------±---------±-----------±---------±------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
±----------±-----------±---------±-----------±---------±------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 1 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 3 | 2019-05-13 | 2 | 2800 |
±----------±-----------±---------±-----------±---------±------+

Result table:
±------------+
| buyer_id |
±------------+
| 1 |
±------------+
id 为 1 的买家购买了一部 S8，但是却没有购买 iPhone，而 id 为 3 的买家却同时购买了这 2 部手机。

select distinct buyer_id#注意，同一个买家，可以多次买同一款产品，因此要distinct
from Sales inner join Product on Sales.product_id=Product.product_id
where product_name='S8' and buyer_id not in (
    select buyer_id
    from Sales as a inner join Product as b on a.product_id=b.product_id
    where b.product_name = 'iPhone'
);

销售分析III

Table: Product

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
±-------------±--------+
product_id 是这个表的主键
Table: Sales

±------------±--------+
| Column Name | Type |
±------------±--------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
±----- ------±--------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

查询结果格式如下所示：

Product table:
±-----------±-------------±-----------+
| product_id | product_name | unit_price |
±-----------±-------------±-----------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
±-----------±-------------±-----------+

Sales table:
±----------±-----------±---------±-----------±---------±------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
±----------±-----------±---------±-----------±---------±------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
±----------±-----------±---------±-----------±---------±------+

Result table:
±------------±-------------+
| product_id | product_name |
±------------±-------------+
| 1 | S8 |
±------------±-------------+
id为1的产品仅在2019年春季销售，其他两个产品在之后销售。

select distinct a.product_id, product_name#同一款产品可能多次被卖出
from Product as a inner join Sales as b on a.product_id=b.product_id
where (sale_date between '2019-01-01' and '2019-03-31') and a.product_id not in (
    select a.product_id
    from Product as a inner join Sales as b on a.product_id=b.product_id
    where sale_date<'2019-01-01' or sale_date>'2019-03-31'
);

组合两个表

表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

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

第二高的薪水

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

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

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

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+
解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

select  Department.Name as Department, Employee.Name as Employee, Salary
from Employee inner join Department on Employee.DepartmentId=Department.Id
where (DepartmentId,Salary) in (
    select DepartmentId,max(Salary)
    from Employee
    group by DepartmentId
);

第N高的薪水

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

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
    RETURN (
      # Write your MySQL query statement below.
      select Salary
      from Employee
      group by Salary#相同的薪水算并列同一排名位置
      order by Salary desc
      limit N,1#第一个参数表示第几个分组（从0开始），第二个参数表示返回几行。
    );
END

连续出现的数字

表：Logs

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| num | varchar |
±------------±--------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

Logs 表：
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+

Result 表：
±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+
1 是唯一连续出现至少三次的数字。

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+
解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

每家商店的产品价格

表：Products

±------------±--------+
| Column Name | Type |
±------------±--------+
| product_id | int |
| store | enum |
| price | int |
±------------±--------+
(product_id,store) 是这个表的主键。
store 字段是枚举类型，它的取值为以下三种 (‘store1’, ‘store2’, ‘store3’) 。
price 是该商品在这家商店中的价格。

写出一个 SQL 查询语句，查找每种产品在各个商店中的价格。

可以以 任何顺序 输出结果。

查询结果格式如下例所示：

Products 表：
±------------±-------±------+
| product_id | store | price |
±------------±-------±------+
| 0 | store1 | 95 |
| 0 | store3 | 105 |
| 0 | store2 | 100 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
±------------±-------±------+
Result 表：
±------------±-------±-------±-------+
| product_id | store1 | store2 | store3 |
±------------±-------±-------±-------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
±------------±-------±-------±-------+
产品 0 的价格在商店 1 为 95 ，商店 2 为 100 ，商店 3 为 105 。
产品 1 的价格在商店 1 为 70 ，商店 3 的产品 1 价格为 80 ，但在商店 2 中没有销售。

select 
    product_id, 
    sum(case when store='store1' then price end) as store1, 
    sum(case when store='store2' then price end) as store2, 
    sum(case when store='store3' then price end) as store3
from products
group by product_id;

删除

删除重复的电子邮箱

编写一个 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 |
±—±-----------------+

DELETE p1
FROM Person as p1,Person as p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;