朝花夕拾：快速回忆被遗忘在角落中的SQL

你点开这篇博客，大概率需要临时使用被遗忘许久的SQL. 幸好，SQL比编程简单得多，回忆起来也是相当简单。

为了回忆SQL，我需要下载MySQL或其它数据库吗？当然可以。不过点开这篇博客的家人们有福了，如果只是回忆基本操作，使用Python内置的库sqlite3即可。运行以下程序即刻体验SQL的魅力：

复制代码

import os, sqlite3


def run_sql(temp_file:str = 'temp.sqlite3'):
	con = sqlite3.connect(temp_file)
	cur = con.cursor()

	while True:
		sql = input('> ')
		if sql == 'exit':
			con.close()
			os.remove(temp_file)
			break
		try:
			result = cur.execute(sql)
			if 'select' in sql.lower():
				for item in result.fetchall():
					print(item)
			elif 'insert' in sql.lower() or 'delete' in sql.lower():
				con.commit()
		except Exception as e:
			print(e)


if __name__ == '__main__':
	run_sql()

不过，sqlite3对数据类型的支持没有主流数据库那么好，但这对文中内容毫无影响。另外，SQL语句的关键字是不区分大小写的。

众所周知，一位自称是“数据科学工作者”的人，他的工作可能就是写SQL. 传统SQL讲究四门工夫——“增删查改”。“增”可以创建表、向表中写入数据；“删”可以删表、删数据，有一种传统艺能叫作“删库跑路”；“查”是SQL的重中之重，大部分SQL都是用来查找的；“改”可以修改表中数据，这个并不常用。

学过数据库的朋友都知道，SQL有两类老掉牙的例子：一是学生、二是订单，我们这里使用的例子正是学生。

“增”：创建表、向表中添加行

建表语句如下：

复制代码

CREATE TABLE stu (id INTEGER PRIMARY KEY, name VARCHAR(32), age INTEGER);

创建了表stu，包含3列：id（学号）、name（姓名）、age（年龄）。

向表中添加记录(10001,  张萌萌, 12), 插入语句如下：

复制代码

INSERT INTO stu VALUES(10001, '张萌萌', 12);

我们再多插入几条记录：

复制代码

INSERT INTO stu VALUES(10002, '李帅', 13);
INSERT INTO stu VALUES(10003, '王杰', 11);
INSERT INTO stu VALUES(10004, '欧阳清华', 14);

“查”：查找表中内容

简单查找

查找语句应该是大家记忆最深的语句了，其格式为：

SELECT [列表项] FROM [表名] WHERE [筛选条件];

例如，我们要查看表stu中的所有行：

复制代码

select * from stu;

查找结果为：

复制代码

(10001, '张萌萌', 12)
(10002, '李帅', 13)
(10003, '王杰', 11)
(10004, '欧阳清华', 14)

如果我们要查找年龄大于12的所有学生姓名：

复制代码

SELECT name FROM stu WHERE age > 12;

查找结果为：

复制代码

('李帅',)
('欧阳清华',)

数据聚合

SQL中常用的内置聚合函数有COUNT、MIN、MAX、SUM和AVG，它们用于执行简单的统计功能。例如，计算表stu中学生的平均年龄：

复制代码

SELECT AVG(age) FROM stu;

关于COUNT

COUNT(*)可统计表的行数，而COUNT(age)可以统计列age中，非空(NOT NULL)值的个数。

分组查询

可通过GROUP BY将表分组，再对每个分组运用统计函数。为了演示效果，我们在表stu中再添加几条记录：

复制代码

INSERT INTO stu VALUES(10005, '李芳芳', 13);
INSERT INTO stu VALUES(10006, '王菜刀', 11);
INSERT INTO stu VALUES(10007, '诸葛韭菜', 11);

插入后，表stu中有年龄重复的行了。如果我们要统计表中各个年龄的有多少人，可以首先根据年龄，将表中数据分为若干个组，再利用统计函数COUNT，统计每个分组的行数：

复制代码

SELECT age, COUNT(*) FROM stu GROUP BY age;

输出为：

复制代码

(11, 3)
(12, 1)
(13, 2)
(14, 1)

提示

当查询语句同时包含WHERE和GROUP BY时，WHERE先执行，GROUP BY作用于WHERE过滤后的表上。GROUP BY的表可通过HAVING进一步筛选，但用得比较少，一般在WHERE中写好过滤条件。

结果排序

对表排序是最常见的一种需求，只需要通过ORDER BY指定排序的列即可。例如，将表stu按照成绩从高到低排序：

复制代码

SELECT * FROM stu ORDER BY age DESC;

SQL默认从小到大排序，想要从大到小排序，需要指定关键字DESC. 排序时可以指定多个列名。

窗口函数

窗口函数的使用格式如下：

复制代码

[窗口函数] OVER (PARTITION BY [列] ORDER BY [排序列])

这里的PARTITION BY是可选项。

 

前面提到的聚合函数都可以作为窗口函数，另外，还有RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数。例如，将表stu_age按照年龄从高到低排序、并分配一个次序：

复制代码

SELECT id, age, RANK() OVER (ORDER BY age) FROM stu_age;

--(10001, 11, 1)
--(10002, 12, 2)
--(10005, 12, 2)
--(10004, 14, 4)


SELECT id, age, DENSE_RANK() OVER (ORDER BY age) FROM stu_age;

--(10001, 11, 1)
--(10002, 12, 2)
--(10005, 12, 2)
--(10004, 14, 3)


SELECT id, age, ROW_NUMBER() OVER (ORDER BY age) FROM stu_age;

--(10001, 11, 1)
--(10002, 12, 2)
--(10005, 12, 3)
--(10004, 14, 4)

从这个例子中，也可以体会到RANK、DENSE_RANK和ROW_NUMBER在设置序号时的小差别。

PARTITION BY可以将表分成若干个组，在每个组内分别排序。

“删”：删除表中特定行、删除表

删除表中特定行的SQL语句为：

复制代码

DELETE FROM [表名] WHERE [指定条件];

删除表stu的操作为：

复制代码

DROP TABLE stu;

“查”：表的连接操作

两张列相同的表可以执行UNION、INTERSECT等集合操作，例如：

(SELECT id, name FROM stu1) UNION (SELECT id, name FROM stu2);

下面重点考察内外连接、左右链接。

有如下两张表：

表stu_name

id	name
10001	王一
10002	王二
10003	王三

复制代码

CREATE TABLE stu_name(id, name);
INSERT INTO stu_name VALUES(10001, '王一');
INSERT INTO stu_name VALUES(10002, '王二');
INSERT INTO stu_name VALUES(10003, '王三');

表stu_age

id	age
10001	11
10002	12
10004	14
10005	12

 

复制代码

CREATE TABLE stu_age(id, age);
INSERT INTO stu_age VALUES(10001, 11);
INSERT INTO stu_age VALUES(10002, 12);
INSERT INTO stu_age VALUES(10004, 14);
INSERT INTO stu_age VALUES(10005, 12);

 

将表中具有相同id的行分别通过内连接、左连接和右连接连接起来：

复制代码

-- 内连接：INNER JOIN
SELECT stu_name.id, name, age FROM stu_name INNER JOIN stu_age ON stu_name.id = stu_age.id;

-- (10001, '王一', 11)
-- (10002, '王二', 12)


-- 左连接：LEFT JOIN
SELECT stu_name.id, name, age FROM stu_name LEFT JOIN stu_age ON stu_name.id = stu_age.id;

-- (10001, '王一', 11)
-- (10002, '王二', 12)
-- (10003, '王三', None)

-- 右连接
SELECT stu_age.id, name, age FROM stu_name RIGHT JOIN stu_age ON stu_name.id = stu_age.id;

-- (10001, '王一', 11)
-- (10002, '王二', 12)
-- (10004, None, 14)
-- (10005, None, 12)

由此可见这几种查询模式的区别：对于内连接，只有两张表同时具有某个id，才能连接上；左连接保证左边表的指定列都在连接后的表中，而右连接保证右边的。左右连接时，如果有空缺的地方，就用NULL填充。

“改”：更新数据

数据库中的输入错误在所难免，更新使用如下语句：

复制代码

UPDATE [表名] SET [列名 = 表达式] where [过滤条件];

其中，WHERE是可选部分。

举个例子：

复制代码

UPDATE stu_age SET id=10002 WHERE id=10001 and age=12;

以上SQL语句将表stu_age中的记录(10001, 12)修改为(10002, 12).