表的约束

发表于

表的描述

空属性

  • null（默认）和not null（不为空）
  • 注意：在开发过程中尽量避免字段不为空，数据为空是没有办法参与运算的。’’
1
2
3
mysql> create table myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null);

默认值

  • 某一种数据会经常出现某个具体的值，可以在一开始指定好，在需要帧数数据的时候，用户可以选择性的使用默认值。
1
2
3
4
mysql> create table tt10 (
    -> name varchar(20) not null,
    -> age tinyint unsigned default 
    -> sex char(2) default '男');

列描述

  • comment，没有实际含义，专门用来描述字段，在创建表时保存
1
2
3
4
mysql> create table tt12 (
    -> name varchar(20) not null comment '姓名',
    -> age tinyint unsigned default 0 comment '年龄',
    -> sex char(2) default '男' comment '性别');

comment通过desc看不到注释信息，需要通过show关键字查询

1
2
3
4
5
6
7
8
9
10
mysql> show create table tt12/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show create table tt12\G;
*************************** 1. row ***************************
       Table: tt12
Create Table: CREATE TABLE `tt12` (
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

zerolfill

  • 相当于自动填充功能（格式化输出），假如指定一个字段的长度为5并且向该字段插入一个数据1后，当你在查询时，就会将宽度小于设定宽度的地方自动填充为0，显示为00001
1
2
3
4
5
6
mysql> select * from tt3;
+-------+------+
|     a |    b |
+-------+------+
| 00001 |    2 |
+-------+------+
  • 在查看时也可以通过hex函数来表示
1
2
3
4
5
6
mysql> select a, hex(a) from tt3;
+-------+--------+
|     a | hex(a) |
+-------+--------+
| 00001 |      1 |
+-------+--------+

主键

primary key：用来唯一约束该字段里边的内容，不能重复，不能为空，一张表中最多只能有一个主键，并且主键所在的列通常是整数类型。

  • 在创建表时，直接在字段上指定主键
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table tt13(
    -> id int unsigned primary key comment '学号不能为空',
    -> name varchar(20) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> desc tt13;                  主键
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
  • 复合主键，可以在创建表的最后，使用复合主键来设置多个字段
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table tt14(
    -> id int unsigned,-> course char(10) comment '代码',
    -> score tinyint unsigned default 60 comment '成绩',
    -> primary key(id, course) -- id和course为复合
    ->);
mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     |int(10) unsigned     | NO   | PRI | 0       |       | 
| course | char(10)            | NO   | PRI |         |       |
| score  | tinyint(3) unsigned | YES  |     | 60 	  |		  |
+--------+---------------------+------+-----+---------+-------+
  • 追加主键
1
alter table 表名 add primary key(字段列表)
  • 主键约束：主键对应的字段不能重复，一旦重复，操作失败
  • 删除主键
1
alter table 表明 drop primary key;

自增长

auto_increment：会自动在当前字段已有的最大值+1操作，得到一个新的不同值。通常与主键搭配使用，作为逻辑主键。

特点：

  • 任何一个字段要做自增长，前提本身是一个索引
  • 自增长字段必须为整数
  • 一张表最多有一个自增长
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> create table tt21(
    -> id int unsigned primary key auto_increment,
    -> name varchar(10) not null default ''
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into tt21(name) values('b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

在插入后获取最近一次auto_increment的值（批量插入获取的是第一个值）

1
2
3
4
5
6
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+

唯一键（unique）

一张表中可以多个唯一键，唯一键与主键差不多，数据不能重复，但是唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

外键

外键用于定义主表与从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为空

语法

1
foreign key (字段名) references 主表(列)

举例：班级表与学生表

  • 创建主键表
1
2
3
4
mysql> create table class(
    -> id int primary key,
    -> name varchar(20) not null comment '班级名'
    -> );
  • 创建从表
1
2
3
4
5
6
mysql> create table stu(
    -> id int primary key,
    -> name varchar(20) not null comment '班级名',
    -> class_id int,
    -> foreign key (class_id) references class(id) 外键约束
    -> );
  • 插入数据
1
2
3
4
5
6
7
8
9
10
mysql> insert into class values(10,'16班'),(20,'15班');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu values(100,'张三',10),(101,'李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu values(102,'王五',null);   外键可以为空
Query OK, 1 row affected (0.00 sec)