标签：第十三 database 数据库笔记查询学习索引 table name

第十四章学习笔记

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()

标签：第十三,database,数据库,笔记,查询,学习,索引,table,name
From： https://www.cnblogs.com/wujiuchun/p/17871623.html

第十三周学习笔记