MySQL 数据库学习（五）主键、外键以及索引

最新推荐文章于 2024-06-12 14:18:50 发布
最新推荐文章于 2024-06-12 14:18:50 发布
阅读量1.5k 收藏 7
点赞数 2
分类专栏： MySQL 数据库学习 文章标签： 数据库 mysql 学习 服务器 linux 运维
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_79227925/article/details/132514407
版权

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.00 |
|     9 | 40000.00 |
+-------+----------+
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 用户）

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

久 一
关注
  • 2
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库学习笔记（六）----MySQL多表查询之外键、表连接、子查询、索引
weixin_34059951的博客
10-22 1840
本章主要内容： 一、外键 二、表连接 三、子查询 四、索引 一、外键： 1、什么是外键 2、外键语法  3、外键的条件 4、添加外键 5、删除外键 1、什么是外键： 主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性 外键：是另一表的主键, 外键可以有重复的, 可以是空值...
mysql数据库设计为表连接设计索引
02-21
连接谓词大部分是基于主键=外键这一条件，这是是最快的连接方式了。有三种扫描方式： 循环嵌套：首先在外层表中找到一行满足本地谓词的记录，然后再从内层表中查找与这一行数据相关的记录，并检其中哪些符合内层表的...
MySQL系列-语法说明以及基本操作（二）
最新发布
苏叶的专栏
06-12 1484
索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。1) 顺序访问顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。
关于数据库主键和外键（终于弄懂啦）
热门推荐
yimenglin的博客
06-18 10万+
转 关于数据库主键和外键（终于弄懂啦） 2018年07月16日 19:44:24 f45056231p 阅读数 19385 ...
mySQL(8)-主键与外键
aggie4628的专栏
03-29 836
1.主键: 干什么的？保证数据的唯一性。 一个表只能有一个主键吗？ 对 一个主键只能有一列吗？错。主键可以由两列合起来。2.外键： 干什么的？某列数据来自于另一张表的一列，且只来自于另一张的一列。 好处： 1.节约空间：比如department，由中文字变数字。比如“中共中央。。。”->1 2.对department做约束，两表数据一致。 不会出现如图所示的情况 3.查询表1...
mysql之primary key
toomemetoo的博客
05-10 418
相关知识 主键，是表中一列或者多列的组合，主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空，主键能够唯一的标识表中的每一条记录，可以结合外键来定义与不同数据表之间的关系。 怎么理解主键呢？我们知道数据库中的表可以存放很多数据，如果把表中的每一个数据比作一个人的话，那么表的主键就可以看做是人的身份证。 怎么给表中的字段添加主键约束 添加主键约束 有两种方式可以添加主键约束： 1.在定义列的同时指定主键； 2.在定义完字段之后再指定主键。 (1) 在定义列的同时指定主
数据库基本操作(创建主键、外键以及索引）
weixin_45859411的博客
05-30 1万+
​ 图书馆学习没有多大干劲了，数据库又快考试了根据考试内容写总结。老师回放已过期不得不自己来尝试 心酸！ 需要按照要求给表创建主键、创建索引，创建外键（并设置删除和更新规则） 一、创建主键 ​ 自己创建一个数据库和最少两个表（student表 和 course 表），然后上图：点击创建主键 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kEcUycta-1622373901728)(C:\Users\赵轶楠\AppData\Roaming\Typ
MySQL-外键索引
feizuiku0116的博客
08-02 1280
一.外键索引 如果数据表含有从属关系，比如部门和员工， 教室和桌椅，班级和同学就可以使用一对多的制表关系 一的一方定义为主表， 多的一方定义为从表 在多的一方添加一列，作为外键约束 ，引用少的一方的主键 如果主表中的主键被应用，则该条数据无法被删除 如果从表中的外键找不到相同值的主键，则无法被插入 # 创建分类表 CREATE TABLE category1 ( cid VARCHAR(32) PRIMARY KEY, cname VARCHAR(100)#分类名称 ); # 商品
MYSQL 数据库给表加外键约束条件 (史上最详细教程)
12-14
- 当试图插入或更新外键字段时，如果对应的主键在参照表中不存在，MySQL会抛出错误，除非数据库配置允许`ON DELETE`和`ON UPDATE`规则，这些规则指定了当主键改变或删除时，如何处理外键字段。 - `ON DELETE CASCADE...
MySQL创建数据表并建立主外键关系详解
12-16
MySQL 创建数据表并建立主外键关系是数据库设计...综上所述，理解和正确使用主键和外键是构建高效、稳定且具有良好数据一致性的MySQL数据库系统的关键。在实际应用中，应根据业务需求灵活调整主外键的设置和约束行为。
mysql数据库索引类型
11-07
MySQL数据库索引类型 MySQL数据库索引是指在关系型数据库管理系统中，用于提高查询速度和优化数据库性能的一种机制。索引是对数据库表中某一列或多列的值进行排序和组织，以便快速定位和检索数据。 一、MySQL...
mysql数据库学习与练习
03-15
创建表时，我们需要定义列的数据类型，如整数（INT）、字符串（VARCHAR）、日期时间（DATETIME）等，并可以设置主键（确保数据唯一性）和外键（建立表间关联）。修改表结构包括添加、删除或修改列，而删除表则意味着...
mysql 主键索引如何创建_SQL创建索引、主键
weixin_36215736的博客
01-19 5066
-- MySQL中四种方式给字段添加索引1)# 添加主键# ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);# -- // 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。#添加唯一索引-- UNIQUE 后面不用跟INDEX# ALTER TABLE tbl_name ADD UNIQUE index_name (col_li...
数据库中复合主键与联合主键的区别？
跟着BOSS有肉吃
01-12 1万+
复合主键 复合主键：数据库表的主键由两个及以上的字段组成。  例如： CREATE TABLE `asm_device_config_info` ( `asmModel` varchar(32) NOT NULL COMMENT '自助机型号', `deviceType` varchar(32) NOT NULL COMMENT '外设类型', `deviceModel` ...
【MySQL】五种约束类型, 主键和外键的使用方式(重点)
yzhcjl_的博客
05-24 4034
一文掌握MySQL中常用的五种约束类型, 其中重点介绍了主键和外键的使用方式, 主键是一条记录的唯一身份标识(not null+unique), 外键用于关联其他表的主键或唯一键
oracle创建表加主键外键,ORACLE数据库创建表、自增主键、外键相关语法讲解-Oracle...
weixin_35909443的博客
04-13 851
一 创建表结构：DROP TABLE base_list;CREATE TABLE base_list (“DATA_LIST_ID” NUMBER NOT NULL ,“BASE_DATA_ID” NUMBER NOT NULL ,“BASE_DATA_LIST” VARCHAR2(100 BYTE) NOT NULL ,“FLAG” NUMBER DEFAULT 0 NOT NULL)LOGG...
如何在数据库表中建立复合主键
踩不死的小逗
05-29 1万+
（即多个字段同时作为主键）主键是数据库表的一个重要属性，建立主键可以避免表中存在完全相同的记录，也就是说主键在一张表中的记录值是唯一的。 建立主键有两种方法：一种是在数据库提供的GUI环境中建立，另一种是通过SQL语句执行建立，下面分别介绍。    1.在数据库提供的GUI环境中建立（以SQL7为例）。 输入表信息后按Ctrl键同时选中多行，然后点上面的主键按钮就行了。 2.通过SQL语句
给表增加主键的sql语句
聆听
01-08 8万+
sql操作中有很多方式给表增加主键，大体有三种方式。 1.创建表的时候直接在表字段后，跟primary key关键字。（一张表有且只能有一个主键，主键具有唯一性。） 2.在创建表的时候在所有字段后面使用primary key（主键字段列表）来创建主键（如果有多个字段作为主键被称为复合主键） 3.在表创建好之后添加主键（表本身没有主键）：alter table  表名  add
mysql外键主键索引
07-27
主键(primary key)是能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。\[1\] 外键是表的外键是另一表的主键，外键可以有重复的值，也可以是空值。外键用来和其他表建立联系用的，可以保持数据的一致性。外键取值规则包括插入非空值时，如果主键表中没有这个值，则不能插入；更新时，不能改为主键表中没有的值；删除主键表记录时，可以选择级联删除外键记录或拒绝删除；更新主键记录时，同样可以选择级联更新外键记录或拒绝执行。\[2\]\[3\] 索引是用来提高查询和排序速度的数据结构。在MySQL中，索引可以是普通索引或唯一索引。普通索引允许有重复的值，而唯一索引不允许有重复的值。一个表可以有多个索引，用来加快查询的速度。\[2\]所以，MySQL中的外键、主键和索引是不同的概念，分别用于保证数据完整性、建立表之间的联系和提高查询排序的速度。 #### 引用[.reference_title] - *1* [MySQL的几个概念：主键，外键，索引，唯一索引](https://blog.csdn.net/weixin_33816611/article/details/92214124)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [mysql：主键、外键和索引的区别？](https://blog.csdn.net/stetstet/article/details/124199699)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [Mysql中主键和外键和索引](https://blog.csdn.net/qq_43508272/article/details/124838709)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值