MySQL-存儲引擎-創建表-字段數據類型-嚴格模式-字段約束-鍵-02

• 2019 年 10 月 7 日
• 筆記

目錄

• 擴展點
  • 查看服務端字符、IP、端口配置
  • 取消本次錯誤輸入
    • 例外情況
• database 數據庫操作
• table 數據表操作
  • 查看MySQL存儲引擎
  • 常見幾個存儲引擎
    • InnoDB
    • MyISAM
    • MEMORY
    • BLACKHOLE
    • 引擎對應的本地化文件
    • 案例
  • 基本操作
    • 創建表的完整語法
• 表記錄基礎操作
• 嚴格模式補充
  • 查看數據庫配置中變量名包含mode的配置參數
    • 模糊匹配
• 基本數據類型
  • 數據範圍
  • 整型
    • TINYINT SMALLINT MEDIUMINT INT BIGINT
    • 應用場景
    • 結合字段驗證數據範圍及有無符號
    • 類型的寬度與存儲寬度的關係驗證
  • 浮點型
    • FLOAT DOUBLE DECIMAL
    • 備註
    • 應用場景
    • 三者最大整數位和小數位對比
  • 字符類型(char與varchar)
    • CHAR VARCHAR
    • 區別
    • 應用場景
    • 案例
  • 日期時間類型
    • DATE TIME DATETIME YEAR TIMESTAMP
    • 應用場景
    • 案例
  • 枚舉與集合
    • 枚舉 enum
    • 集合 set
    • 案例
• 約束條件
  • 常見約束
  • primary key 主鍵
    • 主鍵 — 單字段 primary key
    • 聯合主鍵 primary key(字段1, 字段2…)
    • InnoDB自帶主鍵科普
  • auto_increment 自動遞增
    • 自動遞增序號問題
  • unique 唯一
    • 單列唯一
    • 聯合唯一
  • not null + default 非空與默認值
  • usinged
  • zerofill

MySQL的字符編碼設置已在安裝章節的配置文件中指定

擴展點

查看服務端字符、IP、端口配置

在mysql客戶端輸入 s 可以查看服務端的一些配置信息

取消本次錯誤輸入

在mysql客戶端輸入 c 即可取消當前輸入的那些語句

例外情況

單雙引號必須配對了 c 與 ; 才能生效

不小心按錯 「 」 『』 單雙引號後，可以使用 c 來放棄當前輸入的那些內容

database 數據庫操作

# 增：  create database db1 charset utf8;  # 查：  show databases;  show create database db1;  # 改：  alter database db1 charset latin1;  #刪除:  drop database db1;

table 數據表操作

查看MySQL存儲引擎

不同的數據應該有不同的處理機制

show engines 查看所有的存儲引擎

常見幾個存儲引擎

InnoDB

MySQL 5.5 開始 默認存儲引變更為 InnoDB

默認的存儲引擎，支持事務，支持行鎖，表鎖，外鍵，比較安全

因為 InnoDB 在存儲數據的時候，更加安全，所以默認的存儲引擎是InnoDB(雖然 MyISAM 比 InnoDB 快)

建表會建兩個文件： .frm表結構 .ibd 真實數據

InnoDB存儲引擎在建表的時候，要求表必須有且只有一個主鍵

• 當你沒有設置主鍵的時候，會自上往下尋找非空且唯一的約束字段自動將其升級為主鍵字段
• 當你的表中沒有任何約束（主鍵也是約束）字段的時候，InnoDB會使用內部的一個隱藏字段作為主鍵，我們無法利用該主鍵

MyISAM

老版本用的存儲引擎，支持表鎖

建表會建三個文件： .frm 表結構，.MVD真實數據，.MYI索引

MEMORY

內存引擎（數據全部存在在內存中，一斷電或重啟程序數據就丟失）

建表只會建一個文件： frm表結構，數據存在內存中不需要存文件

BLACKHOLE

正如名字所述，任何寫入到此引擎的數據均會被丟棄掉， 不做實際存儲；select這張表返回內容永遠是空。

建表只會建一個文件： frm表結構，數據不存儲

感興趣的小夥伴可以點這個鏈接了解一下：MySQL的BlackHole引擎在主從架構中的作用

引擎對應的本地化文件

案例

# 查看所有的存儲引擎  show engines;    # 查看不同存儲引擎存儲表結構文件特點  create table t1(id int)engine=innodb;  create table t2(id int)engine=myisam;  create table t3(id int)engine=blackhole;  create table t4(id int)engine=memory;    insert into t1 values(1);  insert into t2 values(1);  insert into t3 values(1);  insert into t4 values(1);

基本操作

# 切換文件夾  user db1;  select database();  # 查看當前所在的庫  # 增  create table t1(id int,name char);  # 創建出來的可能是多個文件，解耦管理  # 改  alter table t1 modify name char(16);  # 查  show tables;  show create table t1;       # 查看錶的詳細信息  describe t1; == desc t1;  # 查看錶結構  # 刪  drop table t1;

創建表的完整語法

# 語法：  create table 表名(  字段名1 類型[(寬度) 約束條件],  字段名2 類型[(寬度) 約束條件],  字段名3 類型[(寬度) 約束條件]  );    # 注意：  # 1. 在同一張表中，字段名不能相同  # 2. 寬度和約束條件可選，字段名和類型是必須的  # 3. 最後一個字段後不能加逗號！    # 補充：  # 1.寬度指的是對存儲數據的限制  create table userinfo(name char);  insert into userinfo values('jason');  """  1.沒有安全模式的數據庫版本，能夠存放數據但是只會存進去一個j  2.最新數據庫版本直接報錯提示無法存儲:Data too long for column 'name' at row 1  """    # 2.約束條件初識>>> null 與 nut null  create table t1(id int,name char not null);  insert into t1 values(1,'j');  # 正常存儲  insert into t1 values(2,null);  # 報錯    # 總結 類型與約束條件區別  # 類型：限制字段必須以什麼樣的數據類型存儲  # 約束條件：約束條件是在類型之外添加一種額外的限制

表記錄基礎操作

記錄是存在表裡的，表又是存在database數據庫里的，所以要先有數據庫和表才能有表

# 增  insert into db1.t1 values (1,'egon'),(2,'kevin'),(3,'jason');  # into可加可不加，db1可以不指定，默認就是在當前庫下  # 改  update db1.t1 set name='DSB' where id > 1;  update db1.t1 set name='DSB' where id = 2 or id = 3;  # 查  select id,name from db1.t1;  # db1可不指定，默認當前庫下  select * from t1;  # 刪  delete from db1.t1 where id >3;  delete from db1.t1 where name='egon'  # 這裡注意如果少了一個引號，後面無論敲什麼都沒有用了需要將引號補全

嚴格模式補充

我們剛剛在上面設置了char, tinyint,存儲數據時超過它們的最大存儲長度，發現數據也能正常存儲進去，只是 mysql 幫我們自動截取了最大長度。但在實際情況下，我們應該盡量減少數據庫的操作，緩解數據庫的壓力，讓它僅僅只管理數據即可，這樣的情況下就需要設置嚴格模式

備註： 在 5.7 左右以後的MySQL版本中默認就是嚴格模式

查看數據庫配置中變量名包含mode的配置參數

show variables like "%mode%";

sql_mode 即sql嚴格模式

# 修改安全模式  set session ...  # 只在當前操作界面臨時有效  set global ...  # 全局有效，長期有效，設置完需要重啟客戶端進入

修改當前 sql 模式為嚴格模式

set global sql_mode ='STRICT_TRANS_TABLES'; # 僅僅char時，分組、char_length 需要額外添加，後面博客會有講到

設置完需要退出客戶端重新進入

5.6.45 默認sql_mode：NO_ENGINE_SUBSTITUTION

模糊匹配

關鍵字 like

• % 匹配任意多個字符
• _ 匹配任意一個字符

基本數據類型

數據範圍

整型

TINYINT SMALLINT MEDIUMINT INT BIGINT

應用場景

存儲年齡，等級，id，各種號碼等

結合字段驗證數據範圍及有無符號

create table t1(x tinyint);  insert into t1 values(128),(-129);    create table t2(x tinyint unsigned);  insert into t2 values(-1),(256);    create table t3(x int unsigned);  insert into t3 values(4294967296);

類型的寬度與存儲寬度的關係驗證

create table t4(x int(8));  insert into t4 values(4294967296123);    # 顯示時，不夠8位用0填充，如果超出8位則正常顯示  create table t5(x int(8) unsigned zerofill);  insert into t5 values(4294967296123);    # create table t6(id int(10) unsigned);  # create table t7(id int(11));

結論

對於整型來說，數據類型後的寬度並不是存儲限制，而是顯示限制，所以在創建表時，如果字段採用的是整型類型，完全無需指定顯示寬度， 默認的顯示寬度，足夠顯示完整當初存放的數據

浮點型

FLOAT DOUBLE DECIMAL

備註

精確度：float < double < decimal

根據精度選擇類型或者轉成字符串存儲

應用場景

身高，體重，薪資

字段限制特點(5,3)前一位表示所有的位數，後一位表示小數個數

三者最大整數位和小數位對比

字符類型(char與varchar)

CHAR VARCHAR

區別

char(4)

最大只能存4個字符，超出會直接報錯（嚴格模式）或截取，如果少了，會自動用空格填充

varchar(4)

最大只能存4個字符，超出會直接報錯（嚴格模式）或截取，如果少了，有幾個字符存幾個字符

應用場景

姓名，地址，描述類信息

案例

create table t10(name char(4))  # 超出四個字符報錯，不夠四個字符空格補全  create table t11(name varchar(4))  # 超出四個字符報錯，不夠四個有幾個就存幾個    # 驗證存儲限制  insert into t12 values('hello');  insert into t13 values('hello');  # 驗證存儲長度  insert into t12 values('a'); #'a    '  insert into t13 values('a'); #'a'  select * from t12  select * from t13  # 無法查看真正的結果    select char_length(name) from t12  select char_length(name) from t13  # 仍然無法查看到真正的結果

char_length()

mysql在存儲char 類型字段的時候，硬盤上確確實實存的是固定長度的數據，但是再取出來的那一瞬間mysql 會自動將填充的空格去除

可以通過嚴格模式來修改該機制，讓其不自動做去除處理

# 如果不想讓mysql幫你做自動去除末尾空格的操作，需要再添加一個模式  set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";  # 退出客戶端重新登陸  select char_length(x) from t12; # 4  select char_length(y) from t13; # 1    # 針對char類型，mysql在存儲時會將數據用空格補全存放到硬盤中。但是會在讀出結果的時候自動取掉末尾的空格

char 與 varchar 的區別

站在二進制數據讀取的角度來講（數據存成文件都是二進制的，文本編輯器等打開都是軟件做了處理），char指定了長度，直接按固定的長度讀取即可，而varchar無法知道數據有多長，有個標誌位來標識多長，要識別等，所以耗費的時間就會相對較長一點

char

字段定長，就算只存了一個字符，還是會佔用字段寬度的自負空間，會浪費空間

取的時候方便，按固定字符數存取數據快

varchar

在存的時候根據字段記錄實際長度存儲（不超過指定長度），比較節省空間

取的時候繁瑣，不知道數據到底多長，存儲速度相對char慢，（類似報頭來確定長度）

前幾年大量用char，近幾年varchar比較多（無所謂哪個好那個不好，知道優缺點就行了）

"""  char與varchar的使用區別  """  name char(5)  # 缺點：浪費空間  # 優點：存取速度都快  egon alex lxx  jxx  txx    name varchar(5)  # 缺點：存取速度慢  # 優點：節省空間  1bytes+egon 1bytes+alex 1bytes+lxx  1bytes+jxx  1bytes+txx 

日期時間類型

DATE TIME DATETIME YEAR TIMESTAMP

應用場景

日期、時間

案例

create table student(      id int,    name char(16),    born_year year,    birth date,    study_time time,    reg_time datetime  );  insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

推薦博客：mysql的5種時間類型的比較

枚舉與集合

枚舉 enum

限制某個字典能夠存儲的數據內容只能是指定的幾個中的一個(多選一)

集合 set

限制某個字段能夠存儲的數據內容只能是指定的內容中的某幾個（多選多）

以字符串的形式傳入多個值，每個值之間用 , 隔開，不要亂加空格等（在字符串里加東西是什麼概念？）

案例

create table user(    id int,    name char(16),    gender enum('male','female','others')  );  insert into user values(1,'jason','xxx')  # 報錯  insert into user values(2,'egon','female')  # 正確！      create table teacher(    id int,    name char(16),    gender enum('male','female','others'),    hobby set('read','sleep','sanna','dbj')  );  insert into teacher values(1,'egon','male','read,sleep,dbj')  # 集合也可以只存一個

約束條件

約束條件主要是用於保證數據的完整性和一致性

常見約束

PRIMARY KEY (PK)  # 標識該字段為該表的主鍵，可以唯一的標識記錄  FOREIGN KEY (FK)  # 標識該字段為該表的外鍵  NOT NULL  # 標識該字段不能為空  UNIQUE KEY (UK)  # 標識該字段的值是唯一的  AUTO_INCREMENT  # 標識該字段的值自動增長（整數類型，而且為主鍵）  DEFAULT  # 為該字段設置默認值  UNSIGNED  # 無符號  ZEROFILL  # 使用0填充

可以有聯合主鍵PRIMARY KEY、UNIQUE

primary key 主鍵

主鍵 — 單字段 primary key

限制效果跟 not null + unique 組合效果一致，非空且唯一

primary key 也是 innodb 引擎查詢必備的索引（類似於書的目錄）

通常每張表都應該有一個id（stu_id等都行）字段，並且將id字段設置為表的主鍵字段

聯合主鍵 primary key(字段1, 字段2…)

多個字段聯合起來作為表的一個主鍵，本質還是一個主鍵

InnoDB自帶主鍵科普

primary key也是innodb引擎查詢必備的索引      索引你就把當成書的目錄    innodb引擎在創建表的時候 必須要有一個主鍵  當你沒有指定主鍵的時候      1.會將非空切唯一的字段自動升級成主鍵      2.當你的表中沒有任何的約束條件  innodb會採用自己的內部默認的一個主鍵字段          該主鍵字段你在查詢時候是無法使用的          查詢數據的速度就會很慢          類似於一頁一頁的翻書            create table t19(              id int,            name char(16),            age int not null unique,            addr char(16) not null unique          );    主鍵字段到底設置給誰呢???      通常每張表裏面都應該有一個id字段      並且應該將id設置為表的主鍵字段    聯合主鍵：多個字段聯合起來作為表的一個主鍵，本質還是一個主鍵!!!      ps:innodb引擎中一張表有且只有一個主鍵      create table t20(          ip char(16),        port int,        primary key(ip,port)      );      desc t20;      主鍵字段應該具備自動遞增的特點      每次添加數據  不需要用戶手動輸入

auto_increment 自動遞增

該約束條件只能加在被設置成 key 的字段上，不能單獨使用，通常都是跟 primary key 聯用

主鍵字段應該具備自動遞增的特點，每次添加數據，不需要用戶手動輸入

自動遞增序號問題

刪除數據後，序號不會接着上一條存在數據往下遞增，而是從上一次的最大序號開始遞增

delete from 清空表僅僅是刪除數據，不會重置 id

delete from tb1; # 不加條件默認刪除表內所有記錄

truncate 清空並初始化表，可以重置主鍵

truncate table tb1; # 清空表，id字段會重置

unique 唯一

單列唯一

限制某個字段是唯一的

聯合唯一

在語句的最後，用括號的形式，表示哪幾個字段組合的結果是唯一的（應用場景： ip + port）

# 單列唯一  create table user1(      id int unique,    name char(16)  );  insert into user1 values(1,'jason'),(1,'egon')  # 報錯  insert into user1 values(1,'jason'),(2,'egon')  # 成功    # 聯合唯一  create table server(      id int,    ip char(16),    port int,    unique(ip,port)  )  insert into server values(1,'127.0.0.1',8080);  insert into server values(2,'127.0.0.1',8080);  # 報錯  insert into server values(1,'127.0.0.1',8081);

not null + default 非空與默認值

create table user(      id int,    name char(16)  );  insert into user values(1,null)  # 可以修改    alter table user modify name char(16) not null;  insert into user(name,id) values(null,2);  # 報錯 插入數據可以在表名後面指定插入數據對應的字段    create table student(      id int,    name char(16) not null,    gender enum('male','female','others') default 'male'  )  insert into student(id,name) values(1,'jason')  # 成功

usinged

無符號，修飾整形和浮點型的數據類型，只存整數，且範圍變大

zerofill

給指定了寬度的數據類型的不足長度的字段不足部分用0填充

修改約束條件，不夠8位用0填充（zerofill）,會自動加上 unsigned

alter table tb1 modify id int(8) zerofill;