MySql學習筆記四 5.3.數據類型 1. 數值型 整型 小數 定點數 浮點數 2. 字元型 較短的文本:char, varchar 較長的文本:text, blob(較長的二進位數據) 3. 日期型 原則:所選擇類型越簡單越好,能保存數值的類型越小越好 5.3.1.數值型 5.3.1.1.整型 ...
MySql學習筆記四
5.3.數據類型
- 數值型
- 整型
- 小數
- 定點數
- 浮點數
- 字元型
- 較短的文本:char, varchar
- 較長的文本:text, blob(較長的二進位數據)
- 日期型
原則:所選擇類型越簡單越好,能保存數值的類型越小越好
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:外鍵,用於限制兩個表的關係,保證該欄位的值必須來自主表的關聯列的值,外鍵表中的引用的欄位必須是非空或者主鍵
添加約束的時機:
- 創建表時
- 修改表時
約束的添加分類
- 列級約束:六大約束都可以寫,但外鍵約束沒有效果
- 表級約束:除了非空和預設,別的都支持
例:
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讀取的內容就是臨時且無效的。
步驟 | 事務1 | 事務2 |
---|---|---|
1 | 開始事務 | |
2 | 開始事務 | |
3 | 讀取A表中的欄位A1 | |
4 | 將A表中的欄位A1更改 | |
5 | 讀取A表中的欄位A1 | |
6 | 將A表中的欄位A1更改 | |
7 | 回滾 | |
8 |
- 不可重覆讀:對於兩個事務T1,T2, T1讀取了一個欄位,然後T2更新了該欄位之後,T1再讀取同一個欄位,值就不同了
步驟 | 事務1 | 事務2 |
---|---|---|
1 | 開始事務 | |
2 | 查詢A表中的欄位A1(並未提交事務) | |
3 | 開始事務 | |
4 | 將A表中的欄位A1更改 | |
5 | 提交 | |
6 | 查詢A表中的欄位A1(並未提交事務) | |
7 | 兩次查詢A表中的A1不一致 |
- 幻讀:對於兩個事務T1,T2,T1從一個表中讀取了一個欄位,然後T2在該表中插入了一些新的行,之後如果T1再次讀取同一個表,就會多出幾行。
步驟 | 事務1 | 事務2 |
---|---|---|
1 | 開始事務 | |
2 | 查詢A表中的數據(結果為多行,並未提交事務) | |
3 | 開始事務 | |
4 | 插入數據 | |
5 | 提交 | |
6 | 查詢A表中的數據(結果為多行,並未提交事務) | |
7 | 兩次查詢A表中數據行數不一致 |
資料庫事務的隔離性:資料庫系統必須具有隔離併發運行各個事務的能力,使它們不會相互影響,避免各種併發問題
有一個事務與去其他事務隔離的程度稱為隔離級別,資料庫規定了多種事務隔離級別,不同隔離級別對應不同的干擾程度,隔離級別越高,數據一致性就也好,但併發性越弱。
臟讀 | 不可重覆讀 | 幻讀 | |
---|---|---|---|
read uncommitted | √ | √ | √ |
read committed | X | √ | √ |
repeatable read | X | X | √ |
serializable | X | X | X |
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 回滾點名稱; #回滾到保存點
保存點之前的事務正常提交