SQL语法面试
SQL语法面试
面试中sql问题回答的思路
掌握常见的函数以及多表联合查询:
- 题目一般是考核sql功底，会写的直接写出来，不会写的也建议写思路，这样拿到及格分
- 如果面试笔试中碰到了需要用很复杂函数的，可以写出思路就行，写成如果用代码自己如何实现的
一般互联网项目也不建议使用复杂的函数，这些一般由代码去编写，除了后台统计，一般不建议使用超过2个表以上的关联查询，因为性能会快速下降
多表关联查询与更新
- 多表关联查询范例
-- 左连接（每个join 的on在每个join后面）
SELECT 要查询的数据
FROM table1 a
LEFT JOIN table2 b
ON a.字段1=b.字段2
LEFT JOIN table3 c
ON a.字段1= c.字段3
WHERE 具体的查询条件
-- 内连接
SELECT 要查询的数据
FROM table1 a, table2 b, table3 c
WHERE a.字段1=b.字段2
AND a.字段1= c.字段3
AND 具体的查询条件
- 多表关联更新范例
-- update的通用写法为：
UPDATE table_references
SET col_name1=expr1 [,col_name2=expr2 ...] [WHERE where_definition]
-- 多表关联update写法为:
update 多个table关联 set 要update的值 where 具体的条件
-- 写法1：
UPDATE table1 a,
table2 b,
table3 c
SET a.name=b.name,
a.age=c.age
WHERE a.字段1=b.字段2
AND a.字段1= c.字段3
AND 具体的查询条件
-- 写法2：
UPDATE table1 a
LEFT JOIN table2 b
ON a.字段1=b.字段2
LEFT JOIN table3 c
ON a.字段1= c.字段3
SET a.name=b.name,
a.age=c.age
WHERE 具体的查询条件
排行榜问题
两个数据库表，用户表r_user和订单表r_order，如下：
CREATE TABLE `r_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
`order_amount` decimal(12,2) DEFAULT NULL COMMENT '订单金额',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`is_deleted` int(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `r_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
`phone` varchar(255) DEFAULT NULL COMMENT '手机号',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`is_deleted` int(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `r_order` VALUES ('1', '7', '3601.80', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('2', '6', '12867.18', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('3', '4', '3160.13', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('4', '7', '8337.95', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('5', '7', '12079.76', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('6', '2', '10573.99', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('7', '9', '287.93', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('8', '2', '404.68', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('9', '8', '4197.61', '2020-08-26 18:01:52', '0');
INSERT INTO `r_order` VALUES ('10', '5', '10212.51', '2020-08-26 18:01:52', '0');
INSERT INTO `r_user` VALUES ('1', '机器人1号', '18698291230', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('2', '机器人2号', '18698291330', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('3', '机器人3号', '18698291430', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('4', '机器人4号', '18698291530', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('5', '机器人5号', '18698291630', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('6', '机器人6号', '18698291730', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('7', '机器人7号', '18698291830', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('8', '机器人8号', '18698291930', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('9', '机器人9号', '18698292030', '2020-08-26 17:45:21', '0');
INSERT INTO `r_user` VALUES ('10', '机器人10号', '18698292130', '2020-08-26 17:45:21', '0');
订单表中一个用户可能会有多个订单，现在我们要统计订单总金额最高的前N名用户排行榜。输出格式见表格。
| 排名 | 用户手机号 | 订单总金额 |
|---|---|---|
| 1 | 186****3530 | 91125.73 |
| 2 | 186****4530 | 84706.45 |
| 3 | 186****3930 | 80842.01 |
| 4 | 186****0030 | 78002.46 |
| 5 | 186****4830 | 73964.09 |
| 6 | 186****8430 | 73232.76 |
题目思路：
- 根据订单金额分组
- 排序找出前十用户
- @rownum绘制排名
SELECT
@rownum := @rownum + 1 AS '排名',
u.phone AS '用户手机号',
b.amount AS '订单总金额'
FROM
(
SELECT
*
FROM
( SELECT user_id uid, sum( order_amount ) amount FROM r_order o GROUP BY o.user_id ) a
ORDER BY
a.amount DESC,
a.uid ASC
LIMIT 10
) b,
r_user u,
( SELECT @rownum := 0 ) r
WHERE
u.id = b.uid
TopN问题
典型题目
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 的工资排第二。
常规做法
group再topN没法直接写出来，思路就是每个员工薪资（即每行记录）与同部门的其他员工的薪资比较，看自己是否在top3
- COUNT(字段名) # 返回表中该字段总共有多少条记录
- DISTINCT 字段名 # 过滤字段中的重复记录
- 我们先找出公司部分里前 3 高的薪水，意思是不超过三个值比这些值大（即每个员工薪资与自己部门的其他人比较，看自己是否在top3中，即3>比自己薪资大的人数）
SELECT e1.Salary
FROM Employee AS e1
WHERE 3 >
(SELECT count(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e1.Salary < e2.Salary
AND e1.DepartmentId = e2.DepartmentId) ;
举个栗子：
当 e1 = e2 = [4,5,6,7,8]
e1.Salary = 4`，`e2.Salary`可以取值 `[5,6,7,8]`，`count(DISTINCT e2.Salary) = 4
e1.Salary = 5`，`e2.Salary`可以取值`[6,7,8]`，`count(DISTINCT e2.Salary) = 3
e1.Salary = 6`，`e2.Salary`可以取值 `[7,8]`，`count(DISTINCT e2.Salary) = 2
e1.Salary = 7`，`e2.Salary` 可以取值 `[8]`，`count(DISTINCT e2.Salary) = 1
e1.Salary = 8`，`e2.Salary` 可以取值 `[]`，`count(DISTINCT e2.Salary) = 0
最后 3 > count(DISTINCT e2.Salary)，所以e1.Salary可取值为[6,7,8]，即集合前 3 高的薪水
- 再把表 Department 和表 Employee 连接，获得各个部门工资前三高的员工。
SELECT Department.NAME AS Department,
e1.NAME AS Employee,
e1.Salary AS Salary
FROM Employee AS e1,Department
WHERE e1.DepartmentId = Department.Id
AND 3 >
(SELECT count( DISTINCT e2.Salary )
FROM Employee AS e2
WHERE e1.Salary < e2.Salary
AND e1.DepartmentId = e2.DepartmentId )
ORDER BY Department.NAME,Salary DESC;
窗口函数法
窗口函数法，该语法仅被Mysql 8.0以上版本支持；也是解决经典topN问题的最简单方法【该解法大致了解就行，因为必须限定在8.0以上，实际项目sql会写出解法1即可，这个属于锦上添花，可以再加上自己写代码如何解决（因为代码会考虑效率，会遵守相关sql规范）】
- 套用窗口函数模板
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
FROM Employee ) AS a
INNER JOIN Department b
ON a.DepartmentId = b.Id
WHERE Ranking <= 3;
典型题目：
“成绩表”记录了学生的学号，学生选修的课程，以及对应课程的成绩。
为了对学生成绩进行考核，现需要查询每门课程的前3高成绩。
注意：如果出现并列第一的情况，则同为第一名。
【解题思路】
题目要求出每个课程获得前三高成绩的所有学生。难点在于每个课程前3高成绩。
前3高的成绩意味着要对成绩排名。
这种题类型其实是“分组排名”，遇到这类型题就要想到用窗口函数。
专用窗口函数rank, dense_rank, row_number有什么区别呢？
它们的区别我举个例子，你们一下就能看懂：
SELECT *,
rank()
OVER (order by 成绩 desc) AS ranking, dense_rank()
OVER (order by 成绩 desc) AS dese_rank, row_number()
OVER (order by 成绩 desc) AS row_num
FROM 班级;
得到结果：
从上面的结果可以看出：
rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。
这三个函数的区别如下：
题目要求“如果出现并列第一的情况，则同为第一名”。所以，我们使用窗口函数dense_rank。
- 按课程分组(partiotion by 课程号)，并按成绩降序排列(order by 成绩 desc)，套入窗口函数的语法，就是下面的sql语句：
SELECT *,
dense_rank() over(partition by 课程号
ORDER BY 成绩 desc) as排名
FROM 成绩表;
运行结果如下：
- 筛选出前3高的成绩，所以我们在上一步基础上加入一个where字句来筛选出符合条件的数据。（where 排名 <=3）
SELECT 课程号,
学号,
成绩,
排名
FROM
(SELECT *,
dense_rank()
OVER (partition by 课程号
ORDER BY 成绩 desc) AS 排名
FROM 成绩表) AS aa
WHERE 排名 <=3;
topN问题总结
-
考察如何使用窗口函数及专用窗口函数排名的区别：rank(并列占位), dense_rank(并列占不位), row_number
-
经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。
# topN问题 sql模板SELECT * FROM (SELECT *, row_number() OVER (partition by 要分组的列名 ORDER BY 要排序的列名 desc) AS 排名 FROM 表名) AS a WHERE 排名 <= N; -
实际面试中写出来解法1即可，解法2可以提一下，然后知道实际项目的思路
符合要求问题
【题目】
用一条SQL语句查询出每门课都大于80分的学生姓名
成绩表：
| name | kecheng | fenshu |
|---|---|---|
| 张三 | 语文 | 79 |
| 张三 | 数学 | 80 |
| 李四 | 语文 | 70 |
| 李四 | 数学 | 96 |
| 王五 | 语文 | 80 |
| 王五 | 数学 | 87 |
| 王五 | 英语 | 97 |
【参考思路】
要查询出每门课程都大于80分的学生姓名，因为一个学生有多门课程，可能所有课程都大于80分，可能有些课程大于80分，另外一些课程少于80分，也可能所有课程都小于80分，那么我们要查找出所有大于80分的课程的学生姓名，我们可以反向思考，找出课程小于80分(可以找出有一些课程小于80分，所有课程小于80分的学生)的学生姓名再排除这些学生剩余的就是所有课程都大于80分的学生姓名了，
【参考答案】
相关sql如下
SELECT distinct(name)
FROM 成绩表
WHERE name NOT IN
(SELECT DISTINCT name
FROM 成绩表
WHERE fengshu<=80);
-- 【备注】 如果是实际项目中，not in需要优化，可以用left join然后部分条件null优化 类似SELECT distinct(a.name)
FROM 成绩表 a
LEFT JOIN
(SELECT distinct(name),
kecheng
FROM 成绩表
WHERE fengshu<=80 ) b
ON a.name=b.name
WHERE b.kecheng is null;
A大于B问题
【题目】
有三张表
学生表：
| sid | sname | gender | class_id |
|---|---|---|---|
| 1 | 张三 | 男 | 1 |
| 2 | 李四 | 男 | 1 |
| 3 | 王五 | 男 | 2 |
课程表：
| cid | cname | tearcher_id |
|---|---|---|
| 1 | 语文 | 1 |
| 2 | 数学 | 2 |
| 3 | 英语 | 3 |
成绩表：
| id | student_id | course_id | number |
|---|---|---|---|
| 1 | 1 | 1 | 79 |
| 2 | 1 | 2 | 80 |
| 3 | 1 | 3 | 70 |
| 4 | 2 | 1 | 66 |
| 5 | 2 | 2 | 80 |
| 6 | 2 | 3 | 87 |
| 7 | 3 | 1 | 97 |
| 8 | 3 | 2 | 69 |
| 9 | 3 | 3 | 89 |
要求查处语文成绩大于数学成绩的学生信息
【参考思路】
课程表和成绩表关联可以查询出课程成绩及学生studentid,关联思路为
select 要查询的信息 from （课程表与成绩表关联 where 课程=语文） a, (课程表与成绩表关联 where 课程=数学)b , 学生表 c
where a.学生id = b.学生id and a.成绩>b.成绩 and a.学生id=c.sid
小贴士：面试中编写sql可以用()让sql看起来更清楚
【参考答案】
相关sql如下
SELECT student.sid,
student.sname
FROM student ,
(SELECT *
FROM 成绩表， 课程表
WHERE 成绩表.course_id=课程表.cid
AND 课程表.cname='语文') AS tempChinese,
(SELECT *
FROM 成绩表， 课程表
WHERE 成绩表.course_id=课程表.cid
AND 课程表.cname='数学')as tempMath
WHERE student.sid=tempChinese.sid
AND tempChinese.sid = tempMath.sid
AND tempChinese.number>tempMath.number
-- 以上写法相关于inner join，即学生必须有语文和数学两门成绩
比赛关系问题
【题目】
一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合.
成绩表：
| name |
|---|
| a |
| b |
| c |
| d |
【参考思路】
这个是同一个表当两个表关联查询,a.name != b.name
但是这样会有重复，例如a跟b打过了，但是到b的话又出现了a(比赛提一次就可以了)，因此用a.name < b.name去掉重复
【参考答案】
相关sql如下
SELECT a.name,
b.name
FROM timp a, timp b
WHERE a.name > b.name
重复数据删除问题
假定表名为table1,其中cardname重复，可能有多个，现在要求如果有多个重复的则删除重复的只保留id最小的
首先是查询要删除的数据
select 数据 from table1 where cardname in (重复的cardname数据) and id not in (重复的cardname并且是最小的id)
完整的sql为：
SELECT id
FROM table1
WHERE cardname IN
(SELECT cardname
FROM table1
GROUP BY cardname
HAVING count(cardname) > 1 )
AND id NOT IN
(SELECT min(id)
FROM table1
GROUP BY cardname
HAVING count(cardname) > 1) );
然后就直接删除好了，因为mysql不允许直接delete where cardname in (相同的表)，即不能直接查询相同的数据然后删除（就是id in 括号中不能是个完整的相同表的查询sql，还是数据安全与并发的问题）
所以用个临时表delete from table1 where id in (select 查询sql as 临时表);
完整sql为：
DELETE
FROM table1
WHERE id in
(SELECT id
FROM table1
WHERE cardname IN
(SELECT cardname
FROM table1
GROUP BY cardname
HAVING count(cardname) > 1 )
AND id NOT IN
(SELECT min(id)
FROM table1
GROUP BY cardname
HAVING count(cardname) > 1) ) AS t );
【备注】
如果是多表关联查询有重复数据了，也是上面的思路，只是把其中部分切换为关联查询语句
实际项目数据库如何建表
实际数据库建表的参考原则是：
-
先确定真实存在实体
-
再确定实体之间的关联关系，然后1:1，1:n还是m:n的关系，其中m:n的关系就需要建表
建表只要符合上面的逻辑即可，另外，实际项目中，为了减少表关联查询次数，并不会完全遵守sql的第四范式，即用冗余换时间
下面我们以简单的电商部分数据为例来说一下数据库建表相关：
实体包括：会员，商品，收货地址
关联关系：
- 会员与商品的暂存关系（n:m),因此这个关联关系变成表，即购物车表（假设这个要存储再服务端）；
- 会员与收货地址是1:n的关系(1个会员有n个收货地址)，因此不会有表，把会员id存储到收货地址表中好了；
- 会员与商品的购买关系（n:m）,这个表就是订单表；
- 上面的购买关系即订单表中需要有收货地址，是n:1关系（1个订单只有1个收货地址，但是1个收货地址会对应n个订单），因此再订单表中加上收货地址的id即可
另外：为了直接在订单列表展示一些信息，订单表中除了商品id还会额外冗余商品名称，会冗余收货地址等等，这就是为了减少查询表关联来做冗余（不符合sql第四范式：修改商品信息可能还需要修改订单，但是实际中一般忽略了）
根据上面就确定了表，然后就是数据表的er图，范例如下：
一般建表都遵守以上过程，然后是与你的leader讨论，最后确定表sql结构，如上面的电商模块，也可以把订单当作实体，只要符合自己的思路就可以，一般是按照真实的需求实体这么定义.
