第八天
-
- 資料備份
- 權限管理
- 索引
- 視圖
- 觸發器
- 事務的基本操作
- 使用python連接配接資料庫
- ORM：（Object Relational Mapping）對象關系映射
- 關系型資料庫三範式
- 資料庫優化
資料備份
先退出mysql 環境
資料的導出:
備份表：
格式 mysqldump -uroot -p 庫名 表名>備份檔案位址\表名.sql
備份庫：
mysqldump -uroot -p 庫名>備份檔案位址\備份檔案名.sql
資料的導入：
mysql -uroot -p 庫名<備份檔案位址\備份檔案名.sql
注：表可以直接導入，
庫要先建立一個空的庫，在将資料導入。
權限管理
使用者：連接配接的賬戶
權限：語序使用者操作的範圍
組：權限的集合
1、修改密碼
mysql 有一個預設的mysql 庫有一個user表，這個表中存放的就是咱們使用者資訊
切換到mysql庫中，執行 update user set authentication_string=pasword(‘1234’) where User=‘root’ and host=‘localhost’;
重新啟動服務
然後再去連接配接資料庫 此時會發現不輸入密碼連接配接失敗，需要使用密碼登入
如果忘記密碼怎麼辦
1.修改配置檔案 在my.cnf 配置檔案中的mysqld 最後一行添加
skip-grant-tables 跳過權限認證
2.重新開機服務
不用輸入密碼可以直接登入到資料庫
執行修改密碼操作
3.把配置檔案修改回來
4.重新開機服務 使用新密碼登入即可
2、建立使用者
格式：create user ‘使用者名’@'ip位址’identified by ‘密碼’
1.建立使用者名為lisi 任何位置都可以登入
create user 'lisi'@'%' identified by '12345'；
2.建立使用者wangwu 隻能在本地登入的使用者
create user 'wangwu'@'localhost'identified by '12345';
3.建立使用者wq隻能在ip位址10.10.107.85 的裝置上連接配接資料庫
create user 'wq'@'10.10.103.85' identified by '123456';
window:
ipconfig 檢視ip
3、給使用者授權允許遠端連接配接
格式：grant 權限 on 庫名.表名 to 使用者名@‘位址’
1.給lisi 添加 所有庫所有表的 查詢權限
grant select on *.* to lisi @'%';
2.給遠端連接配接的zhaoliu 使用者 添加 demo1 資料庫下所有表的權限
grant all on demo1.* to 'zhaoliu'@'%'
4、删除使用者
格式：drop user ‘使用者名’@‘ip位址’
索引
一種高效擷取資料的存儲結構，類似于子字典的目錄
(1:0f000001)
為什麼要使用索引：可以提升查詢資料的效率 減少io的操作
建立索引時，資料庫會額外的建立一張表，來維護索引，會占用磁盤 的資源，一般索引的空間比資料大
索引一般支援的資料結構，b+tree b-tree 哈希
b+tree的基本結構
B+tree的特點：
有三層 分别是 根節點，子節點和葉子節點
對稱
根節點不存儲資料，資料存儲在葉子節點
葉子節點同時儲存了寫一個資料節點的位址
1、索引的優缺點
提升資料的查詢速度，減少io操作
缺點維護成本高，會降低其他操作的執行速度
添加索引可以增加查詢的速度，但不是索引越多越好。
添加索引一般是給經常用作where 條件的字段添加
最左原則：Select * from user where username=’zhansgan’ and age=18;
2、索引分類
普通索引：index 可以為空 可以重複 當我們使用實體外鍵時就預設給該字段添加了普通索引
唯一索引：unique 可以為空 不可以重複
主鍵索引：primary key 不能為空且唯一
多列索引：index() 将多個字段綁定到一起添加一個索引
3、添加索引
格式：alter table 表名 add 索引類型 索引名(字段名);
注：如果不添加索引名，預設的索引名以字段名命名
删除主鍵索引的格式：
删除主鍵索引要先将主建索引的自增屬性删除 然後才能删除主鍵索引
Alter table 表名 drop primary key;
删除主鍵索引和删除其他索引方式不一樣
添加外鍵：alter table 表名 add foreign key(目前表外鍵字段) references 關聯的表名(關聯的字段);
4、檢視索引
格式：show index from 表名;
5、删除索引
格式;drop index 索引名 on 表名;
視圖
view
1、定義：當查詢的複雜度很高時，指多字段，多關系查詢，會導緻sql語句混亂
是一張虛拟的表，有字段和資料，隻存放查詢語句
視圖有字段有行，但是視圖基于源表
2、特點：
簡單：視圖建構了一個虛拟的表，表裡的資料是來源于複雜的查詢語句，我們将複雜的查詢語句存入視圖，使用是直接調用視圖
安全：資料據有對庫和表的權限管理，但是沒有對字段權限，可以通過視圖來實作權限的功能
資料的獨立性：視圖基于源表，當源表的結構發生變化時，不會對視圖産生影響
使用視圖時，一般存的都是複雜的查詢，如果存的是簡單的查詢，在使用視圖時，會作為複雜查詢來去執行，會降低查詢效率
會增加資料庫的維護和管理成本，會對資料遷移造成很大影響
3、建立視圖
CREATE VIEW 視圖名 AS(查詢語句);
4、檢視視圖
SHOW TABLE STATUS WHERE COMMENT=’view’;
5、使用視圖
SELECT * FROM 視圖名;
6、删除視圖
DROP VIEW 視圖名;
觸發器
trigger
由誰觸發：觸發器是一個特殊的存儲過程，不需要手動觸發
什麼時候會觸發：當我們在做 添加 删除 修改操作時會自動觸發觸發器
修改資料的預設結束符 \d ||
1.建立觸發器
建立完觸發器記得把結束符改回來
CREATE TRIGGER 觸發器名字 觸發時機 觸發事件
ON 表名
FOR EACH ROW
BEGIN
觸發器要執行的sql語句
EDN;
給user表建立一個觸發器，當向user表中添加資料時 給userinor也添加一條資料
CREATE TRIGGER add_user AFTER INSERT ON user FOR EACH ROW
BEGIN
INSERT INTO userinfo(name) values(‘沾士幹’);
END;
資料備份 當我們删除user表中的資料時 将删除的資料備份的 back_user表中
create trigger back_data before delete on user for each row
begin
insert into back_user values(old.id,old.name,old.age,old.gender,old.phone,old.address);
end
注：for each row 是多次執行的意思，如user添加多個資料，觸發器會執行多次給userinor添加資料
2.使用觸發器
3.檢視觸發器
Select * from information_schema.trigger;
4.删除觸發器
Drop trigger 觸發器名字
資料備份 當我們删除user表中的資料時 将删除的資料備份的 back_user表中
事務的基本操作
begin 開啟一個事物
commit 送出事物
rollback 復原
是針對資料的
使用python連接配接資料庫
pymysql：是一個第三方的子產品，并且要求你的python版本為3.5以上
Python2中使用的是 mysqldb
1.安裝： pip install pymysql（在cmd中的資料庫bin目錄下中執行）
2.使用：
1.連接配接資料庫
2.建立遊标
3.定義sql語句
4.執行sql語句
5.關閉連接配接
擷取傳回内容的方法
fetchone() 擷取一條資料
Fetchall() 擷取傳回的所有的資料
Rowcount 屬性 擷取操作影響的行數
第一種方法
# 導包
import pymysql
# 1.連接配接資料庫
db=pymysql.connect(host='localhost',user='root',password='123',database='demo',cursorclass=pymysql.cursors.DictCursor)
# 2.建立遊标對象
cursor=db.cursor()
# 3.定義sql語句
# sql='select version()'
# 定義查詢的sql語句
# sql='select * from user'
# 定義添加資料資料
# sql = 'insert into user(id,name) values(25,"明明")'
# 定義更新資料
# sql = 'update user set age=16 where name="明明"'
# 删除資料
# sql = "delete from user where name='靠臉'"
# 4.執行sql語句
cursor.execute(sql)
# 擷取傳回的結果
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchall())
print(cursor.rowcount)
# 如果操作對資料庫中的資料産生了影響必須執行送出(即查詢不需要送出，增删改都需要送出不然資料庫裡的資料不會改變)
# db.commit()
# 5.斷開連接配接
cursor.close()
db.close()
面向對象
import pymysql
class MyDB:
def __init__(self,h='localhost',u='root',p=None,db=None):
# 連接配接資料庫 建立遊标對象
self.db=pymysql.connect(host=h,user=u,password=p,database=db,cursorclass = pymysql.cursors.DictCursor)
self.cursor=self.db.cursor()
# 做查詢
def query(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchall()
# 做添加 删除 修改
def change(self,sql):
self.cursor.execute(sql)
self.db.commit()
return self.cursor.rowcount
# 析構函數
def __del__(self):
# 關閉資料庫連接配接
self.cursor.close()
self.db.close()
# 測試用的，隻支援目前裡面用，别的地方導入pysql2這個子產品下面不會執行
if __name__ == '__main__':
# 執行個體化對象
database = MyDB(p='123',db='demo')
res = database.query('select * from user')
print(res)
from pysql2 import MyDB
# 執行個體化資料庫對象
database = MyDB(h='localhost',u='root',p='123',db='demo')
# 插入資料庫
sql = 'insert into user(id,name) values(5,"小白")'
print(database.change(sql))
# 查詢資料
sql= 'select * from user'
print(database.query(sql))
ORM：（Object Relational Mapping）對象關系映射
ORM是一個操作資料庫的架構
ORM會将python的代碼翻譯成對應資料庫的sql語句
ORM會将資料庫中的表映射成 python中的類
一張表就是一個類
将表中的字段映射成類的屬性
優點：
降低程式和資料庫之間的耦合
是以我們在使用ORM 時，可以不關心你用的是什麼資料庫，隻關心我們的業務邏輯，即使開發人員不會sql語句 也能和資料庫進行互動
即使開發人員不會sql語句 也能和資料庫進行互動
缺點：
降低查詢速度
ORM 生成的sql不是最優的sql語句 執行效率會比較低
python 中有一個 sqlalchemy,仿照的是Django的架構的orm
flask 有一個orm 插件 flask-sqlalchemy
安裝：pip install sqlalchemy
使用：
1.導包
2.建立連接配接
3.聲明一個基類
4.建立類 資料模型
5.做操作
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
# 類似于pymysql 中的遊标
from sqlalchemy.orm import sessionmaker
# 1.建立連接配接
# 資料庫類型+資料庫操作的包：//使用者名:密碼@主機位址/你要操作的資料庫
# 安裝mysqlclient 在sqlalchemy是不需要+pymysql 即 資料庫類型：//使用者名:密碼@主機位址/你要操作的資料庫
db=sqlalchemy.create_engine('mysql+pymysql://root:[email protected]/sqlorm')
# 2.建立基類
base = declarative_base(db)
# 3.建立類 必須繼承基類 建立模型
class User(base):
#表名
__tablename__='user'
id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(32))#varchar(32)
age = sqlalchemy.Column(sqlalchemy.Integer)
class Userinfo(base):
__tablename__='userinfo'
id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
phone=sqlalchemy.Column(sqlalchemy.String(32))
class Shop(base):
__tablename__='shop'
id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
name=sqlalchemy.Column(sqlalchemy.String(32))
if __name__ == '__main__':
#執行資料庫遷移 建立表
base.metadata.create_all(db)
#綁定一個執行個體
s=sessionmaker(bind=db)
#建立會話對象 類似于遊标
session=s()
#添加
# 添加單個資料
# user = User(name='hello',age=17)
# session.add(user)
# session.commit()
#添加多條資料
# session.add_all([
# User(name='world',age=1),
# User(name='python',age=28),
# User(name='PHP',age=24),
# ])
# session.commit()
# 查詢
#查詢所有的資料 傳回一個清單
# res=session.query(User).all()
# for i in res:
# print(i.name,i.age)
#通過主鍵查詢一條資料 傳回一個對象
res=session.query(User).get(2)
print(res.name,res.age)
#條件查詢傳回的是一個清單
res=session.query(User).filter_by(name='hello').all()
print(res)
res=session.query(User).filter(User.name=='hello').all()
print(res)
#修改資料
res=session.query(User).get(1)
print(res.name)
res.name='HELLO'
session.commit()
#删除資料
# res=session.query(User).get(1)
# session.delete(res)
# session.commit()
關系型資料庫三範式
第一範式：字段要具有原子性，不可以繼續拆分，一般根據實際需求來去決定
| id | name | age | address |
|---|---|---|---|
| 1 | 張三 | 18 | 北京市昌平區回龍鎮 |
| id | name | age | sheng | shi |
|---|---|---|---|---|
第二範式：建立在第一範式基礎上，每一列資料必須被唯一區分，依賴于主鍵，每一個主鍵至少要有一個主鍵
第三範式：建立在第二範式的基礎上，一般應用于設計多表關系中，要求一個資料表中不包含已在其他表中已包含的非主鍵字段，因為會出現備援，表的資訊如果能被推導出來就不應該單獨設計一個字段來儲存，可以使用外鍵來關聯，而不是将另一張表中的非主鍵屬性直接寫在目前表中
| id | name | price | Type_id | shi |
|---|---|---|---|---|
| 1 | 超短裙 | 200 | 1 | 女裝 |
| 2 | 雪紡衫 | 200 | 1 | 女裝 |
| id | tname | 描述 |
|---|---|---|
| 1 | 女裝 | |
| 2 | 男裝 |
反三範式：允許部分字段備援
三範式：隻是我們設計表時的一個參考規則，實際表的設計要根據實際業務來設計
資料庫優化
1、庫和表結構優化
分庫分表： 當單個庫或者表中的資料量大時 資料庫的性能會變慢
垂直拆分
垂直拆分表：當一個表中的資料量比較大字段比較多時，建立一個附屬表，将表中不常用的字段存入附屬表，通過建立外檢進行關聯
垂直拆分庫；根絕不同的業務需求，将不同的表放入不同的庫中，一般會放到多個伺服器上
水準拆分
水準分庫分表：單表資料量太大 将資料水準拆分成多個表，多個表組合在一起才能組成一個完成的資料；将拆分的表放到不同的庫中
水準拆分面臨的問題：
主鍵如何保證唯一性
《1》制定每張表的id取值範圍
《2》通過時間或者地理位置
《3》通過趨勢遞增 雪花算法
水準分庫 會面臨 多表查詢會受到影響 事物也會受到影響
目前沒有人能解決這些問題，我們可以使用開源的架構産品來解決
但是不同的開源産品，所解決的問題也不相同，是以根據自己的需求來去選擇
2、結構優化
主從複制(讀寫分離) 添加緩存 一般使用非關系資料庫做為緩存資料庫 将資料存到記憶體中
3、sql 語句優化
允許部分字段備援，使用邏輯外鍵避免使用實體外鍵
添加索引：給查詢頻繁的條件添加索引，使用索引最左原則
查詢時 select 後面不使用*
減少資料庫的查詢的次數
sql關鍵字盡量大寫
使用關聯查詢替代嵌套子查詢
使用where條件過濾 避免全表查詢
Update修改時，避免修改索引字段所在的列
避免修改where後面字段