Mysql刷题笔记

第N高排序

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号 同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4 同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3 不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。最新OJ环境已更新至8.0版本，可直接使用窗口函数。

为此，本文提出以下几种解决思路，仅供参考。 如果有意可关注文末个人公众号，查看一篇更为详尽的分组排名问题。

思路1：单表查询

由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：

  • 同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
  • 排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。 注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。 注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。

代码1

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
# Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

思路2：子查询

排名第N的薪水意味着该表中存在N-1个比其更高的薪水 注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个 最后返回的薪水也应该去重，因为可能不止一个薪水排名第N 由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下 代码2

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT e.salary
      FROM 
          employee e
      WHERE 
          (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
  );
END

思路3：自连接

一般来说，能用子查询解决的问题也能用连接解决。具体到本题：

两表自连接，连接条件设定为表1的salary小于表2的salary 以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重 限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个 考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join 如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。 注：个人认为无需考虑N<=0的情形，毕竟无实际意义。 代码3

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          e1.salary
      FROM 
          employee e1 JOIN employee e2 ON e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

思路4：笛卡尔积

当然，可以很容易将思路2中的代码改为笛卡尔积连接形式，其执行过程实际上一致的，甚至MySQL执行时可能会优化成相同的查询语句。

代码4

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          e1.salary
      FROM 
          employee e1, employee e2 
      WHERE 
          e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

思路5：自定义变量

以上方法2-4中均存在两表关联的问题，表中记录数少时尚可接受，当记录数量较大且无法建立合适索引时，实测速度会比较慢，用算法复杂度来形容大概是O(n^2)量级（实际还与索引有关）。那么，用下面的自定义变量的方法可实现O(2*n)量级，速度会快得多，且与索引无关。

自定义变量实现按薪水降序后的数据排名，同薪同名不跳级，即3000、2000、2000、1000排名后为1、2、2、3； 对带有排名信息的临时表二次筛选，得到排名为N的薪水； 因为薪水排名为N的记录可能不止1个，用distinct去重 代码5

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT salary 
      FROM 
          (SELECT 
                salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
            FROM  
                employee, (SELECT @r:=0, @p:=NULL)init 
            ORDER BY 
                salary DESC) tmp
      WHERE rnk = N
  );
END

思路6：窗口函数

实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：

row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4 rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4 dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3 ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用 显然，本题是要用第三个函数。 另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

partition by，按某字段切分 order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据 注：下面代码仅在mysql8.0以上版本可用，最新OJ已支持。

代码6

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

至此，可以总结MySQL查询的一般性思路是：

能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高

不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果

能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证

自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现

如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。除了经典的获取3种排名信息，还有聚合函数、向前向后取值、百分位等，具体可参考官方指南。以下是官方给出的几个窗口函数的介绍：

最后的最后再补充一点，本题将查询语句封装成一个自定义函数并给出了模板，实际上是降低了对函数语法的书写要求和难度，而且提供的函数写法也较为精简。然而，自定义函数更一般化和常用的写法应该是分三步：

定义变量接收返回值 执行查询条件，并赋值给相应变量 返回结果 例如以解法5为例，如下写法可能更适合函数初学者理解和掌握：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    # i 定义变量接收返回值
​    DECLARE ans INT DEFAULT NULL;  
    # ii 执行查询语句，并赋值给相应变量
​    SELECT 
​        DISTINCT salary INTO ans
​    FROM 
​        (SELECT 
​            salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
​        FROM  
​            employee, (SELECT @r:=0, @p:=NULL)init 
​        ORDER BY 
​            salary DESC) tmp
​    WHERE rnk = N;

    # iii 返回查询结果，注意函数名中是 returns，而函数体中是 return
​    RETURN ans;
END

mysql :=和=的区别

  • = 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=。

  • := 不只在set和update时时赋值的作用，在select也是赋值的作用。

mysql limit/limit offset分页查询

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。

  • limit start, count start 默认值为0，表示跳过多少数据开始查询 count表示跳过start条数据后，要查询count条数据
  • limit a offset b 他代表的意思是跳过b条数据后查询a条
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15。
mysql> SELECT * FROM table LIMIT 10 OFFSET 5;// 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   

//如果只给定一个参数，它表示返回最大的记录行数目：    
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 

//换句话说，LIMIT n 等价于 LIMIT 0,n。 

SELECT * FROM table LIMIT 5,10; ------查询6-15的数据。第五行后面的10条数据
SELECT * FROM table LIMIT 5;------查询前五行数据
第一个参数是偏移量，第二个参数是查询几条数据

mysql contact

# Write your MySQL query statement below
# 一、计算字段

# 其实本题主要考察的就是计算字段的使用。
# 二、知识点
# 2.1 CONCAT() 函数

# CONCAT 可以将多个字符串拼接在一起。
# 2.2 LEFT(str, length) 函数

# 从左开始截取字符串，length 是截取的长度。
# 2.3 UPPER(str) 与 LOWER(str)

# UPPER(str) 将字符串中所有字符转为大写

# LOWER(str) 将字符串中所有字符转为小写
# 2.4 SUBSTRING(str, begin, end)

# 截取字符串，end 不写默认为空。

# SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

# CONCAT 用来拼接字符串 ● LEFT 从左边截取字符 ● RIGHT 从右边截取字符 ● UPPER 变为大写 ● LOWER 变为小写 ● LENGTH 获取字符串长度

# select user_id, CONCAT(UPPER(left(name, 1)), LOWER(SUBSTRING(name, 2))) as name
select user_id, CONCAT(UPPER(left(name, 1)), LOWER(RIGHT(name, length(name) - 1))) as name
from Users
order by user_id

mysql group_concat

  • 理解：group_concat()函数，顾名思义与group by 有关，
  • 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。 所以最后是对sell_date分组才能达到效果，group by sell_date

mysql like

select patient_id, patient_name, conditions
from patients
where conditions like '% DIAB1%'
# 注意上面的DIAB1前有一个空格，防止匹配到类似'ABCDIAB1'这种字符串
or conditions like 'DIAB1%'
# 为了匹配conditions字段以DIAB1开头的记录，因此没有空格

mysql union & union all

  • UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
  • UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同。
SELECT column_name FROM table1

UNION

SELECT column_name FROM table2
  • UNION 默认会去掉重复记录值再合并成结果集，如果需要保留重复的记录值，请使用 UNION ALL。
  • 当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行
SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2
  • UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

mysql having

  • mysql中，当我们用到聚合函数，如sum，count后，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having和group by是组合着用的
  • 注意 having后的判断字段必须是聚合函数返回的结果

mysql group

可是为了能够更好的理解“group by”多个列“和”聚合函数“的应用，我建议在思考的过程中，由表1到表2的过程中，增加一个虚构的中间表：虚拟表3。下面说说如何来思考上面SQL语句执行情况：

1.FROM test：该句执行后，应该结果和表1一样，就是原来的表。

2.FROM test Group BY name：该句执行后，我们想象生成了虚拟表3，如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

img

3.接下来就要针对虚拟表3执行Select语句了：

（1）如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以你看，执行select * 语句就报错了。

（2）我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

（3）那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

（4）例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表3的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：

img

（5）group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图img

（6）接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number，结果如下图：

img（表6)

需要注意的是：以上excel仅是帮助理解的，实际使用可能有些不同，例如虽然表格中group的列合并为了一个，实际上也是可以进行正确的count（column）操作的： EG:统计同名名称各个名称下有多少人同名：

select name from t_6 group by name having count(name)>1

mysql 行列转置

1650679456(1).jpg

# Write your MySQL query statement below
#把每一列转成行
#一列一列处理：把“列名”做为新列的value（如本题的store），把原来的value也作为新列（如本题的price），这是一个查询，其他列不要
#用union all拼接每一列的结果
#注意本题如果这一产品在商店里没有出售，则不输出这一行，所以要原列 is not null的筛选条件

select 
    product_id,'store1' as store, store1 as price 
from 
    Products 
where 
    store1 is not null
union all
select 
    product_id,'store2' as store, store2 as price  
from 
    Products 
where 
    store2 is not null
union all
select 
    product_id,'store3' as store, store3 as price
from 
    Products 
where 
    store3 is not null

OR

select 
    product_id,'store1' as store, store1 as price 
from 
    Products 
where 
    store1 is not null
union all
select 
    product_id,'store2', store2  
from 
    Products 
where 
    store2 is not null
union all
select 
    product_id,'store3', store3
from 
    Products 
where 
    store3 is not null

第二高薪水

方法一：使用子查询和 LIMIT 子句

将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表（本题要求如果无数据查出则返回null，这里我们把上面查询的结果作为临时表，如果无数据则返回null）。

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

方法二：使用 IFNULL 和 LIMIT 子句 解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数，如下所示。

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

mysql ifnull

IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

SELECT IFNULL(NULL,'B');    -- 输出结果：B
SELECT IFNULL('HELLO','B'); -- 输出结果：HELLO

mysql join 合并表相关

inner join：2表值都存在

outer join：附表中值可能存在null的情况。

总结：

①A inner join B：取交集

②A left join B：取A全部，B没有对应的值，则为null

③A right join B：取B全部，A没有对应的值，则为null

④A full outer join B：取并集，彼此没有对应的值为null

上述4种的对应条件，在on后填写。

更新部分字段

如果现在需要Mysql更新字段重部分数据，而不是全部数据，应该采用何种方法呢？下面介绍了两种情况下Mysql更新字段中部分数据的方法，供您参考。

Mysql更新字段中部分数据第一种情况：

update tab set A = concat(substring(A,1,3),’bbb’);

从A的1个字符开始取3个字符，加上’bbb’，再写入a中，如果A原始值为’123aaa’，那么更新之后为’123bbb’了。

Mysql更新字段中部分数据第二种情况:

1.Mysql模糊查询:

select * from table where num like ‘%a%’; 查询有a的字符串2.Mysql更新字段值的部分数据:

我们可能会在项目中碰到要求将数据库中某个字段的值修改成另外一个，那么大家可能会马上想到用update.举例说明：

将字段A中的值为1的，全部替换成2. SQL语句就是这么写：update table set a=2 where a=1;

那么如果我们要讲字段A值中的包含的1，替换成2呢？ 例如：a=2211，现在要替换成2222,就是把1换成2 SQl语句这么写：update table set a=REPLACE(a,’1’,’2’);