第11天，Python操作MySQL

@(python)[筆記]

目錄

一、資料庫操作
    1.1 資料庫管理
        1.1.1 顯示
        1.1.2 使用者管理
        1.1.3 授權管理
    1.2 資料表操作
        1.2.1 建立表
            資料類型
            修飾符
        1.2.2 删除表
        1.2.3 清空表
        1.2.4 修改表
    1.3 表内容操作
        1.3.1 增
        1.3.2 删
        1.3.3 改
        1.3.4 查
        1.3.5 複雜查詢
二、Python操作MySQL
    2.1 下載下傳安裝
    2.2 使用操作
        2.2.1 執行查詢操作
        2.2.2 寫庫操作必須送出commit
        2.2.3 直接擷取新增資料的自增ID
        2.2.4 将查詢結果以字典形式傳回
           

一、資料庫操作

SQL語句分類：

SQL語句分為：
（1） DQL（資料查詢語言）：select
（2） DDL（資料定義語言）：create/drop/alter/truncate
（3） DML（資料操縱語言）：insert/update/delete
 (4)  TCL（事務控制語言）：commit/rollback/savepoint
（5） DCL（資料控制語言）：grant/revoke
           

1.1 資料庫管理

1.1.1 顯示

SHOW DATABASES

//顯示所有資料庫

SHOW TABLES

//顯示所有資料表

USE db_name

//使用某個資料庫

預設的資料庫功能介紹

• mysql - 使用者權限相關資料
• test - 用于使用者測試資料
• information_schema - MySQL本身架構相關資料

1.1.2 使用者管理

建立使用者
    create user '使用者名'@'IP位址' identified by '密碼';
    
删除使用者
    drop user '使用者名'@'IP位址';
    
修改使用者
    rename user '使用者名'@'IP位址'; to '新使用者名'@'IP位址';;
    
修改密碼
    set password for '使用者名'@'IP位址' = Password('新密碼')
  
PS：使用者權限相關資料儲存在mysql資料庫的user表中，是以也可以直接對其進行操作（不建議）
           

1.1.3 授權管理

show grants for '使用者'@'IP位址';            -- 檢視權限
grant  權限 on 資料庫.表 to   '使用者'@'IP位址';      -- 授權
revoke 權限 on 資料庫.表 from '使用者'@'IP位址';      -- 取消權限
           

權限清單

權限名稱	權限功能
all privileges	除grant外的所有權限
select	僅查權限
select,insert	查和插入權限
usage	無通路權限
alter	使用alter table
alter routine	使用alter procedure和drop procedure
create	使用create table
create routine	使用create procedure
create temporary tables	使用create temporary tables
create user	使用create user、drop user、rename user和revoke all privileges
create view	使用create view
delete	使用delete
drop	使用drop table
execute	使用call和存儲過程
file	使用select into outfile 和 load data infile
grant option	使用grant 和 revoke
index	使用index
insert	使用insert
lock tables	使用lock table
process	使用show full processlist
	使用select
show databases	使用show databases
show view	使用show view
update	使用update
reload	使用flush
shutdown	使用mysqladmin shutdown(關閉MySQL)
super	<dbff><dc42><dbff><dc08>使用change master、kill、logs、purge、master和set global。還>允許mysqladmin<dbff><dd57><dbff><dd58><dbff><dc8a><dbff><dc8b>調試登陸
replication client	伺服器位置的通路
replication slave	由複制從屬使用

對于資料庫

對于目标資料庫以及内部其他：

資料庫表示	說明
資料庫名.*	資料庫中的所有
資料庫名.表	指定資料庫中的某張表
資料庫名.存儲過程	指定資料庫中的存儲過程
*.*	所有資料庫

對于使用者和IP位址

表示方法
使用者名@IP位址	使用者隻能在改IP下才能通路
使用者名@192.168.1.%	使用者隻能在改IP段下才能通路(通配符%表示任意)
使用者名@%	使用者可以再任意IP下通路(預設IP位址為%)

重新整理權限清單

flush privileges

//将資料讀取到記憶體中，進而立即生效

root忘記密碼解決辦法

# 啟動免授權服務端
mysqld --skip-grant-tables

# 用戶端
mysql -u root -p

# 修改使用者名密碼
update mysql.user set authentication_string=password('666') where user='root';

#重新整理權限清單
flush privileges;
           

1.2 資料表操作

1.2.1 建立表

參考部落格1 參考部落格2

create table 表名(
    列名  類型  是否可以為空，
    列名  類型  是否可以為空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
           

資料類型

1. 數值類型

數值類型	位元組	範圍（有符号）	範圍（無符号）	用途
TINYINT 整數	1位元組	(-128，127)	(0，255)	小整數值
SMALLINT 整數	2位元組	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT 整數	3位元組	(-8 388 608，8 388 607)	(0，16 777 215)
INT或INTEGER 整數	4位元組	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT 整數	8位元組	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT 浮點數		(-3.402 823 466 E+38，1.175 494 351 E-38)	(0，3.402 823 466 351 E+38)	單精度浮點數值
DOUBLE 浮點數		(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點數值
DECIMAL 浮點數	未知			絕對精度

2. 字元串類型

字元串類型	位元組大小	描述及存儲需求
CHAR	0-255位元組	定長字元串
VARCHAR		變長字元串
TINYBLOB		不超過 255 個字元的二進制字元串
TINYTEXT		短文本字元串
BLOB	0-65535位元組	二進制形式的長文本資料
TEXT		長文本資料
MEDIUMBLOB	0-16 777 215位元組	二進制形式的中等長度文本資料
MEDIUMTEXT		中等長度文本資料
LOGNGBLOB	0-4 294 967 295位元組	二進制形式的極大文本資料
LONGTEXT		極大文本資料
VARBINARY(M)	允許長度0-M個位元組的定長位元組符串，值的長度+1個位元組
BINARY(M)	允許長度0-M個位元組的定長位元組符串

3. 日期和時間類型

類型	大小（位元組）	範圍	格式
DATE	4	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時間值或持續時間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP		1970-01-01 00:00:00/2037 年某時	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

4. ENUM枚舉類型

ENUM 類型因為隻允許在集合中取得一個值，有點類似于單選項。在處理互相排拆的資料時容易讓人了解，比如人類的性别。ENUM 類型字段可以從集合中取得一個值或使用 null 值。

5. SET 集合類型

SET 類型與 ENUM 類型相似但不相同。SET 類型可以從預定義的集合中取得任意數量的值。并且與 ENUM 類型相同的是任何試圖在 SET 類型字段中插入非預定義的值都會使

MySQL 插入一個空字元串。如果插入一個即有合法的元素又有非法的元素的記錄，MySQL 将會保留合法的元素，除去非法的元素。

修飾符

• NOT NULL / NULL 表示是否可為空
• **DEFAULT Null ** 表示預設值為Null
• auto_increment ** 表示自增，注意：**1.每個表隻允許有一列為自增列；2. 對于自增列，必須是索引（含主鍵）；3. 對于自增，可以設定步長和起始值。
• primary key 表示設為主鍵，注意：主鍵，是一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須是唯一。
• 設定外鍵：constraint fk_name foreign key 字段名 references 外表名（字段名），加粗字型為關鍵字，fk_name為自定義的外鍵名。
• UNSIGNED 修飾符規定字段隻儲存正值。

1.2.2 删除表

drop table 表名
           

1.2.3 清空表

delete from 表名       //不會清空自增ID
truncate table 表名    //會将自增ID也清空
           

1.2.4 修改表

添加列：alter table 表名 add 列名 類型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 類型;  -- 類型
        alter table 表名 change 原列名 新列名 類型; -- 列名，類型
  
添加主鍵：
        alter table 表名 add primary key(列名);
删除主鍵：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
  
添加外鍵：alter table 從表 add constraint 外鍵名稱（形如：FK_從表_主表） foreign key 從表(外鍵字段) references 主表(主鍵字段);
删除外鍵：alter table 表名 drop foreign key 外鍵名稱
  
修改預設值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除預設值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
           

1.3 表内容操作

1.3.1 增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)   //一次插入多行
insert into 表 (列名,列名...) select (列名,列名...) from 表    //插入的值是從其它表查詢到的結果
           

1.3.2 删

delete from 表     //清空表内容
delete from 表 where id＝1 and name＝'alex'     //根據指定條件删除對應的行
           

1.3.3 改

update 表 set name ＝ 'alex' where id>1
           

1.3.4 查

查詢文法：

SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 條件
                  GROUP BY field
                  HAVING 篩選
                  ORDER BY field
                  LIMIT 限制條數
           

Mysql在執行sql語句時的執行順序：

-- from  where  select  group by  having order by
           

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1
select distinct gender from student;  //distinct 可以讓相同的值隻出現一次
           

1.3.5 複雜查詢

a、條件

select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)

           

b、通配符

select * from 表 where name like 'ale%'  - ale開頭的所有（多個字元串）
    select * from 表 where name like 'ale_'  - ale開頭的所有（一個字元）

           

c、限制

select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 從第4行開始的5行
    select * from 表 limit 5 offset 4    - 從第4行開始的5行
           

注意：跟NULL作比較時，不能使用=等于号，要用 is

d、排序

select * from 表 order by 列 asc              - 根據 “列” 從小到大排列
    select * from 表 order by 列 desc             - 根據 “列” 從大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根據 “列1” 從大到小排列，如果相同則按列2從小到大排序
 
           

e、分組

select num from 表 group by num
    
    select num,nid from 表 group by num,nid
    
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10

計算男生和女生的平均年齡：
select avg(age) from student group by gender;
    
           

注意：特别的：group by 必須在where之後，order by之前。having 後跟過濾條件，隻能跟group by 一起用，用于将group by分組後的結果再次過濾；分組的主要目的就是做聚合計算的。

• sum() 求和
• min() 求最小值
• max() 求最大值
• avg() 求平均值
• count() 求次數
• count()* 統計符合條件的記錄條數

聚合函數的括号中填一個列名，例如：sum(number)表示計算number這一列數值的和。

f、連表

無對應關系則不顯示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    無對應關系則不顯示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有顯示，如果B中無對應關系，則值為null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有顯示，如果B中無對應關系，則值為null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid

           

g、組合

組合，自動處理重合
    select nickname
    from A
    union
    select name
    from B
 
    組合，不處理重合
    select nickname
    from A
    union all
    select name
    from B

           

h、正規表達式過濾

REGEXP或者RLIKE  
    支援正規表達式過濾
    select * from 表 where name like '^ale'    //查找name是以ale開頭的行
           

二、Python操作MySQL

在Python中操作MySQL用

pymysql

子產品，其使用方法和

MySQLdb

幾乎相同。

2.1 下載下傳安裝

pip3 install pymysql
           

2.2 使用操作

2.2.1 執行查詢操作

import pymysql

# 建立連接配接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
# 建立遊标，利用遊标支資料庫取資料
cursor = conn.cursor()

#執行SQL，傳回值v是受影響的行數
v = cursor.execute('select * from student')
result = cursor.fetchall()   #擷取傳回的全部資料，元組形式
# result = cursor.fetchone()   #擷取傳回的第1條資料
# result = cursor.fetchmany(2)   #擷取傳回的指定行數的資料，不指定行數，預設隻擷取一行資料，傳回元組形式
print(result)

#關閉遊标
cursor.close()
#關閉連接配接
conn.close()
           

注意：

fetchone()

有一個資料指針，擷取一行，就會跳到下一行，不能傳回。

執行個體：讀取資料庫，驗證使用者登入

#通過讀資料庫驗證使用者登入
import pymysql

username = input("請輸入使用者名：")
password = input("請輸入密碼：")
try:
    # 建立連接配接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
    # 建立遊标，利用遊标支資料庫取資料
    cursor = conn.cursor()

    sql = 'select * from userinfo where username="%s" and password="%s" '%(username,password)
    cursor.execute(sql)
    result = cursor.fetchall()   #擷取傳回的全部資料，元組形式
    if result:
        print("登入成功")
    else:
        print("賬号或密碼錯誤")
except Exception as e:
    print(e)
    raise
finally:
    cursor.close()
    conn.close()
           

注意：以上代碼有SQL注入的漏洞，請看如下示範

示範一：輸入正确使用者名和密碼

示範一

示範二: 輸入使用者名：alex" -- , 輸入任意密碼

示範二

解析：由上圖可以看出，使用者名和密碼都是錯誤的，也可以通過驗證。這是為什麼呢？我們來解析一下通過使用者名和密碼生成的SQL。通過字元串替換，我們可以得到這樣一條SQL：

select * from userinfo where username="alex" -- " and password="sdfsfsd"

。從這條SQL可以看出，

username="alex"

後面被

--

注釋了（

--

是SQL語言中的注釋符），後面的語句就不能被mysql解析了，是以不管密碼輸入的是什麼，都會被忽略掉。

示範三：輸入使用者名：xxx" or 1=1 -- , 輸入任意密碼

示範三

解析：由上圖可以看出，即使不知道資料庫中的使用者名和密碼是什麼，也可以通過驗證。通過字元串替換，我們得到這樣的一條SQL：

select * from userinfo where username="xxx" or 1=1 -- " and password="asdffas"

。這條SQL，

1=1

後面被注釋了，而

select * from userinfo where username="xxx" or 1=1

是永遠成立的，可以從資料庫擷取到值，是以可以通過驗證。

改進版《讀取資料庫，驗證使用者登入》

防止被SQL注入

我們在輸入SQL字元串時，不要自己去做字元串格式化替換，可以在給

execute

方法傳參時，第一個參數傳查詢的SQL，第二個參數傳SQL中要替換的字元串清單。pymysql子產品内部會自動替換，在替換時，會将特殊字元替換掉。

#改進版

import pymysql

username = input("請輸入使用者名：")
password = input("請輸入密碼：")

try:
    # 建立連接配接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
    # 建立遊标，利用遊标支資料庫取資料
    cursor = conn.cursor()

    sql = 'select * from userinfo where username=%s and password=%s '
    cursor.execute(sql,[username,password])   #第2個參數為清單
    result = cursor.fetchall()   #擷取傳回的全部資料，元組形式
    if result:
        print("登入成功")
    else:
        print("賬号或密碼錯誤")
except Exception as e:
    print(e)
    raise
finally:
    cursor.close()
    conn.close()
           

示範四：

示範四

可以看出，代碼改進後，就不會再出現被SQL注入了。

2.2.2 寫庫操作必須送出commit

對資料庫進行增、删、改操作都必須執行送出（commit）操作

import pymysql

# 建立連接配接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")

cursor = conn.cursor()

#執行SQL，傳回值v是受影響的行數
sql = 'insert into userinfo(username,password) values(%s,%s)'
cursor.execute(sql,["eric","123321"])

#對資料庫進行增、删、改操作都必須執行送出（commit）操作
conn.commit()
cursor.close()
conn.close()
           

2.2.3 直接擷取新增資料的自增ID

cursor.lastrowid

方法可以直接擷取新增資料的自增ID

需求：現在我想往class班級表中新插入一個班級，然後再往這個新班級中插入一條學生資料（包含班級ID）。

import pymysql

# 建立連接配接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")

cursor = conn.cursor()
cursor.execute('insert into class(caption) values("三年三班")')

new_class_id = cursor.lastrowid  #擷取新增資料的自增ID
cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)',["李傑","女",new_class_id])

#對資料庫進行增、删、改操作都必須執行送出（commit）操作
conn.commit()
cursor.close()
conn.close()
           

2.2.4 将查詢結果以字典形式傳回

需要在

conn.cursor()

傳一個參數：

conn.cursor(cursor=pymysql.cursors.DictCursor)

import pymysql
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("select * from class")
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()

'''
輸出結果：
[{'cid': 1, 'caption': '三年二班'}, {'cid': 2, 'caption': '一年三班'}, {'cid': 3, 'caption': '三年一班'}, {'cid'
'''