欄位型別

欄位型別：MySQL中用來規定實際存盤的資料格式

• 欄位型別在定義表結構時設定
• 設定好欄位型別后，插入資料時必須與欄位型別對應，否則資料錯誤
• 欄位型別的作用就是強制規范錄入的資料格式
  • 規范資料的格式
  • 保證資料的有效性
• MySQL有四大資料型別
  • 整數型別：只能存盤整數
  • 小數型別：可以存盤有效數值
  • 字串型別：存盤字串資料
  • 時間日期型別：存盤時間日期格式資料

整數型別

整數型別：有效的整數資料

• MySQL中為了資料空間的有效使用，設定了五種整數型別
  • 迷你整型：tinyint，使用1個位元組存盤整數，最多存盤256個整數（-128~127）
  • 短整型：smallint，使用2個位元組存盤整數
  • 中整型：mediumint，使用3個位元組存盤整數
  • 標準整型：int，使用4個位元組存盤整數
  • 大整型：bigint，使用8個位元組存盤
• 數值型存盤在MySQL中分為有符號（有負數）和無符號（純正數）需要unsigned 修飾整型

示例：設計一個表記錄個人資訊：年齡、頭發數量

# 年齡：沒有負數，正常年齡也不超過200歲，迷你整型無符號即可
# 頭發數量：沒有負數，大概在幾百萬根，所以標準整型無符號即可
create table t_7(
	age tinyint unsigned, # unsigned修飾整數，表示無符號（從0開始）
    haircount int unsigned
)charset utf8;

顯示寬度：int(L)，整數在資料庫中顯示的符號（數字+符號）個數

• 顯示寬度一般是型別能表示的最大值對應的數字個數（通過desc查看表欄位顯示）
• 顯示寬度包含符號（如果允許為負數，-負號會增加一個寬度）
• 顯示寬度可以主動控制：創建欄位時加括號確定
• 顯示寬度不會影響型別能表示的最大數值
• 可以通過zerofill讓不夠寬度的數值補充到對應寬度：在欄位型別后使用zerofill

小數型別(2種)

1、浮點型：float / double，存盤不是特別精確的數值資料

• 浮點數又稱之為精度資料，分為兩種
  • 單精度：float，使用4個位元組存盤，精度范圍為6-7位有效數字
  • 雙精度：double，使用8個位元組存盤，精度范圍為14-15位有效數字
• 浮點數超過精度范圍會自動進行四舍五入
• 精度可以指定整數和小數部分
  • 默認不指定，整數部分不超過最大值，小數部分保留2位
  • 可以指定：float/double(總長度,小數部分長度)
• 可以使用科學計數法插入資料：AEB，A * 10 ^ B
• 因為浮點數會自動四舍五入，所以不要使用浮點數來存盤對精度要求較高的數值

示例：記錄商品的價格

# 商品名字字串
# 商品價格一般都允許帶小數
create table t_11(
	goods_name varchar(20),
    goods_price float
)charset utf8;
insert into t_11 values('Nokia3310',199.99);
insert into t_11 values('Nokia6100',1999.9999);

2、定點型：decimal，能夠保證精度的小數

• 定點數的存盤模式不是固定長度，所以資料越大占用的存盤空間越長
• 每9個數字使用4個位元組存盤
• 定點型可以指定整數部分長度和小數部分長度
  • 默認不指定，10位有效整數，0位小數
  • 可以指定：decimal(有效數位,小數部分數位)
  • 有效數位不超過65個
• 資料規范
  • 整數部分超出報錯
  • 小數部分超出四舍五入

示例：記錄個人資產情況：資產和負債

# 資產和負債應該都是精確的，小數部分可以到分
create table t_12(
    money decimal(14,2),
    bet decimal(10,2)
)charset utf8;

insert into t_12 values(1111111111.12,1111111.999);
insert into t_12 values(1111111111.12,99999999.999); # 錯誤：進位導致正數部分超過指定范圍

字串型別(5種)

1、定長型：char(L)，指定固定長度的存盤空間存盤字串

• 定長是指定存盤長度
• 定長的長度是字符而不是位元組
  • L的最大值是255
  • 實際存盤空間：L字符數 * 字符集對應位元組數
• 定長里存盤的資料不能超過指定長度，但是可以小于指定長度
• 字串資料使用單引號或者雙引號包裹
• 定長的訪問效率較高，但是空間利用率較低

示例：記錄個人資訊：身份證資訊和手機號碼

# 身份證為固定長度18位（數字）
# 手機號碼是11位固定長度（數字）
create table t_13(
	id_number char(18),
    phone_number char(11)
)charset utf8;
insert into t_13 values('440111999912120304','13512345678');

2、變長型：varchar(L)，根據實際存盤的資料變化存盤空間

• 變長型的存盤空間是由實際存盤資料決定的
• 變長型的L也是指字符而不是位元組
  • L指定的是最大存盤的資料長度
  • L最大值理論是65535
  • 變長需要額外產生1-2個位元組，用來記錄實際資料的長度
    • 資料長度小于256個，多1個位元組
    • 資料長度大于256個，多2個位元組
  • 實際存盤空間：實際字符數 * 字符集對應位元組數 + 記錄長度
• 變長資料不能超過定義的最大長度
• 變長字串在讀取時需要進行長度計算，所以效率沒有定長字串高
• 變長字串能夠更好的利用存盤空間

示例：記錄個人資訊：用戶名、密碼、姓名、身份證

# 用戶名不確定長度，最長不超過50個字符
# 密碼不確定長度，最長超過15個字符
# 姓名不確定長度，最長不超過10個字符
# 身份證固定長度，18個字符
create table t_14(
	username varchar(50),
    password varchar(15),
    name varchar(10),
    id_number char(18)
)charset utf8;

insert into t_14 values('username','password','name','444111999912121111');

3、文本字串：text/blob，專門用來存盤較長的文本

• 文本字串通常在超過255個字符時使用
• 文本字串包含兩大類
  • text：普通字符
    • tinytext：迷你文本，不超過2 ^ 8 -1個字符
    • text：普通文本，不超過 2 ^ 16 - 1個字符
    • mediumtext：中型文本，不超過 2 ^ 24 - 1 個字符
    • longtext：長文本，不超過 2 ^ 32 - 1 個字符（4G）
  • blob：二進制字符（與text類似）
    • tinyblob
    • blob
    • mediumblob
    • longblob
• 文本字串會自動根據文本長度選擇適合的具體型別
• 一般在文本超過255個字符時，都會使用text（blob現在極少使用）

示例：記錄新聞資訊：標題、作者和內容

# 標題一般不會超過50個字符，varchar
# 作者一般不會超過10個字符：varchar
# 內容通常都很長，使用text
create table t_15(
	author varchar(10),
    title varchar(50),
    content text
)charset utf8;

insert into t_15 values('佚名','給聯合國的一封信','給聯合國的一封信...');

4、列舉型：enum, 一種映射存盤方式，以較小的空間存盤較多的資料

• 列舉是在定義時確定可能出現的可能，而后資料只能出現定義時其中的一種的資料型別
• 列舉類似一種單選框
• 列舉使用1-2個位元組存盤，最多可以設計65535個選項
• 列舉實際存盤是使用數值，映射對應的元素資料，從1開始
• 列舉語法：enum(元素1,元素2,...元素N)
• 使用列舉的作用：
  • 規范資料模型
  • 優化存盤空間

示例：記錄人群型別：小朋友、少年、青年、中年、老年，每個人實際只屬于一種類別

# 要保證未來資料只能出現在某種可能中，所以要先列出來，可以使用enum
create table t_16(
	type enum('小朋友','少年','青年','中年','老年')
)charset utf8;

insert into t_16 values('少年');
insert into t_16 values('仙人');	# 不存在的資料不能插入

列舉定義原理：

資料存盤（讀取反過來）：

5、集合型：set，一種映射存盤方式，以較小的空間存盤較多的資料

• 集合是在定義時確定可能出現的元素進行窮舉，而后資料只能出現定義時其中的元素（可以是多個）
• 集合類似一種多選框
• 集合使用1-8個位元組存盤資料，最多可以設計64個元素
• 集合實際存盤是使用數值（二進制位），映射對應的元素資料，每個元素對應一個位元位
  • 資料存在：對應位為 1
  • 資料不存在：對應位為 0
• 集合語法：set(元素1,元素2,...元素N)
• 使用集合的作用：
  • 規范資料模型
  • 優化存盤空間

示例：記錄個人的球類愛好，有籃球、足球、羽毛球、網球、乒乓球、排球、臺球、冰球

# 愛好可以是多種，并非固定的，但是只能從規定的型別中選擇
create table t_17(
	hobby set('足球','籃球','羽毛球','網球','乒乓球','排球','臺球','冰球')
)charset utf8;

insert into t_17 values('足球');
insert into t_17 values('冰球,臺球,籃球');

集合定義原理：

資料存盤（讀取反過來）：

時間日期型別(5種)

1、年：year，MySQL中用來存盤年份的型別

• MySQL中使用1個位元組存盤年份
• year能夠表示的范圍是1901-2155年（256年）
  • year的特殊值是：0000
• year允許用戶使用兩種方式設計（效果一樣）
  • year
  • year(4)
• 因為year欄位表示的范圍有限，所以通常會使用字串來存盤（犧牲空間換安全）

示例：記錄個人的出生年份

create table t_18(
	y1 year,
    y2 year(4)
)charset utf8;
insert into t_18 values(1901,2155);

2、時間戳：timestamp，基于格林威治時間的時間記錄

• MySQL中時間戳表現形式不是秒數，而是年月日時分秒格式
  • YYYY-MM-DD HH:II::SS
  • YYYYMMDDHHIISS
• timestamp使用4個位元組存盤
  • 表示范圍是 1971年1月1日0時0分0秒-2155年12月31日23是59分59秒
  • timestamp可以使用 0000-00-00 00:00:00
• 所對應的記錄不論哪個欄位被更新，該欄位都會更新到當前時間
• 但在MySQL8中需要主動使用on update current_timestamp才會自動更新

示例：記錄商品庫存的最后更新時間

create table t_19(
	goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;

insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');

注意：在MySQL8以后，取消了timestamp的默認自動更新，如果需要使用，需要額外使用屬性： on update current_timestamp

alter table t_19 add c_time timestamp on update current_timestamp;

update t_19 set goods_inventory = 80;

3、日期：date，用來記錄年月日資訊

• 使用3個位元組存盤資料
• 存盤日期的格式為：YYYY-MM-DD
• 存盤的范圍跨度很大，存盤區間是1000 - 9999年：1001-01-01~9999-12-31

示例：記錄個人生日

create table t_20(
	name varchar(10),
    birth date
)charset utf8;

insert into t_20 values('Jim','2000-12-12');
insert into t_20 values('Tom','10011212');

4、日期時間：datetime，用來綜合存盤日期和時間

• 使用8個位元組存盤資料
• 存盤格式為：YYYY-MM-DD HH:II:SS
• 存盤區間為：1000-01-01 00:00:00 到9999-12-31 23:59:59

示例：記錄個人具體的出生時間

create table t_21(
	name varchar(10),
    birth datetime
)charset utf8;

insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');

5、時間：time，用來記錄時間或者時間段

• 使用3個位元組存盤資料
• 資料范圍是 -838:59:59 - 838:59:59
• 資料插入的格式分為兩種
  • 時間格式：[H]HH:II:SS（[ ]表示可以沒有）
  • 時間段格式：D HH:II:SS（D表示天）
  • time型別通常被用來做時間段計算：如多少天后的什么時間點（可以理解為過期檢查）

示例：記錄用戶登錄的具體時間

# 具體登錄時間可以使用時間戳（包含年月日時分秒資訊）
# 也可以時間datetime格式，或者date+time雙欄位格式（具體后面學習范式時會知道該怎么用）
create table t_22(
	login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8;

insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');

屬性

屬性：建立在欄位型別之后，對欄位除型別之外的其他約束

• 屬性是在定義表欄位的時候針對每個欄位進行屬性設定，是MySQL用來增加欄位規范和約束的
• 設定好的屬性可以通過查看表欄位desc進行查看
• 資料在進行增刪改（寫）操作時需要在滿足欄位型別和屬性的要求
• 用好屬性能夠提升資料的有效性，方便未來進行資料操作和資料分析（資料真實性和有效性）

查看表屬性：desc 表名;

desc t_1;
# Field：欄位名字
# Type：資料型別
# Null：是否為空（屬性）
# Key：索引型別（屬性）
# Default：默認值（屬性）
# Extra：額外屬性

NULL屬性

NULL：資料是否允許為空

• Null/Not Null屬性 是用來限定資料是否為Null值的

• 默認情況下資料是允許為Null的，不為空設計：Not Null

• 一般有效的資料都必須設定為Not Null來保證資料的有效性，資料為空一般不具備運算和分析價值

示例：用戶資訊表：用戶名、密碼、姓名、年齡、注冊時間

create table t_23(
	username varchar(50) not null,
    password char(32) not null,
    name varchar(20),
    age tinyint unsigned,
    reg_time int unsigned not null
)charset utf8;

Default屬性

默認值：default，在設計表欄位的時候給定默認資料，在后續欄位操作（資料新增）的時候系統沒有檢測到欄位有資料的時候自動使用的值

• 默認值在欄位設定的時候使用（默認值需要滿足資料型別規范）
• 默認值通常設計的是欄位容易出現的資料
  • 一般欄位的默認值 默認是Null
• 默認值觸發
  • 在系統進行資料插入時自動檢測觸發
  • 主動使用default關鍵字觸發默認值

示例：用戶開戶：銀行卡賬號、身份證號碼、姓名、賬戶余額

create table t_24(
	account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8;

主鍵

主鍵：primary key，用來保證整張表中對應的欄位永遠不會出現重復資料（唯一性）

• 主鍵在一張表中只能有一個
• 主鍵的另外一個特性是能夠提升主鍵欄位作為查詢條件的效率（索引）
• 主鍵不能為空：Not Null（默認）
• 邏輯主鍵：資料沒有具體業務意義，純粹是一種數值資料
  • 邏輯主鍵通常是整數：int
  • 邏輯主鍵目的是方便檢索和資料安全（不暴露資料真實資訊）
• 復合主鍵：多個欄位共同組成不能重復的資料
  • primary key(欄位1,欄位2,…欄位N)
  • 聯合主鍵使用不多，一般也不會超過2個欄位

示例：銀行賬戶資訊：賬戶、姓名、余額

# 銀行賬戶具有唯一性，不能重復，也不允許為空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

# 復合主鍵
create table t_26(
	account varchar(17),
    name varchar(20),
    money decimal(16,2) not null default 0.00,
    primary key(account,name)
)charset utf8;

# 一般使用邏輯主鍵
create table t_27(
	id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

主鍵管理：在創建表并且已經有資料后的維護

• 洗掉主鍵
• 追加主鍵
• 修改主鍵（先洗掉后新增）

1、洗掉主鍵：主鍵只有一個，所以洗掉語法也比較特殊

alter table t_26 drop primary key;

2、后期新增主鍵：如果是針對業務主鍵需要保證欄位資料沒有Null資料且沒有資料重復（一般主鍵都會在表創建時維護好）

alter table t_26 add primary key(account,name);

自增長屬性

自增長：auto_increment，被修飾的欄位在新增時，自動增長資料

• 自增長只能是整數型別，而且對應的欄位必須是一個索引（通常邏輯主鍵）
• 一張表只能有一個自動增長
• 自增長一般是配合邏輯主鍵實作自動增長
  • 整型欄位
  • 存在索引（主鍵）
• 自增長資料可以理解為一種默認值，如果主動給值，那么自動增長不會觸發
• 自增長由兩個變數控制
  • 初始值：auto_increment_offset，默認是1
  • 步長：auto_increment_increment，默認值也是1
  • 查看自增長控制：show variables like 'auto_increment%';

示例：記錄學生資訊：學號和姓名

# 學生資訊：學號自動增長
create table t_28(
    id int primary key auto_increment,
	stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8;

自增長管理：在某些特殊使用下，需要自增長按照需求實作

• 修改表中自增長的值：讓下次自增長按照指定值開始
• 修改自增長控制：調整自增長的變化

1、修改表中自增長的值：跳過一些值，直接從下次開始按照新的目標值出現

alter table t_28 auto_increment = 50;

注意：奇數會保留原值，偶數會自動加1（可能出現的情況）

2、修改自增長控制：步長和起始值（修改針對的是整個資料庫，而非單張表）

set auto_increment_increment = 2;	# 當前用戶當前連接有效（區域）
set @auto_increment_increment = 2;	# 所有用戶一直有效（全域）

唯一鍵

唯一鍵：unique key，用來維護資料的唯一性

• 一個表中可以有多個唯一鍵
• 唯一鍵與主鍵的區別在于 唯一鍵允許資料為Null（而且Null的數量不限）
• 唯一鍵與主鍵一樣，可以提升欄位資料當做條件查詢的效率（索引）
• 復合唯一鍵：多個欄位共同組成
  • unique key(欄位1,欄位2,…欄位N)
  • 一般不會出現，最多2個欄位組成

示例：學生成績表：一個學生只能有一個學科成績，但是可以有多個學科

# 學號和學科編號共同組成唯一
create table t_30(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8;

insert into t_30 values(null,'Jim','Math',50);
insert into t_30 values(null,'Jim','English',80);

唯一鍵管理：在表創建后對唯一鍵的管理

• 洗掉唯一鍵相對麻煩，一張表中不止一個唯一鍵

• 新增唯一鍵要保證欄位里的資料具有唯一性

• 洗掉唯一鍵：alter table 表名 drop index 唯一鍵名字；

• 新增唯一鍵： alter table 表名 add unique key(欄位串列)；

1、洗掉表中已有的唯一鍵

alter table t_30 drop index stu_name;

2、追加唯一鍵

alter table t_30 add unique key stu_course (stu_name,course);

comment屬性

描述：comment，是用文字描述欄位的作用的

• comment代表的內容是對欄位的描述
  • 方便以后自己了解欄位的作用
  • 方便團隊了解欄位的作用
• 描述如果涉及到字符集（中文）一定要在創建表之前 設定好客戶端字符集（否則會出現描述亂碼）

示例：學生成績表

# 學生成績表中通常是存盤學生學號
# 學科通常也是學科代碼
create table t_31(
	id int primary key auto_increment,
    stu_no varchar(10) not null comment '學號',
    course_no varchar(10) not null comment '課程號',
    score decimal(5,2) comment '考試成績',
    unique key stu_course (stu_no,course_no) comment '學號和課程號組成唯一鍵'
)charset utf8;

資料庫記錄長度

資料庫記錄長度：MySQL中規定一條記錄所占用的存盤長度最長不超過65535個位元組

• 記錄長度為表中所有欄位預計占用的長度之和
• 所有欄位只有允許Null存在，系統就會預留一個位元組存盤Null（多個Null也只要一個就好）
• 因為MySQL記錄長度的存在，varchar永遠達不到理論長度
  • GBK存盤：65535（字符） * 2 + 2 = 131072（位元組）
  • UTF8存盤：65535（字符） * 3 + 2 = 196607（位元組）
• 一般資料長度超過255個字符都會使用 text/blob 進行存盤（資料存盤不占用記錄長度）

1、GBK表能存盤的最大varchar字串長度

create table t_32(
	content varchar(65535)
)charset gbk;	# 錯誤

create table t_32(
	content varchar(32767)
)charset gbk;	# 錯誤

create table t_32(
	content varchar(32766)
)charset gbk;	

2、UTF8表能存盤的最大varchar字串長度

create table t_33(
	content varchar(65535)
)charset utf8;	# 錯誤

create table t_33(
	content varchar(21844)
)charset utf8;

3、Null也要占用一個位元組

create table t_34(
    id tinyint,
	content varchar(21844)
)charset utf8;	# 錯誤

create table t_34(
    id tinyint not null,
	content varchar(21844) not null
)charset utf8;