一肴敛、介紹
PRIMARY KEY (PK) 標(biāo)識(shí)該字段為該表的主鍵蛀骇，可以唯一的標(biāo)識(shí)記錄
FOREIGN KEY (FK) 標(biāo)識(shí)該字段為該表的外鍵
NOT NULL 標(biāo)識(shí)該字段不能為空
UNIQUE KEY (UK) 標(biāo)識(shí)該字段的值是唯一的
AUTO_INCREMENT 標(biāo)識(shí)該字段的值自動(dòng)增長(zhǎng)（整數(shù)類型，而且為主鍵）
DEFAULT 為該字段設(shè)置默認(rèn)值
UNSIGNED 無(wú)符號(hào)
ZEROFILL 使用0填充
二交洗、主鍵
============單列做主鍵===============
#方法一：not null+unique
create table department1(
id int not null unique, #主鍵
name varchar(20) not null unique,
comment varchar(100)
);
mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
#方法二：在某一個(gè)字段后用primary key
create table department2(
id int primary key, #主鍵
name varchar(20),
comment varchar(100)
);
mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)
#方法三：在所有字段后單獨(dú)定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #創(chuàng)建主鍵并為其命名pk_name
mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
三劣挫、Foreign Key
代碼(建立一對(duì)多關(guān)系)
#表類型必須是innodb存儲(chǔ)引擎，且被關(guān)聯(lián)的字段傲绣，即references指定的另外一個(gè)表的字段掠哥，必須保證唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;
#dpt_id外鍵，關(guān)聯(lián)父表（department主鍵id）秃诵，同步更新续搀，同步刪除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade
)engine=innodb;
#先往父表department中插入記錄
insert into department values
(1,'歐德博愛(ài)技術(shù)有限事業(yè)部'),
(2,'艾利克斯人力資源部'),
(3,'銷售部');
#再往子表employee中插入記錄
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'劉飛機(jī)',3),
(7,'張火箭',3),
(8,'林子彈',3),
(9,'加特林',3)
;
#刪父表department，子表employee中對(duì)應(yīng)的記錄跟著刪
mysql> delete from department where id=3;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 2 | alex1 | 2 |
| 3 | alex2 | 2 |
| 4 | alex3 | 2 |
+----+-------+--------+
#更新父表department菠净，子表employee中對(duì)應(yīng)的記錄跟著改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 3 | alex2 | 22222 |
| 4 | alex3 | 22222 |
| 5 | alex1 | 22222 |
+----+-------+--------+
建立多對(duì)多關(guān)系
create table author(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20)
);
#這張表就存放作者表與書表的關(guān)系目代，即查詢二者的關(guān)系查這表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
