mysql习题练习

最新推荐文章于 2024-10-10 15:31:49 发布

Z_JUAN1

最新推荐文章于 2024-10-10 15:31:49 发布

阅读量739

点赞数 1

本文链接：https://blog.csdn.net/Z_JUAN1/article/details/122209222

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

记录2021的第一篇也是最后一篇，后面会继续完善的~

【问题1--两表关联】

【答案1.1】

【答案1.2】

【问题2--group by having】

【问题4--limit子句、IFNULL、排序】

【问题6-Rank() over (partition by……order by……）】

【问题11-update与case..when】

【答案11】

【问题1--两表关联】

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

+----+-------+--------+-------

----+

+----+-------+--------+-----------+

| 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL |

+----+-------+--------+-----------+

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

+----------+

| Employee |

+----------+

| Joe |

+----------+

【答案1.1】

SELECT

a.Name AS 'Employee'

FROM

Employee AS a,

Employee AS b

WHERE

a.ManagerId = b.Id

AND a.Salary > b.Salary;

【答案1.2】

SELECT

a.NAME AS Employee

FROM Employee AS a JOIN Employee AS b

ON a.ManagerId = b.Id

AND a.Salary > b.Salary;

【问题2--group by having】

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

示例：

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

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

+---------+

| Email |

+---------+

| a@b.com |

------------

【答案2.1】

select Email from

(

select Email, count(Email) as num

from Person

group by Email

) as statistic

where num > 1;

【答案2.2】

向group by子句中添加条件更常用的一种方法使用Having子句，简单高效。

select Email

from Person

group by Email

having count(Email) > 1;

【问题3 -- not in】

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

Customers 表：

+----+-------+

| Id | Name |

+----+-------+

| 1 | Joe |

| 2 | Henry |

| 3 | Sam |

| 4 | Max |

+----+-------+

Orders 表：

+----+------------+

| Id | CustomerId |

+----+------------+

| 1 | 3 |

| 2 | 1 |

+----+------------+

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

+-----------+

| Customers |

+-----------+

| Henry |

| Max |

+-----------+

【答案3.1】

select customers.name as 'Customers'

from customers

where customers.id not in

(

select customerid from orders

);

select Customers.Name as Customers from ustomers left join Orders on Customers.Id = orders.customerid where orders.id is null ;

【问题4--limit子句、IFNULL、排序】

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

+----+--------+

| Id | Salary |

+----+--------+

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

+----+--------+

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

+---------------------+

| SecondHighestSalary |

+---------------------+

| 200 |

+---------------------+

【答案4.1】

select distinct Salary as SecondHighestSalary from Employee order by Salary desc limit 1,1;

但是如果表中没有若表中数据没有第二高工资，那么需要指定临时表

SELECT

(SELECT DISTINCT

Salary

FROM

Employee

ORDER BY Salary DESC

LIMIT 1 OFFSET 1) AS SecondHighestSalary

【答案4.2】

【ifnull、limit】也可以直接用IFNULL关键字，语法：IFNULL(exp1，exp2)若exp1不为null,则返回exp1,否则范围exp2

SELECT

IFNULL(

(SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

LIMIT 1 OFFSET 1),

NULL) AS SecondHighestSalary

【题解】

要想获取第二高，需要排序，使用 order by（默认是升序 asc，即从小到大），若想降序则使用关键字 desc

去重，如果有多个相同的数据，使用关键字 distinct 去重

判断临界输出，如果不存在第二高的薪水，查询应返回 null，使用 ifNull（查询，null）方法

起别名，使用关键字 as ...

因为去了重，又按顺序排序，使用 limit（）方法，查询第二大的数据，即第二高的薪水，即 limit(1,1) （因为默认从0开始，所以第一个1是查询第二大的数，第二个1是表示往后显示多少条数据，这里只需要一条）

【问题5- join及IN的复杂查询】

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

+----+-------+--------+--------------+

+----+-------+--------+--------------+

| 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 |

+------------+----------+--------+

【答案5.1】

SELECT

Department.name AS 'Department',

Employee.name AS 'Employee',

Salary

FROM

Employee

JOIN

Department ON Employee.DepartmentId = Department.Id

WHERE

(Employee.DepartmentId , Salary) IN

( SELECT

DepartmentId, MAX(Salary)

FROM

Employee

GROUP BY DepartmentId

)

;

【题解】

因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。

SELECT

DepartmentId, MAX(Salary)

FROM

Employee

GROUP BY DepartmentId;

但是有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息，然后，我们可以把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。也就是上述的整个sql。

【问题6-Rank() over (partition by……order by……）】

Employee 表包含所有员工信息，每个员工有其对应的工号 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 |

+------------+----------+--------+

【答案6.1】

/**

解题思路:先对Employee表进行部门分组工资排名，再关联Department表查询部门名称，

再使用WHERE筛选出排名小于等于3的数据（也就是每个部门排名前3的工资）。

**/

SELECT

B.Name AS Department,

A.Name AS Employee,

A.Salary

FROM (SELECT DENSE_RANK() OVER (partition by DepartmentId order by Salary desc) AS ranking,DepartmentId,Name,Salary

FROM Employee) AS A

JOIN Department AS B ON A.DepartmentId=B.id

WHERE A.ranking<=3

【题解】

over —— 在什么条件之上。

partition by e.deptno —— 按部门编号划分（分区）。

order by e.sal desc —— 按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则为非法（都说要排序了，再不指定排序的字段是怕是不合适吧？））

rank()/dense_rank()/row_number() —— 排序

dense_rank(): 连续排序，在每个分组内,如果有两个第一级时，接下来仍然是第二级。

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。

dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

【答案6.2】

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

)

;

【问题7--delete】

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

+----+------------------+

【答案7.1】

DELETE p1 FROM Person p1,

Person p2

WHERE

p1.Email = p2.Email AND p1.Id > p2.Id

【问题8-datediff函数的使用】

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

返回结果不要求顺序。

查询结果格式如下例：

Weather

+----+------------+-------------+

| id | recordDate | Temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Result table:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

2015-01-02 的温度比前一天高（10 -> 25）

2015-01-04 的温度比前一天高（20 -> 30）

Datediff(日期1，日期2)输出的是两个日期的间隔

【答案8.1】

select w1.id from Weather w1 left join Weather w2 on datediff(w1.recordDate, w2.recordDate)=1 where w1.Temperature > w2.Temperature;

【问题9-mod函数】

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

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

例如，下表 cinema:

+---------+-----------+--------------+-----------+

+---------+-----------+--------------+-----------+

| 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 |

+---------+-----------+--------------+-----------+

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

+---------+-----------+--------------+-----------+

+---------+-----------+--------------+-----------+

| 5 | House card| Interesting| 9.1 |

| 1 | War | great 3D | 8.9 |

+---------+-----------+--------------+-----------+

【答案9.1】

1、select id, movie, description, rating from cinema where description not in ('boring') and id&1 order by rating desc;

2、select *

from cinema

where mod(id, 2) = 1 and description != 'boring'

order by rating DESC

mod函数：求余数，和%是一样的，mod(n,m)、n mod m、n%m返回的是n除以m的余数；

【问题10-hiving】

有一个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 |

+---------+

【答案10.1】

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 temp_table

WHERE

num >= 5

【问题11-update与case..when】

请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用单个 update 语句，且不产生中间临时表。

注意，你必须仅使用一条 update 语句，且不能使用 select 语句。

查询结果如下例所示

Salary 表

+----+------+-----+--------+

| id | name | sex | salary |

+----+------+-----+--------+

| 1 | A | m | 2500 |

| 2 | B | f | 1500 |

| 3 | C | m | 5500 |

| 4 | D | f | 500 |

+----+------+-----+--------+

【答案11】

update Salary SET sex=(

case sex when 'm' then 'f' else 'm' end

)

Z_JUAN1

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录