MySQL 约束条件

约束条件：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！
约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

约束条件的分类

• Null 允许为空，默认设置
• NOT NULL 不允许为空
• Key 索引类型
• Default 设置默认值，缺省为NULL
• Extra 额外设置

mysql> desc b1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

NULL ,NOT NULL

1、创建表格

mysql> create table t1(
    -> name char(4) not null,    ---这里意思name字段赋值不允许为空
    -> age int(3) null 			----这里age字段赋值允许为空
    -> )default charset=utf8;   ----设置默认字符集为utf8
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(4) | NO   |     | NULL    |       |
| age   | int(3)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2、给表格赋值来验证

mysql> insert into t1 values("小明",null);   往t1表里面赋值，name=小明、age为空值
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;     
+--------+------+
| name   | age  |
+--------+------+
| 小明   | NULL |
+--------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values("null"26);     往t1表里面赋值，name为空、age=26然后报错说name的值不能为空
ERROR 1048 (23000): Column 'name' cannot be null
mysql>
 mysql> alter table t1 modify name char(4) not null;  修改t1表name字段允许为空
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values("null",26);  再次输入上面的命令就没有提示报错
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+------+
| name   | age  |
+--------+------+
| 小明   | NULL |
| null   |   26 |
+--------+------+
3 rows in set (0.00 sec)

Default

1、创建表格

mysql> create table t2(
    -> name char(4)not null,
    -> age int(4)unsigned not null,  ---age值不能为空并且不能为负数
    -> likes set('上网','玩游戏','打篮球','看电影')default"玩游戏,看电影"   ---likes值可以选择('上网','玩游戏','打篮球','看电影')不填默认为"玩游戏,看电影" 
    -> )default charset=utf8;  ---设置字符集为utf8
Query OK, 0 rows affected (0.03 sec)

mysql> desc t2;
+-------+---------------------------------------------------+------+-----+---------------------+-------+
| Field | Type                                              | Null | Key | Default             | Extra |
+-------+---------------------------------------------------+------+-----+---------------------+-------+
| name  | char(4)                                           | NO   |     | NULL                |       |
| age   | int(4) unsigned                                   | NO   |     | NULL                |       |
| likes | set('上网','玩游戏','打篮球','看电影')                | YES  |     | 玩游戏,看电影       |       |
+-------+---------------------------------------------------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

2、给表格赋值来验证

mysql> insert into t2(name,age) values("小明","27");  ---给t2表赋值name=小明，age=27
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+--------+-----+---------------------+
| name   | age | likes               |
+--------+-----+---------------------+
| 小明   |  27 | 玩游戏,看电影       |
+--------+-----+---------------------+
1 row in set (0.00 sec)
从这里可以看的出来likes不赋值就默认为玩游戏,看电影

Key

1、键值类型：

• INDEX 普通索引
• UNIQUE 唯一索引
• FULLTEXT 全文索引
• PRIMARY KEY 主键
• FOREIGN KEY 外键

• 各自功能不同，共同点都可以约束字段赋值。

2、索引的优点缺点：

• 索引的优点：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2. 可以加快数据的检索速度

• 索引的缺点：

1. 当对表中的数据进行增加， 删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
2. 索引需要占要物理空间

这里只介绍“普通索引、主键、外键”
加粗样式

3、INDEX 普通索引

3.1、索引介绍

• 类似于书的目录。
• 给字段值排队，目的加快查询数据的速度。
• 索引类型包括：Btree 、B+tree、 hash

3.2、索引优缺点

• 索引优点：

1. 通过创建唯一索引，可以保证数据库表中数据的唯一性
2. 可以加快数据的查询速度

• 索引缺点：

1. 当表中数据进行增加、删除和修改时候，索引也要动态的调整，降低了数据的维护速度。
2. 索引需要占用物理空间。

3.3、使用规则

1. 一个表中可以有多个INDEX字段
2. 字段的值允许重复性，且可以赋NULL值
3. 经常把做查询条件的字段设置为INDEX字段
4. INDEX字段的KEY标志市MUL

3.4、创建表时指定索引字段：

mysql> create table b1(
    -> id int(4)unsigned not null,
    -> name char(4) not null,
    -> age  int(4) unsigned not null,
    -> sex  enum('boy','girl')default "girl",
    -> index(id),index(name)      ---把id、name创建普通索引
    -> )default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc b1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(4) unsigned    | NO   | MUL | NULL    |       |
| name  | char(4)            | NO   | MUL | NULL    |       |
| age   | int(4) unsigned    | NO   |     | NULL    |       |
| sex   | enum('boy','girl') | YES  |     | girl    |       |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from b1;   查看b1表中普通索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b1    |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| b1    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

3.5、删除指定表的索引字段：

语法：drop index 索引名 on 表名;

mysql> drop index id on b1;      ----删除b1表中id普通索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from b1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b1    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

3.6、在已有的表中设置INDEX字段

语法：create index 索引名 on 表名(字段名);

mysql> create index id on b1(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from b1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b1    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| b1    |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4、 Primary key主键

• 注意事项：

1. 一个表中只能有一个primary key字段
2. 对应的字段值不允许有重复，且不允许赋NULL值
3. 如果有多个字段作为Primary key，称为复合主键，必须一起创建。
4. 主键字段的KEY标志是PRI
5. 通常于AUTO_INCREMENT连用
6. 经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]

4.1、创建表格

mysql> create table b2(                                                                                                                                                                       
    -> stu_id int(4)unsigned,
    -> name char(4)not null,
    -> age  int(4)unsigned not null,
    -> sex  enum('boy','girl')not null,
    -> likes set('打游戏','上网','打羽毛球','打篮球')not null default"上网,打游戏",
    -> primary key(stu_id)      ----设置stu_id为主键
    -> )default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc b2;
+--------+------------------------------------------------------+------+-----+------------------+-------+
| Field  | Type                                                 | Null | Key | Default          | Extra |
+--------+------------------------------------------------------+------+-----+------------------+-------+
| stu_id | int(4) unsigned                                      | NO   | PRI | NULL             |       |
| name   | char(4)                                              | NO   |     | NULL             |       |
| age    | int(4) unsigned                                      | NO   |     | NULL             |       |
| sex    | enum('boy','girl')                                   | NO   |     | NULL             |       |
| likes  | set('打游戏','上网','打羽毛球','打篮球')             | NO   |     | 打游戏,上网      |       |
+--------+------------------------------------------------------+------+-----+------------------+-------+
5 rows in set (0.00 sec)

4.2、给表格赋值来验证

mysql> insert into b2 values("1","小明","22","boy","上网,打羽毛球");
Query OK, 1 row affected (0.00 sec)

mysql> select * from b2;
+--------+--------+-----+-----+---------------------+
| stu_id | name   | age | sex | likes               |
+--------+--------+-----+-----+---------------------+
|      1 | 小明   |  22 | boy | 上网,打羽毛球       |
+--------+--------+-----+-----+---------------------+
1 row in set (0.00 sec)
mysql> insert into b2 values("1","小红","23","girl","上网,打羽毛球");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
*上面报错是因为stu_id字段值是主键所以不允许有重复的出现。*
mysql> insert into b2 values("null","小红","23","girl","上网,打羽毛球");
ERROR 1366 (HY000): Incorrect integer value: 'null' for column 'stu_id' at row 1
*上面报错是因为stu_id字段值是主键所以不允许有null的出现。*

4.3、Primary key 复合主键：

mysql> create table b3(
    -> clientip char(15),
    -> serport  int(4)unsigned,
    -> status enum('allow','deny'),
    -> primary key(clientip,serport)   ---创建clientip和serport为复合主键
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc b3;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   | PRI | NULL    |       |
| serport  | int(4) unsigned      | NO   | PRI | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注意：复合主键需要所有的字段值都一样才不能输入进去。

4.4、给表格赋值来验证

mysql> insert into b3 values("192.168.2.10","22","deny");
Query OK, 1 row affected (0.00 sec)

mysql> insert into b3 values("192.168.2.10","21","deny");
Query OK, 1 row affected (0.00 sec)

mysql> insert into b3 values("192.168.2.10","21","deny");
ERROR 1062 (23000): Duplicate entry '192.168.2.10-21' for key 'PRIMARY'
这里报错是应为clientip和serport的值重复不符合复合主键的规则
mysql> select * from b3;
+--------------+---------+--------+
| clientip     | serport | status |
+--------------+---------+--------+
| 192.168.2.10 |      21 | deny   |
| 192.168.2.10 |      22 | deny   |
+--------------+---------+--------+
2 rows in set (0.00 sec)

4.5、删除主键

mysql> alter table b3 drop primary key;  删除b3表主键
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc b3;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   |     | NULL    |       |
| serport  | int(4) unsigned      | NO   |     | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.6、Primary key 和AUTO_INCREMENT 连用

注意：auto_increment必须和主键在一起用然后必须是数值类型，不能单独使用；

• 创建表格

mysql> create table b4(
    -> id int primary key auto_increment,  ---设置id为主键并且自动增长
    -> name char(4) not null,
    -> age int(4)unsigned not null,
    -> sex enum('boy','girl')
    -> )default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc b4;
+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| id    | int(11)            | NO   | PRI | NULL    | auto_increment |
| name  | char(4)            | NO   |     | NULL    |                |
| age   | int(4) unsigned    | NO   |     | NULL    |                |
| sex   | enum('boy','girl') | YES  |     | NULL    |                |
+-------+--------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

4.6、给表格赋值来验证

mysql> insert into b4(name,age,sex) values("小明","28","boy"),("小兰","29","gril");
Query OK, 1 row affected (0.00 sec)

mysql> select * from b4;
+----+--------+-----+------+
| id | name   | age | sex  |
+----+--------+-----+------+
|  1 | 小明   |  28 | boy  |
|  2 | 小兰   |  29 | girl |
+----+--------+-----+------+
2 rows in set (0.00 sec)

4.7、删除主键

注意：假如表中有auto_increment要想删除主键首先先删除auto_increment在删除主键

• 在已有的表格里删除主键

  alter  table 表名  drop  primary key;

• 在已有的表格添加主键

alter  table 表名 add primary key(字段名);

mysql> alter table b4 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto 
column and it must be defined as a key
报错的意思是需要删除auto_increment才能删除主键。
mysql> alter table b4 modify id int(11);   删除b4表中auto_increment字段
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table b4 drop primary key;  删除b4表中主键字段
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> desc b4;       ----确认主键是否删除成功
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            | NO   |     | NULL    |       |
| name  | char(4)            | NO   |     | NULL    |       |
| age   | int(4) unsigned    | NO   |     | NULL    |       |
| sex   | enum('boy','girl') | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
##添加id为主键
mysql> alter table t5 add primary key(id);
mysql> desc t5;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            | NO   |     | <null>  |       |
| name  | char(4)            | NO   |     | <null>  |       |
| age   | int(4) unsigned    | NO   |     | <null>  |       |
| sex   | enum('boy','girl') | YES  |     | <null>  |       |
+-------+--------------------+------+-----+---------+-------+
##添加自动增进字段auto_increment
mysql> alter table t5 modify id int auto_increment;
mysql> desc t5;
+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| id    | int(11)            | NO   | PRI | <null>  | auto_increment |
| name  | char(4)            | NO   |     | <null>  |                |
| age   | int(4) unsigned    | NO   |     | <null>  |                |
| sex   | enum('boy','girl') | YES  |     | <null>  |                |
+-------+--------------------+------+-----+---------+----------------+

5、FOREIGN KEY 外键

5.1、 什么是外键？

1. 让当前表字段的值在另一个表字段值的范围内选择。

5.2、 使用外键的条件：

2. 表的存储引擎必须是innodb
3. 字段类型要一致
4. 被参照字段必须要是索引类型的一种（primary key)

5.3、基本用法：

foreign key(表A的字段名） references 表B(字段名)
on update cascade 同步更新
on delete cascade 同步删除

5.4、创建表格

注意：创建外键需要两个表格

创建表格yg

mysql> create table yg(
    -> yg_id int primary key auto_increment,
    -> name char(4)
    -> )engine=innodb default charset=utf8;  --设置存储引擎为innodb,字符集为utf8
Query OK, 0 rows affected (0.00 sec)

mysql> desc yg;      ---查看yg表结构
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| yg_id | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | char(4) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

给yg表添加数据

mysql> insert into yg(name) values("小明"),("小兰"),("小红");   
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from yg;
+-------+--------+
| yg_id | name   |
+-------+--------+
|     1 | 小明   |
|     2 | 小兰   |
|     3 | 小红   |
+-------+--------+
3 rows in set (0.00 sec)

创建gz表

mysql> create table gz(
    -> gz_id int primary key,
    -> pay float(7,2),
    -> foreign key(gz_id) references yg(yg_id) 设置外键
    -> on update cascade   同步更新
    -> on delete cascade   同步删除
    -> )engine=innodb;
  Query OK, 0 rows affected (0.00 sec)
mysql> desc gz;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | NO   | PRI | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec) 

给gz表添加数据
注意：gz表gz_id字段必须是yg表yg_id字段值的范围内选择

mysql> insert into gz values(1,12000),(2,13000),(3,15000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from gz;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 12000.00 |
|     2 | 13000.00 |
|     3 | 15000.00 |
+-------+----------+
2 rows in set (0.00 sec)
MySQL多表查询
mysql> select gz.gz_id,yg.name,gz.pay from gz,yg where gz.gz_id=yg.yg_id;
提取yg表的gz_id、pay和yg表的yg_id 的信息显示出来
+-------+--------+----------+
| gz_id | name   | pay      |
+-------+--------+----------+
|     1 | 小明   | 12000.00 |
|     2 | 小兰   | 13000.00 |
|     3 | 小红   | 15000.00 |
+-------+--------+----------+
3 rows in set (0.00 sec)
###同步删除yg_id=1
mysql> delete from yg where yg_id=1;
mysql> select * from yg; select * from gz;
+-------+------+
| yg_id | name |
+-------+------+
| 2     | 小兰 |
| 3     | 小红 |
+-------+------+

2 rows in set
Time: 0.007s

+-------+---------+
| gz_id | pay     |
+-------+---------+
| 2     | 13000.0 |
| 3     | 15000.0 |
+-------+---------+

2 rows in set
Time: 0.009s
##同步更新
mysql> update yg set yg_id=1 where yg_id=2;
mysql> select * from yg; select * from gz;
+-------+------+
| yg_id | name |
+-------+------+
| 1     | 小兰 |
| 3     | 小红 |
+-------+------+

2 rows in set
Time: 0.008s

+-------+---------+
| gz_id | pay     |
+-------+---------+
| 1     | 13000.0 |
| 3     | 15000.0 |
+-------+---------+