一.數據類型

整數類型（integer types）

數據類型	字節	有符號范圍	無符號范圍	說明
tinyint	1	-128 ~ 127	0 ~ 255	非常小的整數
smallint	2	-32,768 ~ 32,767	0 ~ 65,535	小整數
mediumint	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中等整數
int	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295	常用整數
bigint	8	±9.2 × 101?	0 ~ 18,446,744,073,709,551,615	大整數

浮點類型（floating point types）

數據類型	字節	精度說明	說明
float(m,d)	4	約 7 位有效數字	單精度浮點，非精確計算
double(m,d)	8	約 15~16 位有效數字	雙精度浮點，非精確計算
decimal(m,d)	可變	精確小數，m=總位數，d=小數	精確計算，適合貨幣金額

字符串類型（string types）

數據類型	存儲大小	最大長度	說明
char(n)	固定 n 字節	0 ~ 255字符	固定長度，適合狀態等字段
varchar(n)	可變 + 1~2 字節	理論上最大65535字符	可變長度，常用文本字段
tinytext	最多 255 字節		很短的文本
text	最多 64kb		中等長度文本，不能索引全部
mediumtext	最多 16mb		較長文本
longtext	最多 4gb		超長文本
blob	同上（binary）		存儲二進制數據（圖片等）

1.char(n)若存入字符數小于n，則以空格補于其后，查詢之時再將空格去掉。所以char類型存儲的字符串末尾不能有空格，varchar不限于此

2.char(n)固定長度，char（4）不管存入幾個字符，都將占用4個字節，varchar是存入的實際字符數+1個字節（n<=255）或2個字節（n>255）,所以varchar(4)存入三個字符將占用4個字節

3.char類型的字符串檢索速度要比varchar類型的快

日期時間類型（date and time types）

數據類型	格式	范圍	說明
date	'yyyy-mm-dd'	1000-01-01 ~ 9999-12-31	僅日期
time	'hh:mm:ss'	-838:59:59 ~ 838:59:59	僅時間
datetime	'yyyy-mm-dd hh:mm:ss'	1000-01-01 ~ 9999-12-31	日期 + 時間
timestamp	同上	1970-01-01 ~ 2038-01-19（utc）	自動時區轉換
year	'yyyy'	1901 ~ 2155	表示年份字段

若定義一個字段為timestamp，這個字段里的時間數據會隨其他字段修改的時候自動刷新，所以這個數據類型的字段可以存放這條記錄最后被修改的時間

特殊類型（enum, set）

數據類型	說明
enum('a','b',...)	枚舉值，只能選擇一個
set('a','b',...)	集合值，可以選擇多個，最多 64 項

說明補充：

• 所有整數類型可加 unsigned 表示無符號。

• decimal 是精確存儲，float/double 是近似計算，避免用于金額。

• char 定長，varchar 變長，text 不支持全文索引默認值。

• timestamp 會受當前時區影響，datetime 不會。

二.約束和屬性設置（約束用戶輸入的信息）

表結構設計應同時考慮：數據類型 + 約束 + 屬性

約束(用于限制表中數據的合法性，保證數據的一致性和完整性)

簡寫	全稱	關鍵字	作用說明
PK	primary key	primary key	主鍵約束，唯一且非空。表中只能有一個。自動建立唯一索引，常用于 id 字段
UK	unique key	unique	唯一約束，字段值必須唯一但可為 null。可以有多個字段設置唯一約束
NN	not null	not null	非空約束，字段值不能為 null
FK	foreign key	foreign key	外鍵約束，強制當前字段值必須引用另一個表的主鍵，保證參照完整性，協調多個表

屬性(字段屬性，用于控制字段的行為或輔助信息,它們與約束協同定義字段語義與邏輯)

屬性	作用說明	示例語法
default	設置字段默認值，當插入數據時未指定該字段則使用此值	age int default 18
auto_increment	自動增長，字段值會自動遞增，常用于主鍵	id int primary key auto_increment
comment	字段注釋，僅供開發者查看，不影響數據或邏輯	username varchar(50) comment '用戶名'
unsigned	無符號整數，表示該字段不能為負數，適用于數值類型	price int unsigned

外鍵：也稱外鍵約束，外面的鍵，對數據進行約束，一張表的一個字段指向另一個表的主鍵，那么該字段就稱為外鍵

外鍵所在的表稱之為子表（附表）；外鍵所指向的主鍵所在的表稱之為父表（主表）

設置外鍵

實現將一個表的字段與另一個表的主鍵進行關聯

添加外鍵

1.在創建表的時候就增加外鍵：在表字段之后使用foreign key

語法格式：foreign key(外鍵字段) references 主表(主鍵);

創建外鍵關聯的父表

create table class(

id int primary key auto_increment,

name varchar(10) not null comment "班級姓名，不能為空",

room varchar(10) comment '教室：允許為空'

) charset utf8;

創建子表使用外鍵

create table student(

id int primary key auto_increment,

number char(10) not null unique comment "學號：不能重復0",

name varchar(10) not null comment "姓名",

c_id int,

foreign key(c_id) references class(id)

) charset utf8;

--增加外鍵：c_id是外鍵字段，class是引用表（父表），id是引用字段（主鍵）

查看數據表student的結構信息

desc student;

或

describe student;

PRI稱為主鍵索引，MUL稱為輔助索引

顯示創建表student的完整 SQL 語句，包括字段定義、主鍵、外鍵、默認值、注釋、索引、字符集、存儲引擎等信息。

show create table student;

自動生成了外鍵名

2.再創建表之后增加外鍵：指定外鍵名字

創建外鍵語法格式

alter table 表名 add constraint 外鍵名 foreign key(外鍵字段) references 父表(主鍵字段)

創建沒有外鍵信息的表

create table t_foreign(

id int primary key auto_increment,

c_id int

)charset utf8;

此時表中（t_foreign）沒有創建外鍵

在沒有外鍵的表中添加外鍵

alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);

此時有了外鍵信息

外鍵增加條件：外鍵字段必須與引用表（父表主鍵）的數據類型嚴格保持一致

刪除外鍵

刪除外鍵語法格式：alter table 表名 drop foreign key 外鍵名;

刪除表（t_foreign）中的外鍵信息

alter table t_foreign drop foreign key class_foreign;

刪除外鍵之后僅是把約束刪掉了，但索引不會被刪除

查看外鍵是否被刪除

show create table t_foreign;

可以看到沒有創建外鍵的信息了，外鍵的約束確實被刪除了

查看外鍵

desc t_foreign;

show create table t_foreign;

外鍵作用說明

約束1：外鍵對子表的數據寫操作學術（增加和更新）

如果子表中插入的數據所對應的外鍵在父表中不存在，創建不能成功

因此一旦建立外鍵關系，一定先添加父表，然后操作子表

約束2：外鍵對父表的數據約束

當父表操作一個記錄，但是該記錄被子表所引用的時候，那么父表的操作將會被限制（更新：主鍵和刪除）

無法刪除父表（class）中id=1的字段，因為已被子表引用。但當刪除子表的引用后，父表可以成功刪除

數據模式

SQL_mode（SQL模式）：保證數據錄入合理性

例如：日期不能出現0000-00-00信息，月份只能是1-12，日期只能是1-31，一旦違反常識便會報錯

例如：在進行數據運算時（select 1+1;）,除法運算的除數不能為0

例如：當定義數據類型為char(10)，不能超過字符長度，超過長度就報錯

例如：設置only_full_group_by(5.7以后的特性)，禁止進行分組查詢時，出現聚合信息1對多的顯示輸出

獲取SQLmode設置的默認信息：

select @@sql_mode;

常見 SQL_MODE 模式解釋

模式名	含義
STRICT_TRANS_TABLES	嚴格模式：插入無效數據（如超長字符串、非法數值）時報錯（針對事務表）
STRICT_ALL_TABLES	嚴格模式：所有表（包括非事務表）都嚴格校驗
ONLY_FULL_GROUP_BY	使用 GROUP BY 時必須顯示分組的字段，避免不確定聚合行為
NO_ZERO_IN_DATE	禁止日期中出現 “00” 月或日（如 2025-00-01）
NO_ZERO_DATE	不允許將 0000-00-00 作為合法日期插入
ERROR_FOR_DIVISION_BY_ZERO	遇到除以零時報錯，而不是返回 NULL
NO_ENGINE_SUBSTITUTION	如果指定的存儲引擎不可用則報錯（否則用默認引擎替代）
ANSI_QUOTES	將 " 識別為標識符（列名/表名），而不是字符串引號

注意：在數據遷移時（低版本遷移到高版本），需要關閉SQL_mode

臨時關閉SQLmode(設置SQLmode為空)

set global sql_mode='';

退出數據庫后然后重新進入