python-day43_MySQL数据库2
1,navicat
连接MYSQL数据库的客户端工具软件
2,数据库备份
mysqldump -u root -p db1 > db1.sql #备份:数据表结构+数据
-d 表示只备份结构,不备份数据
3,数据库导入
mysqldump -u root -p db1 < db1.sql
4,AS
1)把查询结果+AS+新的临时表,新的临时表用于其它操作
SELECT corse_id FROM
(SELECT corse_id FROM score WHERE student_id =1)) AS A ;
2)把函数结果AS做为一个变量,用于其它操作
SELECT COUNT(1) AS new_count FROM student;
3)在SELECT中把同一个表,进行合并组合后(表行数*表行数),同时输出
SELECT * FROM score AS S1,score AS S2
5,CASE WHEN ... THEN ... ELSE
CASE WHEN number<60 THEN 0 ELSE 1 END
当条件为真,则输出0,否则输出1
6,pymysql模块
pip3 install pymysql -i https://pypi.douban.com/simple
Python模块,对数据库进行操作(SQL语句)
import pymysql user = input("username:") pwd = input("password:") conn = pymysql.connect(host="localhost",user='root',password='',database="db666") cursor = conn.cursor() sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,) # select * from userinfo where username='uu' or 1=1 -- ' and password='%s' cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() if result: print('登录成功') else: print('登录失败')
import pymysql user = input("username:") pwd = input("password:") conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \ charset='utf8') # conn = pymysql.connect(host="localhost",user='root',password='',database="db666") cursor = conn.cursor() sql = "SELECT * FROM userinfo WHERE username=%s AND password=%s" cursor.execute(sql,[user,pwd]) # cursor.execute(sql,{'u':user,'p':pwd}) #实测会报错 # cursor.execute(sql,user,pwd) #实测会报错 result = cursor.fetchone() cursor.close() conn.close() if result: print('登陆成功') else: print('登陆失败')
增,删,改
conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \
charset='utf8')
cursor=conn.cursor()
sql="INSERT INTO userinfo(username,password) VALUES('egon','123')"
ret=cursor.execute(sql) #返回受影响的行数
conn.commit() #提交操作
cursor.close()
conn.close()
查
conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \
charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #函数中参数使结果以list+字典类型返回,默认为元组
sql="SELECT * FROM userinfo"
ret=cursor.execute(sql)
result=cursor.fetchall() #fetchall()一次取到所有记录
cursor.close()
conn.close()
# result = cursor.fetchone() #fetchone()是一次取一行
# print(result)
# cursor.scroll(1,mode='relative') # 相对当前位置移动
# cursor.scroll(2,mode='absolute') # 相对绝对位置移动
练习: 权限管理 权限表: 1 订单管理 2 用户管理 3 菜单管理 4 权限分配 5 Bug管理 用户表: 1 Alex 2 egon 用户权限关系表: 1 1 1 2 2 1 Python实现: 某个用户登录后,查看自己拥有所有权限 CREATE TABLE userinfo ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username char(20) NOT NULL, password CHAR(30) )ENGINE=INNODB DEFAULT CHARSET = utf8; CREATE TABLE privilege( pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, pname char(20) )ENGINE=INNODB DEFAULT CHARSET = utf8; CREATE TABLE user_priv( uid INT NOT NULL PRIMARY KEY, pid INT, UNIQUE uni_uid(uid,pid), CONSTRAINT fk_uid_pid FOREIGN KEY(pid) REFERENCES privilege(pid) )ENGINE=INNODB DEFAULT CHARSET = utf8; INSERT INTO userinfo(username,password) VALUES('alex','123'),('egon','123') INSERT INTO privilege(pname) VALUES('订单管理'),('用户管理'),('菜单管理'),('权限分配'),('Bug管理') INSERT INTO user_priv(uid,pid) VALUES(1,1),(2,1)
import pymysql def show_priv(user): conn = pymysql.connect(host='10.10.66.45', port=3306, user='alex', password='redhat', database='pydb', \ charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "SELECT user_priv.uid,userinfo.username,privilege.pname FROM user_priv \ LEFT JOIN privilege ON user_priv.pid=privilege.pid \ LEFT JOIN userinfo ON user_priv.uid=userinfo.id \ WHERE userinfo.username=%s" cursor.execute(sql, [user]) result = cursor.fetchall() cursor.close() conn.close() return result def manu(user): manu_list=['查看权限','退出'] print("="*20) for index,i in enumerate(manu_list,1): print(index,i) usercli =input("请输入选项:") while usercli!='2': if usercli=='1': result=show_priv(user) #执行SQL print(result) print("="*20) for index, i in enumerate(manu_list, 1): print(index, i) usercli = input("请输入选项:") print("谢谢使用!") user = input("username:") pwd = input("password:") conn = pymysql.connect(host='10.10.66.45',port=3306,user='alex',password='redhat',database='pydb',\ charset='utf8') cursor=conn.cursor() sql = "SELECT * FROM userinfo WHERE username=%s AND password=%s" cursor.execute(sql,[user,pwd]) result = cursor.fetchone() cursor.close() conn.close() if result: print("登陆成功") manu(user) else: print("登陆失败")