4.2 关系数据库编程
Web数据库开发的基础是熟练应用各种SQL语句，本节将讲解常用的SQL增、删、改、查语句，并演示Python的数据库编程方法。
4.2.1 常用的SQL语句
SQL的英文全称是Structured Query Language，即结构化查询语言，该语言于1986年经过美国国家标准协会（ANSI）的规范成为关系数据库的标准语言，其后ANSI又进行了若干次更新，但至今该查询语言的主体结构未发生变化。SQL由以下6类内容组成。
• 数据定义语言（DDL）：创建、删除表结构的语言，包括Create、Drop。
• 数据控制语言（DCL）：为定义数据访问及修改权限而实现的语句，包括Grant、Revoke。
• 数据查询语言（DQL）：定义从数据表中查询已有数据的方法，如Select。
• 数据操作语言（DML）：定义对数据表中的数据进行增、删、改的方法，包括Insert、Delete、Update。
• 事务处理语言（TPL）：为保证多条SQL语句的数据一致性而定义的语句，如Commit、Rollback。
• 指针控制语言（CCL）：定义对查询到的多条记录进行逐行控制的方法及与Cursor相关的语句。
在以上6类语句中，DDL和DCL是数据库管理员常用的语句，CCL是数据库存储过程中开发者需要的技能，本节不做讲解，我们重点讲解以下语句。
1. INSERT语句
INSERT语句用于向数据表中插入数据，其语法为：
如果INSERT语句中的列名序列与表定义中的位置相同，则可以省略不写。例如，对于表4.1所定义的课程表，在其中插入数据的SQL例子为：
上述3条语句的效果完全相同。第1条语句按正规语法编写；第2条语句省略了列名；第3条语句颠倒了列名的顺序，相应值的顺序也要颠倒。如果在INSERT语句中不指明某列的值且在表定义时没有指定默认值，则数据库将其设置为默认值NULL：
上述INSERT语句中省略了对description列的赋值，在新插入的记录中该列将被置为NULL。
注意：SQL语句本身的关键字不区分大小写，如INSERT INTO、DELETE等。
2. DELETE语句
DELETE语句用于从数据表中删除已有的行，其语法为：
该语义为删除table_name中所有满足条件表达式（即条件表达式结果为True）的记录。条件表达式由条件操作符和操作数组成，常用的SQL条件表达式如表4.6所示。
表4.6 常用的SQL条件表达式
应用多条件表达式时，应注意AND和OR操作符同时出现时的优先顺序：AND运算的优先级高于OR，即先运算AND再运算OR。如果需要指定不按照该优先级执行，则可以通过小括号表示先后顺序。DELETE及WHERE条件表达式的应用举例如下：
3. UPDATE语句
UPDATE语句用于修改数据表中已有记录的列数据，其语法为：
该语义为将table_name表中所有满足条件表达式的记录的指定列设置为新值。其中的条件表达式已经在表4.6中总结过，对表4.3定义的学生表应用UPDATE语句的示例如下：
4. SELECT语句
SELECT用于从数据表中选取数据，是SQL中最常用的语句，语法结构如下：
其中方括号中的内容为可选项目。语法中的第1行用于指定查询结果所需要返回的列：可以逐个列出所有列名，也可以用通配符星号“*”表示返回所有列。而可选项top用于指定返回的最大行数；distinct只用于在只返回一列时指明排除重复项。WHERE条件表达式的用法同UPDATE/DELETE语句相同。对于表4.1定义的课程表举例如下：
GROUP BY用于对数据进行分组以便于汇总计算；HAVING是GROUP BY的可选项，用于对汇总结果进行筛选。汇总计算是指统计记录的个数、计算某列的平均值等。比如：
技巧：GROUP BY语句可以同时指定多个列进行分组。
上例中的AVG(period)、COUNT(*)是SQL的汇总计算聚集函数。常用的SQL聚集函数如表4.7所示。
表4.7 常用的SQL聚集函数
ORDER BY用于指定返回结果的记录按某个或某几列的大小排序，ASC用于指定从小到大排列（ASC是默认值），DESC用于指定从大到小排列。比如：
5. 多表连接的SELECT语句
因为整个系统的数据分布在不同的表中，所以很多时候为了得到完整的结果，开发者需要从两个或更多的表中查询数据，这时需要在FROM子语句中用JOIN关键字连接多个表。JOIN相关的语法为：
其语义为按照连接条件表达式连接两个表，使两个表的列都可以被用于SELECT、WHERE、ORDER BY等子语句。JOIN关键字本身有多种类型，如表4.8所示。
表4.8 JOIN类型表
虽然一个JOIN关键字只能连接两个表，但是可以同时使用多个JOIN关键字以达到连接多个表的目的，对于表4.1～表4.5的数据库进行连接查询，举例如下：
上面的代码分别演示了一次连接查询和两次连接查询，其中都用了关键字INNER JOIN。INNER JOIN是最常用的一种JOIN类型，其含义为只获取两个表中满足查询关键字的连接记录。
6. 事务控制语句
SQL中的事务控制语句能确保被DML语句影响的表的所有行及时得以更新，当必须以原子方式执行的多条语句中一旦有一条失败时，能够取消之前成功的语句。事务是SQL中将一组DML语句赋予原子执行方式的方法。
注意：原子方式执行是指在一组语句中，要么所有语句都执行成功，要么所有语句都不执行。
事务控制语句包含以下3条不可分割的语句。
• BEGIN TRANSACTION：启动一个新事务，即其后的所有语句被封装为一个原子性事务，直到有ROLLBACK或COMMIT被执行。
• ROLLBACK：回滚事务，结束当前事务，并取消（UNDO）在本次事务中已经执行成功的语句。
• COMMIT：提交事务，当前事务正式完成，其中DML语句对数据库做的更新正式生效。
4.2.2 实战演练：在Python中应用SQL
虽然SQL标准统一了数据库语言，但是通过Python、Java、C++等高级语言操作数据库时需要连接每个数据库独特的数据库引擎，之后才能用SQL语言对数据库进行操作。所以，在Python中操作不同的数据库需要引入不同的数据库包，常用数据库引擎的Python包如表4.9所示。
表4.9 常用数据库引擎的Python包
虽然每种数据库引擎的Python包不同，但是所有Python的数据库引擎都遵守DB-API规范，该规范使得引用数据库引擎后的编程方法大体相当，Python数据库编程的步骤如下。
（1）引入Python引擎包：例如，import PsyCopg语句用于为PostgreSQL操作做准备。
（2）连接数据库：使用引擎包的connect方法连接物理数据库，通常在本步骤中需要输入数据库的IP地址、端口、数据库名、数据库用户和密码等。对于SQLite和Excel等文件数据库，本步骤中需要给出文件名。
（3）获取游标：在DB-API规范中，游标（cursor）用于执行SQL命令并且管理查询到的数据集。
（4）执行SQL命令：将SQL命令传给游标执行，并解析返回的结果。本步骤可以多次进行。
（5）提交或回滚事务：在执行DML类的SQL语句时，数据库引擎会自动启动新事务，在一系列的操作完成之后，可以提交或回滚当前事务。
（6）关闭游标：完成SQL操作后关闭游标。
（7）关闭数据库连接：关闭Python客户端和数据库服务器的连接。
【示例4-1】下面演示用SQLite3包操作SQLite数据库：
本例中演示了连接数据库、新增数据、修改数据、读取数据、关闭数据连接的一系列操作。将代码保存为db.py，执行效果如下：
由于所有Python数据库引擎都遵守DB-API开发接口，因此这里不再演示其他数据库的编程代码，读者可以自行尝试。