• Linux学习之MySQL主键、外键、索引

    主键

    • 主键使用规则
    • 表头不允许重复，不允许赋NULL值
    • 一个表中只能有一个 primary key表头
    • 多个表头做主键，称为复合主键，必须一起创建和删除
    • 主键标志为PRI
    • 主键通常与auto_increment连用
    • 通常把表中唯一标识记录的表头设置 为主键[行号表]
    # 1. 主键的创建、查看、删除、添加、并验证主键
# 语法1：create table 库.表(表头名 数据类型 primary key ,表头名 数据类型, ... ...)
mysql> create table db1.t35(name char(10),hz_id char(10) primary key,class char(10));
Query OK, 0 rows affected (0.50 sec)

mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 语法2：create table 库.表(表头名 数据类型,表头名 数据类型, primary key(字段名))
mysql> create table db1.t36(name char(10),hz_id char(10),class char(10),primary key(hz_id));
Query OK, 0 rows affected (0.48 sec)
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 删除主键命令格式 alter table 库名.表名 drop primary key;
mysql> alter table db1.t36 drop primary key;
Query OK, 0 rows affected (1.81 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   |     | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
# 添加主键命令格式 alter table 库.表 add primary key(表头名)
mysql> alter table db1.t36 add primary key(hz_id);
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 使用t35表验证主键约束
mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| hz_id | char(10) | NO   | PRI | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into db1.t35 values("bob","888","nsd2017");
Query OK, 1 row affected (0.10 sec)
# 空值不能插入
mysql> insert into db1.t35 values("bob",null,"nsd2017");
ERROR 1048 (23000): Column 'hz_id' cannot be null
# 重复值不可以插入
mysql> insert into db1.t35 values("bob","888","nsd2017");
ERROR 1062 (23000): Duplicate entry '888' for key 't35.PRIMARY'
mysql> select * from db1.t35;
+------+-------+---------+
| name | hz_id | class   |
+------+-------+---------+
| bob  | 888   | nsd2017 |
+------+-------+---------+
1 row in set (0.00 sec)
# 2.复合主键的使用
# 创建复合主键，表头依次为客户端IP、服务端口号、访问状态
mysql> create table db1.t39(cip varchar(15),port smallint,status enum("deny","allow"),primary key(cip,port));
Query OK, 0 rows affected (0.83 sec)
mysql> insert into db1.t39 values("1.1.1.1",22,"deny");
Query OK, 1 row affected (0.14 sec)
#IP和端口相同则报错
mysql> insert into db1.t39 values("1.1.1.1",22,"deny");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 't39.PRIMARY'
mysql> insert into db1.t39 values("1.1.1.1",80,"deny");
Query OK, 1 row affected (0.10 sec)
mysql> insert into db1.t39 values("2.1.1.1",80,"deny");
Query OK, 1 row affected (0.05 sec)
mysql> select * from db1.t39;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   80 | deny   |
| 2.1.1.1 |   80 | deny   |
+---------+------+--------+
3 rows in set (0.00 sec)
//删除重复数据
mysql> delete from db1.t39 where cip = "2.1.1.1";
Query OK, 1 row affected (0.20 sec)
mysql> select * from db1.t39;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   80 | deny   |
+---------+------+--------+
2 rows in set (0.00 sec)
# 3.auto_increment连用的效果

- 表头设置了auto_increment属性后，
- 插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值
- 要想让表头有自增长 表头必须有主键设置才可以
- 查看表结构时在 Extra（额外设置）位置显示
- 建表时 创建有auto_increment 属性的表头。实现的效果如下：
行号 姓名 班级 住址
1 bob nsd2107 bj
2 bob nsd2107 bj
3 bob nsd2107 bj
4 bob nsd2107 bj

mysql> create table db1.t38(recno int primary key auto_increment,nam char(10),grade char(7),address char(10));
Query OK, 0 rows affected (0.83 sec)

mysql> desc db1.t38;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| recno   | int      | NO   | PRI | NULL    | auto_increment |
| nam     | char(10) | YES  |     | NULL    |                |
| grade   | char(7)  | YES  |     | NULL    |                |
| address | char(10) | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 插入表记录，不给自增长表头赋值
mysql> insert into db1.t38(nam,grade,address)values("bob","nsd2017","bj");
Query OK, 1 row affected (0.11 sec)
mysql> insert into db1.t38(nam,grade,address)values("bob","nsd2017","bj");
Query OK, 1 row affected (0.13 sec)
mysql> insert into db1.t38(nam,grade,address)values("tom","nsd2017","bj");
Query OK, 1 row affected (0.07 sec)

mysql> select * from db1.t38;
+-------+------+---------+---------+
| recno | nam  | grade   | address |
+-------+------+---------+---------+
|     1 | bob  | nsd2017 | bj      |
|     2 | bob  | nsd2017 | bj      |
|     3 | tom  | nsd2017 | bj      |
+-------+------+---------+---------+
3 rows in set (0.00 sec)
# 给自拉长字段的赋值
mysql> insert into db1.t38(recno,nam,grade,address)values(5,"lucy","nsd2017","bj");
Query OK, 1 row affected (0.09 sec)
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.08 sec)
mysql> select * from db1.t38;
+-------+------+---------+---------+
| recno | nam  | grade   | address |
+-------+------+---------+---------+
|     1 | bob  | nsd2017 | bj      |
|     2 | bob  | nsd2017 | bj      |
|     3 | tom  | nsd2017 | bj      |
|     5 | lucy | nsd2017 | bj      |
|     6 | lucy | nsd2017 | bj      |
+-------+------+---------+---------+
5 rows in set (0.01 sec)
# 删除所有行
mysql> delete from db1.t38;
Query OK, 5 rows affected (0.25 sec)
# 再添加行，继续行号，而不是从1开始
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.05 sec)
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.06 sec)
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.10 sec)
mysql> select * from db1.t38;
+-------+------+---------+---------+
| recno | nam  | grade   | address |
+-------+------+---------+---------+
|     7 | lucy | nsd2017 | bj      |
|     8 | lucy | nsd2017 | bj      |
|     9 | lucy | nsd2017 | bj      |
+-------+------+---------+---------+
3 rows in set (0.00 sec)
# truncate 删除行，再添加行，从1开始
mysql> truncate table db1.t38;
Query OK, 0 rows affected (0.61 sec)
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.06 sec)
mysql> insert into db1.t38(nam,grade,address)values("lucy","nsd2017","bj");
Query OK, 1 row affected (0.10 sec)
mysql> select * from db1.t38;
+-------+------+---------+---------+
| recno | nam  | grade   | address |
+-------+------+---------+---------+
|     1 | lucy | nsd2017 | bj      |
|     2 | lucy | nsd2017 | bj      |
+-------+------+---------+---------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205

    外键

    外键使用规则：

    • 表存储引擎必须是innodb
    • 表头数据类型要一致
    • 被参照表头必须要是索引类型的一种(primary key)
      作用：
    • 插入记录时，表头值在另一个表的表头头值范围内选择
    # 1. 外键的创建、查看、删除、添加
# 创建外键命令
create table 库.表(
     表头列表，
     foreign key(表头名) 
     references 库.表(表头名)   #指定参考的表头名
     on update cascade #同步更新
     on delete cascade #同步删除
     )engine=innodb;
mysql> create table db1.yg(yg_id int primary key auto_increment,name char(16));
Query OK, 0 rows affected (0.45 sec)
mysql> create table db1.gz(gz_id int ,pay float,foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade);
Query OK, 0 rows affected (0.67 sec)
mysql> show create table db1.gz\G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int DEFAULT NULL,
  `pay` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# 删除外键
mysql> alter table db1.gz drop foreign key gz_ibfk_1;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table db1.gz;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gz    | CREATE TABLE `gz` (
  `gz_id` int DEFAULT NULL,
  `pay` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 添加外键
mysql> alter table db1.gz add foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade;
Query OK, 0 rows affected (1.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table db1.gz\G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int DEFAULT NULL,
  `pay` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# 2.验证外键
- 外键字段的值必须在参考字段范围内
- 验证同步更新(on update cascade)
- 验证同步删除(on delete cascade)
# 外键的值必须在参考表字段值范围内
mysql> insert into db1.yg(name)values("jerry"),("tom");
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into db1.gz values(1,50000);
Query OK, 1 row affected (0.06 sec)
mysql> insert into db1.gz values(2,60000);
Query OK, 1 row affected (0.38 sec)
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay   |
+-------+-------+
|     1 | 50000 |
|     2 | 60000 |
+-------+-------+
2 rows in set (0.00 sec)
# 没有3号员工，工资表插入记录报错
mysql> insert into db1.gz values(3,60000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
# 验证同步更新
mysql> update db1.yg set yg_id=9 where yg_id=2;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     9 | tom   |
+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay   |
+-------+-------+
|     1 | 50000 |
|     9 | 60000 |
+-------+-------+
2 rows in set (0.00 sec)
# 验证同步删除
mysql> delete from db1.yg where yg_id=9;
Query OK, 1 row affected (0.11 sec)
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay   |
+-------+-------+
|     1 | 50000 |
+-------+-------+
1 row in set (0.00 sec)
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
+-------+-------+
1 row in set (0.00 sec)
# 3. 外键使用注意事项 
# 被参考的表不能删除
mysql> drop table db1.yg;
ERROR 3730 (HY000): Cannot drop table 'yg' referenced by a foreign key constraint 'gz_ibfk_1' on table 'gz'.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124

    索引

    使用规则：

    • 一个表中可以有多个index
    • 任何数据类型的表头都可以设置索引
    • 表头值可以重复，也可以赋NULL值
    • 通常在where条件中的表头上设置Index
    • index索引标志MUL
    # 1. 索引的创建、查看、删除、添加
# 创建索引命令格式
CREATE TABLE  库.表(
字段列表 ,
INDEX(字段名) ,
INDEX(字段名) 
);
Create database  home;
Use home;
CREATE TABLE tea4(
id char(6),
name varchar(6),
age int(3),
gender ENUM('boy','girl') DEFAULT 'boy',
INDEX(id),INDEX(name)
);
mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   | MUL | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)              | YES |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> system ls /var/lib/mysql/home/tea4.ibd  保存排队信息的文件
/var/lib/mysql/home/tea4.ibd
# 查看索引详细信息
show  index   from  home.tea4 \G
*************************** 1. row ***************************
        Table: tea4     #表名
   Non_unique: 1
     Key_name: id   #索引名   （默认索引名和表头名相同，删除索引时，使用的索引名）
 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: tea4   #表名
   Non_unique: 1
     Key_name: name  #索引名
 Seq_in_index: 1
  Column_name: name  #表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE  #排队算法
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
# 删除索引  命令格式   DROP  INDEX   索引名   ON  库.表；
mysql> drop index id on home.tea4 ;
mysql> desc  home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   |     | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)             | YES |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.14 sec)
mysql> show  index   from  home.tea4 \G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
# 已有表添加索引命令（CREATE  INDEX  索引名  ON  库.表(字段名)；）
mysql> create  index nianling on home.tea4(age);
mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   |     | NULL    |       |
| name   | varchar(6)         | YES  | MUL | NULL    |       |
| age    | int(3)             | YES | MUL | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show  index   from  home.tea4 \G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: nianling   设置的索引名
 Seq_in_index: 1
  Column_name: age  表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
# 验证索引
mysql> desc tarena.user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | char(20)    | YES  |     | NULL    |                |
| password | char(1)     | YES  |     | NULL    |                |
| uid      | int         | YES  |     | NULL    |                |
| gid      | int         | YES  |     | NULL    |                |
| comment  | varchar(50) | YES  |     | NULL    |                |
| homedir  | varchar(80) | YES  |     | NULL    |                |
| shell    | char(30)    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
# 使用name表头做筛选条件,查找记录
mysql> select * from tarena.user where name="sshd";
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| id | name | password | uid  | gid  | comment                 | homedir         | shell         |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
| 17 | sshd | x        |   74 |   74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
+----+------+----------+------+------+-------------------------+-----------------+---------------+
1 row in set (0.00 sec)
# 验证是否使用索引
mysql> explain select * from tarena.user where name="sshd" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user  表名
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL      使用的索引名
      key_len: NULL
          ref: NULL
         rows: 27         查找的总行数
     filtered: 10.00
        Extra: Using where   额外说明
1 row in set, 1 warning (0.00 sec)

# 查看表的总行数，查找sshd 用做的是全表扫描
mysql> select count(*) from tarena.user;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.01 sec)
# 给name表头设置索引
mysql> create index  name on tarena.user(name);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tarena.user; 查看
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | char(20)    | YES  | MUL | NULL    |                |
| password | char(1)     | YES  |     | NULL    |                |
| uid      | int         | YES  |     | NULL    |                |
| gid      | int         | YES  |     | NULL    |                |
| comment  | varchar(50) | YES  |     | NULL    |                |
| homedir  | varchar(80) | YES  |     | NULL    |                |
| shell    | char(30)    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
# 验证索引
mysql> explain select  * from tarena.user where name="sshd" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user    表名
   partitions: NULL
         type: ref
possible_keys: name
          key: name  使用的索引名
      key_len: 21
          ref: const
         rows: 1     查找的总行数
     filtered: 100.00
        Extra: NULL  额外说明
1 row in set, 1 warning (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
  • 相关阅读:
    黑客8款常用工具
    计算机毕业设计选题推荐-点餐微信小程序/安卓APP-项目实战
    中国人民大学与加拿大女王大学金融硕士——投资自己，才是当下最稳健的投资
    MyBatis内容分析
    【从零开始学微服务】01.微服务的过去与现在
    22-09-01 西安 JUC（04）java内存模型JMM、volatile关键字、原子性类、CAS比较并交换、AQS锁原理
    探秘数据库中间件：ProxySQL与MaxScale的优势与劣势
    高等数学（第七版）同济大学 习题5-1 个人解答
    海康Visionmaster-全局脚本：VM 加载方案后自动执行 的方法，如何让VM在方案加载后自动执行流程？
    vue - watch:{}监听与 this.$watch()的区别
  • 原文地址：https://blog.csdn.net/xuwenpeng/article/details/132801315