mysql入门

pymysql做分页:

(1)只有上一页和下一页

做一个记录:记录当前页的最大id或最小id(每页10条数据)

下一页:

select * from usreinfo where id>max_id limit 10;

上一页:

select * from userinfo where id < min_id order by id desc limit 10;

(2)中间有页码的情况:

select * from userinfo where id in(
select id from (select * from userinfo where id > pre_max_id limit (cur_max_id-pre_max_id)*10) as A order by A.id desc limit 10
);

 

(1)下载pymysql pip3 install pymysql


import pymysql
# 相当于mysql的客户端程序
# 前端中获取的用户名和密码
username = input('请输入用户名:')
pwd = input('请输入密码:')

#建立链接:

conn=pymysql.connect(

host = '127.0.0.1',

port=3306, #固定的端口号

db = 'db20',
user = 'root',
password='***',
charset ='utf8'

)

#创建游标

cur = conn.cursor()

# select * from userinfo where name = 'alex' and password = '123';
sql = "select * from userinfo where name = %s and password = %s"
print(sql)

# sql --

# 执行sql 返回是查询的成功的记录

result = cur.execute(sql,[username,pwd])

# 游标一定要关闭  连接一定要关闭

cur.close()

conn.close()

if result:

　　#相应 数据 到前端

　　print(login success)

else:

　　print(login failed)

(2)插入数据 删除 更新数据一定要记得conn.commit

(3)查询

　　-fetchone() #获取第一条数据

　　-fetchmany(size)获取多条数据

　　-fetchall()获取所有