mysql 索引、约束

索引

创建索引

-- 从表的角度创建索引
ALTER TABLE actor ADD UNIQUE INDEX  uniq_idx_firstname (first_name);
ALTER TABLE actor ADD INDEX  idx_lastname (last_name);
-- 从数据库的角度创建索引
CREATE UNIQUE INDEX uniq_idx_firstname USING BTREE actor (first_name);

删除索引

ALTER TABLE actor DROP INDEX uniq_idx_firstname;

DROP INDEX uniq_idx_firstname ON actor;

强制使用索引

select *
from actor 
force index (uniq_idx_firstname)
where first_name = 'jd'

约束

添加主键约束

ALTER TABLE employees_test
ADD CONSTRAINT PRIMARY KEY(id);

添加外键约束

ALTER TABLE audit
ADD CONSTRAINT
constraint_name
FOREIGN KEY(emp_no)
REFERENCES employees_test(id);