Python系列课(10)——SQL
·
目录
3. 执行非查询SQL（INSERT/UPDATE/DELETE）
一、数据库介绍
1. 数据库的作用
数据库是存储数据的库，按照 库 → 表 → 数据 三个层级组织数据。
数据库（软件） └── 数据库1（库） ├── 表1 │ ├── 数据行1 │ ├── 数据行2 │ └── ... └── 表2 └── 数据库2（库） └── ...
2. 常见数据库软件
| 数据库 | 特点 |
|---|---|
| MySQL | 中小型，开源免费，最常用 |
| Oracle | 大型，收费，功能强大 |
| SQL Server | 微软产品，Windows生态 |
| PostgreSQL | 开源，功能丰富 |
| SQLite | 轻量级，嵌入式使用 |
二、MySQL安装
1. 下载地址
https://downloads.mysql.com/archives/installer
2. 配置环境变量
将MySQL安装目录的 bin 文件夹路径添加到系统Path变量中。
3. 连接MySQL
mysql -uroot -p # 输入密码后进入命令行环境
4. 常用命令
show databases; -- 查看所有数据库 use 数据库名; -- 使用某个数据库 show tables; -- 查看数据库内有哪些表 exit; -- 退出MySQL命令行
三、SQL语言基础
1. SQL分类
| 分类 | 全称 | 作用 |
|---|---|---|
| DDL | Data Definition Language | 定义数据结构（库、表） |
| DML | Data Manipulation Language | 操作数据（增删改） |
| DQL | Data Query Language | 查询数据 |
| DCL | Data Control Language | 权限管理 |
2. SQL语法特征
-
大小写不敏感
-
可以单行或多行书写，以
;号结束 -
支持注释：
-- 单行注释（--后面必须有空格） # 单行注释（#后面可加可不加空格） /* 多行注释 */
四、DDL（数据定义语言）
1. 数据库管理
-- 查看所有数据库 SHOW DATABASES; -- 创建数据库 CREATE DATABASE 数据库名; CREATE DATABASE py_sql; -- 使用数据库 USE 数据库名; USE py_sql; -- 删除数据库 DROP DATABASE 数据库名; -- 查看当前使用的数据库 SELECT DATABASE();
2. 表管理
-- 查看当前数据库中的所有表 SHOW TABLES; -- 创建表 CREATE TABLE 表名 ( 列名1 数据类型, 列名2 数据类型, ... ); -- 示例：创建学生表 CREATE TABLE student ( id INT, name VARCHAR(50), age INT, score DECIMAL(5,2) ); -- 删除表 DROP TABLE 表名; DROP TABLE student; -- 查看表结构 DESC 表名;
五、DML（数据操作语言）
1. INSERT（插入数据）
-- 插入指定列 INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2); -- 插入全部列（列名可省略） INSERT INTO 表名 VALUES (值1, 值2, 值3); -- 示例 INSERT INTO student (id, name, age, score) VALUES (1, '张三', 18, 85.5); INSERT INTO student VALUES (2, '李四', 19, 92.0); INSERT INTO student VALUES (3, '王五', 20, 78.5); -- 批量插入 INSERT INTO student VALUES (4, '赵六', 18, 88.0), (5, '钱七', 19, 91.5);
注意：字符串需要用单引号包围。
2. DELETE（删除数据）
-- 删除所有数据（危险操作） DELETE FROM 表名; -- 带条件删除 DELETE FROM 表名 WHERE 条件; -- 示例 DELETE FROM student WHERE id = 5; DELETE FROM student WHERE age < 18; DELETE FROM student WHERE name = '张三';
3. UPDATE（更新数据）
-- 更新指定列 UPDATE 表名 SET 列名 = 新值 WHERE 条件; -- 更新多列 UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件; -- 示例 UPDATE student SET age = 19 WHERE name = '张三'; UPDATE student SET age = 20, score = 90.0 WHERE id = 2;
六、DQL（数据查询语言）
1. 基础查询（SELECT）
-- 查询所有列 SELECT * FROM 表名; -- 查询指定列 SELECT 列1, 列2 FROM 表名; -- 带条件查询 SELECT * FROM 表名 WHERE 条件; -- 示例 SELECT * FROM student; -- 查询所有 SELECT name, score FROM student; -- 查询指定列 SELECT * FROM student WHERE age >= 18; -- 条件查询 SELECT * FROM student WHERE name = '张三';
2. 分组聚合（GROUP BY）
-- 基础语法 SELECT 分组列, 聚合函数(列) FROM 表名 GROUP BY 分组列; -- 常用聚合函数 COUNT(*) -- 统计行数 SUM(列) -- 求和 AVG(列) -- 平均值 MAX(列) -- 最大值 MIN(列) -- 最小值 -- 示例：统计每个年龄的学生人数 SELECT age, COUNT(*) FROM student GROUP BY age; -- 示例：统计每个年龄的平均分 SELECT age, AVG(score) FROM student GROUP BY age;
注意：GROUP BY 中出现的列，才能在 SELECT 中的非聚合部分出现。
3. 排序和分页
-- 排序（ASC升序/DESC降序） SELECT * FROM 表名 ORDER BY 列名 ASC/DESC; -- 分页限制 SELECT * FROM 表名 LIMIT 起始位置, 数量; SELECT * FROM 表名 LIMIT 数量; -- 从0开始取前N条 -- 示例 SELECT * FROM student ORDER BY score DESC; -- 按分数降序 SELECT * FROM student ORDER BY age ASC; -- 按年龄升序 SELECT * FROM student LIMIT 0, 3; -- 第1-3条 SELECT * FROM student LIMIT 3, 3; -- 第4-6条 SELECT * FROM student LIMIT 5; -- 前5条
4. 综合查询执行顺序
SELECT 列 FROM 表 WHERE 条件 -- 1. 过滤行 GROUP BY 列 -- 2. 分组 HAVING 分组条件 -- 3. 过滤分组 ORDER BY 列 -- 4. 排序 LIMIT 数量; -- 5. 限制行数
七、Python操作MySQL（pymysql）
1. 安装pymysql
pip install pymysql
2. 连接数据库
from pymysql import Connection
# 创建连接
conn = Connection(
host="localhost", # 主机名
port=3306, # 端口号
user="root", # 用户名
password="123456" # 密码
)
# 选择数据库
conn.select_db("py_sql")
# 关闭连接
conn.close()
3. 执行非查询SQL（INSERT/UPDATE/DELETE）
from pymysql import Connection
conn = Connection(
host="localhost",
port=3306,
user="root",
password="123456"
)
conn.select_db("py_sql")
# 获取游标对象
cursor = conn.cursor()
# 执行SQL
cursor.execute("INSERT INTO student VALUES (1, '张三', 18, 85.5)")
# 提交事务（重要！）
conn.commit()
# 关闭连接
conn.close()
4. 自动提交
conn = Connection( host="localhost", port=3306, user="root", password="123456", autocommit=True # 自动提交 )
5. 执行查询SQL
from pymysql import Connection
conn = Connection(
host="localhost",
port=3306,
user="root",
password="123456",
autocommit=True
)
conn.select_db("py_sql")
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM student")
# 获取所有结果
results = cursor.fetchall()
print(results)
# 输出格式：((1, '张三', 18, 85.5), (2, '李四', 19, 92.0), ...)
# 遍历结果
for row in results:
print(f"id: {row[0]}, name: {row[1]}, age: {row[2]}, score: {row[3]}")
conn.close()
八、综合案例
需求
将1月文本数据和2月JSON数据写入MySQL数据库。
1. 建表SQL
CREATE DATABASE py_sql; USE py_sql; CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, date VARCHAR(20), order_id VARCHAR(50), money INT, province VARCHAR(50) );
2. Python代码实现
import json
from pymysql import Connection
from typing import List, Dict, Tuple
class Record:
"""单条销售记录"""
def __init__(self, date: str, order_id: str, money: int, province: str):
self.date = date
self.order_id = order_id
self.money = money
self.province = province
class FileReader:
def read_text(self, file_path: str) -> List[Record]:
"""读取文本文件"""
records = []
with open(file_path, "r", encoding="UTF-8") as f:
for line in f:
line = line.strip()
if not line:
continue
parts = line.split(",")
record = Record(parts[0], parts[1], int(parts[2]), parts[3])
records.append(record)
return records
def read_json(self, file_path: str) -> List[Record]:
"""读取JSON文件"""
records = []
with open(file_path, "r", encoding="UTF-8") as f:
data = json.load(f)
for item in data:
record = Record(item["date"], item["order_id"], item["money"], item["province"])
records.append(record)
return records
class MySQLWriter:
def __init__(self, host: str, port: int, user: str, password: str, database: str):
self.conn = Connection(
host=host,
port=port,
user=user,
password=password,
autocommit=True
)
self.conn.select_db(database)
def write_records(self, records: List[Record]) -> int:
"""写入记录到数据库，返回写入行数"""
cursor = self.conn.cursor()
count = 0
for record in records:
sql = f"""
INSERT INTO orders (date, order_id, money, province)
VALUES ('{record.date}', '{record.order_id}', {record.money}, '{record.province}')
"""
cursor.execute(sql)
count += 1
cursor.close()
return count
def read_from_db(self) -> List[Tuple]:
"""从数据库读取数据"""
cursor = self.conn.cursor()
cursor.execute("SELECT date, order_id, money, province FROM orders")
results = cursor.fetchall()
cursor.close()
return results
def export_to_file(self, file_path: str):
"""导出数据到文本文件"""
data = self.read_from_db()
with open(file_path, "w", encoding="UTF-8") as f:
for row in data:
f.write(f"{row[0]},{row[1]},{row[2]},{row[3]}\n")
print(f"✅ 数据已导出到：{file_path}")
def close(self):
self.conn.close()
def main():
# 1. 准备数据
jan_data = """2024-01-01,ORD001,1000,北京
2024-01-01,ORD002,1500,上海
2024-01-02,ORD003,2000,广东
2024-01-02,ORD004,1200,北京"""
feb_data = [
{"date": "2024-02-01", "order_id": "FEB001", "money": 3000, "province": "上海"},
{"date": "2024-02-01", "order_id": "FEB002", "money": 2500, "province": "广东"},
{"date": "2024-02-02", "order_id": "FEB003", "money": 1800, "province": "北京"}
]
# 写入模拟文件
with open("sales_jan.txt", "w", encoding="UTF-8") as f:
f.write(jan_data)
with open("sales_feb.json", "w", encoding="UTF-8") as f:
json.dump(feb_data, f, ensure_ascii=False)
# 2. 读取文件
reader = FileReader()
jan_records = reader.read_text("sales_jan.txt")
feb_records = reader.read_json("sales_feb.json")
all_records = jan_records + feb_records
print(f"读取到 {len(all_records)} 条记录")
# 3. 写入MySQL
writer = MySQLWriter(
host="localhost",
port=3306,
user="root",
password="123456",
database="py_sql"
)
count = writer.write_records(all_records)
print(f"✅ 成功写入 {count} 条记录到数据库")
# 4. 导出数据
writer.export_to_file("export_data.txt")
writer.close()
if __name__ == "__main__":
main()
输出：
读取到 7 条记录 ✅ 成功写入 7 条记录到数据库 ✅ 数据已导出到：export_data.txt
九、SQL速查表
DDL
| 操作 | SQL语句 |
|---|---|
| 查看数据库 | SHOW DATABASES; |
| 创建数据库 | CREATE DATABASE 库名; |
| 使用数据库 | USE 库名; |
| 删除数据库 | DROP DATABASE 库名; |
| 查看表 | SHOW TABLES; |
| 创建表 | CREATE TABLE 表名 (列名 类型); |
| 删除表 | DROP TABLE 表名; |
DML
| 操作 | SQL语句 |
|---|---|
| 插入数据 | INSERT INTO 表名 VALUES (...); |
| 更新数据 | UPDATE 表名 SET 列=值 WHERE 条件; |
| 删除数据 | DELETE FROM 表名 WHERE 条件; |
DQL
| 操作 | SQL语句 |
|---|---|
| 基础查询 | SELECT * FROM 表名; |
| 条件查询 | SELECT * FROM 表名 WHERE 条件; |
| 分组聚合 | SELECT 列, COUNT(*) FROM 表名 GROUP BY 列; |
| 排序 | SELECT * FROM 表名 ORDER BY 列 ASC/DESC; |
| 分页 | SELECT * FROM 表名 LIMIT 起始, 数量; |
Python pymysql
| 操作 | 代码 |
|---|---|
| 安装 | pip install pymysql |
| 连接 | conn = Connection(host, port, user, password) |
| 获取游标 | cursor = conn.cursor() |
| 执行SQL | cursor.execute(sql) |
| 查询结果 | cursor.fetchall() |
| 提交事务 | conn.commit() |
| 关闭连接 | conn.close() |
更多推荐
6
0- 0
已为社区贡献15条内容
所有评论(0)