描述
针对如下表actor结构创建索引:
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作,
mysql支持ALTER TABLE创建索引)
CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);
对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
CREATE INDEX idx_lastname ON actor(last_name(45));
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name(45));
ALTER TABLE actor ADD INDEX idx_lastname(last_name);
ALTER TABLE actor ADD UNIQUE INDEX uniq_idx_firstname(first_name);
actor|CREATE TABLE `actor` (
`actor_id` smallint(5) NOT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` datetime NOT NULL,
PRIMARY KEY (`actor_id`),
UNIQUE KEY `uniq_idx_firstname` (`first_name`),
KEY `idx_lastname` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8