MySQL学习笔记

import pymysql


# 连接MySQL (socket)

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")

cursor = conn.cursor()

  

# 1. 查看数据库

# 发送指令

cursor.execute("show databases")

# 获取指令的结果

result = cursor.fetchall()

print(result)  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

  

# 2. 创建数据库 (新增、删除、修改)

# 发送指令

cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")

conn.commit()

  

# 3. 查看数据库

# 发送指令

cursor.execute("show databases")

# 获取指令的结果

result = cursor.fetchall()

print(result)  # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))

  

# 4. 删除数据库

# 发送指令

cursor.execute("drop database db3")

conn.commit()

  

# 3. 查看数据库

# 发送指令

cursor.execute("show databases")

# 获取指令的结果

result = cursor.fetchall()

print(result)  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

  

# 5. 进入数据库，查看表

# 发送指令

cursor.execute("use mysql")

cursor.execute("show tables")

result = cursor.fetchall()

print(result)  # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),.....

  

# 关闭连接

cursor.close()

conn.close()

import pymysql


# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 创建数据库
"""
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
conn.commit()
"""

# 2. 进入数据库、查看数据表
"""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 3. 进入数据库创建表
cursor.execute("use db4")
sql = 
"""
create table L4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

# 4. 插入单条数据
"""
cursor.execute("use db4")
sql = "insert into L4(title,content,ctime) values(%s,%s,%s)"
cursor.execute(sql, ("标题1","内容文本","2026-08-17 16:48:35"))
conn.commit()
"""

# 5. 批量插入多条数据
"""
cursor.execute("use db4")
sql = "insert into L4(title,content,ctime) values(%s,%s,%s)"
data_list = [
    ("标题2","内容2","2026-08-17 17:00:00"),
    ("标题3","内容3","2026-08-17 17:05:00")
]
cursor.executemany(sql, data_list)
conn.commit()
"""

# 6. 查询全部数据 fetchall()
"""
cursor.execute("use db4")
cursor.execute("select * from L4")
result = cursor.fetchall()
print(result)
"""

# 7. 查询单条数据 fetchone()
"""
cursor.execute("use db4")
cursor.execute("select * from L4 where id=1")
result = cursor.fetchone()
print(result)
"""

# 8. 修改更新数据 update
"""
cursor.execute("use db4")
sql = "update L4 set title=%s where id=%s"
cursor.execute(sql, ("修改后的标题",1))
conn.commit()
"""
# 9. 删除数据 delete
"""
cursor.execute("use db4")
sql = "delete from L4 where id=%s"
cursor.execute(sql,(1,))
conn.commit()
"""

# 10. 删除表
"""
cursor.execute("use db4")
cursor.execute("drop table if exists L4")
conn.commit()
"""

# 11. 删除数据库
"""
cursor.execute("drop database if exists db4")
conn.commit()
"""

# 12. 关闭游标、关闭连接
"""
cursor.close()
conn.close()
"""

import pymysql


#连接数据库
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root123',charset="utf8")
cursor = conn.cursor()

#切换数据库
cursor.execute("use db4")

# ==========1.新增数据 insert==========
"""
#单条插入
sql = "insert into tbl(name,password) values(%s,%s)"
cursor.execute(sql,("武沛齐","123123"))

#批量插入
sql2 = "insert into tbl(name,password) values(%s,%s)"
data = [("武沛齐","123123"),("alex","123")]
cursor.executemany(sql2,data)

conn.commit()
"""

# ==========2.删除数据 delete==========
"""
sql = "delete from tbl where name=%s"
cursor.execute(sql,("wupeiqi",))
conn.commit()
"""

# ==========3.修改数据 update==========
"""
sql = "update tbl set name=%s where id=%s"
cursor.execute(sql,("wupeiqi",1))
conn.commit()
"""

# ==========4.查询数据 select==========
"""
cursor.execute("select * from tbl")
res = cursor.fetchall()
print(res)
cursor.execute("select name,password from tbl where id>%s",(2,))
res2 = cursor.fetchall()
print(res2)
"""
#关闭
cursor.close()
conn.close()

import pymysql


def register():
    print("用户注册")
    user = input("请输入用户名：")
    password = input("请输入密码：")

    # 连接指定数据库
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', charset="utf8", db="userdb")
    cursor = conn.cursor()

    # 执行sql语句（有sql注入风险）
    sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
    cursor.execute(sql)
    conn.commit()

    # 关闭数据库连接
    cursor.close()
    conn.close()

    print("注册成功, 用户名: {},密码:{}".format(user, password))


def login():
    """原版风格登录，同样使用format拼接，存在注入漏洞"""
    print("用户登录")
    user = input("请输入用户名：")
    password = input("请输入密码：")

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="userdb")
    cursor = conn.cursor()

    # format拼接SQL，存在SQL注入
    sql = 'select * from users where name="{}" and password="{}"'.format(user, password)
    cursor.execute(sql)
    result = cursor.fetchone()

    cursor.close()
    conn.close()

    if result:
        print("登录成功")
    else:
        print("用户名或密码错误")

if __name__ == '__main__':
    while True:
        op = input("请选择功能：1注册 | 2登录 | 3退出\n>>>")
        if op == "1":
            register()
        elif op == "2":
            login()
        elif op == "3":
            break
        else:
            print("输入无效")

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 列表传参方式
cursor.execute("select * from users where name=%s and password=%s", [user, pwd])

# 或者字典命名占位符传参方式
# cursor.execute("select * from users where name=%(name)s and password=%(pwd)s", {"name": user, 'pwd': pwd})

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

import pymysql

def get_db_conn():
    """封装获取数据库连接，复用代码"""
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', charset="utf8", db="userdb")
    cursor = conn.cursor()
    return conn, cursor


def register():
    """安全版本注册"""
    print("====用户注册====")
    user = input("请输入用户名：")
    password = input("请输入密码：")

    conn, cursor = get_db_conn()
    sql = "insert into users(name,password) values(%s,%s)"
    cursor.execute(sql, (user, password))
    conn.commit()

    cursor.close()
    conn.close()
    print(f"注册成功，用户名：{user}")


def login():
    """用户登录"""
    print("====用户登录====")
    user = input("请输入用户名：")
    password = input("请输入密码：")

    conn, cursor = get_db_conn()
    sql = "select * from users where name=%s and password=%s"
    cursor.execute(sql, (user, password))
    res = cursor.fetchone()  # 查询匹配的一行数据

    cursor.close()
    conn.close()

    if res:
        print("登录成功")
    else:
        print("用户名或密码错误")


# 主程序入口
if __name__ == '__main__':
    while True:
        choice = input("请选择功能：1注册  2登录  3退出\n>>>")
        if choice == "1":
            register()
        elif choice == "2":
            login()
        elif choice == "3":
            break
        else:
            print("输入错误，请重新选择！")