09数据库复习03

-- 创建普通索引
CREATE TABLE test4(
    id TINYINT UNSIGNED,
    username VARCHAR(20),
    INDEX in_id(id),
    KEY in_user(username)
);

-- 创建唯一性索引
CREATE TABLE test5(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    card CHAR(18) NOT NULL,
    UNIQUE KEY uni_card(card)
);

-- 创建全文索引
CREATE TABLE test6(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    userdesc VARCHAR(200) NOT NULL,
    FULLTEXT INDEX full_userDesc(userdesc)
);

-- 创建单列索引
CREATE TABLE test7(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    INDEX in_t1(t1)
);

-- 创建多列索引
CREATE TABLE test8(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    INDEX mul_t1_t2_t3(t1,t2,t3)
);

-- 创建唯一性的多列索引
CREATE TABLE test9(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    UNIQUE KEY  mul_t1_t2_t3(t1,t2,t3)
);

DROP INDEX in_id ON test4;

DROP INDEX in_user ON test4;

-- 在已存在的表上添加索引
CREATE INDEX in_id ON test4(id);

ALTER TABLE test4 ADD INDEX in_username(username);