（二）利用sqlite3创建并操作数据库

• 在命令行启动sqlite3，创建student.db数据库
  
• 查看所有数据库
  
  
• 创建stu表（概括来讲，SQLite支持NULL、INTEGER、REAL、TEXT和BLOB数据类型，分别代表空值、整型值、浮点值、字符串文本、二进制对象。）
  
• 查看表结构
  
• 插入四条记录

sqlite> insert into stu (id, name, gender, age) values (1, '余星舟', '男', 19);
sqlite> insert into stu (id, name, gender, age) values (2, '李云美', '女', 18);
sqlite> insert into stu (id, name, gender, age) values (3, '张运恒', '男', 20);
sqlite> insert into stu (id, name, gender, age) values (4, '郑小翠', '女', 18);

• 1
• 2
• 3
• 4

• 查看stu全部表记录
  
• 查询stu所有男生记录
  
• 查询19岁的男生
  
• 查询19岁的男生，只显示姓名与年龄
  
• 按照年龄升序排列
  
• 按照年龄降序排列
  
• 先按性别降序排列，再按年龄升序排列
  
• 统计个数、最大年龄、最小年龄、总年龄、平均年龄
  
• 按性别分类统计个数、最大年龄、最小年龄、总年龄、平均年龄

select gender, count(*) as 人数, max(age) as 最大年龄, min(age) as 最小年龄, sum(age) as 总年龄, avg(age) as 平均年龄 from stu group by gender;

• 1

• 将郑小翠的年龄改成19岁
  
• 将第一条记录姓名改为“李晓文”，性别改为“女”，年龄改为“19”
  
• 删除id为3的记录
  
• 查询所有姓“李”的记录（模糊查询采用like运算符，有两个通配符：%和_，其中%代表任意多个任意字符，_代表一个任意字符）
  
• 修改id=1的记录，姓名改为“李晓”
  
• 查询所有姓“李”的单名记录
  
• 查询所有姓“李”的双名记录
  
• 插入一条“王晓云”记录
  
• 查询姓名包含了“云”的记录
  

三、使用Navicat来管理数据库

（一）下载Navicat12

下载链接：百度网盘 请输入提取码 提取码：aihp

（二）安装Navicat12

• 按照向导完成安装，启动Navicat12
  

（三）创建SQLite连接

• 选择创建SQLite…连接
  
• 设置连接名，设置现有sqlite3数据库
  
• 单击【确定】按钮
  
• 打开连接，查看数据库及表
  

（三）查看stu表内容

1、直接打开表查看表内容

• 选中表，单击右键，在快捷菜单中选择“打开表”
  
  

2、通过“查询”显示表内容

• 操作步骤录屏
  
• 查询操作的结果
  

（四）修改stu表内容

1、直接打开表修改表内容

• 直接修改id=1的记录
  
  

2、通过“查询”修改表内容

• 通过查询修改id=3的记录
  
• 查看修改后的表内容
  

（五）分类汇总stu表

• 按性别分类统计个数、最大年龄、最小年龄、总年龄、平均年龄
  

（六）多表关联查询

1、创建score表

CREATE TABLE score (id INTEGER, name TINYTEXT, chinese INTEGER, math INTEGER, english INTEGER, sum INTEGER);

• 1

• 给score表插入4条记录

INSERT INTO score (id, name, chinese, math, english) VALUES (1, '余星舟', 78, 84, 91);
INSERT INTO score (id, name, chinese, math, english) VALUES (2, '李云美', 80, 74, 65);
INSERT INTO score (id, name, chinese, math, english) VALUES (3, '张振强', 65, 90, 75);
INSERT INTO score (id, name, chinese, math, english) VALUES (4, '郑小翠', 90, 64, 88);

• 1
• 2
• 3
• 4

• 查看score表内容
  
• 利用update命令填充sum字段
  
• 查看score表记录
  

2、实现stu表与score表的关联查询（一对一的关联）

• 任务：查询每个学生的成绩情况，显示学号、姓名、性别、年龄、语文、数学、英语和总分

（1）利用where子句来实现关联查询

• FROM stu, score —— stu是主表，score是子表
  
  
• 都是按照FROM子句的第一张表的id顺序显示的
• 要按照score表的id顺序来显示（FROM score, stu —— score是主表，stu是子表）
  

（2）利用join关键字来实现关联查询

• 有四种连接方式：inner join, left outer join, right outer join, full outer join
• stu 关联 score 进行查询
  
• score 关联 stu 进行查询
  

四、Python访问SQLite数据库

SQLite是Python自带的唯一的关系数据库包，其他的关系数据库则需要通过第三方扩展来访问。Python的API规范定义了底层Python脚本和数据库的访问SQL接口，各种关系数据库在实现Python的SQL接口可能不会遵循Python规范，但与差异很小。

（一）了的解Python的SQL接口

1、连接对象 - Connection

连接对象用于创建数据库连接，所有的数据库操作均通过连接对象与数据库完成交互。连接对象生成游标对象。

2、游标对象 - Cursor

游标对象用于执行各种SQL语句：create table、update、insert、delete、select等。通常连接对象也可执行各种SQL语句。一般返回select语句都使用游标对象来执行，查询结果保存在游标对象中。

3、SQL查询结果

从游标对象中提取的查询结果时，单个记录表示为元组，多个记录则用包含元组的列表表示。在Python脚本中，进一步使用元组或列表操作来处理从数据库返回的查询结果。

（二）连接和创建SQLite数据库

1、创建数据库

• 访问SQLite数据库时，需要先导入sqlite3模块，然后调用connect()方法建立数据库连接。
  

2、创建内存数据库

• 如果使用“:memory:”表示文件名，Python会创建一个内存数据库。内存数据库中的所有数据均保存在内存中，关闭连接对象时，所有数据自动删除。

3、创建临时数据库

• 如果使用空字符串作为文件名，Python会创建一个临时数据库。临时数据库有一个临时文件，所有数据保存在临时文件中。连接对象关闭时，临时文件和数据也会自动删除。
  

4、关闭数据库连接

• 执行完所有操作后，应执行close()方法关闭连接对象，释放占用的资源。
  

（三）创建表

通过连接对象或游标对象的execute()方法执行create table语句创建表。

1、通过连接对象创建表

conn.execute('create table user(name text, gender text, age integer)')

• 1

• 删除刚才创建的user表
  

2、通过游标对象创建表

cursor = conn.cursor()
cursor.execute('create table user(name text, gender text, age integer)')

• 1
• 2

（四）添加记录

• 通过连接对象或游标对象的execute()方法执行insert into语句插入记录
  

• 添加记录后，可使用游标对象的rowcount属性查看影响的记录行数
  

• SQLite允许在insert into语句中使用问号表示参数，在execute()方法中用元组提供参数数据
  

• 使用参数时，可以使用executemany()方法一次添加多条记录，记录数据用元组列表表示
  

• 执行记录相关的修改操作（添加、删除或更新）时，应执行连接对象的commit()方法提交修改。如果没有执行commit()方法，关闭连接对象后，所有修改都会失效。
  

• 连接对象的另一个方法rollback()可用于撤销最后一次调用commit()方法后所做的修改。

（五）查询记录

1、使用游标对象的fetchall()方法提取全部查询结果

• 执行select语句可返回数据库中的数据。使用连接对象执行select语句时，返回包含查询结果的游标对象。游标对象的fetchall()方法提取全部查询结果。提取出的查询结果中，每条记录为一个元组，所有记录的元组组成一个列表。
  

• 使用连接对象的execute()方法执行SQL语句
  
  

2、使用游标对象来执行select语句

• 说明：调用游标对象的fetchall()方法之后，记录指针指向文件尾
  
• 能否将记录指针移向第一条记录？

3、使用循环来迭代fetchall()方法取回的数据

4、使用fetchone()方法提取一条记录

• fetchone()方法可以每次提取一条记录，返回的记录为一个元组。在达到表位时，返回None。
  
• 换一种方式来遍历全部记录