0、索引类型
- 普通索引：index、key
- 唯一索引：uniqe
- 主键索引：primary key
- 全文索引：fulltext（MyISAM引擎或InnoDB引擎v5.7及以上版本支持）
1、查看当前表创建
mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(50) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`user_des` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `age` (`age`),
KEY `email` (`email`),
FULLTEXT KEY `user_des` (`user_des`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2、查看当前表索引
mysql> show index from user\G
*************************** 1. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: user
Non_unique: 0
Key_name: user_name
Seq_in_index: 1
Column_name: user_name
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: user
Non_unique: 1
Key_name: age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: user
Non_unique: 1
Key_name: email
Seq_in_index: 1
Column_name: email
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: user
Non_unique: 1
Key_name: user_des
Seq_in_index: 1
Column_name: user_des
Collation: NULL
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
5 rows in set (0.00 sec)
3、全文索引查询
- select * from user where match(xxxx) against(‘xxxx’);
mysql> select * from user where match(user_des) against('abc');
+----+-----------+----------------------------------+----------------+-----+----------+
| id | user_name | password | email | age | user_des |
+----+-----------+----------------------------------+----------------+-----+----------+
| 1 | sian | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com | 32 | abc def |
+----+-----------+----------------------------------+----------------+-----+----------+
1 row in set (0.06 sec)
4、外键约束（MyISAM不支持）
- alter table mark add foreign(user_id) references user(id)；
- 有约束相关的字段或表不能删除；
mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password | email | address | fee | age | create_at |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| 1 | sian | 男 | e10adc3949ba59abbe56e057f20f883e | yusian@163.com | NULL | 1200.00 | 32 | 2019-04-23 15:57:29 |
| 2 | 余西安 | 男 | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com | NULL | 1002.00 | 33 | 2019-04-23 21:00:03 |
| 3 | Jack | 男 | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:11:59 |
| 4 | Rose | 女 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL | 0.00 | 18 | 2019-04-22 10:46:41 |
| 5 | Sim | 女 | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com | NULL | 0.00 | 28 | 2019-04-22 10:46:41 |
| 6 | Tony | 男 | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:41:26 |
| 7 | lucy | 男 | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com | NULL | 0.00 | 0 | 2019-04-23 20:13:01 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
7 rows in set (0.00 sec)
mysql> select * from mark;
+----+------+---------+
| id | mark | user_id |
+----+------+---------+
| 1 | 90 | 1 |
| 2 | 88 | 2 |
| 4 | 92 | 4 |
| 5 | 100 | 5 |
| 6 | 77 | 6 |
| 7 | 99 | 7 |
| 11 | 44 | 3 |
+----+------+---------+
7 rows in set (0.00 sec)
mysql> insert into mark(mark, user_id) values(99, 8);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sian`.`mark`, CONSTRAINT `mark_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))
mysql> delete from user where id = 7;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sian`.`mark`, CONSTRAINT `mark_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))
mysql>