Hello World

目标

通过 MySQL 命令行和 Python 驱动，完成数据库创建、表创建、数据增删改查（CRUD）的完整流程。

环境准备

确保 MySQL 已安装并运行：

mysql -u root -p

第一步：命令行方式

-- 创建数据库
CREATE DATABASE IF NOT EXISTS hello_world CHARACTER SET utf8mb4;

-- 切换到该数据库
USE hello_world;

-- 创建学生表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK(age > 0 AND age < 150),
    grade DECIMAL(3,1),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO students (name, age, grade) VALUES
    ('张三', 20, 88.5),
    ('李四', 21, 92.0),
    ('王五', 19, 76.5);

-- 查询
SELECT * FROM students;
SELECT name, grade FROM students WHERE grade >= 80 ORDER BY grade DESC;

-- 更新
UPDATE students SET grade = 90.0 WHERE name = '张三';

-- 删除
DELETE FROM students WHERE name = '王五';

-- 最终结果
SELECT * FROM students;

第二步：Python 驱动方式

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root123",
    database="hello_world"
)
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        age INT,
        grade DECIMAL(3,1)
    )
""")

# 批量插入
students = [("赵六", 22, 85.0), ("钱七", 20, 91.5)]
cursor.executemany(
    "INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)",
    students
)
conn.commit()

# 查询
cursor.execute("SELECT * FROM students WHERE grade >= 85")
for row in cursor.fetchall():
    print(f"ID={row[0]}, Name={row[1]}, Grade={row[3]}")

# 清理
cursor.close()
conn.close()

预期输出

+----+------+------+-------+
| id | name | age | grade |
+----+------+------+-------+
|  1 | 张三 |  20 |  90.0 |
|  2 | 李四 |  21 |  92.0 |
+----+------+------+-------+

ID=1, Name=张三, Grade=90.0
ID=2, Name=李四, Grade=92.0