# Database Topics in Day 36-45 of Python-100-Days: MySQL, SQL, and Python Integration

> Master database topics from Day 36-45 in Python-100-Days. Explore SQL, MySQL, Python database integration, and Hive data warehousing fundamentals with this comprehensive guide.

- Repository: [骆昊/Python-100-Days](https://github.com/jackfrued/Python-100-Days)
- Tags: tutorial
- Published: 2026-02-24

---

**Days 36-45 of the Python-100-Days curriculum deliver a comprehensive ten-day progression from relational database theory through advanced SQL, MySQL 8.0 features, and Python database connectivity, culminating in Hive data warehousing fundamentals.**

The `jackfrued/Python-100-Days` repository structures its second major milestone around practical database engineering, moving from entity-relationship modeling to production-grade Python-MySQL implementations. These ten days—captured in the `Day36-45/` directory—provide the foundational knowledge required for data persistence in Python applications.

## Relational Database Foundations (Day 36)

The journey begins in `Day36-45/36.关系型数据库和MySQL概述.md` with **entity-relationship (ER) modeling** and the core principles of relational database management systems (RDBMS). Learners explore database normalization, primary and foreign key constraints, and the architectural differences between MySQL storage engines such as InnoDB and MyISAM.

## SQL Language Fundamentals (Days 37-40)

Days 37 through 40 dissect SQL into its four functional categories, each occupying a dedicated markdown file in the repository.

### Data Definition Language (DDL)

`Day36-45/37.SQL详解之DDL.md` covers schema management through **CREATE**, **ALTER**, and **DROP** statements. The material demonstrates table creation with constraint definitions, column modifications, and index creation syntax essential for database structure management.

### Data Manipulation Language (DML)

`Day36-45/38.SQL详解之DML.md` focuses on **INSERT**, **UPDATE**, and **DELETE** operations. The content emphasizes batch insertion techniques, safe update practices using WHERE clauses, and transaction-aware data modifications to prevent accidental data loss.

### Data Query Language (DQL)

`Day36-45/39.SQL详解之DQL.md` provides exhaustive coverage of the **SELECT** statement, progressing from basic WHERE clauses to complex **JOIN** operations, **GROUP BY** aggregations with **HAVING** filters, and correlated subqueries. The file introduces window functions for advanced analytical queries.

### Data Control Language (DCL)

`Day36-45/40.SQL详解之DCL.md` addresses database security through **GRANT** and **REVOKE** statements for user privilege management. The section also covers **transaction control language (TCL)** concepts including **COMMIT**, **ROLLBACK**, and savepoint management for ACID compliance.

## MySQL 8.0 Modern Features (Day 41)

`Day36-45/41.MySQL新特性.md` introduces capabilities exclusive to MySQL 8.0, including **JSON data type** support for semi-structured data, **Common Table Expressions (CTEs)** for recursive queries, and enhanced **window functions** such as `ROW_NUMBER()`, `RANK()`, and `LEAD()`/`LAG()` for analytical processing without self-joins.

## Database Objects and Optimization (Days 42-43)

### Views, Functions, and Stored Procedures

`Day36-45/42.视图、函数和过程.md` teaches encapsulation of complex logic through **database views** for simplified querying, **stored functions** for reusable calculations, and **stored procedures** for batch operations with input/output parameters.

### Indexing Strategies

`Day36-45/43.索引.md` explores **B-Tree** and **Hash** index types, composite index design, and the performance implications of covering indexes versus full table scans. The content addresses when to index foreign keys and how to analyze query execution plans.

## Python-MySQL Integration (Day 44)

`Day36-45/44.Python接入MySQL数据库.md` bridges SQL knowledge with Python application development using **`pymysql`** and **`mysql-connector-python`** libraries. The implementation patterns include:

```python
import pymysql

# Establish connection with context manager for automatic cleanup

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # Parameterized query preventing SQL injection

        sql = "INSERT INTO employees (name, salary) VALUES (%s, %s)"
        cursor.execute(sql, ('张三', 15000))
        
        # Transaction control

        conn.commit()
        
        # Fetching results

        cursor.execute("SELECT * FROM employees WHERE salary > %s", (10000,))
        results = cursor.fetchall()
finally:
    conn.close()

```

The file also demonstrates **connection pooling** configurations and proper exception handling for production environments.

## Data Warehousing with Hive (Day 45)

`Day36-45/45.Hive实战.md` transitions to big data ecosystems, introducing **HiveQL** syntax for querying distributed datasets stored in Hadoop. Topics include external tables, partition strategies for performance, and the differences between HiveQL and standard MySQL SQL dialects.

## Summary

- **Day 36** establishes relational theory and MySQL architecture in `Day36-45/36.关系型数据库和MySQL概述.md`
- **Days 37-40** provide exhaustive SQL coverage across DDL, DML, DQL, and DCL categories with dedicated files for each language component
- **Day 41** introduces modern MySQL 8.0 features including JSON support and window functions in `Day36-45/41.MySQL新特性.md`
- **Days 42-43** address database programmability through views/stored procedures and indexing optimization strategies
- **Day 44** delivers practical Python-MySQL connectivity patterns using `pymysql` and parameterized queries for secure database access
- **Day 45** expands into big data with HiveQL fundamentals for data warehousing scenarios

## Frequently Asked Questions

### What SQL categories are covered in Days 37-40?

Days 37-40 dissect SQL into four functional categories: **DDL** (Data Definition Language) for schema management in `Day36-45/37.SQL详解之DDL.md`, **DML** (Data Manipulation Language) for data modifications in `Day36-45/38.SQL详解之DML.md`, **DQL** (Data Query Language) for data retrieval in `Day36-45/39.SQL详解之DQL.md`, and **DCL** (Data Control Language) for security and transaction management in `Day36-45/40.SQL详解之DCL.md`.

### How does Day 44 demonstrate Python-MySQL connectivity?

`Day36-45/44.Python接入MySQL数据库.md` demonstrates connectivity through the **`pymysql`** and **`mysql-connector-python`** libraries, emphasizing context managers for resource cleanup, parameterized queries to prevent SQL injection, explicit transaction control via `commit()` and `rollback()`, and connection pooling for high-performance applications.

### What MySQL 8.0 features are introduced in Day 41?

`Day36-45/41.MySQL新特性.md` covers **JSON data types** for storing semi-structured data, **Common Table Expressions (CTEs)** for recursive and readable complex queries, and enhanced **window functions** including `ROW_NUMBER()`, `RANK()`, and `LEAD()`/`LAG()` for advanced analytical operations without requiring self-joins.

### Does Day 45 cover standard SQL or something different?

Day 45 (`Day36-45/45.Hive实战.md`) covers **HiveQL**, a SQL-like query language designed for Hadoop-based data warehouses. While syntactically similar to MySQL SQL, HiveQL specifically addresses distributed data processing, external tables, and partitioning strategies for petabyte-scale datasets, distinguishing it from the transactional SQL covered in earlier days.