32. 基本資料型別、約束條件

hbutmeng發表於2024-10-11
資料型別

1. 基本資料型別

1.1 整型

整數型別 所佔位元組 所佔位數

無符號數的取值範圍

unsigned

 有符號數的取值範圍
TINYINT 1 8 0~255 -128~127
SMALLINT 2 16 0~65535 -32768~32767
MEDIUMINT 3 24 0~16777215 -8388608~8388607
INT 4 32 0~4294967295 -2147483648~2147483647
BIGINT 8 64 0~18446744073709551615 -9223372036854774808~9223372036854774807

1. 無符號數在建立表的時候要給列加約束條件unsigned

2. 對於所有整數型別而言，括號內的引數代表顯示寬度，而不是最大長度

顯示寬度並不影響儲存大小

　 顯示寬度一般與zerofill配合使用，在MySQL 8.0.17及以後的版本中已經不推薦使用顯示寬度

1.2 浮點型

兩種浮點型：FLOAT、DOUBLE

這兩種型別只能用來表示近似數值，可能會導致精度損失。

浮點數不適用於要求精確結果的計算，比如財務應用。

如果需要更高的精度，應該使用DECIMAL型別。

浮點數型別 中文名稱 小數點後有效位數 語法 語法解釋 所佔位元組 所佔位數 取值範圍
FLOAT 單精度浮點數 7位 FLOAT(M, D)

M是數字的總位數(精度),包括小數點左邊和右邊,範圍是0~255

D是小數點後的位數(標度),必須小於等於M,範圍是0~30，

 4 32 -2^128~2 ^128 換算成小數約等於：
−3.40282347×10^38~3.40282347×10^38
DOUBLE 雙精度浮點數 15位 DOUBLE(M, D) 同上 8 64

-2^1024~ 2^1024 換算成小數約等於：

−1.7976931348623157×10^308~
1.7976931348623157×10^308

一個定點數型別：DECIMAL

DECIMAL不是浮點型，但它用於儲存定點數，適合需要精確計算的場景，如金融資料。

DECIMAL型別儲存為字串，具有更高的精度和範圍。

語法：DECIMAL(M, D)　　

解釋：M是數字的總位數(精度)，包括小數點左邊和右邊，範圍是1~65

　　　D是小數點後的位數(標度)，必須小於等於M，範圍是0~30

如果不指定 m 和 d，MySQL 會使用預設值 DECIMAL(10,0)

浮點型與定點數型別應用：

使用三種型別分別建立三個表

插入資料，小數點後的位數為22位

檢視插入的資料

float的前7位有效，之後的資料發生變化

num的前15位有效，之後的資料發生變化

decimal的有效位數與定義時保持一致

1.3 字串

[1]CHAR

固定長度：CHAR是固定長度的字串資料型別，佔用固定長度的空間，不足的部分用空格填充

效能：由於CHAR的長度是固定的，在處理大量相似長度的字串時，具有更快的讀寫速度，因為資料庫引擎不需要計算每個值的實際長度

空間效率：如果很多資料遠小於最大長度，將導致空間浪費

[2]VARCHAR

可變長度：VARCHAR是可變長度的字串資料型別，只佔用實際字串所需的長度加上一個或兩個位元組（用於儲存字串長度資訊）。

效能：VARCHAR 在處理變長字串時更高效，特別是當儲存的字串長度差異較大時。

空間效率：對於長度變化很大的資料，VARCHAR 通常更節省空間，因為它不會為較短的字串分配多餘的空間。

[3]實際應用

使用char可以不指定長度，預設長度為1；

不指定長度時插入字串的長度不能超過1。

使用varchar必須指定長度，不指定長度會報錯。

資料長度超過varchar指定長度會報錯

[4]補充知識點：統計字元長度char_length

語法：select char_length(列名) from 表名;

varchar查詢到的字串長度為實際長度：

char查詢到的字串長度也為實際長度：

[5]補充知識點：系統變數sql_mode與PAD_CHAR_TO_FULL_LENGTH模式

sql_mode是一個系統變數，它用於控制MySQL如何解析和執行SQL語句，以及如何進行資料校驗。這個變數可以看作是一組約束和規範，確保資料的準確性、完整性和一致性。

這包括對無效資料的處理、日期格式的嚴格程度、是否允許隱式型別轉換等。

使用模糊查詢檢視sql_mode：SHOW VARIABLES LIKE 'sql_mode';

PAD_CHAR_TO_FULL_LENGTH 是MySQL中的一個SQL模式選項，它影響CHAR型別欄位值在檢索時的行為。預設情況下，當檢索CHAR型別欄位的值時，MySQL會刪除尾部的空格。如果啟用了PAD_CHAR_TO_FULL_LENGTH模式，則不會刪除尾部空格，檢索到的CHAR值將填充到它們的全長度。

這個模式不適用於VARCHAR型別的欄位，因為VARCHAR欄位在檢索時保留尾隨空格。需要注意的是，從MySQL 8.0.13開始，PAD_CHAR_TO_FULL_LENGTH已經不被推薦使用，並且在未來的MySQL版本中可能會被移除。

臨時修改sql_mode：set sessison sql_mode="......,PAD_CHAR_TO_FULL_LENGTH ";　　　　

永久修改sql_mode：set global sql_mode="......,PAD_CHAR_TO_FULL_LENGTH ";

注意：在執行以上修改sql_mode的命令時不能忽略系統原有的sql_mode，否則會造成原有sql_mode丟失

臨時新增PAD_CHAR_TO_FULL_LENGTH ：

新增PAD_CHAR_TO_FULL_LENGTH的效果是：char_length變成了定長而不是實際長度

1.4 時間型別

DATE：年月日 　　　　　　　　YYYY-MM-DD
TIME：時分秒 　　 　　　　　　HH:MM:SS
DATETIME：年月日時分秒 　　 YYYY-MM-DD HH:MM:SS
YEAR：年 　　　　　　　　　 YYYY 

create table t1(
    concrete_time datetime,
    nian year,    
    NianYueRi date,
    ShiFenMiao time
);

時間資料型別為字串，一定要用單引號或雙引號括起來。

insert into t1 (concrete_time, nian, NianYueRi, ShiFenMiao) values(
    '1999-06-09 09:25:36',
    '2000',
    '2001-09-01',
    '09:26:31'
);

1.5 字串物件之列舉型別與集合型別

[1]列舉型別

多個預定義的值中選一個

enum(值1,值2,值3,...)　　括號內的值必須為字串型別，用引號括起來

create table student(
    id int,
    name varchar(10),
    grade enum('1','2','3','4','5','6')
);

只能選擇一個值不能選擇多個值

insert into student (id,name,grade) values(1,'avril','1');
insert into student (id,name,grade) values(2,'lavigne','1,2');

[2]集合型別

多個預定義的值中選多個

set(值1,值2,值3,...)　　括號內的值必須為字串型別，用引號括起來

create table t1(
    id int,
    name varchar(10),
    hobby set('run', 'game', 'jqk')
);

既可以選多個值，也可以選一個值

insert into t1 (id, name, hobby) values(1, 'avril', 'run');
insert into t1 (id,name,hobby) values(2, 'lavigne', 'run,game,jqk');

2. 約束條件

2.1 建立表語法回顧

create table 表名(
       列名1 資料型別[(最多字元個數)]  [約束條件],
       列名2 資料型別[(最多字元個數)]  [約束條件],
       ...
       列名n 資料型別[(最多字元個數)]  [約束條件]
);

列名和資料型別必填，中括號裡的內容選填。
列名不可重複。

2.2 null / not null

如果約束條件為not null，那麼在插入資料時該列的值不能為空

如果約束條件為null，那麼在插入資料時該列的值可以為空

建表：

create table t1(id int not null, name varchar(10) null);

插入資料：

insert into t1 (id) values(1);　　--id不能為空，name可以為空，執行成功
insert into t1 (name) values('avril');　　--id不能為空，name可以為空，執行失敗

2.3 唯一性約束

關鍵字：unique　　列的值必須唯一

建表：

create table t1(id int, name varchar(10) unique);

插入資料：

insert into t1 values(1, 'abc');
insert into t1 values(2, 'abc');　　--name的值傳入重複，報錯

2.4 唯一性+不為空≈主鍵

unique+not nulll建表：

create table t1(id int not null unique, name varchar(20));

primary key建表與unique+not null建表效果幾乎一樣：

create table t2(id int primary key, name varchar(20));

主鍵約束：

主鍵約束是一種資料庫約束，用來強制資料的完整性和唯一性。當一個或一組欄位被定義為主鍵時，這個約束會自動應用到這些欄位上。
主鍵約束保證了：
該列或這組列的值在整個表中是唯一的（不允許重複）。
該列或這組列不能包含NULL值。

主鍵欄位：

被設定為主鍵的單個欄位或多欄位組合。可以是一個欄位作為主鍵，也可以是多個欄位一起構成複合主鍵。
單一主鍵：例如，id INT PRIMARY KEY，這裡id就是主鍵欄位。
複合主鍵：例如，PRIMARY KEY (column1, column2)，這裡column1和column2共同構成了複合主鍵。

建立一個複合主鍵示例：

create table t3(id int,name varchar(20),hobby varchar(10),primary key(id, name));

主鍵值：

主鍵值是插入到主鍵欄位中的具體值。這些值必須滿足主鍵約束的要求，即唯一且非空。

主鍵的作用：

新增主鍵primary key的欄位既有unique+not null唯一、非空的約束，還會在定義主鍵時自動在該列上建立一個唯一索引，這個索引有助於提高基於主鍵的查詢、插入和更新操作的效能。

primary key 與unique+not null區別：

一個表只能有一個主鍵，但可以有多個NOT NULL UNIQUE欄位。

2.5 AUTO_INCREMENT

自增，是列的約束條件，通常用於主鍵，自動生成唯一的數字

程式碼示例：

create table t1(id int primary key auto_increment, name varchar(20));
insert into t1(name) values('avril'),('lavigne');

自動生成後，一個主鍵自然數只會出現一次；若刪除改行記錄，或者delele from 表名清空表中所有資料後，重新遞增記錄時，主鍵字然數會跳過直接+1

指定主鍵起始位置：

使用表修改語句將AUTO_INCREMENT的欄位數指定為自定義位置

alter table 表名 AUTO_INCREMENT=指定主鍵起始位置；

如果表中主鍵數值間隔，插入資料時以最後的主鍵數值為準遞增。

如果表中主鍵數值間隔，指定起始位置的數值只能大於表中最大的數值，起始位置為表中間隔的數值無效。

相關文章