@(python)[筆記]

目錄

一、數(shù)據(jù)庫操作
    1.1 數(shù)據(jù)庫管理
        1.1.1 顯示
        1.1.2 用戶管理
        1.1.3 授權(quán)管理
    1.2 數(shù)據(jù)表操作
        1.2.1 創(chuàng)建表
            數(shù)據(jù)類型
            修飾符
        1.2.2 刪除表
        1.2.3 清空表
        1.2.4 修改表
    1.3 表內(nèi)容操作
        1.3.1 增
        1.3.2 刪
        1.3.3 改
        1.3.4 查
        1.3.5 復(fù)雜查詢
二、Python操作MySQL
    2.1 下載安裝
    2.2 使用操作
        2.2.1 執(zhí)行查詢操作
        2.2.2 寫庫操作必須提交commit
        2.2.3 直接獲取新增數(shù)據(jù)的自增ID
        2.2.4 將查詢結(jié)果以字典形式返回

一、數(shù)據(jù)庫操作

SQL語句分類：

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

1.1 數(shù)據(jù)庫管理

1.1.1 顯示

SHOW DATABASES //顯示所有數(shù)據(jù)庫
SHOW TABLES //顯示所有數(shù)據(jù)表
USE db_name //使用某個數(shù)據(jù)庫

默認的數(shù)據(jù)庫功能介紹

• mysql - 用戶權(quán)限相關(guān)數(shù)據(jù)
• test - 用于用戶測試數(shù)據(jù)
• information_schema - MySQL本身架構(gòu)相關(guān)數(shù)據(jù)

1.1.2 用戶管理

創(chuàng)建用戶
    create user '用戶名'@'IP地址' identified by '密碼';
    
刪除用戶
    drop user '用戶名'@'IP地址';
    
修改用戶
    rename user '用戶名'@'IP地址'; to '新用戶名'@'IP地址';;
    
修改密碼
    set password for '用戶名'@'IP地址' = Password('新密碼')
  
PS：用戶權(quán)限相關(guān)數(shù)據(jù)保存在mysql數(shù)據(jù)庫的user表中，所以也可以直接對其進行操作（不建議）

1.1.3 授權(quán)管理

show grants for '用戶'@'IP地址';            -- 查看權(quán)限
grant  權(quán)限 on 數(shù)據(jù)庫.表 to   '用戶'@'IP地址';      -- 授權(quán)
revoke 權(quán)限 on 數(shù)據(jù)庫.表 from '用戶'@'IP地址';      -- 取消權(quán)限

權(quán)限列表

對于數(shù)據(jù)庫

對于目標數(shù)據(jù)庫以及內(nèi)部其他：

對于用戶和IP地址

刷新權(quán)限列表

flush privileges //將數(shù)據(jù)讀取到內(nèi)存中，從而立即生效

root忘記密碼解決辦法

# 啟動免授權(quán)服務(wù)端
mysqld --skip-grant-tables

# 客戶端
mysql -u root -p

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

#刷新權(quán)限列表
flush privileges;

1.2 數(shù)據(jù)表操作

1.2.1 創(chuàng)建表

參考博客1

參考博客2

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

數(shù)據(jù)類型

1. 數(shù)值類型

2. 字符串類型

3. 日期和時間類型

4. ENUM枚舉類型

ENUM 類型因為只允許在集合中取得一個值，有點類似于單選項。在處理相互排拆的數(shù)據(jù)時容易讓人理解，比如人類的性別。ENUM 類型字段可以從集合中取得一個值或使用 null 值。

5. SET 集合類型

SET 類型與 ENUM 類型相似但不相同。SET 類型可以從預(yù)定義的集合中取得任意數(shù)量的值。并且與 ENUM 類型相同的是任何試圖在 SET 類型字段中插入非預(yù)定義的值都會使
MySQL 插入一個空字符串。如果插入一個即有合法的元素又有非法的元素的記錄，MySQL 將會保留合法的元素，除去非法的元素。

修飾符

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

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 表內(nèi)容操作

1.3.1 增

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

1.3.2 刪

delete from 表     //清空表內(nèi)容
delete from 表 where id＝1 and name＝'alex'     //根據(jù)指定條件刪除對應(yīng)的行

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 限制條數(shù)

Mysql在執(zhí)行sql語句時的執(zhí)行順序：

            -- 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 可以讓相同的值只出現(xiàn)一次

1.3.5 復(fù)雜查詢

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              - 根據(jù) “列” 從小到大排列
    select * from 表 order by 列 desc             - 根據(jù) “列” 從大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根據(jù) “列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分組后的結(jié)果再次過濾；分組的主要目的就是做聚合計算的。

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

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

f、連表

    無對應(yīng)關(guān)系則不顯示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    無對應(yīng)關(guān)系則不顯示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有顯示，如果B中無對應(yīng)關(guān)系，則值為null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有顯示，如果B中無對應(yīng)關(guān)系，則值為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 執(zhí)行查詢操作

import pymysql

# 創(chuàng)建連接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
# 創(chuàng)建游標，利用游標支數(shù)據(jù)庫取數(shù)據(jù)
cursor = conn.cursor()

#執(zhí)行SQL，返回值v是受影響的行數(shù)
v = cursor.execute('select * from student')
result = cursor.fetchall()   #獲取返回的全部數(shù)據(jù)，元組形式
# result = cursor.fetchone()   #獲取返回的第1條數(shù)據(jù)
# result = cursor.fetchmany(2)   #獲取返回的指定行數(shù)的數(shù)據(jù)，不指定行數(shù)，默認只獲取一行數(shù)據(jù)，返回元組形式
print(result)

#關(guān)閉游標
cursor.close()
#關(guān)閉連接
conn.close()

注意：fetchone()有一個數(shù)據(jù)指針，獲取一行，就會跳到下一行，不能返回。

實例：讀取數(shù)據(jù)庫，驗證用戶登錄

#通過讀數(shù)據(jù)庫驗證用戶登錄
import pymysql

username = input("請輸入用戶名：")
password = input("請輸入密碼：")
try:
    # 創(chuàng)建連接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
    # 創(chuàng)建游標，利用游標支數(shù)據(jù)庫取數(shù)據(jù)
    cursor = conn.cursor()

    sql = 'select * from userinfo where username="%s" and password="%s" '%(username,password)
    cursor.execute(sql)
    result = cursor.fetchall()   #獲取返回的全部數(shù)據(jù)，元組形式
    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 -- , 輸入任意密碼

演示三

解析：由上圖可以看出，即使不知道數(shù)據(jù)庫中的用戶名和密碼是什么，也可以通過驗證。通過字符串替換，我們得到這樣的一條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是永遠成立的，可以從數(shù)據(jù)庫獲取到值，所以可以通過驗證。

改進版《讀取數(shù)據(jù)庫，驗證用戶登錄》

防止被SQL注入

我們在輸入SQL字符串時，不要自己去做字符串格式化替換，可以在給execute方法傳參時，第一個參數(shù)傳查詢的SQL，第二個參數(shù)傳SQL中要替換的字符串列表。pymysql模塊內(nèi)部會自動替換，在替換時，會將特殊字符替換掉。

#改進版

import pymysql

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

try:
    # 創(chuàng)建連接
conn = pymysql.Connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       password="123456",
                       database="study",
                       charset="utf8")
    # 創(chuàng)建游標，利用游標支數(shù)據(jù)庫取數(shù)據(jù)
    cursor = conn.cursor()

    sql = 'select * from userinfo where username=%s and password=%s '
    cursor.execute(sql,[username,password])   #第2個參數(shù)為列表
    result = cursor.fetchall()   #獲取返回的全部數(shù)據(jù)，元組形式
    if result:
        print("登錄成功")
    else:
        print("賬號或密碼錯誤")
except Exception as e:
    print(e)
    raise
finally:
    cursor.close()
    conn.close()

演示四：

演示四

可以看出，代碼改進后，就不會再出現(xiàn)被SQL注入了。

2.2.2 寫庫操作必須提交commit

對數(shù)據(jù)庫進行增、刪、改操作都必須執(zhí)行提交（commit）操作

import pymysql

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

cursor = conn.cursor()

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

#對數(shù)據(jù)庫進行增、刪、改操作都必須執(zhí)行提交（commit）操作
conn.commit()
cursor.close()
conn.close()

2.2.3 直接獲取新增數(shù)據(jù)的自增ID

cursor.lastrowid方法可以直接獲取新增數(shù)據(jù)的自增ID

需求：現(xiàn)在我想往class班級表中新插入一個班級，然后再往這個新班級中插入一條學(xué)生數(shù)據(jù)（包含班級ID）。

import pymysql

# 創(chuàng)建連接
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  #獲取新增數(shù)據(jù)的自增ID
cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)',["李杰","女",new_class_id])

#對數(shù)據(jù)庫進行增、刪、改操作都必須執(zhí)行提交（commit）操作
conn.commit()
cursor.close()
conn.close()

2.2.4 將查詢結(jié)果以字典形式返回

需要在conn.cursor()傳一個參數(shù)：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()

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