MySQL 数据库 编程语言 数据分析 笔记

SQL 复习笔记

知识点复习补充

Posted by Paradise on July 23, 2023

函数和关键字

用法 说明
round(x, n) 四舍五入，x为浮点数，n为保留的位数
ceil(x) 向上取整
floor(x) 向下取整
truncate(x, n) 截断x，n为保留的位，该位之后的数值置零，位数表示示例：321.123，其中小数点前用负值表示，小数点位数为0，即 truncate(x, 0) 等同于舍弃小数部分取整
mod(a, b) 返回 a 除以 b 的余数
abs(x) 返回 x 的绝对值
rand() 返回 0-1 的随机浮点数
sign(x) 返回数值的符号，1、-1或者0
avg(column) 返回字段或数组切片的均值
var_pop(column) 返回字段或数组切片的总体方差
var_samp(column) 返回字段或数组切片的样本方差（计算方法不同）
std(column) 总体标准差
stddev_samp(column) 样本标准差
replace(str, a, b) 将字符串中的 a 替换为 b(从头开始检索，替换所有存在的片段)
concat(a, b) 合并字符串（不能直接 a + b）（接受多个参数，按顺序合并）
concat_ws(fill, a, b) 合并并在两者之间填充 fill 片段
left(str, n) / right(str, n) 左起 / 右起取 n 个字符
substring(str, x, n) 左起 x 位（含）取 n 个字符
ltrim / rtrim / trim(str) 去除 左 / 右 / 两边的空格
repeat(str, n) 重复 n 次
char_length(str) / length(str) 返回字符数 / 字节数（mysql 中中文字符默认 3 字节）
coalesce(column, value) 使用 value 填充字段 column 中的空值
cast(column as dtype) 将 column 字段的数据类型转换为 dtype（如 char）
convert(column, dtype) 同上
now() 返回当前的年月日时分秒，默认格式为”%Y-%m-%d %h:%i:%s”
year/month/day/hour/minute/second(x) 依次返回 x 中包含的年/月/日/时/分/秒
date(time)/time(x) 返回 x 中的日期/时间
weekofyear(x)/dayofweek(x) 顾名思义，注意每周以周日为第 0 天，周一为第 1 天
date_format(x, format) format 可以是 “%Y-%m-%d”，或者”%w”（返回周几）
date_add/date_sub(x, interval n unit) x前移或后移一段时间，n 为数值，unit 为 year、month、day 等关键字
date_diff(a, b) 两个时间日期的差值
select * INTO OUTFILE filename from tablename; 输出到文件，默认在安装目录的 Data 文件夹
select * from tablename LIMIT n, m 从 n 行开始（含）取 m 行
select … where column LIKE “%str%” 匹配字符串，用 % 表示任意数量的任意字符，用 - 表示单个任意字符
select … where column REGEXP “regexp” 类似 LIKE，使用正则表达式，更精准的匹配
select … IF(condition, true_value, false_value) from table 条件赋值
select … CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 … ELSE last_value END from table 多个条件匹配赋值

速查表

面试问题

1. 规范化与反规范化

规范化（Normalization）是通过组织数据库的字段和表来最小化冗余和依赖性的过程。规范化的主要目的是添加、删除或修改可以在单个表中进行的字段。

反规范化（Denormalization）是一种用于从数据库的高范式到低范式访问数据的技术。这也是通过合并相关表中的数据将冗余引入到表中的过程。

2. 规范化的 5 中范式（Normal Form）

  • 1NF：从表中删除所有重复的列。创建相关数据表并识别唯一列。
  • 2NF：满足第一范式的所有要求。将数据子集放置在单独的表中并使用主键在表之间创建关系。
  • 3NF：满足 2NF 的所有要求。删除不依赖于主键约束的列。
  • 4NF：满足第三范式的所有要求，并且不应该具有多值依赖。
  • 5NF：满足第四范式，且表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。

3. 索引的三种类型

索引是一种性能优化方法，可以更快地从表中检索记录。索引为每个值创建一个条目，检索数据会更快

  • 唯一索引：如果列是唯一索引的，则此索引不允许字段具有重复值。当定义主键时，可以自动应用唯一索引。
  • 聚集索引：这种类型的索引重新排序表的物理顺序并根据键值进行搜索。每个表只能有一个聚集索引。
  • 非聚集索引：非聚集索引不会改变表的物理顺序并保持数据的逻辑顺序。每个表可以有 999 个非聚集索引。

区别：聚集索引用于通过改变记录的存储方式来轻松地从数据库中检索数据。数据库按设置为聚集索引的列对行进行排序。非聚集索引不会改变其存储方式，而是在表中创建一个完全独立的对象。搜索后它指向原始表行。

4. 什么是游标？

数据库游标是一种可以遍历表中的行或记录的控件。这可以被视为指向一组行中的一行的指针。游标对于检索、添加和删除数据库记录等遍历非常有用。

5. 什么是触发器？

数据库触发器是自动执行以响应数据库中的表或视图上的某些事件的代码或程序。主要是触发器有助于维护数据库的完整性。触发器允许您在对特定表执行插入、更新或删除命令时执行一批 SQL 代码。实际上，触发器是特殊类型的存储过程，被定义为就地或数据修改后自动执行。

示例：当新学生添加到学生数据库时，应在相关表（如考试、分数和出勤表）中创建新记录。

6. 什么是局部变量和全局变量以及它们的区别？

局部变量是可以在函数内部使用或存在的变量。其他函数不知道它们，并且无法引用或使用这些变量。每当调用该函数时都可以创建变量。

全局变量是可以在整个程序中使用或存在的变量。在全局中声明的相同变量不能在函数中使用。每当调用该函数时都无法创建全局变量。

7. 什么是自增？

自动增量关键字允许用户创建一个在将新记录插入表中时生成的唯一编号。Oracle 中可以使用 AUTO INCREMENT 关键字，SQL SERVER 中可以使用 IDENTITY 关键字。大多数情况下，只要使用 PRIMARY KEY，就可以使用此关键字。

8. 什么是数据仓库？

数据仓库是来自多个信息源的数据的中央存储库。这些数据经过整合、转换并可用于挖掘和在线处理。仓库数据有一个数据子集，称为数据集市。

9. 存储过程的优点和缺点？

存储过程可以用作模块化编程——意味着创建一次、存储并在需要时多次调用。这支持更快的执行而不是执行多个查询。这减少了网络流量并为数据提供了更好的安全性。缺点是只能在数据库中执行，占用数据库服务器较多的内存。

10. 什么是在线事务处理（OLTP）？

在线事务处理或 OLTP 管理基于事务的应用程序，可用于数据输入和轻松的数据检索处理。这种处理使得简单性和效率变得更容易。与 OTLP 相比，它更快、更准确的结果和费用。（示例 – 每天的银行交易）

11. SQL支持编程吗？

不，SQL 没有循环或条件语句。它像命令语言一样用于访问数据库。

12. 什么是数据库的 ACID 属性？

ACID 是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

ACID 属性用于确保数据事务在数据库系统中得到可靠处理。对数据的单个逻辑操作称为事务。

  • 原子性：要求每笔交易要么全有，要么全无。这意味着如果事务的一部分失败，则整个事务失败并且数据库状态保持不变。
  • 一致性：一致性属性确保数据必须满足所有验证规则。简而言之，您可以说您的事务在未完成其状态的情况下永远不会离开数据库。
  • 隔离性：该属性确保不满足执行的并发性。提供隔离的主要目标是并发控制。
  • 持久性：持久性仅仅意味着一旦事务被提交，它将保持不变，甚至可能出现断电、崩溃或错误。

示例与习题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

/* Employee */
-- +----+-------+--------+--------------+
-- | Id | Name  | Salary | DepartmentId |
-- +----+-------+--------+--------------+
-- | 1  | Joe   | 70000  | 1            |
-- | 2  | Henry | 80000  | 2            |
-- | 3  | Sam   | 60000  | 2            |
-- | 4  | Max   | 90000  | 1            |
-- +----+-------+--------+--------------+

/* Department */
-- +----+----------+
-- | Id | Name     |
-- +----+----------+
-- | 1  | IT       |
-- | 2  | Sales    |
-- +----+----------+

/* result */
-- +------------+----------+--------+
-- | Department | Employee | Salary |
-- +------------+----------+--------+
-- | IT         | Max      | 90000  |
-- | Sales      | Henry    | 80000  |
-- +------------+----------+--------+

/* 最直接的思路就是一层一层的嵌套子查询了，但是可读性一般 */
SELECT 
    DName AS Department,
    Name AS Employee,
    Salary
FROM (
    SELECT 
        Name, DName, Salary,
        RANK() OVER (PARTITION BY DName ORDER BY Salary DESC) as Ranking
    FROM (
        SELECT * FROM Employee 
        INNER JOIN 
        (SELECT Name AS Dname, Id AS DId FROM Department)
        AS d 
        ON d.DId = Employee.departmentId
    ) as joined
)
AS ranked
WHERE ranked.Ranking = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

/* 来自 <https://github.com/datawhalechina/team-learning-sql/blob/main/Task06%EF%BC%9A%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0.md> 的题目 */

/* Answer 01 */

WITH t2 AS (
	WITH t1 AS(
		SELECT 
		a.Name AS Employee,
		a.Salary AS Salary,
		b.Name AS Department
		FROM employee AS a JOIN department AS b
		WHERE a.DepartmentId = b.Id
	)
    SELECT 
	*, 
	RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS ranking
	FROM t1
)
SELECT 
Department, Employee, Salary
FROM t2
WHERE ranking = 1;

/* Answer 02 */

SELECT 
(
CASE
WHEN id <> (select count(id) from seat) AND mod(id, 2) <> 0 THEN id + 1
WHEN id <> (select count(id) from seat) AND mod(id, 2) = 0 THEN id - 1
ELSE id
END
) 
AS id,
student
FROM seat
ORDER BY id;

/* Answer 03*/

SELECT 
Score,
RANK() OVER(ORDER BY amount DESC) AS `Rank`
FROM Score;

/* Answer 04 */

SELECT DISTINCT(Num) AS ConsecutiveNums 
FROM 
(
	SELECT 
	*,
	lag(id, 2) OVER(PARTITION BY Num ORDER BY id) AS id_new 
	FROM logs
) AS A 
WHERE A.id = A.id_new + 2;

-- LAG 函数语法：
-- LAG(return_value ,offset [,default]) 
-- OVER (
--     [PARTITION BY partition_expression, ... ]
--     ORDER BY sort_expression [ASC | DESC], ...
-- )

/* Answer 05 */

SELECT id,
CASE
WHEN t.p_id IS NULL THEN 'Root' 
WHEN t.id IN(SELECT p_id FROM tree ) THEN 'Inner'
ELSE 'Leaf' 
END AS `Type`
FROM  tree t;

/* Answer 06 */

SELECT 
A.Name
FROM Employee AS A
LEFT JOIN Employee AS B
ON A.Id = B.ManagerId
GROUP BY A.Id
HAVING COUNT(*) >=  5
目录
    文章标签
    R 数据分析 Python RCM 笔记 总结 编程语言 数据可视化 统计分析 商业分析 机器学习 爬虫 转载 市场分析 MySQL 数据库 神经网络 计算机视觉 Java