SQL基础

数据库

数据库可以理解为一个电子文件柜或者仓库，用于存储和组织数据。数据库中的数据以表的形式呈现，可以简单理解为我们在Excel中看到的那种表格。一个数据库可以有很多张表，比如一张表存储用户信息，另一张表存储图片信息。 表的每一列代表一种类型的数据，每一行代表一个具体的数据记录。而SQL是一种专门用来和数据库进行交互的语言，利用SQL我们可以对数据库进行增删改查操作，以实现数据管理。

创建和删除

SQL语句包含关键字和非关键字，虽然关键字不论大小写都会正确执行，但是为了保证代码的可读性，关键字一般都大写。同时为了分隔两条SQL语句，一般都会在一条SQL语句的末尾添加分号。

创建和删除数据库：

CREATE DATABASE <database_name>;
DROP DATABASE <database_name>;

数据库以表的形式组织数据，而在创建表之前需要指定使用的数据库，因为一个服务中可以存在多个数据库。创建和删除表

USE <database_name>;
CREATE TABLE <table_name> (
    <row_name> <type>
);

// example
CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

上面的例子在record_company这个数据库中创建了一张bands表，同时在创建这个表的时候还传递了一些参数。首先是前两行，表是由行和列组成的，在创建表时需要指定列以及该列存储的数据类型。 上面的例子在表中创建了id name两列分别存储整数和字符串，NOT NULL的意思是这项必填，AUTO_INCREMENT表示自增即每添加一个新数据，它的id在前一个的基础上+1。 最后的PRIMARY KEY用于指定快速查找表数据的项。若需要在已经创建的表中新增列可以采用：

ALTER TABLE bands
ADD anther_row VARCHAR(255);

插入和查询数据

在指定的表格中插入数据，需要指定每一个数据的必填项，也就是NOT NULL指定的列的内容：

INSERT INTO bands (name)
VALUES ('Iron Maiden'), ('Ankor'), ('Avenged Sevenfold'), ('Deuce');

查询表中数据使用SELECT关键字，主要有以下几种查询方式。

查询所有数据：

SELECT * FROM bands;

查询指定列的所有数据（支持列重命名）：

SELECT name FROM bands;

SELECT id AS 'ID', name AS 'Band Name' FROM bands;

按顺序排列查询结果，去除重复数据：

SELECT * FROM bands ORDER BY id;

SELECT  DISTINCE name FROM bands;

更新数据

更新数据需要指定更新哪一张表中，哪一列、哪一行的数据，使用UPDATE关键字来实现：

UPDATE albums
SET release_year = 1982
WHERE id = 1;

上面这段SQL的意思是，更新albums表中的数据，将id为1这一行数据的release_year的值设置为1982。

WHERE是SQL中一个常用的关键字，用于筛选符合指定条件的数据。

SELECT * FROM albums
WHERE release_year < 2000 OR band_id = 2;

SELECT * FROM albums
WHERE name LIKE '%er%' AND band_id = 1; 

SELECT * FROM albums
WHERE release_year BETWEEN 2000 AND 2023;

AND OR BETEEN都是比较语义化的关键字。第一个例子是寻找发行日期在2000之前并且band_id为2的专辑；第二个例子的LIKE关键用于匹配数据，在这个例子中表示寻找名字中带有er的专辑名称；第三个例子则用于寻找发行日期在2000至2023年之间的专辑。

与WHERE类似还有一个经常使用的关键字HAVING，两者的用法是完全一致的。区别在WHERE在执行后文所说的聚合函数之前执行，在执行聚合函数之后还需求对数据进行筛选则需要使用HAVING。

删除数据

DELETE关键字用于删除数据。和更新数据一样，一般情况下都需要指定需要删除数据项的具体位置：

DELETE FROM albums WHERE id = 5;

表格连接

关系型数据库的一大特点就是可以管理不同数据之间的关联关系，所以经常出现的场景是需要将不同表中的数据进行连接。

SELECT * FROM bands 
JOIN albums ON bands.id = albums.band_id;

上面代码的意思是将bands和albums两张表连接在一起，连接的依据是bands表的id和albums表的band_id两者相同的时候进行连接。表格进行连接时有两种常用的模式，LEFT JOIN和RIGHT JOIN。两者的区别在于哪一个表格中的所有项可以全部展示。以下面两张表格为例子：

id	name
1	Iron Maiden
2	Ankor
3	Avenged Sevenfold
4	Deuce

id	name	release_year	band_id
1	The Number of Beasts	1985	1
2	Power Slave	1984	2
3	Nightmare	2018	2
4	Nightmare	2010	3

使用LEFT JOIN模式将两张表格进行连接，SQL语句：

sql
SELECT * FROM bands 
LEFT JOIN albums ON bands.id = albums.band_id;

最终结果如下：

id	name	id	name	release_year	band_id
1	Iron Maiden	1	The Number of Beasts	1985	1
2	Ankor	2	Power Slave	1984	2
2	Ankor	3	Nightmare	2018	2
3	Avenged Sevenfold	4	Nightmare	2010	3
4	Deuce	NULL	NULL	NUll	NULL

不难发现，最后一行数据显得有些突兀，因为Deuce没有任何一张专辑与它匹配并建立连接，但是它仍然出现在了最后的结果表格中，这就是LEFT JOIN导致的，左边的表格的所有项都会出现在最后的结果中，不论右表格中有没有与它匹配的项。 RIGHT JOIN则相反。但是有一点需要注意，默认情况下使用的JOIN模式不是两者中任何一个，而是INNER JOIN，该模式会排除掉两个表格中没有匹配的行。

聚合函数

聚合函数是SQL中一些常用的工具函数，用于对数据进行简单处理以实现更加复杂的操作。比如求所有专辑发行年份的平均值：

SELECT AVG(release_year) FROM albums;

除了AVG还有使用SUM来求和，COUNT来计数等常用的聚合函数。

SELECT COUNT(release_year) FROM alnums

用于计数在albums表格中发行日期不为空的专辑的数量。COUNT最常见的用法是用于统计满足某些条件的行的数量，假设现在有一张students表，需要统计男生的数量。

student_id	gender
1	M
2	F
3	M
4	F
5	M

使用COUNT函数搭配WHERE条件即可实现这一点：

SELECT COUNT(*) AS male_student_count
FROM students 
WHERE gender = 'M';

最终得到的结果：

male_students_count
3

数据分组

分布在不同表格中的数据可能存在联系，有些时候我们希望按照某些规则对数据进行分组，GROUP BY就是为此而生的。比如我们希望统计属于同一个乐队的专辑的数量：

SELECT band_id, COUNT(band_id) FROM albums 
GROUP BY band_id;

GROUP BY会按照band_id对表格中的所有专辑进行分类，具有相同band_id的专辑会被分为一类。同时COUNT函数会对每一组中band_id不为空的专辑进行计数。最后的结果会展示band_id以及对应的专辑的数量，不过这样的数据显然是不够语义化的，更多时候我们希望得到的是每个乐队的名字以及它们所具有的专辑的数量。综合表格连接、数据分组和聚合函数，可以使用下面的SQL语句来完成这个需求：

SELECT b.name AS band_name, COUNT(a.id) AS album_number
FROM bands AS b LEFT JOIN albums AS a ON b.id = a.band_id
GROUP BY b.id;

简单的理解，GROUP BY会按照指定的属性对数据进行分类并聚合。假设有一张客户订单表：

order_id	customer_id	amount
1	1	100
2	2	200
3	1	150
4	3	300
5	2	250

我们希望能够按照客户id对数据进行分类聚合，从而计算出每个用户的订单的总金额。这时候就可以使用GROUP BY来实现：

SELECT customer_id, SUM(amount) AS total_amount
FROM order
GROUP BY customer_id

最终输出的结果为：

customer_id	amount
1	100
2	200
3	300

注意事项：GROUP BY一般都会搭配聚合函数来进行使用，比如SUM统计综合，COUNT计算属于同一类的行的数量等等。若不采用任何聚合函数，则GROUP BY只会返回每个分组的一个组，一般来说这是无意义的。

小结

本文描述的SQL的基本用法来自于观看视频Learn SQL in 60 minutes (opens new window)的文字总结，对于不太了解SQL和数据库的前端开发者来说可以通过这个视频快速了解SQL的基本用法。课后练习可以参考这里 (opens new window)。