MySql學習筆記四

5.3.數據類型

1. 數值型
   • 整型
   • 小數
     • 定點數
     • 浮點數
2. 字元型
   • 較短的文本：char, varchar
   • 較長的文本：text, blob(較長的二進位數據)
3. 日期型

原則：所選擇類型越簡單越好，能保存數值的類型越小越好

5.3.1.數值型

5.3.1.1.整型

Tinyint： 一個位元組

Smallint：兩個位元組

Mediumint：三個位元組

Int, Integer：四個位元組

Bigint：八個位元組

預設是有符號，無符號整型是在類型後加unsigned

超出範圍時是距離範圍最近的臨界值

如果不設置長度，會有預設長度，長度只跟查詢結果的顯示寬度有關

zerofill會根據長度來補充0，但是如果使用該關鍵字會自動設置為無符號整型

5.3.1.2.小數

5.3.1.2.1.浮點型

float(M, D)：四個位元組

double(M, D)：八個位元組

M表示小數和整數的位數和，D表示小數點後的位數

不加(M, D)則會根據插入數值的精度來決定精度

5.3.1.2.2.浮點型

DEC(M, D)或DECTMAL(M, D) ： M個字元，最大取值範圍與double相同，比浮點型準確

不加(M, D)預設是(10, 0)

5.3.2.字元型

較短的文本：char, varchar(還有binary和varbinary 存儲較短的二進位類型)

較長的文本：text

較大的二進位：blob

char, varchar

char(M)：M個字元，0 <= M <= 255

varchar(M)：M個字元，0 <= M <= 65535

char代表固定長度字元，varchar代表可變長度字元，char的效率高於varchar的效率

ENUM, Set

ENUM('str1', 'str2',...) ：枚舉類型，不區分大小寫

Set跟ENUM相似，但是能選擇多個成員插入，成員間用,隔開

5.3.3.日期型

date: 四位元組，只保存年月日，範圍 1000-01-01 至 9999-12-31

datetime: 八位元組，保存年月日時分秒，範圍 1000-01-01 00:00:00 至 9999-12-31 23:59:59

timestamp: 四位元組，取值範圍19700101080001至2038年某個時間，timestamp和實際時區有關，受Mysql版本和SQLmode影響較大

5.4.約束

約束是一種限制，用於限製表中的數據，為了保證表中的數據的準確和可靠

六大約束：

• NOT NULL：非空，用於保證該欄位的值不為空
• DEFAULT：預設，給欄位設置預設值
• PRIMARY KEY：主鍵，保證欄位的值具有唯一性，並且非空
• UNIQUE：唯一，用於保證欄位的值具有唯一性，可以為空，但只能插入一個null
• CHECK：檢查約束（Mysql不支持）
• FOREIGN KEY：外鍵，用於限制兩個表的關係，保證該欄位的值必須來自主表的關聯列的值，外鍵表中的引用的欄位必須是非空或者主鍵

添加約束的時機：

1. 創建表時
2. 修改表時

約束的添加分類

• 列級約束：六大約束都可以寫，但外鍵約束沒有效果
• 表級約束：除了非空和預設，別的都支持

例：

create table 表名(
    欄位名 欄位類型 (列級約束),
    ...
    表級約束
)

5.4.1.創建表時添加約束

5.4.1.1.添加列級約束

示例：

create table 表名(
    欄位名 欄位類型 primary key, #主鍵
    欄位名 欄位類型 not null, #非空約束
    欄位名 欄位類型 check(欄位條件), #檢查約束
    欄位名 欄位類型 unique, #唯一約束
    欄位名 欄位類型 default 預設值, #預設約束 mysql不支持
    欄位名 欄位類型 references 外鍵表名(欄位名) #外鍵約束無效
)

5.4.1.2.添加表級約束

(constraint 約束名) 約束類型(欄位名)

constraint 約束名 可以省略

示例

create table 表名(
    欄位名1 欄位類型1,
    欄位名2 欄位類型2,
    ...
    欄位名n 欄位類型n,
    
    constraint pk primary key(欄位名), #主鍵約束
    constraint uq unique(欄位名), #唯一約束
    constraint ck check(欄位條件), #檢查約束
    constraint fk foreign key(欄位名) references 外鍵表名(欄位名) #外鍵約束
)

5.4.2.修改表時添加約束

• 添加非空約束

alter table 表名 modify column 列名 新類型 not null;
alter table 表名 modify column 列名 新類型 (null); #刪除

• 添加預設約束

alter table 表名 modify column 列名 新類型 default 預設值;
alter table 表名 modify column 列名 新類型 ; #刪除

• 添加主鍵約束

alter table 表名 modify column 列名 新類型 Primary key; #列級約束
alter table 表名 add (constraint 約束名) Primary key(列名); #表級約束
alter table 表名 drop primary key; #刪除

• 添加唯一約束

alter table 表名 modify column 列名 新類型 UNIQUE; #列級約束
alter table 表名 add (constraint 約束名) UNIQUE(列名); #表級約束
alter table 表名 drop index 約束名; #刪除

• 添加外鍵

alter table 表名 add (constraint 約束名) foreign key(欄位名) references 外鍵表名(欄位名); #表級約束
alter table 表名 drop foreign key 約束名; #刪除

5.5.標識列

又稱自增長列，可以不用手動的插入值，系統提供預設的序列值

一個表最多只能有一個標識列

標識列的類型只能是數值型

create table 表名(
    欄位名1 欄位類型1 auto_increment,
    ...
    ) #創建
    
alter table 表名 modify column 列名 新類型 約束 auto_increment; #修改
alter table 表名 modify column 列名 新類型; #刪除

6.TCL（事務控制語言）

特性：ACID

• 原子性(Atomicity)：事務是一個不可分割的工作單位，事務中的操作要麼都發生，要麼都不發生
• 一致性(Consistency)：事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態
• 隔離性(Isolation)：一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的數據對併發的其他事務是隔離的，併發執行的各個事務之間不能相互干擾
• 持久性(Durability)：一個事務一旦被提交，它對資料庫中數據的改變就是永久性的，接下來的其他操作和資料庫故障不應該對其有任何影響

6.1.事務的創建

隱式事務：事務並沒有明顯的開啟和結束的標記,比如insert, delete, update

顯式事務：事務有明顯的開始和結束的標記，前提必須設置自動提交功能為禁用

示例：

set autocommit = 0; #開啟事務
(start transaction)
sql語句1
sql語句2
...
commit; #提交事務
rollback; #回滾事務

6.2.隔離級別

對於同時運行的多個事務，當這些事務訪問資料庫中相同的數據時。如果沒有採取必要的隔離機制，就會導致各種併發問題：

• 臟讀：對於兩個事務T1,T2，T1已經讀取了T2更新但還沒被提交的欄位之後，若T2回滾，T1讀取的內容就是臨時且無效的。

• 不可重覆讀：對於兩個事務T1,T2, T1讀取了一個欄位，然後T2更新了該欄位之後，T1再讀取同一個欄位，值就不同了

• 幻讀：對於兩個事務T1,T2，T1從一個表中讀取了一個欄位，然後T2在該表中插入了一些新的行，之後如果T1再次讀取同一個表，就會多出幾行。

資料庫事務的隔離性：資料庫系統必須具有隔離併發運行各個事務的能力，使它們不會相互影響，避免各種併發問題

有一個事務與去其他事務隔離的程度稱為隔離級別，資料庫規定了多種事務隔離級別，不同隔離級別對應不同的干擾程度，隔離級別越高，數據一致性就也好，但併發性越弱。

mysql中預設repeatable read

查詢隔離級別

select @@tx_isolation;
select @@transaction_isolation; #Mysql8值後換為這個

設置隔離級別

set session|global transaction isolation level 隔離級別;

6.3.回滾點

示例：

set autocommit = 0; #開啟事務
(start transaction)
sql語句1
savepoint 回滾點名稱
sql語句2
...
rollback to 回滾點名稱; #回滾到保存點

保存點之前的事務正常提交