对数据表的操作

1.创建数据表

1>.调用数据库命令：USE itcast;

命令输入正确后的结果   

Database changed ;

2>.创建时数据表：CREATE TABLE student_tb
                                  -> (
                                  -> id INT(10),
                                  -> name CHAR(20),
                                   -> age INT(2),
                                   -> sex CHAR(5)
                                   -> );
 命令输入正确后的结果  

Query OK, 0 rows affected

2.查看数据表是否创建成功：SHOW TABLES;

命令输入正确后的结果
+------------------+
| Tables_in_itcast |
+------------------+
| student_tb       |
+------------------+
1 row in set

3.查看已创建数据表信息：SHOW CREATE TABLE student_tb;

命令输入正确后的结果
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                       |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_tb | CREATE TABLE `student_tb` (
  `id` int(10) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `sex` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

4.查看数据表信息（表的具体信息）：DESC student_tb;

命令输入正确后的结果
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(10)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| age   | int(2)   | YES  |     | NULL    |       |
| sex   | char(5)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set

5.修改数据表名称：ALTER TABLE student_tb RENAME TO student1923_tb;

命令输入正确后的结果

Query OK, 0 rows affected

6.修改数据字段名：ALTER TABLE student1923_tb CHANGE name username CHAR(20);

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

7.修改字段数据类型：ALTER TABLE student1923_tb MODIFY username VARCHAR(20);

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

 

命令输入正确后的结果
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set

8.添加字段：

1>.添加至最后：ALTER TABLE student1923_tb ADD address CHAR(20);

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

命令输入正确后的结果

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
| address  | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set

2>.添加至中间：ALTER TABLE student1923_tb ADD mail CHAR(30) AFTER sex;

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

命令输入正确后的结果

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
| mail     | char(30)    | YES  |     | NULL    |       |
| address  | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set

3>.添加至最前:ALTER TABLE student1923_tb ADD grade CHAR(30) FIRST;

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

命令输入正确后的结果

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| grade    | char(30)    | YES  |     | NULL    |       |
| id       | int(10)     | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
| mail     | char(30)    | YES  |     | NULL    |       |
| address  | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set

9.删除字段：ALTER TABLE student1923_tb DROP mail;

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

命令输入正确后的结果

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| grade   | char(30)    | YES  |     | NULL    |       |
| id       | int(10)     | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
| address  | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set

10.修改字段排列位置：

1>.将字段修改为第一：ALTER TABLE student1923_tb MODIFY username VARCHAR(20) FIRST;

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看数据表信息（表的具体信息）是否修改成功:DESC student1923_tb;

命令输入正确后的结果

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| grade    | char(30)    | YES  |     | NULL    |       |
| id       | int(10)     | YES  |     | NULL    |       |
| age      | int(2)      | YES  |     | NULL    |       |
| sex      | char(5)     | YES  |     | NULL    |       |
| address  | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set

2>.将字段修改为...后：ALTER TABLE student1923_tb MODIFY username VARCHAR(20) AFTER grade;

命令输入正确后的结果
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

11.删除数据表：DROP TABLE student1923_tb;

命令输入正确后的结果
Query OK, 0 rows affected

12.查看是否删除数据表成功：SHOW TABLES;

命令输入正确后的结果
Empty set

13.字键约束：

1>.单字段：CREATE TABLE exam1
                     -> (
                     -> id INT(20),
                     -> name CHAR(20) PRIMARY KEY
                     -> );

命令输入正确后的结果
Query OK, 0 rows affected

查看数据表信息（表的具体信息）是否修改成功:DESC exam1;

命令输入正确后的结果

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(20)  | YES  |     | NULL    |       |
| name  | char(20) | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

2>.多字段：CREATE TABLE exam2
                           -> (
                          -> id INT(20),
                          -> name CHAR(20),
                           -> PRIMARY KEY(id,name)
                             -> );

命令输入正确后的结果
Query OK, 0 rows affected

查看数据表信息（表的具体信息）是否修改成功:DESC exam2;

命令输入正确后的结果

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(20)  | NO   | PRI | 0       |       |
| name  | char(20) | NO   | PRI |         |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

14.非空约束：CREATE TABLE exam3
                          -> (
                          -> id INT(20),
                          -> name CHAR(20) NOT NULL
                          -> );

命令输入正确后的结果
Query OK, 0 rows affected

查看数据表信息（表的具体信息）是否修改成功:DESC exam3;

命令输入正确后的结果

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(20)  | YES  |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

15.唯一约束：CREATE TABLE exam4
                      -> (
                      -> id INT(20),
                      -> name CHAR(20)UNIQUE
                     -> );

命令输入正确后的结果

Query OK, 0 rows affected

查看数据表信息（表的具体信息）是否修改成功：DESC　exam４；

命令输入正确后的结果

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(20)  | YES  |     | NULL    |       |
| name  | char(20) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

16.默认约束: CREATE TABLE exam5
                      -> (
                      -> id INT(20) PRIMARY KEY AUTO_INCREMENT,
                      -> name CHAR(20)
                       -> );

命令输入正确后的结果
Query OK, 0 rows affected

查看数据表信息（表的具体信息）是否修改成功：DESC　exam5；

命令输入正确后的结果

+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set

17.索引

17.1

 

 17.2

 

 17.3

17.3.1：创建数据表时创建索引

 

 

 17.3.1.1：创建普通索引

 

eg1：

mysql> CREATE TABLE t1(id INT,name VARCHAR(20),score FLOAT,INDEX(id));
Query OK, 0 rows affected

查看是否创建正确

1：

mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `score` float DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2：

mysql> EXPLAIN SELECT*FROM t1;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set

17.3.1.2创建唯一性索引

 

 

 

 

 

 eg2：

mysql> CREATE table t2(id INT NOT NULL,name  VARCHAR(20)NOT NULL,score FLOAT,UNIQUE INDEX unique_id(id ASC));
Query OK, 0 rows affected

查看是否创建正确

1：

mysql> SHOW CREATE TABLE t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` float DEFAULT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2：

mysql> EXPLAIN SELECT*FROM t2;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t2    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set

17.3.1.3：创建全文索引

 

 

 eg3：

mysql> CREATE TABLE t3(id INT NOT NULL,name VARCHAR(20) NOT NULL,score FLOAT,FULLTEXT INDEX fulltext_name(name))ENGINE=MyISAM;
Query OK, 0 rows affected

查看是否创建正确

1.

mysql> SHOW CREATE TABLE t3;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` float DEFAULT NULL,
  FULLTEXT KEY `fulltext_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2.

mysql> EXPLAIN SELECT*FROM t3;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t3    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set

17.3.1.4：创建单列索引

 

 

 eg4：

mysql> CREATE TABLE t4(id INT NOT NULL,name VARCHAR(20) NOT NULL,score FLOAT,INDEX single_name(name(20)));
Query OK, 0 rows affected

查看是否创建正确

1：

mysql> SHOW CREATE TABLE t4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` float DEFAULT NULL,
  KEY `single_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2：

mysql> EXPLAIN SELECT*FROM t4;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t4    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set

17.3.1.5：创建多列索引

 

 

 eg5：

mysql> CREATE TABLE t5(id INT NOT NULL,name VARCHAR(20) NOT NULL,score FLOAT,INDEX multi(id,name(20)));
Query OK, 0 rows affected

查看是否创建正确

1.

mysql> SHOW CREATE TABLE t5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` float DEFAULT NULL,
  KEY `multi` (`id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2.

mysql> EXPLAIN SELECT*FROM t5;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | t5    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set

 

 mysql> EXPLAIN SELECT *FROM t5 WHERE id=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set

 mysql> EXPLAIN SELECT *FROM t5 WHERE name="Mike";
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set

17.3.1.6：创建空间索引

 

 

eg1：

 mysql> CREATE TABLE t6(id INT,space GEOMETRY NOT NULL,SPATIAL INDEX sp(space))ENGINE=MyISAM;
Query OK, 0 rows affected

查看是否创建正确

mysql> SHOW CREATE TABLE t6;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t6    | CREATE TABLE `t6` (
  `id` int(11) DEFAULT NULL,
  `space` geometry NOT NULL,
  SPATIAL KEY `sp` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.2:

 

 

 

 17.3.2.1:创建普通索引

创建book表

mysql> CREATE TABLE book (
  bookid  INT NOT NULL,
              bookname VARCHAR(255) NOT NULL,
 authors VARCHAR(255) NOT NULL,
    info VARCHAR(255) NULL,
  comment VARCHAR(255) NULL,
  publicyear YEAR NOT NULL
);

 

 mysql> CREATE INDEX index_id ON book(bookid);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看是否建立成功

mysql> SHOW CREATE TABLE book;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  KEY `index_id` (`bookid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.2.2：创建唯一性索引

mysql> CREATE UNIQUE INDEX uniqueidx ON book(bookid);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看是否建立成功

mysql> SHOW CREATE TABLE book;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.2.3：创建单列索引

 

 mysql> CREATE INDEX singleidx ON book(comment);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看是否建立成功

mysql> SHOW CREATE TABLE book;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`),
  KEY `singleidx` (`comment`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.2.3.4：创建多列索引

 

 mysql> CREATE INDEX mulitidx ON book(authors(20),info(20));

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看是否建立成功

mysql> SHOW CREATE TABLE book;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`),
  KEY `singleidx` (`comment`),
  KEY `mulitidx` (`authors`(20),`info`(20))
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.2.3.5：创建全文索引

 

删除表book

 mysql> DROP TABLE book;

Query OK, 0 rows affected

创建表

mysql> CREATE TABLE book (
  bookid  INT NOT NULL,
              bookname VARCHAR(255) NOT NULL,
 authors VARCHAR(255) NOT NULL,
    info VARCHAR(255) NULL,
  comment VARCHAR(255) NULL,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;
Query OK, 0 rows affected

17.3.2.5：创建全文索引

 

 mysql> CREATE FULLTEXT INDEX fulltextidx ON book(info);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

查看是否创建成功

mysql> SHOW CREATE TABLE book;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  FULLTEXT KEY `fulltextidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.2.3.6：创建空间索引

 

 CREATE TABLE t7(
                   g GEOMETRY NOT NULL
                 )ENGINE=MyISAM
    -> ;
Query OK, 0 rows affected

 

mysql> CREATE SPATIAL INDEX spatidx ON t7(g);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

mysql> SHOW CREATE TABLE  t7;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| t7    | CREATE TABLE `t7` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3：

 

 

  

删除数据表

mysql> DROP TABLE book;
Query OK, 0 rows affected

重建数据表

mysql> CREATE TABLE book (
  bookid  INT NOT NULL,
        bookname VARCHAR(255) NOT NULL,
 authors VARCHAR(255) NOT NULL,
    info VARCHAR(255) NULL,
  comment VARCHAR(255) NULL,
  publicyear YEAR NOT NULL
);

Query OK, 0 rows affected

 

 17.3.3.1：创建普通索引

mysql> ALTER TABLE book ADD INDEX index_id(bookid);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE book ;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  KEY `index_id` (`bookid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3.2：创建唯一性索引

mysql> ALTER TABLE book ADD UNIQUE uniqueidx(bookid);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE book ;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3.3：创建单列索引

mysql> ALTER TABLE book ADD INDEX singleidx (comment(50));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE book ;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`),
  KEY `singleidx` (`comment`(50))
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3.4：创建多列索引

mysql> ALTER TABLE book ADD INDEX multidx(authors(20),info(50));

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE book ;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`),
  KEY `singleidx` (`comment`(50)),
  KEY `multidx` (`authors`(20),`info`(50))
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3.5：创建全文索引

mysql> DROP TABLE book;

Query OK, 0 rows affected

 

然后重建数据表book；

mysql> CREATE TABLE book (
  bookid  INT NOT NULL,
        bookname VARCHAR(255) NOT NULL,
 authors VARCHAR(255) NOT NULL,
    info VARCHAR(255) NULL,
  comment VARCHAR(255) NULL,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;

Query OK, 0 rows affected

 

 

 

mysql> ALTER TABLE book ADD FULLTEXT INDEX fulltextidx(info);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE book ;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year(4) NOT NULL,
  FULLTEXT KEY `fulltextidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

17.3.3.6：创建空间索引

建表

mysql> CREATE TABLE t8(
                  space GEOMETRY NOT NULL
                )ENGINE=MyISAM;
Query OK, 0 rows affected

 

创建空间索引

mysql> ALTER TABLE t8 ADD SPATIAL INDEX spatidx(space);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否创建成功

mysql> SHOW CREATE TABLE  t8 ;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t8    | CREATE TABLE `t8` (
  `space` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set

17.4：删除索引

 eg1：

mysql> ALTER TABLE book DROP INDEX fulltextidx;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

 

eg2：

 

mysql> DROP INDEX spatidx ON t8;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 

查看是否删除成功

mysql> SHOW CREATE TABLE  t8 ;
+-------+-----------------------------------------------------------------------------------+
| Table | Create Table                                                                      |
+-------+-----------------------------------------------------------------------------------+
| t8    | CREATE TABLE `t8` (
  `space` geometry NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------+
1 row in set