1、第第9章数据库程序设计章数据库程序设计本章内容本章内容n9.1 数据库基础知识n9.2 SQL语言与SQLite数据库n9.3 Python的SQLite数据库编程29.1 数据库基础知识数据库基础知识n什么是数据库l按照按照某种数据结构来组织、存储和管理数据，相对某种数据结构来组织、存储和管理数据，相对于数据文件来讲有许多特点，比如实现数据共享、于数据文件来讲有许多特点，比如实现数据共享、减少数据冗余、数据具有独立性、实现数据集中管减少数据冗余、数据具有独立性、实现数据集中管理和控制、数据具有一致性和可维护性、数据故障理和控制、数据具有一致性和可维护性、数据故障可恢复等可恢复等。n数据库的类
2、型l层次数据库层次数据库l网状数据库网状数据库l对象数据库对象数据库l关系数据库关系数据库3n关系数据库的几个概念l关系（表）关系（表）l行（记录、元组）行（记录、元组）l列（字段、域）列（字段、域）l主键（主码）主键（主码）l外键（外码）外键（外码）l视图（虚表）视图（虚表）l触发器触发器l存储过程（函数）存储过程（函数）4n关系数据库举例表5n关系数据库举例实体关系图6n什么是数据库管理系统操纵操纵和管理数据库的系统软件，用于建立、使用和维和管理数据库的系统软件，用于建立、使用和维护数据库，对数据库进行统一的管理和控制，以保证护数据库，对数据库进行统一的管理和控制，以保证数据库的安全性和完
3、整性数据库的安全性和完整性，功能，功能包括数据定义、数据包括数据定义、数据操作、数据库的运行管理、数据组织、存储与管理、操作、数据库的运行管理、数据组织、存储与管理、数据库的保护、维护和通信等数据库的保护、维护和通信等。79.2 SQL语言与语言与SQLite数据库数据库n什么是SQLl数据库的结构化查询语言，即数据库的结构化查询语言，即Structured Query Language，使用它用户和管理员更透明、更有效，使用它用户和管理员更透明、更有效地访问数据库。地访问数据库。nSQL有哪些语句类型l数据定义语言数据定义语言DDL定义数据库结构，定义数据库结构， CREATE、ALTER和
4、和DROP表结构的创建、修改和删除表结构的创建、修改和删除l数据操作语言数据操作语言DML 访问数据，访问数据， SELECT、INSERT、UPDATE和和DELETE数据的增、删、改数据的增、删、改、查、查l数据控制语言数据控制语言DCL进行访问权限控制，进行访问权限控制，GRANT、REVOKE用户访问权限的设置和回收。用户访问权限的设置和回收。8n什么是SQLite一一款开源和轻便型的关系型款开源和轻便型的关系型数据库管理系统（软件）数据库管理系统（软件），它占用的内存、外存和它占用的内存、外存和CPU资源非常低，处理速度资源非常低，处理速度却比较快，支持最新的却比较快，支持最新的SQ
5、L语言标准，语言标准，支持各种主流支持各种主流操作系统，并能够与很多程序设计语言很好地操作系统，并能够与很多程序设计语言很好地结合。结合。SQLite可以独立使用，也可以嵌入在其它产品中作可以独立使用，也可以嵌入在其它产品中作为产品的一部分来使用。目前为产品的一部分来使用。目前SQLite3是是SQLite的最的最新版本新版本。9nSQLite3数据类型l整数、长整数、浮点数、字符串、文本、大文本、整数、长整数、浮点数、字符串、文本、大文本、二进制、布尔、日期和时间等数据类型二进制、布尔、日期和时间等数据类型l其中最常见的数据类型有整数（其中最常见的数据类型有整数（INTEGER）、浮）、浮点
6、数（点数（REAL）、文本（）、文本（TEXT）和二进制（）和二进制（BLOB）。）。l文本类型字段用于存放文字内容，二进制类型字段文本类型字段用于存放文字内容，二进制类型字段用于存放图片、声音、视频等二进制文件内容等。用于存放图片、声音、视频等二进制文件内容等。10nSQLite3运算符l二元运算符：二元运算符：字符串运算：字符串运算：|字符串连接字符串连接算术运算：算术运算：* 乘、乘、 / 除、除、 %求余、求余、 + 加、加、 - 减减位位 运运 算：算： 右移、右移、& 按位与、按位与、 | 按位或按位或条件运算：条件运算： 小于、小于、 大于、大于、= 大于等于、大于等于、= =等
7、于、等于、 != 不等于不等于逻辑运算：逻辑运算：IN逻辑判断字段在集合内、逻辑判断字段在集合内、 AND逻辑与、逻辑与、 OR逻辑或、逻辑或、BETWEEN 逻辑之间逻辑之间l一元运算符：一元运算符：- 正、正、 + 负、负、 ! 反、反、 反、反、NOT 逻辑反逻辑反11nSQLite3的主要函数包括单值函数和聚类函数。所谓单值函数即获取一个包括单值函数和聚类函数。所谓单值函数即获取一个或多个单项值的函数。所谓聚类函数即获得个数、求或多个单项值的函数。所谓聚类函数即获得个数、求和、求平均值等统计结果的函数。和、求平均值等统计结果的函数。abs( X )，绝对值，绝对值。length( X
8、)，字符长度。，字符长度。round( X )、round(X ,Y )，四舍五入。，四舍五入。substr(X ,Y ,Z ) ，字符串的，字符串的子串子串。avg( X ) ，均值。，均值。 count(X )、count(*) ，非，非空值的空值的次数、数据行次数、数据行数。数。max(X ) ，最大，最大值值。min(X ) ，最小值。，最小值。sum( X )、total( X ) ，非，非空值的数字和空值的数字和。12nSQLite3的主要SQL语句1.建表建表CREATE TABLE IF NOT EXISTS 表名表名 ( 字段名字段名 字段类型字段类型 AUTOINCREME
9、NT DEFAULT 缺省缺省值值 , . ,PRIMARY KEY ( 主键主键 ) ,FOREIGN KEY(外键字段名外键字段名) REFERENCES 主表主表(主键字主键字段名段名);13nSQLite3的主要SQL语句2.删除表删除表DROP TABLE IF EXISTS 表名表名;其中，使用其中，使用IF EXISTS判断表是否存在，若存在则删除，否判断表是否存在，若存在则删除，否则不做任何事情。则不做任何事情。14nSQLite3的主要SQL语句3.增加数据增加数据INSERT INTO 表名表名 ( 字段列表字段列表 ) VALUES( 字段值列表字段值列表);其中，表名后
10、是字段列表，用逗号隔开，其中，表名后是字段列表，用逗号隔开，VALUES后是需要后是需要添加的字段值列表。添加的字段值列表。15nSQLite3的主要SQL语句4.修改数据修改数据UPDATE 表名表名SET 字段名字段名=值值, .WHERE 条件条件;其中，其中，SET表示将字段的值改为新值，表示将字段的值改为新值，WHERE后是修改条后是修改条件。件。16nSQLite3的主要SQL语句5.删除数据删除数据DELETE FROM 表名表名 WHERE 条件条件;其中，其中， WHERE后是删除条件，若没有条件时，删除所有记后是删除条件，若没有条件时，删除所有记录。录。17nSQLite3
11、的主要SQL语句6.查询数据查询数据SELECT 字段名列表字段名列表 FROM 表名表名WHERE 条件条件ORDER BY 字段字段LIMIT 记录数记录数 ( OFFSET | , ) 开始记录号开始记录号;其中，其中， WHERE后是查询条件，后是查询条件，ORDER BY是对结果进行是对结果进行排序，排序，LIMIT表示只显示指定范围的记录。表示只显示指定范围的记录。189.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法1.模块的引入和数据库连接模块的引入和数据库连接import sqlite3 #导入导入sqlite3数据库模块数据库模块c
12、on = sqlite3.connect(library.db ) #建立建立连接连接也可以使用内存数据库以提高数据库的访问速度，但不能永也可以使用内存数据库以提高数据库的访问速度，但不能永久保存，内存数据库名写为久保存，内存数据库名写为“:memory:”，此时模块引入和，此时模块引入和数据库连接的写法改为：数据库连接的写法改为：import sqlite3conn = sqlite3.connect(:memory: )199.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法2.建立游标对像建立游标对像所谓游标对象实际是包含执行所谓游标对象实际是包含
13、执行SQL语句及对数据进行一系列语句及对数据进行一系列操作的操作的cursor类的一个实例。一旦建立好数据库连接，就可类的一个实例。一旦建立好数据库连接，就可以进一步建立连接对象上的数据库游标（以进一步建立连接对象上的数据库游标（Cursor）对象，）对象，并调用游标对象的并调用游标对象的execute()方法执行各式各样的方法执行各式各样的SQL语句语句了。了。建立游标对象的写法如下：建立游标对象的写法如下：cursor= conn.cursor()209.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法3.执行执行SQL语句操作数据语句操作数据（1）
14、建立数据库的读者表的）建立数据库的读者表的Python语句如下，包括语句如下，包括一张读者表，含三个字段：一张读者表，含三个字段：cursor.execute(CREATE TABLE IF NOT EXISTS 读者读者表表 ( 借书证号借书证号 INTEGER, 姓名姓名TEXT, 班级班级TEXT, PRIMARY KEY(借书证号借书证号) ;)注意，其中的注意，其中的为续行符。为续行符。219.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法（2）向数据库依次增加四条读者数据的）向数据库依次增加四条读者数据的Python语句。语句。cursor
15、.execute(INSERT INTO 读者表读者表 VALUES(12305, 张张之焕之焕, 机械机械1318);)cursor.execute(INSERT INTO 读者表读者表 VALUES(63108, 李李红红, 电气电气1216);)cursor.execute(INSERT INTO 读者表读者表 VALUES(49529, 王王可詹可詹, 能动能动1120);)cursor.execute(INSERT INTO 读者表读者表 VALUES(49530, 王王可詹可詹, 能动能动1120);)（3）修改数据库读者表的一条记录中的李红为李宏。）修改数据库读者表的一条记录中的
16、李红为李宏。 cursor.execute(UPDATE读者表读者表 set 姓名姓名=李宏李宏 WHERE 姓名姓名=李红李红; )229.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法（4）删除数据库中的读者表的最后一条记录。）删除数据库中的读者表的最后一条记录。 cursor.execute(DELETE FROM读者表读者表 WHERE借书证号借书证号=49530; )（5）可以通过带）可以通过带“?”参数的参数的SQL语句进行读者表的数语句进行读者表的数据的增加。据的增加。cursor.execute(INSERT INTO 读者表读者表 V
17、ALUES (?,?,?), (12305, 张之焕张之焕, 机械机械1318)其中的三个问号会自动分别被其中的三个问号会自动分别被12305、 张之焕张之焕、 机械机械1318替替换。换。239.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法（6）还可以通过带）还可以通过带“?”参数的参数的SQL语句批量进行读者语句批量进行读者表的多条数据的增加，此时需要调用表的多条数据的增加，此时需要调用executemany()方方法，法，Python语句如下：语句如下：datas=(12306, 朱丽娟朱丽娟, 机械机械1318 )， (12307, 马小娜马
18、小娜, 机械机械1318 )， (12308, 马少飞马少飞, 机械机械1318 ) cursor.executemany(INSERT INTO 读者表读者表 VALUES (?,?,?);, datas) 249.3 Python的的SQLite数据库编程数据库编程n9.3.1 建表和数据的增删改方法4.提交更改和关闭连接提交更改和关闭连接使用连接对象的使用连接对象的commit()方法提交刚才作的修改，并使用连接方法提交刚才作的修改，并使用连接对象的对象的close ()方法关闭打开的数据库连接。方法关闭打开的数据库连接。Python语句如下语句如下：mit()conn.close()2
19、59.3 Python的的SQLite数据库编程数据库编程n9.3.2 数据的查询方法每一个操作每一个操作SQLite数据库的程序都需要引入数据库的程序都需要引入sqlite3模块、打开数据库、建立数据库游标对象。如：模块、打开数据库、建立数据库游标对象。如：import sqlite3conn = sqlite3.connect( library.db )cursor = conn.cursor()l通过游标对象的通过游标对象的execute()方法执行方法执行SQL语句进语句进行数据查询行数据查询；cursor.execute(SELECT * FROM读者表读者表 ORDER BY 班班
20、级级;) 269.3 Python的的SQLite数据库编程数据库编程n9.3.2 数据的查询方法通过游标对象的通过游标对象的fetchone()方法获取一条查询结果方法获取一条查询结果：row=cursor.fetchone() #获取一条查询结果获取一条查询结果print(row) #显示结果显示结果row=cursor.fetchone() #获取下一条结果获取下一条结果print(row0,row1,row2) #显示前三列显示前三列print(cursor.fetchone() #获取并显示下一条查询结果获取并显示下一条查询结果 或或row=cursor.fetchone()whil
21、e(row!=None)print(row)279.3 Python的的SQLite数据库编程数据库编程n9.3.2 数据的查询方法使用游标对象的使用游标对象的fetchall()方法可获取全部查询结果方法可获取全部查询结果。Python语句如下：语句如下：rows=cursor.fetchall()print(rows)也可以通过带也可以通过带“?”参数的参数的SQL语句进行读者表的数据语句进行读者表的数据查询，查询，Python语句如下：语句如下：t = (机械机械1318,)cursor.execute (SELECT* FROM读者表读者表 WHERE班级班级=?,t)print( cursor.fetchall() )289.3 Python的的SQLite数据库编程数据库编程n9.3.2 数据的查询方法还可以通过迭代器循环获取全部查询结果，还可以通过迭代器循环获取全部查询结果，Python语句如下：语句如下：resultset=cursor.execute(SELECT * FROM读者表读者表 ORDER BY 班级班级;)for row in resultset: print(row)最后使用连接对象的最后使用连接对象的close ()方法关闭打开的数据库方法关闭打开的数据库连接，连接，Python语句如下：语句如下：conn.close()29
