根据要求 创建表 结构并编写相应的SQL语句（基于MySQL自带客户端操作）
| 列名 | 类型 | 备注 |
|---|---|---|
| id | int | 不为空 & 自增 & 主键 |
| name | varchar(32) | 不为空 |
| password | varchar(64) | 不为空 |
| gender | char(1) | 不为空，支持：男、女 |
| varchar(64) | 可以为空 | |
| amount | decimal(10,2) | 不为空 & 默认值为 0 |
| ctime | datetime | 新增时的时间<br />提示：可基于datetime模块实现 |
- 根据上述表的要求创建相应的数据和表结构（注意编码）。
create table user_info(
-> id int not null auto_increment primary key,
-> name varchar(32) not null,
-> password varchar(64) not null
-> )default charset=utf8;
alter table user_info add gender char(1) check(gender='男' or gender='女');
alter table user_info add ctime datetime;
- 任意插入5条数据。
insert into user_info(name,password,gender,email,amount,ctime)values
-> ("Ann","123","女","dshjf@qq.com",6.552,"2025-11-11 11:11:44"),
-> ("Fksjf","123","女","54dsf@163.com",8664.55,"2025-11-11 11:11:44"),
-> ("dgisoh","123","男","dghsio@sdf.com",8794.35,"2025-11-11 11:11:44");
- 将
id>3的所有人的性别改为 男。
update user_info set gender="男" where id>3;
- 查询余额
amount>1000的所有用户。
select * from user_info where amount>1000;
- 让每个人的余额在自己原的基础上 +1000 。
update user_info set amount=amount+1000;
- 删除性别为男的所有数据。
delete from user_info where gender="男";
-
通过Python代码实现上述除了第一个以外的操作。
插入5条数据时，ctime那一列不要自己写“2021-11-11.。。” 而是使用datatime模块生成当前时间。
import re
import datetime
import pymysql
# 连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset="utf8")
cursor = conn.cursor() # 使用 cursor() 方法创建一个游标对象
# 创建数据库
# cursor.execute("create database userdb default charset utf8 collate utf8_general_ci")
# conn.commit() # 提交到数据库执行
# 查看数据库
cursor.execute("show databases")
result = cursor.fetchall()
print(result)
# # 进入数据库创建表
cursor.execute("use userdb")
# sql = """
# create table user_info(
# id int not null primary key auto_increment,
# name varchar(32) not null,
# password varchar(64) not null,
# gender char(1) check(gender='男' or gender='女'),
# email varchar(64),
# amount decimal(10,2) not null default 0,
# ctime datetime
# )default charset=utf8;
# """
# cursor.execute(sql) # 执行sql语句
# conn.commit() # 提交到数据库执行
# 查看数据库中的表
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
while True:
name = input("请输入用户名(Q退出)：").strip()
if name.upper() == "Q":
print("退出")
break
password = input("请输入密码：")
gender = input("请输入性别：")
if gender not in {"男", "女"}:
print("性别输入错误")
continue
email = input("请输入邮箱：")
if not re.match("(\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)", email, re.ASCII):
print("邮箱格式错误")
continue
amount = input("请输入余额：")
if not isinstance(float(amount), float):
print("格式错误")
continue
if len(amount) > 11:
print("长度错误")
continue
amount = round(float(amount), 2)
# print(amount)
ctime = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S') ## 把日期按照format指定的格式进行格式化
sql = 'insert into user_info(name,password,gender,email,amount,ctime) values("{}","{}","{}","{}","{}","{}")'.format(
name,
password,
gender,
email,
amount,
ctime)
cursor.execute(sql)
conn.commit()
# 关闭数据库连接
cursor.close()
conn.close()
import pymysql
# 连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", charset="utf8")
cursor = conn.cursor() # 使用 cursor() 方法创建一个游标对象
cursor.execute("use userdb")
sql = """
insert into user_info(name,password,gender,email,amount,ctime)values
("Ann","123","女","dshjf@qq.com",6.552,"2025-11-11 11:11:44"),
("Fksjf","123","女","54dsf@163.com",8664.55,"2025-11-11 11:11:44"),
("dgisoh","123","男","dghsio@sdf.com",8794.35,"2025-11-11 11:11:44");
"""
cursor.execute(sql)
conn.commit()
sql = """select * from user_info;"""
cursor.execute(sql)
conn.commit()
result = cursor.fetchall()
print(result)
# 关闭数据库连接
cursor.close()
conn.close()
