这里是使用 Python 的 PyMySQL 完成上述任务的代码：
```python
import pymysql
# 连接数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='test')
# 创建游标
cur = conn.cursor()
# 创建 users 表
cur.execute('''
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
)
''')
# 创建 orders 表
cur.execute('''
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
amount INT,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
)
''')
# 插入数据
users_data = [
(1, '张三', 18),
(2, '李四', 20),
(3, '王五', 22),
(4, '赵六', 25),
(5, '钱七', 28)
]
orders_data = [
(1, '2021-09-01', 500, 1),
(2, '2021-09-02', 1000, 2),
(3, '2021-09-03', 600, 3),
(4, '2021-09-04', 800, 4),
(5, '2021-09-05', 1500, 5),
(6, '2021-09-06', 1200, 3),
(7, '2021-09-07', 2000, 1),
(8, '2021-09-08', 300, 2),
(9, '2021-09-09', 700, 5),
(10, '2021-09-10', 900, 4)
]
for user in users_data:
cur.execute('INSERT INTO users VALUES (%s, %s, %s)', user)
for order in orders_data:
cur.execute('INSERT INTO orders VALUES (%s, %s, %s, %s)', order)
# 提交事务
conn.commit()
# 查询语句
# 1. 查询订单总金额
cur.execute('SELECT SUM(amount) FROM orders')
print(cur.fetchone()[0])
# 2. 查询所有用户的平均年龄，并将结果四舍五入保留两位小数。
cur.execute('SELECT ROUND(AVG(age), 2) FROM users')
print(cur.fetchone()[0])
# 3. 查询订单总数最多的用户的姓名和订单总数。
cur.execute('''
SELECT users.name, COUNT(*) AS total_orders
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_orders DESC
LIMIT 1
''')
print(cur.fetchone())
# 4. 查询所有不重复的年龄。
cur.execute('SELECT DISTINCT age FROM users')
print([row[0] for row in cur.fetchall()])
# 5. 查询订单日期在2021年9月1日至9月4日之间的订单总金额。
cur.execute('SELECT SUM(amount) FROM orders WHERE order_date BETWEEN "2021-09-01" AND "2021-09-04"')
print(cur.fetchone()[0])
# 6. 查询年龄不大于25岁的用户的订单数量，并按照降序排序。
cur.execute('''
SELECT users.name, COUNT(*) AS total_orders
FROM users JOIN orders ON users.id = orders.user_id
WHERE age <= 25
GROUP BY users.id
ORDER BY total_orders DESC
''')
print(cur.fetchall())
# 7. 查询订单总金额排名前3的用户的姓名和订单总金额。
cur.execute('''
SELECT users.name, SUM(amount) AS total_amount
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_amount DESC
LIMIT 3
''')
print(cur.fetchall())
# 8. 查询订单总金额最大的用户的姓名和订单总金额。
cur.execute('''
SELECT users.name, SUM(amount) AS total_amount
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_amount DESC
LIMIT 1
''')
print(cur.fetchone())
# 9. 查询订单总金额最小的用户的姓名和订单总金额。
cur.execute('''
SELECT users.name, SUM(amount) AS total_amount
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_amount ASC
LIMIT 1
''')
print(cur.fetchone())
# 10. 查询所有名字中含有“李”的用户，按照名字升序排序。
cur.execute('SELECT * FROM users WHERE name LIKE "%李%" ORDER BY name ASC')
print(cur.fetchall())
# 11. 查询所有年龄大于20岁的用户，按照年龄降序排序，并只显示前5条记录。
cur.execute('SELECT * FROM users WHERE age > 20 ORDER BY age DESC LIMIT 5')
print(cur.fetchall())
# 12. 查询每个用户的订单数量和订单总金额，并按照总金额降序排序。
cur.execute('''
SELECT users.name, COUNT(*) AS total_orders, SUM(amount) AS total_amount
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_amount DESC
''')
print(cur.fetchall())
# 关闭游标和连接
cur.close()
conn.close()
```
注意：在运行代码之前，需要先安装 PyMySQL 模块，可以使用以下命令进行安装：
```
pip install pymysql
```
扫一扫
1844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
