2.4 MySQL索引管理

索引的类型

INDEX                普通索引
UNIQUE                唯一索引
FULLTEXT                全文索引
PRIMARY   KEY            主键
FOREIGN   KEY            外键

INDEX普通索引

使用说明

一个表中可以有多个INDEX字段
字段的值允许有重复，且可以赋空值
把查询频率高的字段设置为INDEX字段
INDEX字段的KEY标志是MUL

建表的时候指定索引字段
    INDEX（字段1），INDEX（字段2），INDEX（字段3）….

mysql> create table db1.t4( name char(10) not null, age tinyint unsigned not null, gender enum("man","woman","no") default "no", index(name),index(age) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t4;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | char(10)                 | NO   | MUL | NULL    |       |
| age    | tinyint(3) unsigned      | NO   | MUL | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

在已有的表中设置INDEX字段
    CREATE      INDEX   索引名     ON    表名(字段名);

mysql> create index cc on t2(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  | MUL | NULL    |       |
| gender | char(10) | YES  |     | NULL    |       |
| hobby  | char(10) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除索引信息
删除指定表的索引字段
    DROP    INDEX    索引名    ON    表名；
当有索引名字的时候删除索引名字
没有索引名字删除索引字段名（默认索引名与字段名相同）


mysql> drop index cc on t2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> drop index name on t4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t4;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | char(10)                 | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned      | NO   | MUL | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查看索引信息

查看表的索引信息
    SHOW    INDEX    FROM   表名\G；
mysql> show index from t4\G
*************************** 1. row ***************************
        Table: t4
   Non_unique: 1
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.01 sec)

mysql> create index cc on t2(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t2\G
*************************** 1. row ***************************
Table: t2
Non_unique: 1
Key_name: cc
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

主键

注意事项
    一个表中只能有一个primary key字段
    对应的字段值不允许有重复，且不允许赋NULL值
    主键字段的KEY标志是PRI
    primary  key通常与AUTO_INCREMENT连用
    当需要删除primary key时，须先删除AUTO_INCREMENT
    经常把表中能够唯一标识记录的字段设置为主键字段，如ID字段

SQL AUTO INCREMENT 字段
Auto-increment 会在新记录插入表中时生成一个唯一的数字。

AUTO INCREMENT 字段
    我们通常希望在每次插入新记录时，自动地创建主键字段的值。
    我们可以在表中创建一个 auto-increment 字段。    

    MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
    默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1

创建主键

建表时指定PRIMARY KEY 字段
    PRIMARY KEY（字段名）；
mysql> create table db1.t5( name char(10) not null, age tinyint unsigned not null, gender enum("man","woman","no") default "no", index(name),index(age), primary key (age) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t5;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | char(10)                 | NO   | MUL | NULL    |       |
| age    | tinyint(3) unsigned      | NO   | PRI | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

在已有的表中设置primary key 字段
    ALTER    TABLE    表名    ADD    PRIMARY KEY（字段名);
在给表字段添加主键时如果该字段的值有重复或空值，不允许添加
mysql> desc tt;
+--------+------------------------------------+------+-----+---------+-------+
| Field  | Type                               | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| name   | varchar(7)                         | YES  |     | NULL    |       |
| gender | enum('man','woman')                | YES  |     | NULL    |       |
| hobby  | set('woman','game','movie','char') | YES  |     | NULL    |       |
+--------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tt add primary key (name);
ERROR 1138 (22004): Invalid use of NULL value



mysql> create table test( name varchar(4) not null, age int(4) );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test add primary key (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test add primary key (age);
ERROR 1068 (42000): Multiple primary key defined

移除表中的PRIMARY KEY 字段
    ALTER    TABLE    表名    DROP    PRIMARY KEY；
删除主键后，NULL字段不会自动恢复成 YES

mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | NO   | PRI | NULL    |       |
| age   | int(4)     | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | NO   |     | NULL    |       |
| age   | int(4)     | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

AOTU_INCREMENT 自增长

建表时创建PRIMARY  KEY 主键字段与 AUTO_INCREMENT 自增长连用
AUTO_INCREMENT必须用在主键字段且字段类型必须是数值类型

mysql> create table t9(
    -> id int auto_increment,
    -> name char(10) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t9;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

在已有的表中添加AUTO_INCREMENT
mysql> alter table date 
    -> add
    -> id int primary key auto_increment
    -> first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc date;
+---------+----------------------------------+------+-----+---------+----------------+
| Field   | Type                             | Null | Key | Default | Extra          |
+---------+----------------------------------+------+-----+---------+----------------+
| id      | int(11)                          | NO   | PRI | NULL    | auto_increment |
| name    | char(10)                         | YES  |     | NULL    |                |
| gender  | enum('man','woman')              | YES  |     | NULL    |                |
| height  | float                            | YES  |     | NULL    |                |
| weight  | tinyint(3) unsigned              | YES  |     | NULL    |                |
| bust    | char(3)                          | YES  |     | NULL    |                |
| hobby   | set('man','game','movie','chat') | YES  |     | NULL    |                |
| address | varchar(100)                     | YES  |     | NULL    |                |
| years   | year(4)                          | YES  |     | NULL    |                |
| dates   | date                             | YES  |     | NULL    |                |
| times   | time                             | YES  |     | NULL    |                |
+---------+----------------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

外键

什么时外键？
    用来在两个表的数据之间建立链接
    让当前表字段的值在另一个表中字段值的范围内选择

使用外键的条件
    表的存储引擎必须是 innodb 类型
    
    被参照表字段类型宽度要一致段必须是索引类型的一种primary key

基本用法
    FOREIGN KEY （表A字段名）
    REFERENCES  表B （字段名）
    ON UPDATE CASCADE        //同步更新
    ON DELETE  CASCADE        //同步删除
        

 

mysql> create table 工资表(姓名 varchar(7),工资 int(3),foreign key(姓名) references 员工信息表(姓名));


mysql> create table 工资表(姓名 varchar(7),工资 int(3),foreign key(姓名) references 员工信息表(姓名) on update cascade  on delete cascade) engine=innodb,default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

同步更新：
update 员工信息表 set 姓名="王昭君" where 姓名="甄姬";
同步删除：
mysql> delete from 员工信息表 where 姓名="王昭君";
Query OK, 1 row affected (0.00 sec)

 

删除外键
使用show create table 表名，查看外键名字
mysql> alter table 工资表3 drop foreign key 工资表3_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0