mysql应用管理
mysql> desc student;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name| varchar(25) | YES| | NULL | |
| age | tinyint(2)| NO | | 0 | |
| dept| varchar(16) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> create index index_name on student(name); #在student表name字段建索引index_name
Query OK, 0 rows affected (0.07 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> create unique index index_id_nameon student(id);#创建唯一索引index_id在id上
Query OK, 0 rows affected (0.05 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name| varchar(25) | YES| MUL | NULL | |
| age | tinyint(2)| NO | | 0 | |
| dept| varchar(16) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student add index index_age_name(age); #使用alter建立索引
Query OK, 0 rows affected (0.05 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> drop index index_age_name on student; #删除索引index_age_name
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> show index from student\G #查看索引
*************************** 1. row ***************************
Table: student #表名
Non_unique: 0 #s如果索引不能包含重复数据为0，如果可以则为1
Key_name: index_id_name #索引名称
Seq_in_index: 1 #索引的列序列号
Column_name: id #列名称
Collation: A #列以什么方式存储在索引中，A表示升序，NULL表示无分类
Cardinality: 0 #
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE #索引类型，可以是BTREE，FULLTEXT，HASH或者RTREE
Comment: #注释
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> alter table student drop index index_name; #使用alter删除索引。
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
页:
[1]