#返回顶部按钮 #生成一个目录导航
xingchenck
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)
posted on 2020-01-09 15:51  xingchenck  阅读(172)  评论(0)  编辑  收藏  举报

Copyright © 2024 xingchenck
Powered by .NET 9.0 on Kubernetes Powered By博客园