mysql总结(2)-索引

mysql命令学习总结（数据库版本：8.0.21）

在mysql客户端工具中打开mysql命令：以navicat15为例， 新建查询执行以下语句

索引注意：
在索引列上使用函数，会使索引失效

1、创建查看普通索引
-- 创建表时创建普通索引
CREATE TABLE t_dept(
    deptno INT auto_increment COMMENT '部门编号',
    dname VARCHAR(20) COMMENT '部门名称',
    loc VARCHAR(40) COMMENT '部门所在地',
    INDEX indx_deptno(deptno)
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '部门表'

-- 添加测试数据
INSERT INTO t_dept(dname, loc)VALUES('市场部','建材城东里森林大第小区居委会');
INSERT INTO t_dept(dname, loc)VALUES('技术部','建材城东里森林大第小区南区居委会');
INSERT INTO t_dept(dname, loc)VALUES('质量部','建材城东里森林大第小区北区居委会');

-- 给表t_dept增加主键
ALTER TABLE t_dept MODIFY COLUMN deptno INT PRIMARY KEY auto_increment COMMENT '部门编号'

-- 查看索引是否创建成功
SHOW CREATE TABLE t_dept;

-- 检查索引是否被使用
EXPLAIN
    SELECT * FROM t_dept WHERE deptno = 1;
    
-- 删除索引
DROP INDEX indx_deptno ON t_dept;

-- 在已存在的表上创建普通索引
create index idx_deptno on t_dept(deptno);

-- 通过sql语句Alter table 创建普通索引
ALTER TABLE t_dept 
ADD INDEX idx_deptno(deptno);

2、创建查看唯一索引
-- 创建表时创建唯一索引
CREATE TABLE t_dept(
    deptno int auto_increment PRIMARY KEY COMMENT '部门编号',
    dname VARCHAR(20) COMMENT '部门名称',
    loc VARCHAR(40) COMMENT '部门地址',
    UNIQUE INDEX idx_deptno(deptno)
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '部门表'

-- 在已存在的表上创建唯一索引
CREATE UNIQUE INDEX idx_deptno ON t_dept(deptno);

-- 通过sql语句Alter table创建唯一索引
ALTER TABLE t_dept 
ADD UNIQUE INDEX idx_deptno(deptno);

3、创建查看全文索引
-- 创建表时创建全文索引
CREATE TABLE t_dept(
    deptno INT auto_increment PRIMARY KEY COMMENT '部门编号',
    dname VARCHAR(20) COMMENT '部门名称',
    loc VARCHAR(40) COMMENT '部门位置',
    FULLTEXT INDEX idx_full_loc(loc)
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '部门表'

-- 在已存在的表上创建全文索引
CREATE FULLTEXT INDEX idx_ful_loc
ON t_dept(loc);

-- 通过sql语句Alter table创建全文索引
ALTER TABLE t_dept 
ADD FULLTEXT INDEX idx_ful_loc(loc);

4、创建和查看多列索引
--创建表时创建多列索引
CREATE TABLE t_dept(
    deptno INT auto_increment PRIMARY KEY COMMENT '部门编号',
    dname VARCHAR(20) COMMENT '部门名称',
    loc VARCHAR(40) COMMENT '部门位置',
    KEY idx_dname_loc(dname, loc)
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '部门表'

-- 在已存在的表上创建多列索引
CREATE INDEX idx_dname_loc ON t_dept(dname, loc);

-- 通过sql语句Alter table创建多列索引
ALTER TABLE t_dept
ADD INDEX idx_dname_loc(dname,loc);