SQL索引--刘世民

#创建库
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(50),
    salary INT,
    dept_id INT,
    manager_id INT
    
);
#查询
SELECT * FROM emp;
#直接添加索引
CREATE INDEX index_emp_name ON emp(emp_name);
SELECT * FROM emp WHERE emp_name="liu";
#修改表时添加索引
ALTER TABLE emp ADD INDEX  index_salary (salary);
#创建表时添加索引
CREATE TABLE  IF NOT EXISTS article
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50) COMMENT '标题',
    content VARCHAR(100) COMMENT '内容',
    dt DATE,
    INDEX index_title (title)
);
SELECT * FROM article;

#删除索引
DROP INDEX index_salary ON emp;
DROP INDEX index_emp_name ON emp;
DROP INDEX index_title ON article;

#创建唯一索引
CREATE UNIQUE INDEX index_emp_name ON emp(emp_name);
ALTER TABLE emp ADD  UNIQUE INDEX index_salary (salary);
CREATE TABLE  IF NOT EXISTS article2
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50) COMMENT '标题',
    content VARCHAR(100) COMMENT '内容',
    dt DATE,
    UNIQUE    INDEX index_title (title)
);
SELECT * FROM article2
#创建组合索引1
CREATE INDEX index_title_content ON article(title,content);
SELECT * FROM article WHERE title ="冬奥会" AND content="金牌数量";