mysql字段约束-索引-外键

mysql的key同时具有constraint（偏重于约束和规范数据库的结构完整性）和index（辅助查询用的）的意义，index只是单纯的索引，用于辅助查询。可以形象的比喻，constraint拥有双重国籍，中国国籍和美国国籍，所以constraint即是中国人又是美国人，而index只拥有中国国籍，它只是单纯的中国人。

创建表时创建key有3种方式（适用于唯一索引、主键索引；下面以创建主键索引为例）：
  （1）在字段级以key方式建立，如 create table t (id int primary key);
  （2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
  （3）在表级以key方式建立，如create table t(id int, primary key (id)); #可以看作是第二种方式的简写
在前面创建一种，在后面创建两种；

前两种不适用于创建普通索引，第三种适用于创建普通索引。

创建表时创建index（适用于唯一索引）

（2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT uk_t_id unique index(id));
（3）在表级以key方式建立，如create table t(id int, unique index(id)); #可以看作是上一种方式的简写

总结：

创建普通索引可以用index或者key；

创建唯一索引可以用index或者key；

创建主键索引不能用index，只能用key；

一张图了解创建表时创建索引的所有方式：

注意：创建唯一索引时，index或key可以省略。

方法二：当表创建完成后，使用alter为表添加索引：

先删除索引：

alter table demo drop key pwd;此处的的pwd指的是索引的名称

再用alter添加：

alter table demo add key(pwd)

3.2.1.2、查看索引：

1、desc demo

mul：普通索引；可以是单列普通索引，或者是复合索引中非唯一索引的第一列，又或者是复合索引中唯一索引的非前导列；列值可以重复，可以为空值，可以为null（应尽量避免，对NULL列加索引需要额外的磁盘空间，会使索引、索引统计和值的比较运算更加复杂）。

uni：唯一索引；可以是单列唯一索引，或者是复合索引中唯一索引的第一列；列值不可以重复，可以为空值或者null（尽量避免）。

pri：主键索引；该列是主键索引或者主键索引的组成部分；列值不可以重复，可以为空值但是不能为null；

2、show index/keys from 表名;

3.2.1.3、删除普通索引：

alter table 表名 drop key/index(索引名);

3.2.1.4、补充

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。便于分析你的查询语句或者是表结构的性能瓶颈。

3.2.2、唯一索引

唯一索引与普通索引基本相同，索引列的值可以为null或空值，但是有一个区别：唯一索引索引列的多有值都只能出现一次，即必须唯一，不能有重复的值，用来约束内容。

3.2.2.1、创建唯一索引

唯一索引的创建同样有两种方法：

方法一：创建表时添加唯一索引

create table 表名(

列定义，

unique key 索引名1（字段），

unique key 索引名2（字段）

);

注意：唯一索引常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

create table demo3(id,int(4) auto_increment primary key,uName varchar(20),uPwd varchar(20),unique index (uName));

方法二：修改表时加唯一索引

alter table 表名 add unique 索引名（字段）；

删除之前创建的唯一索引：alter table demo3 drop key uName;

alter table demo3 add unique (uName);

3.2.2.2、删除唯一索引

语法：alter table 表名 drop key/index 索引名；

3.2.3、主键索引

设置主键就会在该列上自动创建主键索引，每个表最多只能有一个主键。主键列要求列的所有内容必须唯一，主键索引是一种特殊的唯一索引，但不允许有空值。

not null + 唯一索引 = 主键索引

create table t(id int not null unqiue);

desc t;

此时索引列显示的是pri而不是uni

3.2.3.1、创建主键索引

方法一：创建表创建主键索引

create table demo5(id int(4) not_null auto_increment primary key,name varchar(20) default null);

或者

create table demo5(id int(4) not_null auto_increment,name varchar(20),primary key(id));

方法二：创建表后添加

不推荐使用这种方法，如果生产的数据无法保持唯一，创建主键会报错，主键索引一般在设计数据库时，创建表来添加。

先删除主键索引，再创建

alter table demo5 drop primary key;

删除遇到这种报错，是auto_increment的原因。先取消自增长

alter table demo5 change id int(4) not null;

alter table demo5 drop primary key;

3.2.3.2、删除主键索引

alter table 表名 drop primary key;(因为一张表种只能有一个主键索引，所以删除主键索引不用指定索引名)

3.2.4、复合索引

索引可以包含一个、两个或更多个列。两列或更多个列上的索引被称作复合索引

例：创建一个表，存放服务器允许或拒绝的ip和port，要数值种ip和port字段唯一。

create table firewall (host varchar(15) not null,port smallint(4) not null,access enum('deny','allow') not null,primary key(host,port));

复合主键必须有一个字段的值是不能重复的。

为了进一步提升MySQL的效率，就要考虑建立复合索引。

create table mytable(id int not null,username varchar(16) not null,city varchar(50) not null,age int not null);

把username,city,age建到一个索引里。

alter table mytable add index username_city_age(username,city,age);

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

username,city,age username,city username 为什么没有city,age这样的组合索引呢？这是因为MySQL复合索引“最左前缀”的结果。就是只从最左边的列开始组合。并不是只要包含这三列的查询都会用到该索引。

如果对多列进行索引（复合索引），列的顺序非常重要，MySQL只能对索引最左边的前缀进行有效的查找。

下面的几个SQL就会用到这个组合索引：
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到：
SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"

3.3、索引设计原则

1、索引并非越多越好，每个额外的索引都要占用额外的磁盘空间，并降低增删改的性能，因此，索引越多，所花的时间越长。

2、数据量不大不需要建立索引

3、在where子句种出现的字段，常排序（order by）和分组(grou by)的列需要建立索引。

4、唯一性约束对应使用唯一性索引

5、MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like（不以通配符开头的情形）。

4、外键约束

4.1、什么是外键约束

foreign key 就是表与表之间某种特定的关系，由于这种关系的存在，我们能让表与表之间的数据，更加的完整，关联性更强。

关于完整性，关联性我们举个例子：

有两张表，一张是用户表，一张是订单表：

1、如果我删除了用户里的用户，那么订单表里面与这个用户有关的数据，就变成了无头数据，不完整了。

2、如果我在订单表里面，随便插入一条数据，这个数据在用户表里面，没有与之对应的用户，这样数据也不完整了。

如果有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能使数据完整。

如果表A的主键字段是表B中的字段，则该字段称为表B的外键，表A成为主表，表B成为从表。外键是用来实现参照完整性的，外键约束方式可以让两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的引用完整性和一致性。

4.2、创建外键约束方法一：

外键：每次插入或更新时，都会检查数据的完整性。

4.2.1、语法：

create table 表名(

...,

[constraint [外键名]] foreign key [字段名1，字段名2]

references [主表名] (主键字段1，主键字段2)

[on delete cascade|restrict]

[on update cascade|restrict]

);

4.2.2、关于参数的解释：

restrict：拒绝对父表的删除或更新操作。

cascade:从父表删除或更新则自动删除或更新字表中匹配的行。

on delete cascade和on update cascade可以同时使用。

注意：on delete update是级联更新的意思，on delete cascade是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。

例如：

创建主表，并将sid设为主键

create table stu(sid int primary key,name varchar(50) not null);

创建从表，并将sid设为外键

create table score(score double,sid int,constraint fk_stu_score_sid foreign key(sid) reference stu(sid));

注：创建成功，必须满足以下4个条件：

1、确保参照的表和字段都存在

2、组成外键的字段被索引。

3、必须使用engine指定存储引擎为：innodb

4、外键字段和关联字段，数据类型必须一致。

4.2.3、创建一个数据库，包含用户表和订单表

create database market;

use market;

create table `user`(id int(11) not null auto_increment primary key,name varchar(50) not null default '',sex int(1) not null default '0') engine=innodb;

#创建表时，如果表名是sql关键字，使用时，需要引用反引号``

create table `order`(o_id int(11) auto_increment,u_id int(11) default '0',username varchar(50),money int(11),primary key(o_id),index(u_id),foreign key order_f_key(uid) references user(id) on delete cascade on update cascade) engine=innodb;

注：

1、on delete cascade on update cascade 添加级联删除和更新。

2、确保参照表user中id字段存在。组成外键的字段u_id被索引。

3、外键字段和关联字段，数据类型必须一致。