備份和恢復命令

備份庫

直接在cmd視窗中直接輸入，結束不需要輸入；

mysqldump -h埠號 -u用戶名 -p密碼 資料庫名>備份地址

恢復庫

在cmd視窗中進行
1、連接資料庫
mysql -u用戶名 -p密碼
2、創建資料庫
create database 庫名
3、切換到可用資料庫
use 庫名
4、進行恢復
source 備份檔案地址

授權：

新用戶資訊增改

1.創建用戶:
# 指定ip：192.118.1.1的用戶登錄
create user '用戶名'@'192.118.1.1' identified by '密碼';
# 指定ip：192.118.1.開頭的用戶登錄
create user '用戶名'@'192.118.1.%' identified by '密碼';
# 指定任何ip的用戶登錄
create user '用戶名'@'%' identified by '密碼';
2.洗掉用戶
drop user '用戶名'@'IP地址';
3.修改用戶
rename user '用戶名'@'IP地址' to '新用戶名'@'IP地址';
4.修改密碼
set password for '用戶名'@'IP地址'=Password('新密碼');

用戶權限管理

#查看用戶權限
show grants for '用戶名'@'IP地址'
1、授權
#授權用戶僅對某檔案有查詢、插入和更新的操作
grant select,insert,update on 檔案名 to '用戶名'@'IP地址';
#授權所有的權限，除了grant這個命令，這個命令是root才有的，用戶對db1下的t1檔案有任意操作
grant all privileges  on db1.t1 to '用戶名'@'IP地址';
#授權用戶可以對db1資料庫中的所有檔案執行任何操作
grant all privileges  on db1.* to '用戶名'@'IP地址';
#授權用戶可以對所有資料庫中檔案有任何操作
grant all privileges  on *.*  to '用戶名'@'IP地址';
 
2、取消權限
# 取消用戶對db1的t1檔案的任意操作
revoke all on db1.t1 from '用戶名'@'IP地址';  
# 取消來自遠程服務器的mjj用戶對資料庫db1的所有表的所有權限
revoke all on db1.* from '用戶名'@'IP地址';  
# 取消來自遠程服務器的mjj用戶所有資料庫的所有的表的權限
revoke all privileges on *.* from '用戶名'@'IP地址';

為什么使用視圖

多表的聯合查詢，最多也才3張表，如果面臨更多的表，為了簡化連表操作，可以使用MySQL中的視圖

好處

1、簡化sql陳述句

2、提高了sql的重用性

3、保護基表的資料，提高了安全性

創建視圖

create view 視圖名
as
查詢陳述句;

修改視圖

方式一

create or replace view 視圖名
as
查詢陳述句;

方式二

alter view 視圖名
as
查詢陳述句

洗掉視圖

drop view 視圖1，視圖2,...;

查看視圖

desc 視圖名;
show create view 視圖名;

視圖和表的對比

什么是索引

==索參考于快速找出在某個列中有一特定值的行，避免全表掃描==

MySQL索引，默認是B+樹索引

查詢表中的索引

show index from 表名

索引的優缺點

優點：

1、所有的MySQL列型別（欄位）都可以被索引，也就是可以給任意欄位設定索引

2、大大加快資料的查詢速度

缺點：

1、創建索引和維護索引要耗費時間，并且隨著資料量的增加所耗費的時間也會增加

2、索引也需要占空間，如果有大量的索引，索引檔案可能會比資料檔案更快達到上限值

3、當對表中的資料進行增、刪、改時，索引也需要動態的維護，降低了資料的維護速度

索引的分類

單列索引（普通索引、唯一索引、主鍵索引）、組合索引、全文索引、空間索引

創建索引

普通索引(b+樹索引)

alter table table_name add index index_name(column_name) using btree;
create unique index index_name on table_name(column_name);

組合索引

唯一索引

全文索引

什么是事務

事務是作為單個邏輯單元執行的一系列操作

多個操作作為一個整體向系統提交，要么執行、要么都不執行，事務是一個不可分割的作業作業邏輯單元

四大特性（ACID）

原子性（A -- Atomicity）：原子是參與化學反應中最小的粒子，不能再分割了，也就是說事務就是最小的，不能再分割了，如果把事務都分割了，就會出現問題

一致性（C -- Consistency）：在事務執行前資料庫的資料處于正確的狀態，而事務執行完成后資料庫的資料還是處于正確的狀態，即資料完整性約束沒有被破壞

隔離性（I -- Isolation）：事務與事務之間互不干擾

持久性（D -- Durability）：通過事務進行操作后的資料，是永久保存的

注意：在實際開發中，經常會打破隔離性，當多個事務共同操作同一張表的時候，一旦打破了隔離性，就會出現安全問題

存盤引擎

存盤引擎：RDBMS中，決定了資料如何存盤，如何獲取，如何控制事務，如何控制外鍵等一系列功能的一套程式

**常用引擎：**InnoDB，MyIsam

InnoDB與MyIsam的區別

1. InnoDB支持事務，支持外鍵；而MyIsam不支持事務，不支持外鍵
2. InnoDB由于受到事務和外鍵的影響，所以對資料的存盤以及查詢效率偏低；MyIsam相反偏高
3. InnoDB在存盤時，表檔案是2個：frm，ibd；而MyIsam是3個檔案，分別存盤frm，MYD，MYI
4. InnoDB是MYSQL 5.5之后的默認存盤引擎；而MyIsam是5.5之前的默認存盤引擎

事務操作

方式一

開啟事務：start transaction;

提交事務：commit;

==注意==：當事務開啟之后，只有執行了commit，資料才會真的改變，如果沒有執行commit，資料還原

回滾事務：rollback;

方式二

修改默認的提交方式，默認是自動提交，我們要改成手動提交

show variables like '%autocommit%'

set @@autocommit=0（默認為1，自動提交；0，手動提交）

隔離級別

隔離等級越高，資料庫事務并發執行能力越差，能處理的操作越少，因此在實際專案開發中為了考慮并發性能一般使用讀已提交隔離級別，他能避免丟失更新和臟讀，盡管不可重復讀和幻讀不能避免，但可以在可能出現的場合使用悲觀鎖或樂觀鎖來解決這些問題，

什么是幻讀、不可重復度、臟讀

1、臟讀：事務A讀取了事務B更新的資料，然后B回滾操作，那么A讀取到的資料是臟資料，讀取到未提交的資料，

2、不可重復讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的程序中，對資料作了更新并提交，導致事務A多次讀取同一資料時，結果不一致，

3、幻讀：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束后發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀

隔離級別與更新丟失的情況

• 第一類更新丟失
  • 事務A撤銷時，把已經提交的事務B的更新資料覆寫了
• 第二類更新丟失
  • 事務A覆寫事務B已經提交的資料，造成事務B所做的操作丟失

設定隔離級別

set session transaction isolation level 隔離級別

查看隔離級別(當前客戶端)

select @@tx_isolation

鎖

樂觀鎖

==默認存在==，什么都不做，就是樂觀鎖，總是樂觀的認為，在維護這個資料的時候，沒有其他人來維護，

insert update delete 在執行SQL的同時，才給資料加上了樂觀鎖，

可能會導致的問題

age:12
A用戶:update table set age=32 where id=1
B用戶:update table set age=12 where id=1
A用戶:select * from table where id=1
這個時候，A用戶查看的資料為B修改過的，自己的修改已經被覆寫

悲觀鎖

總是擔心，在自己修改資料的時候，有其他人，將這個資料修改，所以在修改資料之前，提前鎖住資料

==默認沒有開啟，需要自己開啟==

開啟命令

#在查詢陳述句后加for update就可以開啟（悲觀排他鎖）
select * from book for update

優缺點

優點：準確性高，更加安全

缺點：效率太低，一旦一個用戶執行悲觀鎖，那么其他用戶都無法查看這個表的資料，也無法進行修改，除非執行悲觀鎖的用戶commit

行鎖

只會鎖住一行資料

select * from book where id=1 for update

表鎖

會鎖住整個的一個表

select * from book for update

排他鎖

A用戶在給表加排他鎖以后，那么其他用戶都無法對表進行操作、查看、加鎖

//表級排他鎖
lock table book write
//解鎖
unlock table

共享鎖

所有的用戶都可以對表進行加鎖，但是，所有的用戶都無法對表進行操作，包括上鎖的用戶

//表級共享鎖
lock table book read
//解鎖
unlock table

死鎖

兩個執行緒自己各自持有自己資料的鎖，但互相都想鎖住被對方鎖住的資料，就產生了死鎖

系統檢索到死鎖，會自動釋放一個鎖

標籤：其它

上一篇：TDSQL PG版再升級，深度參與開源生態建設

下一篇：在 SQL Server 中查找活動連接和死鎖