索引：
为了加快查找速度
普通索引: index
唯一索引: unique index
维一索引可以有多个
主键索引: primary key 不能重复
主键必定要维一
一张表上只有一个主键
全文索引: fulltext index
查看索引:
show index from table_name;
建立索引：
alter table table_name add index/unique/primary key/fulltex 索引名(列名) #索引名可省略， 不加时默认为当前列名
建立主键:
alter table table_name add primary key (列名);
删除索引:
alter table table_name drop index 索引名;
删除主键：
alter table table_name drop primary key;
全文索引:
alter table table_name add fulltext (列名)
全文索引用法:
select * from table_name match(索引名) against('关键字');
注意， 如果关键字太常见， 索引不是查找， 会认为是停止词。
mysql> create table id(id int, name text);
Query OK, 0 rows affected (0.04 sec)
mysql> show index from id;
Empty set (0.00 sec)
mysql> alter table id add index id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table id add index (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from id;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| id | 1 | id | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show index from id\G
*************************** 1. row ***************************
Table: id
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql> alter table id add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table id add primary key (id);
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table id drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from id;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| id | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table id drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from id;
Empty set (0.00 sec)
--------------------------下面是全文索引的例子-------------------------------
mysql> create table text(id int primary key, `in` text);
Query OK, 0 rows affected (0.04 sec)
mysql> desc text;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| in | text | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into text('i love perl6 very much!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''i love perl6 very much!')' at line 1
mysql> insert into text values ('i love perl6 very much!');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into text values (1,'i love perl6 very much!');
Query OK, 1 row affected (0.00 sec)
mysql> insert into text values (2,'www.0668sec.info');
Query OK, 1 row affected (0.00 sec)
mysql> select * from text;
+----+-------------------------+
| id | in |
+----+-------------------------+
| 1 | i love perl6 very much! |
| 2 | www.0668sec.info |
+----+-------------------------+
2 rows in set (0.00 sec)
mysql> alter table text add fulltext (id);
ERROR 1283 (HY000): Column 'id' cannot be part of FULLTEXT index
mysql> alter table text add fulltext (in);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in)' at line 1
mysql> alter table text add fulltext (`in`);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show index from text;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| text | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| text | 1 | in | 1 | in | NULL | NULL | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> select * from text match(`in`) against('sec');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match(`in`) against('sec')' at line 1
mysql> select * from text match(`in`) against('sec');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match(`in`) against('sec')' at line 1
mysql> select * from text where match(`in`) against('sec');
Empty set (0.00 sec)
mysql> select * from text where match(`in`) against('0668sec');
Empty set (0.00 sec)
mysql> select * from text where match(`in`) against('0668sec.info');
Empty set (0.00 sec)
mysql> insert into text values (2,'nubbs you team data2 metasploit');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into text values (3,'nubbs you team data2 metasploit');
Query OK, 1 row affected (0.00 sec)
mysql> select * from text where match(`in`) against('metasploit');
+----+---------------------------------+
| id | in |
+----+---------------------------------+
| 3 | nubbs you team data2 metasploit |
+----+---------------------------------+
1 row in set (0.00 sec)
