import pymysql
# 连接mysql
# host为数据库的主机IP地址, port为MySQL的默认端口号, user为数据的用户名, password为数据库的登录密码, db为数据库的名称
config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'reptiledata'
}
db = pymysql.connect(**config)
cursor = db.cursor() # 建立游标对象

# -----------------------------------------表操作----------------------------------------------------------------
# 如果存在就删除表
cursor.execute("drop table if exists user")

# 新增表
cursor.execute("""create table if not exists user(
                  id int auto_increment primary key,
                  name varchar(5),
                  sex  char(1),
                  id_card  char(18),
                  phone  varchar(14),
                  address  varchar(12),
                  create_time time)""")

# 修改自增长Id,数据库新增的数据会从1000开始但是原来的数据不会改变
cursor.execute('alter table user AUTO_INCREMENT=1000')

# 新增数据库表字段
cursor.execute('alter table user add plus varchar(8)')
# --------------------------插入数据-------------------------------------------------------------------------------
# 批量插入数据
cursor.execute('alter table user drop plus')

# 插入单条数据但因为后面用了db.rollback()回滚，所以此条数据不会被添加
cursor.execute("""insert into user(name, sex, id_card, phone, address)
        values ('李四', '女', '511569845612354879', '10086', '数据不会被添加')""")
db.rollback() 

# 批量插入数据，如果不列出字段名mysql会按照顺序添加
sql = """insert into user(name, sex, id_card, phone, address)
        values (%s, %s, %s, %s, %s)"""
sql_data = [['张三', '女', '511569845612354879', '10086', '太古里77号'],
            ['李四', '男', '511569845612354879', '10086', '太古里77号'],
            ['王麻子子', '男', '511569845612354879', '10086', '太古里66号'],
            ['张二', '女', '511569845612354879', '10086', '太古里77号'],
            ['李五', '男', '511569845612354879', '10086', '太古里88号'],
            ['王麻子', '女', '511569845612354879', '10086', '太古里99号']]
cursor.executemany(sql, sql_data)

# ----------------------------------------查询数据-----------------------------------------------------------------

# order by 对查询结果进行排序 select 字段1,字段2,字段3 from 表名 order by 字段 desc(降序)||asc(升序)
cursor.execute("select address,id from user order by id desc")
select = cursor.fetchall()
print(select)

# 去重复查询 select distinct 字段 from 表名
cursor.execute("select distinct address from user")
select = cursor.fetchall()
print(select)

# 分页查询 limit select 字段1，字段2 from 表名 limit 初始位置，记录数
cursor.execute("select id, name, sex, id_card from user limit 3,6")
select = cursor.fetchall()
print(select)

# 精准查询 in 只查找括号内的数据； not in 则相反
cursor.execute("select * from user where ID in (1001,1003)")
select = cursor.fetchall()
print(select)

# 精准查询 between and 查找1001-1003范围内的数据；not between and 相反
cursor.execute("select * from user where ID between 1001 and 1003")
select = cursor.fetchall()
print(select)

# 模糊查询 like (% 代表任意字符 _ 代表单个字符)；not like相反
cursor.execute("select * from user where name like '_麻%'")
select = cursor.fetchall()
print(select)

# 多条件查询 and 关键字
cursor.execute("select * from user where sex='女' and address='太古里77号'")
select = cursor.fetchall()
print(select)

# 多条件查询 or 关键字 只需满足一个条件
cursor.execute("select * from user where name='王麻子子' or address='太古里77号' ")
select = cursor.fetchall()
print(select)

# ------删除数据和更新数据 or,and,between and,like等关键字可以查询到多条数据然后批量删除或者更新-----------------------------------
# 删除数据
cursor.execute("delete from user where name='王麻子子' or address='太古里77号'")
# 数据库清空
cursor.execute("truncate table user")

# 更新数据
cursor.execute("update user set name = '更新' where ID in (1005,1006)")

cursor.close()  # 关闭游标对象
db.commit()  # 提交操作
db.close()  # 关闭数据库