语句
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` varchar(16) NOT NULL,
`score` int(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
增加主键
alter table student change id id int primary key auto_increment ;
一般不需要修改主键,但有时建表的时候忘记了加,要后补就可以这样写
格式是alter table change 字段属性,这里的auto_increment也是后补上去的。
mysql> desc mydb.student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| score | int(1) | YES | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
添加索引
mysql> alter table mydb.student add index name(name);
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mydb.student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | MUL | NULL | |
| score | int(1) | YES | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
那个name(name)其实是索引名(字段名),索引名字可以自行定义。成功后,在desc里可以看到key显示MUL。
删除索引
alter table mydb.student drop index name;
这里的name是索引的名字
查看索引
show index from mydb.student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
创建部分内容的索引
有些内容比较长,但其中前几个字符已经是唯一的情况下,即可使用这几个字符形成索引。
例如使用name的前4个字符形成索引
mysql> create index index_name on mydb.student (name(4));
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mydb.student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | MUL | NULL | |
| score | int(1) | YES | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show index from mydb.student\G;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
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: 4
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
ERROR:
No query specified
看2. Row的Sub_part
创建联合索引
在上面索引的基础上,输入多个字段名用以创建一个联合索引
例如
mysql> create index index_id_name on mydb.student (id,name(4));
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from mydb.student \G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
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: 4
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_id_name
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: index_id_name
Seq_in_index: 2
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 4
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
4 rows in set (0.00 sec)
可以看到第3、4行的key_name是一样的
删除联合索引也是一样用索引名字即可
mysql> drop index index_id_name on mydb.student;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建单一索引,在关键字index前加unique关键字
mysql> create unique index index_id_name on mydb.student (id,name(4));
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0