#第9篇分享：python資料儲存-MySQL資料庫

#資料庫的學習-MySQL

1.要想選擇一款適合自己的資料庫，我們首先要對資料庫有個瞭解：

a.資料庫從字面上的理解就是資料的倉庫， 其實我們平時說的資料庫是指資料庫管理系統(Database Management System) ，它是一種操縱和管理資料庫的大型軟體，用於建立、使用和維護資料庫，簡稱DBMS；

b.資料庫是資料庫管理系統的例項， 一個資料庫管理系統可以有多個資料庫例項，我們運算元據庫步驟：建立自己的資料庫->在自己的資料庫裡面建立表格->對錶格進行資料的增刪改查；

c.資料庫不僅僅有我們熟悉的關係型資料庫（和excle類似的表格），還 有鍵值（Key-Value）資料庫、列儲存資料庫、文件資料庫和搜尋引擎等型別。（其他資料庫有興趣也可以去了解一下，我們要介紹的MySQL資料庫就是關係型資料庫）

關係型資料庫： 這種型別的資料庫是最古老的資料庫型別，關係型資料庫模型是把複雜的資料結構歸結為簡單的二元關係（即二維表格形式，類似於excle）， 通常該表第一行為欄位名稱，描述該欄位的作用，下面是具體的資料。在定義該表時需要指定欄位的名稱及型別。

2.MySQL資料庫的安裝：官網下載社群版zip，解壓後，把bin檔案加環境變數載入後便可使用；mysql官網軟體
你也許會遇到以下的坑：

a.提示：缺少系統啟動檔案或者後來會遇到缺少.dll檔案（我剛剛就遇到了，網上說啥的都有，差點把資料庫解除安裝了），可下載安裝 ”微軟常用執行庫合集” 解決以下，也是百度教我的；

b.提示：未安裝進系統服務：mysql –install可以實現；用mysqld –remove 命令，解除安裝mysql服務（安裝後可在電腦的服務裡面找到，win10直接下面搜尋框搜尋服務即可看見），刪除安裝的資料庫服務：sc delete MYSQL；

c.提示：沒有data資料夾：mysqld --initialize-insecure --user=mysql ，會發現多了一個data資料夾；

d.安裝資料庫服務並繫結my.ini配置(這個檔案自己建立即可，內容也是自己新增，完成後用下面的命令繫結一下即可使用)：mysqld --install “mysql80" --defaults-file=" D:\python\mysql-8.0.20-winx64\my.ini “
my.ini 裡面內容：
#設定mysql客戶端預設字符集
default-character-set=utf8
[mysqld]
#設定3306埠
port = 3306
#設定mysql的安裝目錄
basedir=D:\python\mysql-8.0.20-winx64
#設定mysql資料庫的資料的存放目錄
datadir=D:\python\mysql-8.0.20-winx64\data
#允許最大連線數
max_connections=200
#服務端使用的字符集預設為8位元編碼的latin1字符集
character-set-server=utf8
#建立新表時將使用的預設儲存引擎
default-storage-engine=INNODB

e.修改密碼：進入MySQL後(mysql -u root,預設無密碼)，輸入以下命令設定密碼：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密碼’;

3.操作MySQL資料庫：命令列操作mysql

a.啟動MySQL資料庫:net start xxx（填寫你的資料庫名字）
停止資料庫：net stop xxx（填寫你的資料庫名字）
檢視資料庫版本：mysql –V
進入資料庫：mysql -u root –p xxx(你的密碼)
b.運算元據庫：（mysql的結構層級大致為 資料庫 -> 表 -> 行 從左到右都是一對多）

@檢視所有資料庫：show databases;檢視活躍連線show processlist;
@建立資料庫：create database +資料庫名字（default character set utf8（指定資料庫字符集）可選項）；
@進入資料庫：use +資料庫名字；
@刪除資料庫：drop database +資料庫名字；修改資料庫編碼alter database 資料庫名字 default character set gbk;
@檢視所有表：show tables；
@建立表： create table [表名] ( [屬性名] [資料型別] [約束條件-可選]) 參考(sid int key,sname varchar(20));）資料型別：int,float,varchar；
@修改表名稱：alter table 現表名 rename to 新表名;
@刪除表格： drop table [IF EXISTS] 表名1 [ ,表名2, 表名3 …]

c.資料庫-表格的增刪改：
-----> INSERT INTO <表名> [ <列名1> [ , … <列名n>可選] ]VALUES (值1) [… , (值n) ]；
-----> INSERT INTO <表名>SET <列名1> = <值1>,<列名2> = <值2>,……
-----> INSERT INTO…SELECT…FROM 語句用於快速地從一個或多個表中取出資料，並將這些資料 作為行資料插入另一個表中；

----> 刪除表格內容： DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
-----> 刪除主鍵約束：ALTER TABLE <資料表名> DROP PRIMARY KEY;
-----> 建立表格時增加外來鍵約束：CONSTRAINT 約束名字 FOREIGN KEY (外來鍵表格定義的欄位) REFERENCES 主鍵表格的欄位
-----> 表格建完了，後加約束：ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY(category_id) REFERENCES category(id);
-----> 刪除外來鍵約束：alter table product drop foreign key product_ibfk_1 注意這個是外來鍵名，而不是定義外來鍵那個欄位名字，容易搞錯的喲；

-----> truncate table 表名;delete from 表名;
-----> delete from 表名 where 主鍵名稱 =‘*****’；
例：delete from student where id=2;
刪除對比：delete from可以帶條件刪除；只能刪除表的資料，不能刪除表的約束 ；可以回滾（事務）truncate table不能帶條件刪除 ；既可以刪除表的資料，也可以刪除表的約束；不能回滾

-----> 修改表格資料： UPDATE <表名> SET 欄位 1=值 1 [,欄位 2=值 2… ] [WHERE 子句 ][ORDER BY 子句] [LIMIT 子句] ；
例：update student set gender=‘男’,age=30 where id=2;

-----> 檢視錶格內容：SELECT * FROM <表名>;
-----> 檢視錶結構：desc 表名；
-----> 檢視錶格內容：select *from 表名；SELECT < 列名 > FROM < 表名 >;

d.資料庫-表的查詢：（增刪改相對來說比較簡單，查詢是需要下功夫進行學習的，用例項鍛鍊比較好）
①基礎查詢

-----> 查詢時新增常量列SELECT 欄位名1，欄位名2，*（萬用字元：所有欄位） FROM 表名; 例：SELECT *FROM 表名;

-----> 簡單查詢表格資料SELECT DISTINCT 欄位名1，欄位名2，*（萬用字元：所有欄位） FROM <表名>;去重查詢

-----> SELECT COUNT(DISTINCT 欄位名1，欄位名2) FROM <表名>;返回不重複欄位數

-----> SELECT * FROM <表名> LIMIT 3,5; LIMIT 初始位置，記錄數；對返回數量進行限制

-----> SELECT 欄位名1 FROM <表名> ORDER BY欄位名1 DESC; ORDER BY <欄位名> [ASC升序|DESC降序]

-----> SELECT * FROM <表名> WHERE欄位名1 BETWEEN 75 and 90（條件）; WHERE 查詢條件； [NOT] BETWEEN 取值1 AND 取值2：範圍查詢

-----> SELECT 欄位名1 FROM <表名> WHERE 欄位名1 NOT LIKE ‘T%‘/ IS [NOT] NULL ；
[NOT] LIKE ’字串’ ； “%” 代表任何長度的字串，字串的長度可以為 0；
“_”只能代表單個字元，字元的長度不能為 0。MySQL 提供了 IS NULL 關鍵字，用來判斷欄位的值是否為空值（NULL）。空值不同於 0，也不同於空字串。

-----> SELECT GROUP_CONCAT(欄位名1),欄位名2 FROM <表名> GROUP BY 欄位名2 HAVING AVG(欄位名2)>170;
GROUP BY <欄位名>（對查詢結果進行分組）； GROUP_CONCAT() 函式會把每個分組的欄位值都顯示出來；
HAVING <查詢條件>（可以使用 HAVING 關鍵字對分組後的資料進行過濾）

-----> 子查詢：子查詢指將一個查詢語句巢狀在另一個查詢語句中:WHERE 表示式 操作符 (子查詢-返回TRUE/FALSE);
例：SELECT name FROM tb_students_info ->
WHERE course_id IN/NOT NI/EXISTXS/NOT EXISTS(SELECT id FROM tb_course WHERE course_name = 'Java’);
注：子查詢語句可以巢狀在 SQL 語句中任何表示式出現的位置;只出現在子查詢中而沒有出現在父查詢中的表不能包含在輸出列中

②聚合查詢：常用的聚合函式： sum() avg() max() min() count()

③MySQL常用函式：version():返回mysql伺服器版本；now():返回當前的日期和時間；length(str):返回字串的字元數量；
concat(s1,s2,……sn):連線字串；

4.MySQL圖形操作軟體：Navicat Premium 15
以上介紹的都是命令列對mysql資料庫的操作，但其實我們有更好的選擇，就是其他大神開發的圖形操作介面，下面我們來瞅一瞅：

a.軟體下載及安裝：Navicat Premium官網，選擇合適自己的版本
直接下一步安裝，沒有注意事項，軟體是試用版本，下面教你如何無限試用，不喜歡自己去找破解方法；
b.軟體無限試用：
等我們python學的比較多了就會發現，他的一些資訊是存在我們的電腦裡面的，比如他如何直到我們使用了多少天呢，還不是因為他偷偷的在我們電腦中記錄了，也就是在一個叫做登錄檔的地方偷偷記錄的，我們把他刪掉是不他就不知道我們用過了，哈哈哈：win+r ->cmd ->regedit(調出登錄檔)
然後按照下圖操作，你會發現用可以從開頭試用了：

5.MySQL在實際專案裡如何應用：
a.在自建的單個程式及Scrapy框架裡，我們這麼用：引入pymysql->建立mysql資料庫連線（db,名字可以是別的），建立遊標（cursor,名字可以是別的）->cursor.execute(sql語句，與命令列一致) 執行mysql語句-> db.commit() 提交事務，完成入庫操作。

import  pymysql
conn = pymysql.connect('localhost','root','f199506','test')
cursor = conn.cursor()
#cursor.execute('SELECT  VERSION()')
cursor.execute('select  *from test1')
DATA =cursor.fetchall()
conn.commit()  # 提交事務
print(DATA)
conn.close()

#scrapy-----pipeline中設定：
import pymysql
class SqlTestPipeline(object):
    def __init__(self):  # 定義需要初始化的引數（可省略）
        #1.建立資料庫連結，給cursor新增一個引數，讓其的到資料是一個字典
         self.db = pymysql.connect(host='localhost',port=3306,user='root',password='f199506',db='scrapy_mysql',charset='utf8')
         self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)
    def process_item(self, item, spider):
        #1.儲存資料到資料庫：
        #a.建立表格
        self.sql = 'create table test (id int auto_increment key ,Title varchar(150),money varchar(100),jobsite varchar(50),content varchar(500)) '
        self.cursor.execute(self.sql)  # 執行mysql語句
        self.db.commit()  # 提交事務
        #b.插入資料
        self.sql = 'INSERT INTO test(Title,money,jobsite,content,url) VALUES("%s","%s","%s","%s","%s")'
        self.cursor.execute(self.sql % (item["Title"], item["money"], item["jobsite"], item["content"],item["url"]))  # 執行mysql語句
        self.db.commit()  # 提交事務
        #c.刪除資料
        self.sql = 'DELETE FROM test'
        self.cursor.execute(self.sql)  # 執行mysql語句
        self.db.commit()  # 提交事務
        #查詢資料：
        self.sql = 'select *from test'
        self.a = self.cursor.execute(self.sql)  # 執行mysql語句
        print(self.a)
        self.db.commit()  # 提交事務

    # 當爬取結束時候執行的方法（可省略）
    def close_spider(self, spider):
        #1.關閉資料庫連結
        self.cursor.close()
        self.db.close()

b.在Django大型框架裡，我們這麼用：
這裡我們介紹一個概念ORM:即Object-Relationl Mapping，它的作用是在關係型資料庫和物件之間作一個對映，這樣，我們在具體的運算元據庫的時候，就不需要再去和複雜的SQL語句打交道，只要像平時操作物件一樣操作它就可以了

有些人會問了，操作物件的方法是什麼呢？我舉個例子，其實就是 . 操作，下面就是查詢資料的例項，其他的我們以後再介紹吧：
#1.分類資訊的獲取（導航欄資料）
category_list = Category.objects.all()
#2.廣告資料
ad_list = Ad.objects.all()

操作步驟：init.py裡面引入pymysql，settings裡面配置連線>操作物件的方式使用即可（這就是框架的魅力，明白他的套路後，可以更簡單的開發出我們的專案。）

# 資料庫的配置
#專案配置__init__.py裡面引入pymysql：
import pymysql
pymysql.install_as_MySQLdb()

#ettings裡面按下面配置資料庫：
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'blogdb',
        'USER': 'root',
        'PASSWORD': 'f199506',
        'HOST': '',
        'PORT': '',
    }
}

#以上配置完成後即可按照操作物件的方式運算元據庫

最近心態不錯，不是因為總是遇見好的事情，而是突然明白了個道理，只要是生活在這個社會裡面，總是會遇見你未預料事情，但是又不得不去做，這個時候與其很牴觸的強制完成，不如聽我一句：不能改變，那就享受吧，也許事情並沒有那麼糟。
這篇文章我思考了好久才開始重新提筆完成，因為我自覺自己沒有多麼厲害，但是轉念一想，把我遇到的坑寫出來，幫助一個人那就足夠了，在寫部落格之前，我很少給別人點贊，但是寫了之後真的是感同身受，總是希望有人來看一看，肯定自己一下；
這個感覺大學也有過，就是我參加完學校的廣場舞之後，我發現我突然可以看進去歌舞節目了，以前是很牴觸的，因為我是真真切切明白了每個動作的不容易，也能get到那種美了；所以不瞭解一個人的時候不要隨意評價，行業亦然。
第九篇分享，持續更新，，，