MySQL笔记（二）

最新推荐文章于 2025-08-22 11:10:38 发布
转载 最新推荐文章于 2025-08-22 11:10:38 发布 · 82 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：https://juejin.im/post/5c1757c0e51d4546f83c8ff0
文章标签：

#数据库

本文详细解析了数据库中的多表联查技术，包括笛卡尔积、内连接、左外连接、右外连接等概念，以及外键约束的使用方法，包括创建、动态删除、添加外键及外键约束的参照操作。

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

多表联查

多表查询，也叫链接查询

数据环境

SHOW CREATE TABLE `emp`;
| emp   | CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL COMMENT '用户名',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '18' COMMENT '年龄',
  `sex` enum('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
  `addr` varchar(20) NOT NULL DEFAULT '北京',
  `dep_id` tinyint(3) unsigned NOT NULL COMMENT '部门对应编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                |
SHOW CREATE TABLE `dep`;
| dep   | CREATE TABLE `dep` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `dep_name` varchar(50) NOT NULL,
  `dep_desc` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `dep_name` (`dep_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

SELECT * FROM emp;
+----+----------+-----+--------+--------+--------+
| id | username | age | sex    | addr   | dep_id |
+----+----------+-----+--------+--------+--------+
|  1 | tom      |  20 | 保密   | 北京   |      1 |
|  2 | john     |  23 | 保密   | 北京   |      2 |
|  3 | mike     |  20 | 保密   | 北京   |      3 |
|  4 | rose     |  23 | 保密   | 北京   |      2 |
|  5 | lili     |  20 | 保密   | 北京   |      4 |
|  6 | oven     |  23 | 保密   | 北京   |      1 |
+----+----------+-----+--------+--------+--------+

SELECT * FROM dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 技术部    | 研发         |
|  2 | 人事部    | 人事工作     |
|  3 | 财务部    | 财务工作     |
|  4 | 运营部    | 运营工作     |
+----+-----------+--------------+

-- 需求：查询 emp中的id,username,age dep表中的dep_name
复制代码

笛卡尔积的形式

-- 笛卡尔积的形式相当于两张表乘积
SELECT emp.id,emp.username,emp.age,dep.dep_name FROM emp,dep;

+----+----------+-----+-----------+
| id | username | age | dep_name  |
+----+----------+-----+-----------+
|  1 | tom      |  20 | 人事部    |
|  1 | tom      |  20 | 技术部    |
|  1 | tom      |  20 | 财务部    |
|  1 | tom      |  20 | 运营部    |
|  2 | john     |  23 | 人事部    |
|  2 | john     |  23 | 技术部    |
|  2 | john     |  23 | 财务部    |
|  2 | john     |  23 | 运营部    |
|  3 | mike     |  20 | 人事部    |
|  3 | mike     |  20 | 技术部    |
|  3 | mike     |  20 | 财务部    |
|  3 | mike     |  20 | 运营部    |
|  4 | rose     |  23 | 人事部    |
|  4 | rose     |  23 | 技术部    |
|  4 | rose     |  23 | 财务部    |
|  4 | rose     |  23 | 运营部    |
|  5 | lili     |  20 | 人事部    |
|  5 | lili     |  20 | 技术部    |
|  5 | lili     |  20 | 财务部    |
|  5 | lili     |  20 | 运营部    |
|  6 | oven     |  23 | 人事部    |
|  6 | oven     |  23 | 技术部    |
|  6 | oven     |  23 | 财务部    |
|  6 | oven     |  23 | 运营部    |
+----+----------+-----+-----------+
24 rows in set (0.00 sec)
复制代码

内连接的形式

查询两个表中符合连接条件的记录(取交集)

语法：SELECT 字段名称,... FROM 表名1 INNER JOIN 表名2 ON 连接条件 INNER JOIN 表名3 ON 连接条件...;(INNER 可以省略)

SELECT e.id,e.username,e.age,d.dep_name
FROM emp AS e
INNER JOIN dep AS d
ON e.dep_id = d.id;
+----+----------+-----+-----------+
| id | username | age | dep_name  |
+----+----------+-----+-----------+
|  1 | tom      |  20 | 技术部    |
|  2 | john     |  23 | 人事部    |
|  3 | mike     |  20 | 财务部    |
|  4 | rose     |  23 | 人事部    |
|  5 | lili     |  20 | 运营部    |
|  6 | oven     |  23 | 技术部    |
+----+----------+-----+-----------+
复制代码

外链接的形式

INSERT dep SET dep_name='法务部',dep_desc='法务工作';
INSERT emp SET username='nico',age=19,dep_id=10;
复制代码

左外连接

先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替

语法：SELECT 字段名,... FROM 表名1 LEFT OUTER JOIN 表名2 ON 条件;(OUTER 可省略)

SELECT e.id,e.username,e.age,d.dep_name,d.dep_desc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.dep_id = d.id;

+----+----------+-----+-----------+--------------+
| id | username | age | dep_name  | dep_desc     |
+----+----------+-----+-----------+--------------+
|  1 | tom      |  20 | 技术部    | 研发         |
|  6 | oven     |  23 | 技术部    | 研发         |
|  2 | john     |  23 | 人事部    | 人事工作     |
|  4 | rose     |  23 | 人事部    | 人事工作     |
|  3 | mike     |  20 | 财务部    | 财务工作     |
|  5 | lili     |  20 | 运营部    | 运营工作     |
|  7 | nico     |  19 | NULL      | NULL         |
+----+----------+-----+-----------+--------------+
复制代码

右外连接

先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替

语法：SELECT 字段名,... FROM 表名1 RIGHT OUTER JOIN 表名2 ON 条件;(OUTER 可省略)

SELECT e.id,e.username,e.age,d.dep_name,d.dep_desc
FROM emp AS e
RIGHT OUTER JOIN dep AS d
ON e.dep_id = d.id;

+------+----------+------+-----------+--------------+
| id   | username | age  | dep_name  | dep_desc     |
+------+----------+------+-----------+--------------+
|    1 | tom      |   20 | 技术部    | 研发         |
|    2 | john     |   23 | 人事部    | 人事工作     |
|    3 | mike     |   20 | 财务部    | 财务工作     |
|    4 | rose     |   23 | 人事部    | 人事工作     |
|    5 | lili     |   20 | 运营部    | 运营工作     |
|    6 | oven     |   23 | 技术部    | 研发         |
| NULL | NULL     | NULL | 法务部    | 法务工作     |
+------+----------+------+-----------+--------------+
复制代码

外键约束

只有INNODB存储引擎支持外键约束

  • 建表时指定外键

语法： [CONSTRAINT 外键名称]FOREIGH KEY (字段名) REFERENCES 表名(字段名); 外键名称可省略

子表的外键字段和父表字段类型要相似，如果是数值型要求一致，有无符号位也要一致，如果是字符型，要求类型一致，长度可以不同

如果外键字段没有创建索引，mysql会自动添加一个普通索引

子表的外键关联的必须是父表的主键

-- 创建父表
CREATE TABLE `news_cate` (
	`id` TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
	`cateName` VARCHAR (50) NOT NULL COMMENT '分类名称',
	`cateDesc` VARCHAR (100) NOT NULL DEFAULT '' COMMENT '分类描述',
	PRIMARY KEY (`id`),
	UNIQUE KEY `cate_name` (`cateName`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 创建子表
CREATE TABLE `news` (
	`id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
	`title` VARCHAR (100) NOT NULL COMMENT '新闻标题',
	`content` VARCHAR (1000) NOT NULL COMMENT '新闻内容',
	`cateId` TINYINT (3) UNSIGNED NOT NULL COMMENT '新闻所属分类id',
	PRIMARY KEY (`id`),
	UNIQUE KEY `title` (`title`),
	CONSTRAINT cateId__fk__news_cate FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入数据
INSERT news_cate (cateName)
VALUES
	('国内新闻'),
	('国际新闻'),
	('娱乐新闻'),
	('体育新闻');

INSERT news (title, content, cateId)
VALUES
	('a1', 'aaaa1', 1),
	('a2', 'aaaa2', 1),
	('a3', 'aaaa3', 4),
	('a4', 'aaaa4', 2),
	('a5', 'aaaa5', 3);
	

-- 当插入数据不符合要求时报错
INSERT news (title, content, cateId) VALUES ('a10', 'aaaa10', 10);
ERROR 1452 (23000):Cannot add or update a child row: a foreign key constraint fails (`test`.`news`, CONSTRAINT `cateId__fk__news_cate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
-- 删除父表或父表中的记录时，要确保被删除的内容没有被子表引用，有则先删除子表中的引用再做删除
-- 修改父表中的id时，要确保被修改的id没有被子表引用，有则先删除子表中的引用在做修改
复制代码
  • 动态删除、添加外键

删除外键 ：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

ALTER TABLE news DROP FOREIGN KEY cateId__fk__news_cate;
SHOW CREATE TABLE news;
+-----------------------------------------------------------------------
CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(3) unsigned NOT NULL COMMENT '新闻所属分类id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId__fk__news_cate` (`cateId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
+-----------------------------------------------------------------------
复制代码

添加外键 ：ALTER TABLE 表名 ADD FOREIGN KEY (外键字段名) REFERENCES 父表(主键字段名);

动态添加外键需要确保表中记录是合法的

ALTER TABLE news ADD CONSTRAINT cateId__fk__news_cate FOREIGN KEY (cateId) REFERENCES news_cate (id);
SHOW CREATE TABLE news;
+-----------------------------------------------------------------------
CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(3) unsigned NOT NULL COMMENT '新闻所属分类id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId__fk__news_cate` (`cateId`),
  CONSTRAINT `cateId__fk__news_cate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
+-----------------------------------------------------------------------
复制代码

外键约束的参照操作

  • CASCADE 级联操作 从父表中删除或者更新，子表也跟着删除或者更新
  • SET NULL 从父表中删除或更新记录，并设置字表的外键列为NULL 需要取消该列NOT NULL 约束
  • NO ACTION | RESTRICT 拒绝父表做更新和删除操作（默认）

语法： [CONSTRAINT 外键名称]FOREIGH KEY (字段名) REFERENCES 表名(字段名) ON DELET CASCADE ON UPDATE CASCADE;

转载于:https://juejin.im/post/5c1757c0e51d4546f83c8ff0

mysql 性别_MySQL中设置性别时想设置“男”，“女”，“保密”，而且要默认为“保密”的解决办法。...
weixin_35498756的博客
01-18 8306
比如在创建用户表的过程中，涉及到设置性别：CREATE TABLE imooc_user(id int unsigned auto_increment key,username varchar(20) not null unique,password char(32) not null,sex enum('保密','男','女')not null,face varchar(50) not null...
狂神MySQL笔记.rar
11-28
【狂神MySQL笔记】是一份全面且深入的MySQL学习资源，涵盖了从基础到高级的各种MySQL技术知识点。作为数据库领域的核心工具，MySQL因其高效、稳定、开源等特性，被广泛应用于各种规模的企业和项目中。这份笔记旨在...
mysql5.0基础语句_MySQL基础语句
weixin_39552304的博客
01-19 63
查看语句查看所有数据库show databases;查看表结构desc table_name;查看库中所有表show tables；查看建表语句show create table ;新建表语句新建表：id int unsigned not null auto_increment comment '用户id',uesr_name varchar(20) not null comment '用户名'...
MYSQL 唯一索引UNIQUE使用方法详解
dxyzhbb的博客
09-09 3415
创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE 把它定义为一个唯一索引。 创建唯一索的方法 操作表 代码如下 复制代码 CREATE TABLE wb_blog ( id smallint(8) unsigned ...
mysql中unique key中在查询中的使用
热门推荐
iCoding91
09-11 1万+
1、建表语句： CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `
mysql中key 、primary key 、unique key 与index区别
zccst的专栏
10-12 2181
作者：zccst [b]一、key与primary key区别[/b] CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varchar(100) default NULL, operation_time datetime default NULL, logreco...
MySQL 笔记
云满笔记
09-08 1445
MySQL 笔记
Mysql笔记二之连接虚拟机的数据库
m0_49047167的博客
09-16 2115
一、测试虚拟机自己的数据库访问情况，确保登录访问正常 mysql -u 用户名 -p回车，根据提示输入密码验证数据库登录情况，确保正常； 二、开启telnet client 1、打开：控制面板\所有控制面板项\程序和功能，在页面左侧位置点击启用或关闭windows功能，勾选telnet Client,系统会自己更新。 2、验证telnet指令验证 1）准备 验证前确保能ping 通虚拟机的IP 。 本人的虚拟机的IP为：192.168.0.90 若ping不通则参考文档： 物理机ping虚拟机及虚拟机访
MySQL笔记
亲测，只为展现最完美的有效！
08-09 668
MySQL笔记 https://github.com/guodongxiaren/MySQL-docs
mysql笔记
2302_76166597的博客
02-26 1666
mysql笔记
宋红康 MySQL高级篇 学习笔记
weixin_39408609的博客
07-18 1144
查询缓存：有就直接返回了。解析器进行解析：检查sql合不合语法 优化器：对sql语句进行逻辑优化，看是否使用索引，生成执行计划。存贮引擎：myisam ,innodb去执行上述计划 当然返回的时候 也会在缓存一下结果。
尚硅谷mysql笔记.zip
08-18
尚硅谷的MySQL笔记可能是为了帮助学习者深入理解和掌握MySQL的相关知识而编写的。这份笔记可能包含了从基础概念到高级特性的全面讲解，包括但不限于SQL语言、数据类型、数据库设计、索引、事务处理、视图、存储过程...
MySQL笔记，老杜，初级MySQL笔记
06-23
MySQL笔记，老杜，初级MySQL笔记
MySQL学习笔记 MySQL学习笔记
09-28
二、数据库相关概念 1. DBMS（数据库管理系统）：是用于创建、操作和管理数据库的软件，如MySQL、Oracle、SQL Server等。 2. DB（数据库）：是通过DBMS存储和管理的一系列有组织的数据集合。 3. SQL：是用于与数据库...
MySQL笔记(狂神说java)
09-25
"狂神说Java"的MySQL笔记可能深入探讨了如何在Java编程环境中与MySQL进行交互，这对于我们理解数据库管理和开发至关重要。以下是对这个主题的详细阐述： 1. **MySQL基础**： - 数据库概念：MySQL是一个用于存储和...
缓存雪崩、缓存穿透、缓存击穿在实际中如何处理
Rookie
08-19 1939
也有一个更为简单粗暴的方法（我们采用的就是这种），如果一个查询返回的数据为空（不管是数 据不存在，还是。缓存穿透是指查询一个一定不存在的数据，由于缓存是不命中时被动写的，并且出于容错考虑，如果从存储层查不。到数据则不写入缓存，这将导致这个不存在的数据每次请求都要到存储层去查询，失去了缓存的意义。缓存雪崩是指在我们设置缓存时采用了相同的过期时间，导致缓存在某一时刻同时失效，请求全部转发到。系统故障），我们仍然把这个空结果进行缓存，但它的过期时间会很短，最长不超过五分钟。所以我们把过期时间存在。
在职老D渗透日记day23:sqli-labs靶场通关（第29关-31关）http参数过滤
渗透学习&在职小白&每日督促更新
08-21 518
waf好像没有生效，先照着跑跑。
MySQL数据库第一章
2301_76169459的博客
08-20 1033
MySQL数据库概述 MySQL是一种开源免费的关系型数据库管理系统，具有小巧、功能齐全、使用便捷等特点，适用于中小型甚至大型应用。数据库分为关系型（如MySQL、Oracle）和非关系型（如Redis、MongoDB）两类。MySQL通过SQL语言进行操作，包含数据定义语言（DDL）、数据操作语言（DML）、数据查询语言（DQL）和数据控制语言（DCL）。创建数据表时需要指定列类型（数值、字符串、日期时间等）和字段属性，并通过命令行或工具进行管理。
prompt提示词2
最新发布
guo20082200的博客
08-22 253
太好了，我帮你整理一份 “提示词工程的架构师视角指南”，用你熟悉的架构思维把整个流程、模块、复用策略和优化方法系统化，方便你直接设计高效的提示词系统。提示词工程 = “AI任务系统设计”，你的模板库就像微服务，模块化、参数化、可复用，最终形成可自动化执行的量化分析工作流。[提示词模板库] → [模板组合与参数填充] → [AI模型执行层] → [输出管理与优化]全流程分析 → [数据处理, 技术指标计算, 策略分析, 可视化]• 模块类型、模板文本、占位符列表、描述、版本。
MySQL基础操作与笔记
"MySQL笔记，包含了数据库连接、库级操作、表级操作等基础概念和命令，以及创建、查看和修改表的示例。" 在MySQL数据库管理中，笔记涵盖了一系列核心概念和常用命令，这对于理解和操作MySQL至关重要。首先，笔记...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值