資料庫之MySQL高級

来源:https://www.cnblogs.com/yifchan/archive/2019/07/06/sql-1-4.html
-Advertisement-
Play Games

python用於操作數據的pymysql模塊，及相關的視圖，觸發器，函數，存儲過程，事務，SQL註入等相關拓展。 ...


內容來自本人以前在github搭建的博客寫的：MySQL高級之視圖事務函數觸發器

pymysql安裝

pymysql是python用於連接並操作資料庫的一個原生模塊

linux下:

pip3 install pymysql

 

sql註入

1.簡單模擬登錄

先來看一個簡單例子，代碼如下：

import pymysql

user = input("username:")
pwd = input("password:")

# 與資料庫建立連接
conn = pymysql.connect(host="localhost", user="root", password="root", database="test")
# 設置游標
cursor = conn.cursor()
# 將要執行的sql語句
sql = "select * from userinfo where username = '%s' and password = '%s'" % (user, pwd)
# 執行語句
cursor.execute(sql)
# 用fetchone獲取查詢結果
result = cursor.fetchone()
# 關閉連接
cursor.close()
conn.close()

if result:
    print("登錄成功")
else:
    print("用戶名或密碼錯誤")

2.sql註入漏洞

註意，上面的代碼雖然可正確執行，但是是存在漏洞的。

如圖，漏洞為即使你隨便輸入一個賬戶，按照”xxx’ or 1=1 – “的格式，不輸入密碼也能登錄進去，這種漏洞我們稱呼為”sql註入”；問題主要存在於sql語句的寫法。

3.sql註入原理

如圖

4.避免sql註入

為了避免sql註入，我們不要自己做拼接，用pymysql自帶的execute後面傳參數的方式,有如下三種方法:

sql = "select * from userinfo where username = %s and password = %s"
cursor.execute(sql, (user, pwd))

或者：

sql = "select * from userinfo where username = %s and password = %s"
cursor.execute(sql, [user, pwd])

或者：

sql = "select * from userinfo where username = %(u)s and password = %(p)s"
cursor.execute(sql, {'u': user, 'p': pwd})

順利解決sql註入問題，如圖

 

5.模擬登陸代碼

import pymysql

name = input("username:")
pwd = input("password:")

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "select * from userinfo where name=%s and password=%s"
r = cursor.execute(sql, (name, pwd))
# 方法二
# sql = "select * from userinfo where name=%s and password=%s"
# r = cursor.execute(sql, [name, pwd])
# 方法三
# sql = "select * from userinfo where name=%(u)s and password=%(p)s"
# r = cursor.execute(sql, {'u': name, 'p': pwd})
result = cursor.fetchone()

cursor.close()
conn.close()

if result:
print("登陸成功")
else:
print("賬戶名或密碼錯誤")

pymysql操作資料庫

1.pymysql增

1.1.插入一行數據

代碼如下：

import pymysql

conn = pymysql.connect(host="localhost", user="root", password="root", database="test")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('那英','naying')"
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

值得註意的是，在增刪改時我們用的是commit進行提交，在查時用的是fetchone進行查找。
當然，我們也可以自定義插入，如下：

sql = "insert into userinfo(username,password) values(%s,%s)"
cursor.execute(sql, [user, pwd])
conn.commit()

 

1.2.插入多行數據

在插入當行數據是，應註意此時我們用的是executemany，而不是execute。

sql = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql, [('張學友', 'zhangxueyou'), ('林俊傑', 'linjunjie')])
conn.commit()

 

1.3.execute的返回值r

execute和executemany都有返回值，用來記錄受影響的行數，如下：

sql = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql, [('張學友', 'zhangxueyou'), ('林俊傑', 'linjunjie')])
print(r)
conn.commit()

結果為2；
不僅僅是增刪改會有受影響的行數，查詢時也會有。
同時，executemany只適用於插入數據，刪和改用execute即可。

2.pymysql查

2.1.fetchone

代碼如下：

result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)

結果：

(1, '劉德華', 'liudehua')
(3, '郭德綱', 'guodegang')
(4, '胡歌', 'huge')

fetchone可以單次查詢，也可以連續單次查詢，連續單次查詢時，會有類似於指針一樣的東西，當你查詢一次後，指針自動跳往下一行數據。

 

2.2.fetchall

fetchall在未指定limit時，預設查詢所有數據
代碼如下：

sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchall()
print(result)

結果：

((1, ‘劉德華’, ‘liudehua’), (3, ‘郭德綱’, ‘guodegang’), (4, ‘胡歌’, ‘huge’), (5, ‘周傑倫’, ‘zhoujielun’), (7, ‘那英’, ‘naying’), (8, ‘王菲’, ‘wangfei’), (11, ‘張學友’, ‘zhangxueyou’), (14, ‘林俊傑’, ‘linjunjie’))

 

2.3.fetchmany

fetchmany(n)查詢指定前n條數據
代碼如下：

sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchmany(3)
print(result)

結果：

((1, ‘劉德華’, ‘liudehua’), (3, ‘郭德綱’, ‘guodegang’), (4, ‘胡歌’, ‘huge’))

 

2.4.fetch小結

  • 1.fetchone查詢單次數據，fetchall查詢所有數據，fetchmany(n)查詢指定n詞數據。
  • 2.如果想要實現分頁的話，要先在查詢語句中limit 10數據，再用fetchall查詢指定的10條數據；而不能先fetchall所有數據，再一部分一部分的顯示。
  • 3.相對來說，用的最多的是fetchone和fetchall

註：在fetch數據時按照順序進行，可以使用cursor.scroll(num,mode)來移動游標位置，如：

  • cursor.scroll(1,mode=’relative’) # 相對當前位置移動
  • cursor.scroll(2,mode=’absolute’) # 相對絕對位置移動

2.5.查詢數據字典形式顯示

為了方便查看，我們會將數據以字典格式顯示key和value
代碼如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchmany(3)
print(result)

結果：

[{‘id’: 1, ‘username’: ‘劉德華’, ‘password’: ‘liudehua’}, {‘id’: 3, ‘username’: ‘郭德綱’, ‘password’: ‘guodegang’}, {‘id’: 4, ‘username’: ‘胡歌’, ‘password’: ‘huge’}]

 

2.6.新增數據的自增id

獲取新增數據的自增id:cursor.lastrowid,直接用
如果插入的為多行數據，則顯示的為最後一個自增的id

print(cursor.lastrowid)

3.pymysql刪改

增和查的註意點可能多一點，而pymysql刪與改的操作重點在於sql語句，其他只需要連接，執行，關閉連接即可。

3.1.改

代碼如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "update userinfo set name='李玉剛' where id=1"
cursor.execute(sql)
conn.commit()

cursor.close()
conn.close()

4.pymysql刪

4.1.刪

代碼如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "delete from userinfo where id=1"
cursor.execute(sql)
conn.commit()

cursor.close()

 

視圖

為某個查詢語句設置別名，方便使用，即為視圖；

1.創建視圖

創建語句：

create view viewname as SQL

示例：

CREATE view v1 as SELECT * FROM userinfo WHERE id>5;
CREATE view v1 as SELECT id,name FROM userinfo WHERE id>5;

2.修改視圖

修改語句：alter view viewname as SQL
eg：

alter view v1 as SELECT id,NAME from userinfo WHERE id >5;

3.刪除視圖

刪除語句：drop view viewname;
eg：

drop view v1;

4.查看視圖

查看語句：select from viewname;
eg:

select from v1;
select id,name from v1;

5.視圖小結

給一個臨時表設置別名的過程就叫做創建視圖，別名就是視圖的名稱；實際存在的表比如userinfo就是物理表，而一個視圖就是虛擬表，虛擬表動態的從物理表中取數據，所以在物理表中插入數據後虛擬表也可能會隨之改變，但不能向虛擬表中插入數據；創建，修改，刪除視圖。

 

觸發器

對某個表進行【增/刪/改】操作的前後觸發一些操作即為觸發器，如果希望觸發增刪改的行為之前或之後做操作時，可以使用觸發器，觸發器用於自定義用戶對錶的行進行【增/刪/改】前後的行為。

1.創建觸發器

delimiter //   # 修改終止符
CREATE TRIGGER tri_before_insert_userinfo BEFORE INSERT on userinfo for EACH ROW
BEGIN
insert into userinfolog(type,newname) VALUES('insert','姓名');
END//
delimiter ;    # 結束後將終止符修改回來

2.多次觸動觸發器

insert into userinfo(name,password) values('石超','shichao'),('林殊','linshu');

因為each row的存在，在一次SQL中插入兩行數據，會觸發兩次觸發器，即向userinfo插入數據前，會分別兩次向user插入數據。

3.自定義觸發器新插入數據

在前面我們寫的觸發器里，只能插入固定的數據，那麼我們可不可以在user中插入即將插入userinfo的數據呢？答案是可以的，如下：

delimiter //
create trigger tri_after_delete_userinfo after delete on userinfo for each row
begin
insert into userinfolog(type,oldname) values(‘delete’,OLD.name);
end //
delimiter ;

delimiter //
create trigger tri_after_update_userinfo after update on userinfo for each row
begin
insert into userinfolog(type,oldname,newname) values('update',OLD.name,NEW.name);
end //
delimiter ;

註意：觸發器無法被修改，如果想要修改的話，可以先drop，再重新創建

delimiter //
create trigger tri_after_update_userinfo after insert on userinfo for each row
begin
if NEW.operate = 'insert' then
    insert into userinfolog(type,newname) values('INSERT',NEW.name);
elseif NEW.operate = 'delete' then
    insert into userinfolog(type,newname) values('delete',OLD.name);
else
    insert into userinfolog(type,oldname,newname) values('update',OLD.name);
end if;
end //
delimiter ;

 

函數

1、內置函數

MySQL中提供了許多內置函數，例如：
CURDATE(),可以查看當前時間；
使用：

select CURDATE()

查看更多

2、自定義函數

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

3、刪除函數

drop function func_name();

4、執行函數

# 獲取返回值
set @i1=1;
set @i2=2;
select f1(@i1,@i2) into @j;
SELECT @j;

select f1(11,nid) ,name from tb2; # 在查詢中使用,對列的值函數計算後返回。

5、查看函數

show function status;

6、查看函數構建語句

show create function func_name\G

 

存儲過程

存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行。

1、創建存儲過程

無參數存儲過程

– 創建存儲過程

delimiter //　　#修改結束符號，為//
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

– 執行存儲過程

call p1()

 

對於存儲過程，可以接收參數，其參數有三類：

  • in 僅用於傳入參數用
  • out 僅用於返回值用
  • inout 既可以傳入又可以當作返回值

關於變數設置，對於調用存儲過程或者函數時，外部傳入參數或者獲取參數，需加符號@，例如set @t=1；在本次會話內這些帶@的變數都可以被獲取到。斷開連接後變數失效。

有參數存儲過程

– 創建存儲過程

delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0; 
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;  
    set i3 = i3 + 100;
end\\
delimiter ;

– 執行存儲過程

SET @t2=3;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

delimiter //　 將結束符號;修改為//
DECLARE　　聲明變數。如果沒有DEFAULT子句，初始值為NULL。用於內部變數申明。
SET　　　　  變數賦值。用於內部變數賦值，和傳參數時參數賦值。

2、刪除存儲過程

drop procedure proc_name;

3、執行存儲過程

– 無參數

call proc_name();

– 有參數，全in

call proc_name(1,2);

– 有參數，有in，out，inout

set @t1=3;
call proc_name(1,2,@t1,@t2);


import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root', password='root', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('p1', args=(1, 22, 3, 4)) # 執行存儲過程

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") # 獲取執行完存儲的參數
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

4、查看存儲過程

列出所有的存儲過程

SHOW  PROCEDURE  STATUS;

5、查看存儲過程生成語句

查看存儲過程

SHOW CREATE PROCEDURE 存儲過程名\G

 

事務

事務用於將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可回滾到原來的狀態，從而保證資料庫數據完整性。

定義存儲過程：

delimiter \\
drop PROCEDURE if EXISTS p1;
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception     -- 定義錯誤處理
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; -- 回滾
  END; 

  DECLARE exit handler for sqlwarning     -- 定義告警處理
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 

  START TRANSACTION; -- 開始事務，使下麵的多條SQL語句操作變成原子性操作
        UPDATE tb7 set licnese=(licnese-5) WHERE nid=21; 
        UPDATE tb7 set licnese=(licnese+5) WHERE nid=22; 

  COMMIT; 

  -- SUCCESS 
  set p_return_code = 0; 

END\\
delimiter ;

執行存儲過程：

call p1(@p);
SELECT @p;

 

SQL防註入之動態SQL

在高級語言的DB API不提供防註入的參數化查詢功能時，可以使用這種方法來防止SQL註入。在pymysql中的調用點這裡。

定義存儲過程：

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql (
    in nid1 INT,
    in nid2 INT,
    in callsql VARCHAR(255)
    )
BEGIN
    set @nid1 = nid1;
    set @nid2 = nid2;
    set @callsql = callsql;
        PREPARE myprod FROM @callsql;
--     PREPARE prod FROM 'select * from tb2 where nid>? and  nid<?';    傳入的值為字元串，？為占位符
--     用@nid1，和@nid2填充占位符
        EXECUTE myprod USING @nid1,@nid2;
    DEALLOCATE prepare myprod; 

END\\
delimiter ;

調用存儲過程

set @nid1=12;
set @nid2=15;
set @callsql = 'select * from tb7 where nid>? and nid<?';
CALL proc_sql(@nid1,@nid2,@callsql)

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ps命令入門使用指南
    常用的ps命令，如ps aux，ps lax，ps f，ps -A | grep java，ps aux > ps001.txt， ps aux --sort cmd等 ...
  • Linux系統：centos7下搭建ZooKeeper3.4中間件，常用命令總結
    一、下載解壓 1、Zookeeper簡介 Zookeeper 作為一個分散式的服務框架，主要用來解決分散式集群中應用系統的一致性問題，它能提供基於類似於文件系統的目錄節點樹方式的數據存儲，但是 Zookeeper 並不是用來專門存儲數據的，它的作用主要是用來維護和監控你存儲的數據的狀態變化。通過監控 ...
  • Windows 2003 IIS6.0下配置ASP+MySQL+PHP+ISAPI_Rewrite+Zend+Xcache
    windows 2003,自己買吧...安裝IIS6.0:安裝系統後在"控制面板"->"添加或刪除程式"->"添加/刪除Windows組件"->雙擊"應用程式伺服器"->然後選中"Internet信息服務(IIS)"->確定->安裝完畢...下載PHP:http://cn2.php.net/get/ ...
  • 總結關於CPU的一些基本知識
    關於CPU和程式的執行 CPU是電腦的大腦。 1. 程式的運行過程，實際上是程式涉及到的、未涉及到的一大堆的指令的執行過程 。 當程式要執行的部分被裝載到記憶體後，CPU要從記憶體中取出指令，然後指令解碼(以便知道類型和操作數，簡單的理解為CPU要知道這是什麼指令)，然後執行該指令。再然後取下一個指令 ...
  • IIS下配置Php+Mysql+zend的圖文教程(完整版)
    網上有很多關於PHP在IIS下配置的教程,但都是一些很理性化的東西,我從裡面整理出來這個教程 發出來為了方便參考,,有什麼問題也可以大家一起交流,,如果有什麼不對的地方,請指正.. 下麵的教程都是在windows 2000下實現的,其他系統請自己參照修改, 操作系統目錄:c:\winnt PHP預設 ...
  • Linux(二)各種實用命令
    繼續Linux命令學習，沒有什麼捷徑，每個命令都去敲幾遍就熟悉了，第二篇學習的是一些比較實用類的命令，主要是從開發的角度進行學習，並不深入，話不多說，開始！ 一、系統管理類 1.1 stat --stat 顯示指定文件的相關信息,比ls命令顯示內容更多 1.2 who --顯示線上登錄用戶 1.3 ...
  • SQL Server溫故系列(1)：SQL 數據操作 CRUD 之增刪改合
    《SQL Server溫故系列》，sql，crud。毋庸置疑，開發者最常用的資料庫技術就是 SQL 了，即便是 ORM 大行其道的今天也常常需要寫 SQL 語句。而 SQL 語句中最常用的就是增刪改查了，本系列就先對增刪改查語句來個系統的回顧吧！ ...
  • SQL Server溫故系列(0)：導航目錄
    《SQL Server溫故系列》，sqlserver教程，tsql教程，sql教程，SQL Server 系列博客導航目錄 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類