20231227 SQL基础50题打卡

20231227 SQL基础50题打卡

知识点复习

连接

在 MySQL 中，连接查询是一种强大的技术，用于从多个表中检索数据并将它们连接起来。连接查询可以分为多种类型，包括内连接、外连接和交叉连接。下面是对这些连接查询的详细讲解：

1. 内连接（INNER JOIN）

内连接是最常用的连接类型之一。它根据两个或多个表之间的共同值来连接数据，仅返回满足连接条件的行。

语法：

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

示例：

假设有两个表 students 和 courses，它们通过 student_id 列进行关联。

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.student_id = courses.student_id;

2. 外连接（OUTER JOIN）

外连接允许检索所有匹配条件的行，并且也会返回某个表中未匹配到的行。它分为左外连接、右外连接和全外连接。

• 左外连接（LEFT JOIN）：返回左表中的所有行，以及右表中满足条件的行。
• 右外连接（RIGHT JOIN）：返回右表中的所有行，以及左表中满足条件的行。
• 全外连接（FULL JOIN）：返回左右两个表中的所有行，无论是否满足条件。

语法：

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

示例：

假设我们需要获取所有学生以及他们选修的课程，即使有些学生还没有选修任何课程：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.student_id = courses.student_id;

3. 交叉连接（CROSS JOIN）

交叉连接返回两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。

语法：

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

示例：

如果我们需要获取所有学生与所有课程的组合：

SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

4. 使用连接查询时的注意事项：

• 性能问题：连接查询可能会对数据库性能造成影响，特别是在大型数据集上。谨慎使用连接，并优化查询以提高性能。
• 索引：确保连接列上有适当的索引，以加快连接的执行速度。
• 表别名：使用表别名可以使查询更易读，特别是在连接多个表时。

以上是 MySQL 中连接查询的主要知识点，根据实际场景和需求选择合适的连接类型以获取所需的数据。

函数

MySQL 中有许多内置函数，可以用于各种操作，包括数学运算、字符串处理、日期和时间处理等。以下是一些常用的 MySQL 函数，以及它们的用法和含义：

1. 数学函数

• ABS()：返回一个数的绝对值。

  • 用法：ABS(number)

• ROUND()：将一个数四舍五入为指定的小数位数。

  • 用法：ROUND(number, decimals)

• CEIL() / FLOOR()：CEIL() 返回大于或等于指定数的最小整数，而 FLOOR() 返回小于或等于指定数的最大整数。

  • 用法：CEIL(number), FLOOR(number)

• RAND()：返回一个 0 到 1 之间的随机数。

  • 用法：RAND()

2. 字符串函数

• CONCAT()：将两个或多个字符串连接在一起。

  • 用法：CONCAT(string1, string2, ...)

• SUBSTRING()：返回字符串的子串。

  • 用法：SUBSTRING(string, start_position, length)

• UPPER() / LOWER()：将字符串转换为大写 / 小写。

  • 用法：UPPER(string), LOWER(string)

• LENGTH()：返回字符串的长度。

  • 用法：LENGTH(string)

3. 日期和时间函数

• NOW()：返回当前日期和时间。

  • 用法：NOW()

• DATE_FORMAT()：格式化日期。

  • 用法：DATE_FORMAT(date, format)

• DATEDIFF()：计算两个日期之间的天数差。

  • 用法：DATEDIFF(date1, date2)

• DATE_ADD() / DATE_SUB()：对日期进行加减运算。

  • 用法：DATE_ADD(date, INTERVAL value unit), DATE_SUB(date, INTERVAL value unit)

4. 聚合函数

• COUNT()：计算行数或非 NULL 值的数量。

  • 用法：COUNT(column_name), COUNT(*)

• SUM()：计算指定列的总和。

  • 用法：SUM(column_name)

• AVG()：计算指定列的平均值。

  • 用法：AVG(column_name)

• MAX() / MIN()：计算指定列的最大值 / 最小值。

  • 用法：MAX(column_name), MIN(column_name)

MySQL 中还有很多其他函数，以下是一些常用的函数以及它们的用法和含义：

5. 条件函数

• IF() / CASE WHEN：根据条件返回不同的值。
  • IF(condition, value_if_true, value_if_false)
  • CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END

6. NULL 函数

• COALESCE()：返回参数列表中的第一个非 NULL 值。
  • 用法：COALESCE(value1, value2, ...)

7. 聚合函数扩展

• GROUP_CONCAT()：将组内行连接为一个字符串。
  • 用法：GROUP_CONCAT(column_name SEPARATOR ',')

8. 数据类型转换函数

• CAST() / CONVERT()：将一个数据类型转换为另一个数据类型。
  • 用法：CAST(expression AS data_type), CONVERT(expression, data_type)

9. 系统函数

• USER()：返回当前用户的用户名和主机名。

  • 用法：USER()

• VERSION()：返回当前 MySQL 服务器的版本信息。

  • 用法：VERSION()

10. 其他函数

• UUID()：生成一个唯一的 Universally Unique Identifier（UUID）。

  • 用法：UUID()

• INET_ATON() / INET_NTOA()：在 IP 地址和整数之间进行转换。

  • 用法：INET_ATON(ip_address), INET_NTOA(integer)

以上列举的函数只是 MySQL 中的一部分。MySQL 提供了广泛的函数库，以支持在 SQL 查询和数据处理中的各种需求。根据实际情况和需要，你可以探索更多 MySQL 函数的用法和功能。

1148. 文章浏览 I

---

Views 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
此表可能会存在重复行。（换句话说，在 SQL 中这个表没有主键）
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。

请查询出所有浏览过自己文章的作者

结果按照 id 升序排列。

查询结果的格式如下所示：

示例 1：

输入：
Views 表：
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+

输出：
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

# Write your MySQL query statement below
select distinct(author_id) id from Views where viewer_id = author_id order by id asc 

1683. 无效的推文

---

表：Tweets

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
在 SQL 中，tweet_id 是这个表的主键。
这个表包含某社交媒体 App 中所有的推文。

查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。

以任意顺序返回结果表。

查询结果格式如下所示：

示例 1：

输入：
Tweets 表：
+----------+----------------------------------+
| tweet_id | content                          |
+----------+----------------------------------+
| 1        | Vote for Biden                   |
| 2        | Let us make America great again! |
+----------+----------------------------------+

输出：
+----------+
| tweet_id |
+----------+
| 2        |
+----------+
解释：
推文 1 的长度 length = 14。该推文是有效的。
推文 2 的长度 length = 32。该推文是无效的。

# Write your MySQL query statement below
SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;

1378. 使用唯一标识码替换员工ID

---

Employees 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
在 SQL 中，id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。

EmployeeUNI 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+
在 SQL 中，(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。

展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以 任意 顺序返回结果表。

返回结果的格式如下例所示。

示例 1：

输入：
Employees 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+
EmployeeUNI 表:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+
输出：
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+
解释：
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。

# Write your MySQL query statement below
SELECT 
    EMPLOYEEUNI.UNIQUE_ID,EMPLOYEES.NAME 
FROM 
    EMPLOYEES
LEFT JOIN
    EMPLOYEEUNI
ON 
    EMPLOYEES.ID = EMPLOYEEUNI.ID