數據庫概述

• 數據庫的由來

  發展歷程	說明
  人工管理階段	用紙帶等進行數據的存儲
  文件系統階段	數據存儲在文件中
  數據庫階段	解決了文件系統問題
  高級數據庫階段	分布式數據庫

• 數據庫的分類

  數據庫分類	說明	常用庫
  關系型數據庫	采用了關系模型來組織數據的數據庫：關系模型指的就星二維表格模型	Oracle Microsoft SQL Server MySQL SQLite
  非關系型數據庫	Not Only SQL 強調Key-Value的方式存儲數據	Mongodb redis

• 關系型數據庫的使用場景
  Web網站系統、日志記錄系統、數據倉庫系統、嵌入式系統

Mysql概述

MySQL是一個$關系型數據庫管理系統$軟件。

• 關系型數據庫管理系統-RDBMS
  MySQL是一個關系型數據庫管理系統軟件，在WEB應用方面，MySQL是最好的RDBMS(Relational Database Management System, 關系數據庫管理系統）應用軟件，它是由瑞典MySQL AB司開發，目前屬于Oracle旗下產品，MySQL是最流行的關系型數據庫管理系統中的一個。

• 關系型數據庫管理系統可以分為：

  1. 關系型數據庫服務端軟件-Server
  2. 關系型數據庫客戶端軟件-Client

• 關系型數據庫管理系統的流程圖：
  

• 關系型數據庫管理系統的通信流程效果圖：
  

• SQL介紹
  SQL(Structured Query Language)是$結構化查詢語言$，是一種用來操作$RDBMS$的數據庫的語言，也就是說通過SQL可以操作oracle, sql server, mysql, sqlite等關系型的數據庫。

• SQL語言的分類

  簡寫	語義	說明
  DQL	數據查詢語句	select
  DML	數據操作語句	insert update delete
  DDL	數據定義語句	creat drop
  TPL	事務處理語言	begin transaction commit rollback
  DCL	數據控制語言	grant revoke

• MySQL的特點

  1. MySQL是開源的，所以你不需要支付額外的費用。
  2. M小ySQL支持大型的數據庫。可以處理擁有上干萬條記錄的大型數據庫。
  3. MySQL使用標準的SQL數據語言形式。
  4. MySQL可以安裝在不同的操作系統，并且提供多種編程語言的操作接口。這些編程語言包括C、C++、Python、Java、Ruby等等。

• MySQL的數據類型

  數據類型	說明
  int, bit	整數
  decimal	小數
  varchar, char	字符串
  date, time, datetime	日期和時間
  enum	枚舉類型

• MySQL的字段約束

  約束參數	說明
  primary key	主鍵約束
  not null	非空約束
  unique	唯一約束
  default	默認約束

Mysql安裝與使用

• Mysql安裝
  MySQL數據庫$服務$端軟件的安裝：sudo apt-get install mysql-server
  MySQL數據庫$客戶$端軟件的安裝：sudo apt-get insatll mysql-client
• Mysql數據庫服務端啟動
  查看MySQL服務狀態：sudo service mysql status
  停止MySQL服務：sudo service mysql stop
  啟動MySQL服務：sudo service mysql start
  重啟MySQL服務：sudo service mysql restart
• Mysql的配置文件
  1. mqsql配置文件的路徑：/etc/mysql/mysql.conf.d/
     
  2. 配置項介紹
     <1> port表示端口，默認為3306；
     <2> bind-address表示服務器綁定的ip，默認為127.0.0.1；
     <3> datadir表示數據庫保存路徑，默認為/var/lib/mysql;
     <4> log_error表示錯誤日志，默認為/var/log/mysql/error.log;

Navicat安裝和使用

• Navicat介紹
  Navicat是一款圖形化界面的數據庫客戶端軟件。
• Navicat的安裝——基于Linux操作系統
  1. 官網下載安裝包：https://www.navicat.com.cn
  2. 解壓navicat安裝包：tar -zxvf navicat112_mysql_cs_x64.tar.gz
  3. cd到navicat安裝包文件夾目錄：cd navicate112_mysql_cs_x64
  4. 運行navicat：./start_navicat

  安裝包及破解工具下載： 百度網盤 【提取碼：4KaE】
  破解教程：Navicat Premium15永久破解版安裝教程

• Navicat的連接mysql服務
  

Mysql終端指令操作

• Mysql登錄登出客戶端
  連接mysql服務端指令：mysql -uroot -p
  顯示當前時間：select now();
  退出連接：exit/quit/[control+d]
  
• Mysql數據庫操作
  查看所有數據庫：show databases;
  創建數據庫：create database 數據庫名 charset=utf-8;
  使用數據庫：use 數據庫名;
  查看當前使用的數據庫：select database();
  刪除數據庫：drop database 數據庫名;
  
• Mysql表操作
  查看所有當前庫中的所有表：show tables;
  創建表：create table 表名(字段名稱 數據類型 可選的約束條件, column1 datatype contral, ...);
  修改表字段類型：alter table 表名 modify 列名 類型 約束;
  刪除表：drop table 表名;
  查看表結構：desc 表名;
  
• Mysql -CRUD操作
  1. 查詢數據
     查詢所有列：select * from 表名;
     查詢指定列：select 列名1, 列名2,... from 表名;
     
  2. 增加數據
     全列插入 - 值的順序必須和字段順序完全一致：insert into 表名 values(...);
     部分列插入 - 值的順序和給出的列的順序對應：insert into 表名(列1, 列2,...) values(值1, 值2,...)
     全列多行插入：insert into 表名 values(...), (...), (...);
     部分列多行插入：insert into 表名(列1, 列2,...) values(值1,...), (值1,...), (值1,...)
     
  3. 修改數據：update 表名 set 列1=值1, 列2=值2,... where 條件;
     
  4. 刪除數據：delete from 表名 where 條件;
     
• Mysql數據庫備份和恢復

  1. 備份導出：mysqldump -u用戶名 -p密碼 數據庫名 表名 > data.sql （注意：不寫表名默認導出所有數據表）
     

  2. 恢復導入：

     cd 到數據文件路徑下
     mysql -u用戶名 -p密碼
     use 數據庫
     source data.sql
     

     

Mysql和python交互

• pymysql的安裝
  安裝pymysql：sudo pip3 install pymysql
  查看安裝情況：pip show pymysql / pip3 list
  卸載pymysql：sudo pip3 uninstall pymysql
  

• pymysql的使用

  1. 導包：import pymysql
  2. 創建和mysql服務端的連接對象：conn = pymysql.connect(參數列表)
  3. 獲取游標對象：cursor = conn.cursor()
  4. 執行sql語句：row_count = cursor.execute(sql)
  5. 獲取查詢結果集：result = cursor.fetchall()
  6. 將增加和修改操作提交到數據庫：conn.commit()
  7. 回滾數據：conn.rollback()
  8. 關閉游標對象：cursor.close()
  9. 關閉連接：conn.close()

• pymysql查詢數據

  # 查詢數據庫goods下student表中的所有數據
  # 1. 導包
  import pymysqltry:# 2. 連接mysql數據庫的服務conn = pymysql.Connect(# mysql服務端的IP，默認127.0.0.1/localhost,也可填真實iphost='192.168.90.172',user='root',password='mysql',database='goods',port=3306,charset='utf8')# 3. 創建游標對象cur = conn.cursor()# 4. 編寫sql語句sql = 'select * from students;'# 5. 使用游標對象去調用sqlcur.execute(sql)# 6. 獲取查詢的結果并打印result = cur.fetchall()print(result)# 7. 關閉游標對象cur.close()# 8. 關閉連接conn.close()
  except Exception as e:print(e)
  

  結果如下：
  

• pymysql增刪改數據

  '''
  對數據庫goods下的students表進行如下操作：
  1. 增加數據：李磊 35 男
  2. 修改數據：李磊的名字改為王磊
  3. 刪除數據：王磊
  '''# 1. 導包
  import pymysql# 2. 連接mysql數據庫的服務
  conn = pymysql.Connect(# mysql服務端的IP，默認127.0.0.1/localhost,也可填真實iphost='192.168.90.172',user='root',password='mysql',database='goods',port=3306,charset='utf8'
  )
  # 3. 創建游標對象
  cur = conn.cursor()
  try:# 4. 編寫增加、修改、刪除的sql語句add_sql = 'insert into students values(%s, %s, %s, %s);'add_data = [0, '李磊', 35, '男']update_sql = 'update students set name=%s where name = "李磊";'update_data = ['王磊']delete_sql = 'delete from students where name=%s;'delete_data = ['王磊']# 5. 使用游標對象執行sqlcur.execute(add_sql, add_data)cur.execute(update_sql, update_data)cur.execute(delete_sql, delete_data)# 6. 提交操作conn.commit()
  except Exception as e:print(e)# 數據回滾conn.rollback()
  finally:# 7. 關閉游標對象cur.close()# 8. 關閉連接conn.close()
  

訂單管理案例實現

• 需求分析
  
  ① 創建數據庫orders
  ② 創建orders訂單表
  ③ 插入測試數據orders.sql
  ④ Python和Mysql交互
  ⑤ 查詢訂單數據
  ⑥ 增加訂單數據
  ⑦ 刪除訂單數據
  ⑧ 修改訂單數據

• 實現步驟

  1. 建庫和建表

     --創建數據庫
     create database orders charset=utf8;--創建數據表
     create table orders(id int not null auto_increment primary key,count int not null,price decimal(10,2) not null,freight decimal(10,2) not null,user varchar(50) not null,status enum('待支付','待發貨','待收貨') default '待支付' not null,time date not null);
     

  2. 插入測試數據
     ① cd素材的目錄下
     ② 登錄mysql客戶端：mysql -uroot -p
     ③ 切換數據庫： use orders
     ④ 導入測試數據：source orders.sql
     

  3. 實現訂單查詢功能
     ① 導包pymysql
     ② 連接mysql服務
     ③ 創建游標對象
     ④ 編寫查詢所有訂單SQL語句
     ⑤ 執行SQL語句
     ⑥ 獲取所有查詢的結果
     ⑦ 將獲取結果轉換成列表字典格式
     ⑧ 對Mysql的操作加Try處理

     # 1 導包
     import pymysqldef order()# 2 鏈接mysql的服務端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 創建游標對象cur = conn.cursor()try:# 4 編寫 查詢orders表的所有數據SQLsql = 'select * from orders;'# 5 使用游標對象執行SQLcur.execute(sql)# 6 獲取查詢的所有數據 fetchall() ==>元組result = cur.fetchall()print('查詢數據：', result)# 打印結果如下：# 查詢數據：((1, 2, Decimal('100.00'), Decimal('10.00'), '老王'， ‘待收貨', datetime.data(2020, 1, 1)), (2, 3, Decimal('200.00'), Decimal('25.00'), '錢紅'， ’待支付', datatime.date(2020, 4,1)))# 7 將數據轉換成[{}, {}]data_lst = []for row in result:data_lst.append({'id': row[0],'count': row[1],'price': str(row[2]),'freight': str(row[3]),'user': row[4],'status': row[5],'time': str(row[6])})# 8 加個try優化下except Exception as e:print('報錯信息：', e)finally:# 關閉游標對象cur.close()# 關閉連接conn.close()
     

  4. 實現訂單增加功能
     ① 導包pymysql
     ② 連接mysql服務
     ③ 創建游標對象
     ④ 編寫增加訂單的SQL語句
     ⑤ 執行SQL語句并提交
     ⑥ 對Mysql的操作加Try處理

     # 1 導包
     import pymysqldef add(data):# 2 鏈接mysql的服務端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 創建游標對象cur =conn.cursor()try:# 4 編寫 增加數據的SQLsql = 'insert into orders values(%s,%s,%s,%s,%s,%s,%s)'# 5 使用游標對象執行SQL并提交cur.execute(sql, data)conn.commit()# 6 加個try優化下except Exception as e:print('報錯信息：', e)# 回滾數據conn.rollback()finally:# 關閉游標對象cur.close()# 關閉連接conn.close()
     

  5. 實現封裝功能函數
     查詢和增加的功能重復度比較高，均有：導包、連接mysql、創建游標對象、執行SQL語句、Try語句。實現封裝函數的操作：
     ① 定義封裝函數的名字
     ② 粘貼重復度高的代碼
     ③ 提交參數
     ④ 測試

     # 1 導包
     import pymysql# 封裝一個執行CRUD的函數
     def execute_crud_sql(sql,data):# 2 鏈接mysql的服務端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 創建游標對象cur = conn.cursor()try:# 4 編寫 查詢orders表的所有數據SQL# 5 使用游標對象執行SQLcur.execute(sql, data)# 6 提交操作conn.commit()# 8 加個try優化下except Exception as e:print('報錯信息：', e)# 回滾數據conn.rollback()finally:# 關閉游標對象cur.close()# 關閉連接conn.close()def add(data):sql = 'insert into orders values(%s,%s,%s,%s,%s,%s,%s)'execute_crud_sql(sql, data)
     

  6. 實現訂單修改和刪除功能

     # 修改的SQL語句
     update orders set count=%s, price=%s, freight=%s, user=%s, status=%s, time=%s where id=%s;
     # 將參數中id的順序從第一個顛倒到最后一個
     data.append(data.pop(0))# 刪除的SQL語句
     delete from orders where id=%s;
     

     # 1 導包
     import pymysql# 封裝一個執行CRUD的函數
     def execute_crud_sql(sql,data):# 2 鏈接mysql的服務端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 創建游標對象cur = conn.cursor()try:# 4 編寫 查詢orders表的所有數據SQL# 5 使用游標對象執行SQLcur.execute(sql, data)# 6 提交操作conn.commit()# 8 加個try優化下except Exception as e:print('報錯信息：', e)# 回滾數據conn.rollback()finally:# 關閉游標對象cur.close()# 關閉連接conn.close()# 修改訂單數據
     def update(data):print('修改的數據：', data)   # ['7', '99', '9.90', '1.00', '明明'，'待收貨', '2020-03-29']data.append(data.pop(0))# 1. 修改的SQL語句sql = 'update orders set count=%s, price=%s, freight=%s, user=%s, status=%s, time=%s where id=%s'# 2. 執行調用execute_crud_sql(sql, data)# 刪除訂單數據    
     def delete(data):# 1. 刪除的SQL數據sql = 'delete from orders where id=%s;'# 2. 執行execute_crud_sql(sql, data)