MySQL列屬性約束及存儲引擎

MySQL列屬性約束及存儲引擎

列屬性約束，保證表的結構和數據的正確性和穩定性。

總的來說有五種：唯一性和主鍵約束、外鍵約束、檢查約束、空值約束、默認值約束。
五大關鍵詞：UNIQUE和Primary Key, Foreign Key, CHECK, NOT NULL, DEFAULT

是否為空

規定一個字段的值，是否可以是null。
null(默認) 或 not null
Null表示沒有值。與任何數據不同。表示什麼都沒有。
如果一個列不允許為空，但是在賦值時，沒有為該字段賦值，則會出現問題。
Mysql的每條記錄，如果存在可以為null的字段，則需要使用一個字節保存哪些字段是空。

create table one(
a int not null,
b int 
);
insert into one (a) values(10);
select * from one;

默認值屬性

使用default value來聲明
DEFAULT子句用於為列指定一個默認，當該字段不存在值時，會被啟用。
默認值必須為一個常數，不能為一個函數或一個表達式。
可以不存在default，但是mysql會判斷：
如果該列可以為空，則默認值為null；如果不可以，則不設置默認值。

注意：
可以在處理數據時，使用default，顯示地使用默認值。
有些列是不能有默認值的：Blob，text。
時間戳類型，可以設置一個特殊的默認值 CURRENT_TIMESTAMP。在當列不存在或者傳遞的值為null時，使用當前的時間戳。

drop table two;
create table two(
a int not null default 10,
b int not null default 20, 
c int default 30,
d int
);
insert into two(a) values(10);
insert into two(b) values(10);
select * from two;


默認值，在沒有為該字段設置值是啟用。
而且默認值的設置需要使用固定值。<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwPjxjb2RlIGNsYXNzPQ=="hljs sql">常見場景
一個字段不能為空，而且存在默認值。

主鍵約束

主鍵（PK，primary key）就是可以唯一標識某條記錄的字段或者是字段的集合。
主關鍵字是表中的一個或多個字段，要求主鍵的值不能重復，只能有一個主鍵。
如果還存在不重復的記錄，可以定義成唯一索引，提高檢索效率。
主鍵不能為空，不設置或者null都會變成not null。

主鍵可以是真實實體的屬性。
但是常用的好的解決方案是：
利用一個與實體信息不相關的屬性，作為唯一標識。
主鍵與業務邏輯不發生關系。只用來標識記錄。（自身保證不沖突，主鍵不屬於當前實體的任何屬性。）

設置主鍵的語法：通過primary key完成
兩種方案：
1、字段上設置

drop table teacher;
create table teacher(
t_id int primary key,
t_name varchar(10),
classname varchar(6),
days tinyint unsigned
)character set gbk;
insert into teacher values(1,'周傑倫','0324',32);
insert into teacher values(-1,'劉德華','0334',35);

show create table teacher\G

主鍵不能為空，也不能重復。一個字段是主鍵的話，自動設置不為空。

2、在定義完字段後，可以定義
通過這種方式可以定義多列主鍵。

create table teacher(
t_id int,
t_name varchar(10),
classname varchar(6),
days tinyint unsigned,
primary key (t_id)
);

組合主鍵

drop table teacher;
create table teacher(
t_name varchar(10),
classname varchar(6),
days tinyint unsigned,
primary key (t_name,classname)
);

注意：
組合主鍵表示一個主鍵內包括多個字段，而不是多個字段都是主鍵；只需要一個唯一標識即可，mysql規定只能存在一個主鍵。

可以通過修改表來刪除主鍵 alter table tbl_name drop primary key;
主鍵字段原則上在插入後，應該不被修改，但是語法上可以修改，但是修改的值不能與已有值沖突。

主鍵常見的設計：
每個表都應該存在一個可以唯一標識的主鍵字段，
最好與實體沒有聯系，不是實體屬性字段。

自動增長

為每條記錄提供給一個唯一的標識。
每次插入記錄時，將每個字段的值自動增加1。
使用auto_increment標識。

需要整型，還需要有索引。

通常自動增長是從1開始遞增，但是可以通過修改表屬性，更改初始值。
表屬性 auto_increment=x;（如果比已存在的小，則會從已有的最大值新記錄）

drop table teacher;
create table teacher(
t_id int primary key auto_increment,
t_name varchar(10),
classname varchar(6),
days tinyint unsigned
);
insert into teacher values(null,'周傑倫','0324',32);
insert into teacher values(null,'劉德華','0334',35);

insert into teacher (t_name,classname,days)values('周韋彤','0324',31);

select * from teacher;

自動增長的初始值：是可以設置的，默認是1。
通過表的選項：auto_increment n

alter table teacher auto_increment 10;
select * from teacher;
insert into teacher values(null,'范冰冰','0334',35);
select * from teacher;

自動增長是否可以手動插入該列的值？
仍然可以手動插入。
如果是主鍵的話，該列不能重復，否則，可以重復。
insert into teacher values(7,’范冰冰’,’0334’,35);

自動增長該列是否可以更新？
可以

update teacher set t_id=100 where t_name='范冰冰';
delete from teacher where t_name='范冰冰';

實體之間的關系

1:1 一對一
兩個實體表內，存在相同的主鍵字段。
如果記錄的主機值等於另一個關系表內的記錄的主機主鍵值，則兩條記錄一一對應。

1:n 一對多
一個實體，對應多個其他實體
例如：一個班級對應多個學生

設計：
在多的那端，增加一個字段，
用於指向該實體所屬的另外的實體的唯一標識，即主鍵。

m:n 多對多
設計：
典型的是利用一個中間表，表示實體之間的關系。

中間表的每個記錄，表示一個關系。
一個M:N可以通過1:M，1:N來實現。

外鍵

如果一個實體(student)的某個字段(class_id)，指向（引用）另一個實體(class)的主鍵(class_id)，就稱student實體的class_id是外鍵。

被指向的實體（class），稱之為主實體，也叫（父實體）。
負責指向的實體，稱之為從實體（從表），也叫子實體（子表）。

作用：
用於約束處於關系內的實體。

需要解決的問題？
增加子表記錄時，是否有與之對應的父表記錄。
在刪除或者更新主表記錄時，從表應該如何處理相關的記錄。

如何定義一個外鍵？
在從表上，增加一個字段，指向主表的主鍵。
使用關鍵字 foreign key

drop table if exists class;
create table class(
class_id int primary key auto_increment,
class_name varchar(20) not null default 'deeplearning' comment '班級名稱'
)character set utf8;

set names gbk;客戶端與服務器端通信的編碼
character set utf8這裡的utf8是數據存儲到服務器的編碼。

drop table if exists student;
create table student(
stu_id int primary key auto_increment,
stu_name varchar(10) not null default '',
class_id int,
foreign key (class_id) references class(class_id)
)character set utf8;

必須主表裡面先有數據，才能在從表中插入數據。

insert into class values (null,'c program design');
insert into student values (null,'張三豐',1);

設置級聯操作
在主表數據發生改變時，與之關聯的從表數據應該如何處理。
主表更新，主表刪除時：使用關鍵字on update，on delete來標識。

允許的級聯動作：
cascade關聯操作，如果主表被更新或刪除，那麼從表也會執行相應的操作。
set null，設置null，表示從表不指向任何主表記錄。
restrict，拒絕主表的相關操作。

修改外鍵：
先刪除，再新建，通過修改表完成。
alter table table_name drop foreign key(class_id);
刪除外鍵需要用指定外鍵名稱達到目的，可以通過在創建外鍵時指定名稱
或者使用mysql默認生成的名稱。
show create table student\G

alter table student drop foreign key student_ibfk_1;

增加外鍵，設置刪除時將從表的數據設置為null
alter table table_name add foreign key(外鍵);

alter table student add foreign key (class_id) 
references class(class_id)
on delete set null;

show create table student\G

select * from class;
select * from student;

delete from class where class_id=2;
select * from class;
select * from student;

on delete
增加外鍵，設置刪除時將從表的數據同時也刪除。

insert into class values (1,'c program design');
update student set class_id=1 where stu_id = 1; 

alter table student drop foreign key student_ibfk_1;

alter table student add foreign key (class_id) 
references class(class_id)
on delete cascade;

select * from class;
select * from student;
delete from class where class_id=1;
select * from class;
select * from student;

on update
指的是只有主表的主鍵發生變化，才會對從表產生影響。

insert into class values (1,'c program design');
insert into student values (1,'張三豐',1);

select * from class;
select * from student;

alter table student drop foreign key student_ibfk_1;
alter table student add foreign key (class_id) 
references class(class_id)
on delete cascade
on update restrict;

update class set class_id=2 where class_id=1;不能操作成功

更新存在關聯的主表數據的主鍵字段

alter table student drop foreign key student_ibfk_1;
alter table student add foreign key (class_id) 
references class(class_id)
on delete cascade
on update cascade;

存儲引擎


默認的服務器表類型，通過my.ini可以配置。
default-storage-engine=INNODB

在創建表或編輯表時，可以指定表的存儲引擎。
利用表屬性：engine 引擎類型。
通過engine myisam,engine innodb 在創建時指定。

alter table class engine myisam; 不能操作成功，有外鍵。

注意：外鍵只被innodb存儲引擎所支持。其他引擎是不支持的。

create table classroom(
room_id int primary key auto_increment,
room_no char(3)
)engine myisam character set utf8;

myisam與innodb區別
1、保存文件的方式不同
myisam:一個表三個文件。tablename.frm結構，tablename.myd數據，tablename.myi索引
innodb:一個表一個文件。tablename.frm結構
所有的innodb表，都使用相同的innodb存儲表空間在保存數據和索引。

2、Innodb很多時候是行級鎖，而myisam是表級鎖，innodb的並發高

3、MyIASM支持索引壓縮，而Innodb索引和數據是綁定保存不壓縮，體積大。

4、InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，
但是MyISAM只要簡單的讀出保存好的行數即可。
注意：當count(*)語句包含 where條件時，兩種表的操作是一樣的。

myisam適於插入，查找。
innodb適於更新，刪除。

選擇存儲引擎的依據：
1、性能
2、功能