SQL索引

CREATE DATABASE IF NOT EXISTS web DEFAULT CHARSET utf8;
USE web;

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);

SELECT * FROM emp WHERE emp_name="zxc"
#1.1单独创建索引
CREATE INDEX index_name ON emp(emp_name);
#1.2创建表时修改索引
ALTER TABLE emp ADD INDEX index_salary (salary);

#1.3创建表时创建索引
CREATE TABLE IF NOT EXISTS article(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title (title))
#2删除索引
DROP INDEX index_name ON emp;

#3.1唯一索引：列的值必须唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name)
#3.2修改表创建唯一索引
ALTER TABLE emp ADD UNIQUE INDEX index_name (emp_name)

#3.3创建表时创建唯一索引
CREATE TABLE IF NOT EXISTS article1(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
UNIQUE INDEX index_title (title))

#4.1组合索引
CREATE INDEX index_ct ON article(content,dt);
SELECT * FROM article WHERE content="123" AND dt="2020-10-10"

#4.2修改表创建组合索引
ALTER TABLE emp ADD INDEX index_dept (dept_id,salary)

#4.3创建表时创建组合索引
CREATE TABLE IF NOT EXISTS article2(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title (title),
INDEX index_dt (title,dt))