連表操作、子查詢、pymysql 模組、sql注入問題（感覺已沒有）

认真的六六發表於2024-05-19
MySql

【連表操作】

 1 建表
 2 create table dep1(
 3     id int,
 4     name varchar(20)
 5     );
 6 create table emp1(
 7     id int primary key auto_increment,
 8     name varchar(20),
 9     sex enum('male','female') not null default 'male',
10     age int,
11     dep_id int
12     );
13 
14 
15 插入資料
16     insert into dep1 values
17     (200,'技術'),
18     (201,'人力資源'),
19     (202,'銷售'),
20     (203,'運營');
21 
22 insert into emp1(name,sex,age,dep_id) values
23 ('jason','male',18,200),
24 ('egon','female',48,201),
25 ('kevin','male',18,201),
26 ('nick','male',28,202),
27 ('owen','male',18,203),
28 ('jerry','female',18,204);
29 
30 select * from dep1;
31 select * from emp1;
32 
33 ----------------------------------------------操作
34 1.查詢jason這個人的部門名稱
35     拼表操作：
36     select * from dep1,emp1;     將兩張表拼成一張表，結果叫：笛卡爾積
37     或是
38     select * from emp1,dep1 where emp1.dep_id=dep1.id;
39 
40 MySQL在後面查詢資料的過程中，肯定會經常用到拼表操作
41 所以特地開始了對應的方法：
42 *********************************重要
43     inner join: 內連線
44     left join:  左連線
45     right join: 右連線
46     union:      全連線
47 
48 
49 使用場景
50 inner join：內連線             on依據
51     select * from emp1 inner join dep1 on dep1.id=emp1.dep_id;
52     但這時候發現，有資料丟失，原因是，內連線，只拼接兩張表共有的資料部分
53 
54 left join：左連線
55     select * from emp1 left join dep1 on dep1.id=emp1.dep_id;
56     左邊的表所有的資料都展示出來，沒有對應的項就用null填充
57 
58 right join：右連線
59     select * from emp1 right join dep1 on dep1.id=emp1.dep_id;
60     右邊的表所有的資料都展示出來，沒有對應的項就用null填充,(但是我的顯示資料丟失)
61 
62 union:       全連線，左右兩個表所有的資料都展示出來
63     select * from emp1 left join dep1 on dep1.id=emp1.dep_id
64     union
65     select * from emp1 right join dep1 on dep1.id=emp1.dep_id;

。

。

【子查詢】

子查詢就是我們平時解決問題的思路
    分步驟解決問題
    第一步........
    第二步........
將一個查詢語句的結果當作另外一個查詢語句的條件取使用

1.查詢部門是技術部門或者是人力欄位的員工資訊
    1）.獲取部門的id號
    2）.再去員工表裡面篩選出對應的員工
    select id from dep1 where name='技術' or name='人力資源';
    select name from emp1 where dep1_id in (200,201);

精簡寫法：
    select * from emp1 where dep_id in (select id from dep1 where  name='技術' or name='人力資源');

總結：
    表的查詢結果可以作為其他表的查詢條件，也可以透過起別名的方式把它當作一個虛擬表根其他表關聯
    多表查詢就兩種方式：1.先拼接表再查詢 2.子查詢一步一步來


------------------------------------------------------
練習
查詢平均年齡在25歲以上的部門名稱
只要涉及到多表查詢，就有兩種思路：
    方式一：連表操作
        先拿到部門和員工表，拼接之後的結果
        得出需要進行分組
        select dep1.name from emp1 inner join dep1 on emp1.dep_id=dep1.id
        group by dep1.name
        having avg(age)>25;

    2.子查詢
        select name from dep1 where id in (
            select dep_id from emp1 group by dep_id
            having avg(age)>25
        );

    3.關鍵字 exists
    只返回T or N
    返回T的時候外層查詢語句執行
    返回F的時候外層查詢語句不執行
        select * from emp1 where exists (
        select * from dep1 where id>3);

        select * from emp1 where exists (
        select * from dep1 where id>300);  (後面不執行)

。

。

【python運算元據庫pymysql】

基本使用

安裝
    pip3 install pymysql

建立檔名不要和模組名一樣

import pymysql

# 連線資料庫
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',  # 不要加 -
    port=3306,
    database='day4')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 產生一個遊標物件,來幫我們執行命令的
# cursor=pymysql.cursor.DictCursor # 查詢結果以字典的形式返回

sql = 'select * from teacher'
res = cursor.execute(sql)
# print(res)  # execute返回的是當前sql語句所影響的行數，該返回值我們一般不用
# 獲取命令執行的查詢結果
print(cursor.fetchone())  # 只拿一條，並列印第一個元素{'id': 100, 'cl_name': '高三1班 '}
print(cursor.fetchmany(2))  # 可以指定拿幾條
print(cursor.fetchall())  # 讀取所有的資料
cursor.scroll(1, 'relative')  # 相對於游標所在的位置繼續往後移動1位
cursor.scroll(1, 'absolute')  # 相對於資料的開頭位置繼續往後移動1位
conn.close()  # 關閉連線

===================================

 1 pymysql運算元據庫
 2 
 3 增刪改查
 4 
 5 #  1. pymysql運算元據庫的增刪改查
 6 import pymysql
 7 
 8 conn = pymysql.connect(
 9     host='127.0.0.1',
10     user='root',
11     password='199721',
12     charset='utf8',
13     port=3306,
14     database='day4')
15 
16 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 產生一個遊標物件,來幫我們執行命令的
17 
18 # 增
19 sql = 'insert into user(name,password) values(%s,%s)'
20 rows = cursor.execute(sql, ('jackson', 1234))  # 放的是元組
21 # rows=cursor.executemany(sql,[('jason',123456),('jason2',123456),('jason3',123456)])  # 列表套元組
22 print(rows)  # 到這裡其實沒有增加
23 conn.commit()  # 我們需要確認
24 
25 
26 # 修改
27 sql = 'update user set name="jasonNB" where id=1'
28 rows = cursor.execute(sql)
29 print(rows)  # 在表中還是沒有修改
30 conn.commit()  # 確認
31 
32 # 刪
33 sql = 'delete from user where id=1'
34 rows = cursor.execute(sql)
35 print(rows)  # 也沒有用
36 conn.commit()  # 確認
37 
38 
39 # 查
40 sql = 'select * from user'
41 rows = cursor.execute(sql)
42 print(rows)  # 有
43 
44 # 增刪改查中，查的結果是返回一個數字，表示影響的行數，其他操作返回的數字表示操作的行數
45 # 需要二次確認，才能增刪改

------------------------------------------------------------------------------

（在pymysql模組中如何呼叫儲存過程）

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host='127.0.0.1',
 5     user='root',
 6     password='199721',
 7     charset='utf8',
 8     port=3306,
 9     database='day4')
10 
11 cursor = conn.cursor(pymysql.cursors.DictCursor)
12 # # 呼叫儲存過程
13 # cursor.callproc('proc_test', [1, 2])
14 # print(cursor.fetchall())
15 cursor.execute('select * from user')
16 print(cursor.fetchall())

【sql注入問題及解決辦法】

SQL語言主要用於存取資料、查詢資料、更新資料和管理關聯式資料庫系統,SQL語言由IBM開發。

SQL語言分為3種型別：

　　1、DDL語句 資料庫定義語言： 資料庫、表、檢視、索引、儲存過程，例如CREATE DROP ALTER

　　2、DML語句 資料庫操縱語言： 插入資料INSERT、刪除資料DELETE、更新資料UPDATE、查詢資料SELECT

　　3、DCL語句 資料庫控制語言： 例如控制使用者的訪問許可權GRANT、REVOKE

sql注入: 利用現有的程式，將惡意的sql命令注入到後臺的資料庫引擎執行的能力 像符號--會註釋掉之後的sql，根本 原理就是字串的拼接name=‘%s' 原生sql中讓execute幫做拼接就可以，django的orm種就不會存在這個問題

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host='127.0.0.1',
 5     user='root',
 6     password='199721',
 7     charset='utf8',
 8     port=3306,
 9     database='day4')
10 
11 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 產生一個遊標物件,來幫我們執行命令的
12 username = input('請輸入使用者名稱:')
13 password = input('請輸入密碼:')
14 sql = "select* from user where name='%s' and password='%s'"%(username,password)
15 rows = cursor.execute(sql)
16 if rows:
17     print('登入成功')
18     print(cursor.fetchall())
19 else:
20     print('使用者名稱或密碼錯誤')
21 
22 ---------------------------------------------
23 上述輸入密碼賬號看似沒有問題，但是當我們不輸入密碼的時候，提示我們也能登入成功，其實這就是sql注入問題
24 解決辦法
25 
26 sql = "select * from user where name=%s and password=%s"
27 # 不要手動拼接資料，先用%s佔位，然後將需要拼接的資料直接交給execute方法即可
28 rows = cursor.execute(sql, (username, password))
29 # 自動識別sql裡面的%s，然後將資料替換進去
30 if rows:
31     print('登入成功')
32 else:
33     print('使用者名稱或密碼錯誤')
34 
35 ----------------------------------------------
36 PS：2024.5.19我重新測試了sql注入問題，發現這個漏洞已經解決了，不知道是我測試方法不對還是什麼

。

。

。

【connect中一些引數的意思】

 1 conn = pymysql.connect(
 2     host='127.0.0.1',
 3     user='root',   %
 4     password='199721',
 5     charset='utf8',
 6     port=3306,
 7     database='day4')
 8 
 9 host：代表地址，
10 user：登入mysql 的使用者名稱，
11 password：登入mysql 的密碼，
12 charser：代表編碼格式，
13 port：埠號，常規的很多都是3306也有自定義，
14 database：資料庫名稱

相關文章