今天继续写数据库的内容，后续将包含数据库的项目开发。

一、视图（View）—— 数据库的"虚拟表"

1. 视图的基本概念

什么是视图？

视图是基于SQL查询结果的虚拟表，不实际存储数据，只是【保存查询定义】。

视图的优势：

• 简化复杂查询
• 提高安全性（隐藏敏感数据）：管理员 和 普通用户的权限一样吗？
  • 管理员只把学生表中的学号和姓名导出为视图给普通用户，隐藏电话号、家庭住址等敏感数据
• 保持数据逻辑独立性：对视图进行排序等操作，是否影响原数据？

2. 视图操作语句

语法格式：

CREATE VIEW 视图名 AS 复杂查询语句

简单理解，就是给复杂的查询语句，起个小名，并且可以复用。

3. 视图的应用

创建常用业务视图

使用视图简化查询

更新数据表，视图会自动更新

更新视图可以吗？ INSERT DELETE UPDATE -- 有限制，只能基于单表

视图可以嵌套吗？ -- 可以，但是嵌套过深容易影响性能

二、索引（Index）—— 数据库的"目录"

1. 索引的基本概念

什么是索引？

索引是帮助数据库高效获取数据的数据结构，类似于书籍的目录。

索引的优势：

• 大幅提高查询速度
• 加速表连接
• 保证数据唯一性

索引的代价：

• 占用存储空间
• 降低写入速度（增删改时需要维护索引）

为什么快？三大原因：

1.数据量大幅减少：
        - 索引只包含关键字段，比整行数据小得多
        - 同样的磁盘I/O能读取更多索引条目
2.有序存储：
        - 索引是有序排列的（像字典的字母顺序）
        - 可以快速跳过不符合条件的数据
3.高效算法：
        - 使用B+树等结构，使查询复杂度从O(n)降到O(log n)
        - 100万数据只需约20次比较就能找到（因为2^20≈100万）

2. 常见索引类型

索引类型	说明	适用场景
普通索引	最基本的索引	大多数查询字段
唯一索引	值必须唯一，但是可以为NULL	身份证号、用户名等
主键索引	特殊的唯一索引	表的主键
复合索引	多列组合的索引，建议遵循从左往右	多条件查询
全文索引	文本内容搜索	文章、描述搜索

3. 索引操作语句

第一种：
-- 在创建数据表的时候，可以直接指定
CREATE TABLE  IF NOT EXISTS students(
    id INT PRIMARY KEY,    --主键 自动创建主键索引
    name VARCHAR(20),
    age INT,
    INDEX idx_name (name),    -- 创建名为idx_name的普通索引
    INDEX idx_name_age (name,age) -- 创建复合索引
);

第二种：
-- 在已有数据表的基础上，创建索引
CREATE INDEX idx_name ON students(name);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_tel ON users(tel);
-- 创建复合索引
CREATE INDEX idx_name_age ON students(name, age);
-- 查看索引
SHOW INDEX FROM students;
-- 删除索引
DROP INDEX idx_name ON students;

4. 索引设计原则

1.索引选择原则：

- 在WHERE、JOIN、ORDER BY子句中频繁使用的字段适合建索引
- 高基数字段(唯一值多的字段)更适合建索引

2.查看已有索引：

SHOW INDEX FROM users;

3.索引命名规范：

- 一般采用idx_字段名或表名_字段名_idx的格式
- 示例：idx_username或users_username_idx

4.复合索引顺序：

-- 好的顺序：WHERE a=? AND b=?
CREATE INDEX idx_a_b ON table(a, b);

-- 最左前缀原则
假设有idx_cls_age 基于 students(cls_id, age)
WHERE cls_id=1        使用 idx_cls_age
WHERE cls_id=1 AND age=18        使用 idx_cls_age
WHERE age=18        不会使用 idx_cls_age
WHERE cls_id=1 AND name='刘德华'       部分使用 idx_cls_age
 

三、事务（Transaction）——数据库的"原子操作"

1. 事务的基本概念

什么是事务？

事务是数据库操作的最小工作单元，是作为一个整体执行的一系列操作，要么全部执行成功，要么全部失败回滚。

2. 事务的四大特性（ACID）

特性	说明	生活比喻
Atomicity 原子性	事务是不可分割的工作单位	要么全做，要么全不做，像原子一样不可分割
Consistency 一致性	事务执行前后数据库状态一致	转账前后总金额不变
Isolation 隔离性	并发事务间互不干扰	多人同时转账互不影响
Durability 持久性	事务提交后永久有效	转账成功后结果永久保存

3. 事务控制语句

-- 开始事务
START TRANSACTION;  -- 或 BEGIN

-- 执行SQL操作
INSERT INTO orders(...) VALUES(...);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 123;

-- 提交或回滚
COMMIT;  -- 确认执行
ROLLBACK; -- 取消所有操作

4. 事务隔离级别

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✓	✓	✓	最高
READ COMMITTED	×	✓	✓	高
REPEATABLE READ	×	×	✓	中
SERIALIZABLE	×	×	×	最低

四、综合对比

特性	事务	索引	视图
目的	保证数据操作的完整性	提高查询性能	简化复杂查询
存储	不存储数据	存储索引数据结构	只存储查询定义
影响	影响数据一致性	影响查询/写入性能	不影响底层数据
生命周期	临时性(commit后结束)	永久性	永久性

最佳实践
1.事务使用建议：
- 尽量缩短事务持续时间
- 避免在事务中进行用户交互
- 合理设置隔离级别
2.索引优化建议：
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 定期分析表
ANALYZE TABLE orders;
3.视图设计建议：
- 避免多层嵌套视图
- 不要用视图执行复杂计算
- 考虑物化视图提高性能

五、pymysql使用流程

1、pymysql模式

import pymysql

# 1. 拨号连接
connection = pymysql.connect(
    host='localhost',  # 仓库地址
    user='root',       # 仓库管理员
    password='1234',   # 管理员密码
    database='hqyj',   # 指定仓库
    port=3306,         # 仓库门牌号
    charset='utf8'     # 沟通语言
)

# 2. 获取对讲机（游标）
cursor = connection.cursor()

# 3. 发送指令
cursor.execute("SELECT * FROM users;")

# 4. 听回复
print(cursor.fetchall())  # 获取所有结果

# 5. 挂电话（务必记得！）
cursor.close()
connection.close()

2、核心API

2.1 连接参数

参数	说明	示例
host	数据库服务器地址	'localhost' '127.0.0.1' '阿里云服务器等等'
port	端口号（默认3306）	3306
user	用户名	'root' 当然也可以配置子管理员
password	密码	'1234'
database	要连接的数据库名	'taobao'
charset	编码（推荐utf8）	'utf8'

2.2 游标操作

方法	作用	示例
execute()	执行SQL语句	cursor.execute("SELECT * FROM users")
fetchone()	获取一行结果	cursor.fetchone()
fetchall()	获取所有结果	cursor.fetchall()
fetchmany(n)	获取n行结果	cursor.fetchmany(5)
rowcount	获取受影响行数	cursor.rowcount

2.3 事务控制

try:
    # 执行修改操作
    cursor.execute("UPDATE account SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE account SET balance = balance + 100 WHERE id = 2")
    
    # 提交事务（确认操作）
    connection.commit()  
except:
    # 回滚事务（撤销操作）
    connection.rollback()

六、Python + SQLite 入门教程

SQLite 是一个轻量级的数据库系统，Python 内置了对 SQLite 的支持，使得我们可以很方便地在 Python 程序中使用数据库。

1. 基本概念

• SQLite：一个嵌入式的数据库，不需要单独的服务器进程
• 数据库：像是一个电子文件柜，用来存储有组织的数据
• 表：数据库中的一种数据结构，类似于 Excel 表格
• 记录(行)：表中的一条数据
• 字段(列)：表中的一列，表示数据的某个属性

2. 连接数据库

import sqlite3

# 连接到数据库（如果不存在会创建）
conn = sqlite3.connect('mydatabase.db')

# 创建一个游标对象，用于执行SQL命令
cursor = conn.cursor()

3. 创建表

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

# 提交更改
conn.commit()

4. 插入数据

# 插入一条数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ('张三', 25, 'zhangsan@example.com'))

# 插入多条数据
users_data = [
    ('李四', 30, 'lisi@example.com'),
    ('王五', 28, 'wangwu@example.com'),
    ('赵六', 35, 'zhaoliu@example.com')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)

# 提交更改
conn.commit()

5. 查询数据

# 查询所有用户
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
print("所有用户:")
for user in all_users:
    print(user)

# 查询特定条件的用户
cursor.execute("SELECT name, age FROM users WHERE age > ?", (28,))
older_users = cursor.fetchall()
print("\n年龄大于28的用户:")
for user in older_users:
    print(user)

6. 更新数据

# 更新张三的年龄
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, '张三'))
conn.commit()

print("\n更新后的张三信息:")
cursor.execute("SELECT * FROM users WHERE name = ?", ('张三',))
print(cursor.fetchone())

7. 删除数据

# 删除赵六的记录
cursor.execute("DELETE FROM users WHERE name = ?", ('赵六',))
conn.commit()

print("\n删除赵六后的用户列表:")
cursor.execute("SELECT * FROM users")
for user in cursor.fetchall():
    print(user)

8. 关闭连接

cursor.close()        # 关闭游标
conn.close()          # 关闭数据库

9. 实用技巧

1.使用上下文管理器 with（推荐）：

with sqlite3.connect('mydatabase.db') as conn:
    cursor = conn.cursor()
    # 执行各种操作
    # 不需要手动commit或close，退出with块会自动处理
    
在前面的文件操作中使用过的，with open() as f:不管文件的操作正确与否，
最终都会正确的关闭文件

 2 .获取列名：

• MySQL中cursor.execute 返回值是int 受影响的行数
• sqlite中cursor.execute 返回值是self 通过self.description 可以获取列名

3.使用字典形式返回结果：

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor:
    print(row['name'], row['email'])  # 可以通过列名访问

七、个人小项目展示

一、项目思路：

1、读者登录界面，跟数据库存储的账号密码进行匹配，如果匹配上了就登陆成功，同时利用索引进行登录频率排序进行优化登录搜查速度
    1.1成功后跳转用户管理界面，实现以下功能
        1.1.1 默认查看图书列表，呈现效果有限，将变成表格形式，仅有图书名称，图书作者以及库存余量。
        1.1.2 借书界面，选择要借的书（用下拉栏实现，仅显示离搜索框里的字最相近的五本图书，同时根据余量进行降序排序）然后点击借书后成功借出，数据库根据情况进行调整。
        1.1.3 借书管理界面，将呈现用户已经借出的书，显示书名，借书时间，还书情况，如果还书情况为未还，则后面会出现还书按钮，点击后，还书情况将改变为已归还，同时后面的按钮变为灰色不可点击。
2、管理员登录界面，同样是数据库匹配，为了快速则进行分开登陆界面，使用两个两个数据库进行分开操作。
    2.1成功后跳转管理员界面，实现以下功能
        2.1.1 图书馆情况，各种表（暂无）
        2.1.2 用户管理（预设是拉黑，暂无）
 

二、数据库设计

部分	内容
4张表	users（读者）、admins（管理员）、books（图书）、borrow_records（借阅记录）
6 个索引	登录频率排序、用户名快速匹配、图书搜索+库存排序、借阅记录按用户/图书查询
外键约束	borrow_records 关联 users 和 books，删除用户时自动清理其借阅记录
测试数据	3 个读者（含 1 个被拉黑的 bob）、2 个管理员、10 本书、5 条借阅记录（有未还也有已归还）

三、项目结构

library_system/                 ← 项目根目录（唯一入口文件夹）
│
├── app.py                      # 主入口：组装所有层，根据登录态路由页面
├── library.db                  # SQLite 数据库（单文件，跟着项目走）
├── core/                       # 核心层：数据 + 业务（设计的底层类）
│   ├── __init__.py
│   ├── database.py             # Database 类（连接、通用CRUD、事务）
│   ├── dao.py                  # UserDAO / AdminDAO / BookDAO / BorrowDAO
│   ├── service.py              # AuthService / BookService / AdminService
│   └── session.py              # SessionManager（登录态管理）
│
└── views/                      # 表现层：Streamlit 页面类（避免用 pages/ 与 Streamlit 自动导航冲突）
    ├── __init__.py
    ├── login_view.py           # LoginPage（读者/管理员双入口）
    ├── reader_view.py          # ReaderPage（图书列表 / 借书 / 借书管理）
    └── admin_view.py           # AdminPage（统计面板 / 用户管理）

四、项目展示（仅展示部分功能）

 1、登陆界面

2、读者中心

3、管理员中心

以上就是本人对数据库内容的复习。