11358 字
57 分钟
06: mysql之主键外键索引
2026-06-03
数据库
运维
/
数据库

[toc]

06: mysql之主键外键索引#

今日工作任务路线图

flowchart LR
    subgraph BottomRow[主键外键索引]
        direction LR
        A[主键] --> B[外键] --> C[MySQL索引]
    end
    classDef highlight stroke:#f00,stroke-width:2px;
    class E highlight;

一、工作场景#

小型电商公司

林悦是一家小型电商公司的数据库管理员。随着业务发展，订单量逐渐增多，数据库查询变得缓慢。林悦发现订单表缺乏主键约束，数据存在重复，在查询某一订单时效率极低。同时，订单表和商品表之间没有设置外键关联，导致数据一致性难以保证。她为订单表添加主键，建立订单表与商品表的外键关系，并在经常用于查询的字段上创建索引，大大提高了数据查询和写入的效率，保障了数据的准确性。

大型互联网企业

张成就职于大型互联网企业，负责管理用户相关数据库。该企业用户数量庞大，每天产生海量数据。在用户表中，使用自增主键确保数据的唯一标识。为了关联用户表和用户行为表，设置了外键约束，保证数据的参照完整性。随着业务复杂度增加，查询需求多样化，他依据不同业务需求，在用户表的常用查询字段如“注册时间”“用户等级”上创建索引，显著提升了系统响应速度。

二、作用、为什么要学呢#

学习 MySQL 的主键、外键和索引十分必要。

从数据完整性看 主键能唯一标识表中每行数据，防止数据重复，确保数据准确。 外键可建立表间关联，保证数据参照完整性，避免出现孤立或错误关联的数据。

从查询性能上 索引能加快数据查询速度。在海量数据中，如果没有索引，查询会全表扫描，效率极低。 合理创建索引后，数据库能快速定位到所需数据。

在实际应用里 如电商系统、金融系统等，都离不开这些技术保障数据质量与系统性能。 掌握它们能让你更高效地管理和操作数据库，为项目稳定运行提供有力支持。

三、主键#

当前任务环节

flowchart LR
    subgraph BottomRow[主键外键索引]
        direction LR
        A[主键] --> B[外键] --> C[MySQL索引]
    end
    classDef highlight stroke:#f00,stroke-width:2px;
    class A highlight;

主键使用规则：#

  • 表头值不允许重复，不允许赋NULL【nò】值
  • 一个表中只能有一个primary【普赖莫瑞】 key【kì】 表头
  • 多个表头做主键，称为复合主键，必须一起创建和删除
  • 主键标志PRI
  • 主键通常与auto_increment【奥特欧_因克瑞门特】连用
  • 通常把表中唯一标识记录的表头设置为主键[行号表]

3.1 主键的创建、查看、删除、添加、验证主键#

创建表时创建主键#

//语法格式1
create【克瑞特】  table【忒-部】  库.表（ 表头名 数据类型  primary【普赖莫瑞】 key【kì】  , 表头名  数据类型 , ..... ）;


//建表
mysql>  create【克瑞特】 table【忒-部】 db1.t35(
    -> name char【查尔】(10) ,
    -> hz_id  char【查尔】(10) primary【普赖莫瑞】 key【kì】  ,
    -> class【克拉斯】 char【查尔】(10)
    -> );
-- mysql> create table db1.t35(
--     -> name char(10),
--     -> hz_id char(10) primary key,
--     -> class char(10));
Query OK, 0 rows affected (0.49 sec)


//查看表头
mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


//语法格式2
create【克瑞特】  table【忒-部】  库.表（ 字段名 类型 , 字段名 类型 , primary【普赖莫瑞】 key【kì】（字段名） ）;


//建表
mysql> create【克瑞特】 table【忒-部】 db1.t36(
    -> name char【查尔】(10) ,
    -> hz_id  char【查尔】(10) ,
    -> class【克拉斯】 char【查尔】(10),
    -> primary【普赖莫瑞】 key【kì】(hz_id)
    -> );
-- mysql> create table db1.t36(
--     -> name char(10),
--     -> hz_id char(10),
--     -> class char(10),
--     -> primary key(hz_id));
Query OK, 0 rows affected (0.39 sec)


//查看表头
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除主键#

//删除主键命令格式
mysql> alter【奥-特】  table【忒-部】   库.表   drop【卓-普】 primary【普赖莫瑞】 key【kì】 ;


//例子
mysql>  alter【奥-特】  table【忒-部】 db1.t36  drop【卓-普】 primary【普赖莫瑞】 key【kì】 ;
-- mysql> alter table db1.t36 drop primary key;
Query OK, 0 rows affected (1.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


//查看表头
mysql> desc  db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   |     | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>

添加主键#

//添加主键命令格式
mysql> alter【奥-特】  table【忒-部】  库.表  add  primary【普赖莫瑞】 key【kì】(表头名);


//例子
mysql> alter【奥-特】  table【忒-部】  db1.t36  add  primary【普赖莫瑞】 key【kì】(hz_id);
-- mysql> alter table db1.t36 add primary key(hz_id);
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

验证主键约束#

//使用t35表 验证主键约束
//查看主键表头
mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


-- 插入第1条记录 正常
mysql> insert【因涩特】 into【因兔】 db1.t35  values【挖柳斯】 ("bob","888","nsd2107");
-- mysql> insert into db1.t35 values("bob","888","nsd2107");
Query OK, 1 row affected (0.05 sec)


-- 主键表头值不允许为null【nò】，否则插入失败
mysql> insert【因涩特】 into【因兔】 db1.t35  values【挖柳斯】 ("john",null【nò】,"nsd2107");
-- mysql> insert into db1.t35 values("john",null,"nsd2107");
ERROR 1048 (23000): Column 'hz_id' cannot be null【nò】
mysql>


-- 主键表头值不允许重复，否则插入失败
mysql> insert【因涩特】 into【因兔】 db1.t35  values【挖柳斯】 ("john","888","nsd2107");
-- mysql> insert into db1.t35 values("john","888","nsd2107");
ERROR 1062 (23000): Duplicate entry '888' for key【kì】 'primary【普赖莫瑞】'


-- 主键表头值不重复也不是null【nò】可以
mysql> insert【因涩特】 into【因兔】 db1.t35  values【挖柳斯】 ("john","988","nsd2107");
-- mysql> insert into db1.t35 values("john","988","nsd2107");
Query OK, 1 row affected (0.07 sec)


-- 查看表记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t35 ;
-- mysql> select * from db1.t35;
+------+-------+---------+
| name | hz_id | class   |
+------+-------+---------+
| bob  | 888   | nsd2107 |
| john | 988   | nsd2107 |
+------+-------+---------+
2 rows in set (0.00 sec)

3.2 复合主键的使用#

创建表时创建复合主键#

-- 创建复合主键 表头依次是客户端ip 、服务端口号、访问状态
mysql> create【克瑞特】  table【忒-部】  db1.t39(
cip   varchar【瓦儿-查儿】(15) ,
port【破特】  smallint【斯莫尔因特】,
status【s 塔丢】  enum【ing纽某】("deny【迪奈】","allow【饿劳】") ,
primary【普赖莫瑞】 key【kì】(cip,port【破特】)
);
-- mysql> create table db1.t39(
--     -> cip varchar(15),
--     -> port smallint,
--     -> status enum("deny","allow"),
--     -> primary key(cip,port));


-- 插入记录验证
insert【因涩特】 into【因兔】  db1.t39  values【挖柳斯】 ("1.1.1.1",22,"deny【迪奈】");
-- mysql> insert into db1.t39 values("1.1.1.1",22,"deny");
-- Query OK, 1 row affected (0.09 sec)


insert【因涩特】 into【因兔】  db1.t39  values【挖柳斯】 ("1.1.1.1",22,"deny【迪奈】"); --两个复合主键相同报错
-- mysql> insert into db1.t39 values("1.1.1.1",22,"deny");
-- ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 't39.PRIMARY'


insert【因涩特】 into【因兔】  db1.t39  values【挖柳斯】 ("1.1.1.1",80,"deny【迪奈】"); -- 单个复合主键相同可以插入
-- mysql> insert into db1.t39 values("1.1.1.1",80,"deny");
-- Query OK, 1 row affected (0.10 sec)


insert【因涩特】 into【因兔】  db1.t39  values【挖柳斯】 ("2.1.1.1",80,"allow【饿劳】"); -- 单个复合主键相同可以插入
-- mysql> insert into db1.t39 values("2.1.1.1",80,"allow");
-- Query OK, 1 row affected (0.10 sec)


-- 查看记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t39;
-- mysql> select * from db1.t39;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   80 | deny   |
| 2.1.1.1 |   80 | allow  |
+---------+------+--------+
3 rows in set (0.00 sec)

删除复合主键#

//删除复合主键
mysql> alter【奥-特】 table【忒-部】  db1.t39 drop【卓-普】 primary【普赖莫瑞】 key【kì】;
-- mysql> alter table db1.t39 drop primary key;
Query OK, 3 rows affected (1.10 sec)
Records: 3  Duplicates: 0  Warnings: 0


//查看表头
mysql>  desc  db1.t39;
-- mysql> desc db1.t39;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | varchar(15)          | NO   |     | NULL    |       |
| port   | smallint             | NO   |     | NULL    |       |
| status | enum('deny','allow') | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


//没有复合主键约束后 ，插入记录不受限制了
mysql>  insert【因涩特】 into【因兔】 db1.t39 values【挖柳斯】("2.1.1.1",80,"allow【饿劳】");
-- mysql> insert into db1.t39 values("2.1.1.1",80,"allow");
Query OK, 1 row affected (0.06 sec)
mysql> insert【因涩特】 into【因兔】 db1.t39 values【挖柳斯】("2.1.1.1",80,"deny【迪奈】");
--mysql> insert into db1.t39 values("2.1.1.1",80,"deny");
Query OK, 1 row affected (0.08 sec)


//查看表记录
mysql> select【涩莱克特】  * from【弗乱】  db1.t39;
-- mysql> select * from db1.t39;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   80 | deny   |
| 2.1.1.1 |   80 | allow  |
| 2.1.1.1 |   80 | allow  |
| 2.1.1.1 |   80 | deny   |
+---------+------+--------+
5 rows in set (0.00 sec)

添加复合主键#

//添加复合主键时 字段下的数据与主键约束冲突 不允许添加
mysql> alter【奥-特】 table【忒-部】  db1.t39 add primary【普赖莫瑞】 key【kì】(cip,port【破特】);
-- mysql> alter table db1.t39 add primary key(cip,port);
ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key【kì】 't39.primary【普赖莫瑞】'


//删除重复的数据
mysql> delete【迪  利 特】 from【弗乱】 db1.t39 where【威尔】 cip="2.1.1.1";
-- mysql> delete from db1.t39 where cip="2.1.1.1";
Query OK, 3 rows affected (0.05 sec)


mysql> select【涩莱克特】  * from【弗乱】  db1.t39;
-- mysql> select * from db1.t39;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   80 | deny   |
+---------+------+--------+
2 rows in set (0.00 sec)


//添加复合主键
mysql> alter【奥-特】 table【忒-部】  db1.t39 add primary【普赖莫瑞】 key【kì】(cip,port【破特】);
-- mysql> alter table db1.t39 add primary key(cip,port);
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0


//查看表头
mysql>  desc  db1.t39;
-- mysql> desc db1.t39;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | varchar(15)          | NO   | PRI | NULL    |       |
| port   | smallint             | NO   | PRI | NULL    |       |
| status | enum('deny','allow') | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.3 auto_increment【奥特欧_因克瑞门特】的使用#

表头设置了auto_increment【奥特欧_因克瑞门特】属性后，

插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值

要想让表头有自增长 表头必须有主键设置才可以

查看表结构时 在 Extra【埃斯处了】 （额外设置） 位置显示

建表时 创建有auto_increment【奥特欧_因克瑞门特】 属性的表头。实现的效果如下：

行号姓名班级住址
1bobnsd2107bj
2bobnsd2107bj
3bobnsd2107bj
4bobnsd2107bj

1）建表

mysql> create【克瑞特】     table【忒-部】   db1.t38 (
    -> 行号   int【因特】   primary【普赖莫瑞】 key【kì】  auto_increment【奥特欧_因克瑞门特】,
    -> 姓名   char【查尔】(10) ,
    -> 班级   char【查尔】(7) ,
    -> 住址   char【查尔】(10)
    -> );
-- mysql> create table db1.t38(
--     -> 行号 int primary key auto_increment,
--     -> 姓名 char(10),
--     -> 班级 char(7),
--     -> 住址 char(10));
Query OK, 0 rows affected (0.76 sec)


//查看表头
mysql> desc db1.t38 ;
-- mysql> desc db1.t38;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| 行号   | int      | NO   | PRI | NULL    | auto_increment |
| 姓名   | char(10) | YES  |     | NULL    |                |
| 班级   | char(7)  | YES  |     | NULL    |                |
| 住址   | char(10) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


//插入表记录 不给自增长表头赋值
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址)  values【挖柳斯】("bob","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("bob","nsd2107","bj");
Query OK, 1 row affected (0.05 sec)


mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("bob","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("bob","nsd2107","bj");
Query OK, 1 row affected (0.04 sec)


mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("tom","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("tom","nsd2107","bj");
Query OK, 1 row affected (0.05 sec)


//查看表记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t38;
-- mysql> select * from db1.t38;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2107 | bj     |
|      2 | bob    | nsd2107 | bj     |
|      3 | tom    | nsd2107 | bj     |
+--------+--------+---------+--------+
3 rows in set (0.00 sec)

自增长使用注意事项#

//给自增长字段的赋值
mysql> insert【因涩特】  into【因兔】  db1.t38(行号,姓名,班级,住址) values【挖柳斯】(5,"lucy","nsd2107","bj");
-- mysql> insert into db1.t38(行号,姓名,班级,住址) values(5,"lucy","nsd2107","bj");
Query OK, 1 row affected (0.26 sec)


//不赋值后 用最后1条件记录表头的值+1结果赋值
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");
Query OK, 1 row affected (0.03 sec)


//查看记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t38 ;
-- mysql> select * from db1.t38;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2107 | bj     |
|      2 | bob    | nsd2107 | bj     |
|      3 | tom    | nsd2107 | bj     |
|      5 | lucy   | nsd2107 | bj     |
|      6 | lucy   | nsd2107 | bj     |
+--------+--------+---------+--------+
5 rows in set (0.00 sec)


//删除所有行
mysql> delete【迪  利 特】  from【弗乱】  db1.t38 ；
-- mysql> delete from db1.t38;


//再添加行 继续行号 而不是从 1 开始
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");


mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");


mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");


//查看记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t38;
-- mysql> select * from db1.t38;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      8 | lucy   | nsd2107 | bj     |
|      9 | lucy   | nsd2107 | bj     |
|     10 | lucy   | nsd2107 | bj     |
+--------+--------+---------+--------+
3 rows in set (0.01 sec)




-- truncate【创kí特】删除行，会同时重置auto_increment值为1开始
//truncate【创kí特】删除行 再添加行 从1开始
mysql> truncate【创kí特】 table【忒-部】  db1.t38;
-- mysql> truncate table db1.t38;
Query OK, 0 rows affected (2.66 sec)


//插入记录
mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");
Query OK, 1 row affected (0.04 sec)


mysql> insert【因涩特】  into【因兔】  db1.t38(姓名,班级,住址) values【挖柳斯】("lucy","nsd2107","bj");
-- mysql> insert into db1.t38(姓名,班级,住址) values("lucy","nsd2107","bj");
Query OK, 1 row affected (0.30 sec)


//查看记录
mysql> select【涩莱克特】  * from【弗乱】 db1.t38;
-- mysql> select * from db1.t38;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | lucy   | nsd2107 | bj     |
|      2 | lucy   | nsd2107 | bj     |
+--------+--------+---------+--------+
2 rows in set (0.01 sec)
mysql>

四、外键#

当前任务环节

flowchart LR
    subgraph BottomRow[主键外键索引]
        direction LR
        A[主键] --> B[外键] --> C[MySQL索引]
    end
    classDef highlight stroke:#f00,stroke-width:2px;
    class B highlight;

外键使用规则：#

  • 表存储引擎必须是innodb【伊诺 DB】
  • 表头数据类型要一致
  • 被参照表头必须要是索引类型的一种(primary【普赖莫瑞】 key【kì】)

作用：

  • 插入记录时，表头值在另一个表的表头值范围内选择。

4.1 外键的创建、查看、删除、添加#

创建外键命令#

create【克瑞特】 table【忒-部】   库.表(
表头列表 ,
foreign【佛润】 key【kì】(表头名)        #指定外键
references【略粉谁斯】 库.表(表头名)   #指定参考的表头名
on【昂】 update【阿普 dei 特】  cascade【卡斯kèi】         #同步更新
on【昂】  delete【迪  利 特】  cascade【卡斯kèi】        #同步删除
)engine【恩-京】=innodb【伊诺 DB】;

需求： 仅给公司里已经入职的员工发工资

首先创建存储员工信息的员工表

表名 yg

员工编号 yg_id

姓名 name

#创建员工表
create【克瑞特】 table【忒-部】  db1.yg (
yg_id   int【因特】  primary【普赖莫瑞】 key【kì】  auto_increment【奥特欧_因克瑞门特】,
name  char【查尔】(16)
) engine【恩-京】=innodb【伊诺 DB】;
-- mysql> create table db1.yg (
--     -> yg_id int primary key auto_increment,
--     -> name char(16)
--     -> )engine=innodb;

创建工资表

表名 gz

员工编号 gz_id

工资 pay

#创建工资表 指定外键表头
-- 语句一
mysql> create【克瑞特】 table【忒-部】 db1.gz(
  gz_id int【因特】 primary【普赖莫瑞】 key【kì】 auto_increment【奥特欧_因克瑞门特】,  -- 指定主键
  pay  float【弗洛特】,
foreign【佛润】 key【kì】(gz_id)  references【略粉谁斯】 db1.yg(yg_id)  -- 指定外键值为主键，即yg表的yg_id值为gz表的gz_id
on【昂】 update【阿普 dei 特】 cascade【卡斯kèi】                       -- 同步更新
on【昂】 delete【迪  利 特】 cascade【卡斯kèi】                         -- 同步删除
)engine【恩-京】=innodb【伊诺 DB】 ;                                   -- 指定存储引擎
-- mysql> create table db1.gz(
--     -> gz_id int primary key auto_increment,
--     -> pay float,
--     -> foreign key(gz_id) references db1.yg(yg_id)
--     -> on update cascade
--     -> on delete cascade
--     -> )engine=innodb;




-- 语句二（推荐）
mysql> create【克瑞特】 table【忒-部】 db1.gz (
    -> gz_id int【因特】 primary【普赖莫瑞】 key【kì】 auto_increment【奥特欧_因克瑞门特】,  -- 指定主键
    -> pay float【弗洛特】,
    -> yg_id int【因特】,
    -> foreign【佛润】 key【kì】(yg_id) references【略粉谁斯】 db1.yg(yg_id)  -- 指定外键
    -> on【昂】 update【阿普 dei 特】 cascade【卡斯kèi】  -- 同步更新
    -> on【昂】 delete【迪  利 特】 cascade【卡斯kèi】-- 同步删除
    -> )engine【恩-京】=innodb【伊诺 DB】;  -- 指定存储引擎
-- mysql> create table db1.gz(
--     -> gz_id int primary key auto_increment,
--     -> pay float,
--     -> yg_id int,
--     -> foreign key(yg_id) references db1.yg(yg_id)
--     -> on update cascade
--     -> on delete cascade
--     -> )engine=innodb;

查看外键#

//查看工资表外键
mysql> show【瘦】 create【克瑞特】  table【忒-部】 db1.gz \G
-- mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int NOT NULL AUTO_INCREMENT,
  `pay` float DEFAULT NULL,
  `yg_id` int DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  KEY `yg_id` (`yg_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`yg_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.022 sec)

删除外键#

//删除外键
mysql> alter【奥-特】 table【忒-部】 db1.gz drop【卓-普】 FOREIGN【佛润】 KEY【kì】  gz_ibfk_1;
-- mysql> alter table db1.gz drop foreign key gz_ibfk_1;


-- 手动命名：在创建外键时，使用 CONSTRAINT关键字后面跟一个自定义的名称，例如 CONSTRAINT fk_student_class。这样做的好处是名称有意义，便于记忆和管理
--             CREATE TABLE db1.gz(
--                 gz_id INT PRIMARY KEY AUTO_INCREMENT,
--                 pay FLOAT,
--                 yg_id INT,
--                 CONSTRAINT fk_gz_yg_id                     -- 这里添加了显式的外键约束名
--                 FOREIGN KEY(yg_id) REFERENCES db1.yg(yg_id)
--                 ON UPDATE CASCADE
--                 ON DELETE CASCADE
--             ) ENGINE=InnoDB;


-- 自动生成：创建外键时没有指定名称（即省略了 CONSTRAINT [symbol]部分），MySQL 会自动生成一个约束名。自动生成的名称通常遵循 表名_ibfk_序号的规则
-- 在MySQL中，当创建一个外键时，无论是手动命名还是系统自动生成，数据库都会为这个约束关系分配一个唯一的名称。这个名称就是外键约束的标识符。
-- 在MySQL中，外键约束的标识符是以表名和外键列名作为前缀，后跟一个下划线和一个数字作为后缀。


-- 在上面的示例中，外键约束的标识符是 gz_ibfk_1。
--  gz：这是您当前表（从表）的名字。
--  ibfk：这是 InnoDB 存储引擎为外键（InnoDB Foreign Key）生成的标识符，表示这是一个外键约束。
--  1：这是一个数字，表示这是该表中第一个外键约束,如果表中有多个外键，序号会依次递增（如 gz_ibfk_2, gz_ibfk_3）。


-- 可以使用 SHOW CREATE TABLE 语句来查看外键约束的标识符。




//查看不到外键
mysql> show【瘦】 create【克瑞特】  table【忒-部】 db1.gz \G
-- mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int NOT NULL AUTO_INCREMENT,
  `pay` float DEFAULT NULL,
  `yg_id` int DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  KEY `yg_id` (`yg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

添加外键#

//添加外键
mysql> alter【奥-特】 table【忒-部】 db1.gz
add  foreign【佛润】 key【kì】(yg_id)  references【略粉谁斯】 db1.yg(yg_id)
on【昂】 update【阿普 dei 特】 cascade【卡斯kèi】  on【昂】 delete【迪  利 特】 cascade【卡斯kèi】 ;
-- mysql> alter table db1.gz
--     -> add foreign key(yg_id) references db1.yg(yg_id)
--     -> on update cascade on delete cascade;




//查看外键
mysql> show【瘦】 create【克瑞特】  table【忒-部】 db1.gz \G
-- mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int NOT NULL AUTO_INCREMENT,
  `pay` float DEFAULT NULL,
  `yg_id` int DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  KEY `yg_id` (`yg_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`yg_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.2 验证外键功能#

1)、外键字段的值必须在参考表字段值范围内#

-- 员工表插入记录
mysql> insert【因涩特】 into【因兔】 db1.yg (name) values【挖柳斯】 ("jerry"),("tom");
-- mysql> insert into db1.yg(name) values("jerry"),("tom");
-- Query OK, 2 rows affected (0.15 sec)
-- Records: 2  Duplicates: 0  Warnings: 0


mysql> select【涩莱克特】  * from【弗乱】 db1.yg;
-- mysql> select *from db1.yg;
-- +-------+-------+
-- | yg_id | name  |
-- +-------+-------+
-- |     1 | jerry |
-- |     2 | tom   |
-- +-------+-------+
-- 2 rows in set (0.00 sec)


-- 工资表插入记录
mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】(1,50000);
-- mysql> insert into db1.gz values(1,50000,1);
-- Query OK, 1 row affected (0.15 sec)


mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】(2,60000);
-- mysql> insert into db1.gz values(2,50000,2);
-- Query OK, 1 row affected (0.16 sec)


mysql> select【涩莱克特】  * from【弗乱】 db1.gz;
-- mysql> select * from db1.gz;
+-------+-------+-------+
| gz_id | pay   | yg_id |
+-------+-------+-------+
|     1 | 50000 |     1 |
|     2 | 50000 |     2 |
+-------+-------+-------+
2 rows in set (0.00 sec)


-- 没有的3号员工 工资表插入记录报错(因为yg表中没有3号员工数据，需要先插入3号员工数据)
mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】(3,50000);
-- mysql> insert into db1.gz values(3,50000,3);
ERROR 1452 (23000): Cannot add or update【阿普 dei 特】 a child row: a foreign key【kì】 constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY【kì】 (`gz_id`) REFERENCES【略粉谁斯】 `yg` (`yg_id`) ON【昂】 DELETE【迪  利 特】 CASCADE【卡斯kèi】 ON【昂】 UPDATE【阿普 dei 特】 CASCADE【卡斯kèi】)




-- 员工表 插入编号3的员工
mysql> insert【因涩特】 into【因兔】 db1.yg (name) values【挖柳斯】 ("Lucy");
-- mysql> insert into db1.yg(name) values("Lucy");
-- Query OK, 1 row affected (0.14 sec)


mysql> select【涩莱克特】  * from【弗乱】  db1.yg;
-- mysql> select * from yg;
-- +-------+-------+
-- | yg_id | name  |
-- +-------+-------+
-- |     1 | jerry |
-- |     2 | tom   |
-- |     3 | Lucy  |
-- +-------+-------+
-- 3 rows in set (0.00 sec)




-- 可以给3号员工 发工资了
mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】(3,40000,3);
-- mysql> insert into db1.gz values(3,40000,3);
-- Query OK, 1 row affected (0.12 sec)

2)、验证同步更新#

-- 查看员工表记录
mysql> select【涩莱克特】 * from【弗乱】 db1.yg;
-- mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     3 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)


-- 把yg表里编号是3的改成9
mysql> update【阿普 dei 特】 db1.yg set yg_id=9 where【威尔】 yg_id=3;
-- mysql> update db1.yg set yg_id=9 where yg_id=3;
-- Query OK, 1 row affected (0.17 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0


mysql> select【涩莱克特】 * from【弗乱】 db1.yg;
-- mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     9 | Lucy  |
+-------+-------+
3 rows in set (0.00 sec)


-- 工资表里编号是3的自动变成 9
mysql> select【涩莱克特】 * from【弗乱】 db1.gz;
-- mysql> select * from db1.gz;
+-------+-------+-------+
| gz_id | pay   | yg_id |
+-------+-------+-------+
|     1 | 50000 |     1 |
|     2 | 50000 |     2 |
|     3 | 40000 |     9 |
+-------+-------+-------+
3 rows in set (0.00 sec)

3)、验证同步删除#

-- 删除前查看员工表记录
mysql> select【涩莱克特】 * from【弗乱】 db1.yg;
-- mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
|     9 | Lucy  |
+-------+-------+
3 rows in set (0.00 sec)


-- 删除编号2的员工
mysql> delete【迪  利 特】 from【弗乱】 db1.yg where【威尔】 yg_id=2;
-- mysql> delete from db1.yg where yg_id=2;
-- Query OK, 1 row affected (0.19 sec)




-- 删除后查看
mysql> select【涩莱克特】 * from【弗乱】 db1.yg;
-- mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     9 | Lucy  |
+-------+-------+
2 rows in set (0.00 sec)




-- 查看工资表也没有编号2的工资了
mysql> select【涩莱克特】 * from【弗乱】 db1.gz;
-- mysql> select * from db1.gz;
+-------+-------+-------+
| gz_id | pay   | yg_id |
+-------+-------+-------+
|     1 | 50000 |     1 |
|     3 | 40000 |     9 |
+-------+-------+-------+
2 rows in set (0.00 sec)

4)、外键使用注意事项#

-- 被参考的表不能删除
mysql> drop【卓-普】 table【忒-部】 db1.yg;
-- mysql> drop table db1.yg;  --因为db1.yg的yg_id表被db1.gz表的yg_id参考引用为外键，所以不能删除，只能先解除外键或者先删除gz表
ERROR 1217 (23000): Cannot delete【迪  利 特】 or update【阿普 dei 特】 a parent row: a foreign key【kì】 constraint fails
mysql>






-- 给gz表的gz_id表头 加主键标签
-- 保证每个员工只能发1遍工资  且有员工编号的员工才能发工资
-- 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键
mysql> delete【迪  利 特】 form db1.gz;
-- mysql> delete from db1.gz;
-- Query OK, 2 rows affected (0.15 sec)


-- 删除主键
-- 先查看表头
mysql> desc db1.gz;
+-------+-------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra          |
+-------+-------+------+-----+---------+----------------+
| gz_id | int   | NO   | PRI | NULL    | auto_increment |
| pay   | float | YES  |     | NULL    |                |
| yg_id | int   | YES  | MUL | NULL    |                |
+-------+-------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


-- 删除auto_increment（一个列是auto_increment自增长，该列就必须定义为主键，所有要删除该主键必须先删除auto_increment属性）
mysql> alter table db1.gz modify gz_id int;
Query OK, 0 rows affected (2.83 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 再次查看表头属性（现在自增长属性已删除，可以开始删除主键了）
mysql> desc db1.gz;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| gz_id | int   | NO   | PRI | NULL    |       |
| pay   | float | YES  |     | NULL    |       |
| yg_id | int   | YES  | MUL | NULL    |       |
+-------+-------+------+-----+---------+-------+
3 rows in set (0.00 sec)


-- 删除主键
mysql> alter table db1.gz drop primary key;
Query OK, 0 rows affected (2.82 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 查看表头属性(已删除，没有PRI标识)
mysql> desc db1.gz;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| gz_id | int   | NO   |     | NULL    |       |
| pay   | float | YES  |     | NULL    |       |
| yg_id | int   | YES  | MUL | NULL    |       |
+-------+-------+------+-----+---------+-------+
3 rows in set (0.00 sec)


--添加主键
mysql> alter【奥-特】 table【忒-部】 db1.gz add primary【普赖莫瑞】 key【kì】(gz_id);
-- mysql> alter table db1.gz add primary key(gz_id);
Query OK, 0 rows affected (2.21 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 查看表头属性
mysql> desc db1.gz;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| gz_id | int   | NO   | PRI | NULL    |       |
| pay   | float | YES  |     | NULL    |       |
| yg_id | int   | YES  | MUL | NULL    |       |
+-------+-------+------+-----+---------+-------+
3 rows in set (0.00 sec)


-- 添加auto_increment自增长属性
mysql> alter table db1.gz modify gz_id int auto_increment;
Query OK, 0 rows affected (2.75 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 查看表头属性
mysql> desc db1.gz;
+-------+-------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra          |
+-------+-------+------+-----+---------+----------------+
| gz_id | int   | NO   | PRI | NULL    | auto_increment |
| pay   | float | YES  |     | NULL    |                |
| yg_id | int   | YES  | MUL | NULL    |                |
+-------+-------+------+-----+---------+----------------+
3 rows in set (0.00 sec)








-- 保证每个员工只能发1遍工资  且有员工编号的员工才能发工资
mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】 (1,53000);  报错
-- mysql> insert into db1.gz values(1,53000);
-- ERROR 1136 (21S01): Column count doesn't match value count at row 1




mysql> insert into db1.gz values(1,53000,9);
Query OK, 1 row affected (0.19 sec)


mysql> select * from gz;
+-------+-------+-------+
| gz_id | pay   | yg_id |
+-------+-------+-------+
|     1 | 53000 |     9 |
+-------+-------+-------+
Query OK, 1 row affected (0.10 sec)


mysql> insert【因涩特】 into【因兔】 db1.gz values【挖柳斯】 (NULL【nò】,53000,2); 报错
-- mysql> insert into db1.gz values(NULL,53000,2);        -- 主键值不能为空
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`yg_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

五、MySQL索引#

当前任务环节

flowchart LR
    subgraph BottomRow[主键外键索引]
        direction LR
        A[主键] --> B[外键] --> C[MySQL索引]
    end
    classDef highlight stroke:#f00,stroke-width:2px;
    class C highlight;

使用规则：#

  • 一个表中可以有多个index【因戴克斯】
  • 任何数据类型的表头都可以设置索引
  • 表头值可以重复，也可以赋NULL【nò】值
  • 通常在where【威尔】条件中的表头上设置Index【因戴克斯】
  • index【因戴克斯】索引标志MUL

主键和唯一索引和普通索引的区别#

主键和唯一索引是数据库设计中两个核心但易混淆的概念。下面这个表格能帮你快速把握它们的核心区别。

特性维度主键 (PRIMARY KEY)外键 (FOREIGN KEY)唯一索引 (UNIQUE INDEX)普通索引 (INDEX)
本质约束，用于唯一标识每条记录引用约束，用于建立和加强表间的数据关联索引，附带唯一性保证最基本的索引类型，没有唯一性约束
数量限制一个表只能有一个一个表可以有多个一个表可以有多个一个表可以有多个
空值处理不允许为 NULL允许为 NULL，表示该记录无关联记录允许为 NULL，且可包含多个 NULL允许出现重复值和空值
核心功能唯一标识表中的每一行记录，保证实体完整性维护引用完整性，确保值必须在另一表的主键中存在确保索引列的数据唯一，防止重复加速查询，提高查询效率，不做任何约束
外键引用可以被其他表的外键引用定义表间关系（如一对多），外键是另一表主键的引用不可以直接作为外键引用目标不可以直接作为外键引用目标
核心语句ALTER TABLE 表名 ADD PRIMARY KEY (字段);ALTER TABLE 从表 ADD FOREIGN KEY (外键字段) REFERENCES 主表(主键字段);ALTER TABLE 表名 ADD UNIQUE [索引名] (字段);ALTER TABLE 表名 ADD INDEX [索引名] (字段); 或 CREATE INDEX 索引名 ON 表名 (字段);

核心内涵与进阶理解#

  • 主键：数据存储的基石 “主键还充当聚簇索引”非常关键。在InnoDB存储引擎中，表数据本身就是按主键顺序组织存储的。这使得主键查询极快，但同时也意味着如果主键值是随机的（如UUID），可能导致频繁的页分裂，影响插入性能并产生碎片。因此，使用与业务无关的自增主键往往是性能最佳实践。
  • 外键：表间关系的纽带与数据一致性的守护者 外键的本质是一个约束，它通过强制要求子表（从表）中的外键字段值必须存在于主表（被引用表）的主键中，来维护参照完整性。这有效防止了“孤立数据”的产生，例如，确保了不会有一条订单记录指向一个不存在的客户ID。外键关系通常用于实现“一对多”的关联，例如，一个客户（主表）可以拥有多个订单（子表）。 在实现上，定义外键时通常可以指定级联操作（如 ON DELETE CASCADE 或 ON UPDATE SET NULL），这允许在主表数据变更时自动处理子表中的关联数据，但使用时需谨慎，以免导致非预期的数据修改。
  • 唯一索引：业务规则的守护者 它的强大之处在于将业务唯一性要求（如“手机号不可重复”）固化在数据库层，从根源上防止了脏数据的产生。与主键不同，一个表可以有多个唯一索引，为多个业务字段提供唯一性保障。它也可以是复合索引（多列组合），确保几个字段的组合值唯一，例如防止同一用户对同一商品重复评论 (user_id, product_id)。
  • 普通索引：纯粹的查询加速器 它唯一的目标就是“快”。其核心价值在于大幅减少磁盘I/O，通过索引数据结构（如B+Tree）快速定位数据，避免全表扫描。创建普通索引时，应考虑字段的“区分度”（选择性），即不重复值的比例。区分度越高，索引筛选效率越好。像“性别”这种区分度很低的字段创建索引意义不大。
创建唯一索引#
-- 语法一
mysql> alter table db1.customers add unique index email_index(email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 语法二
mysql> alter table db1.customers add unique (email);
Query OK, 0 rows affected (4.387 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 建表时创建
 CREATE TABLE 库.表 (
  id INT,
  表头名 VARCHAR(255),
  ...,
  UNIQUE INDEX 索引名 (表头名)
  );
-- 或
 CREATE TABLE 库.表 (
  id INT,
  表头名 VARCHAR(255)
  UNIQUE, ...);


-- 为已存在表添加
  ALTER TABLE 库.表 ADD UNIQUE INDEX 索引名 (表头名);
-- 或
  ALTER TABLE 库.表 ADD UNIQUE (表头名);
--或
  CREATE UNIQUE INDEX 索引名 ON 库.表 (表头名);


-- 删除唯一索引
  DROP INDEX 索引名 ON 库.表;
-- 或
  ALTER TABLE 库.表 DROP INDEX 索引名;


-- 修改唯一索引
-- MySQL 不直接支持修改索引。通常需要先删除旧索引，再添加新索引。
-- 例如，先
  DROP INDEX 旧索引名 ON 库.表;
--   再
  CREATE UNIQUE INDEX 新索引名 ON 库.表 (表头名);

5.1 索引的创建、查看、删除、添加#

1)建表时创建索引命令格式#

CREATE【克瑞特】 TABLE【忒-部】  库.表(
字段列表 ,
INDEX【因戴克斯】(字段名) ,
INDEX【因戴克斯】(字段名)
);
Create【克瑞特】 database  home;
Use home;
CREATE【克瑞特】 TABLE【忒-部】 tea4(
id char【查尔】(6),
name varchar【瓦儿-查儿】(6),
age int(3),
gender enum【ing纽某】('boy','girl') DEFAULT【迪-佛特】 'boy',
INDEX【因戴克斯】(id),INDEX【因戴克斯】(name)
);
-- mysql> create database home;
-- Query OK, 1 row affected (0.17 sec)


-- mysql> use home;
-- Database changed
-- mysql> create table tea4(
--     -> id char(6),
--     -> name varchar(6),
--     -> age int(3),
--     -> gender enum('boy','girl') default 'boy',
--     -> index(id),index(name));-- 创建名为 `id`和`name` 的普通索引
-- Query OK, 0 rows affected, 1 warning (1.70 sec)


-- mysql> desc tea4;
-- +--------+--------------------+------+-----+---------+-------+
-- | Field  | Type               | Null | Key | Default | Extra |
-- +--------+--------------------+------+-----+---------+-------+
-- | id     | char(6)            | YES  | MUL | NULL    |       |
-- | name   | varchar(6)         | YES  | MUL | NULL    |       |
-- | age    | int                | YES  |     | NULL    |       |
-- | gender | enum('boy','girl') | YES  |     | boy     |       |
-- +--------+--------------------+------+-----+---------+-------+
-- 4 rows in set (0.00 sec)

2）查看索引#

des  库.表；
mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   | MUL | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)              | YES |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> system ls /var/lib/mysql/home/tea4.ibd  保存排队信息的文件
/var/lib/mysql/home/tea4.ibd
mysql>

3）查看索引详细信息#

show【瘦】 index【因戴克斯】   from【弗乱】   库.表；
-- mysql> show index from home.tea4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tea4  |          1 | id       |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tea4  |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
show【瘦】  index【因戴克斯】   from【弗乱】  home.tea4 \G
-- mysql> show index from home.tea4 \G
*************************** 1. row ***************************
        Table: tea4         -- 表名
   Non_unique: 1
     Key_name: id           -- 索引名   （默认索引名和表头名相同，删除索引时，使用的索引名）
 Seq_in_index: 1
  Column_name: id           -- 表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE        -- 索引类型
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: tea4         -- 表名
   Non_unique: 1
     Key_name: name         -- 索引名
 Seq_in_index: 1
  Column_name: name         -- 表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE        -- 排队算法
      Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql>

4）删除索引#

命令格式   DROP【卓-普】  INDEX【因戴克斯】   索引名   ON【昂】  库.表；
mysql> drop【卓-普】 index【因戴克斯】 id on【昂】 home.tea4 ;
-- mysql> drop index id on home.tea4;
-- Query OK, 0 rows affected (0.48 sec)
-- Records: 0  Duplicates: 0  Warnings: 0


mysql> desc  home.tea4;
-- mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   |     | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)             | YES |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.14 sec)


mysql> show【瘦】  index【因戴克斯】   from【弗乱】  home.tea4 \G
-- mysql> show index from home.tea4 \G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)
mysql>

5）已有表添加索引命令#

CREATE【克瑞特】  INDEX【因戴克斯】  索引名  ON【昂】  库.表(字段名)；
mysql> create【克瑞特】  index【因戴克斯】 nianling on【昂】 home.tea4(age);
-- mysql> create index nianling on home.tea4(age);    -- 给age创建一个索引，索引命令为"nianling"
-- Query OK, 0 rows affected (0.50 sec)
-- Records: 0  Duplicates: 0  Warnings: 0


mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   |     | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)             | YES | MUL | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)






mysql> show【瘦】 create【克瑞特】 table【忒-部】 home.tea4 \G
-- mysql> show create table home.tea4 \G
*************************** 1. row ***************************
       Table: tea4
Create Table: CREATE TABLE `tea4` (
  `id` char(6) DEFAULT NULL,
  `name` varchar(6) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` enum('boy','girl') DEFAULT 'boy',
  KEY `name` (`name`),
  KEY `nianling` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> show【瘦】 index【因戴克斯】 from【弗乱】 home.tea4;
mysql> show index from home.tea4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tea4  |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tea4  |          1 | nianling |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.07 sec)


mysql> show【瘦】  index【因戴克斯】   from【弗乱】  home.tea4 \G
-- mysql> show index from home.tea4 \G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: nianling       -- 设置的索引名
 Seq_in_index: 1
  Column_name: age            -- 表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql>

6）验证索引#

mysql> explain【e 斯 波 类】 select【涩莱克特】  * from【弗乱】 moershi.user where【威尔】 name="sshd" \G
-- mysql> explain select * from moershi.user where name="sshd" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user         -- 表名
   partitions: NULL
         type: ref
possible_keys: name
          key: name         -- 使用的索引名
      key_len: 21
          ref: const
         rows: 1            -- 查找的总行数
     filtered: 100.00
        Extra: NULL         -- 额外说明
1 row in set, 1 warning (0.00 sec)

六、作业#

1 选择题#

  1. 以下关于主键的描述，正确的是（ C ） A. 一个表可以有多个主键 B. 主键的值可以为 NULL C. 主键用于唯一标识表中的每一行记录 D. 主键只能由一个字段组成
Terminal window
主键的核心作用是
  唯一标识表中的每一行记录
  一个表只能有一个主键（不论是单字段还是多字段联合主键），
  且主键值不可重复、不可为NULL
  主键可以由多个字段组成（联合主键）
  1. 外键的作用是（ B ） A. 保证表中数据的唯一性 B. 建立表与表之间的关联 C. 提高数据的查询速度 D. 对数据进行加密
Terminal window
外键的主要作用是
  建立并维护表与表之间的关联关系
  从而保证数据的参照完整性，防止产生“孤立数据”。
  保证数据唯一性是主键或唯一约束的作用
  1. 以下哪种情况适合创建索引（ C ） A. 经常进行更新操作的字段 B. 数据量很小的表 C. 经常作为查询条件的字段 D. 很少使用的字段
Terminal window
为频繁作为查询条件（WHERE子句） 的字段创建索引，可以显著提高查询速度。
而经常更新的字段、数据量小的表或很少使用的字段创建索引，可能无法带来性能提升，反而增加维护开销
  1. 在 MySQL 中，创建主键的关键字是（ B ） A. PRIMARY B. PRIMARY KEY C. UNIQUE D. FOREIGN KEY
Terminal window
在MySQL中，定义主键的语法关键字是 PRIMARY KEY
  1. 若要删除表中的外键约束，应使用的关键字是（ A ） A. DROP FOREIGN KEY B. DELETE FOREIGN KEY C. REMOVE FOREIGN KEY D. ALTER FOREIGN KEY
Terminal window
删除外键约束需要使用 ALTER TABLE 库.表 DROP FOREIGN KEY(表头名);语句，后接具体的外键约束名

2 简答题#

  1. 简述主键和唯一索引的区别。 答： 主键： 是一种约束，用于唯一标识每条记录，可以作为外键的引用。一个表只能有一个主键（但可以是复合主键），主键值不可重复、不可为NULL。

    唯一索引： 是一种索引，附带唯一性保证，用于确保索引列的数据唯一。一个表可以有多个唯一索引，也可以作为外键的引用（只要它满足唯一性）。唯一索引允许NULL值，并且可以包含多个NULL（但具体取决于数据库系统，如MySQL中唯一索引允许多个NULL）。

  2. 说明外键约束在数据库设计中的重要性。 答： 外键约束通过强制要求子表（从表）中的外键字段值必须存在于主表（被引用表）的主键中，来维护参照完整性，这有效防止了”孤立数据”的产生。

  3. 索引在什么情况下会失效？如何避免索引失效？ 答： 索引在以下情况下会失效： 索引在以下情况下会失效（针对WHERE条件）： 查询条件中使用了函数或表达式，如 WHERE YEAR(date_column) = 2020。 查询条件中使用了通配符（以%开头），如 WHERE name LIKE ‘%John%’。 查询条件中使用了不等于操作符（如 !=或 <>），但如果是覆盖索引可能有效。 数据类型隐式转换（如字符串列与数字比较）。 OR条件连接多个索引列（如果未优化）。

    为了避免索引失效，可以采取以下措施： 避免在查询条件中使用函数或表达式，尽量使用索引列原始值。 尽量使用等值查询，避免通配符开头；如果必须用LIKE，尝试用前缀匹配（如 LIKE ‘John%’）。 确保ORDER BY或GROUP BY的列上有索引，以利用索引排序。 使用覆盖索引（索引包含所有查询字段）来优化不等于查询。 避免数据类型转换，确保比较时类型一致。

3 操作题#

  1. 创建一个名为 customers 的表，包含 customer_id（作为主键）、customer_name、email 字段。
mysql> create table db1.customers (
    -> customer_id int primary key auto_increment,
    -> customer_name char(10),
    -> email varchar(255)
    -> )engine=innodb;
Query OK, 0 rows affected (1.44 sec)
  1. 创建一个 orders 表，包含 order_id、customer_id（作为外键关联 customers 表的 customer_id）、order_date 字段。
mysql> create table db1.orders (
    -> order_id int primary key auto_increment,
    -> customer_id int,
    -> order_date datetime,
    -> foreign key (customer_id) references customers(customer_id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
  1. 在 customers 表的 email 字段上创建一个唯一索引。
mysql>
mysql> alter table db1.customers add unique (email);
Query OK, 0 rows affected (4.387 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. 修改 orders 表，添加一个新的外键约束，关联 products 表（假设 products 表已存在，有 product_id 字段）。
-- 添加product_id字段
mysql> alter table db1.orders add product_id int;
Query OK, 0 rows affected (5.441 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 创建product表
mysql> create table db1.products(
    -> product_id int primary key auto_increment
    -> )engine=innodb;
Query OK, 0 rows affected (0.689 sec)
-- 添加外键约束
mysql> alter table db1.orders
    -> add foreign key(product_id) references db1.products(product_id)
    -> on update cascade;
Query OK, 0 rows affected (2.100 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看orders表
mysql> show create table db1.orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `customer_id` (`customer_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.065 sec)
  1. 删除 customers 表上的索引。
-- 查看`customers` 表索引
mysql> show index from db1.customers;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customers |          0 | PRIMARY  |            1 | customer_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customers |          0 | email    |            1 | email       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (1.751 sec)
-- 删除email唯一索引
mysql> alter table db1.customers drop index email;
Query OK, 0 rows affected (2.905 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看`customers` 表索引
mysql> show index from db1.customers;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customers |          0 | PRIMARY  |            1 | customer_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.256 sec)

删除 orders 表上的索引。

-- 查看外键属性
mysql> show create table db1.orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `customer_id` (`customer_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (1.279 sec)


-- 删除外键orders_ibfk_1
mysql> alter table db1.orders drop foreign key orders_ibfk_1;
Query OK, 0 rows affected (1.222 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 删除外键orders_ibfk_2
mysql> alter table db1.orders drop foreign key orders_ibfk_2;
Query OK, 0 rows affected (0.342 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 查看索引名
mysql> show index from db1.orders;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY     |            1 | order_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | product_id  |            1 | product_id  | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| orders |          1 | customer_id |            1 | customer_id | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (1.037 sec)


-- 删除索引 product_id
mysql> drop index product_id on db1.orders;
Query OK, 0 rows affected (1.191 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 删除索引 customer_id
mysql> drop index customer_id on db1.orders;
Query OK, 0 rows affected (0.326 sec)
Records: 0  Duplicates: 0  Warnings: 0


-- 查看索引属性
mysql> show index from db1.orders;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY  |            1 | order_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.392 sec)
06: mysql之主键外键索引
https://fuwari.vercel.app/posts/数据库/06-mysql之主键外键索引/
作者
肥猫少杰
发布于
2026-06-03
许可协议
CC BY-NC-SA 4.0
05：mysql之表结构管理
07 mysql之数据导入导出与用户管理
© 2026 肥猫少杰. 保留所有权利。 / RSS / 站点地图
由 Astro & Fuwari 驱动