【跟着例子学MySQL】 补充内容 -- 主外键和索引

news/2024/7/25 20:33:08/文章来源:https://blog.csdn.net/nick0314221_1/article/details/139225675

文章目录

  • 前言
  • 回顾
  • 主键
  • 外键
  • 索引
  • 未完待续


前言

在这里插入图片描述
举例子,是最简单有效的学习方法。本系列文章以一个贯穿始终的场景,结合多个实例讲解MySQL的基本用法。

为什么要写这个系列?

  • 模仿是最好的老师,实践是检验成果的方法。
  • 本系列以实操样例和应用场景为核心,将MySQL基本用法贯穿其中,达到学以致用的效果。

为什么要学习MySQL?

  • MySQL是最常用的数据库之一,具有简单易用的特点,适合初学者学习
  • 数据库的基本用法有相通之处,学会MySQL可以为学习其他数据库打下良好基础

跟别的入门教材有什么不同?

  • 以一个贯穿始终的应用场景为主线,渐进地讲解用法
  • 难度适中,既有基础方法,也有值得注意的关键细节

本系列文章不包含哪些内容?

该系列文章的目标是,将最核心的知识,以最直接的方式,呈现给初学者。因此不包含:

  • MySQL安装方法
  • MySQL系统管理方法,例如备份、恢复、导入导出等
  • 高级主题,例如数据库监控、数据库调优和SQL优化

回顾

上篇文章👉《【跟着例子学MySQL】多表关联 – 一对一关系》 讲了一对一的多表关联。这篇讲解关于主键、外键和索引的更多知识。


主键

在关系模型中,表不应包含重复的行,因为这会在检索中产生歧义。为了确保唯一性,每个表都应该有一个列((或一组列),称为主键,它可以唯一地标识表中的每个记录。例如,一个唯一的 customerID可以作为客户表的主键;产品表的productCode;图书表的isbn。如果主键是单列,则称为简单键;如果它由几列组成,则称为复合键。大多数RDBMS会在主键上建立一个索引,以方便快速搜索。主键通常用于与其他表进行关联。

外键

子表的外键用于引用父表。可以施加外键约束,以确保子表中的所谓引用完整性值必须是父表中的有效值。

我们在定义引用父表的子表时定义外键,如下所示:

-- 子表定义
CREATE TABLE tableName (............CONSTRAINT constraintName FOREIGN KEY (columName) REFERENCES parentTableName     (columnName)[ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION] [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION] 
)

你可以通过可选的ON UPDATEON DELETE子句来指定更新和删除的引用:

  1. RESTRICT(默认):如果子表中存在匹配的行,则不允许删除或更新父行。
  2. CASCADE:将删除或更新操作级联到子表中匹配的行。
  3. SET NULL:将子表中的外键值设置为NULL(如果允许为NULL)。
  4. NO ACTION:表示对父行没有操作。

尝试删除 products_suppliers(子)表所引用的suppliers(父)表中的记录,例如,

mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
| 2001      | 501        |
| 2002      | 501        |
| 2003      | 501        |
| 2004      | 502        |
| 2001      | 503        |
+-----------+------------+
-- 尝试从父表中删除与子表中的行相匹配的行
mysql> DELETE FROM suppliers WHERE supplierID = 501;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`productsales`.`products_suppliers`, CONSTRAINT `products_suppliers_ibfk_2`
FOREIGN KEY (`supplierID`) REFERENCES `suppliers` (`supplierID`))

无法删除该记录,因为已施加了默认的“ON DELETE RESTRICT”约束。

索引

可以在选定的列上创建索引(或键),以方便快速搜索。在没有索引的情况下,SELECT * FROM products WHERE productID=x需要与products中所有记录的productID列相匹配。如果对productID列进行索引(例如,使用二叉树),匹配可以大大改进(通过二叉树搜索)。

你应该索引在WHERE子句中常用的列;并作为JOIN列。

索引的缺点是成本和空间。构建和维护索引需要计算和内存空间。索引便于快速搜索,但降低修改表(插入/更新/删除)的性能,因为需要验证。然而,关系数据库通常为查询和检索进行优化,但不是为更新进行优化。

在MySQL中,关键字KEY是索引的同义词。

在MySQL中,索引可以建立在:

  1. 单列(列索引)
  2. 一组列(组合索引)
  3. 唯一值列(唯一索引或唯一键)
  4. 字符串(VARCHAR或CHAR)的列前缀,例如前5个字符

在一个表中可以有多个索引。索引会自动建立在主键列上。你可以通过创建表、创建索引或更改表来构建索引。

CREATE TABLE tableName (......[UNIQUE] INDEX|KEY indexName (columnName, ...),-- 可选关键字UNIQUE确保此列中的所有值都是不同. KEY是索引的同义词......
PRIMAY KEY (columnName, ...) -- 自动建立在主键列上的索引
);
CREATE [UNIQUE] INDEX indexName ON tableName(columnName, ...);
ALTER TABLE tableName ADD UNIQUE|INDEX|PRIMARY KEY indexName (columnName, ...)
SHOW INDEX FROM tableName;

例如:

mysql> CREATE TABLE employees (emp_no INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,gender ENUM ('M','F') NOT NULL,birth_date DATE NOT NULL,hire_date DATE NOT NULL,PRIMARY KEY (emp_no) -- 在主键列上自动构建的索引);
mysql> DESCRIBE employees;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| emp_no     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)      | NO   |     | NULL    |                |
| gender     | enum('M','F')    | NO   |     | NULL    |                |
| birth_date | date             | NO   |     | NULL    |                |
| hire_date  | date             | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
mysql> SHOW INDEX FROM employees \G
*************************** 1. row ***************************
Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
.......
mysql> CREATE TABLE departments (dept_no CHAR(4) NOT NULL,dept_name VARCHAR(40) NOT NULL,PRIMARY KEY (dept_no), -- 在主键列上自动构建的索引UNIQUE INDEX (dept_name) -- 在此唯一值列上构建索引);
mysql> DESCRIBE departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM departments \G
*************************** 1. row ***************************
Table: departments
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_no
.......
*************************** 2. row ***************************
Table: departments
Non_unique: 0
Key_name: dept_name
Seq_in_index: 1
Column_name: dept_name
.......
-- 员工与部门之间的多对多连接表
mysql> CREATE TABLE dept_emp (emp_no INT UNSIGNED NOT NULL,dept_no CHAR(4) NOT NULL,from_date DATE NOT NULL,to_date DATE NOT NULL,INDEX (emp_no), -- 在此非唯一值列上构建索引INDEX (dept_no), -- 在此非唯一值列上构建索引FOREIGN KEY (emp_no) REFERENCES employees (emp_no)ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (dept_no) REFERENCES departments (dept_no)ON DELETE CASCADE ON UPDATE CASCADE,PRIMARY KEY (emp_no, dept_no) -- 自动生成的索引);
mysql> DESCRIBE dept_emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| emp_no    | int(10) unsigned | NO   | PRI | NULL    |       |
| dept_no   | char(4)          | NO   | PRI | NULL    |       |
| from_date | date             | NO   |     | NULL    |       |
| to_date   | date             | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM dept_emp \G
*************************** 1. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
........
*************************** 2. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: dept_no
........
*************************** 3. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: emp_no
Seq_in_index: 1
Column_name: emp_no
........
*************************** 4. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: dept_no
Seq_in_index: 1
Column_name: dept_no
........

未完待续

下一篇我们接着介绍SQL的高级用法。


如果喜欢这篇文章,请不要忘记关注🧡、点赞👍和收藏📔哦!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_1052639.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

机械臂与Realsense D435 相机的手眼标定ROS包

本教程主要介绍机械臂与 Realsense D435 相机手眼标定的配置及方法。 系统:Ubuntu 20.0.4 ◼ ROS:Noetic ◼ OpenCV 库:OpenCV 4.2.0 ◼ Realsense D435:librealsense sdk(2.50.0)、realsense-ros 功能包&…

【quarkus系列】构建可执行文件native image

目录 序言为什么选择 Quarkus Native Image?性能优势便捷的云原生部署 搭建项目构建可执行文件方式一:配置GraalVM方式二:容器运行错误示例构建过程分析 创建docker镜像基于可执行文件命令式构建基于dockerfile构建方式一:构建mic…

“提升人工智能大模型智能:策略与挑战“

文章目录 每日一句正能量前言算法创新数据质量与多样性模型架构优化后记 每日一句正能量 失败时可以称为人生财富,成功时可以称为财富人生。 前言 随着人工智能技术的飞速发展,大模型已经成为推动多个领域创新的关键力量。从自然语言处理到图像识别&…

数据集007:垃圾分类数据集(含数据集下载链接)

数据集简介 本数据拥有 训练集:43685张; 验证集:5363张; 测试集:5363张; 总类别数:158类。 部分代码: 定义数据集 class MyDataset(Dataset):def __init__(self, modetrain, …

Redis篇 数据的编码方式和单线程模型

编码方式和单线程模型 一.redis中的数据类型二. Redis中查询编码方式命令三. 单线程模型四. 经典面试题,redis为何这么快?什么是IO多路复用? 一.redis中的数据类型 在redis中,数据类型大致分为5种 1.字符串类型 2.哈希 3.列表 4.集合 5.有序集合 redis底层在实现这些数据结构…

08、SpringBoot 源码分析 - 自动配置深度分析一

SpringBoot 源码分析 - 自动配置深度分析一 refresh和自动配置大致流程如何自动配置SpringBootApplication注解EnableAutoConfiguration注解AutoConfigurationImportSelector自动配置导入选择器DeferredImportSelectorHandler的handleDeferredImportSelectorGroupingHandler的r…

【Qt】Qt框架文件处理精要:API解析与应用实例:QFile

文章目录 前言:1. Qt 文件概述2. 输入输出设备类3. 文件读写类3.1. 打开open3.2. 读read / readline/ readAll3.3. 写write3.4. 关闭close 4. 读写文件示例5. 文件件和目录信息类总结: 前言: 在现代软件开发中,文件操作是应用程序…

【git】开发提交规范(feat、fix、perf)

这段时间收到的需求很多,可能是临近两周一次的大版本灰度上线,这次产生了一个关于git的思考,就是各个版本之间怎么管理的问题,这里做出我自己的一些方法。 首先,既然已经明确了remote分支中的release分支为主分支&…

如何设置远程桌面连接?

远程桌面连接是一种方便快捷的远程访问工具,可以帮助用户在不同地区间快速组建局域网,解决复杂网络环境下的远程连接问题。本文将针对使用远程桌面连接的操作步骤进行详细介绍,以帮助大家快速上手。 步骤一:下载并安装远程桌面连接…

文件上传漏洞:pikachu靶场中的文件上传漏洞通关

目录 1、文件上传漏洞介绍 2、pikachu-client check 3、pikachu-MIME type 4、pikachu-getimagesize 最近在学习文件上传漏洞,这里使用pikachu靶场来对文件上传漏洞进行一个复习练习 废话不多说,开整 1、文件上传漏洞介绍 pikachu靶场是这样介绍文…

前端Vue自定义顶部搜索框:实现热门搜索与历史搜索功能

前端Vue自定义顶部搜索框:实现热门搜索与历史搜索功能 摘要: 随着前端开发复杂性的增加,组件化开发成为了提高效率和降低维护成本的有效手段。本文介绍了一个基于Vue的前端自定义顶部搜索框组件,该组件不仅具备基本的搜索功能&am…

记录一次内存取证

1.情景复现 我姐姐的电脑坏了。我们非常幸运地恢复了这个内存转储。你的工作是从系统中获取她所有的重要文件。根据我们的记忆,我们突然看到一个黑色的窗口弹出,上面有一些正在执行的东西。崩溃发生时,她正试图画一些东西。这就是我们从崩溃…

SQL——SELECT相关的题目(力扣难度等级:简单)

目录 197、上升的温度 577、员工奖金 586、订单最多的客户 596、超过5名学生的课 610、判断三角形 620、有趣的电影 181、超过经理收入的员工 1179、重新格式化部门表(行转列) 1280、学生参加各科测试的次数 1965、丢失信息的雇员 1068、产品销售分…

微信小程序基础 -- 小程序UI组件(5)

小程序UI组件 1.小程序UI组件概述 开发文档:https://developers.weixin.qq.com/miniprogram/dev/framework/view/component.html 什么是组件: 组件是视图层的基本组成单元。 组件自带一些功能与微信风格一致的样式。 一个组件通常包括 开始标签 和 结…

SEO优化,小白程序员如何做SEO优化流量从0到1

原文链接:SEO优化,小白程序员如何做SEO优化流量从0到1 1、SEO是什么? SEO即:搜索引擎优化(Search Engine Optimization),是一种通过优化网站结构、内容和外部链接等因素,提高网站在搜索引擎中的自然排名&…

防止特权升级攻击的有效策略

防止特权升级攻击的有效策略 特权升级攻击是一种通过利用操作系统或应用程序中的编程错误、漏洞、设计缺陷、配置错误或访问控制缺陷,获得对原本应该被限制访问的资源进行未授权访问的行为。这种攻击方式可能导致攻击者获取敏感数据、安装恶意软件并发动其他网络攻…

51单片机-实机演示(单多个数码管)

仿真链接&#xff1a; http://t.csdnimg.cn/QAPhx 目录 一.引脚位置 二.多个显示 三 扩展 一.引脚位置 注意P00 - >A ; 这个多个的在左边,右边的A到B是控制最右边那个单个的. 接下来上显示单个的代码 #include <reg52.h> #include <intrins.h> #define u…

vscode中使用conda虚拟环境

每一次配置环境&#xff0c;真的巨烦&#xff0c;网上的资料一堆还得一个个尝试&#xff0c;遂进行整理 1.准备安装好Anaconda 附带一篇测试教程&#xff0c;安装anaconda 2.准备安装vscode 安装地址&#xff1a;Visual Studio Code 3.创建Conda环境 搜索框搜索Anaconda…

单点登录(JWT实现)

单点登陆的英文名是&#xff1a;Single Sign On&#xff08;简称SSO&#xff09;&#xff0c;只需要登陆一次&#xff0c;就可以访问所有信任的应用系统。 在单体项目中&#xff0c;我们登陆之后可以把验证用户信息的值放入session中&#xff0c;单个tomcat中的session是可以共…

一、Nginx详解和安装

目录 一、简介 1、什么是Nginx 2、Nginx的优点 二、四大应用场景 1、HTTP服务器 2、反向代理 3、负载均衡 4、动静分离 三、Nginx 源码安装 1、安装相关依赖 2、启动nginx 3、安装成系统服务 一、简介 1、什么是Nginx Nginx是一款轻量级的Web服务器&#xff0c;反向…