第十三周学习笔记

第十四章学习笔记

1. 数据库基础

1.1 数据库管理系统 (DBMS)

  • 数据库管理系统的概念和作用。
  • MySQL 是一个开源的关系型数据库管理系统 (RDBMS)。

1.2 数据库和表

  • 创建数据库：CREATE DATABASE database_name;
  • 选择数据库：USE database_name;
  • 删除数据库：DROP DATABASE database_name;
  • 创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
  • 删除表：DROP TABLE table_name;

1.3 数据类型

  • 整数类型：INT, BIGINT, TINYINT, 等。
  • 浮点数类型：FLOAT, DOUBLE.
  • 字符串类型：VARCHAR, CHAR, TEXT.
  • 日期和时间类型：DATE, TIME, DATETIME.

1.4 约束

  • 主键约束：PRIMARY KEY.
  • 外键约束：FOREIGN KEY.
  • 唯一约束：UNIQUE.
  • 默认值约束：DEFAULT.
  • 非空约束：NOT NULL.

2. SQL 查询

2.1 基本查询

  • 查询所有数据：SELECT * FROM table_name;
  • 条件查询：SELECT * FROM table_name WHERE condition;

2.2 排序和限制

  • 排序：SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
  • 限制行数：SELECT * FROM table_name LIMIT number;

2.3 聚合函数

  • COUNT(), SUM(), AVG(), MAX(), MIN() 的使用。
  • GROUP BY 子句：SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

2.4 分组和过滤

GROUP BY 和 HAVING 子句的使用。

2.5 连接

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 的使用。

2.6 子查询

子查询的基本语法和嵌套查询的应用。

3. 表设计

3.1 范式

  • 第一范式 (1NF)：每一列都包含不可再分的原子数据。
  • 第二范式 (2NF)：非主键列完全依赖于主键。
  • 第三范式 (3NF)：非主键列之间没有传递依赖关系。

3.2 关系

  • 一对一关系，一对多关系，多对多关系的理解和设计。

3.3 索引

  • 了解索引的作用和种类。
  • 创建索引：CREATE INDEX index_name ON table_name(column_name);
  • 删除索引：DROP INDEX index_name ON table_name;

4. 数据库管理

4.1 用户管理

  • 创建用户：CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  • 赋予权限：GRANT permission ON database_name.table_name TO 'username'@'localhost';
  • 撤销权限：REVOKE permission ON database_name.table_name FROM 'username'@'localhost';

4.2 备份和恢复

  • 备份数据库：mysqldump -u username -p database_name > backup.sql
  • 恢复数据库：mysql -u username -p database_name < backup.sql

5. 性能优化

5.1 查询优化

  • 使用 EXPLAIN 分析查询计划，了解查询执行的步骤和顺序。

5.2 索引优化

  • 选择适当的列作为索引。
  • 避免在列上进行函数操作以保持索引的有效性。

5.3 数据库配置

  • 了解并调整 MySQL 配置文件，根据服务器硬件和工作负载进行优化。

6. 安全性

6.1 访问控制

  • 使用 GRANT 和 REVOKE 控制用户对数据库的访问权限。

6.2 数据加密

  • 使用 SSL/TLS 加密数据库连接。
  • 对敏感数据进行加密存储。

苏格拉底挑战



问题及解决方案

1. 性能问题

1.1 查询性能慢

  • 问题原因： 可能是没有合适的索引，查询语句写得不够优化。
  • 解决方案：
  • 分析查询计划，使用 EXPLAIN 命令找出性能瓶颈。
  • 优化查询语句，确保索引被充分利用。
  • 考虑对频繁查询的列创建合适的索引。

1.2 数据库连接过多

  • 问题原因： 大量并发连接可能导致数据库性能下降。
  • 解决方案：
  • 调整数据库连接池的配置参数，控制最大连接数。
  • 使用连接池技术，如连接池软件或应用服务器中的连接池。

1.3 数据库配置不合理

  • 问题原因： MySQL 配置文件参数设置不合理。
  • 解决方案：
  • 根据服务器硬件和工作负载进行调整配置参数。
  • 考虑使用专业的性能优化工具进行调优。

2. 安全性问题

2.1 未授权访问

  • 问题原因： 数据库用户权限设置不当。
  • 解决方案：
  • 使用 GRANT 和 REVOKE 命令精确控制用户权限。
  • 避免使用过于宽泛的权限赋予，仅赋予必需的权限。

2.2 数据泄露

  • 问题原因： 敏感数据存储不加密，或者数据库连接不安全。
  • 解决方案：
  • 对敏感数据进行加密存储。
  • 使用 SSL/TLS 加密数据库连接。

3. 数据库设计问题

  • 3.1 不合理的表设计
  • 问题原因： 表的范式设计不当，冗余字段过多。
  • 解决方案：
  • 重新设计表结构，符合范式要求。
  • 考虑使用反范式设计，根据实际需求决定是否冗余字段。

3.2 缺乏合适的索引

  • 问题原因： 查询性能差，没有充分利用索引。
  • 解决方案：
  • 分析查询语句，确保索引被充分利用。
  • 考虑添加缺失的索引。

实践

1. 连接到 MySQL 数据库：

import mysql.connector
# 连接到 MySQL 数据库
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
# 创建一个游标对象
cursor = conn.cursor()

2. 创建表：

# 创建一个名为 students 的表
cursor.execute("""
    CREATE TABLE students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        age INT,
        grade VARCHAR(10)
    )
""")

3. 插入数据：

# 插入一条学生记录
cursor.execute("INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)", ("John Doe", 20, "A"))

4. 查询数据：

# 查询所有学生的记录
cursor.execute("SELECT * FROM students")
students = cursor.fetchall()

# 打印查询结果
for student in students:
    print(student)

5. 更新数据：

# 更新学生信息
cursor.execute("UPDATE students SET age = %s WHERE name = %s", (21, "John Doe"))

6. 删除数据：

# 删除学生记录
cursor.execute("DELETE FROM students WHERE name = %s", ("John Doe",))

7. 提交并关闭连接：

# 提交更改并关闭连接
conn.commit()
conn.close()
posted @ 2023-12-02 15:02  20211308wjc  阅读(4)  评论(0)  编辑  收藏  举报