tags:
- SQLLecture 0 - Querying
本节课，你先会接触到 Database 和 DataBase Management System 的相关知识。之后，我们会简单地学习如何用SQL与数据库进行交互、查询你想知道的信息。
Information Age
人类一直在和信息、数据打交道。在象形文字发明初期的早些时候，人们将文字篆刻在石壁上、龟壳上来记录一些部落的高光时刻。之后，人们学会了如何造纸，将信息写到纸上，便于驮运和阅读学习。由此，人类文明得以发扬壮大。
在计算机出现之前，人们用纸质的表(Table)来记录数据。纸质记录虽然可以长久保存，但是有许多缺点：如数据冗余、不一致性、效率低下、而且安全性低。在PC机出现后，人们记录和处理信息的方式发生了变化。可以选用文本文件并按一定格式存储数据，但缺点依旧。
随着信息技术的发展，我们进入了信息时代。传统的信息存放方式已经不再能够满足我们对信息获取时效性、准确性和安全性的需求。人们开始把信息存放在计算机中，随之而来的是许多专门用于按存放数据的spreadsheet softwares，如 Apple Numbers, Microsoft Excel, Google Sheets 等。
但这个课程是关于数据库和SQL的，我们并不关心这些 spreadsheet softwares。我们关心使用数据库的好处优点？或者说有什么是这些spreadsheet softwares无法带给我们的？我们有三个理由去使用数据库，分别是扩展性（十亿级别的扩展性）、CRUD的频率和速度。
Databases, SQL and SQLite
Databases
数据库是一种组织数据的方式，你可以通过数据库管理系统(DBMS)同数据库进行交互。在数据库中，你可以进行CURD的操作，即Create, Update, Read, Delete我们常说的增删改查操作。
使用DBMS来管理和处理数据，除了能提高数据存储和检索的效率，还可以保证数据的完整性和安全性保障。如今，SQL成为了管理和操作数据库的标准语言，使得数据处理更加便捷和高效。市面上也有诸多的 DBMS，使用 DBMS，你就可以同数据库进行 CRUD 的交互。常见的 DBMS 有 MySQL, Oracle, MongoDB, SQLite 等。
SQL
DBMS 通常使用 SQL(Structured Query Language)来向上层应用提供服务的接口。SQL 最早是由 IBM 公司在 System R 上首次实现的，最早的时候叫 SEQUEL(Structured English Query Language)。在下面学习 SQL 语句的时候你就能感受到，使用 SQL 进行 CRUD 操作时实际上和英语非常类似，相比于晦涩的 C, Java 等高级语言，SQL 的使用要简单得多。
SQL 有相应的标准，由 ISO 和 ANSI 制订，标准的存在让不同的 DBMS 都需要给用户提供通用的接口。此外，用户可以在不同的 DBMS 之间迁移数据和应用程序，而不需要进行大量的修改。但需要注意的是，不同的 DBMS 可能会在标准的基础上增加一些扩展和功能。我们这里将使用SQLite作为我们的DBMS。
SQLite
SELECT
为了回答我们的数据库里面有哪些数据，我们需要用到 SELECT 关键字。这是我们接触到的第一个关键字，SELECT 允许我们在数据库中的表中选择特定的行打印出来。在下面，我们将 longlist 数据库中的所有行都打印了出来，在 SQL 中，* 就是全部的意思。
SELECT *
FROM "longlist";
上面的 SQL 语句的意思就是：Select and print all the rows from the "longlist" database table. 也就是将数据库 longlist 中所有行所有列都打印出来。
如果我们想要查看一些特定列中所有行的数据，我们就要用 SELECT 选择特定的列进行打印。我们用下面的 SQL 语句来打印所有关于 title 的数据：
SELECT "title"
FROM "longlist";
这句 SQL 语句就表示：Select and print all the rows in the "title" column from the "longlist" database table.
如果想查看更多的一些信息，比如说作者的信息，只需要在 title 列后面隔个逗号加上要查询的列信息，如下：
SELECT "title", "author"
FROM "longlist"
Uppercase and Double Quotes for Good Practice
LIMIT
在上面的查询中，我们看到一次查询显示出来的数据太长了。假如我们的数据库有几百万行，那一次查询出来的数据根本不是看一眼就能够大致知道发生什么了的。在这种情况下，我们引入一个新的关键字 LIMIT 。LIMIT 让我们可以简单地瞥见表中都有什么东西。
我们有下面的语句，表示 Select and print the rows in the "title" column from the "longlist" database table with a limit of 10 queries.
SELECT "title"
FROM "longlist"
LIMIT 10;
WHERE
有时候，我们并不想要查看所有行的数据，我们只想要查看自己感兴趣的一些数据。这时候，我们就会用到另一个关键字 WHERE 用于条件选择查找。当特定的条件满足时输出特定行。
比如下面的语句，表示 Select and print the rows in the "title" and "author" columns from the "longlist" database table where the listed "year" is equal to 2023.
SELECT "title", "author"
FROM "longlist"
WHERE "year" = 2023;
!= and <>
我们用 = 表示 "equal to"，在 SQL 中，我们用 != 和 <> 来表达 "not equal to"，这两个是等价的。比如下面的语句就可以表示：Select and print the rows in the "title" and "format" columns from the "longlist" table where the "format" cover is not equal to 'hardcover'.
SELECT "title", "format"
FROM "longlist"
WHERE "format" != 'hardcover'; -- equals to WHERE "format" <> 'hardcover'
除了用 != 和 <> 来表达不等于，在 SQL 中，我们还额外地有一个关键字 NOT 来表示一层否定的关系。上面的 SQL 语句用 NOT 就相当于：
SELECT "title", "format"
FROM "longlist"
WHERE NOT "format" = 'hardcover';
AND and OR
知道如何用 WHERE 条件性选择查找后，我们现在要考虑的事情就是如果同时选择多个条件同时查找？在 SQL 中，我们有 AND 和 OR 关键字来整合我们想要查询的多个条件。比如说，我们想查一下在 2022 和 2023 年 longlisted 的作者和书名，我们可以这样：
SELECT "title", "author"
FROM "longlist"
WHERE "year" = 2022 OR "year" = 2023;
上面语句的意思就是： Select and print the rows in the "title" and "author" columns from the "longlist" database table where the longlisted year is equal to 2022 or 2023.
我们还可以加入一个条件，比如说在这些在 2022 或 2023 年 longlisted 的书中，我们再选择那些不是 hardcover 的。我们可以这样：
SELECT "title", "format"
FROM "longlist"
WHERE ("year" = 2022 OR "year" = 2023) AND "format" != 'hardcover';
通常而言，AND 的优先级默认高于 OR。这里的 () 表示一种优先级，意思就是说这里强制让 OR 的条件先计算，再与 AND 结合。使用括号可以让逻辑变得更加清晰。
NULL
在数据库中，有的字域可能没有填入数据，我们称为数据缺失 (data missing) 。在 SQL 中，我们用 NULL 类型来指示某个数据可能没有值或在数据库中不存在。在 longlist.db 中，我们记录了许多书籍的信息，如果书籍没有翻译者，那么格子的数据就会被标记为 NULL。
为了查看哪些书籍没有翻译，我们可以：
SELECT "title", "translator"
FROM "longlist"
WHERE "translator" IS NULL;
这句 SQL 语句的意思就是： Select and print the rows in the "title" and "author" columns from the "longlist" database table where the "translator" field is an empty NULL.
注意这里我们使用 IS 而不使用 = 或者 != 是因为 NULL 是一个无值的状态，用 = 比较 NULL 会返回一个 UNKNOWN 而不是 TRUE 或 FALSE。而 IS 操作符专门用于检查某个字段是否是 NULL 。
查看哪些书籍有翻译，我们在 IS 后面加一个 NOT 就可以了，和英语一样。如下：
SELECT "title", "translator"
FROM "longlist"
WHERE "translator" IS NOT NULL;
LIKE
如果我们不能确定确切的字符串叫什么，我们可以用关键字 LIKE 粗略的查询一些字符串。比如，等下我们会用 LIKE 来查询一些书名含有某个单词的书籍。顺便提一下， LIKE 所查询的字符串并不区分大小写。
在我们用 LIKE 查询时，我们通常会配合通配符使用。在 SQL 中，我们有两个通配符 % 和 _ 。% 用于匹配大于等于0个的任意字符；_ 用于匹配一个任意字符；如果要查询不以某某开头的任意字符，我们可以用 '[^AB]%' 来表示不以AB开头的任意字符串。
例如，当我们想查询书名带 'love' 的书籍，我们可以用下面的 SQL 语句：
SELECT "title"
FROM "longlist"
WHERE "title" LIKE '%love%';
这句 SQL 语句的意思就是：Select and print the rows in the "title" column from the "longlist" database table where the "title" contains the substring like "love".
当我们想查询书名是 "The" 开头的书籍，我们可以用：
SELECT "title"
FROM "longlist"
WHERE "title" LIKE 'The%';
意为：Select and print the rows in the "title" column from the "longlist" database table where the "title" starts the substring like "The".
上面的查询中，我们想要的是单词 "The" 开头的书籍，但实际上我们查询的书名开头可能是任何以 The 开头的单词。如果要查询以单词 "The" 开头的书籍，我们可以在 % 前加一个空格。
如果你有一本书叫 "Pyre" ，但是你忘记这本书是 "Pyre" 还是 "Pire"。你可以用 _ 通配符来匹配字符串中的任何单个字符。如下：
SELECT "title"
FROM "longlist"
WHERE "title" LIKE 'P_re';
Select and print the rows in the "title" column from the "longlist" database table where the "title" is like something matches the pattern 'P_re'.
Range Conditions
此外，SQL 中还引入了 > , < , >= , <= 用于匹配特定值范围内的数据。比如我们可以用下面的 SQL 语句筛选一下在 2019 年到 2022 年 longlisted 的书籍名和作者：
SELECT "title", "author"
FROM "longlist"
WHERE "year" >= 2019 AND "year" <= 2022;
同样的，我们还可以用 关键字 BETWEEN 和 AND 来获得相同的结果：
SELECT "title", "author"
FROM "longlist"
WHERE "year" BETWEEN 2019 AND 2022;
这两句 SQL 语句的意思都是：Select and print the rows in the "title" and "author" columns from the "longlist" database table where the "year" is between 2019 and 2022.
此外，我们还可以通过范围条件来筛选评分大于 4.0 的书籍：
SELECT "title", "author"
FROM "longlist"
WHERE "rating" > 4.0;
意为：Select and print the rows in the "title" and "author" columns from the "longlist" database table where the book "rating" is more than 4.0.
最后，通过 AND 或 OR 的组合条件逻辑，我们还可以筛选一下评分大于 4.0 而且投票数大于 10,000 的书籍：
SELECT "title", "author"
FROM "longlist"
WHERE "rating" > 4.0 AND "votes" > 10000;
意为：Select and print the rows in the "title" and "author" columns from the "longlist" database table where the book "rating" is more than 4.0 and votes is more than 10000.
ORDER BY
我们已经学过了用 WHERE 进行条件查询、在条件中加入条件范围、组合条件逻辑和用 LIMIT 来筛选出最初出现的 n 行数据。现在，我们来学习如何用关键字 ORDER BY将选出的数据进行排序。
例如之前的语句”筛选评分大于 4.0 而且投票数大于 10,000 的书籍“，我们可以用 ORDER BY 语句对这些书籍按评分进行排序。我们再引入俩个关键字 DESC 和 ASC，意思是降序(Descending)和升序(Ascending)。
SELECT "title", "author"
FROM "longlist"
WHERE "rating" > 4.0 AND "votes" > 10000;
ORDER BY "rating" DESC
LIMIT 10;
上面这一长串语句的意思就是再前面的基础上选出 10 本评分最高的书籍。意为：Select and print the rows in the "title" and "author" columns from the "longlist" database table where the book "rating" is more than 4.0 and votes is more than 10000, order by "rating" in descending and limit the output to the top 10 entries.
Aggregate Functions
本节的最后一节课，我们来介绍一下 SQL 中的聚合函数。这些函数用于再多个数据行上执行计算并返回单个值，通常用于汇总、统计和分析数据。常见的聚合函数有：
COUNT() - 计算并返回指定列中非空值的数量。
-- 查询数据库中有多少行数据，也就是书籍的数量。
SELECT COUNT(*) AS "Book Count"
FROM "longlist";
SUM() - 计算并返回指定列中所有值的总和。
-- 查询所有书籍的投票数量总和。
SELECT SUM("votes") AS "Sum of Votes"
FROM "longlist";
AVG() - 计算并返回指定列中所有值的平均值。
-- 查询所有书籍的平均评分。
SELECT AVG("rating") AS "Average Rating"
FROM "longlist";
ROUND(number, decimals) - 对数值进行四舍五入，第一个参数通常填入 AVG()，第二个参数是要保留的小数位数。
SELECT ROUND(AVG("rating"), 2) AS "Average Rating"
FROM "longlist";
MAX() - 查询并返回指定列中的最大值。
-- 查询最受好评的书籍。
SELECT MAX("rating") AS "The Most Favorite Book"
FROM "longlist";
MIN() - 查询并返回指定列中的最小值。
-- 查询最不受好评的书籍。
SELECT MAX("rating") AS "The Last Favorite Book"
FROM "longlist";