day38:MySQL資料庫之約束&索引&外鍵&存儲引擎

目錄

part1:數據類型

part2:約束

part3:主鍵索引 PRI &唯一索引 UNI &普通索引 MUL

part4:外鍵:foreign key

part5:在外鍵中設置聯級刪除和聯級更新

part6:表和表之間的關係

part7:存儲引擎

part8:關於約束的添加和刪除

part1:數據類型

date YYYY-MM-DD 年月日 (結婚紀念日,節假日)

time HH:MM:SS 時分秒 (體育競賽)

year YYYY 年份值 (歷史,酒的年份)

datetime YYYY-MM-DD HH:MM:SS 年月日 時分秒 (用戶登錄時間,下單時間)

    create table t1(d date , t time , y year , dt datetime);
    insert into t1 values("2020-08-25","08:25:30","2020","2020-08-25 08:25:30")
    insert into t1 values(now(),now(),now(),now())

t1表中內容如下所示

timestamp YYYYMMDDHHMMSS(時間戳) 自動更新時間(不需要手動,系統自動更新時間) 數據上一次的修改時間

    create table t2(dt datetime , ts timestamp);
    insert into t2 values(null,null);
    insert into t2 values(20200825082530,20200825082530);
    insert into t2 values(20200825082530,20380825082530); # error 不能超過2038年的某一年

t2表中內容如下所示

part2:約束

unsigned 無符號

not null 不為空

default 設置默認值

unique 唯一約束,數據唯一不重複

primary key 主鍵,標記數據的唯一特徵(唯一且不為空)

auto_increment 自增加1(一般配合主鍵使用, 或 unique進行自增)

zerofill 零填充(配合整型int使用) int(11) , 位數不夠11位,拿0補充

foreign key 外鍵,把多張表通過一個關聯欄位聯合在一起,(這個欄位可以加外鍵)

1.unsigned 無符號

# unsigned      無符號
    create table t3(id int unsigned);
    insert into t3 values(100);
    insert into t3 values(-100); error

下圖是t3表的詳情參數

2.not null 不為空

# not null      不為空
    create table t4(id int not null , name varchar(255));
    insert into t4 values(1,"宋雲傑");
    insert into t4 values(null,"宋雲傑"); # error id不可為空
    insert into t4(name) values('abc');  # error id不可為空

下圖是t4表的詳情參數

3.default 設置默認值 

# default       設置默認值
    create table t5(id int not null,name varchar(255) default '高雪峰');
    insert into t5 values(1,null);
    insert into t5(id) values(2);

下圖是t5表的詳情參數

下圖是t5表的存儲內容

4.unique  唯一約束,數據唯一不重複

索引:相當於字典的目錄,通過索引可以加快查詢的速度

UNI 唯一索引,允許插入NULL空值

    create table t6(id int unique , name varchar(255) default '戈隆');
    insert into t6(id) values(1);
    insert into t6(id) values(1); # error id是唯一約束，不能有兩個1
    insert into t6(id) values(null); 
    insert into t6(id) values(null);

下圖是t6表的詳情參數

5.primary key 主鍵,標記數據的唯一特徵(唯一且不為空)

1.PRI 主鍵 非空且唯一 在一個表裡只能有一個主鍵

 

not null unqiue約等於primary key

    create table t7(id int not null unique , name varchar(255) default '戈隆');
    insert into t7 values(1,"1122")
    insert into t7 values(null,"1122") # error id不能為空

下圖是t7表的詳情參數

2.primary key 創建主鍵

    create table t8(id int primary key , name varchar(255) default 'libolun' );
    insert into t8 values(1,"ppp")

下圖是t8表的詳情參數

3.兩者同時存在 (優先顯示primary key 作為主鍵,另一個設置成UNI 唯一索引)

    create table t9(id int primary key , name char(3) not null unique);

下圖是t9表的詳情參數

4.一個表裡只能有一個主鍵

    create table t10(id int primary key , name char(3) primary key); error

當創建表中有多個主鍵，會出現如下錯誤提示

6.auto_increment 自增加1(一般配合主鍵使用, 或 unique進行自增)

    create table t11(id int primary key auto_increment , name varchar(255) default 'Alan')
    insert into t11 values(1,"Wuming")
    insert into t11 values(null,"Huahai")
    insert into t11(id) values(null)    
    # 使用默認值自動插入
    insert into t11 values()
    
    # delete 只刪除數據,id號保留
    delete from t11 ;
    # truncate 刪除所有數據 + 重置id
    truncate table t11;

下圖是t11表的詳情參數

下圖是t11表的存儲內容

7.zerofill  零填充(配合整型int使用) int(11) , 位數不夠11位,拿0補充

    create table t12(id int(8) zerofill);
    insert into t12 values(2)
    insert into t12 values(123456789)

下圖是t12表的詳情參數

下圖是t12表中的存儲內容

part3:主鍵索引 PRI &唯一索引 UNI &普通索引 MUL

1.聯合唯一約束(欄位都設置成not null + unique 顯示PRI , 聯合在一起表達一種唯一性) 

格式：unique(欄位1,欄位2,欄位3 … ) 把多個欄位拼在一起表達唯一的數據

    create table t1_server(id int , name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));
    insert into t1_server values(1,"aaa","192.168.1.1",3306);
    insert into t1_server values(1,"aaa","192.168.1.1",3306); # error ip,port為聯合唯一索引，不可重複
    insert into t1_server values(1,"aaa","192.168.1.1",443); 
    insert into t1_server values(1,"aaa","192.168.1.255",443);

下圖是t1_server表的詳情參數

下圖是t1_server表的存儲內容

2.聯合唯一約束(欄位不設置成not null)

    create table t2_server(id int ,name varchar(255) not null,ip char(15) ,port int , unique(ip,port));
    insert into t2_server values(1,"aaa","192.168.65.135",3306);
    insert into t2_server values(1,"aaa",null,null); # 注意點,允許插入多個空值;
    insert into t2_server values(1,"aaa",null,null);
    insert into t2_server values(1,"aaa",null,null);
    insert into t2_server values(1,"aaa",null,null);
    insert into t2_server values(1,"aaa",null,null);

下圖是t2_server表的詳情參數

下圖是t2_server表的存儲內容

3.聯合唯一索引 和 主鍵 之間是否可以同時存在?

unique(ip,port) 聯合唯一索引

primary key(ip,port) 聯合主鍵

這兩個用法一模一樣,區別:前者可以繼續添加一個主鍵,後者不能再額外添加主鍵

主鍵可以是單個欄位,也可以是聯合主鍵,設置多個單欄位做主鍵不行的.

    create table t3_server(id int , name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));

現在t3_server表的詳情參數是這樣的

如果現在設置id是主鍵

    alter table t3_server add primary key(id);

 

part4:外鍵:foreign key

外鍵:把多張表通過一個關聯欄位聯合在一起,(這個欄位可以加外鍵) [可設置成聯級更新和刪除]

 

要注意的是:外鍵所關聯的其他欄位必須具有唯一屬性 unique 或者 primary key

創建class表:

    # 創建class1
    create table class1(id int , classname varchar(255))
    
    # 刪除索引
    alter table class1 drop index id

    # 添加索引
    alter table class1 add unique(id);

創建student表，並且在class1表和student1表中添加數據

    # 創建student1
    create table student1(
    id int primary key auto_increment, 
    name varchar(255), 
    age int , 
    classid int,
    foreign key(classid) references class1(id)
    );
        
    # 添加數據
    insert into class1 values(1,"python30");
    insert into class1 values(2,"python31");    
    insert into class1 values(3,"python32");
        
    insert into student1 values(null,"yuanweizhuo",88,2);
    insert into student1 values(null,"lihuling",99,2);
    insert into student1 values(null,"wangwen",18,3);
    
    # 刪除class1裡面的python31這個班級  (報錯刪不掉,因為有其他數據關聯該班級)
    delete from class1 where id = 2;

    # 需要先把關聯的其他數據都刪掉之後再刪,才能成功
    delete from student1 where id = 1;
    delete from student1 where id = 2;

part5:在外鍵中設置聯級刪除和聯級更新

聯級刪除 on delete cascade

聯級更新 on update cascade

    # 創建class2
    create table class2(id int unique , classname varchar(255))    ;
        
    # 創建student2
    create table student2(
    id int primary key auto_increment, 
    name varchar(255), 
    age int , 
    classid int,
    foreign key(classid) references class2(id) on delete cascade on update cascade
    );
        
    # 添加數據
    insert into class2 values(1,"python30");
    insert into class2 values(2,"python31");    
    insert into class2 values(3,"python32");
        
    insert into student2 values(null,"yuanweizhuo",88,2);
    insert into student2 values(null,"lihuling",99,2);
    insert into student2 values(null,"wangwen",18,3);

    # 聯級刪除
    delete from class2 where id = 2

    # 聯級更新
    update class2 set id = 100  where classname = "python32";

part6:表和表之間的關係

1.一對一 : 表1 id z1 z2 z3 .. 表2 id z4 z5 z6 (可以設置z3為關聯欄位且唯一 , 關聯表2中的唯一一個id)

2.一對多 或者 多對一 : 一個班級裡面可以由多個學生,在學生表中創建一個關聯欄位,關聯班級,把關聯欄位設置成外鍵,去存儲班級的id

3.多對多 : 一個學生可以學習多個學科,一個學科也可以被多個學生學習。一本書可以被多個作者共同撰寫,一個作者也可以寫多本書.

如果表和表是多對多的話，需要注意設置第三張關係表

part7:存儲引擎

show engines：查看所有的存儲引擎

一些相關的概念：

　　1.表級鎖: 如果有人修改當前這個表,會直接上鎖,其他用戶無法進行修改,不能進行高並發.

　　2.行級鎖: 如果有人修改當前這個表中的一條記錄,當前這條數據會被鎖定,其他數據仍然可以被修改,速度快,允許高並發

　　3.事務處理: 執行sql語句時,必須所有的操作全部成功,最終提交數據,否則數據回滾,回到剛開始沒操作的那個狀態.

　　　　begin : 開啟事務

　　　　commit: 提交數據

　　　　rollback: 回滾數據

常見的四種存儲引擎：

　　1.MyISAM : 支援表級鎖(5.6版本前默認存儲引擎)

　　2.InnoDB : 事務處理,行級鎖,外鍵(5.6版本後默認存儲引擎)

　　3.MEMORY : 把數據放在記憶體中,做一個臨時的快取

　　4.BLACKHOLE : 黑洞,產生binlog日誌,不產生真實數據

　　　　　　   用來同步主從資料庫中的數據,場景發生在多伺服器集群中 (一主一從,一主多從,主資料庫:增刪改,從資料庫:查)

用這四種引擎創建表時，所生成的文件是不一樣的：

'''create table myisam1(id int , name varchar(255)) engine = MyISAM;'''
# myisam1.frm  表結構
# myisam1.MYD  表數據
# myisam1.MYI  表索引

'''create table innodb1(id int , name varchar(255)) engine = InnoDB;'''
# innodb1.frm  表結構
# innodb1.ibd  表數據 + 表索引

'''create table memory1(id int , name varchar(255)) engine = MEMORY;'''
# memory1.frm 表結構
沒有數據文件的,因為所有的數據都臨時存儲在記憶體之中

'''create table blackhole1(id int , name varchar(255)) engine = BLACKHOLE;'''
# blackhole1.frm 表結構
記憶體中不存儲任何值

part8:關於約束的添加和刪除

# 關於約束的添加和刪除
# 1 添加/刪除 約束 not null
    #alter table 表名 modify 欄位名 類型
    alter table t1 modify id int not null
    alter table t1 modify id int

# 2 添加/刪除 unique 唯一索引
    # alter table 表名 add unique(id)
    alter table t1 add unique(id)
    alter table t1 drop index id
    
# 3 添加/刪除 primary key
    # alter table 表名 add primary key(id);
    alter table t1 add primary key(id);
    alter table t1 drop primary key;
    
# 4 添加/刪除 foreign key 外鍵 (show create table student1 找到外鍵名字,然後再刪)
    alter table student1 drop foreign key student1_ibfk_1; #刪除
    alter table student1 add foreign key(classid) references class1(id) #添加
Tags:

VirMach 便宜 VPS

QNews

QNews