多表查询(含完整例子)

最新推荐文章于 2024-06-07 13:38:34 发布
最新推荐文章于 2024-06-07 13:38:34 发布
阅读量6.1k 收藏 115
点赞数 13
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/atu1111/article/details/105692652
版权

文章目录

多表查询

1.多表联合查询
1.1 语法
select  字段1,字段2... from 表1,表2... [where 条件]

  不加等值条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积

  笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积.

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

select * from person,dept

  上述语句查询结果：
在这里插入图片描述

1.2 等值连接

  等值条件直接进行查询,不会去重重复字段，新关系的记录数：两表相同字段相同值的记录数=∑n * m, n为A表某种与B表相同的值的个数，m为B表与A表相对应值的个数

select user.name,user.product_name,product.company from user,product where user.product_name = product.name

在这里插入图片描述

  上述语句查询结果：

在这里插入图片描述

2.多表连接查询
2.1 语法
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;
2.2 内连接查询

  含义：查询满足某一条件的A，B内部的数据，也即是得到的是A,B内部共有数据

  查询语句：

select * from person inner join dept on person.dept_id = dept.did

  效果如下：

在这里插入图片描述

  数学模型表示：
在这里插入图片描述

2.3 左连接查询

  含义：查询A的所有数据，和满足某一条件的B的数据

  查询语句：

select * from person left join dept on person.dept_id = dept.did

  效果如下：
在这里插入图片描述

  数学模型表示：
在这里插入图片描述

2.4 左连接查询 [where B.column is null]

  含义：查询A中的所有数据减去"与B满足同一条件 的数据"，然后得到的A剩余数据

  查询语句：

select * from person left  join dept on person.dept_id = dept.did where dept.did is null

  效果如下：

在这里插入图片描述
  数学模型表示：

在这里插入图片描述

2.5 右连接查询

  含义：B的所有数据，和满足某一条件的A的数据

  查询语句：

select * from person right  join dept  on person.dept_id = dept.did

  效果如下：
在这里插入图片描述
  数学模型表示：
在这里插入图片描述

2.6 联合查询

  含义：满足某一条件的公共记录，和独有的记录
  查询语句：

select * from person left  join dept on person.dept_id = dept.did
union 
select * from person right  join dept  on person.dept_id = dept.did

  效果如下：

在这里插入图片描述

  数学模型表示：

在这里插入图片描述

2.7 联合查询 [where column is null]

  含义：查询A，B中不满足某一条件的记录之和

  查询语句：

select * from person left  join dept on person.dept_id = dept.did where dept.did is null
union 
select * from person right  join dept  on person.dept_id = dept.did where person.dept_id is null

  效果如下：
在这里插入图片描述

2.8 交叉连接查询

  含义：查询A，B中不满足某一条件的记录之和
  语法：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

  查询语句：

select * from person cross  join dept

  效果如下：

在这里插入图片描述

  还可以为cross join指定条件 （where）：

select * from person cross  join dept on person.dept_id = dept.did

  效果如下：
在这里插入图片描述
  实际上实现了内连接的效果

2.9 子查询

  含义：子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

  语法：

WHERE <表达式> <操作符> (子查询)

  子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等。

  1）IN | NOT IN
  当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

  2）EXISTS | NOT EXISTS
  用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

  3）any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。some是any的别名，用法相同。

  4）all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

  例子：
   1.作为表名使用

select * from (select * from person) as 表名;

   求最大工资那个人的姓名和薪水例子

select t.name,t.salary from (select name,salary,max(salary) from person) as t

   运行效果：

在这里插入图片描述

   2.子查询包含=操作符

   求最大工资那个人的姓名和薪水例子

select name,salary  from person where salary = (select max(salary) from person)

在这里插入图片描述

   对比2.和1.的例子，可以得知1.例子是运行结果是错误的，错误的原因是因为select name,salary,max(salary) from person 并不是获取最大工资那个人的姓名和薪水，而是获取了最大工资，姓名只是获取第一行，而不是最大工资那行的姓名。

   3.子查询包含any 关键字

   假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，


select ...from ... where a > any(...);
->
select ...from ... where a > result1 or a > result2 or a > result3;

   求工资大于任意一个在3000-5000水平的人的姓名和薪水例子

select name,salary from person where salary > any(select salary from person where   salary >= 2000.00 and salary <= 5000.00)

在这里插入图片描述

   4.子查询包含all 关键字

   ALL关键字与any关键字类似，只不过上面的or改成and。即:

select ...from ... where a > all(...);
->
select ...from ... where a > result1 and a > result2 and a > result3;

   求工资大于在3000-5000水平的人的姓名和薪水例子

select name,salary from person where salary > all(select salary from person where   salary >= 2000.00 and salary <= 5000.00)

在这里插入图片描述

   4.子查询包含in 关键字
   查询部门是销售部和市场部的人员姓名和年龄的例子：

select * from person where dept_id in (select did from dept where dname in ('销售部','市场部'))

在这里插入图片描述

   5.子查询包含EXISTS 关键字

  查询是否存在市场部，如果存在，就查询出该部门人员中年龄大于20的记录例子：

select * from person where  exists(select dname from dept where dname ='市场部') and age >20  and dept_id = (select did from dept where dname ='市场部')

在这里插入图片描述

  所需表和数据

-- 创建部门
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


-- 添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);



DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` float(255, 0) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `company` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `product` VALUES ('1', 21, 'office工具', '金山');
INSERT INTO `product` VALUES ('2', 30, 'office工具', '微软');
INSERT INTO `product` VALUES ('3', 10, '报表', '帆软');
INSERT INTO `product` VALUES ('4', 1, '报表', '易数');
INSERT INTO `product` VALUES ('5', 12, 'apple手机', '苹果');
INSERT INTO `product` VALUES ('6', 14, 'huawei手机', '华为');
INSERT INTO `product` VALUES ('7', 15, '报表', '易晰');


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES ('1', 'tbb', 'china', '报表');
INSERT INTO `user` VALUES ('2', 'rose', 'Japen', 'office工具');
INSERT INTO `user` VALUES ('3', 'xm', 'English', '报表');
INSERT INTO `user` VALUES ('4', 'xb', 'chian', 'office工具');
INSERT INTO `user` VALUES ('5', 'ca', 'chian', 'huawei手机');
闲人不梦卿
关注
  • 13
    点赞
  • 踩
  • 115
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
Spring、SpringMVC和mybatis完整整合实例（含案例）
12-21
- Mapper接口：为每个数据库表创建对应的Mapper接口，这些接口定义了数据库操作方法，如查询、添加、更新和删除。 - Service层：创建Service类，它们处理业务逻辑，调用Mapper接口与数据库交互。 - Controller层...
多表查询(较难, 有例子)
csdn_yaohailong的博客
11-14 264
内连接查询 隐式内连接 显式内连接 内连接查询 外连接查询 左外连接 右外连接
left join on左连接的使用
change的博客
03-12 832
把left join左边的表的记录全部找出来。系统会先用表A和表B做个笛卡儿积，然后以表A为基表，去掉笛卡儿积中表A部分为NULL的记录。最后形成你的结果。–进行左连接时，就有涉及到主表、辅表，这时主表条件写在WHERE之后，辅表条件写在ON后面。...
mysql数据库笔记
最新发布
weixin_73596091的博客
06-07 833
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表的类型。默认的存储引擎为InnoDB（1）在建表时，指定存储引擎（2）查看当前数据库支持的存储引擎。
VFP数据库-查询
weixin_50494137的博客
06-10 1021
VFP数据库-查询
VFP学习_5 SQL查询语句
pigerr的博客
10-11 886
vfp 查询的三种方式: SQL语句 、 查询(设计器)、 视图(设计器)
数据库查询语句--子查询
weixin_53402685的博客
02-21 632
子查询就是在查询语句中插入查询语句形成条件要什么，子查询就写什么 语法： select 列名 from 表名 where 列名 in (select子查询语句); 或者 select 列名 from 表名 where 列名 = (select子查询语句); 比如我们要查询一个学生表中各个专业的第一名的学生的信息，我们要先知道每个专业的最高分，在根据最高分查询学生的信息，每个专业的最高分就是子查询，也就是条件。 例如：在下表中列出每个学院最高分的学生的信息 1.先得出各学院分数最高的是多少
VC++应用sqlite数据库（2012新版） 含例子 说明 源码
10-13
在这个"VC++应用sqlite数据库（2012新版）含例子 说明 源码"的资源中，我们可以找到关于如何在VC++项目中集成和使用SQLite数据库的相关知识。 首先，SQLite的版本"sqlite-shell-win32-x86-3071401"表明这是针对32位...
成语数据库(总共31851个成语,含拼音,解释,出处,例子)
09-03
在access版本中，数据库可能包含多个表，如“成语表”、“拼音表”、“解释表”、“出处表”和“例句表”。每个表都有相应的字段，如“成语ID”、“成语词目”、“拼音”、“解释”、“出处”和“例句”等。MySQL...
好例子网_JSP成长在线考试网源码(含数据库脚本) _数据开发_JSP成长在线考试网源码_
10-04
3. 数据库交互：通过JDBC（Java Database Connectivity）进行数据库操作，包括创建考试表、用户表、成绩表等，以及插入、查询和更新数据。 4. MVC（Model-View-Controller）架构：源码可能采用了MVC模式，分离了...
asp.net合并表头，实现多行表头
12-11
在这个例子中，我们创建了两行表头，第一行包含三个单元格，第二行包含六个单元格。每个单元格可以通过`Attributes.Add()`方法添加样式和属性，如`rowspan`、`colspan`以及背景颜色等，以实现单元格的跨行和合并。 ...
查询在表a中但不在表b中的数据
小程师兄啊
01-12 3187
表a 表b 查询在a中但不在b中的数据 a、b表，找出id字段中，存在a表，但是不存在b表的数据。 方法一： 子查询统计的是关联上的记录，等于0就是关联不上，就不存在(是方法三的三倍) select * from a where (select count(1) as num from b where b.id = a.id) = 0 方法二： select distinct a.id from a where a.id not in (select id from b); 方法三： left join…o
要将两个查询条件的结果进行表关联
weixin_45427945的博客
09-01 244
要将两个查询条件的结果进行表关联
计算机二级vf知识点总结,2015年计算机二级考试《VFP》复习重点:第六章
weixin_28724661的博客
06-20 235
8.在Visual FoxPro 中，嵌套查询是基于____的查询。A.一个表B.多个表C.一个关系D.多个关系[解析] 本题考查的知识点是嵌套查询。在Visual FoxPro 中，嵌套查询是基于多个关系的查询。因此正确答案为D。[答案] D9. 嵌套查询命令中的IN 相当于____。A.等号=B.集合运算符∈C.加号 +D.减号 -[解析] 本题考查的知识点是嵌套查询命令中的参数。嵌套查询命令...
SQL查询一个表中另外一个表不存在的数据。
weixin_30645617的博客
11-16 516
#方法一：使用 not in ,容易理解,效率低 ~执行时间为：1.395秒~ SELECT COUNT(1) FROM ecs_goods WHERE ecs_goods.goods_id NOT IN (SELECT ecs_member_price.goods_id FROM ecs_member_price); #方法二：null" 表示左连接之后在B.ID 字段为...
一个文本框查询多个字段
weixin_44484621的博客
07-18 2430
开发工具与关键技术：VS、 作者：#33 撰写时间：撰写时间：2019年07月17日 一个文本框查询一个字段的方法太麻烦，做项目中遇到的一个文本框查询多个表多个字段的功能。如下面的仓库表和发货表内容的查询：查询仓库名称、编号、备注、联系人、电话 查询结果： 控制器查询方法：传参(WarehouseName),通过参数得到的值进行模糊查询 再加上条件语句（下面红色框）用“||”分...
Sql 4表多表查询例子
05-10
假设我们有以下四个表： - Students: 学生信息表，包括学生的ID、姓名、年龄、性别等； - Courses: 课程信息表，包括课程的ID、名称、学分、教师等； - Scores: 成绩信息表，包括学生ID、课程ID、成绩等； - Teachers: 教师信息表，包括教师ID、姓名、性别、所属学院等。 我们要查询每个学生的姓名、年龄、所选课程及成绩、授课教师的姓名和学院，可以使用以下SQL语句： ``` SELECT Students.name, Students.age, Courses.name, Scores.score, Teachers.name, Teachers.college FROM Students INNER JOIN Scores ON Students.id = Scores.student_id INNER JOIN Courses ON Scores.course_id = Courses.id INNER JOIN Teachers ON Courses.teacher_id = Teachers.id; ``` 该语句中，我们使用了INNER JOIN进行多表连接，将四个表连接起来，并使用SELECT语句选取我们需要的列。通过使用ON关键字，我们指定了连接条件。其中，Students.id = Scores.student_id表示连接Students表和Scores表时，以学生ID作为连接条件；Scores.course_id = Courses.id表示连接Scores表和Courses表时，以课程ID作为连接条件；Courses.teacher_id = Teachers.id表示连接Courses表和Teachers表时，以教师ID作为连接条件。 这样，我们就可以得到每个学生的姓名、年龄、所选课程及成绩、授课教师的姓名和学院。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值