MySQL练习题记录

最新推荐文章于 2025-04-08 22:46:01 发布

业精勤而荒嬉

最新推荐文章于 2025-04-08 22:46:01 发布

阅读量889

点赞数

分类专栏： Java

本文链接：https://blog.youkuaiyun.com/sinat_34976604/article/details/85430099

版权

Java 专栏收录该内容

4 篇文章

订阅专栏

题目来自Leetcode

627. Swap Salary

只用一个 SQL 查询，将 sex 字段反转

UPDATE salary
SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );

UPDATE salary SET 
    sex = CASE sex 
        WHEN 'm' THEN 'f' 
        ELSE 'm' 
    END;

620. Not Boring Movies

查找 id 为奇数，并且 description 不是 boring 的电影，按 rating 降序。

SELECT * 
FROM cinema 
WHERE id & 1 = 1 AND description != 'boring' 
ORDER BY rating DESC;

或者利用mod(id, 2) = 1，mod(x, y)相当于x%y

596， Classes More Than 5 Students

查找有五名及以上 student 的 class

select 
    class 
from 
    courses 
group by class 
having count(DISTINCT student) >= 5
;

select 
    class 
from 
    (select 
        class, count(DISTINCT student) as num 
     from courses 
     group by class) as result
where 
    num >= 5
;

182, Duplicate Emails

查找重复的邮件地址

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

196. Delete Duplicate Emails

删除重复的邮件地址

连接：

DELETE p1
FROM
    Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
    AND p1.Id > p2.Id;
    
子查询：

DELETE
FROM
    Person
WHERE
    id NOT IN ( SELECT id FROM ( SELECT min( id ) AS id FROM Person GROUP BY email ) AS m );
应该注意的是上述解法额外嵌套了一个 SELECT 语句，如果不这么做，会出现错误：
You can't specify target table 'Person' for update in FROM clause。
以下演示了这种错误解法。
错误原因是：In MySQL, you can't modify the same table which you use in the SELECT part.
DELETE
FROM
    Person
WHERE
    id NOT IN ( SELECT min( id ) AS id FROM Person GROUP BY email );

参考：pMySQL Error 1093 - Can’t specify target table for update in FROM clause

175. Combine Two Tables

查找 FirstName, LastName, City, State 数据，而不管一个用户有没有填地址信息

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

181. Employees Earning More Than Their Managers

查找薪资大于其经理薪资的员工信息

select a.Name as Employee
from Employee a, Employee b
where a.ManagerId = b.id and a.Salary > b.Salary;

select a.Name as Employee
from Employee a join Employee b 
    on a.ManagerId = b.id and a.Salary > b.Salary;

183. Customers Who Never Order

查找没有订单的顾客信息：

子查询
select name as Customers
from Customers
where id not in
    (
        select CustomerId from Orders
    );
    
左外链接
SELECT
    C.Name AS Customers
FROM
    Customers C
    LEFT JOIN Orders O
    ON C.Id = O.CustomerId
WHERE
    O.CustomerId IS NULL;

184. Department Highest Salary

查找一个 Department 中收入最高者的信息：

select d.Name as Department,e.Name as Employee,m.Salary
from Employee e, Department d, 
    (select DepartmentId, max(Salary) as Salary from Employee group by DepartmentId) m
where e.DepartmentId = d.Id 
    and e.DepartmentId = m.DepartmentId 
    and e.Salary = m.Salary;
    
解法二：
select d.Name as Department,e.Name as Employee,Salary 
from Employee e join Department d on e.DepartmentId = d.Id
where 
    (DepartmentId, Salary) in 
    (select DepartmentId, max(Salary) from Employee group by DepartmentId);

176. Second Highest Salary

查找工资第二高的员工,没有找到返回 null。

select(
	select DISTINCT  Salary 
	from Employee 
	ORDER BY Salary DESC LIMIT 1 OFFSET 1) as SecondHighestSalary;

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

select max(salary) as SecondHighestSalary
from employee
where salary < (select max(salary) from employee)

177. Nth Highest Salary

查找工资第 N 高的员工。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary DESC LIMIT 1 OFFSET N
  );
END

197.Rising Temperature

今天比昨天暖

select Weather.Id as Id 
from Weather join Weather w 
    on datediff(Weather.RecordDate, w.RecordDate) = 1
        and Weather.Temperature > w.Temperature;

DATEDIFF() 函数返回两个日期之间的天数。
将上面and改为where效果更好。

The simplest and best way to get yesterday's date is:
SUBDATE(current_date, 1)

SELECT Weather.Id 
FROM   Weather 
       JOIN Weather AS w 
         ON w.RecordDate = SUBDATE(Weather.RecordDate, 1) 
WHERE  Weather.Temperature > w.Temperature

Leetcode 619. Biggest Single Number

https://www.cnblogs.com/haoweizh/p/10198568.html
Table number contains many numbers in column num including duplicated ones.
Can you write a SQL query to find the biggest number, which only appears once.

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 | 
For the sample data above, your query should return 
the following result:

+---+
|num|
+---+
| 6 |

Note:
If there is no such number, just output null.

SELECT num FROM number GROUP BY num HAVING COUNT(*)=1 
ORDER BY num DESC LIMIT 1;

无法满足在没有时返回null的规定。
因此，若要输出null，需要满足两个条件，第一，select中应有聚合函数，第二，聚合函数处理的行数应为0。

select max(num) as num 
from (select num from number group by num havimg count(*) = 1)

LeetCode 613. Shortest Distance in a Line_Easy tag

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.
Write a query to find the shortest distance between two points in these points.

| x   |
|-----|
| -1  |
| 0   |
| 2   |

The shortest distance is ‘1’ obviously, which is from point ‘-1’ to ‘0’. So the output is as below:

| shortest|
|---------|
| 1       |

Note: Every point is unique, which means there is no duplicates in table point.

select min(abs(p1.x - p2.x)) as shortest 
from point p1 join point p2 on p1.x != p2.x

LeetCode610. Triangle Judgement_Easy tag

A pupil Tim gets homework to identify whether three line segments could possibly form a triangle.
However, this assignment is very heavy because there are hundreds of records to calculate.
Could you help Tim by writing a query to judge whether these three sides can form a triangle, assuming table triangle holds the length of the three sides x, y and z.

| x  | y  | z  |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |
For the sample data above, your query should return 
the follow result:

| x  | y  | z  | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |

select *, 
if(x+y>z and x+z>y and y+z>x, 'Yes', 'No') as triangle 
from triangle

select x,y,z,
    case
        when x+y>z and x+z>y and y+z>x then 'Yes'
        else 'No'
    end as triangle
from triangle;

Leetcode 607. Sales Person

Given three tables: salesperson, company, orders.
Output all the names in the table salesperson, who didn’t have sales to company ‘RED’.

Table: salesperson

+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+

Table: company

+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+

Table: orders

+----------+----------+---------+----------+--------+
| order_id |  date    | com_id  | sales_id | amount |
+----------+----------+---------+----------+--------+
| 1        | 1/1/2014 |    3    |    4     | 100000 |
| 2        | 2/1/2014 |    4    |    5     | 5000   |
| 3        | 3/1/2014 |    1    |    1     | 50000  |
| 4        | 4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+

结果

output

+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+

答案

select name from salesperson where sales_id not in(
select sales_id from orders
where com_id = (select com_id from company where name='Red'))
 
 
select name from salesperson where sales_id not in
(
select o.sales_id from orders o left join company c on o.com_id=c.com_id
where c.name='Red'
)

LeetCode 603. Consecutive Available Seats_Easy tag

Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seats order by the seat_id using the following cinema table?

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
Your query should return the following result for the sample case above.
| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

Note:

The seat_id is an auto increment int, and free is bool (‘1’ means free, and ‘0’ means occupied.).
Consecutive available seats are more than 2(inclusive) seats consecutively available.

SELECT DISTINCT c1.seat_id FROM cinema AS c1 JOIN cinema AS c2 
WHERE abs(c1.seat_id - c2.seat_id) = 1 
	and c1.free = 1 and c2.free = 1
order by c1.seat_id