数据库键值类型,主键,外键,索引的应用

最新推荐文章于 2024-08-28 19:34:48 发布

（_浪漫☆傲天

最新推荐文章于 2024-08-28 19:34:48 发布

阅读量664

点赞数

文章标签：数据库主键外键索引

本文链接：https://blog.youkuaiyun.com/MANlidong/article/details/100534920

版权

本文详细解析了数据库中的主键、外键和索引的概念及应用，包括它们的作用、创建方式、优缺点，以及如何在MySQL中进行操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主键,外键,索引的区别

在这里插入图片描述

索引–类似于书的目录,对表中的数据进行排序.

索引的类型有:Btree B+tree hash

索引的优点:唯一索引可以保证数据的唯一性.
加快数据的查询速度

索引的缺点:对数据进行增删修改时,索引会动态调整,降低数据的写入速度
占用物理空间

mysql键值的类型

index 普通索引

unique 唯一索引

fulltext 全文索引

primary key 主键

foreign key 外键

查看索引信息:show index from 表名\G;

mysql> show index from test\G;
*************************** 1. row ***************************
        Table: test
   Non_unique: 1
     Key_name: 学号            //索引名称
 Seq_in_index: 1
  Column_name: 学号         //字段名称
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE     //二叉树木算法
      Comment: 
Index_comment:

index 普通索引

可以有多个普通索引,字段值允许重复,且可以为空,通常把作为查询条件的字段值设置为index字段,标识 MUL

1.在创建表时创建索引

mysql> create table school.test(
    -> 学号   char(7) not null,
    -> 姓名   varchar(10),
    -> 年龄   int(2),
    -> index(学号),index(姓名));
Query OK, 0 rows affected (0.60 sec)

mysql> desc school.test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 学号   | char(7)     | NO   | MUL | NULL    |       |
| 姓名   | varchar(10) | YES  | MUL | NULL    |       |
| 年龄   | int(2)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2.在已经有的表里创建索引
命令格式:create index 索引名 on 表名(字段名);

mysql> create index age on school.test(年龄);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc school.test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 学号   | char(7)     | NO   | MUL | NULL    |       |
| 姓名   | varchar(10) | YES  | MUL | NULL    |       |
| 年龄   | int(2)      | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.删除索引
命令格式:drop index 索引名 on 表名

mysql> drop  index age on school.test;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc school.test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 学号   | char(7)     | NO   | MUL | NULL    |       |
| 姓名   | varchar(10) | YES  | MUL | NULL    |       |
| 年龄   | int(2)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

primary key 主键

字段值不允许重复,不允许为空所以通常把表中唯一标示的字段设为主键
通常与auto_increment (自动增长)
一个表只有一个主键
多个字段作为主键,称为复合主键,必须一起创建.
字段值标识PRI

1.建表时创建主键

mysql> create table school.t8(
    -> id int  primary key,
    -> name  char(3));
Query OK, 0 rows affected (0.41 sec)

mysql> desc school.t8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| name  | char(3) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.创建复合主键

mysql> create table t7( name  char(10), class char(7), status  enum("yes","no"), primary key(name,class));
Query OK, 0 rows affected (0.34 sec)

mysql> desc t7;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | char(10)         | NO   | PRI | NULL    |       |
| class  | char(7)          | NO   | PRI | NULL    |       |
| status | enum('yes','no') | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.在已有的表里创建主键
命令格式:alter table 表名 add primary key(字段名);

mysql> desc t9
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(3) | NO   |     | NULL    |       |
| id    | int(3)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t9 add primary key(name);
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(3) | NO   | PRI | NULL    |       |
| id    | int(3)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4.删除主键
命令格式:alter table 表名 drop primary key;

mysql> alter table t9 drop primary key;
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(3) | NO   |     | NULL    |       |
| id    | int(3)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.与auto_increment自增长连用

mysql> create table t10( id int  primary key auto_increment, name char(10));
Query OK, 0 rows affected (0.48 sec)

mysql> desc t10;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+------------

foreign key 外键(多张表同步更新,同步删除)

插入记录时,字段值在另一个表字段值范围内选择.
使用限制:表存储引擎必须是innodb,字段类型一致,被参照字段必须要是索引类型的一种(primary key)

创建外键

1.创建一个员工表和一个工资表

mysql> create table yg(
    -> yg_id  int  primary key auto_increment,
    -> name  char(10))engine=innodb;
Query OK, 0 rows affected (0.58 sec)

mysql> create table gz(
    -> gz_id  int,
    -> name  char(10),
    -> gz  float(7.2),
    -> foreign key(gz_id) references yg(yg_id)
    -> on  update cascade  
    -> on  delete cascade
    -> )engine=innodb;
Query OK, 0 rows affected (0.94 sec)

2.为yg表插入数据时因为yg_id有auto_increment自增长属性,所以只需要给name赋值即可

mysql> insert into yg(name) values("jerry"),("tom");
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|     2 | tom   |
+-------+-------+

3.给gz表添加员工工资

mysql> insert into gz values(1,"jerry",10000)
    -> ;
Query OK, 1 row affected (0.13 sec)

mysql> insert into gz values(2,"tom",8000);
Query OK, 1 row affected (0.13 sec)

4.验证表update更新联动(验证是不是同步更新)
我们可以看到更新了yg表之后 gz表数据能自动更新

mysql> update yg set yg_id=23 where name="tom";
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yg
    -> ;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | jerry |
|    23 | tom   |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from gz;
+-------+-------+-------+
| gz_id | name  | gz    |
+-------+-------+-------+
|     1 | jerry | 10000 |
|    23 | tom   |  8000 |
+-------+-------+-------+

5.验证delete删除联动(验证是否同步删除)
我们可以看到删除yg表jerry之后gz表的jerry也删除了

mysql> delete from yg where name="jerry";
Query OK, 1 row affected (0.13 sec)

mysql> select * from gz;
+-------+------+------+
| gz_id | name | gz   |
+-------+------+------+
|    23 | tom  | 8000 |
+-------+------+------+
1 row in set (0.00 sec)

mysql> select * from yg;
+-------+------+
| yg_id | name |
+-------+------+
|    23 | tom  |
+-------+------+

删除外键

1.获取外键约束名称
其中gz_idfk_1 是外键名称

mysql> show create table gz\G;
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  `gz` 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=utf8mb4
1 row in set (0.00 sec)

2.删除外键

mysql> alter table gz drop foreign key gz_ibfk_1;
Query OK, 0 rows affected (0.17 sec)

3.确认删除结果

mysql> show create table gz\G;
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  `gz` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)