7.4本章实例
在本小节将在jop数据库中创建一个user表和一个information表。具体下图所示。
user表
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|---|
| userid | 编号 | INT(10) | 是 | 否 | 是 | 是 | 是 |
| username | 用户名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| password | 密码 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| info | 附加信息 | TEXT | 否 | 否 | 否 | 否 | 否 |
information
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|---|
| id | 编号 | INT(10) | 是 | 否 | 是 | 是 | 是 |
| name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
| birthday | 出生日期 | DATE | 否 | 否 | 否 | 否 | 否 |
| address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
| tel | 电话号码 | VARCHAR(20) | 否 | 否 | 否 | 否 | 否 |
| pic | 照片 | BLOB | 否 | 否 | 否 | 否 | 否 |
按照下列要求进行操作：
(1)登录数据库系统后创建job数据库。
(2)创建user表,存储引擎为MyISAM类型。创建表的时候同时几个索引，在userid字段上创建名为index_uid的唯一性索引，并且以降序的形式排列；在usemame和passwd 字段上创建名为index_user的多列索引；在info字段上创建名为index_info的全文索引。
(3)创建information表。
(4) 在name字段创建名为index_name的单列索引，索引长度为10。
(5) 在birthday和address字段是创建名为index_bir的多列索引，然后判断索引的使用情况。
(6) 用ALTER TABLE语句在id字段上创建名为index_id的唯一性索引，而且以升序排列。
(7) 删除user表上的index_user索弓|。
(8) 删除information 表上的 index_name 索引。
本实例的执行过程如下：
1.登录数据库系统并创建job数据库
在命令行中登录MySQL数据库管理系统，输入内容如下：
mysql -h localhost -u root -p
提示输入密码后，按要求输入密码，显示为：
Enter password: ****
按ENTER键后，检验密码正确后进入Mysql管理系统。执行SHOW语句来查看数据库系统中已经存在的数据库，代码执行如下：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | example | | landingbj | | mysql | +--------------------+ 4 rows in set (0.14 sec)
结果显示，数据库系统中不存在名为JOB的数据库。CRCREATE DATABASE语句来创建数据库，代码执行如下：
mysql> CREATE DATABASE job;
Query OK, 1 row affected (0.08 sec)
执行结果显示，数据库创建成功。再次执行SHOW语句来查看JOB数据库是否已经存在。代码执行如下：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | example | | job | | landingbj | | mysql | +--------------------+ 5 rows in set (0.03 sec)
结果显示，已经存在名为job的数据库
2.创建user表
先使用USE job语句选择job数据库。
结果显示，数据库已经选择成功。然后可以执行CREATE TABLE语句来创建user表。根据实例要求，存储引擎为MyISAM类型；在userid字段上创建名为index_uid的唯一性索引，并且以降序的形式排列；在usemame和passwd字段上创建名为index_user的多列索引；在info字段上创建名为index_info的全文索引。SQL代码如下：
mysql> CREATE TABLE user(userid INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,passwo rd VARCHAR(20) NOT NULL , info TEXT,UNIQUE INDEX index_uid(userid DESC),INDEX index_user(username,password),FULLTEXT IND EX index_info(info))ENGINE=MyISAM;
执行结果显示，user表创建成功。执行SHOW CREATE TABLE语句来查看use表的 结构。执行结果如下：
mysql> show create table user \G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `userid` int(10) NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(20) NOT NULL, `info` text, PRIMARY KEY (`userid`), UNIQUE KEY `userid` (`userid`), UNIQUE KEY `index_uid` (`userid`), KEY `index_user` (`username`,`password`), FULLTEXT KEY `index_info` (`info`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
结果显示，index_uid是userid字段上的唯一性索引；index_user是user字段和passwd 字段上的索引；index_user是user字段上的全文索引；存储引擎为MyISAM。
3.创建information表
在job数据库创建名为information的表。代码如下：
mysql> create table information(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL,sex VAR CHAR(4) NOT NULL,birthday DATE,address VARCHAR(20),tel VARCHAR(20),pic BLOB); Query OK, 0 rows affected (0.23 sec)
执行结果显示，information表创建成功。执行SHOW CREATE TABLE语句来查看information表的结构。执行结果如下：
mysql> show create table information \G; *************************** 1. row *************************** Table: information Create Table: CREATE TABLE `information` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `sex` varchar(4) NOT NULL, `birthday` date DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `tel` varchar(20) DEFAULT NULL, `pic` blob, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
查询结果显示,id字段是主键，而且有唯一性约束。
4.在那么字段创建名为index_name的索引
使用CREATE INDEX语句创建index_name索引，代码如下
mysql> create INDEX index_name ON information(name(10)); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0
5.创建名为index_bir的多列索引
使用CREATE INDEX 语句创建index_bir索引，代码如下：
mysql> create index index_bir on information(birthday ,address); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0
6.用ALTER TABLE语句创建index_id的唯一性索引
使用ALTER TABLE语句创建index_bir索引，代码如下：
mysql> alter table information add index index_id(id ASC); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
结果显示index_id索引创建成功。执行SHOW CREATE TABLE语句来查看information表的结构。执行结果如下：
mysql> show create table information \G; *************************** 1. row *************************** Table: information Create Table: CREATE TABLE `information` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `sex` varchar(4) NOT NULL, `birthday` date DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `tel` varchar(20) DEFAULT NULL, `pic` blob, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `index_name` (`name`(10)), KEY `index_bir` (`birthday`,`address`), KEY `index_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
执行结果显不，information表中己经存在index name、index_bir和index_id等3个索引。
7.删除user表上的index_user索引
执行DROP语句可以删除user表上的索引。代码执行结果如下：
mysql> drop index index_user on user; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
结果显示删除成功。执行SHOW CREATE TABLE语句来查看user表的结构。执行结果如下：
mysql> show create table user \G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `userid` int(10) NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(20) NOT NULL, `info` text, PRIMARY KEY (`userid`), UNIQUE KEY `userid` (`userid`), UNIQUE KEY `index_uid` (`userid`), FULLTEXT KEY `index_info` (`info`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
结果显示index_user索引已经不存在了。
删除information表上的index_name索引
执行DROP语句可以删除information表上的index_name索引。代码执行结果如下：
mysql> drop index index_name on information; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
结果显示删除成功。执行SHOW CREATE TABLE语句来查看information表的结构。执行结果如下：
mysql> show create table information \G; *************************** 1. row *************************** Table: information Create Table: CREATE TABLE `information` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `sex` varchar(4) NOT NULL, `birthday` date DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `tel` varchar(20) DEFAULT NULL, `pic` blob, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `index_bir` (`birthday`,`address`), KEY `index_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
结果显示，index_name索引已经不存在了。
