MySQL知识（五）--- 索引分析

2022-10-09 07:44:23

0.准备

首先创建三张表：tb_emp(职工表)、tb_dept(部门表)和tb_desc(描述表)

1）tb_emp表。

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
   `deptid` int(11) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp`(username,deptid) VALUES (‘Tom‘, ‘1‘);
INSERT INTO `tb_emp`(username,deptid) VALUES (‘Jack‘, ‘1‘);
INSERT INTO `tb_emp`(username,deptid) VALUES (‘Mary‘, ‘2‘);
INSERT INTO `tb_emp`(username,deptid) VALUES (‘Rose‘, ‘3‘);

2）tb_dept表。

DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_dept`(name) VALUES (‘综合部‘);
INSERT INTO `tb_dept`(name) VALUES (‘研发‘);
INSERT INTO `tb_dept`(name) VALUES (‘测试‘);
INSERT INTO `tb_dept`(name) VALUES (‘总裁‘);

3）tb_desc表。

DROP TABLE IF EXISTS `tb_desc`;
CREATE TABLE `tb_desc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empid` int(11) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_desc`(empid,deptid) VALUES (1, 1);
INSERT INTO `tb_desc`(empid,deptid) VALUES (2, 1);
INSERT INTO `tb_desc`(empid,deptid) VALUES (3, 2);
INSERT INTO `tb_desc`(empid,deptid) VALUES (4, 3);