mysql--表的约束

约束条件与数据类型的宽度一样，都是修饰表的可选参数

作用：用于保证数据的完整性和一致性

主要分为以下几类：

　　PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
　　FOREIGN KEY (FK)    标识该字段为该表的外键
　　NOT NULL    标识该字段不能为空
　　UNIQUE KEY (UK)    标识该字段的值是唯一的
　　AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
　　DEFAULT    为该字段设置默认值
　　UNSIGNED 无符号
　　ZEROFILL 使用0填充
　　还有一些联合组合使用方法
说明：

　　1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
　　2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
　　sex enum('male','female') not null default 'male'
　　age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
　　3. 是否是key
　　主键 primary key
　　外键 foreign key
　　索引 (index,unique...)
一：not null 与 default
　　是否可空，null表示空，非字符串
　　not null-不可空
　　null-可空
　　
　　默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则默认添加默认值

　　　　create table tb1(nid int not null defalut 2,num int not null);
　　验证：
　　　　==================not null====================
　　　　mysql> create table t1(id int); #id字段默认可以插入空
　　　　mysql> desc t1;
　　　　+-------+---------+------+-----+---------+-------+
　　　　| Field | Type    | Null | Key | Default | Extra |
　　　　+-------+---------+------+-----+---------+-------+
　　　　| id    | int(11) | YES  |     | NULL    |       |
　　　　+-------+---------+------+-----+---------+-------+
　　　　mysql> insert into t1 values(); #可以插入空


　　　　mysql> create table t2(id int not null); #设置字段id不为空
　　　　mysql> desc t2;
　　　　+-------+---------+------+-----+---------+-------+
　　　　| Field | Type    | Null | Key | Default | Extra |
　　　　+-------+---------+------+-----+---------+-------+
　　　　| id    | int(11) | NO   |     | NULL    |       |
　　　　+-------+---------+------+-----+---------+-------+
　　　　mysql> insert into t2 values(); #不能插入空
　　　　ERROR 1364 (HY000): Field 'id' doesn't have a default value



　　　　==================default====================
　　　　#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
　　　　mysql> create table t3(id int default 1);
　　　　mysql> alter table t3 modify id int not null default 1;


　　
　　　　==================综合练习====================
　　　　mysql> create table student(
   　　　　 -> name varchar(20) not null,
   　　　　 -> age int(3) unsigned not null default 18,
   　　　　 -> sex enum('male','female') default 'male',
   　　　　 -> hobby set('play','study','read','music') default 'play,music'
   　　　　 -> );
　　　　mysql> desc student;
　　　　+-------+------------------------------------+------+-----+------------+-------+
　　　　| Field | Type                               | Null | Key | Default    | Extra |
　　　　+-------+------------------------------------+------+-----+------------+-------+
　　　　| name  | varchar(20)                        | NO   |     | NULL       |       |
　　　　| age   | int(3) unsigned                    | NO   |     | 18         |       |
　　　　| sex   | enum('male','female')              | YES  |     | male       |       |
　　　　| hobby | set('play','study','read','music') | YES  |     | play,music |       |
　　　　+-------+------------------------------------+------+-----+------------+-------+
　　　　mysql> insert into student(name) values('egon');
　　　　mysql> select * from student;
　　　　+------+-----+------+------------+
　　　　| name | age | sex  | hobby      |
　　　　+------+-----+------+------------+
　　　　| egon |  18 | male | play,music |　　　　+------+-----+------+------------+
二 unique　　
　　============设置唯一约束 UNIQUE=============== 　　
方法一： 　　　　
　　create table department1( id int, name varchar(20) 　　　　
　　unique, comment varchar(100) ); 　　
方法二：　　 
　　create table department2( id int, name varchar(20),　　 
　　comment varchar(100), constraint uk_name unique(name) );　　 
　　mysql> insert into department1 values(1,'IT','技术');　　 
　　Query OK, 1 row affected (0.00 sec) 　　 
　　mysql> insert into department1 values(1,'IT','技术'); 　　 
　　ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'　　
not null + unique的化学反应

　　mysql> create table t1(id int not null unique);
　　Query OK, 0 rows affected (0.02 sec)

　　mysql> desc t1;
　　+-------+---------+------+-----+---------+-------+
　　| Field | Type    | Null | Key | Default | Extra |
　　+-------+---------+------+-----+---------+-------+
　　| id    | int(11) | NO   | PRI | NULL    |       |
　　+-------+---------+------+-----+---------+-------+
　　row in set (0.00 sec

联合唯一
　　create table service(
　　id int primary key auto_increment,
　　name varchar(20),
　　host varchar(15) not null,
　　port int not null,
　　unique(host,port) #联合唯一
　　);

　　mysql> insert into service values
    　　-> (1,'nginx','192.168.0.10',80),
    　　-> (2,'haproxy','192.168.0.20',80),
    　　-> (3,'mysql','192.168.0.30',3306)
    　　-> ;
　　Query OK, 3 rows affected (0.01 sec)
　　Records: 3  Duplicates: 0  Warnings: 0

　　mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
　　ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
三 primary key
　　

　　primary key字段的值不为空且唯一

　　一个表中可以：

　　单列做主键
　　多列做主键（复合主键）

　　但一个表内只能有一个主键primary key

 单列主键
　　============单列做主键===============
　　#方法一：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)

　　#方法二：在某一个字段后用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)

　　#方法三：在所有字段后单独定义primary key
　　create table department3(
　　id int,
　　name varchar(20),
　　comment varchar(100),
　　constraint pk_name primary key(id); #创建主键并为其命名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)
多列主键

　　==================多列做主键================
　　create table service(
　　ip varchar(15),
　　port char(5),
　　service_name varchar(10) not null,
　　primary key(ip,port)
　　);


　　mysql> desc service;
　　+--------------+-------------+------+-----+---------+-------+
　　| Field        | Type        | Null | Key | Default | Extra |
　　+--------------+-------------+------+-----+---------+-------+
　　| ip           | varchar(15) | NO   | PRI | NULL    |       |
　　| port         | char(5)     | NO   | PRI | NULL    |       |
　　| service_name | varchar(10) | NO   |     | NULL    |       |
　　+--------------+-------------+------+-----+---------+-------+
　　rows in set (0.00 sec)

　　mysql> insert into service values
    　　-> ('172.16.45.10','3306','mysqld'),
    　　-> ('172.16.45.11','3306','mariadb')
   　　 -> ;
　　Query OK, 2 rows affected (0.00 sec)
　　Records: 2  Duplicates: 0  Warnings: 0

　　mysql> insert into service values ('172.16.45.10','3306','nginx');
　　ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
四 auto_increment
　　约束字段为自动增长，被约束的字段必须同时被key约束
#不指定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
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | 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 | egon | male   |
|  2 | alex | 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('egon');
Query OK, 1 row affected (0.01 sec)

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

员工信息表有三个字段：工号  姓名  部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：

我们完全可以定义一个部门表

然后让员工信息表关联该表，如何关联，即foreign key

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（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,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');


#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;


#删父表department，子表employee中对应的记录跟着删
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中对应的记录跟着改
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 |
+----+-------+--------+