主键、外键、建表范式、MySQL索引、用户管理

Top

NSD DBA DAY05

1. 案例1：主键
2. 案例2：外键
3. 案例3：MySQL索引
4. 案例4：用户管理

1 案例1：主键

1.1 问题

完成如下练习：

1. 练习主键的创建、查看、删除、添加、验证主键
2. 练习复合主键的使用
3. 练习与auto_increment连用的效果

1.2 方案

主键使用规则：

• 表头值不允许重复，不允许赋NULL值
• 一个表中只能有一个primary key 表头
• 多个表头做主键，称为复合主键，必须一起创建和删除
• 主键标志PRI
• 主键通常与auto_increment连用
• 通常把表中唯一标识记录的表头设置为主键[行号表]

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习主键的创建、查看、删除、添加、验证主键

1. //语法格式1
2. create table 库.表（ 表头名 数据类型 primary key , 表头名 数据类型 , ..... ）;
3. //建表
4. mysql> create table db1.t35(
5. -> name char(10) ,
6. -> hz_id char(10) primary key ,
7. -> class char(10)
8. -> );
9. Query OK, 0 rows affected (0.49 sec)
10. //查看表头
11. mysql> desc db1.t35;
12. +-------+----------+------+-----+---------+-------+
13. | Field | Type | Null | Key | Default | Extra |
14. +-------+----------+------+-----+---------+-------+
15. | name | char(10) | YES | | NULL | |
16. | hz_id | char(10) | NO | PRI | NULL | |
17. | class | char(10) | YES | | NULL | |
18. +-------+----------+------+-----+---------+-------+
19. 3 rows in set (0.00 sec)
20. //语法格式2
21. create table 库.表（ 字段名 类型 , 字段名 类型 , primary key（字段名） ）;
22. //建表
23. mysql> create table db1.t36(
24. -> name char(10) ,
25. -> hz_id char(10) ,
26. -> class char(10),
27. -> primary key(hz_id)
28. -> );
29. Query OK, 0 rows affected (0.39 sec)
30. //查看表头
31. mysql> desc db1.t36;
32. +-------+----------+------+-----+---------+-------+
33. | Field | Type | Null | Key | Default | Extra |
34. +-------+----------+------+-----+---------+-------+
35. | name | char(10) | YES | | NULL | |
36. | hz_id | char(10) | NO | PRI | NULL | |
37. | class | char(10) | YES | | NULL | |
38. +-------+----------+------+-----+---------+-------+
39. 3 rows in set (0.00 sec)
40. //删除主键命令格式
41. mysql> alter table 库.表 drop primary key ;
42. //例子
43. mysql> alter table db1.t36 drop primary key ;
44. Query OK, 0 rows affected (1.00 sec)
45. Records: 0 Duplicates: 0 Warnings: 0
46. //查看表头
47. mysql> desc db1.t36;
48. +-------+----------+------+-----+---------+-------+
49. | Field | Type | Null | Key | Default | Extra |
50. +-------+----------+------+-----+---------+-------+
51. | name | char(10) | YES | | NULL | |
52. | hz_id | char(10) | NO | | NULL | |
53. | class | char(10) | YES | | NULL | |
54. +-------+----------+------+-----+---------+-------+
55. 3 rows in set (0.00 sec)
56. mysql>
57. //添加主键命令格式
58. mysql> alter table 库.表 add primary key(表头名);
59. //例子
60. mysql> alter table db1.t36 add primary key(hz_id);
61. mysql> desc db1.t36;
62. +-------+----------+------+-----+---------+-------+
63. | Field | Type | Null | Key | Default | Extra |
64. +-------+----------+------+-----+---------+-------+
65. | name | char(10) | YES | | NULL | |
66. | hz_id | char(10) | NO | PRI | NULL | |
67. | class | char(10) | YES | | NULL | |
68. +-------+----------+------+-----+---------+-------+
69. 3 rows in set (0.00 sec)
70. //使用t35表 验证主键约束
71. //查看主键表头
72. mysql> desc db1.t35;
73. +-------+----------+------+-----+---------+-------+
74. | Field | Type | Null | Key | Default | Extra |
75. +-------+----------+------+-----+---------+-------+
76. | name | char(10) | YES | | NULL | |
77. | hz_id | char(10) | NO | PRI | NULL | |
78. | class | char(10) | YES | | NULL | |
79. +-------+----------+------+-----+---------+-------+
80. 3 rows in set (0.00 sec)
81. //插入第1条记录 正常
82. mysql> insert into db1.t35 values ("bob","888","nsd2107");
83. Query OK, 1 row affected (0.05 sec)
84. //空不可以
85. mysql> insert into db1.t35 values ("john",null,"nsd2107");
86. ERROR 1048 (23000): Column 'hz_id' cannot be null
87. mysql>
88. //与第1条重复不可以
89. mysql> insert into db1.t35 values ("john","888","nsd2107");
90. ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'
91. //不重复也不是null可以
92. mysql> insert into db1.t35 values ("john","988","nsd2107");
93. Query OK, 1 row affected (0.07 sec)
94. //查看表记录
95. mysql> select * from db1.t35 ;
96. +------+-------+---------+
97. | name | hz_id | class |
98. +------+-------+---------+
99. | bob | 888 | nsd2107 |
100. | john | 988 | nsd2107 |
101. +------+-------+---------+
102. 2 rows in set (0.00 sec)

步骤二：练习复合主键的使用

1. //创建复合主键 表头依次是客户端ip 、服务端口号、访问状态
2. mysql> create table db1.t39(
3. cip varchar(15) ,
4. port smallint ,
5. status enum("deny","allow") ,
6. primary key(cip,port)
7. );
8. //插入记录验证
9. insert into db1.t39 values ("1.1.1.1",22,"deny");
10. insert into db1.t39 values ("1.1.1.1",22,"deny"); 同时相同报错
11. insert into db1.t39 values ("1.1.1.1",80,"deny"); 可以
12. insert into db1.t39 values ("2.1.1.1",80,"allow");可以
13. //查看记录
14. mysql> select * from db1.t39;
15. +---------+------+--------+
16. | cip | port | status |
17. +---------+------+--------+
18. | 1.1.1.1 | 22 | deny |
19. | 1.1.1.1 | 80 | deny |
20. | 2.1.1.1 | 80 | allow |
21. +---------+------+--------+
22. 3 rows in set (0.00 sec)
23. //删除复合主键
24. mysql> alter table db1.t39 drop primary key;
25. Query OK, 3 rows affected (1.10 sec)
26. Records: 3 Duplicates: 0 Warnings: 0
27. //查看表头
28. mysql> desc db1.t39;
29. +--------+----------------------+------+-----+---------+-------+
30. | Field | Type | Null | Key | Default | Extra |
31. +--------+----------------------+------+-----+---------+-------+
32. | cip | varchar(15) | NO | | NULL | |
33. | port | smallint | NO | | NULL | |
34. | status | enum('deny','allow') | YES | | NULL | |
35. +--------+----------------------+------+-----+---------+-------+
36. 3 rows in set (0.00 sec)
37. //没有复合主键约束后 ，插入记录不受限制了
38. mysql> insert into db1.t39 values("2.1.1.1",80,"allow");
39. Query OK, 1 row affected (0.06 sec)
40. mysql> insert into db1.t39 values("2.1.1.1",80,"deny");
41. Query OK, 1 row affected (0.08 sec)
42. //查看表记录
43. mysql> select * from db1.t39;
44. +---------+------+--------+
45. | cip | port | status |
46. +---------+------+--------+
47. | 1.1.1.1 | 22 | deny |
48. | 1.1.1.1 | 80 | deny |
49. | 2.1.1.1 | 80 | allow |
50. | 2.1.1.1 | 80 | allow |
51. | 2.1.1.1 | 80 | deny |
52. +---------+------+--------+
53. 5 rows in set (0.00 sec)
54. //添加复合主键时 字段下的数据与主键约束冲突 不允许添加
55. mysql> alter table db1.t39 add primary key(cip,port);
56. ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key 't39.PRIMARY'
57. //删除重复的数据
58. mysql> delete from db1.t39 where cip="2.1.1.1";
59. Query OK, 3 rows affected (0.05 sec)
60. mysql> select * from db1.t39;
61. +---------+------+--------+
62. | cip | port | status |
63. +---------+------+--------+
64. | 1.1.1.1 | 22 | deny |
65. | 1.1.1.1 | 80 | deny |
66. +---------+------+--------+
67. 2 rows in set (0.00 sec)
68. //添加复合主键
69. mysql> alter table db1.t39 add primary key(cip,port);
70. Query OK, 0 rows affected (0.67 sec)
71. Records: 0 Duplicates: 0 Warnings: 0
72. //查看表头
73. mysql> desc db1.t39;
74. +--------+----------------------+------+-----+---------+-------+
75. | Field | Type | Null | Key | Default | Extra |
76. +--------+----------------------+------+-----+---------+-------+
77. | cip | varchar(15) | NO | PRI | NULL | |
78. | port | smallint | NO | PRI | NULL | |
79. | status | enum('deny','allow') | YES | | NULL | |
80. +--------+----------------------+------+-----+---------+-------+
81. 3 rows in set (0.00 sec)

步骤三：练习与auto_increment连用的效果

表头设置了auto_increment属性后，

插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值

要想让表头有自增长 表头必须有主键设置才可以

查看表结构时 在 Extra （额外设置） 位置显示

建表时 创建有auto_increment 属性的表头。实现的效果如下：

行号 姓名 班级 住址

1 bob nsd2107 bj

2 bob nsd2107 bj

3 bob nsd2107 bj

4 bob nsd2107 bj

1）建表

1. mysql> create table db1.t38 (
2. -> 行号 int primary key auto_increment ,
3. -> 姓名 char(10) ,
4. -> 班级 char(7) ,
5. -> 住址 char(10)
6. -> );
7. Query OK, 0 rows affected (0.76 sec)
8. //查看表头
9. mysql> desc db1.t38 ;
10. +--------+----------+------+-----+---------+----------------+
11. | Field | Type | Null | Key | Default | Extra |
12. +--------+----------+------+-----+---------+----------------+
13. | 行号 | int | NO | PRI | NULL | auto_increment |
14. | 姓名 | char(10) | YES | | NULL | |
15. | 班级 | char(7) | YES | | NULL | |
16. | 住址 | char(10) | YES | | NULL | |
17. +--------+----------+------+-----+---------+----------------+
18. 4 rows in set (0.00 sec)
19. //插入表记录 不给自增长表头赋值
20. mysql> insert into db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
21. Query OK, 1 row affected (0.05 sec)
22. mysql> insert into db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
23. Query OK, 1 row affected (0.04 sec)
24. mysql> insert into db1.t38(姓名,班级,住址)values("tom","nsd2107","bj");
25. Query OK, 1 row affected (0.05 sec)
26. //查看表记录
27. mysql> select * from db1.t38;
28. +--------+--------+---------+--------+
29. | 行号 | 姓名 | 班级 | 住址 |
30. +--------+--------+---------+--------+
31. | 1 | bob | nsd2107 | bj |
32. | 2 | bob | nsd2107 | bj |
33. | 3 | tom | nsd2107 | bj |
34. +--------+--------+---------+--------+
35. 3 rows in set (0.00 sec)

自增长使用注意事项

1. //给自增长字段的赋值
2. mysql> insert into db1.t38(行号,姓名,班级,住址)values(5,"lucy","nsd2107","bj");
3. Query OK, 1 row affected (0.26 sec)
4. //不赋值后 用最后1条件记录表头的值+1结果赋值
5. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
6. Query OK, 1 row affected (0.03 sec)
7. //查看记录
8. mysql> select * from db1.t38 ;
9. +--------+--------+---------+--------+
10. | 行号 | 姓名 | 班级 | 住址 |
11. +--------+--------+---------+--------+
12. | 1 | bob | nsd2107 | bj |
13. | 2 | bob | nsd2107 | bj |
14. | 3 | tom | nsd2107 | bj |
15. | 5 | lucy | nsd2107 | bj |
16. | 6 | lucy | nsd2107 | bj |
17. +--------+--------+---------+--------+
18. 5 rows in set (0.00 sec)
19. //删除所有行
20. mysql> delete from db1.t38 ；
21. //再添加行 继续行号 而不是从 1 开始
22. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
23. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
24. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
25. //查看记录
26. mysql> select * from db1.t38;
27. +--------+--------+---------+--------+
28. | 行号 | 姓名 | 班级 | 住址 |
29. +--------+--------+---------+--------+
30. | 8 | lucy | nsd2107 | bj |
31. | 9 | lucy | nsd2107 | bj |
32. | 10 | lucy | nsd2107 | bj |
33. +--------+--------+---------+--------+
34. 3 rows in set (0.01 sec)
35. //truncate删除行 再添加行 从1开始
36. mysql> truncate table db1.t38;
37. Query OK, 0 rows affected (2.66 sec)
38. //插入记录
39. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
40. Query OK, 1 row affected (0.04 sec)
41. mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
42. Query OK, 1 row affected (0.30 sec)
43. //查看记录
44. mysql> select * from db1.t38;
45. +--------+--------+---------+--------+
46. | 行号 | 姓名 | 班级 | 住址 |
47. +--------+--------+---------+--------+
48. | 1 | lucy | nsd2107 | bj |
49. | 2 | lucy | nsd2107 | bj |
50. +--------+--------+---------+--------+
51. 2 rows in set (0.01 sec)
52. mysql>

2 案例2：外键

2.1 问题

完成如下练习：

1. 练习外键的创建、查看、删除、添加
2. 验证外键功能

2.2 方案

外键使用规则：

• 表存储引擎必须是innodb
• 表头数据类型要一致
• 被参照表头必须要是索引类型的一种(primary key)

作用：

• 插入记录时，表头值在另一个表的表头值范围内选择。

2.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习外键的创建、查看、删除、添加

//创建外键命令

1. create table 库.表(
2. 表头列表 ,
3. foreign key(表头名) #指定外键
4. references 库.表(表头名) #指定参考的表头名
5. on update cascade #同步更新
6. on delete cascade #同步删除
7. )engine=innodb;

需求： 仅给公司里已经入职的员工发工资

首先创建存储员工信息的员工表

表名 yg

员工编号 yg_id

姓名 name

1. #创建员工表
2. create table db1.yg (
3. yg_id int primary key auto_increment ,
4. name char(16)
5. ) engine=innodb;

创建工资表

表名 gz

员工编号 gz_id

工资 pay

1. #创建工资表 指定外键表头
2. mysql> create table db1.gz(
3. gz_id int , pay float,
4. foreign key(gz_id) references db1.yg(yg_id)
5. on update cascade on delete cascade
6. )engine=innodb ;
7. //查看工资表外键
8. mysql> show create table db1.gz \G
9. *************************** 1. row ***************************
10. Table: gz
11. Create Table: CREATE TABLE `gz` (
12. `gz_id` int(11) DEFAULT NULL,
13. `pay` float DEFAULT NULL,
14. KEY `gz_id` (`gz_id`),
15. CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
17. //删除外键
18. mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;
19. //查看不到外键
20. mysql> show create table db1.gz \G
21. *************************** 1. row ***************************
22. Table: gz
23. Create Table: CREATE TABLE `gz` (
24. `gz_id` int(11) DEFAULT NULL,
25. `pay` float DEFAULT NULL,
26. KEY `gz_id` (`gz_id`)
27. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
28. 1 row in set (0.00 sec)
29. mysql>
30. //添加外键
31. mysql> alter table db1.gz
32. add foreign key(gz_id) references db1.yg(yg_id)
33. on update cascade on delete cascade ;
34. //查看外键
35. mysql> show create table db1.gz \G
36. *************************** 1. row ***************************
37. Table: gz
38. Create Table: CREATE TABLE `gz` (
39. `gz_id` int(11) DEFAULT NULL,
40. `pay` float DEFAULT NULL,
41. KEY `gz_id` (`gz_id`),
42. CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
43. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
44. 1 row in set (0.00 sec)

步骤二：验证外键功能

1. 外键字段的值必须在参考表字段值范围内
2. 验证同步更新（ on update cascade)
3. 验证同步删除（ on delete cascade)

1)、外键字段的值必须在参考表字段值范围内

1. 员工表插入记录
2. mysql> insert into db1.yg (name) values ("jerry"),("tom");
3. mysql> select * from db1.yg;
5. 工资表插入记录
6. mysql> insert into db1.gz values(1,50000);
7. mysql> insert into db1.gz values(2,60000);
8. mysql> select * from db1.gz;
9. +-------+----------+
10. | gz_id | pay |
11. +-------+----------+
12. | 1 | 50000 |
13. | 2 | 60000 |
14. +-------+----------+
15. 2 rows in set (0.00 sec)
17. #没有的3号员工 工资表插入记录报错
18. mysql> insert into db1.gz values(3,50000);
19. 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)
21. 员工表 插入编号3的员工
22. mysql> insert into db1.yg (name) values ("Lucy");
23. mysql> select * from db1.yg;
25. 可以给3号员工 发工资了
26. mysql> insert into db1.gz values(3,40000);    

2)、验证同步更新（ on update cascade)

1. 查看员工表记录
2. mysql> select * from db1.yg;
3. +-------+-------+
4. | yg_id | name |
5. +-------+-------+
6. | 1 | jerry |
7. | 2 | tom |
8. | 3 | lucy |
9. +-------+-------+
10. 3 rows in set (0.00 sec)
12. 把yg表里编号是3的改成9
13. mysql> update db1.yg set yg_id=9 where yg_id=3;
14. mysql> select * from db1.yg;
15. +-------+-------+
16. | yg_id | name |
17. +-------+-------+
18. | 1 | jerry |
19. | 2 | tom |
20. | 9 | lucy |
21. +-------+-------+
22. 3 rows in set (0.00 sec)
24. 工资表里编号是3的自动变成 9
25. mysql> select * from db1.gz;
26. +-------+----------+
27. | gz_id | pay |
28. +-------+----------+
29. | 1 | 50000.00 |
30. | 2 | 60000.00 |
31. | 9 | 40000.00 |
32. +-------+----------+
33. 3 rows in set (0.00 sec)
35. mysql>

3)、验证同步删除（ on delete cascade)

1. 删除前查看员工表记录
2. mysql> select * from db1.yg;
3. +-------+-------+
4. | yg_id | name |
5. +-------+-------+
6. | 1 | jerry |
7. | 2 | tom |
8. | 9 | lucy |
9. +-------+-------+
10. 3 rows in set (0.00 sec)
12. 删除编号2的员工
13. mysql> delete from db1.yg where yg_id=2;
14. Query OK, 1 row affected (0.04 sec)
16. 删除后查看
17. mysql> select * from db1.yg;
18. +-------+-------+
19. | yg_id | name |
20. +-------+-------+
21. | 1 | jerry |
22. | 9 | lucy |
23. +-------+-------+
24. 2 rows in set (0.00 sec)
26. 查看工资表也没有编号2的工资了
27. mysql> select * from db1.gz;
28. +-------+----------+
29. | gz_id | pay |
30. +-------+----------+
31. | 1 | 50000.00 |
32. | 9 | 40000.00 |
33. +-------+----------+
34. 2 rows in set (0.00 sec)
36. mysql>

4)、外键使用注意事项

1. #被参考的表不能删除
2. mysql> drop table db1.yg;
3. ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
4. mysql>
6. 给gz表的gz_id表头 加主键标签
7. 保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
9. # 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键
10. delete form db1.gz;
11. alter table db1.gz add primary key(gz_id);
13. 保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
14. mysql> insert into db1.gz values (1,53000); 报错
15. mysql> insert into db1.gz values (9,58000); 报错
16. mysql> insert into db1.gz values (NULL,80000); 报错

3 案例3：MySQL索引

3.1 问题

完成如下练习：

1. 练习索引的创建、查看、删除、添加
2. 验证索引

3.2 方案

使用规则：

• 一个表中可以有多个index
• 任何数据类型的表头都可以设置索引
• 表头值可以重复，也可以赋NULL值
• 通常在where条件中的表头上设置Index
• index索引标志MUL

3.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习索引的创建、查看、删除、添加

1)建表时创建索引命令格式

1. CREATE TABLE 库.表(
2. 字段列表 ,
3. INDEX(字段名) ,
4. INDEX(字段名)
5. );

例子

1. Create database home;
2. Use home;
3. CREATE TABLE tea4(
4. id char(6),
5. name varchar(6),
6. age int(3),
7. gender ENUM('boy','girl') DEFAULT 'boy',
8. INDEX(id),INDEX(name)
9. );

查看索引

1. 查看表头是否有索引
2. des 库.表；

例子

1. mysql> desc home.tea4;
2. +--------+--------------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +--------+--------------------+------+-----+---------+-------+
5. | id | char(6) | YES | MUL | NULL | |
6. | name | varchar(6) | YES | MUL | NULL | |
7. | age | int(3) | YES | | NULL | |
8. | gender | enum('boy','girl') | YES | | boy | |
9. +--------+--------------------+------+-----+---------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> system ls /var/lib/mysql/home/tea4.ibd 保存排队信息的文件
13. /var/lib/mysql/home/tea4.ibd
14. mysql>

查看索引详细信息

1. show index from 库.表；

例子

1. show index from home.tea4 \G
2. *************************** 1. row ***************************
3. Table: tea4 #表名
4. Non_unique: 1
5. Key_name: id #索引名 （默认索引名和表头名相同，删除索引时，使用的索引名）
6. Seq_in_index: 1
7. Column_name: id #表头名
8. Collation: A
9. Cardinality: 0
10. Sub_part: NULL
11. Packed: NULL
12. Null:
13. Index_type: BTREE #索引类型
14. Comment:
15. Index_comment:
16. *************************** 2. row ***************************
17. Table: tea4 #表名
18. Non_unique: 1
19. Key_name: name #索引名
20. Seq_in_index: 1
21. Column_name: name #表头名
22. Collation: A
23. Cardinality: 0
24. Sub_part: NULL
25. Packed: NULL
26. Null:
27. Index_type: BTREE #排队算法
28. Comment:
29. Index_comment:
30. 2 rows in set (0.00 sec)
32. mysql>

删除索引

1. 命令格式 DROP INDEX 索引名 ON 库.表；

例子

1. mysql> drop index id on home.tea4 ;
2. mysql> desc home.tea4;
3. +--------+--------------------+------+-----+---------+-------+
4. | Field | Type | Null | Key | Default | Extra |
5. +--------+--------------------+------+-----+---------+-------+
6. | id | char(6) | YES | | NULL | |
7. | name | varchar(6) | YES | MUL | NULL | |
8. | age | int(3) | YES | | NULL | |
9. | gender | enum('boy','girl') | YES | | boy | |
10. +--------+--------------------+------+-----+---------+-------+
11. 4 rows in set (0.14 sec)
12. mysql> show index from home.tea4 \G
13. *************************** 1. row ***************************
14. Table: tea4
15. Non_unique: 1
16. Key_name: name
17. Seq_in_index: 1
18. Column_name: name
19. Collation: A
20. Cardinality: 0
21. Sub_part: NULL
22. Packed: NULL
23. Null:
24. Index_type: BTREE
25. Comment:
26. Index_comment:
27. 1 row in set (0.00 sec)
28. mysql>

已有表添加索引命令

1. CREATE INDEX 索引名 ON 库.表(字段名)；

例子

1. mysql> create index nianling on home.tea4(age);
2. mysql> desc home.tea4;
3. +--------+--------------------+------+-----+---------+-------+
4. | Field | Type | Null | Key | Default | Extra |
5. +--------+--------------------+------+-----+---------+-------+
6. | id | char(6) | YES | | NULL | |
7. | name | varchar(6) | YES | MUL | NULL | |
8. | age | int(3) | YES | MUL | NULL | |
9. | gender | enum('boy','girl') | YES | | boy | |
10. +--------+--------------------+------+-----+---------+-------+
11. 4 rows in set (0.00 sec)
12. mysql> show index from home.tea4 \G
13. *************************** 1. row ***************************
14. Table: tea4
15. Non_unique: 1
16. Key_name: name
17. Seq_in_index: 1
18. Column_name: name
19. Collation: A
20. Cardinality: 0
21. Sub_part: NULL
22. Packed: NULL
23. Null:
24. Index_type: BTREE
25. Comment:
26. Index_comment:
27. *************************** 2. row ***************************
28. Table: tea4
29. Non_unique: 1
30. Key_name: nianling 设置的索引名
31. Seq_in_index: 1
32. Column_name: age 表头名
33. Collation: A
34. Cardinality: 0
35. Sub_part: NULL
36. Packed: NULL
37. Null:
38. Index_type: BTREE
39. Comment:
40. Index_comment:
41. 2 rows in set (0.00 sec)
42. mysql>

步骤二：验证索引

1. mysql> desc tarena.user;
2. +----------+-------------+------+-----+---------+----------------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+-------------+------+-----+---------+----------------+
5. | id | int | NO | PRI | NULL | auto_increment |
6. | name | char(20) | YES | | NULL | |
7. | password | char(1) | YES | | NULL | |
8. | uid | int | YES | | NULL | |
9. | gid | int | YES | | NULL | |
10. | comment | varchar(50) | YES | | NULL | |
11. | homedir | varchar(80) | YES | | NULL | |
12. | shell | char(30) | YES | | NULL | |
13. +----------+-------------+------+-----+---------+----------------+
14. 8 rows in set (0.00 sec)

2）使用name表头做筛选条件,查找记录

1. mysql> select * from tarena.user where name="sshd";
2. +----+------+----------+------+------+-------------------------+-----------------+---------------+
3. | id | name | password | uid | gid | comment | homedir | shell |
4. +----+------+----------+------+------+-------------------------+-----------------+---------------+
5. | 17 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
6. +----+------+----------+------+------+-------------------------+-----------------+---------------+
7. 1 row in set (0.00 sec)

3）验证是否使用索引

1. mysql> explain select * from tarena.user where name="sshd" \G
2. *************************** 1. row ***************************
3. id: 1
4. select_type: SIMPLE
5. table: user 表名
6. partitions: NULL
7. type: ALL
8. possible_keys: NULL
9. key: NULL 使用的索引名
10. key_len: NULL
11. ref: NULL
12. rows: 27 查找的总行数
13. filtered: 10.00
14. Extra: Using where 额外说明
15. 1 row in set, 1 warning (0.00 sec)

4）查看表的总行数，查找sshd 用做的是全表扫描

1. mysql> select count(*) from tarena.user;
2. +----------+
3. | count(*) |
4. +----------+
5. | 28 |
6. +----------+
7. 1 row in set (0.01 sec)

5） 给name表头设置索引

1. mysql> create index name on tarena.user(name);
2. Query OK, 0 rows affected (0.43 sec)
3. Records: 0 Duplicates: 0 Warnings: 0
5. mysql> desc tarena.user; 查看
6. +----------+-------------+------+-----+---------+----------------+
7. | Field | Type | Null | Key | Default | Extra |
8. +----------+-------------+------+-----+---------+----------------+
9. | id | int | NO | PRI | NULL | auto_increment |
10. | name | char(20) | YES | MUL | NULL | |
11. | password | char(1) | YES | | NULL | |
12. | uid | int | YES | | NULL | |
13. | gid | int | YES | | NULL | |
14. | comment | varchar(50) | YES | | NULL | |
15. | homedir | varchar(80) | YES | | NULL | |
16. | shell | char(30) | YES | | NULL | |
17. +----------+-------------+------+-----+---------+----------------+
18. 8 rows in set (0.00 sec)

6）验证索引

1. mysql> explain select * from tarena.user where name="sshd" \G
2. *************************** 1. row ***************************
3. id: 1
4. select_type: SIMPLE
5. table: user 表名
6. partitions: NULL
7. type: ref
8. possible_keys: name
9. key: name 使用的索引名
10. key_len: 21
11. ref: const
12. rows: 1 查找的总行数
13. filtered: 100.00
14. Extra: NULL 额外说明
15. 1 row in set, 1 warning (0.00 sec)

4 案例4：用户管理

4.1 问题

1. 允许所有主机使用root连接数据库服务，对所有库和所有表有完全权限、密码为123qqq…A
2. 允许192.168.88.0/24网段主机使用plj连接数据库服务，仅对gamedb库有完全权限、密码为tarena
3. 允许在本机使用pljadmin用户连接数据库服务器，仅对tarena库有查询、插入、更新、删除记录的权限，密码为NSD2023…a
4. 允许192.168.88.51主机使用yaya用户连接数据库服务，仅对tarena库有查询权限，密码为tarena1
5. 给yaya用户追加，插入记录的权限
6. 撤销plj用户删库、删表、删记录的权限
7. 删除pljadmin用户

4.2 方案

授权是在数据库服务器里添加用户并设置权限及密码；重复执行grant命令时如果库名和用户名不变时，是追加权限。授权步骤如下：

授权信息保存在mysql库的如下表里：

• user表 保存已有的授权用户及用户对所有库的权限
• db表 保存已有授权用户对某一个库的访问权限
• tables_priv表 记录已有授权用户对某一张表的访问权限
• columns_priv表 记录已有授权用户对某一个表头的访问权限

在192.168.88.50 数据库服务器练习用户授权

在192.168.88.51 数据库服务器测试

4.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：在192.168.88.50 数据库服务器做如下授权练习

命令操作如下所示：

1. //数据库管理员登陆
2. ]# mysql -uroot -pNSD2023...a

1）允许所有主机使用root连接数据库服务，对所有库和所有表有完全权限、密码为123qqq…A

1. mysql> create user root@"%" identified by "123qqq...A"; 创建用户
2. Query OK, 0 rows affected (0.08 sec)
3. mysql> grant all on *.* to root@"%" ; 授予权限
4. Query OK, 0 rows affected (0.13 sec)

2）允许192.168.88.0/24网段主机使用plj连接数据库服务，仅对gamedb库有完全权限、密码为tarena

1. mysql> create user plj@"192.168.88.0/24" identified by "tarena"; 创建用户
2. Query OK, 0 rows affected (0.06 sec)
3. mysql> grant all on gamedb.* to plj@"192.168.88.0/24"; 授予权限
4. Query OK, 0 rows affected (0.05 sec)

3）允许在本机使用pljadmin用户连接数据库服务器，仅对tarena库有查询、插入、更新、删除记录的权限，密码为NSD2023…a

1. mysql> create user pljadmin@"localhost" identified by "NSD2023...a"; 创建用户
2. Query OK, 0 rows affected (0.05 sec)
3. mysql> grant select , insert , update,delete on tarena.* to pljadmin@"localhost";授予权限
4. Query OK, 0 rows affected (0.06 sec)

4）允许192.168.88.51主机使用yaya用户连接数据库服务，仅对tarena库有查询权限，密码为tarena1

1. mysql> create user yaya@"192.168.88.51" identified by "tarena1" ; 创建用户
2. Query OK, 0 rows affected (0.10 sec)
3. mysql> grant select on tarena.* to yaya@"192.168.88.51"; 授予权限
4. Query OK, 0 rows affected (0.07 sec)

5）给yaya用户追加，插入记录的权限

1. mysql> grant insert on tarena.* to yaya@"192.168.88.51";
2. Query OK, 0 rows affected (0.05 sec)

6）查看添加的用户

1. //添加的用户保存在 mysql库的user表里
2. mysql> select host,user from mysql.user;
3. +-----------------+------------------+
4. | host | user |
5. +-----------------+------------------+
6. | % | root |
7. | 192.168.88.0/24 | plj |
8. | 192.168.88.51 | yaya |
9. | localhost | mysql.infoschema |
10. | localhost | mysql.session |
11. | localhost | mysql.sys |
12. | localhost | pljadmin |
13. | localhost | root |
14. +-----------------+------------------+
15. 8 rows in set (0.00 sec)
17. //查看已有用户的访问权限
18. mysql> show grants for yaya@"192.168.88.51";
19. +--------------------------------------------------------------+
20. | Grants for yaya@192.168.88.51 |
21. +--------------------------------------------------------------+
22. | GRANT USAGE ON *.* TO `yaya`@`192.168.88.51` |
23. | GRANT SELECT, INSERT ON `tarena`.* TO `yaya`@`192.168.88.51` |
24. +--------------------------------------------------------------+
25. 2 rows in set (0.00 sec)
26. //用户对某一个库的访问权限保存在mysql库的db表里
27. mysql> select * from mysql.db where db="tarena" and user="yaya" \G
28. *************************** 1. row ***************************
29. Host: 192.168.88.51
30. Db: tarena
31. User: yaya
32. Select_priv: Y
33. Insert_priv: Y
34. Update_priv: N
35. Delete_priv: N
36. Create_priv: N
37. Drop_priv: N
38. Grant_priv: N
39. References_priv: N
40. Index_priv: N
41. Alter_priv: N
42. Create_tmp_table_priv: N
43. Lock_tables_priv: N
44. Create_view_priv: N
45. Show_view_priv: N
46. Create_routine_priv: N
47. Alter_routine_priv: N
48. Execute_priv: N
49. Event_priv: N
50. Trigger_priv: N
51. 1 row in set (0.00 sec)
52. mysql>

7）撤销plj用户删库、删表、删记录的权限

1. mysql> revoke delete,drop on gamedb.* from plj@"192.168.88.0/24" ;

8）修改yaya用户的登陆密码为123456

1. mysql> set password for yaya@"192.168.88.51"="123456" ;
2. Query OK, 0 rows affected (0.05 sec)

9）删除pljadmin用户

1. mysql> drop user pljadmin@"localhost" ;
2. Query OK, 0 rows affected (0.04 sec)

步骤二：在192.168.88.51测试授权

命令格式 mysql -h数据库服务器ip地址 –u用户名 -p密码

1）在mysql51连接mysql50 （使用50 添加的yaya 用户）

1. [root@mysql51 ~]# mysql -h192.168.88.50 -uyaya -p123456
2. mysql> show grants; //查看权限
3. +--------------------------------------------------------------+
4. | Grants for yaya@192.168.88.51 |
5. +--------------------------------------------------------------+
6. | GRANT USAGE ON *.* TO `yaya`@`192.168.88.51` |
7. | GRANT SELECT, INSERT ON `tarena`.* TO `yaya`@`192.168.88.51` |
8. +--------------------------------------------------------------+
9. 2 rows in set (0.00 sec)
10. mysql> select user();//查看登陆信息
11. +--------------------+
12. | user() |
13. +--------------------+
14. | yaya@192.168.88.51 |
15. +--------------------+
16. 1 row in set (0.00 sec)
17. mysql> insert into tarena.user(name,uid) values("jim",11); //权限内可以执行
18. Query OK, 1 row affected (0.06 sec)
19. mysql> delete from tarena.salary ;
20. ERROR 1142 (42000): DELETE command denied to user 'yaya'@'192.168.88.51' for table 'salary' //超出权限 报错
21. mysql>