索引实例

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

CREATE DATABASE  IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
CREATE TABLE IF NOT EXISTS emp (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    salary INT,
    dept_id INT,
    manager_id INT
);
INSERT INTO emp(emp_name,salary,dept_id,manager_id)VALUES
("tom",15000,1,NULL),
("lucy",1600,2,NULL),
("morty",14000,5,NULL),
("张建华",4000,1,1),
("李华",6000,1,1),
("孙思秒",3000,1,1),
("王强",6000,2,2),
("周杰",5000,2,2),
("曹光思",7000,5,3),
("刘毅真",8000,5,3),
("孙岩",5000,5,3);

#查询工资比王强高的人
SELECT * FROM emp WHERE salary >(SELECT salary FROM emp WHERE emp_name="王强")
#
SELECT * FROM emp WHERE dept_id = (SELECT dept_id FROM emp WHERE emp_name="张建国")
AND salary > (SELECT salary FROM emp WHERE emp_name = "孙岩");

#多值
#查询姓孙员工所在部门的全部员工信息
SELECT *FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE emp_name LIKE "孙%");

#创建索引：单独创建索引
CREATE INDEX index_name ON emp (emp_name);
SELECT * FROM emp WHERE emp_name = "父";
#创建索引：修改表结构的方式添加索引
ALTER TABLE emp ADD INDEX index_salary(salary);
#创建索引:创建表时，创建索引
CREATE TABLE IF NOT EXISTS srticle(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(20),
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title (title)
);
#删除索引
DROP INDEX index_name ON emp;
#唯一索引：列的值必须唯一
DROP UNIQUE INDEX index_name ON emp(emp_name);
#单独创建    组合索引
CREATE INDEX index_ct ON article(contennt,dt);
SELECT * FROM article WHERE content = "123" AND dt = "2020-10-10";