Mysql 数据库网题笔记

最新推荐文章于 2024-09-14 15:21:37 发布

wenjie1388

最新推荐文章于 2024-09-14 15:21:37 发布

阅读量310

点赞数

分类专栏：数据库网题-Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41935112/article/details/126444352

版权

网题-Mysql 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

笔记目录

176. 第二高的薪水
- 笔记
178. 分数排名
- 笔记
1757. 可回收且低脂的产品
- 笔记
尾锚点

176. 第二高的薪水

笔记

1、limit 0,1; 和 limit 0 offset 1; 是不同的取值。
2、创建临时表可以和 IFNULL 函数起到相同作用。

mysql> select salay from Employee group by salay order by salay limit 0,1;
+-------+
| salay |
+-------+
|   300 |
+-------+
1 row in set (0.00 sec)

mysql> select salay from Employee group by salay order by salay limit 0 offset 1;
Empty set (0.00 sec)

mysql> select (select salay from Employee group by salay order by salay limit 0 offset 1)
    -> ;
+-----------------------------------------------------------------------------+
| (select salay from Employee group by salay order by salay limit 0 offset 1) |
+-----------------------------------------------------------------------------+
|                                                                        NULL |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (select salay from Employee group by salay order by salay limit 0 offset 1) as tmp;
+------+
| tmp  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

178. 分数排名

笔记

rank() over
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7
dense_rank() over
作用：查出指定条件后的进行排名，条件相同排名相同，排名连续。
说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6
row_number() over
作用：查出指定条件后的进行排名，条件相同排名也不相同，排名连续。
说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

作者：friendly-chandrasekharhye
链接：https://leetcode.cn/problems/rank-scores/solution/by-friendly-chandrasekharhye-d70u/

mysql> select salay ,rank() over( order by salay desc) from Employee;
+-------+-----------------------------------+
| salay | rank() over( order by salay desc) |
+-------+-----------------------------------+
|   900 |                                 1 |
|   500 |                                 2 |
|   300 |                                 3 |
|   300 |                                 3 |
|   300 |                                 3 |
|   300 |                                 3 |
|   300 |                                 3 |
|   200 |                                 8 |
|   100 |                                 9 |
+-------+-----------------------------------+
9 rows in set (0.00 sec)

mysql> select salay ,dense_rank() over( order by salay desc) from Employee;
+-------+-----------------------------------------+
| salay | dense_rank() over( order by salay desc) |
+-------+-----------------------------------------+
|   900 |                                       1 |
|   500 |                                       2 |
|   300 |                                       3 |
|   300 |                                       3 |
|   300 |                                       3 |
|   300 |                                       3 |
|   300 |                                       3 |
|   200 |                                       4 |
|   100 |                                       5 |
+-------+-----------------------------------------+
9 rows in set (0.00 sec)

mysql> select salay ,row_number() over( order by salay desc) from Employee;
+-------+-----------------------------------------+
| salay | row_number() over( order by salay desc) |
+-------+-----------------------------------------+
|   900 |                                       1 |
|   500 |                                       2 |
|   300 |                                       3 |
|   300 |                                       4 |
|   300 |                                       5 |
|   300 |                                       6 |
|   300 |                                       7 |
|   200 |                                       8 |
|   100 |                                       9 |
+-------+-----------------------------------------+
9 rows in set (0.00 sec)

2022/8/25

1757. 可回收且低脂的产品

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是 2。

对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/find-customer-referee
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

笔记

一. 基础知识
1.1 三值逻辑
题目虽简单，但是背后包含的知识点真的也不少，你都掌握了吗？

NULL有两种，“未知”unknown和“inapplicable”不适用。不知道戴眼镜的人眼睛是什么颜色，就是未知。只要不摘掉眼睛，就不知道，但是这个人眼睛肯定有颜色的。不知道冰箱的眼睛是什么颜色。这就是不适用，这个属性不适用于冰箱。冰箱是没有眼睛的。现在DBMS都将这两种类型NULL归为了一类，并采用三值逻辑。

1.2 为什么是IS NULL, 而不是"= NULL"?

很奇怪，是不是？小学的时候学的=就是表示相等关系。但是，对NULL使用谓词得到的结果是unknown。

Tip: SQL的保留字中，很多都被归类为谓词一类，例如>,<>,= 等比较谓词，以及BETWEEN, LIKE, IN, IS NULL等。总结，谓词是一种特殊的函数，返回值是真值。(前面提到的诶个谓词，返回值都是true, false, unknown,SQL是三值逻辑，所以有三种真值）

因为查询结果只会包含WHERE子句里的判断结果为true的行！不包含判断结果为false和unknown的行。且不仅是等号，对NULL使用其他比较谓词（比如> NULL），结果也都是unknown。

重点理解：
NULL不是值，所以不能对其使用谓词，如果使用谓词，结果是unknown。
可以认为它只是一个没有值的标记,而比较谓词只适用于比较值。因此对非值的NULL使用比较谓词是没有意义的

1.3 如何理解IS NULL?是两个单词？IS空格NULL?
"NULL值" 和 "列的值为NULL"这个说法是错误的。NULL不属于关系型数据库中的某种类型。
我们为什么会误认为NULL是一个值？
可能因为混淆了别的语言，在一些语言中NULL是一个常量。还有个重要原因是IS NULL是两个单词，所以我以前也把IS当作谓词，比如IS-A,所以误认为NULL是一个值。特别是SQL里有IS TRUE, IS FALSE。在讲解SQL标准的书里提醒人那么样，我们应该把IS NULL看作一个谓词，如果可以IS_NULL或许更合适。

1.4 三值逻辑运算。
unknown小写，是第三个真值。与作为NULL的一种UNKNOWN(未知)是不同的东西。小写是明确的布尔类型的真值，后者大写的既不是值也不是变量。为了对比不同：看x=x的情况。


unknown = unknown -> true
UNKNOWN = UNKNOWN ->unknown
重点：【三值逻辑运算】


NOT unknown => unknown

true          OR unknown => true
unknown OR unknown => unknown
false         OR unknown => unknown

true          AND unknown => unknown
unknown AND unknown => unknown
false         AND unknown => false

记忆：优先级：
AND:    false > unknown > true
OR:       true > unknown > false
二 实战
2.1 题解
法一： 使用NOT IN, 注意不能先查出name，否则可能name有重名，id不同，导致数据查询不全。我一开始就犯了这个错误。应该查询出id，去避免这个问题。同样，用UNION也会自动去重，要用的话用UNION ALL.
注意code格式规范： 所有关键字大写，关键字右边对齐，子句缩进。即使很短的代码，也要严格要求自己，好的代码不仅自己能懂，也要让一个新人能很容易读懂。切不可随意潦草，我自己要慢慢修炼！


SELECT  name
 FROM  customer C
WHERE C.id NOT IN (  SELECT C1.id 
                                        FROM customer C1
                                      WHERE C1.referee_id = 2  );
法二：
使用exists，先查出referee_id的数据，再用exists,注意语法，exists需要关联表。


SELECT C.name 
  FROM customer C
 WHERE NOT EXISTS (SELECT C1.name
                                     FROM  customer C1
                                    WHERE  C1.id  = C.id
                                        AND  C1. referee_id = 2);
法三：
直接增加第二个判断，用OR。


 SELECT C.name 
  FROM customer C
WHERE C. referee_id <> 2
       OR C. referee_id IS NULL;
法四: 用UNION ALL


SELECT name 
 FROM customer 
WHERE referee_id <> 2
UNION ALL
SELECT name 
 FROM customer 
WHERE referee_id IS NULL.
为什么有时NOT IN 会查不到值，以及推导公式。
如果用WHERE id NOT IN (1, 2, NULL), 是不能查出数据的。
推导【经典】：

=> WHERE NOT id IN (1, 2, NULL) //NOT和IN等价改写NOT IN 
=> WHERE NOT（（id = 1）OR（id = 2）OR（id= NULL））//用OR等价改写谓词IN
=> WHERE NOT (id = 1) AND NOT (id = 2) AND NOT( id = NULL) //德摩根定律等价改写
=> WHERE (id <> 1) AND (id <> 2) AND (id <> NULL)//用<>改写NOT 和 =
=> WHERE (id <> 1) AND (id <> 2) AND unknown //对NULL使用<>,结果为unknown
=> WHERE false 或者 unknown //AND运算包含unknown,结果不可能为true
结论：如果NOT IN的子查询用到的表里被选择的列中存在NULL，则SQL整体的查询结果永远是空！

为了解决烦人的NULL，最好在表里添加NOT NULL约束来尽力排除NULL

觉得有所帮助，帮忙点赞关注，我是橡树，沉淀自己，欢迎交流讨论，一起进步！

作者：xiang-shu-k
链接：https://leetcode.cn/problems/find-customer-referee/solution/san-by-xiang-shu-k-7ywp/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。