mysql学习笔记

语句

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

关于索引类型，参考http://blog.sina.com.cn/s/blog_6fd335bb0100v1lm.html

创建联合索引

在上面索引的基础上，输入多个字段名用以创建一个联合索引

例如

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