#创建库
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="金牌数量";