数据库常见的操作

2020-10-11

Word count: 2.2k | Reading time≈ 11 min

数据库的操作

常见查询语句

每个用户最新登录的时间

//每个用户最新登录的时间
select Max(date) from login group by user_id order by user_id;
//或者（先group分组用户，再在该组里order by排序limit取值）
SELECT (SELECT DATE FROM login WHERE user_id = l.user_id ORDER BY DATE DESC LIMIT 1)d FROM login l GROUP BY user_id

每个用户最近一天登录的日子

1
2
3

//每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序
SELECT DISTINCT(u.name),c.name ,MAX(l.DATE) FROM login l,CLIENT c,USER u 
WHERE l.user_id =u.id AND l.client_id=c.id GROUP BY l.user_id ORDER BY u.name

查询分组里前2名

//分区函数partition by ，不重复排序row_number()，相同同名跳跃rank(),
//连续相同归一组dense_rank(),NTILE(group_num)将所有记录分成group_num个组每组序号一样
select g.id, l.name, g.score
from (select *, 
      dense_rank() over(partition by language_id order by score desc) as rank 
      from grade) g, 
      language l
where g.language_id = l.id and g.rank <= 2
order by l.name asc, g.score desc, g.id asc

LeetCode

重新格式化部门表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
链接：https://leetcode-cn.com/problems/reformat-department-table
# Write your MySQL query statement below

select id,
max(if( month='Jan', revenue,null )) as Jan_Revenue,
max(if( month='Feb', revenue,null )) as Feb_Revenue,
max(if( month='Mar', revenue,null )) as Mar_Revenue,
max(if( month='Apr', revenue,null )) as Apr_Revenue,
max(if( month='May', revenue,null )) as May_Revenue,
max(if( month='Jun', revenue,null )) as Jun_Revenue,
max(if( month='Jul', revenue,null )) as Jul_Revenue,
max(if( month='Aug', revenue,null )) as Aug_Revenue,
max(if( month='Sep', revenue,null )) as Sep_Revenue,
max(if( month='Oct', revenue,null )) as Oct_Revenue,
max(if( month='Nov', revenue,null )) as Nov_Revenue,
max(if( month='Dec', revenue,null )) as Dec_Revenue
 from Department group by id

交换工资

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

# Write your MySQL query statement below
#select s.id,s.name,(if(s.sex='f','m','f'))sex,s.salary from salary s
update salary set sex=if(sex='f','m','f')

换座位

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
# Write your MySQL query statement below
#select id from seat where mod(id,2)=0 #and id< count(id);
#select * from seat a , seat b  on
(b.id in (select * from (select id from seat where mod(id,2)=0)temp) and a.id=b.id-1)
#set a.student=b.student and b.student =a.student;

select * from (
    select (if(mod(s.id,2)=1,if(s.id<(select count(*) from seat),s.id+1,s.id),s.id-1)) id,s.student 
            from seat s
    )temp order by temp.id

有趣的电影

+---------+-----------+--------------+-----------+
|   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 mod(id,2)=1 and description!='boring' 
order by rating desc

超过5名学生的课

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+
应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+
# Write your MySQL query statement below

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

大的国家

+-----------------+------------+------------+--------------+---------------+
| 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 area > 3000000 or population > 25000000;

删除重复的电子邮箱

+----+------------------+
| 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  |
+----+------------------+
# Write your MySQL query statement below

#delete * from Person b  where b.Id>(select min(id) from Person where Email =b.Email) ;

DELETE FROM Person WHERE Id NOT IN (select * from (SELECT MIN(Id) 
FROM Person GROUP BY Email)tmp) ;

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

+----+-------+--------+--------------+
| 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  |
+------------+----------+--------+
# Write your MySQL query statement below

select d.Name as Department,e.Name as Employee ,e.Salary 
from Department d , Employee e where e.DepartmentId = d.id 
and (e.Salary >=(select Salary from Employee 
where DepartmentId= d.id 
group by Salary order by Salary desc limit 2,1) 
or (select count(distinct Salary) from Employee where DepartmentId= d.id)<4);

部门工资最高的员工

+----+-------+--------+--------------+
| 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  |
+------------+----------+--------+
# Write your MySQL query statement below

select d.Name as Department,e.Name as Employee ,e.Salary 
from Department d , Employee e 
where e.DepartmentId = d.id and e. Salary =(select max(Salary) from Employee 
where DepartmentId= d.id)

从不订购的客户

Customers 表：
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
# Write your MySQL query statement below

select  Name as Customers from Customers where Id not in
(select distinct CustomerId from Orders);

查找重复的电子邮箱

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
# Write your MySQL query statement below
select Email from Person  group by Email having count(id)>1;

超过经理收入的员工

+----+-------+--------+-----------+
| 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      |
+----------+
# Write your MySQL query statement below
select a.Name as Employee from Employee a,Employee b 
where a.ManagerId = b.Id and a.Salary>b.Salary;

连续出现的数字

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
# Write your MySQL query statement below

select distinct(a.Num) as ConsecutiveNums from
Logs a,Logs b,Logs c 
where a.id=b.id +1 and b.id= c.id+1
and a.Num=b.Num and b.Num=c.Num;

分数排名

+----+-------+
| 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    |
+-------+------+
# Write your MySQL query statement below
#SELECT a.Score, ((SELECT COUNT(DISTINCT b.Score) FROM Scores b  
#WHERE a.Score < b.Score) + 1 )'Rank' FROM Scores a order by a.Score DESC;
SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS Rank
       FROM Scores ORDER BY Score DESC;

第N高的薪水

+----+--------+
| 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 DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N, 1

  );
END

第二高的薪水

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

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+
# Write your MySQL query statement below
SELECT (SELECT DISTINCT(Salary)  FROM Employee
ORDER BY Salary DESC LIMIT 1,1) AS SecondHighestSalary;

组合两个表

表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
# Write your MySQL query statement below

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

Copyright： Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.

数据库的操作

常见查询语句

每个用户最新登录的时间

每个用户最近一天登录的日子

查询分组里前2名

LeetCode

重新格式化部门表

交换工资

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

换座位

有趣的电影

超过5名学生的课

大的国家

删除重复的电子邮箱

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

部门工资最高的员工

从不订购的客户

查找重复的电子邮箱

超过经理收入的员工

连续出现的数字

分数排名

第N高的薪水

第二高的薪水

组合两个表