根據(jù)要求 創(chuàng)建表 結(jié)構(gòu)并編寫相應(yīng)的SQL語句（基于MySQL自帶客戶端操作）

• 根據(jù)上述表的要求創(chuàng)建相應(yīng)的數(shù)據(jù)和表結(jié)構(gòu)（注意編碼）。

 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條數(shù)據(jù)。

 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;

• 讓每個人的余額在自己原的基礎(chǔ)上 +1000 。

update user_info set amount=amount+1000;

• 刪除性別為男的所有數(shù)據(jù)。

delete from user_info where gender="男";

• 通過Python代碼實現(xiàn)上述除了第一個以外的操作。

  插入5條數(shù)據(jù)時，ctime那一列不要自己寫“2021-11-11.。?！?而是使用datatime模塊生成當(dāng)前時間。
  

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() 方法創(chuàng)建一個游標(biāo)對象
# 創(chuàng)建數(shù)據(jù)庫
# cursor.execute("create database userdb default charset utf8 collate utf8_general_ci")
# conn.commit() # 提交到數(shù)據(jù)庫執(zhí)行
# 查看數(shù)據(jù)庫
cursor.execute("show databases")
result = cursor.fetchall()
print(result)
# # 進入數(shù)據(jù)庫創(chuàng)建表
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) # 執(zhí)行sql語句
# conn.commit() # 提交到數(shù)據(jù)庫執(zhí)行

# 查看數(shù)據(jù)庫中的表
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()
# 關(guān)閉數(shù)據(jù)庫連接
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() 方法創(chuàng)建一個游標(biāo)對象
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)
# 關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()