一，IDE工具介绍
生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具下载链接：https://pan.baidu.com/s/1bpo5mqj掌握：#1. 测试+链接数据库#2. 新建库#3. 新建表，新增字段+类型+约束#4. 设计表：外键#5. 新建查询#6. 备份库/表#注意：批量加注释：ctrl+？键批量去注释：ctrl+shift+？键
二，pymysql模块
先安装 pip3 install pymysql 才能导入这个模块import pymysqlconn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8')cursor=conn.cursor(pymysql.cursors.DictCursor)# rows=cursor.execute('show tables;')rows=cursor.execute('select * from class;')print(rows)## print(cursor.fetchone())# print(cursor.fetchmany(2))# print(cursor.fetchone())# print(cursor.fetchall())## print(cursor.fetchall())# cursor.scroll(3,'absolute')# print(cursor.fetchone())## print(cursor.fetchone())# print(cursor.fetchone())# cursor.scroll(1,'relative')# print(cursor.fetchone())## cursor.close()# conn.close()import pymysql #pip3 install pymysqlconn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8')cursor=conn.cursor(pymysql.cursors.DictCursor)inp_user=input('用户名>>：').strip() #inp_user=""inp_pwd=input('密码>>：').strip() #inp_pwd=""sql="select * from user where name='%s' and password='%s'" %(inp_user,inp_pwd)print(sql)rows=cursor.execute(sql)if rows: print('登录成功')else: print('登录失败')cursor.close()conn.close()直接改变了sql 绕过了密码和用户名#在服务器端防止sql注入问题：不要自己拼接字符串，让pymysql模块去拼接import pymysql #pip3 install pymysqlconn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8')cursor=conn.cursor(pymysql.cursors.DictCursor)inp_user=input('用户名>>：').strip() #inp_user=""inp_pwd=input('密码>>：').strip() #inp_pwd=""sql="select * from user where name=%s and password=%s"print(sql)rows=cursor.execute(sql,(inp_user,inp_pwd))if rows: print('登录成功')else: print('登录失败')cursor.close()conn.close() 三，、增、删、改：conn.commit()
import pymysql #pip3 install pymysqlconn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8')cursor=conn.cursor(pymysql.cursors.DictCursor)# sql='insert into user(username,password) values(%s,%s)'# rows=cursor.execute(sql,('EGON','123456'))# print(rows)# print(cursor.lastrowid)# rows=cursor.execute('update user set username="alexSB" where id=2')# print(rows)# 一次插入多行记录sql='insert into user(username,password) values(%s,%s)'rows=cursor.executemany(sql,[('lwz','123'),('evia','455'),('lsd','333')])print(rows)print(cursor.lastrowid)conn.commit() # 只有commit提交才会完成真正的修改cursor.close()conn.close() 四，查：fetchone，fetchmany，fetchall
fetchone（查询单条） fetchmany（查询多条） fetchall（查询所有）import pymysqlconn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8')cursor=conn.cursor(pymysql.cursors.DictCursor)# rows=cursor.execute('show tables;')rows=cursor.execute('select * from class;')print(rows)## print(cursor.fetchone())# print(cursor.fetchmany(2))# print(cursor.fetchone())# print(cursor.fetchall())## print(cursor.fetchall())# cursor.scroll(3,'absolute')# print(cursor.fetchone())## print(cursor.fetchone())# print(cursor.fetchone())# cursor.scroll(1,'relative')# print(cursor.fetchone())## cursor.close()# conn.close() 五，获取最后
------查看表中最后一行的iDimport pymysqlconn=pymysql.connect(host='localhost',user='root',password='123456', database='day47',charset='utf8')cursor=conn.cursor()sql='insert into user1(user,password) values(%s,%s);'rows=cursor.execute(sql,('alex','123'))# rows=cursor.executemany(sql,[('yuanhao','123'),('laowu','123'),('kgf','12323')])conn.commit()print(cursor.lastrowid) #查看表中最后一行的iDcursor.close()conn.close()