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

这篇具有很好参考价值的文章主要介绍了主键、外键、建表范式、MySQL索引、用户管理。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1 案例1:主键

1.1 问题

完成如下练习:

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

1.2 方案

主键使用规则:

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

1.3 步骤

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

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

    //语法格式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.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)
    //语法格式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.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 null
    mysql> 
    //与第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)建表

    mysql> 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: gz
    Create 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: gz
    Create Table: CREATE TABLE `gz` (
      `gz_id` int(11) DEFAULT NULL,
      `pay` float DEFAULT NULL,
      KEY `gz_id` (`gz_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 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: gz
    Create 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
    1 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 fails
    mysql> 
    给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.ibd
    mysql>

查看索引详细信息

    show index   from   库.表; 

例子

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

步骤二:验证索引

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

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

6)验证索引

    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)

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.51
                       Db: tarena
                     User: yaya
              Select_priv: Y
              Insert_priv: Y
              Update_priv: N
              Delete_priv: N
              Create_priv: N
                Drop_priv: N
               Grant_priv: N
          References_priv: N
               Index_priv: N
               Alter_priv: N
    Create_tmp_table_priv: N
         Lock_tables_priv: N
         Create_view_priv: N
           Show_view_priv: N
      Create_routine_priv: N
       Alter_routine_priv: N
             Execute_priv: N
               Event_priv: N
             Trigger_priv: N
    1 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 用户)文章来源地址https://www.toymoban.com/news/detail-793443.html

    [root@mysql51 ~]# mysql -h192.168.88.50 -uyaya -p123456
    mysql> 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>

到了这里,关于主键、外键、建表范式、MySQL索引、用户管理的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL】MySQL索引、事务、用户管理

    20岁的男生穷困潦倒,20岁的女生风华正茂,没有人会一直风华正茂,也没有人会一直穷困潦倒… 1. MySQL给用户提供存取数据的服务,但数据在linux机器的磁盘外设上进行存储,而磁盘的读取效率是比较低的,MySQL如何进行数据存取以提高效率呢?这是一个重要的话题。 在硬件

    2024年02月14日
    浏览(44)
  • MySQL---数据用户管理和索引

    DDL:CTEATE DROP ALTER dml:对数据进行管理 update insert into delete truncate dpl:查询语句 select dcl:权限控制语句 grant revoke 创建用户 修改用户权限 删除用户 grant要在终端执行 多个权限之间用逗号隔开 索引是一个排序的列表,列表当中存储的是索引的值和包含这个值的数据所在行的

    2024年02月07日
    浏览(35)
  • 浅谈mysql的主键和索引

    在上一篇文章《count(1)、count(*)、count(字段)哪个更靠谱》中,我们提到过主键是优化不了count的查询效率的,需要建索引才可以,那么,是不是意味着主键的效率还不如一般的索引呢?怀着这个疑问,我们一起来了解下mysql主键和索引的相关知识。 mysql数据库的MYISAM和InnoDB引擎

    2024年02月08日
    浏览(43)
  • 【Mysql】复合主键的索引

    复合主键在where中使用查询的时候到底走不走索引呢?例如下表: 当执行以下SQL的时候到底走不走索引呢?             Explain结果: 使用索引   使用索引   使用索引   不使用索引   不使用索引   使用索引   结论:Mysql复合主键的顺序十分重要,WHERE查询条件中会按列匹配

    2023年04月25日
    浏览(80)
  • mysql中主键索引和联合索引的原理解析

    主键索引 :按照主键数据从小到大按照从左到右进行排序,叶节点只存储数据区; 接着将上面的页生成出来,页只存储索引和指针,指针指向数据域,当通过主键查找数据时,从B+树的头部开始寻址数据、读取数据。 上面为索引页 下面为数据页 查询select * from table where a=6,

    2024年04月08日
    浏览(38)
  • Mysql-InnoDB索引:普通索引、主键索引、唯一索引、组合索引

    事务方面 InnoDB支持事务,MyISAM不支持事务。这是Mysql将默认存储引擎从MyISAM变成InnoDB的重要原因之一 外键方面 InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MyISAM会失败 索引层面 InnoDB是聚集(聚簇)索引,MyISAM是非聚集(非聚簇)索引。 MyISAM 支持 FULLTEXT 类型

    2024年02月03日
    浏览(52)
  • 【面试】Mysql主键索引普通索引索引和唯一索引的区别是什么?

    在 MySQL 中, 索引是在存储引擎层实现的, 所以并没有统⼀的索引标准, 由于 InnoDB 存储引擎在 MySQL数据库中使⽤最为⼴泛, 下⾯以 InnoDB 为例来分析⼀下其中的索引模型.在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, InnoDB 使⽤了 B+ 树索引模型,所以数据都是存储在 B+ 树

    2023年04月17日
    浏览(53)
  • Mysql 索引(三)—— 不同索引的创建方式(主键索引、普通索引、唯一键索引)

    了解了主键索引的底层原理,主键索引其实就是 根据主键字段建立相关的数据结构 (B+树),此后在 使用主键字段作为条件查询时,会直接根据主键查找B+树的叶子结点。 除了 主键索引外,普通索引和唯一键索引也是如此,只不过普通索引要稍微绕一点,下面会具体介绍。

    2024年02月03日
    浏览(38)
  • 【后端面经】MySQL主键、唯一索引、联合索引的区别和作用

    目录 0. 简介 1. 主键 2. 唯一索引 3. 联合索引 4. 索引对数据库操作的影响 5. 其他索引 5.1 普通索引 5.2 全文索引 5.3 前缀索引 6. 总结 7. 参考资料 索引是一类特殊的 文件 ,用来存储检索信息,使数据库查找更加快速。 主键是一类特殊的唯一索引,选择某一列元素作为主键,用

    2024年02月09日
    浏览(44)
  • mysql的主键索引为什么不能null

    这是一个非常奇怪且有趣的问题。可以通过官方文档进行解读 https://dev.mysql.com/doc/refman/5.7/en/glossary.html A special value in SQL, indicating the absence of data. Any arithmetic operation or equality test involving a NULL value, in turn produces a NULL result. (Thus it is similar to the IEEE floating-point concept of NaN, “not

    2024年02月14日
    浏览(72)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包