(1)创建job数据库
CREATE DATABASE job;
(2)使用job数据库,创建user表和information表
USE job;
CREATE TABLE USER(userid INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
passwd VARCHAR(20) NOT NULL,
info TEXT,
UNIQUE INDEX index_uid(userid DESC),
INDEX index_user(username,passwd),
FULLTEXT INDEX index_info(info)
)ENGINE=MYISAM;
CREATE TABLE information(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
tel VARCHAR(20),
pic BLOB
);
(3)在name字段创建名为index_name的单列索引,索引长度为10
CREATE INDEX index_name ON information(NAME(10));
(4)在birthday和address字段是创建名为index_bir的多列索引,然后判断索引的使用情况
CREATE INDEX index_bir ON information(birthday,address);
(5)用ALTER TABLE 语句在id字段上创建名为index_id的唯一性索引,而且以升序排列
ALTER TABLE information ADD UNIQUE INDEX index_id(id ASC);
(6)删除userr表上的index索引
DROP INDEX index_user ON USER;
(7)删除information表上的index_name索引
DROP INDEX index_name ON information;
CREATE DATABASE job;
(2)使用job数据库,创建user表和information表
USE job;
CREATE TABLE USER(userid INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
passwd VARCHAR(20) NOT NULL,
info TEXT,
UNIQUE INDEX index_uid(userid DESC),
INDEX index_user(username,passwd),
FULLTEXT INDEX index_info(info)
)ENGINE=MYISAM;
CREATE TABLE information(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
tel VARCHAR(20),
pic BLOB
);
(3)在name字段创建名为index_name的单列索引,索引长度为10
CREATE INDEX index_name ON information(NAME(10));
(4)在birthday和address字段是创建名为index_bir的多列索引,然后判断索引的使用情况
CREATE INDEX index_bir ON information(birthday,address);
(5)用ALTER TABLE 语句在id字段上创建名为index_id的唯一性索引,而且以升序排列
ALTER TABLE information ADD UNIQUE INDEX index_id(id ASC);
(6)删除userr表上的index索引
DROP INDEX index_user ON USER;
(7)删除information表上的index_name索引
DROP INDEX index_name ON information;