mysql 数据库 III（约束）

1.今日内容

1. not null 非空约束，指定某列不能为空

2. defauit 设置默认值

3. unique 唯一约束，指定某列或者几列组合不能重复

   • 唯一
   • 联合唯一

4. auto_increment

   • 自增。针对int

   • 自带。not null

   • 前提。需要设置unique

5. primary key 主键，指定该列的值可以为宜地标识该列记录

   • 相当于 ：非空 + 唯一
   • 一张表只能有一个，并且必须有一个
   • 联合主键

6. foreign key 外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

   • 外键约束
     • 约束的字段至少unique
     • 级联删除： on delete cascade
     • 级联更新： on update cascade

• not null 不能为空

  not null 示例

  ```mysql mysql> create database day39; Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
  +--------------------+
  | Database |
  +--------------------+
  | information_schema |
  | day39 |
  | mes |
  | mysql |
  | performance_schema |
  | sys |
  +--------------------+
  6 rows in set (0.01 sec)

  mysql> use day39
  Database changed
  mysql> create table t1(id int not null);
  Query OK, 0 rows affected (0.03 sec)

  mysql> show tables;
  +-----------------+
  | Tables_in_day39 |
  +-----------------+
  | t1 |
  +-----------------+
  1 row in set (0.00 sec)

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

  不能向id列插入空元素

  mysql> insert into t1 values(null);
  ERROR 1048 (23000): Column 'id' cannot be null

  mysql> insert into t1 values (1);
  Query OK, 1 row affected (0.01 sec)

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

  </details>
  
  
  

• defauit 设置默认值

  默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

  defauit 示例

  ```mysql mysql> create table t2 (id int not null, id2 int not null default 222); Query OK, 0 rows affected (0.01 sec)

  mysql> desc t2;
  +-------+---------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | id | int(11) | NO | | NULL | |
  | id2 | int(11) | NO | | 222 | |
  +-------+---------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  只向id字段添加值，会发现id2字段会使用默认值填充

  mysql> insert into t2(id) values (111);
  Query OK, 1 row affected (0.01 sec)

  mysql> select * from t2;
  +-----+-----+
  | id | id2 |
  +-----+-----+
  | 111 | 222 |
  +-----+-----+
  1 row in set (0.00 sec)

  id字段不能为空，所以不能单独向id2字段填充

  mysql> insert into t2 (id2) values (212);

  ERROR 1364 (HY000): Field 'id' doesn't have a default value

  向id、id2字段分别填充数据，id2的填充数据会覆盖默认值

  mysql> insert into t2(id ,id2) values (123,234);
  Query OK, 1 row affected (0.00 sec)

  mysql> select * from t2;
  +-----+-----+
  | id | id2 |
  +-----+-----+
  | 111 | 222 |
  | 123 | 234 |
  +-----+-----+
  2 rows in set (0.01 sec)

  </details>
  
  
  + 设置严格模式
  
  ```mysql
  设置严格模式：
  	不支持对not null 字段插入null值；
  	不支持对自增长字段插入值；
  	不支持text字段有默认值；
  	
  直接在mysql中生效（重启）：
  mysql> set sql_mide = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
  
  配置文件添加（永久生效）：
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

• unique

  唯一约束，指定某列或者几列组合不能重复

  • 唯一

  unique示例
  
  #方法一
  mysql> create table department1(
      -> id int,
      -> name varchar(20) unique,
      -> comment varchar(100)
      -> );
  Query OK, 0 rows affected (0.01 sec)
  
  #方法二
  mysql> create table department2(
      -> id int,
      -> name varchar(20),
      -> comment varchar(100),                                                        		
      -> unique (name)
      -> );
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> insert into department1 values(1,'IT','技术');
  Query OK, 1 row affected (0.01 sec)
  
  #name重复报错
  mysql> insert into department1 values(1,'IT','技术');
  ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
  

  not null 和 unique的结合
  
  
  mysql> create table t3(
      -> id int not null unique
      -> );
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> desc t3;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | id    | int(11) | NO   | PRI | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  1 row in set (0.00 sec)
  

  • 联合唯一

  mysql> create table service(
      -> id int primary key auto_increment,
      -> name varchar(20),
      -> host varchar(15) not null,
      -> port int not null,
      -> unique (host,port)
      -> );
  Query OK, 0 rows affected (0.01 sec)
  
  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.00 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  mysql> select * from service;
  +----+---------+--------------+------+
  | id | name    | host         | port |
  +----+---------+--------------+------+
  |  1 | nginx   | 192.168.0.10 |   80 |
  |  2 | haproxy | 192.168.0.20 |   80 |
  |  3 | mysql   | 192.168.0.30 | 3306 |
  +----+---------+--------------+------+
  3 rows in set (0.01 sec)
  
  #host与port重复报错
  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'
  

• auto_increment

  • 自增。只能操作数字

  • 自带。自带非空属性，not null

  • 前提。只对unique字段金星设置

  • 不受删除影响的

  mysql> create table t4(
      -> id int unique auto_increment,
      -> name char(12) not null
      -> );
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> desc t4;
  +-------+----------+------+-----+---------+----------------+
  | Field | Type     | Null | Key | Default | Extra          |
  +-------+----------+------+-----+---------+----------------+
  | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
  | name  | char(12) | NO   |     | NULL    |                |
  +-------+----------+------+-----+---------+----------------+
  2 rows in set (0.00 sec)
  
  #不指定id，则自动增长
  mysql> insert into t4(name) values ('大圣');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from t4;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 大圣   |
  +----+--------+
  1 row in set (0.00 sec)
  
  #也可以指定id
  mysql> insert into t4 values (3,'');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> insert into t4 values (6,'齐天');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from t4;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 大圣   |
  |  3 |        |
  |  6 | 齐天   |
  +----+--------+
  3 rows in set (0.00 sec)
  
  #对于自增字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
  mysql> delete from t4;
  Query OK, 3 rows affected (0.01 sec)
  
  mysql> insert into t4(name) values ('大圣');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from t4;
  +----+--------+
  | id | name   |
  +----+--------+
  |  7 | 大圣   |
  +----+--------+
  1 row in set (0.00 sec)
  
  #应该用truncate清空表，
  mysql> truncate t4;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> insert into t4(name) values ('大圣');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from t4;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 大圣   |
  +----+--------+
  1 row in set (0.00 sec)
  

  了解

  auto_increment了解

  ```mysql #在创建表后，修改自增字段的起始值 mysql> alter table t4 auto_increment = 10; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

  mysql> show create table t4;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
  | t4 | CREATE TABLE t4 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name char(12) NOT NULL,
  UNIQUE KEY id (id)
  ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  mysql> insert into t4(name) values ('奇天');
  Query OK, 1 row affected (0.00 sec)

  mysql> select * from t4;
  +----+--------+
  | id | name |
  +----+--------+
  | 1 | 大圣 |
  | 10 | 奇天 |
  +----+--------+
  2 rows in set (0.00 sec)

  也可以在创建表时，指定auto_increment的初始值，⚠️初始值的设置为表选项，应该放到括号外

  mysql> create table t5(
  -> id int primary key auto_increment,
  -> name varchar(20)
  -> )auto_increment = 3;
  Query OK, 0 rows affected (0.01 sec)

  mysql> show create table t5;
  +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
  | t5 | CREATE TABLE t5 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
  +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  </details>
  
  
  

• primary key

  主键为了保证表中的每一条数据的该字段都是表哥中的唯一值。也就是说，它是用来唯一确认表格中的每一行数据。

  主键可以包含一个字段或多个字段。当主键包含多个字段时，称为组合键，也叫联合主键。

  • 相当于 ：非空 + 唯一
  • 一张表只能有一个，并且必须有一个
  • 联合主键

  #单字段主键
  
  #方法一：not null + unique
  mysql> create table department3(                                                    		
         -> id int not null unique,     #主键                                   
         -> name varchar(20) not null unique,                                            		
         -> comment varchar(100));                                                 
  

Query OK, 0 rows affected (0.02 sec)

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

方法二：在某个字段后用primary key

mysql> create table department4(
-> id int primary key, #主键
-> name varchar(20),
-> comment varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

方法三：在所有字段后单独定义primary key

mysql> create table department5(
-> id int,
-> name varchar(20),
-> comment varchar(100),
-> primary key(id)); #设置主键
Query OK, 0 rows affected (0.02 sec)

mysql> desc department5;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

方法四：给已经建成的表添加主键约束

mysql> create table department6(
-> id int,
-> name varchar(20),
-> comment varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> desc department6;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table department6 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc department6;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

+ ### 多字段主键

```mysql
mysql> create table service1(
    -> ip varchar(15),
    -> service_name varchar(10) not null,                                           		
    -> port char(5),
    -> primary key (ip,port));   #联合主键                                                
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> select * from service1;
+--------------+--------------+------+
| ip           | service_name | port |
+--------------+--------------+------+
| 172.16.45.10 | mysql        | 3306 |
| 172.16.45.20 | mariadb      | 3306 |
+--------------+--------------+------+
2 rows in set (0.00 sec)

#ip与port组合唯一，重复报错
mysql> insert into service1 values('172.16.45.10','nginx','3306');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
mysql> 

• foreign key

  多表：

  ​ 假如要描述公司的所有员工，例如：工号，姓名，部门等

  ​ 假如部门有3个，员工5千，如果用一张表去存储这些信息，可以看到部门这个字段大量的重复存储，部门名字越长，越浪费

  ​ 解决方法：再去定义一个部门表，然后让员工表去关联该表，即foreign key

  • 外键约束

    • 约束的字段至少unique

    mysql> create table departments(
        -> dep_id int(4),
        -> dep_name varchar(11));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> desc departments;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | YES  |     | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> create table staff_info ( 
        -> s_id int,
        -> name varchar(20),
        -> dep_id int,
        -> foreign key(dep_id) references departments(dep_id));                    
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc departments;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | YES  | UNI | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    #当设置字段为unique唯一字段时，设置该字段为外键成功
    mysql> alter table departments modify dep_id int(4) unique;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> create table staff_info ( 
        -> s_id int,
        -> name varchar(20),
        -> dep_id int,
        -> foreign key(dep_id) references departments(dep_id));                     
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc departments;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | YES  | UNI | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    

    • 外键操作

      • 级联删除： on delete cascade
      • 级联更新： on update cascade

    mysql> create table employee(
        -> id int primary key,
        -> name varchar(20) not null,
        -> dpt_id int,
        -> foreign key(dpt_id) references departments(dep_id) 
        -> on delete cascade  #级连删除
        -> on update cascade  #级连更新
        -> );
    Query OK, 0 rows affected (0.02 sec)
    
    #给父表departments中插入数据
    mysql> insert into departments values
        -> (1,'质教部'),
        -> (2,'技术部'),
        -> (3,'人力资源部');
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    #给子表employee中插入记录
    mysql> insert into employee values
        -> (1,'yuan',1),(2,'nezha',2),(3,'egon',2),(4,'alex',2),
        -> (5,'wusir',3),
        -> (6,'李沁洋',3),
        -> (7,'皮卡丘',3),
        -> (8,'程咬金',3),
        -> (9,'程咬银',3);
    Query OK, 9 rows affected (0.01 sec)
    Records: 9  Duplicates: 0  Warnings: 0
    
    mysql> select * from employee;
    +----+-----------+--------+
    | id | name      | dpt_id |
    +----+-----------+--------+
    |  1 | yuan      |      1 |
    |  2 | nezha     |      2 |
    |  3 | egon      |      2 |
    |  4 | alex      |      2 |
    |  5 | wusir     |      3 |
    |  6 | 李沁洋    |      3 |
    |  7 | 皮卡丘    |      3 |
    |  8 | 程咬金    |      3 |
    |  9 | 程咬银    |      3 |
    +----+-----------+--------+
    9 rows in set (0.00 sec)
    
    #删除父表departments中的数据，子表employee中对应的记录也被删除
    mysql> delete from departments where id = 2;
    ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
    mysql> delete from departments where dep_id = 2;
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from employee;
    +----+-----------+--------+
    | id | name      | dpt_id |
    +----+-----------+--------+
    |  1 | yuan      |      1 |
    |  5 | wusir     |      3 |
    |  6 | 李沁洋    |      3 |
    |  7 | 皮卡丘    |      3 |
    |  8 | 程咬金    |      3 |
    |  9 | 程咬银    |      3 |
    +----+-----------+--------+
    6 rows in set (0.00 sec)
    
    #更新父表departments中的数据，子表employee中对应的记录也被更新
    mysql> update departments set dep_id = 4 where dep_id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from employee;
    +----+-----------+--------+
    | id | name      | dpt_id |
    +----+-----------+--------+
    |  1 | yuan      |      4 |
    |  5 | wusir     |      3 |
    |  6 | 李沁洋    |      3 |
    |  7 | 皮卡丘    |      3 |
    |  8 | 程咬金    |      3 |
    |  9 | 程咬银    |      3 |
    +----+-----------+--------+
    6 rows in set (0.00 sec)