mysql数据库（索引）

#建库

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

#建表

CREATE TABLE IF NOT EXISTS cxc(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) not null,
    salary INT not null,
    dept_id INT not null,
    manager_id INT not null
);

#创建索引的三种方式
#1.直接指定索引
CREATE INDEX index_name ON cxc(name);
SELECT*FROM cxc WHERE name="liu";
#2.修改表时添加索引
ALTER TABLE cxc ADD INDEX index_salary (salary);
#3.创建表时添加索引
CREATE TABLE article(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20) COMMENT'标题',
    content VARCHAR(100) COMMENT'内容',
    dt DATE,
    #指定索引
    INDEX index_title(title)
)

#删除索引
DROP INDEX index_salary ON cxc;
DROP INDEX index_name ON cxc;
DROP INDEX index_title ON article;

#创建唯一索引
CREATE UNIQUE INDEX inedx index_name ON cxc(name);
ALTER TABLE cxc ADD UNIQUE INDEX index_salary(salary);

CREATE TABLE article(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20) COMMENT'标题',
    content VARCHAR(100) COMMENT'内容',
    dt DATE,
    #指定索引
    UNIQUE INDEX index_title(title)
);

#创建组合索引(为两字段创建一个索引)
CREATE INDEX index_title_content ON article(title,content)
SELECT*FROM article WHERE title="冬奥会" AND content ="金牌";

ALTER TABLE cxc ADD INDEX index_salary (salary);