SQL练习题（一）

ゞ浴缸里的玫瑰 2021-12-03 10:21 289阅读 0赞

*  题目一：交换性别（简单）
    
     *  实现思路
     *  提交代码
 *  题目二：连续出现的数字（中等）
    
     *  实现思路
        
         *  方式一
         *  方式二
     *  提交代码
        
         *  方式一
         *  方式二
 *  题目三：换座位（中等）
    
     *  实现思路
        
         *  方式一
         *  方式二
     *  提交代码
        
         *  方式一
         *  方式二
 *  题目知识点
    
     *  &：按位与
     *  IF 语句
     *  IFNULL 函数
     *  CASE WHEN 语句
     *  MOD 函数
 *  参考文档

> 以下题目均来源力扣（[LeetCode][]），括号中的标注为难度级别

# 题目一：交换性别（简单） #

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

> 您只能写一个 Update 语句，请不要编写任何 Select 语句。
> 
> 执行该 Update 语句后，再次查询 salary 表，即可得到性别交换后的表

![1702087-20190711180045753-1262086387.png][]

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

## 实现思路 ##

> 根据题干要求，使用 update 语句，更新 salary 表中所有的性别字段
> 
> 当 sex 字段值为 'm' 时，将值更新成 'f'
> 
> 当 sex 字段值不为 'm' 时（即 'f' 时），将值更新为 'm'
> 
> 如此便实现了 'm' 与 'f' 值的相互替换

## 提交代码 ##

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

# 题目二：连续出现的数字（中等） #

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

![1702087-20190711180446164-41844780.png][]

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

## 实现思路 ##

### 方式一 ###

1.  因为是判断连续出现至少三次，所以将表自关联两次
2.  一行记录中表示包含了连续的三条记录
3.  判断一行记录中的 *Num* 值是否全部相等，相等时，*Num* 值即为连续出现三次的数字

### 方式二 ###

1.  根据需求判断连续出现三次的数字
2.  **通过在单表查询中对每一条记录都增加两个判断条件**，判断该条记录的后两条记录（num值是不变的）是否在表中存在

## 提交代码 ##

### 方式一 ###

SELECT DISTINCT a.num AS ConsecutiveNums FROM logs a LEFT JOIN logs b ON b.id = a.id + 1 LEFT JOIN logs c ON c.id = b.id + 1 WHERE a.num = b.num AND b.num = c.num

### 方式二 ###

SELECT DISTINCT num AS ConsecutiveNums FROM logs WHERE (id + 1, num) IN ( SELECT * FROM logs ) AND (id + 2, num) IN ( SELECT * FROM logs )

> 说明：
> 
>  *  两种解题方式都是基于 id 为连续的情况下
>  *  两种方式查询，字段都需要进行去重，因为该数字可能连续出现三次以上

# 题目三：换座位（中等） #

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

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

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

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

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

![1702087-20190711180458979-1128835722.png][]

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

## 实现思路 ##

### 方式一 ###

1.  通过左外连接实现表的自关联
2.  **通过增加 if 条件来改变关联表中的座位id**
3.  按照座位表的 id 进行升序排列并显示查询字段

> 注意：
> 
>  *  通过 if 条件判断时不能使用内连接的方式，因为当学生数量为奇数时，则最后一位学生查询不到
>  *  student字段需要非空判断，因为当学生数量为奇数时，关联表中无数据

### 方式二 ###

1.  通过 *case...when...* 语句来交换座位的 id
2.  id 为奇数并且为最后一位学生时，id 值不改变（题干要求）
3.  其他情况下，id 为奇数时，id 值加1
4.  以上条件都不满足，则 id 值减1

> 注意：
> 
>  *  可以通过 count 函数来得到最后一位学生的id（id 为连续递增时）
>  *  case...when 语句中，多个 when 条件时，一个 when 满足条件就不再执行下面的when

## 提交代码 ##

### 方式一 ###

SELECT s1.id, ifnull(s2.student, s1.student) AS student FROM seat s1 LEFT JOIN seat s2 ON if(s1.id & 1, s1.id = s2.id - 1, s1.id = s2.id + 1) ORDER BY s1.id

### 方式二 ###

SELECT CASE WHEN mod(id, 2) = 1 AND id = ( SELECT COUNT(id) FROM seat ) THEN id WHEN mod(id, 2) = 1 THEN id + 1 ELSE id - 1 END AS id, student FROM seat ORDER BY id

# 题目知识点 #

## &：按位与 ##

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

上述换座位题目的解题方式一中使用 if 语句根据 id 的奇偶性执行不同的操作：

mysql> select 2&1;
    +-----+
    | 2&1 |
    +-----+
    |   0 |
    +-----+

> 理解为：奇数 & 1 = 1，偶数 & 1 = 0

## IF 语句 ##

MySQL的 IF 既可以作为表达式（函数）用，也可在存储过程中作为流程控制语句使用，如下是做为函数使用：

IF(expr,v1,v2)

如果 expr 是**TRUE (expr1 <> 0 and expr1 <> NULL) 或者表达式成立**，返回结果 v1；否则，返回结果 v2。 （<> 号为不等于）

> 作为表达式的 if 也可以用 *case...when...* 来实现

## IFNULL 函数 ##

IFNULL(v1,v2)

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

## CASE WHEN 语句 ##

CASE expression
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
       ...
        WHEN conditionN THEN resultN
        ELSE result
    END

CASE 表示函数开始，END 表示函数结束。如果 condition1 条件成立，则返回 result1, 如果 condition2 条件成立，则返回 result2，**当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了**。

## MOD 函数 ##

MOD(x,y)

返回 x 除以 y 以后的余数

# 参考文档 #

*  [MySQL 函数 | 菜鸟教程][MySQL _ _]

转载于:https://www.cnblogs.com/zhiyin1209/p/11171680.html

[LeetCode]: https://leetcode-cn.com/problems/exchange-seats
[1702087-20190711180045753-1262086387.png]: /images/20211203/2988470b17cd42ff888b4e68e6421292.png
[1702087-20190711180446164-41844780.png]: /images/20211203/db594c6832084c768cc3b4f914682b74.png
[1702087-20190711180458979-1128835722.png]: /images/20211203/e08cecf1267445a2b144ad5bf4b36267.png
[MySQL _ _]: https://www.runoob.com/mysql/mysql-functions.html