MySQL 约束条件

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

约束条件的分类

  • Null 允许为空,默认设置
  • NOT NULL 不允许为空
  • Key 索引类型
  • Default 设置默认值,缺省为NUL
  • 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 普通索引

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

3.1、建表制定索引字段:

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.2、删除指定表的索引字段:

语法: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.3、在已有的表中设置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在删除主键

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)

5、FOREIGN KEY 外键

  • 什么是外键?
  1. 让当前表字段的值在另一个表字段值的范围内选择。
  • 使用外键的条件:
  1. 表的存储引擎必须是innodb
  2. 字段类型要一致
  3. 被参照字段必须要是索引类型的一种(primary key)
  • 基本用法:
    foreign key(表A的字段名) references 表B(字段名)
    on update cascade 同步更新
    on delete cascade 同步删除

5.1、创建表格

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

创建表格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 update 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)
  • 1
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值