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 函数的用法和功能。
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
表: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;
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