1创建系部表departments,表结构见课本附录B中表B-7,为dptcode列创建主键索引,为dptname列创建唯一性索引,索引名为UN_dptname,然后查看departments表的索引情况。
CREATE TABLE departments (
dptcode INT NOT NULL,
dptname VARCHAR(255) NOT NULL,
PRIMARY KEY (dptcode)
);
CREATE UNIQUE INDEX UN_dptname ON departments(dptname);
SHOW INDEX FROM departments;
2修改学生表students,使用ALTER TABLE语句创建索引,为dob列创建普通索引,为stdname列创建全文索引,索引名为FT_stdname,然后查看students表的索引情况。
ALTER TABLE students ADD INDEX idx_dob (dob);
ALTER TABLE students ADD FULLTEXT INDEX FT_stdname (stdname);
SHOW INDEX FROM students;
3使用CREATE INDEX语句给学生表students的dptcode和classcode列创建复合索引,索引名为FH_dptcode_classcode,然后查看students表的索引情况。
CREATE INDEX FH_dptcode_classcode ON students(dptcode, classcode);
SHOW INDEX FROM students;
4将学生表students的复合索引FH_dptcode_classcode改为不可见索引。
ALTER INDEX FH_dptcode_classcode SET CONCURRENTLY UNUSABLE;
5删除学生表students的复合索引FH_dptcode_classcode,然后查看students表的索引情况。
DROP INDEX FH_dptcode_classcode ON students;
SHOW INDEX FROM students;