MySQL 索引 主键 外键

索引介绍

什么是索引

• 索引是对记录集的多个字段进行排序的方法
• 类似于书的目录

总结: 索引是给字段值排序的树形结构,类似于"书的目录"

​ MySQL键值 (重点) 约束如何给字段赋值

索引	说明
index_type	Btree B+tree hash
优点	加快数据的查询速度
缺点	当对表中数据进行增加,删除和修改的时候,索引也要动态维护,降低数据的写入速度.需要占用物理存储空间.

索引键值类型分类

键值	说明	键值标志
INDEX	普通索引	MUL
UNIQUE	唯一索引
FULLTEXT	全文索引
PRIMARY KEY	主键	PRI
FORREIGN KEY	外键

索引的查看

查看表的索引

SHOW INDEX FROM 表名;

mysql> show index from t21\G;

查看表的索引标志

DESC 表名;

mysql> desc t21;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| stu_id | int(2)   | YES  | MUL | NULL    |       |
| name   | char(15) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

#INDEX 索引的key 标志为 MUL

INDEX 普通索引

使用说明

• 一个表中可以有多个INDEX字段
• 字段的值允许有重复,且可以赋NULL值
• 经常把做查询条件的字段设置为INDEX字段
• INDEX字段的KEY标志是MUL

创建

建表时指定索引字段

建表时指定的索引,索引名和字段名相同.

create table 表名(
字段名 类型 约束条件,
index(字段名),index(字段名)
);

mysql> create table t21(
    -> stu_id int(2),
    -> name char(15),
    -> index(stu_id)
    -> );

在已有表中设置索引字段

create index 索引名 on 表名(字段名);

mysql> create index aaa on t21(name);

删除

DROP INDEX 索引名 ON 表名;

mysql> drop index aaa on t21;

PRIMARY KEY 主键

使用说明

• 一个表中只能有一个 primary key
• primary key 对应值唯一且不为 NULL
• 多个字段作为primary key,称为复合主键,必须一起创建.
• 主键字段的KEY标志是PRI
• 通常与 AUTO_INCREMENT 连用
• 通常把表中能唯一标识记录的字段设置为主键字段[记录编号字段]

创建

建表时指定主键字段

CREATE TABLE 表名(

字段名 类型 primary key

);

mysql> create table t17(
    -> stu_id char(9) primary key,
    -> name char(10)
    -> );

CREATE TABLE 表名(

字段名 类型,

字段名2 类型,

primary key(字段名)

);

mysql> create table t18(
    -> stu_id char(9),
    -> name char(10),
    -> primary key(stu_id)
    -> );

在已有表中添加主键

该字段值不能重复也不能为NULL

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

mysql> alter table t17 add primary key(stu_id);

删除

ALTER TABLE 表名 DROP PRIMARY KEY;

mysql> alter table t17 drop primary key;

复合主键

创建

建表时指定复合主键字段

CREATE TABLE 表名(

字段名1 类型,

字段名2 类型,

PRIMARY KEY(字段名,字段名)

);

mysql> create table t18(
    -> cip char(18),
    -> serport smallint(2),
    -> status enum("yes","no"),
    -> primary key(cip,serport)
    -> );

在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名,字段名);

mysql> alter table t18 add primary key(cip,serport);

自增长

AUTO_INCREMENT 字段值自增长

1. 必须为主键
2. 类型为数值型,最好为整型

mysql> create table t20(
    -> stu_id int(2) primary key auto_increment,
    -> name char(10),
    -> age tinyint(2)
    -> );

FOREIGN KEY 外键

什么是外键

• 让当前的字段的值在另一个表中字段值的范围内选择.

使用说明

• 表的存储引擎必须是innodb
• 字段类型要一致,类型长度也要一致
• 被参照字段必须要是索引类型的一种(primary key)

基本语法

FOREIGN KEY(表A的字段名) References 表B(字段名)

ON UPDATE CASCADE

ON DELETE CASCADE

创建

#创建外键时,必须先存在被参照的表,先建立被参照的员工表
mysql> create table ygb(
    -> yg_id int(2) primary key auto_increment,
    -> name char(15),
    -> bumen char(20)
    -> )engine=innodb;
#插入数据
mysql> insert into ygb(name,bumen) values("bob","tea"),("jack","teat"),("leo","boss");
#建表时指定外键字段,并设置实时同步更新
mysql> create table gzb(
    -> gz_id int(2),
    -> name char(15),
    -> pay float(7,2),
    -> bumen char(20),
    -> foreign key(gz_id) references ygb(yg_id)
    -> on update cascade
    -> on delete cascade
    -> )engine=innodb;
#插入数据至新的工资表
mysql> insert into gzb values(1,"bob","100","tea"),(2,"jack","200","tea"),(3,"leo","99999","boss");

同步更新

ON UPDATE CASCADE 更新时同步

#更新员工表中被参照字段值
mysql> update ygb set yg_id="888" where name="leo";

mysql> select * from ygb;
+-------+------+-------+
| yg_id | name | bumen |
+-------+------+-------+
|     1 | bob  | tea   |
|     2 | jack | teat  |
|   888 | leo  | boss  |
+-------+------+-------+
#查看工资表中参照字段值同步更新
mysql> select * from gzb;
+-------+------+----------+-------+
| gz_id | name | pay      | bumen |
+-------+------+----------+-------+
|     1 | bob  |   100.00 | tea   |
|     2 | jack |   200.00 | tea   |
|   888 | leo  | 99999.00 | boss  |
+-------+------+----------+-------+

同步删除

ON DELETE CASCADE 删除时同步

#删除员工表中被参照字段值,
mysql> delete from ygb where yg_id=2;

mysql> select * from ygb;
+-------+------+-------+
| yg_id | name | bumen |
+-------+------+-------+
|     1 | bob  | tea   |
|   888 | leo  | boss  |
+-------+------+-------+
#查看工资表中参照字段值同步删除
mysql> select * from gzb;
+-------+------+----------+-------+
| gz_id | name | pay      | bumen |
+-------+------+----------+-------+
|     1 | bob  |   100.00 | tea   |
|   888 | leo  | 99999.00 | boss  |
+-------+------+----------+-------+

删除

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

#查看外键的键名
mysql> show create table gzb;
| gzb   | CREATE TABLE `gzb` (
  `gz_id` int(2) NOT NULL,
  `name` char(15) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  `bumen` char(20) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gzb_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `ygb` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
#删除外键
mysql> alter table gzb drop foriegn key gzb_ibfk_1;

注意

• 生产环境中,通常需要给外键的字段再设置为主键,避免在参照表中字段重复或为 NULL

• 被参考的表,表的字段不允许被删除