MySQL索引

自增长

auto_increment: 当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值 -> 通常与主键搭配，作为逻辑主键

• 任何一个字段要做自增长，前提是本身是一个索引，（key 一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

mysql> create table auto_increment(
    -> id int unsigned primary key auto_increment,
    -> name varchar(10) not null
    -> );

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

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

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

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

​ 可以看到，当我们没有插入id时，因为有auto_increment 所以每次插入都会在上一次的基础上进行自增插入

mysql> insert into auto_increment values(122,'n');
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from auto_increment;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   2 | n    |
| 122 | n    |
| 123 | m    |
+-----+------+
4 rows in set (0.00 sec)

唯一键

• 一张表中往往有 很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，所以需要有唯一键来保证其他不能重复的元素不出现重复的值

mysql> create table student (
   -> id char(10) unique comment '学号，不能重复，但可以为空',
   -> name varchar(10)
   -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(id, name) values('01', 'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复

ERROR 1062 (23000): Duplicate entry '01' for key 'id'

mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空

Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| 01   | aaa |
| NULL | bbb |
+------+------+

​ 唯一键允许为空，而且可以多个为空，空字段不做唯一比较。

外键

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

语法：

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

mysql> create table class(
    -> id int primary key,
    -> name varchar(30) not null comment 'class name'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table stu(
    -> id int primary key,
    -> name varchar(30) not null,
    -> class_id int,
    #建立外键，将从表中的class_id 绑定到 class中的id
    -> foreign key (class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into class values(1,101),(2,102);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+------+
| id | name |
+----+------+
|  1 | 101  |
|  2 | 102  |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into stu values(100, '张三', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu values(101, '李四', 2);
Query OK, 1 row affected (0.01 sec)

#如果像插入到一个不存在的class_id是不允许的
mysql> insert into stu values(102, '李四', 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

​ 这里像删除主表中的某个元素，就要保证从表中没有元素是绑定在这个主表上的。