数据库常见19道题

lee175组合两个表

SQL架构

表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

solution

用左连接 left join

SELECT FirstName, LastName, City, State
FROM Person P
	LEFT JOIN Address A ON P.PersonId = A.PersonId;

lee176

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

±—±-------+

Id	Salary
1	100
2	200
3	300

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

±--------------------+

SecondHighestSalary
200

±--------------------+

solution

1. 返回的值是唯一 要用到 distinct

2. 排序用 order by ，
   降序：DESC

3. LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
   eg:
   SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

4. select as xxx 新建一列 设为

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

lee177 第n高的薪水

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

±—±-------+

Id	Salary
1	100
2	200
3	300

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

±-----------------------+

getNthHighestSalary(2)
200

±-----------------------+

solution

1. 这是一个函数
2. 和上一题一样的格式
   就注意函数的格式就行

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	SET N = N - 1;
    RETURN (
        SELECT (
            SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            LIMIT N, 1   // 注意这里不能n-1
        )
    );
END

lee178 分数排名 看懂了点

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

±—±------+

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

±------±-----+

solution

1. count(distinct 列名)—只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。
2. 首先，我们需要对分数进行倒序排序(以下得出的结果是没有去重过的倒序的分数)
   select Score from Scores order by Score DESC
3. 接下来我们还缺少一列Rank。例如：一个班有20个人，但是所有人都考了95分，那么全班同学都是并列第一即名次只有一个；如果一部分同学考了95，一部分考了90，一部分考了85，那么整个成绩的排名就分为3个名次。（以下得出的结果是去重之后名次的总数）
   select count(distinct Score) from Scores
4. 最后考虑如何将前面的两者结合起来，并且将各自的名次显示出来。在我们执行第二步的时候，是否可以这么考虑，在对分数进行去重统计总数的基础上，我们再加一个条件，就是统计去重总数的同时我们筛选出来比我们分数高的人的个数（因为这里是去重的统计，所以最终的出来的名次就是该人的真实名次，不会出现比如有两个人是并列第一，而接下来的那个人却是排名第三的这种错误的情况）这里+1的理解是因为条件部分使用的是 > 而不是>=,如果使用的是>=的话就不需要+1操

SELECT s1.Score, COUNT(DISTINCT s2.Score) Rank
FROM Scores s1
JOIN Scores s2 ON s1.Score <= s2.Score
GROUP BY s1.Id, s1.Score
ORDER BY s1.Score DESC

180.连续出现的数字

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

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

±—±----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+

ConsecutiveNums
1

±----------------+

select distinct l1.Num ConsecutiveNums 
--注意要加distinct
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

181.超过经理收入的员工

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

±—±------±-------±----------+

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

±---------+

select e1.name Employee
from Employee e1
join Employee e2
on e1.ManagerId=e2.Id and e1.Salary>e2.Salary;

或者

select e1.name Employee
from Employee e1,Employee e2
where e1.ManagerId=e2.Id and e1.Salary>e2.Salary;

182.查找重复的电子邮箱

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

示例：

±—±--------+

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

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

±--------+

Email
a@b.com

±--------+

select Email
from Person 
group by Email
having count(Email)>1;// 根据group by 的分组来

183. 从不订购的客户

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

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

±—±------+
Orders 表：

±—±-----------+

Id	CustomerId
1	3
2	1

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

±----------+

Customers
Henry
Max

±----------+

solution1 左连接

select c.Name as Customers
from Customers c
left join Orders o 
on c.Id=o.CustomerId 
where o.CustomerId is null;// 是is null 不是=null

solution2 嵌套

select Name as Customers
from Customers c
where c.Id not in{
    select CustomerId
    from Orders
}

184.部门工资最高的员工

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

±—±------±-------±-------------+

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1

±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+

Id	Name
1	IT
2	Sales

±—±---------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

±-----------±---------±-------+

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

±-----------±---------±-------+

select d.Name as Department ,e.Name as Employee, e.Salary
from Employee e,Department d 
where e.DepartmentId=d.Id 
and  (e.Salary,e.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId);

185.部门工资前三高的员工

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,e.Name as Employee,e.Salary as Salary
from Employee  e inner join Department  d 
# inner join效率更高，比join、left join
on e.DepartmentId = d.Id
where e.Id in
(
    select e1.Id
    from Employee as e1 left join Employee as e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.Id
    having count(distinct e2.Salary) <= 2
)
and e.DepartmentId in (select Id from Department)

自己写了一遍：

select d.Name as Department ,e.Name as Employee ,e.Salary as Salary
from Employee e
inner join Department d         # 先用join连接
on e.DepartmentId=d.Id
where(
    select count(distinct Salary)
    from Employee e2
    where e.DepartmentId=e2.DepartmentId
    and e2.Salary>=e.Salary
)<=3

196.删除重复的电子邮箱

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

±—±-----------------+

solution1

delete p1 
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id>p2.Id;

solution2

这是因为MySQL不允许同时查询和删除一张表，我们可以通过子查询的方式包装一下即可避免这个报错

delete 
from Person
where Id not in(
  --加上这个外层筛选可以避免You can't specify target table for update in FROM clause错误
    select t.Id from(
        select min(Id) Id
        from Person 
        group by Email
    ) t
)

197.上升的温度

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

±--------±-----------------±-----------------+

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

±--------±-----------------±-----------------+
例如，根据上述给定的 Weather 表格，返回如下 Id:

±—+

Id
2
4

±—+

solution

学到了DATEDIFF是两个日期的天数差集

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

262.行程的用户

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

±—±----------±----------±--------±-------------------±---------+

Id	Client_Id	Driver_Id	City_Id	Status	Request_at
1	1	10	1	completed	2013-10-01
2	2	11	1	cancelled_by_driver	2013-10-01
3	3	12	6	completed	2013-10-01
4	4	13	6	cancelled_by_client	2013-10-01
5	1	10	1	completed	2013-10-02
6	2	11	6	completed	2013-10-02
7	3	12	6	completed	2013-10-02
8	2	12	12	completed	2013-10-03
9	3	10	12	completed	2013-10-03
10	4	13	12	cancelled_by_driver	2013-10-03

±—±----------±----------±--------±-------------------±---------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

±---------±-------±-------+

Users_Id	Banned	Role
1	No	client
2	Yes	client
3	No	client
4	No	client
10	No	driver
11	No	driver
12	No	driver
13	No	driver

±---------±-------±-------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

±-----------±------------------+

Day	Cancellation Rate
2013-10-01	0.33
2013-10-02	0.00
2013-10-03	0.50

±-----------±------------------+

595.大的国家

这里有张 World 表

±----------------±-----------±-----------±-------------±--------------+

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

±-------------±------------±-------------+

select name, population, area
from world
where area > 3000000 or population > 25000000

范围查询会全表扫描，尽量不要用，用union

select name,population,area 
from World 
where area > 3000000 
union 
select name,population,area 
from World 
where population > 25000000

596. 超过5名学生的课

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

±--------+
Note:
学生在每个课中不应被重复计算。

solution

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

601.体育馆人流量

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

例如，表 stadium：

±-----±-----------±----------+

id	visit_date	people
1	2017-01-01	10
2	2017-01-02	109
3	2017-01-03	150
4	2017-01-04	99
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

±-----±-----------±----------+
对于上面的示例数据，输出为：

±-----±-----------±----------+

id	visit_date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

±-----±-----------±----------+

提示：
每天只有一行记录，日期随着 id 的增加而增加。

select distinct s4.*
from stadium s1,stadium s2,stadium s3,stadium s4
where s1.id=s2.id-1 and s2.id=s3.id-1
and s1.people>=100 and s2.people>=100 and
s3.people>=100
and s4.id in(s1.id,s2.id,s3.id)

620.有趣的电影

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

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

例如，下表 cinema:

±--------±----------±-------------±----------+

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

±--------±----------±-------------±----------+

solution

自己写的 根据之前那个not in，比较麻烦

select c.id as id,c.movie as movie,c.description,c.rating
from cinema c
where c.id not in (
    select c1.id
    from cinema c1
    where c1.description='boring '    --注意是单引号
    or id%2=0
)
order by rating desc

别人写的，直接就可以用！=‘ xxx’ 来

select c.id as id,c.movie as movie,c.description,c.rating
from cinema c
where c.description!='boring'
and c.id%2=1
order by c.rating desc;

626.换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

±--------±--------+

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

±--------±--------+
假如数据输入的是上表，则输出结果如下：

±--------±--------+

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

±--------±--------+
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

solution

自己想不出来怎么改
看大佬们的，直接就改id就可以 奇数+1，偶数-1
特殊情况最后一个不动

select里格式

select(
	case when....then....
	     when....then....
	     else ....
	     end
)as id,.......

select (
    case when id%2=0 then id-1 
         when id%2=1 and id != (select max(id) from seat) then id+1 
         else id 
         end
    ) as id, student
from seat
order by id;

627.交换工资

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

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

solution

if( 1, 2, 3) 如果1成立，取2，否则取3

update salary set sex = if(sex = 'm','f','m')