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
　　)；

　　先说一点：在我们插入数据的时候，可以这么写insert into tb1(nid，num) values（1，‘chao’）；就是在插入输入的时候，指定字段插入数据，如果我在只给num插入值，可以这样写insert into tb1(num) values('chao');还可以插入数据的时候，指定插入数据字段的顺序：把nid和num换个位置，但是对应插入的值也要换位置。注意：即便是你只给一个字段传值了，那么也是生成一整条记录，这条记录的其他字段的值如果可以为空，那么他们就都是null空值，如果不能为空，就会报错。

 

not null和default测试

==================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('chao');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| chao|  18 | male | play,music |
+------+-----+------+------------+

not null和default测试

注意一点：如果是非严格模式，int类型不传值的话会默认为0，因为null不是int类型的，字段是int类型，所以他会自动将null变为0

 

三 unique

　　独一无二，唯一属性：id，身份证号等

　　是一种key，唯一键，是在数据类型之外的附加属性，其实还有加速查询的作用。

　　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'

unique创建

联合唯一:

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字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

　　主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

　　

　　一个表中可以：

　　　　单列做主键
　　　　多列做主键（复合主键或者叫做联合主键）

unique key和primary key都是MySQL的特殊类型，不仅仅是个字段约束条件，还称为索引，可以加快查询速度，这个索引功能我们后面再讲，现在只讲一下这些key作为约束条件的效果。

    关于主键的强调内容：
        1.一张表中必须有，并且只能由一个主键字段：innodb引擎下存储表数据的时候，会通过你的主键字段的数据来组织管理所有的数据，将数据做成一种树形结构的数据结构，帮你较少IO次数，提高获取定位数据、获取数据的速度，优化查询。
            解释：如果我们在一张表中没有设置primary key，那么mysql在创建表的时候，会按照顺序从上到下遍历你设置的字段，直到找到一个not null unique的字段，自动识别成主键pri，通过desc可以看到，这样是不是不好啊，所以我们在创建表的时候，要给他一个主键，让他优化的时候用，如果没有pri也没有not null unique字段，那么innodb引擎下的mysql被逼无奈，你没有设置主键字段，主键又有不为空且唯一的约束，又不能擅自给你的字段加上这些约束，那么没办法，它只能给你添加一个隐藏字段来帮你组织数据，如果是这样，你想想，主键是不是帮我们做优化查询用的啊，这个优化是我们可以通过主键来查询数据：例如：如果我们将id设置为主键，当我们查一个id为30的数据的时候，也就是select * from tb1 where id=30；这个查询语句的速度非常快，不需要遍历前面三十条数据，就好像我们使用的字典似的，找一个字，不需要一页一页的翻书，可以首先看目录，然后看在哪一节，然后看在哪一页，一步步的范围，然后很快就找到了，这就像我们说的mysql的索引(主键、唯一键)的工作方式，一步一步的缩小范围来查找，几步就搞定了，所以通过主键你能够快速的查询到你所需要的数据，所以，如果你的主键是mysql帮你加的隐藏的字段，你查询数据的时候，就不能将这个隐藏字段作为条件来查询数据了，就不能享受到优化后的查询速度了，对么
        
        2.一张表里面，通常都应该有一个id字段，而且通常把这个id字段作为主键，当然你非要让其他的字段作为主键也是可以的，看你自己的设计，创建表的时候，一般都会写create table t1(id int primary key);id int primary key这个东西在建表的时候直接就写上

关于主键的通俗解释和强调内容（重点*****）

 

在没有设置主键的时候 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)

在没有设置主键的时候，not null+unique会被默认当成主键

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

单列主键测试

 

联合主键
        和联合唯一是类似的，
        mysql> create table t10(
            ->id int,
            ->port int,
            ->primary key(id,port)
            -> );
        Query OK, 0 rows affected (0.45 sec)

        mysql> desc t10;
        +-------+---------+------+-----+---------+-------+
        | Field | Type    | Null | Key | Default | Extra |
        +-------+---------+------+-----+---------+-------+
        | id    | int(11) | NO   | PRI | 0       |       | 
        | port  | int(11) | NO   | PRI | 0       |       |
        +-------+---------+------+-----+---------+-------+
rows in set (0.10 sec)
      
        看key，两个都写的是pri，两个联合起来作为主键，他们两个作为一个主键，不能再有其他的主键了，也就是在创建表的时候，只能出现一次primary key方法。
        有同学说，老师，我不写primary key行不，只写一个not null unique字段，当然行，但是我们应该这样做吗，是不是不应该啊，所以以后设置主键的时候，就使用primary key来指定

联合主键解释

==================多列做主键================
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

　　之前我们插入数据的时候，id也需要自己来写，是不是很麻烦啊，我们是不是想，只要有一条记录就直接插入进去啊，不需要考虑说，你现在存储到第多少条数据了，对不对，所以出现了一个叫做auto_increment的属性

　　约束字段为自动增长，被约束的字段必须同时被key约束，也就是说只能给约束成key的字段加自增属性，默认起始位置为1，步长也为1.

#不指定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)

auto_increment测试

 

步长：auto_increment_increment 起始偏移量：auto_increment_offset

#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

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

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

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver：自增步长
    基于表级别
    create table t1（
        id int。。。
    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）


#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
比如：设置auto_increment_offset=3，auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

步长：auto_increment_increment 起始偏移量：auto_increment_offset

六 foreign key

　　一 快速理解foreign key（外键其实就是标明表和表之间的关系，表和表之间如果有关系的话就三种：一对一，多对一，多对多，我们挨个看看~）

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

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

　　　　那这就体现出来了三个缺点：

　　　　　　1.表的组织结构不清晰：员工的信息、部门的信息等等都掺在一张表里面。

　　　　　　2.浪费空间，每一条信息都包含员工和部门，多个员工从属一个部门，也需要每个员工的信息里都包含着部门的信息，浪费硬盘空间。

　　　　　　3.扩展性极差：如果想修改一个部门的信息，比如修改部门名称，那么这个包含员工和部门信息的表中的所有的包含这个部门信息的数据都需要进行修改，那么修改起来就非常麻烦，这是非常致命的缺点。

　　　　解决方法：（画一个excel表格来表示一下效果~~）

　　　　　　我们完全可以定义一个部门表，解耦和

　　　　　　我们虽然将部门表提出来了，但是员工表本身是和部门有联系的，你光把部门信息提出来还是不够的，还需要建立关联

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