无码av人妻精品一区二区三区抖音,日韩成人在线免费电影

數(shù)據(jù)庫設(shè)計

**三范式

經(jīng)過研究和對使用中問題的總結(jié)，對于設(shè)計數(shù)據(jù)庫提出了一些規(guī)范，這些規(guī)范被稱為范式 (Nomal Form)，目前有跡可錄的共有8種范式，一般需要遵守3范式即可
第一范式（1NF） ：強調(diào)列的原子性，即列不能再分成其他幾列
- 舉例：設(shè)計一個表，有姓名、年齡，電話字段，如果電話有移動電話和固定電話，就不符合這一范式。應(yīng)這么設(shè)計：姓名、年齡、移動電話、固定電話
第二范式（2NF） ：基于1NF之后，另外表里面必須有一個主鍵；沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分
- 舉例：設(shè)計一個訂單收貨地址表，有訂單號、區(qū)域價、收貨記錄id、收貨人詳細地址、收貨人名稱，這時的主鍵應(yīng)為（訂單號、收貨記錄id），區(qū)域價需要依賴主鍵（訂單號、收貨記錄id），而收貨人名稱和收貨人詳細地址，則只需依賴（收貨記錄id），所以這不符合這一范式。應(yīng)這么設(shè)計：將訂單收貨地址表拆分成兩個，訂單信息表、收貨地址信息表。訂單信息表的字段為（訂單號、收貨記錄id、區(qū)域價）；收貨地址信息表（收貨記錄id、收貨人名稱、收貨人詳細地址）
第三范式（3NF） ：基于2NF之后，另外非主鍵列必須直接依賴于主鍵，不能傳遞依賴，即不能存在非主鍵A依賴非主鍵B，非主鍵B依賴主鍵的情況
- 舉例：設(shè)計一個訂單表，有訂單號、訂單金額、下單時間、采購人ID、采購人名稱、采購人地址，主鍵是（訂單號）。這里面采購人名稱、采購人地址是直接依賴采購人id，不是直接依賴主鍵。應(yīng)這么設(shè)計：拆分成訂單表和采購人信息表，訂單表字段為（訂單號、訂單金額、下單時間、采購人ID），采購人信息表（采購人ID、采購人名稱、采購人地址）

Python與MySql交互

步驟

安裝模塊： 使用pip命令安裝pymysql
- ```
pip install pymysql
```
引入模塊： 在py文件中引入pymysql模塊
- ```
from pymysql import *
```
Connection對象
- 用于建立Python與數(shù)據(jù)庫的連接
- 創(chuàng)建對象：調(diào)用connect()方法
- ```
conn=connect(參數(shù)列表)
```
  - 參數(shù)host：連接的mysql主機，當(dāng)前機器可以寫成 localhost
  - 參數(shù)post：連接mysql的端口，默認是 3306
  - 參數(shù)database：數(shù)據(jù)庫的名稱
  - 參數(shù)user：連接的用戶名
  - 參數(shù)password：連接的密碼
  - 參數(shù)charset：通信采用的編碼方式，推薦使用utf8
- Connection對象的方法
  - close() 關(guān)閉連接
  - commit() 提交
  - cursor() 返回Cursor對象，用于執(zhí)行sql語句并獲得結(jié)果
Cursor對象
- 用于執(zhí)行sql語句，使用頻率最高的語句是 select、insert、update、delete
- 獲取Cursor對象：調(diào)用Connection對象的cursor()方法
  - ```
  cus=conn.cursor()
```
- Cursor對象的方法
  - close() 關(guān)閉
  - execute(self, query, args) 執(zhí)行語句，接收的參數(shù)為sql語句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)
  - fetchone()執(zhí)行查詢語句，獲取查詢結(jié)果集的第一行數(shù)據(jù)，返回一個元組
  - fetchmany()執(zhí)行查詢時，獲取所有結(jié)果行，每行是一個元組，再將這些元組放入一個元組中返回
- 對象的屬性
  - rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
  - connection獲得當(dāng)前連接對象

示例

from pymysql import *


# 建立數(shù)據(jù)庫連接
conn=connect(host="localhost",port=3306,user="root",password="123456",database="python01",charset="utf8")
# 獲取Cursor對象
cursor=conn.cursor()


# 查詢數(shù)據(jù)表
cursor.execute("select *from person")
# 獲取所有結(jié)果集
lines=cursor.fetchall()
print(lines)
for line in lines:
    print(line)


# 插入數(shù)據(jù)
count=cursor.execute("insert into person(id,name,age) values(12,'李天王',333),(13,'哪吒',222)")
print("插入成功條數(shù)：",count)


# 修改表數(shù)據(jù)
count=cursor.execute("update person set name='白蛇' where id=8")
print("修改成功條數(shù)：",count)


# 刪除表數(shù)據(jù)
count=cursor.execute("delete from person where id=5")
print("刪除成功條數(shù)：",count)


# 修改數(shù)據(jù)表后，需要提交
conn.commit()


# 再次查詢數(shù)據(jù)表
cursor.execute("select *from person")
print(cursor.fetchall())


# 關(guān)閉Cursor對象,關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()

**輸出結(jié)果

MySQL高級

**視圖

視圖就是一條 SELECT 語句執(zhí)行后返回的結(jié)果集 ，所以我們在創(chuàng)建視圖的時候，主要的工作就落在了SELECT查詢語句上
視圖是對若干張基本表的引用，一張?zhí)摫?，查詢語句執(zhí)行的結(jié)果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會跟著改變）

定義視圖

建議以 v_ 開頭

create view 視圖名稱 as select 語句;

查看視圖

查看表會將所有的視圖也列出來

show tables;

使用視圖

視圖的用途就是查詢

select * from v_person

刪除視圖

drop view 視圖名稱;

視圖的作用

提高了重用性，就像是一個函數(shù)
創(chuàng)建視圖的源數(shù)據(jù)被修改了，視圖中的數(shù)據(jù)也會被修改，與windows的快捷方式很像（對數(shù)據(jù)庫重構(gòu)，卻不影響程序的運行）
提高了安全性，可以對不同的用戶
讓數(shù)據(jù)更加清晰

示例

# 創(chuàng)建視圖, 查詢訂單與訂單收貨 信息表，生成一個虛擬表（視圖）
create view v_order_info as  SELECT a.order_no,a.order_price,b.receive_name,b.receive_phone FROM `order` a, order_receive b where a.order_no=b.order_no;


# 查看視圖的內(nèi)容
SELECT * from v_order_info;

**事務(wù)

所謂事務(wù)，它是一個操作序列，這些操作要么都執(zhí)行成功，要么都執(zhí)行失敗， 是一個不可分割的工作單位。
- 舉例：銀行轉(zhuǎn)賬，第一次轉(zhuǎn)100，第二次轉(zhuǎn)200，都放到一個事務(wù)里面，要么全部轉(zhuǎn)成功，要么都失敗

事務(wù)的四大特性（ACID）

原子性
- 強調(diào)事務(wù)不可分割，整個事務(wù)中的所有操作要么全部成功，要么全部失敗
一致性
- 事務(wù)的執(zhí)行的前后數(shù)據(jù)的完整性保持一致，即上面轉(zhuǎn)賬的例子中，如果轉(zhuǎn)了一半的錢，系統(tǒng)崩潰了，但是事務(wù)最終沒有提交，那么在事務(wù)中做的修改也不會保存到數(shù)據(jù)庫中
隔離性
- 一個事務(wù)執(zhí)行的過程中，不應(yīng)該受到其它事務(wù)的干擾
持久性
- 一旦事務(wù)提交，則其所做的修改會永久保存到數(shù)據(jù)庫

**事務(wù)的命令

表的引擎類型必須是 innodb類型才可以使用事務(wù)，這是mysql表的默認引擎
開啟事務(wù)
- 開啟事務(wù)后執(zhí)行修改命令，變更會維護到本地緩存中，而不維護到物理表中
- ```
begin;
或者
start transaction;
```
提交事務(wù)
- 將緩存中的數(shù)據(jù)變更維護到物理表中
- ```
commit;
```
回滾事務(wù)
- 放棄緩存中變更的數(shù)據(jù)
- ```
rollback;
```
注意
- 默認修改數(shù)據(jù)的sql語句會自動觸發(fā)事務(wù)（開啟與提交），即 insert、update、delete 語句
- 一般在sql語句中手動開啟事務(wù)的原因是：可以進行多次數(shù)據(jù)的修改，如果成功則一起成功，否則一起失敗

**示例

BEGIN;


# 修改person表，id=1 的名稱 (此語句執(zhí)行后，由于沒有提交，別的查詢語句查詢時，不會查到修改的數(shù)據(jù))
update person set name='小白' where id=1;


# 提交事務(wù),這個語句執(zhí)行后，數(shù)據(jù)表中的名稱變更為 小白
COMMIT;

事務(wù)隔離級別要解決的問題

臟讀
- 臟讀指的是讀到了其他事務(wù)未提交的數(shù)據(jù)，未提交意味著這些數(shù)據(jù)可能會回滾，也就是可能最終不會存到數(shù)據(jù)庫中，也就是不存在的數(shù)據(jù)。讀到了并一定最終存在的數(shù)據(jù)，這就是臟讀
- 舉例： 小明的媳婦給小明打500塊錢買衣服，但是不小心按成了1000塊，事務(wù)還沒有提交，小明這時查到卡里有1000元，小明的媳婦發(fā)現(xiàn)不對后就回滾了事務(wù)，這時小明卡里的錢就沒了
不可重復(fù)讀
- 不可重復(fù)讀指的是在同一事務(wù)內(nèi)，不同的時刻讀到的同一批數(shù)據(jù)可能是不一樣的，可能會受到其他事務(wù)的影響，比如其他事務(wù)改了這批數(shù)據(jù)并提交了
- 舉例： 小明卡里有1000元，買了衣服準備結(jié)賬（事務(wù)開啟），這時他媳婦將小明卡里的錢轉(zhuǎn)出來了，收費系統(tǒng)提示卡里面沒錢了，小明郁悶了。同一事務(wù)內(nèi)相同的查詢語句，出現(xiàn)了不同的結(jié)果就是不可重復(fù)讀
幻讀
- 幻讀是針對數(shù)據(jù)插入（INSERT）操作來說的。假設(shè)事務(wù)A對某些行的內(nèi)容作了更改，但是還未提交，此時事務(wù)B插入了與事務(wù)A更改前的記錄相同的記錄行，并且在事務(wù)A提交之前先提交了，而這時，在事務(wù)A中查詢，會發(fā)現(xiàn)好像剛剛的更改對于某些數(shù)據(jù)未起作用，但其實是事務(wù)B剛插入進來的，讓用戶感覺很魔幻，感覺出現(xiàn)了幻覺，這就叫幻讀。
- 舉例：小明某天花了1000元錢消費，他媳婦查看了當(dāng)天的消費記錄（全表掃描，事務(wù)開啟），看到確實花了1000元，就在這時，小明又花了1000元買了件衣服，并提交了事務(wù)，當(dāng)他媳婦打印消費清單時（事務(wù)提交），發(fā)現(xiàn)花了2000元，以為出現(xiàn)了幻覺，這就是幻讀

事務(wù)的隔離級別

SQL 標準定義了四種隔離級別，MySQL 全都支持。這四種隔離級別分別是：
- 讀未提交（READ UNCOMMITTED)
- 讀已提交（READ COMMITTED）
- 可重復(fù)讀（REPEATABLE READ）
- 串行化（SERIALIZABLE）
從上往下，隔離強度逐漸增強，性能逐漸變差。采用哪種隔離級別要根據(jù)系統(tǒng)需求權(quán)衡決定，其中， 可重復(fù)讀是 MySQL 的默認級別 。
事務(wù)隔離其實就是為了解決上面提到的臟讀、不可重復(fù)讀、幻讀這幾個問題，下面展示了 4 種隔離級別對這三個問題的解決程度

隔離級別	臟讀	不可重復(fù)讀	幻讀
讀未提交	可能	可能	可能
讀已提交	不可能	可能	可能
可重復(fù)讀	不可能	不可能	可能
串行化	不可能	不可能	不可能

索引

索引是一種特殊的文件 （InnoDB 數(shù)據(jù)表上的索引是表空間的一個組成部分），它們包含著對數(shù)據(jù)表里面所有記錄的引用指針。通俗來講，索引就好比一本書前面的目錄，能加快數(shù)據(jù)查詢的速度
索引的目的在于提高查詢效率，可以類比字典

索引的使用

查看索引

show index form 表名;

創(chuàng)建索引

ALTER TABLE 表名
ADD UNIQUE INDEX 索引名 (字段) USING BTREE ;

刪除索引

ALTER TABLE 表名
DROP INDEX 索引名;

示例

**創(chuàng)建一個數(shù)據(jù)表，并插入99萬條記錄，用作測試索引

**

CREATE TABLE `test_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

from pymysql import *


# 建立數(shù)據(jù)庫連接
conn=connect(host="localhost",port=3306,user="root",password="123456",database="python01",charset="utf8")
# 獲取Cursor對象
cursor=conn.cursor()


for i in range(100000):
    cursor.execute("insert into test_index(id,name) values (%d,'testindex')" %i)


# 修改數(shù)據(jù)表后，需要提交
conn.commit()


# 關(guān)閉Cursor對象,關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()

開啟運行時間監(jiān)測

set profiling=1

在整個表中查找 id=88888的記錄

SELECT * from test_index where id=88888;

查看執(zhí)行時間

show profiles;

為id字段添加索引

ALTER TABLE `test_index`
ADD UNIQUE INDEX `idx_id` (`id`) USING BTREE ;

添加索引后再次查詢

SELECT * from test_index where id=88888;

再次查看執(zhí)行時間

show PROFILES;

最終結(jié)果

注意

**建立太多的索引會影響更新和插入數(shù)據(jù)的速度，因為它需要同樣更新每個索引文件，對于一個經(jīng)常更新和插入的表格，沒有必要為一個很少使用的where子句單獨建立索引。 **
**建立索引會占用磁盤空間

**

MySQL的主從

定義
- 主從同步使用數(shù)據(jù)可以從一個服務(wù)器上復(fù)制到其它服務(wù)器上，在復(fù)制數(shù)據(jù)時，一個服務(wù)充當(dāng)主服務(wù)器（master)，其余的服務(wù)器充當(dāng)從服務(wù)器(slave)，因為復(fù)制是異步進行的，所以從服務(wù)器不需要一直連接主服務(wù)器，從服務(wù)器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務(wù)器，通過配置文件可以指定復(fù)制所有的數(shù)據(jù)庫、某個數(shù)據(jù)庫、甚至某個數(shù)據(jù)上的某個表
主從同步的好處
- 通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能，在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動態(tài)的調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個數(shù)據(jù)庫的性能。
- 提高數(shù)據(jù)的安全性，因為數(shù)據(jù)復(fù)制到了從數(shù)據(jù)庫，如果一旦主服務(wù)器掛掉了，可以使用從服務(wù)器上的數(shù)據(jù)
- 在主服務(wù)器上生成實時數(shù)據(jù)，在從服務(wù)器上分析這些數(shù)據(jù)，從而提高服務(wù)器的性能
主從同步機制
- Mysql服務(wù)器之間的主從同步是基于二進制日志機制，主服務(wù)器使用二進制日志來記錄數(shù)據(jù)庫的變動情況，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致性
- 在使用二進制日志時，主服務(wù)器的所有操作都會被記錄下來，然后從服務(wù)器會接收到該日志的一個副本。從服務(wù)器可以指定執(zhí)行該日志文件中的哪一類事件（比如只插入數(shù)據(jù)或只更新數(shù)據(jù)）。默認會執(zhí)行日志中的所有語句

配置主從同步的基本步驟
- 在主服務(wù)器上，必須開啟二進制日志機制和配置一個獨立的ID
- 在每一個服務(wù)器上，配置一個唯一的ID，創(chuàng)建一個用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號
- 在開始復(fù)制進程前，在主服務(wù)器上記錄二進制文件的位置信息
- 如果在開始復(fù)制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫，或者直接復(fù)制數(shù)據(jù)文件）
- 配置從服務(wù)器要連接的主服務(wù)器的ip地址和登陸授權(quán)，二進制日志文件名和位置
詳細配置主從同步方法
- 主和從的身份可以自己指定，我將虛擬機centOs中的Mysql作為主服務(wù)器，將window中的mysql作為從服務(wù)器，在主從設(shè)置之前，要保證centOs與windows間的網(wǎng)絡(luò)能互通
- 如果在設(shè)置主從同步前，主服務(wù)器上已有大量數(shù)據(jù)，可以使用mysqldump進行數(shù)據(jù)備份并還原到從服務(wù)器上實現(xiàn)數(shù)據(jù)復(fù)制
- 在主服務(wù)器上執(zhí)行命令進行備份
  - ```
  mysqldump -uroot -pmysql --all-databases --lock-all-tables > /tmp/master_data.sql
```
- 說明
  - -u：用戶名
  - -p：密碼
  - --all-databases：表示導(dǎo)出所有數(shù)據(jù)庫
  - --lock-all-tables：執(zhí)行操作時鎖住所有表，防止操作時數(shù)據(jù)被修改
  - /tmp/master_data.sql：導(dǎo)出文件存放的位置
- 在從服務(wù)器上讀取剛在主服務(wù)器上生存的數(shù)據(jù)文件
  - ```
  mysql -uroot -p密碼 新數(shù)據(jù)庫名 < master_data.sql
```
- 編寫mysqld的配置文件，設(shè)置Log_bin和server-id （注：把#刪除）
  - ```
  sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
```
- ```
log_bin  = var/log/mysql/mysql-bin.log
server-id = 1
```
  - 重啟mysql服務(wù)
  - ```
  server mysql restart
```
- 登陸主服務(wù)器中的Mysql,創(chuàng)建用于從服務(wù)器同步數(shù)據(jù)使用的賬號
  - ```
  mysql -uroot -p密碼
```
- ```
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
```
  - ```
  FLUSH PRIVILEGES;
```
- 獲取主服務(wù)器二進制日志信息
  - ```
  SHOW MASTER STARTS;
```
- 配置從服務(wù)器的mysqld文件，server-id與主服務(wù)器不一致即可，然后重新啟動
- 在從服務(wù)器進入mysql中執(zhí)行以下命令
  - ```
  change master to master_host='主服務(wù)器ip地址' ,master_user=’slave',master_password='slave',master_log_file='mysql-bin.000006',master_log_pos=590
```
- 注：以上配置的master_log_file 和 master_log_pos 可通過在主服務(wù)器上進入mysql輸入以下命令查看
- ```
show master status;
```
- 開啟同步，查看同步狀態(tài)(進入mysql，執(zhí)行以下命令)
  - ```
  start slave;
```
- ```
show slave status \\G;
```
  - **在顯示的信息中看到 Slave_IO_Runnig: YES 和 Slave_SQL_Running:YES 就表示同步成功 **
  - 注：如果沒有顯示YES，說明沒配置成功，原因可能是從命令中的 change master中的配置沒配置好，要仔細檢查

聲明：本文內(nèi)容及配圖由入駐作者撰寫或者入駐合作網(wǎng)站授權(quán)轉(zhuǎn)載。文章觀點僅代表作者本人，不代表電子發(fā)燒友網(wǎng)立場。文章及其配圖僅供工程師學(xué)習(xí)之用，如有內(nèi)容侵權(quán)或者其他違規(guī)問題，請聯(lián)系本站處理。舉報投訴