Python项目开发教程 课件 第十一章 数据库操作

第十一章数据库操作Python项目开发实用教程目录11.1数据库基础数据库简介关系型数据库数据库和Python接口11.2结构化查询语言SQLSQL简介面对数据库的操作面对数据库中表的操作面对表中记录的操作11.3Python数据库编程实践环境搭建与连接配置增删改查(CRUD)实战事务处理与异常捕获11.1数据库基础DatabaseFundamentals&CoreConcepts11.1.1数据库简介-数据库的定义数据库的定义数据库是数据的集合体，通过特定的组织方式将大量数据有序存储，以便于管理、查询和维护。数据组织与存储按预定义规则汇总存储，确保数据的有序性和一致性。多用户共享支持多用户同时访问和操作，实现数据的共享与协同。减少冗余通过规范化技术减少数据重复，提高存储效率。数据独立性数据与程序相互独立，一方修改不会影响另一方运行。11.1.1数据库简介-数据库管理系统(DBMS)DBMS的定义数据库管理系统是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据。DBMS的主要功能定义与创建：定义数据库模式、表、视图等对象。维护与管理：数据转换、备份恢复、性能检测与优化。存储与组织：确定数据的物理存储结构和存取方式。数据存取：提供数据的增删改查(CRUD)操作功能。事务与安全：保证数据的安全性、完整性和并发控制。通信与接口：支持与其他软件通信、异构数据转换。11.1.1数据库简介-Python对数据库的支持Python支持的数据库类型大型关系型数据库支持Sybase,SAP,SQLServer,Oracle,MySQL等。轻量级嵌入式数据库支持Python自带的SQLite，无需独立服务。非关系型数据库(NoSQL)支持MongoDB,Redis等主流NoSQL数据库。Python与数据库交互技术PythonDB-API遵循统一规范，直接编写SQL语句访问数据库，是本章学习重点。ORM(对象关系映射)采用面向对象方式操作数据库，无需编写SQL，如SQLAlchemy。11.1.2关系型数据库RelationalDatabaseManagementSystem11.1.2关系型数据库-定义与特点（1）关系型数据库的定义关系型数据库采用了关系模型（二维表模型）来组织数据。数据以行（记录）和列（字段）的形式存储在表中，一组表构成了整个数据库。这种结构直观且易于理解。核心特点解析结构化存储数据分为行和列，结构清晰，易于查询和管理。先定义后存储先定义表结构(Schema)，再存入数据，保证数据完整性。遵循规范化原则按最小关系表存储，有效避免数据冗余和不一致。纵向扩展为主主要依赖提升单台服务器硬件性能，扩展能力相对受限。11.1.2关系型数据库-特点（2）查询方式(SQL)采用结构化查询语言(SQL)，功能强大，支持复杂的查询、统计和分析操作。数据规范化处理复杂实体时需分割存入多张表，通过关联关系连接，ORM技术可简化此过程。事务性(ACID)严格遵守ACID特性，保证数据操作的安全性和可靠性，适合高一致性要求场景。读写性能为保证强一致性牺牲部分性能，在海量数据和高并发场景下，性能可能显著下降。授权方式多为商业软件（如Oracle），需付费使用；MySQL是流行的免费开源选择。11.1.2关系型数据库-常用术语关系(Relation)可以理解为一张二维表，每个关系对应一个表名。元组(Tuple)二维表中的一行，在数据库中称为一条记录(Record)。属性(Attribute)二维表中的一列，在数据库中称为一个字段(Field)。域(Domain)一个属性的取值范围。例如，性别字段的域是{'男','女'}。关键字(Key)唯一标识一条记录的属性，即主键(PrimaryKey)。11.1.3数据库和Python接口11.1.3数据库和Python接口-PythonDatabaseAPIPythonDatabaseAPI(PEP249)Python官方制定的数据库接口标准，旨在为不同数据库提供一致的访问方式。核心价值：统一接口：无论底层是MySQL、Oracle还是SQLite，代码风格一致。降低成本：开发者无需为每种数据库学习一套新的API。标准核心定义模块接口：定义基本连接函数，如connect()连接对象(Connection)：管理会话，提供提交/回滚方法游标对象(Cursor)：执行SQL语句并获取查询结果类对象与常量：定义标准的数据库错误类型和常量错误处理机制：规范了异常的抛出和处理方式11.1.3数据库和Python接口-支持的数据库类型关系型数据库大型商业数据库

Oracle,SQLServer,DB2,Sybase开源免费数据库

MySQL,PostgreSQL嵌入式数据库

SQLite(标准库自带)非关系型数据库(NoSQL)键值存储

Redis文档数据库

MongoDB列族存储

HBase其他类型数据仓库

IBMNetezzaXML数据库

各种XML数据库11.2结构化查询语言SQLStructuredQueryLanguage11.2.1SQL简介SQL的定义SQL(StructuredQueryLanguage)，即结构化查询语言，是用于管理关系型数据库的标准语言。它是一种非过程化语言，用户只需告诉数据库“做什么”，而无需关心底层执行的“怎么做”。主要特点与规范大小写不敏感：命令不区分大小写，但字符串常量区分。书写格式灵活：支持单行或多行书写，必须以分号(;)结尾。关键词规范：关键词不能跨多行书写，也不能随意简写。支持注释：单行使用--，多行使用/*...*/包裹。可读性强：合理的空格和缩进能显著提高代码可读性。折行操作：SQL语句可以折行书写，以提高代码的可读性。11.2.2面对数据库的操作DatabaseOperations11.2.2面对数据库的操作-创建数据库功能说明用于创建一个新的数据库，是数据库操作的基础语句。参数详解库名(DatabaseName)需符合命名规范，是数据库的唯一标识。CHARACTERSET(字符集)可选参数，建议指定为utf8或gbk，避免中文乱码。若不指定，将使用系统默认字符集。语法格式CREATEDATABASE库名[CHARACTERSET字符集];代码示例--创建一个名为test_db的数据库，并指定字符集为utf8CREATEDATABASEtest_dbCHARACTERSETutf8;11.2.2面对数据库的操作-查看数据库查看所有数据库--列出所有数据库名称SHOWDATABASES;列出当前数据库服务器上所有的数据库名称，方便快速概览系统资源。查看数据库创建信息--查看详细配置SHOWCREATEDATABASEdb_name;显示创建指定数据库时的详细语句，包括字符集、排序规则等配置信息。查看当前使用的数据库--切换并查看USEdb_name;SELECTDATABASE();使用USE切换数据库上下文，使用SELECTDATABASE()确认当前会话所在的数据库。11.2.2面对数据库的操作-修改数据库语法格式ALTERDATABASE库名[CHARACTERSET新字符集];代码示例--将数据库test_db的字符集修改为gbkALTERDATABASEtest_dbCHARACTERSETgbk;功能说明ALTERDATABASE语句主要用于修改数据库的属性，最常见的用途是修改数据库的字符集。注意事项修改字符集可能影响已有表的字符集，操作前请务必谨慎，并在测试环境中验证。执行此操作通常需要数据库管理员（DBA）权限。11.2.2面对数据库的操作-删除数据库DROPDATABASE语法用于删除已存在的数据库。执行后数据库及其所有表和数据将被永久删除，且无法恢复。DROPDATABASE库名;--删除名为test_db的数据库DROPDATABASEtest_db;重要操作警告谨慎操作执行前务必确认数据库名称正确无误，避免误删。备份数据若数据仍有用，请先进行完整备份，以防数据丢失。权限控制严格限制普通用户的DROP权限，降低误操作风险。11.2.3面对数据库中表的操作TableOperationsinDatabase11.2.3面对数据库中表的操作-创建表语法结构与参数说明CREATETABLE表名(字段1数据类型[约束条件],字段2数据类型[约束条件],...);字段(Field)表中的列名，定义数据的属性。数据类型(DataType)定义字段存储的数据类型，如INT,VARCHAR,DATE。完整性约束(Constraint)限制字段的取值规则，如NOTNULL,PRIMARYKEY。实战：创建学生信息表--1.切换到目标数据库USEtest_db;--2.创建学生信息表CREATETABLEstudent_info(student_idINTAUTO_INCREMENTPRIMARYKEY,student_nameVARCHAR(100)NOTNULL,birth_dateDATE,enrollment_yearYEAR);11.2.3面对数据库中表的操作-查看表信息（1）查看当前数据库中所有的表--列出当前数据库中所有的数据表名称SHOWTABLES;查看表的创建语句和详细信息--显示创建表的完整SQL语句SHOWCREATETABLE表名;应用示例与说明SHOWCREATETABLEstudent_info;该命令会返回创建`student_info`表的完整`CREATETABLE`语句，包含字段定义、字符集及存储引擎等详细信息，是排查表结构问题的常用工具。11.2.3面对数据库中表的操作-查看表结构语法与示例(DESCRIBE/DESC)用于查看数据表的字段信息，包括字段名、数据类型、键信息等。DESC是DESCRIBE的简写形式。--查看表结构的三种方式DESCRIBEstudent_info;DESCstudent_info;SHOWCOLUMNSFROMstudent_info;输出结果字段解析Field：字段名称Type：数据类型(如INT,VARCHAR)Null：是否允许为空(YES/NO)Key：键类型(PRI表示主键)Default：字段的默认值Extra：额外信息(如AUTO_INCREMENT)11.2.3面对数据库中表的操作-修改表结构（语法）ALTERTABLE语句用于修改已存在表的结构，支持增加、删除、修改字段及约束等操作。基本语法格式ALTERTABLE表名([操作1],[操作2],...);常用操作类型ADD：增加新字段(ADD字段名类型[约束])MODIFY：修改字段类型或约束CHANGE：修改字段名称、类型和约束DROP：删除现有字段详细语法示例ADD语法ADD新字段数据类型[约束][FIRST|AFTER字段]MODIFY语法MODIFY字段名新数据类型[新约束条件]CHANGE语法CHANGE旧字段名新字段名新类型[新约束]DROP语法DROP字段名11.2.3面对数据库中表的操作-修改表结构（示例）SQL代码示例：修改学生信息表--1.增加email字段2.修改姓名长度3.重命名生日字段ALTERTABLEstudent_infoADDemailVARCHAR(100),MODIFYstudent_nameVARCHAR(150),CHANGEbirth_datedate_of_birthDATE;执行结果分析新增字段：email(VARCHAR(100))修改长度：student_name(100->150)重命名字段：birth_date->date_of_birth验证方法：使用DESCstudent_info;查看结构11.2.3面对数据库中表的操作-修改表名语法与示例(RENAMETABLE)语法格式：RENAMETABLE旧表名TO新表名;代码示例：--将表名从student_info修改为studentsRENAMETABLEstudent_infoTOstudents;注意事项依赖对象更新修改表名后，所有依赖于该表的对象（如视图、存储过程）可能需要相应修改。应用程序同步如果应用程序中直接使用了硬编码的表名，也需要同步更新，否则会导致程序出错。确认无并发使用操作前最好确认没有其他用户正在使用该表，以避免锁表或数据不一致。11.2.3面对数据库中表的操作-删除表DROPTABLE概述用于删除一个已存在的数据表。此操作会永久删除表结构及其所有数据，且无法恢复。语法格式DROPTABLE表名;代码示例--删除名为students的数据表DROPTABLEstudents;重要警告与注意事项极度危险执行前必须确保数据不再需要或已完整备份，数据删除后不可恢复。级联删除若表被外键关联，需先删除约束或使用级联选项，否则可能删除失败。权限控制应严格限制普通用户的DROPTABLE权限，防止误操作。11.2.4面对表中记录的操作CRUD核心操作详解11.2.4面对表中记录的操作-增加记录（INSERTVALUES）功能说明用于向数据表中插入一条或多条新记录。字段列表与值列表必须一一对应。基本语法INSERT[INTO]表名(字段1,字段2)VALUES(值1,值2);INSERT[INTO]表名(字段1,字段2)VALUES(v1,v2),(v3,v4);注意事项关键字INTO可以省略。若插入所有字段，字段列表可省略，但值的顺序必须与表结构一致。代码示例(MySQL)--创建表结构CREATETABLEstudent_info(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(100)NOTNULL,birthDATE);--插入单条记录INSERTINTOstudent_info(name,birth)VALUES('Charlie','2000-05-15');--插入多条记录INSERTINTOstudent_info(name,birth)VALUES('Alice','1999-08-20'),('Bob','2000-01-10');11.2.4面对表中记录的操作-增加记录（INSERTSET）语法格式与示例INSERT[INTO]表名SET字段1=值1,字段2=值2,...;代码示例：--使用SET方式插入一条记录INSERTINTOstudent_infoSETstudent_name='David',birth_date='2001-03-25',enrollment_year=2020;特点与优势可读性高字段和值成对出现，一目了然，不容易出错。灵活赋值可方便地为部分字段赋值，未指定字段使用默认值或NULL。不支持多行插入与VALUES不同，SET方式每次只能插入一条记录。11.2.4面对表中记录的操作-修改记录（UPDATE）语法结构与说明UPDATE表名SET字段1=新值1,字段2=新值2,...[WHERE条件];SET子句：指定要更新的字段及其新值。WHERE子句：限定更新范围。若省略，将更新表中所有记录。代码示例与警告--将Charlie的入学年份更新为2020UPDATEstudent_infoSETenrollment_year=2020WHEREstudent_name='Charlie';重要警告务必仔细检查WHERE条件。生产环境建议先执行SELECT验证条件，再执行UPDATE。11.2.4面对表中记录的操作-删除记录（DELETE）DELETE语句概述用于删除表中已存在的记录。操作不可逆，请务必谨慎。基本语法格式DELETEFROM表名[WHERE条件];FROM：指定要删除记录的表。WHERE：指定删除条件。若省略，将删除表中所有记录。代码示例--删除名为'David'的学生记录DELETEFROMstudent_infoWHEREstudent_name='David';--警告：DELETEFROMstudent_info;会删除所有记录重要警告操作前务必检查WHERE条件，建议先用SELECT验证。DELETE仅删除数据，不删除表结构（删除表结构用DROP）。删除的数据通常难以恢复，请确保已备份。11.2.4面对表中记录的操作-清空表记录方法一：DELETEFROM(逐条删除)DELETEFROM表名;支持事务回滚(ROLLBACK)不重置自增主键计数器逐条删除，大数据量时速度较慢方法二：TRUNCATETABLE(重建表)TRUNCATETABLE表名;不可回滚，操作立即生效会重置自增主键计数器执行速度极快，直接释放数据页选择建议：需回滚或保留自增计数器用DELETE；确定永久删除且追求速度用TRUNCATE。11.2.4面对表中记录的操作-查询记录（基本SELECT）核心概念与语法SELECT是SQL中最常用的语句，用于从表中检索数据。最基础的形式是查询所有字段或指定字段。--查询所有字段SELECT*FROMtable_name;--查询指定字段SELECTcol1,col2FROMtable_name;注意事项：通配符`*`虽然方便，但在字段较多时效率较低，建议明确指定字段名。字段名之间需用英文逗号分隔。实战代码示例--1.插入测试数据INSERTINTOstudent_info(name,enroll_year)VALUES('Alice',2018),('Bob',2019);--2.查询所有学生信息SELECT*FROMstudent_info;--3.查询姓名和入学年份(推荐写法)SELECTname,enroll_yearFROMstudent_info;11.2.4面对表中记录的操作-查询记录（完整语法）SELECT完整语法结构SELECT[DISTINCT]字段列表|*FROM表名[JOIN其他表ON连接条件][WHERE过滤条件][GROUPBY分组字段][HAVING分组过滤条件][ORDERBY排序字段[ASC|DESC]][LIMIT记录数限制];各子句核心作用解析SELECT/FROM：指定查询字段与数据来源表，最基础的部分。WHERE：在分组前对记录进行过滤，筛选原始数据。GROUPBY/HAVING：实现分组统计，并对分组结果进行二次过滤。ORDERBY：对最终结果进行排序，ASC升序，DESC降序。LIMIT：限制返回的记录条数，常用于分页查询。11.2.4面对表中记录的操作-SQL执行顺序SQL执行顺序（简化版）01.FROM/JOIN-确定数据来源，处理表连接02.WHERE-过滤记录03.GROUPBY-对记录进行分组04.HAVING-过滤分组后的结果05.SELECT-确定返回字段和计算06.DISTINCT-去除重复记录07.ORDERBY-对结果集排序08.LIMIT/OFFSET-限制条数或分页重要推论与代码示例⚠️WHERE中不能使用SELECT别名原因：WHERE执行时，SELECT尚未执行，别名尚未定义。--错误示例：WHERE中使用了别名'ny'SELECTname,birth_yearASnyFROMusersWHEREny>18;--正确示例：重复计算或使用子查询SELECTname,birth_yearASnyFROMusersWHEREbirth_year>18;✅ORDERBY可以使用SELECT别名原因：ORDERBY是最后执行的步骤，此时字段已确定。11.2.4面对表中记录的操作-WHERE子句（比较运算符）常用比较运算符运算符描述示例=等于WHEREname='Alice'<>不等于WHEREyear<>2019!=不等于WHEREyear!=2019>大于WHEREscore>90<小于WHEREage<20>=大于等于WHEREyear>=2018<=小于等于WHEREdate<='2000-01-01'SQL代码示例--查询2019年入学的所有学生SELECT*FROMstudent_infoWHEREenrollment_year=2019;--查询出生在2000年及之后的学生SELECT*FROMstudent_infoWHEREbirth_date>='2000-01-01';--查询名字不是'Bob'的学生SELECT*FROMstudent_infoWHEREstudent_name!='Bob';注意事项比较的两边数据类型应尽量一致，避免隐式类型转换带来的性能问题或错误。字符串比较通常区分大小写（具体取决于数据库的字符集和排序规则）。11.2.4面对表中记录的操作-WHERE子句（关键字）BETWEEN...AND...判断值是否在某个连续的范围内。--查询2018-2020入学学生SELECT*FROMstudent_infoWHEREenrollment_yearBETWEEN2018AND2020;IN(...)集合判断判断值是否在某个离散的集合中。--查询Alice或CharlieSELECT*FROMstudent_infoWHEREnameIN('Alice','Charlie');LIKE模糊查询配合通配符%(任意字符)和_(单个字符)。--查询姓张的学生SELECT*FROMstudentsWHEREnameLIKE'张%';11.2.4面对表中记录的操作-WHERE子句（逻辑运算符）常用逻辑运算符运算符描述作用AND逻辑与所有条件为真，结果才为真OR逻辑或只要有一个条件为真，结果就为真NOT逻辑非对条件的结果取反运算优先级优先级：NOT>AND>OR建议使用圆括号`()`明确执行顺序，避免逻辑错误。SQL代码示例--1.AND:2019年入学且姓李SELECT*FROMstudent_infoWHEREyear=2019ANDnameLIKE'李%';--2.OR:2018或2020年入学SELECT*FROMstudent_infoWHEREyear=2018ORyear=2020;--3.NOT:不是2019年入学SELECT*FROMstudent_infoWHERENOT(year=2019);--4.组合:2019年入学且非BobSELECT*FROMstudent_infoWHEREyear=2019ANDNOT(name='Bob');11.2.4面对表中记录的操作-GROUPBY子句核心概念与语法GROUPBY子句用于将查询结果按字段分组，常与聚合函数配合，对每个分组进行独立统计计算。SELECT分组字段,聚合函数(字段)FROM表名[WHERE条件]GROUPBY分组字段;常用聚合函数COUNT():统计记录数SUM():计算总和AVG():计算平均值MAX():最大值MIN():最小值代码示例：学生成绩统计--创建学生成绩表CREATETABLEstudent_scores(idINT,subjVARCHAR(50),scoreINT);--插入测试数据INSERTINTOstudent_scoresVALUES(1,'Math',95),(1,'English',88),(2,'Math',90);--按学生ID分组统计课程数和平均分SELECTid,COUNT(*)ASsubj_count,AVG(score)ASavg_scoreFROMstudent_scoresGROUPBYid;11.2.4面对表中记录的操作-HAVING子句HAVING子句：分组结果的过滤器HAVING子句用于在GROUPBY分组之后对结果进行过滤。它与WHERE的核心区别在于：WHERE过滤行，HAVING过滤分组。标准语法格式SELECT分组字段,聚合函数(字段)FROM表名[WHERE条件]GROUPBY分组字段HAVING分组过滤条件;代码示例：查询平均分>90的学生SELECTstudent_id,AVG(score)FROMstudent_scoresGROUPBYstudent_idHAVINGAVG(score)>90;WHERE与HAVING核心区别特性WHEREHAVING作用对象单个行记录分组后的结果集聚合函数不允许使用允许使用11.2.4面对表中记录的操作-ORDERBY子句语法格式与参数说明SELECT字段列表FROM表名[WHERE...][GROUPBY...][HAVING...]ORDERBY字段1[ASC|DESC],字段2[ASC|DESC];排序字段：支持字段名、SELECT别名或表达式。ASC/DESC：ASC为升序(默认)，DESC为降序。执行顺序：SELECT语句中最后执行的子句之一。代码示例解析--示例1:按入学年份升序(默认)SELECT*FROMstudent_infoORDERBYenrollment_year;--示例2:多字段排序(年份降序,姓名升序)SELECT*FROMstudent_infoORDERBYenrollment_yearDESC,nameASC;--示例3:使用别名排序(平均分降序)SELECTid,AVG(score)ASavgFROMscoresGROUPBYidORDERBYavgDESC;11.2.4面对表中记录的操作-LIMIT子句语法格式与代码示例(MySQL)--基本用法：返回前N条记录SELECTFROMtableLIMITN;--分页用法：偏移offset，取N条SELECTFROMtableLIMIToffset,N;--查询前2条记录SELECTFROMstudentLIMIT2;--分页：第2页，每页2条(记录3-4)SELECTFROMstudentLIMIT2,2;核心概念与参数用途：限制SELECT语句返回的记录条数，实现分页查询。参数N：要返回的记录条数。参数offset：偏移量，从结果集第(offset+1)条开始返回。注意事项与兼容性重要提示：使用LIMIT时应始终配合ORDERBY，否则“前N条”定义模糊。