Mysql主键外键 Mysql索引用户管理

最新推荐文章于 2024-08-11 15:20:18 发布

阿林的核桃酥

最新推荐文章于 2024-08-11 15:20:18 发布

阅读量16

点赞数

文章标签： mysql android 数据库

1 主键

主键使用规则：

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

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

语法格式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)

删除主键命令格式

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>

添加主键命令格式

alter table 库.表 add primary key(表头名);

//例子

查看表头

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表验证主键约束

//插入第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");
 mysql> insert into db1.t39 values("2.1.1.1",80,"deny");

//查看表记录

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

//建表

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");
 
 mysql> insert  into  db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
 
 mysql> insert  into  db1.t38(姓名,班级,住址)values("tom","nsd2107","bj");

//查看表记录

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)

//删除所有行

//再添加行继续行号而不是从 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> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
 mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");

//查看记录

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 方案

外键使用规则：

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

作用：

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

2.2 步骤

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

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

//创建外键命令

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

步骤二：验证外键功能

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

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

员工表插入记录

工资表插入记录

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的员工

可以给3号员工发工资了

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> 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表头加主键标签

delete from 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 方案

使用规则：

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

3.2 步骤

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

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

建表时创建索引

Create database  home;
 Use home;
 CREATE TABLE tea4(
 id char(6),
 name varchar(6),
 age int(3),
 gender ENUM('boy','girl') DEFAULT 'boy',
 INDEX(id),INDEX(name)
 );

查看索引

mysql> desc home.tea4;
 +--------+--------------------+------+-----+---------+-------+
 | Field  | Type               | Null | Key | Default | Extra |
 +--------+--------------------+------+-----+---------+-------+
 | id     | char(6)            | YES   | MUL | NULL    |       |
 | name   | varchar(6)         | YES  | MUL | NULL    |       |
 | age    | int(3)              | YES |     | NULL    |       |
 | gender | enum('boy','girl') | YES  |     | boy     |       |
 +--------+--------------------+------+-----+---------+-------+
 4 rows in set (0.00 sec)

查看索引详细信息

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>

删除索引

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>

已有表添加索引命令

查看索引

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>

步骤二：验证索引

查看表头（name 表头没有设置索引）

mysql> desc tarena.user;
 +----------+-------------+------+-----+---------+----------------+
 | Field    | Type        | Null | Key | Default | Extra          |
 +----------+-------------+------+-----+---------+----------------+
 | id       | int         | NO   | PRI | NULL    | auto_increment |
 | name     | char(20)    | YES  |     | NULL    |                |
 | password | char(1)     | YES  |     | NULL    |                |
 | uid      | int         | YES  |     | NULL    |                |
 | gid      | int         | YES  |     | NULL    |                |
 | comment  | varchar(50) | YES  |     | NULL    |                |
 | homedir  | varchar(80) | YES  |     | NULL    |                |
 | shell    | char(30)    | YES  |     | NULL    |                |
 +----------+-------------+------+-----+---------+----------------+
 8 rows in set (0.00 sec)

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

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

验证是否使用索引

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

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

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

给name表头设置索引

mysql> create index  name on tarena.user(name);
 Query OK, 0 rows affected (0.43 sec)
 Records: 0  Duplicates: 0  Warnings: 0

查看

mysql> desc tarena.user; 
 +----------+-------------+------+-----+---------+----------------+
 | Field    | Type        | Null | Key | Default | Extra          |
 +----------+-------------+------+-----+---------+----------------+
 | id       | int         | NO   | PRI | NULL    | auto_increment |
 | name     | char(20)    | YES  | MUL | NULL    |                |
 | password | char(1)     | YES  |     | NULL    |                |
 | uid      | int         | YES  |     | NULL    |                |
 | gid      | int         | YES  |     | NULL    |                |
 | comment  | varchar(50) | YES  |     | NULL    |                |
 | homedir  | varchar(80) | YES  |     | NULL    |                |
 | shell    | char(30)    | YES  |     | NULL    |                |
 +----------+-------------+------+-----+---------+----------------+
 8 rows in set (0.00 sec)

验证索引

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

4 案例4：用户管理

4.1 方案

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

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

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

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

在192.168.88.51 数据库服务器测试

4.2 步骤

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

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

数据库管理员登陆完成如下练习：

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

mysql> create user root@"%" identified by "123qqq...A"; 创建用户
 mysql> grant all on *.* to root@"%" ; 授予权限

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

mysql> create user plj@"192.168.88.0/24" identified by "tarena"; 创建用户
 mysql> grant all on  gamedb.*  to plj@"192.168.88.0/24"; 授予权限

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

mysql> create user pljadmin@"localhost" identified by "NSD2023...a"; 创建用户
 mysql> grant  select , insert , update,delete on tarena.* to pljadmin@"localhost";授予权限

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

mysql> create user yaya@"192.168.88.51"  identified by "tarena1" ; 创建用户
 mysql> grant select on tarena.* to yaya@"192.168.88.51"; 授予权限

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

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用户删库、删表、删记录的权限

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

9）删除pljadmin用户

步骤二：在192.168.88.51测试授权

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

//查看权限

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> delete from  tarena.salary ;
 ERROR 1142 (42000): DELETE command denied to user 'yaya'@'192.168.88.51' for table 'salary'  
 mysql>

原创作者: hezidong 转载于: https://blog.51cto.com/hezidong/11456766

阿林的核桃酥

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql主键外键 Mysql索引用户管理

1 主键主键使用规则：表头值不允许重复，不允许赋NULL值一个表中只能有一个primary key 表头多个表头做主键，称为复合主键，必须一起创建和删除主键标志PRI主键通常与auto_increment连用通常把表中唯一标识记录的表头设置为主键[行号表]步骤一：练习主键的创建、查看、删除、添加、验证主键语法格式1crea...
复制链接

扫一扫

Mysql主键 外键 Mysql索引 用户管理

1 主键

2 案例2：外键

2.1 方案

2.2 步骤

3 案例3：MySQL索引

3.1 方案

3.2 步骤

4 案例4：用户管理

4.1 方案

4.2 步骤

Mysql主键外键 Mysql索引用户管理