MySQL笔记（五）：多表查询

最新推荐文章于 2024-08-06 17:49:28 发布
最新推荐文章于 2024-08-06 17:49:28 发布
阅读量197 收藏 2
点赞数 4
文章标签： mysql 笔记 android
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SSuperwoman/article/details/140931975
版权

一、查询增强

对单表进行加强查询

#查询加强

-- 1\使用WHERE语句  对1991.12.1后入职的员工进行查询
	-- 日期类型可以直接比较
SELECT * FROM emp
	WHERE hiredate>'1991-12-01';
	
-- 2\like 模糊查询
	-- %：表示0到任意字符 _:表示单个任意字符   '
	-- 显示首字符为s的员工姓名和工资  'S%'：S在前，以S开头，'%S'：S在后，以S结尾
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%';
	
-- 如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE '__O%';
	

-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;/*不能写mgr = null*/

-- 查询表结构
DESC emp;

#order by 语句
-- 如何按照工资从低到高的顺序显示雇员信息
SELECT * FROM emp ORDER BY sal;

-- 按照部门号升序而（部门内部）雇员工资降序排列，显示雇员信息
SELECT * FROM emp
	ORDER BY deptno ASC,
	sal DESC;

二、分页查询

在这里插入图片描述

#分页查询
-- 按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页
-- 第1页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3;
	
-- 第2页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3;
	
-- 第3页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6,3;
	
-- 第5页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 12,3;
	
-- 公式：
-- 不能直接写公式 每页显示记录数*(第几页-1)，要算出结果
-- SELECT * FROM emp
-- 	ORDER BY empno
-- 	LIMIT 每页显示记录数*(第几页-1),每页显示记录数;

-- 按雇员的empno号降序输出，每页显示5条记录，请分别显示第3页，第5页对应的sql语句
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 10,5;

SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20,5;

三、分组增强

在这里插入图片描述

#分组增强
-- (1)显示每种岗位的雇员总数、平均工资
SELECT COUNT(*),AVG(sal),job FROM emp
	GROUP BY job;
	
SELECT * FROM emp;
-- (2) 显示雇员总数，以及获得补助的雇员数


-- count（列）：如果列的值为NULL,是不会统计的	
SELECT COUNT(*),COUNT(comm) FROM emp
	GROUP BY job;


-- 统计没有获得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL)) FROM emp
	GROUP BY job;
SELECT COUNT(*),COUNT(*)-COUNT(comm) FROM emp
	GROUP BY job;
	
-- (3)显示管理者的总人数
SELECT COUNT(mgr) FROM emp; /*这样写会重复*/
SELECT COUNT(DISTINCT mgr) FROM emp;

-- (4) 显示雇员工资的最大差额
SELECT MAX(sal)-MIN(sal) FROM emp;

四、多子句查询

-- 应用案例：估计各个部门group by 的平均工资avg,并且是大于1000的having,并且按照平均工资从高到低排序，order by 取出前两行激励limit
SELECT  deptno,AVG(sal) AS avg_sal FROM emp
	GROUP BY deptno
	HAVING avg_sal>1000
	ORDER BY avg_sal DESC /*只能在这里order by 在查询完了之后排序*/
	LIMIT 0,2;

五、多表查询

在这里插入图片描述
在这里插入图片描述

在默认情况下：当查询两个表时，规则：
1、从表1中，去除一行跟第二张表的每一条记录做拼接，返回这个结果，结果含有两张表的所有列
2、一共返回的记录数，表1的行数*表2的行数
3、这个默认的结果，为笛卡尔集
4、解决多表的关键是写出正确的过滤条件where，需要分析

SELECT ename,sal,dname,emp.deptno  /*对于两个表中都有的列，需要标注是哪个表的列*/
	FROM emp,dept
	WHERE emp.deptno=dept.deptno;

在这里插入图片描述

#多表查询
-- 1、显示雇员民，雇员工资（emp表）及所在部门的名字（dept表）（笛卡尔集）
SELECT * FROM emp,dept;

SELECT ename,sal,dname,emp.deptno  /*对于两个表中都有的列，需要标注是哪个表的列*/
	FROM emp,dept
	WHERE emp.deptno=dept.deptno;
	
-- 2、显示部门为10的部门名、员工名和工资
SELECT dname,ename,sal,emp.deptno 
	FROM emp,dept
	WHERE emp.deptno=dept.deptno AND emp.deptno=10;

SELECT * FROM salgrade;
-- 3、显示各个员工的姓名，工资、工资级别
SELECT *
	FROM emp,salgrade /*只有sal落在对应级别的losal和hisal之间的那一行才有效*/
SELECT ename,sal,grade
	FROM emp,salgrade
	WHERE sal BETWEEN losal AND hisal;
	
-- 4\显示雇员名，工资和所在部门名字，并按部门排序
SELECT ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY emp.deptno DESC;

六、自连接

自连接：指在同一张表的连接查询【将同一张表看作两张表】

#多表查询的自连接

– 显示公司员工名字和他的上级的名字
– 员工和上级名字在ename，通过mgr连接

#自连接的特点：1.一张表当程两张表使用

2.需要给表取别名 表明表别名

3.列名不明确，需要取别名

SELECT worker.ename AS ‘职员名’,boss.ename AS ‘上级名’
FROM emp worker,emp boss
WHERE worker.mgr=boss.empno;

SELECT * FROM emp;

七、子查询

(1)基础嵌套子查询

什么是子查询
子查询是指嵌入在其他aql语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子查询
指返回多行数据的子查询，使用关键字in

#多行子查询
SELECT * FROM emp;
-- 显示与smith同一部门的所有员工
SELECT * FROM emp
	WHERE deptno = (SELECT deptno FROM emp 
		WHERE ename='SMITH');
		


-- 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10自己
SELECT ename,job,sal,deptno
	FROM emp
	WHERE job IN (SELECT DISTINCT job FROM emp
		WHERE deptno=10) AND
		deptno != 10; /*!= 也可以写成<>*/

（2）子查询临时表
这一部分我没有表，直接看第47集

（3）mysql_all和any

#all和any操作符
-- 显示工资比30部门的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
	FROM emp
	WHERE sal>ALL(SELECT sal 
			FROM emp
			WHERE deptno=30);
			


SELECT ename,sal,deptno
	FROM emp
	WHERE sal> (SELECT MAX(sal) 
			FROM emp
			WHERE deptno=30);
			
-- 显示工资比30部门的其中一个员工的工资高的员工的姓名、工资和部门号

SELECT ename,sal,deptno
	FROM emp
	WHERE sal> ANY(SELECT sal
			FROM emp
			WHERE deptno=30);
			
SELECT ename,sal,deptno
	FROM emp
	WHERE sal> (SELECT MIN(sal)
			FROM emp
			WHERE deptno=30);

(4)多列子查询
多列子查询：查询返回多个列数据的子查询语句

#多列子查询
#(字段1，字段2，...)=select 字段1，字段2，... from 。。。
-- 查询与SMITH的部门和岗位完全相同的所有雇员（不含simth)
SELECT deptno,job
	FROM emp
	WHERE ename = 'ALLEN'; 
SELECT * 
	FROM emp
	WHERE (deptno,job)=(SELECT deptno,job
				FROM emp
				WHERE ename = 'ALLEN')
		AND ename <> 'ALLEN';

八、表复制

（1）自我复制数据（蠕虫复制）
为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

#表复制

CREATE TABLE my_tab01(
	id INT,
	ename VARCHAR(32),
	sal DOUBLE,
	job CHAR(32),
	deptno INT);

DESC my_tab01
SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1、先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
	(id,ename,sal,job,deptno)
	SELECT empno,ename,sal,job,deptno FROM emp;

-- 2\自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;
	
SELECT COUNT(*) FROM my_tab01;


-- 删除表的重复记录
-- 1、先创建一张表my_tab02
-- 2、让my_tab02有重复的记录
CREATE TABLE my_tab02 LIKE emp;  -- 这个语句 把 emp 表的结构（列），复制到my_tab02;
DESC my_tab02
SELECT * FROM my_tab02;
INSERT INTO my_tab02
	SELECT * FROM emp;
-- 3\去重
-- 创建临时表my_tmp,该表的结构与my_tab02相同->把my_tab02的记录通过distinct关键字处理后把记录复制到my_tmp->清除my_tab02的记录->把my_tmp复制到my_tab02->drop my_tmp
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp
	SELECT	DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02 SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM my_tab02;

九、合并查询

合并多个select语句
union all
：取得两个结果集的并集，不会取消重复行

#合并语句
SELECT ename,sal,job FROM emp WHERE sal>2500;

SELECT ename,sal,job FROM emp WHERE job='manager'

#union all 合并不去重
SELECT ename,sal,job FROM emp WHERE sal>2500 

UNION ALL

SELECT ename,sal,job FROM emp WHERE job='manager'

#union :合并去重
SELECT ename,sal,job FROM emp WHERE sal>2500 

UNION 

SELECT ename,sal,job FROM emp WHERE job='manager'

十、表外连接

在这里插入图片描述
在这里插入图片描述

#表外连接

SELECT	dname,ename,job
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY dname;


-- 创建stu

CREATE TABLE stu3(
	id INT,
	tname VARCHAR(32));

INSERT INTO stu3 
	VALUES(1,'jack'),(2,'tom'),(3,'kitty'),(4,'nono');

SELECT * FROM stu3;

-- 创建exam
CREATE TABLE exam(
	id INT,
	grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);



-- 不小心添加了两次，联系一下去重
CREATE TABLE tmpex LIKE exam;
INSERT INTO tmpex  
	SELECT DISTINCT * FROM exam;
DELETE FROM exam;
INSERT INTO exam 
	SELECT * FROM tmpex;
DROP TABLE tmpex;


-- 使用左连接
	-- 显示所有人的成绩，如果没有成绩，也要显示id和姓名
SELECT tname,stu.id,grade
	FROM stu3,exam
	WHERE stu3.id=exam.id;
	
-- 改成左外连接 
-- select .. from 表1 left join 表2 on 条件【表1：左表，表2：右表】
SELECT tname,stu3.id,grade
	FROM stu3 LEFT JOIN exam
	ON stu3.id=exam.id;
	
-- 右外连接
-- select .. from 表1 right join 表2 on 条件【表1：左表，表2：右表】
SELECT tname,stu3.id,grade 
SELECT tname,stu3.id,grade
	FROM stu3 RIGHT JOIN exam
	ON stu3.id=exam.id;
	
-- 列出部门名称和这些部门的员工信息（名字，和工作），同时列出没有员工共的部门名
-- 1、使用左外连接
SELECT ename,job,dname
	FROM dept LEFT JOIN emp
	ON dept.deptno=emp.deptno;
	
-- 2、使用右外连接
SELECT ename,job,dname
	FROM emp RIGHT JOIN dept
	ON emp.deptno=dept.deptno;
King without kingdom
关注
  • 4
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL笔记：B站康师傅天花板教程(持续更新)
分享思想，留下痕迹。
03-30 3万+
✅作者简介：大家好，我是Cisyam，热爱Java后端开发者，一个想要与大家共同进步的男人😉😉💞当前专栏：✨特色专栏：🥭本文内容：MySQL笔记：B站康师傅天花板教程(持续更新)，欢迎大家访问📚个人知识库：，欢迎大家访问B站康师傅MySQL天花板教程笔记： 最详细，最全面，最硬核！！！下面是对章节与视频的对应关系和学习建议【MySQL上篇：基础篇】【第1子篇：数据库概述与MySQL安装篇】p01-p11。
MySQL学习笔记：组合查询
01-19
本文为本人学习书籍《MySQL必知必会》笔记系列，欢迎持续关注，有问题随时留言评论，一起探讨学习~ 17组合查询 17.1 使用UNION操作符将多条SELECT语句组合成一个结果集。 查找Price1>5000的商品 SELECT id,name,...
MySQL学习笔记：JSON嵌套数组查询
山鬼谣的专栏
06-07 6356
MySQL 5.7简单JSON串就不说了，很简单，来个复杂点的： 在MySQL数据库中，它长这个样子：网上很多都是先对象，然后再嵌套数组，但是我的数据先是个数组，然后嵌套对象，然后再嵌套数组。这种情况下，该如何查询呢？现在想查询，JSON串中城市字段是上海的。............
MySQL的多表关联查询
星霖的博客
11-07 1万+
MySQL学习笔记
MySQL笔记13：查询结果集
魏波
01-11 6447
操作mysql - 查询结果集 1.用mysql进行数据查询的时候，mysql会返回一个结果集给我们，接着我们需要调用mysql的API，从这个结果集中取得我们要的数据。取完数据之后，需要释放这个结果集。 2.mysql的结果集其实就是一个MYSQL_RES结构，其定义如下： typedef struct st_mysql_res { my_ulonglong row_count
【mysql】外键与多表查询
前端学习博客
07-12 4530
这里是小飞侠Pan🥳，立志成为一名优秀的前端程序媛！！！本篇博客收录于我的github前端笔记仓库中，持续更新中，欢迎star~👉https://github.com/mengqiuleo/myNote我们可以使用外键将两个表连接起来比如现在有两张表，一张是products一张是brand：此时我们使用外键将两个表连接起来语法： 然后我们现在给新增的外键赋值 此时我们的products表中的外键就有了值我们来思考一个问题：我们来进行一个更新操作：比如将华为的id更新为100 这个时候执行代码是报错的。如..
MySQL- 多表查询(上)
一起努力共同进步，为了未来一起奋斗吧！
05-21 4268
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库
MySQL学习笔记1：数据库概述（尚硅谷）
qq_43101414的博客
02-22 3199
MySQL学习系列1
【MySQL笔记】多表查询（JOIN ON）
热门推荐
lijibai_的博客
05-03 1万+
多表查询，也称关联查询，指两个或更多个表一起完成查询操作 目录 多表查询的前提条件 多表查询的基本语法 多表查询的分类 等值连接与非等值连接 自连接与非自连接 内连接与外连接（JOIN ON） 现在我们假设有两个表，employees 和 departments，它们之中有一些重复的字段 多表查询的前提条件 这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如员工表与部门表之间依靠 “部门编号” 进行关联
MySql学习笔记（五） - 多表查询
Code-Zhao
06-04 1174
1. 子查询    即嵌套在其他查询中的查询。SELECT与剧中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySql实际上执行了两个操作。首先执行：SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'次查询返回两个订单号 20005，20007。然后这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部...
MySQL数据库—查询：关联查询（一篇教会你在多表关联下查询数据）
小谷同学的博客
05-07 2577
一篇教会你快速且准确的在多张关联表之间查询到想要的数据！
MySQL学习笔记3：表的基本操作介绍
09-10
### MySQL学习笔记3：表的基本操作介绍 #### 一、选择数据库 在进行任何与表相关的操作之前，首先需要选择一个数据库。这是因为表是存储在特定数据库中的。选择数据库可以通过以下命令实现： ```sql mysql> use ...
MySQL学习笔记：插入数据
12-14
通过`INSERT INTO SELECT`语句，可以从一个或多个表中选取数据并插入到另一个表中。这在数据迁移或整合时非常有用。例如： ```sql INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state...
MySQL学习笔记5：修改表(alter table)
09-10
在MySQL数据库管理中，`ALTER TABLE`语句是用于修改已存在的表结构的关键命令。它允许用户在不丢失数据的情况下对表进行各种调整，包括修改字段名称、数据类型、添加或删除字段，以及更改字段的位置等。下面我们将...
认识MySQL
m0_51821434的博客
08-06 268
这篇文章可以让你了解数据库，不在懵懵懂懂！
尚硅谷谷粒商城项目笔记——六、使用navciat连接docker中的mysql容器【电脑CPU：AMD】
B6665X的博客
08-06 105
尚硅谷谷粒商城项目笔记——六、使用navciat连接docker中的mysql容器【电脑CPU：AMD】
MYSQL主库切换binlog模式后主从同步错误
最新发布
weixin_45626288的博客
08-06 458
在使用FlinkSQL的mysql-cdc连接器监听MySQL数据库时，确保binlog模式为ROW是必要的。但在切换binlog模式时，需要特别注意主从库的数据一致性和同步状态。
2024.8.2（MySQL）
weixin_70751278的博客
08-02 1460
2024-08-02T01:49:19.435048Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dQ1o<YO&YNyX 密码。语法：create table user( id int primarykey,username varchar，password varchar );#insert into 表名 (字段名列表) values (字段值列表)2.5.2 修改列名。
Java面试题:MySQL高频面试题
qq_55630615的博客
08-06 117
MySQL为什么选择B+树来存储索引？层数低,可以存放的数据多。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值