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('登入失敗')      

有SQL注入問題的代碼

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("登陸失敗")      

homework

轉載于:https://www.cnblogs.com/yygy/p/11039801.html