mysql-限制介紹

一、限制介紹

限制是一種限制，它通過對表的行或列的資料做出限制，來確定資料的完整性、一緻性。限制條件與資料類型寬度一樣都是可選參數。

常用限制：

PRIMARY KEY (PK)    辨別該字段為該表的主鍵，可以唯一的辨別記錄
FOREIGN KEY (FK)    辨別該字段為該表的外鍵
NOT NULL    辨別該字段不能為空
UNIQUE KEY (UK)    辨別該字段的值是唯一的
AUTO_INCREMENT    辨別該字段的值自動增長（整數類型，而且為主鍵）
DEFAULT    為該字段設定預設值

UNSIGNED 無符号
ZEROFILL 使用0填充      

（一）、not null與default

not null 用于限制列不允許為空

null 列的預設限制為null  允許為空

default 預設值，建立列時可以指定其預設值，插入資料為設定時，自動添加為預設值。

mysql> create table test1(id int not null,name varchar(11) default 'aaa');
Query OK, 0 rows affected (0.26 sec)

mysql> insert into test1 value(null,'abc');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test1(id) value(1);
Query OK, 1 row affected (0.29 sec)

mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)      

test

（二）、unique

設定唯一限制，當你需要限定你的某個表字段每個值都唯一,沒有重複值時使用。

允許為空

mysql> create table test2(id int not null ,name varchar(11),phone int(11) unique);
Query OK, 0 rows affected (0.50 sec)

mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(11) | YES  |     | NULL    |       |
| phone | int(11)     | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test2 value(1,'abc',111111);
Query OK, 1 row affected (0.29 sec)

mysql> insert into test2 value(1,'abc',111111);
ERROR 1062 (23000): Duplicate entry '111111' for key 'phone'
mysql>      

聯合唯一：（多個唯一）

mysql> create table test3 (id int not null,name varchar(11),phone int(11),unique(id,phone));
Query OK, 0 rows affected (0.47 sec)
mysql> insert into test3 value(1,'aaa',123);
Query OK, 1 row affected (0.29 sec)

mysql> insert into test3 value(1,'bbb',123);
ERROR 1062 (23000): Duplicate entry '1-123' for key 'id'
mysql> insert into test3 value(1,'bbb',555);
Query OK, 1 row affected (0.29 sec)      

（四）、primary key

站在限制角度看primary key=not null unique。主鍵primary key是innodb存儲引擎組織資料的依據，innodb稱之為索引組織表，一張表中必須有且隻有一個主鍵。

主鍵限制列不允許重複，也不允許出現空值。每個表最多隻允許一個主鍵，建立主鍵限制可以在列級别建立，也可以在表級别建立。當建立主鍵的限制時，系統預設會在所在的列和列組合上建立對應的唯一索引。（通常id字段被設定為主鍵）

-- 基本模式
create table temp( 
id int primary key,
name varchar(20)
);

-- 組合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);      

（五）、auto_increment

 限制字段為自動增長

#不指定id，則自動增長
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('aaa'),
    -> ('bbb')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | aaa | male |
|  2 | bbb | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | aaa | male   |
|  2 | bbb | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#對于自增的字段，在用delete删除後，再插入值，該字段仍按照删除前的位置繼續增長
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#應該用truncate清空表，比起delete一條一條地删除記錄，truncate是直接清空表，在删除大表時用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('aaa');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | aaa | male |
+----+------+------+
row in set (0.00 sec)      

（六）、foreign key

外鍵限制是保證一個或兩個表之間的參照完整性，外鍵是建構于一個表的兩個字段或是兩個表的兩個字段之間的參照關系。

現在有兩個表，第一個學生表 有三個字段，學号、姓名、班級 第二個學校表 有班級， 老師 等字段， 每個學生都有班級，那班級這個字段就需要重複存儲，很浪費資源，我們可以建一個班級表，讓學生關聯這個班級表。

#表類型必須是innodb存儲引擎，且被關聯的字段，即references指定的另外一個表的字段，必須保證唯一
create table class(
id int primary key,
name varchar(20) not null
)
#cls_id外鍵，關聯父表（department主鍵id），同步更新，同步删除
create table student(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(cls)
references class(id)
on delete cascade
on update cascade 
)engine=innodb;      

 foreign key注意：

 1、被關聯的字段必須是一個key，通常是id字段

 2、建立表時：必須先建立被關聯的表，才能建立關聯表

 3、插入記錄時：必須先往被關聯的表插入記錄，才能往關聯表中插入記錄

 4、删除時：應該先删除關聯表中的記錄，再删除被關聯表對應的記錄

二、表與表之間的關系

如何才能找出兩張表之間的關系呢？

分析步驟：
#1、先站在左表的角度去找
是否左表的多條記錄可以對應右表的一條記錄，如果是，則證明左表的一個字段foreign key 右表一個字段（通常是id）

#2、再站在右表的角度去找
是否右表的多條記錄可以對應左表的一條記錄，如果是，則證明右表的一個字段foreign key 左表一個字段（通常是id）

#3、總結：
#多對一：
如果隻有步驟1成立，則是左表多對一右表
如果隻有步驟2成立，則是右表多對一左表

#多對多
如果步驟1和2同時成立，則證明這兩張表時一個雙向的多對一，即多對多,需要定義一個這兩張表的關系表來專門存放二者的關系

#一對一:
如果1和2都不成立，而是左表的一條記錄唯一對應右表的一條記錄，反之亦然。這種情況很簡單，就是在左表foreign key右表的基礎上，将左表的外鍵字段設定成unique即可      

建立表與表之間的關系：

多對一：

=====================多對一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);


insert into press(name) values
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識産權沒有用出版社')
;

insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨孤九劍',3),
('降龍十巴掌',2),
('葵花寶典',3)
;      

View Code

多對多：

=====================多對多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);


#這張表就存放作者表與書表的關系，即查詢二者的關系查這表就可以了
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)
);


#插入四個作者，id依次排開
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每個作者與自己的代表作如下
1 egon: 
      1 九陽神功
      2 九陰真經
      3 九陰白骨爪
      4 獨孤九劍
      5 降龍十巴掌
      6 葵花寶典


2 alex: 
      1 九陽神功
      6 葵花寶典

3 yuanhao:
      4 獨孤九劍
      5 降龍十巴掌
      6 葵花寶典

4 wpq:
      1 九陽神功


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;      

一對一：

#一定是student來foreign key表customer，這樣就保證了：
#1 學生一定是一個客戶，
#2 客戶不一定是學生，但有可能成為一個學生


create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);


create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #該字段一定要是唯一的
foreign key(customer_id) references customer(id) #外鍵的字段一定要保證unique
on delete cascade
on update cascade
);


#增加客戶
insert into customer(name,qq,phone) values
('李飛機','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴彈','283818181',1867141331),
('吳坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('戰地雷','112312312',18811431230)
;


#增加學生
insert into student(class_name,customer_id) values
('美術一班',3),
('聲樂二班',4),
('美術一班',5)
;      

焚膏油以繼晷，恒兀兀以窮年。