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

1 案例1：主键

1.1 问题

完成如下练习：

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

1.2 方案

主键使用规则：

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

1.3 步骤

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

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

    //语法格式1create  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.49 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)//语法格式2create  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.39 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)//删除主键命令格式mysql> alter  table   库.表   drop primary  key ;//例子mysql>  alter  table db1.t36  drop primary key ;Query OK, 0 rows affected (1.00 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.00 sec)mysql> //添加主键命令格式mysql> alter  table  库.表  add  primary key(表头名);//例子mysql> alter  table  db1.t36  add  primary key(hz_id);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)//插入第1条记录 正常mysql> insert into db1.t35  values ("bob","888","nsd2107");Query OK, 1 row affected (0.05 sec)//空不可以mysql> insert into db1.t35  values ("john",null,"nsd2107"); ERROR 1048 (23000): Column 'hz_id' cannot be nullmysql> //与第1条重复不可以mysql> insert into db1.t35  values ("john","888","nsd2107"); ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'//不重复也不是null可以mysql> insert into db1.t35  values ("john","988","nsd2107"); Query OK, 1 row affected (0.07 sec)//查看表记录mysql> select  * from db1.t35 ;+------+-------+---------+| name | hz_id | class   |+------+-------+---------+| bob  | 888   | nsd2107 || john | 988   | nsd2107 |+------+-------+---------+2 rows in set (0.00 sec)

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

    //创建复合主键 表头依次是客户端ip 、服务端口号、访问状态mysql> create  table  db1.t39(cip   varchar(15) , port  smallint ,  status  enum("deny","allow") , primary key(cip,port));//插入记录验证insert into  db1.t39  values ("1.1.1.1",22,"deny");insert into  db1.t39  values ("1.1.1.1",22,"deny"); 同时相同报错insert into  db1.t39  values ("1.1.1.1",80,"deny"); 可以insert into  db1.t39  values ("2.1.1.1",80,"allow");可以//查看记录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 | allow  |+---------+------+--------+3 rows in set (0.00 sec)//删除复合主键mysql> alter table  db1.t39 drop primary key;Query OK, 3 rows affected (1.10 sec)Records: 3  Duplicates: 0  Warnings: 0//查看表头mysql>  desc  db1.t39;+--------+----------------------+------+-----+---------+-------+| Field  | Type                 | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+-------+| cip    | varchar(15)          | NO   |     | NULL    |       || port   | smallint             | NO   |     | NULL    |       || status | enum('deny','allow') | YES  |     | NULL    |       |+--------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)//没有复合主键约束后 ，插入记录不受限制了mysql>  insert into db1.t39 values("2.1.1.1",80,"allow");Query OK, 1 row affected (0.06 sec)mysql> insert into db1.t39 values("2.1.1.1",80,"deny");Query OK, 1 row affected (0.08 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 | allow  || 2.1.1.1 |   80 | allow  || 2.1.1.1 |   80 | deny   |+---------+------+--------+5 rows in set (0.00 sec)//添加复合主键时 字段下的数据与主键约束冲突 不允许添加mysql> alter table  db1.t39 add primary key(cip,port);ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key 't39.PRIMARY'//删除重复的数据mysql> delete from db1.t39 where cip="2.1.1.1";Query OK, 3 rows 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 rows in set (0.00 sec)//添加复合主键mysql> alter table  db1.t39 add primary key(cip,port);Query OK, 0 rows affected (0.67 sec)Records: 0  Duplicates: 0  Warnings: 0//查看表头mysql>  desc  db1.t39;+--------+----------------------+------+-----+---------+-------+| Field  | Type                 | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+-------+| cip    | varchar(15)          | NO   | PRI | NULL    |       || port   | smallint             | NO   | PRI | NULL    |       || status | enum('deny','allow') | YES  |     | NULL    |       |+--------+----------------------+------+-----+---------+-------+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）建表

ysql> create     table   db1.t38 (-> 行号   int   primary key  auto_increment   , -> 姓名   char(10) , -> 班级   char(7) , -> 住址   char(10)-> );
Query OK, 0 rows affected (0.76 sec)
//查看表头
mysql> desc db1.t38 ;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| 行号   | int      | NO   | PRI | NULL    | auto_increment |
| 姓名   | char(10) | YES  |     | NULL    |                |
| 班级   | char(7)  | YES  |     | NULL    |                |
| 住址   | char(10) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
//插入表记录 不给自增长表头赋值
mysql> insert  into  db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
Query OK, 1 row affected (0.05 sec)
mysql> insert  into  db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
Query OK, 1 row affected (0.04 sec)
mysql> insert  into  db1.t38(姓名,班级,住址)values("tom","nsd2107","bj");
Query OK, 1 row affected (0.05 sec)
//查看表记录
mysql> select  * from db1.t38;
+--------+--------+---------+--------+
| 行号   | 姓名   | 班级    | 住址   |
+--------+--------+---------+--------+
|      1 | bob    | nsd2107 | bj     |
|      2 | bob    | nsd2107 | bj     |
|      3 | tom    | nsd2107 | bj     |
+--------+--------+---------+--------+
3 rows in set (0.00 sec)

自增长使用注意事项

    //给自增长字段的赋值mysql> insert  into  db1.t38(行号,姓名,班级,住址)values(5,"lucy","nsd2107","bj");  Query OK, 1 row affected (0.26 sec)//不赋值后 用最后1条件记录表头的值+1结果赋值mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");  Query OK, 1 row affected (0.03 sec)//查看记录mysql> select  * from db1.t38 ;+--------+--------+---------+--------+| 行号   | 姓名   | 班级    | 住址   |+--------+--------+---------+--------+|      1 | bob    | nsd2107 | bj     ||      2 | bob    | nsd2107 | bj     ||      3 | tom    | nsd2107 | bj     ||      5 | lucy   | nsd2107 | bj     ||      6 | lucy   | nsd2107 | bj     |+--------+--------+---------+--------+5 rows in set (0.00 sec)//删除所有行mysql> delete  from  db1.t38 ；  //再添加行 继续行号 而不是从 1 开始 mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");//查看记录mysql> select  * from db1.t38;+--------+--------+---------+--------+| 行号   | 姓名   | 班级    | 住址   |+--------+--------+---------+--------+|      8 | lucy   | nsd2107 | bj     ||      9 | lucy   | nsd2107 | bj     ||     10 | lucy   | nsd2107 | bj     |+--------+--------+---------+--------+3 rows in set (0.01 sec)//truncate删除行 再添加行 从1开始mysql> truncate table  db1.t38;  Query OK, 0 rows affected (2.66 sec)//插入记录mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");Query OK, 1 row affected (0.04 sec)mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");Query OK, 1 row affected (0.30 sec)//查看记录mysql> select  * from db1.t38;+--------+--------+---------+--------+| 行号   | 姓名   | 班级    | 住址   |+--------+--------+---------+--------+|      1 | lucy   | nsd2107 | bj     ||      2 | lucy   | nsd2107 | bj     |+--------+--------+---------+--------+2 rows in set (0.01 sec)mysql> 

2 案例2：外键

2.1 问题

完成如下练习：

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

2.2 方案

外键使用规则：

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

作用：

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

2.3 步骤

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

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

//创建外键命令

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

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

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

表名 yg

员工编号 yg_id

姓名 name

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

创建工资表

表名 gz

员工编号 gz_id

工资 pay

    #创建工资表 指定外键表头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)engine=innodb ;//查看工资表外键mysql> show create  table db1.gz \G*************************** 1. row ***************************Table: gzCreate Table: CREATE TABLE `gz` (`gz_id` int(11) 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=latin1//删除外键mysql> alter table db1.gz drop FOREIGN KEY  gz_ibfk_1;//查看不到外键mysql> show create  table db1.gz \G*************************** 1. row ***************************Table: gzCreate Table: CREATE TABLE `gz` (`gz_id` int(11) DEFAULT NULL,`pay` float DEFAULT NULL,KEY `gz_id` (`gz_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> //添加外键mysql> alter table db1.gz add  foreign key(gz_id)  references db1.yg(yg_id)  on update cascade  on delete cascade ;//查看外键mysql> show create  table db1.gz \G*************************** 1. row ***************************Table: gzCreate Table: CREATE TABLE `gz` (`gz_id` int(11) 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=latin11 row in set (0.00 sec)

步骤二：验证外键功能

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

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

员工表插入记录
mysql> insert into db1.yg (name) values ("jerry"),("tom");
mysql> select  * from db1.yg;
工资表插入记录
mysql> insert into db1.gz values(1,50000);
mysql> insert into db1.gz values(2,60000);
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,50000);  
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)
员工表 插入编号3的员工
mysql> insert into db1.yg (name) values ("Lucy"); 
mysql> select  * from  db1.yg;  可以给3号员工 发工资了 
mysql> insert into db1.gz values(3,40000);  

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

    查看员工表记录mysql> select * from db1.yg;+-------+-------+| yg_id | name  |+-------+-------+|     1 | jerry ||     2 | tom   ||     3 | lucy  |+-------+-------+3 rows in set (0.00 sec)把yg表里编号是3的改成9 mysql> update db1.yg set yg_id=9 where yg_id=3;  mysql> select * from db1.yg;+-------+-------+| yg_id | name  |+-------+-------+|     1 | jerry ||     2 | tom   ||     9 | lucy  |+-------+-------+3 rows in set (0.00 sec)工资表里编号是3的自动变成 9 mysql> select * from db1.gz;+-------+----------+| gz_id | pay      |+-------+----------+|     1 | 50000.00 ||     2 | 60000.00 ||     9 | 40000.00 |+-------+----------+3 rows in set (0.00 sec)mysql> 

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

    删除前查看员工表记录mysql> select * from db1.yg;  +-------+-------+| yg_id | name  |+-------+-------+|     1 | jerry ||     2 | tom   ||     9 | lucy  |+-------+-------+3 rows in set (0.00 sec)删除编号2的员工mysql> delete from db1.yg where yg_id=2; Query OK, 1 row affected (0.04 sec)删除后查看mysql> select * from db1.yg; +-------+-------+| yg_id | name  |+-------+-------+|     1 | jerry ||     9 | lucy  |+-------+-------+2 rows in set (0.00 sec)查看工资表也没有编号2的工资了mysql> select * from db1.gz;  +-------+----------+| gz_id | pay      |+-------+----------+|     1 | 50000     ||     9 | 40000     |+-------+----------+2 rows in set (0.00 sec)mysql> 

4)、外键使用注意事项

    #被参考的表不能删除mysql> drop table db1.yg;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint failsmysql> 给gz表的gz_id表头 加主键标签保证每个员工只能发1遍工资  且有员工编号的员工才能发工资# 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键 delete form db1.gz;alter table db1.gz add primary key(gz_id); 保证每个员工只能发1遍工资  且有员工编号的员工才能发工资mysql> insert into db1.gz values (1,53000);  报错mysql> insert into db1.gz values (9,58000);  报错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)建表时创建索引命令格式

    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));

查看索引

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

例子

    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.ibdmysql>

查看索引详细信息

    show index   from   库.表； 

例子

    show  index   from  home.tea4 \G*************************** 1. row ***************************Table: tea4     #表名Non_unique: 1Key_name: id   #索引名   （默认索引名和表头名相同，删除索引时，使用的索引名）Seq_in_index: 1Column_name: id   #表头名Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREE      #索引类型Comment: Index_comment: *************************** 2. row ***************************Table: tea4   #表名Non_unique: 1Key_name: name  #索引名Seq_in_index: 1Column_name: name  #表头名Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREE  #排队算法Comment: Index_comment: 2 rows in set (0.00 sec)mysql> 

删除索引

    命令格式   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: tea4Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: Index_comment: 1 row in set (0.00 sec)mysql> 

已有表添加索引命令

    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: tea4Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: Index_comment: *************************** 2. row ***************************Table: tea4Non_unique: 1Key_name: nianling   设置的索引名Seq_in_index: 1Column_name: age  表头名Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: Index_comment: 2 rows in set (0.00 sec)mysql> 

步骤二：验证索引

    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)

2）使用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)

3）验证是否使用索引

    mysql> explain select * from tarena.user where name="sshd" \G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user  表名partitions: NULLtype: ALLpossible_keys: NULLkey: NULL      使用的索引名key_len: NULLref: NULLrows: 27         查找的总行数filtered: 10.00Extra: Using where   额外说明1 row in set, 1 warning (0.00 sec)

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

    mysql> select count(*) from tarena.user;+----------+| count(*) |+----------+|       28 |+----------+1 row in set (0.01 sec)

5） 给name表头设置索引

    mysql> create index  name on tarena.user(name);Query OK, 0 rows affected (0.43 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> 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)

6）验证索引

    mysql> explain select  * from tarena.user where name="sshd" \G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user    表名partitions: NULLtype: refpossible_keys: namekey: name  使用的索引名key_len: 21ref: constrows: 1     查找的总行数filtered: 100.00Extra: NULL  额外说明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 数据库服务器做如下授权练习

命令操作如下所示：

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

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

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

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

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

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

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

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

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

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

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

6）查看添加的用户

    //添加的用户保存在 mysql库的user表里mysql> select  host,user from  mysql.user; +-----------------+------------------+| host            | user             |+-----------------+------------------+| %               | root             || 192.168.88.0/24 | plj              || 192.168.88.51   | yaya             || localhost       | mysql.infoschema || localhost       | mysql.session    || localhost       | mysql.sys        || localhost       | pljadmin         || localhost       | root             |+-----------------+------------------+8 rows in set (0.00 sec)//查看已有用户的访问权限mysql> show grants for yaya@"192.168.88.51"; +--------------------------------------------------------------+| Grants for yaya@192.168.88.51                                |+--------------------------------------------------------------+| GRANT USAGE ON *.* TO `yaya`@`192.168.88.51`                   || GRANT SELECT, INSERT ON `tarena`.* TO `yaya`@`192.168.88.51` |+--------------------------------------------------------------+2 rows in set (0.00 sec)//用户对某一个库的访问权限保存在mysql库的db表里mysql> select  * from  mysql.db where db="tarena"  and user="yaya" \G*************************** 1. row ***************************Host: 192.168.88.51Db: tarenaUser: yayaSelect_priv: YInsert_priv: YUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N1 row in set (0.00 sec)mysql>

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

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

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

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

9）删除pljadmin用户

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

步骤二：在192.168.88.51测试授权

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

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

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