数据库索引与约束

1、表属性

　　创建表的基本语法：　

　　　　create table 【if not exists】 表名 (字段列表 【，索引或约束列表】）【表选项列表】

　　其中，字段列表格式如下：

　　　　字段名 类型 【属性列表】，

　　　　字段名 类型 【属性列表】...

　　属性列表中各个属性之间用空格隔开。

　　常用的字段属性：

auto_increment	设置字段值自动增长，用于整数类型
primary key	设置字段为主键，此时该字段的值可以“唯一确定”一行数据
unique key	设置字段为唯一的，在整个数据表中不会重复
not null	设置字段不能为null，如果不指定该属性，那么字段的值默认是可以为null的
default	设置字段的默认值，插入数据的时候，若不指定该字段你的值，会使用默认值填充。
comment	设置字段的说明

　　说明：primary key跟unique key都确定了字段的唯一性，由这两个属性修饰的字段都能唯一的确定一行数据，区别在于primary key不能为null（指定了primary key的时候，默认的指定了not null属性），而unique key则可以为null。可以说，primary key是特殊的unique key。

代码：

/*创建表，注意属性*/
mysql> create table item_properties_table(
    -> id int auto_increment primary key,
    -> name varchar(20) not null unique key,
    -> pwd varchar(48) not null,
    -> age tinyint default 18,
    -> email varchar(50) comment '电子邮件'
    -> );
Query OK, 0 rows affected (0.02 sec)

/*查看表结构，主要是为了查看comment*/
mysql> show full columns from item_properties_table;
+-------+------------+-----------------+------+-----+---------+--------------+---------------------------------+---------+
| Field | Type       | Collation       | Null | Key | Default |Extra         | Privileges                      | Comment |
+-------+------------+-----------------+------+-----+---------+--------------+---------------------------------+---------+
| id    | int(11)    | NULL            | NO   | PRI | NULL    |auto_increment| select,insert,update,references |         |
| name  | varchar(20)| utf8_general_ci | NO   | UNI | NULL    |              | select,insert,update,references |         |
| pwd   | varchar(48)| utf8_general_ci | NO   |     | NULL    |              | select,insert,update,references |         |
| age   | tinyint(4) | NULL            | YES  |     | 18      |              | select,insert,update,references |         |
| email | varchar(50)| utf8_general_ci | YES  |     | NULL    |              | select,insert,update,references | 电子邮件 |
+-------+------------+-----------------+------+-----+---------+--------------+---------------------------------+---------+
5 rows in set (0.00 sec)

mysql> 

 

2、索引

　　索引是帮助mysql高效获取数据的数据结构，由系统维护。

　　要使用索引，就要建立索引，就是指定一个表的某个或某些字段作为“索引字段”。格式如下：

　　　　索引类型（字段名）

　　 索引类型：

　　　　（1）、普通索引

　　　　　　　　格式：key（字段名）

　　　　（2）、唯一索引

　　　　　　　　格式：unique key（字段名）

　　　　（3）、主键索引

　　　　　　　　格式：primary key（字段名）

　　　　（4）、外键索引

　　　　　　　　格式：foreign key（字段名）references 其他表（字段名)

　　　　（5）、全文索引

　　　　　　　　格式：fulltext（字段名）

普通索引、主键索引、唯一索引代码：

mysql> create table indexes_table(#创建表，演示索引的创建
    -> id int auto_increment,
    -> name varchar(20),
    -> email varchar(50),
    -> age int,                   #没有为age建立索引
    -> key(email),                #为email建立普通索引
    -> primary key(id),           #为id建立主键索引
    -> unique key(name)           #为name建立唯一索引
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show full columns from indexes_table;
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-------+
| Field | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      |Comment|
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-------+
| id    | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |       |
| name  | varchar(20) | utf8_general_ci | YES  | UNI | NULL    |                | select,insert,update,references |       |
| email | varchar(50) | utf8_general_ci | YES  | MUL | NULL    |                | select,insert,update,references |       |
| age   | int(11)     | NULL            | YES  |     | NULL    |                | select,insert,update,references |       |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-------+
4 rows in set (0.00 sec)

 

这样，就建立了索引。此时，以id，name或者email做条件来进行查找，速度会很快，如果以age做条件进行查找，相对就会慢一些。

 

外键索引代码：

mysql> create table classes(#班级表
    -> id int auto_increment primary key, 
    -> classid varchar(10) unique key comment '班级号码',
    -> teacher varchar(10) comment '班主任',
    -> opendate date comment '开班日期'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table students(#学生表
    -> id int auto_increment primary key,
    -> name varchar(20),
    -> age tinyint,
    -> classid int comment '所在班级的id',
    -> foreign key(classid) references classes(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show full columns from classes;
+--------+-----------+---------------+------+-----+---------+---------------+---------------------------------+----------+
|Field   |Type       |Collation      | Null | Key | Default | Extra         | Privileges                      | Comment  |
+--------+-----------+---------------+------+-----+---------+---------------+---------------------------------+----------+
|id      |int(11)    |NULL           | NO   | PRI | NULL    | auto_increment| select,insert,update,references |          |
|classid |varchar(10)|utf8_general_ci| YES  | UNI | NULL    |               | select,insert,update,references | 班级号码  |
|teacher |varchar(10)|utf8_general_ci| YES  |     | NULL    |               | select,insert,update,references | 班主任    |
|opendate|date       |NULL           | YES  |     | NULL    |               | select,insert,update,references | 开班日期  |
+--------+-----------+---------------+------+-----+---------+---------------+---------------------------------+----------+
4 rows in set (0.00 sec)

mysql> show full columns from students;
+-------+-----------+-----------------+------+-----+--------+--------------+---------------------------------+-----------+
|Field  |Type       | Collation       | Null | Key | Default|Extra         | Privileges                      | Comment   |
+-------+-----------+-----------------+------+-----+--------+--------------+---------------------------------+-----------+
|id     |int(11)    | NULL            | NO   | PRI | NULL   |auto_increment| select,insert,update,references |           |
|name   |varchar(20)| utf8_general_ci | YES  |     | NULL   |              | select,insert,update,references |           |
|age    |tinyint(4) | NULL            | YES  |     | NULL   |              | select,insert,update,references |           |
|classid|int(11)    | NULL            | YES  | MUL | NULL   |              | select,insert,update,references | 所在班级的id|
+-------+-----------+-----------------+------+-----+--------+--------------+---------------------------------+-----------+
4 rows in set (0.00 sec)

 

tips：外键——某个表中的某个字段，必须在另一个表中存在（字段名字可以不同，但是意义要一样，比如上面学生表中的classid与班级表中的id）。

 

3、约束

　　约束，就是对数据的一种要求。

　　约束类型：

　　（1）、主键约束

　　　　　　格式：primary key（字段名）

　　　　　　作用：该字段可以唯一的确定一行数据，其实就是主键。

　　（2）、唯一约束

　　　　　　格式：unique key（字段名）

　　　　　　作用：该字段的值是唯一的，也可以区分一行数据。

　　（3）、外键约束

　　　　　　格式：foreign key（字段名）references 其他表（字段名）

　　　　　　作用：字段的值必须是其他表中的某个对应的字段，不能随便输入。

　　（4）、非空约束

　　　　　　格式：not null，就是字段的属性

　　（5）、默认约束

　　　　　　格式：default value，就是字段的默认值属性

　　（6）、检查约束

　　　　　　格式：check（判断语句）

　　　　　　代码：

　　　　　　　　create table check_constraint(

　　　　　　　　　　age tinyint,

　　　　　　　　　　check(age>=0 and age<100) #检查约束

　　　　　　　　);

 

 

唯一约束和唯一索引区别

创建唯一索引保证了往表中插入重复索引列值的操作都会失败。如果一个单独的sql语句试图往表中插入包含重复索引列值的数据行，sql server将不会插入以上所有行。例如，当一个insert操作试图把从表A中取出的20行插入到表B，而其中的10行跟索引列值重复的话，默认情况下以上20行都不会被插入。然而，如果把索引对应的 “忽略重复键”开关打开的话，包含重复数值的行不会被插入，而非重复数值行会被插入。也就是说，其中的10行会被插入。

约束则没有这一开关，因此定义了一个约束之后，只要有与定义列重复值的行，插入都将被拒绝。

unique约束与unique索引后者包含了前者,且有索引的作用.
如果某列有多行包含 NULL 值，则不能在该列上创建unique索引

因为unique约束也是通过unique索引实现的.
唯一的区别在于建立和删除上.
索引是使用 create/drop index 创建和删除的
而约束是使用 alter table tb add constraint 建立, 使用 drop constraint 删除 

忽略重复值这个选项设置上后, 如果你插入的数据的值与现有值重复, 则插入不会失败, 而是自动跳过重复的这条记录的插入.

 

总结：“主键约束、外键约束、唯一约束”跟“主键索引、外键索引、唯一索引”，其实是同一个概念的不同理解角度

索引是面向数据库本身的，用于查询优化等操作，约束则更多的是业务上的关系，包括唯一性、外键约束等