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

    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. ysql> 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;
    4. 工资表插入记录
    5. mysql> insert into db1.gz values(1,50000);
    6. mysql> insert into db1.gz values(2,60000);
    7. mysql> select  * from db1.gz;
    8. +-------+----------+
    9. | gz_id | pay      |
    10. +-------+----------+
    11. |     1 | 50000    |
    12. |     2 | 60000    |
    13. +-------+----------+
    14. 2 rows in set (0.00 sec)
    15. #没有的3号员工 工资表插入记录报错
    16. mysql> insert into db1.gz values(3,50000);  
    17. 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)
    18. 员工表 插入编号3的员工
    19. mysql> insert into db1.yg (name) values ("Lucy"); 
    20. mysql> select  * from  db1.yg;  
    21.     可以给3号员工 发工资了 
    22. 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)
    11.     把yg表里编号是3的改成9 
    12.     mysql> update db1.yg set yg_id=9 where yg_id=3;  
    13.     mysql> select * from db1.yg;
    14.     +-------+-------+
    15.     | yg_id | name  |
    16.     +-------+-------+
    17.     |     1 | jerry |
    18.     |     2 | tom   |
    19.     |     9 | lucy  |
    20.     +-------+-------+
    21.     3 rows in set (0.00 sec)
    22.     工资表里编号是3的自动变成 9 
    23.     mysql> select * from db1.gz;
    24.     +-------+----------+
    25.     | gz_id | pay      |
    26.     +-------+----------+
    27.     |     1 | 50000.00 |
    28.     |     2 | 60000.00 |
    29.     |     9 | 40000.00 |
    30.     +-------+----------+
    31.     3 rows in set (0.00 sec)
    32.     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)
    11.     删除编号2的员工
    12.     mysql> delete from db1.yg where yg_id=2; 
    13.     Query OK, 1 row affected (0.04 sec)
    14.     删除后查看
    15.     mysql> select * from db1.yg; 
    16.     +-------+-------+
    17.     | yg_id | name  |
    18.     +-------+-------+
    19.     |     1 | jerry |
    20.     |     9 | lucy  |
    21.     +-------+-------+
    22.     2 rows in set (0.00 sec)
    23.     查看工资表也没有编号2的工资了
    24.     mysql> select * from db1.gz;  
    25.     +-------+----------+
    26.     | gz_id | pay      |
    27.     +-------+----------+
    28.     |     1 | 50000     |
    29.     |     9 | 40000     |
    30.     +-------+----------+
    31.     2 rows in set (0.00 sec)
    32.     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> 
    5.     给gz表的gz_id表头 加主键标签
    6.     保证每个员工只能发1遍工资  且有员工编号的员工才能发工资
    7.     # 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键 
    8.     delete form db1.gz;
    9.     alter table db1.gz add primary key(gz_id); 
    10.     保证每个员工只能发1遍工资  且有员工编号的员工才能发工资
    11.     mysql> insert into db1.gz values (1,53000);  报错
    12.     mysql> insert into db1.gz values (9,58000);  报错
    13.     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)
    11.     mysql> system ls /var/lib/mysql/home/tea4.ibd  保存排队信息的文件
    12.     /var/lib/mysql/home/tea4.ibd
    13.     mysql>

    查看索引详细信息

        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)
    31.     mysql>

    删除索引

        命令格式   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>

    已有表添加索引命令

        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)
    8.

    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
    4.     mysql> desc tarena.user; 查看
    5.     +----------+-------------+------+-----+---------+----------------+
    6.     | Field    | Type        | Null | Key | Default | Extra          |
    7.     +----------+-------------+------+-----+---------+----------------+
    8.     | id       | int         | NO   | PRI | NULL    | auto_increment |
    9.     | name     | char(20)    | YES  | MUL | NULL    |                |
    10.     | password | char(1)     | YES  |     | NULL    |                |
    11.     | uid      | int         | YES  |     | NULL    |                |
    12.     | gid      | int         | YES  |     | NULL    |                |
    13.     | comment  | varchar(50) | YES  |     | NULL    |                |
    14.     | homedir  | varchar(80) | YES  |     | NULL    |                |
    15.     | shell    | char(30)    | YES  |     | NULL    |                |
    16.     +----------+-------------+------+-----+---------+----------------+
    17.     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)
    5.

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

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

        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>
  • 相关阅读:
    十年架构五年生活-04第一个工作转折点
    虚幻引擎:UEC++中如何解析JSON字符串
    sql语句去掉括号及中括号中的内容，大型数据库与access数据库
    HDLBits: 在线学习 SystemVerilog（一）-Problem 2-6
    蓝牙运动耳机什么品牌性价比高，六款值得推荐的运动耳机分享
    数字ic设计——SPI
    Pycharm中Debug的高级技巧
    Android系统安全 — 5.3-APK V2签名介绍
    zemax场曲/畸变图与网格畸变图
    孙卫琴的《精通Vue.js》读书笔记-Vue组件的单向数据流
  • 原文地址：https://blog.csdn.net/fmj121030/article/details/136268631