MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)

这篇具有很好参考价值的文章主要介绍了MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1. 内连接查询

数据准备:

use mydb3;

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
 
-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

内连接查询语法:

-- 语法:
-- 隐式内连接(SQL92标准):select * from A,B where 条件; 
-- 显示内连接(SQL99标准):select * from A inner join B on 条件;

-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
 
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
 
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

-- 查询人数大于等于3的部门,并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

2. 外连接查询

语法: 左外连接:left outer join:

            select * from A left outer join B on 条件;

MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)

            右外连接:right outer join:

            select * from A right outer join B on 条件;

MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)

            满外连接: full outer join:

            select * from A full outer join B on 条件;

MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)

-- 外连接查询
-- 查询哪些部门有员工,哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- 查询哪些员工有对应的部门,哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
 
 
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

3. 子查询

子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询。通俗一点就是包含select嵌套的查询。

子查询可以返回的数据类型一共分为四种:

单行单列:返回的是一个具体列的内容,可以理解为一个单值数据;

单行多列:返回一行数据中多个列的内容;

多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围;

多行多列:查询返回的结果是一张临时表。

-- 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
 
 
-- 查询年研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
 
 
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字,部门名字
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2

ALL关键字:

与子查询返回的所有值比较为true 则返回true。

ALL可以与=>>=<<=<>结合是来使用,分别表示等于、大于、大于等于、小于、小于

于、不等于其中的所有数据。

ALL表示指定列中的值必须要大于子查询集的每一个值,即必须要大于子查询集的最大值;如果是

小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况

-- 语法:
-- select …from …where c > all(查询语句)
-- 等价于:
-- select ...from ... where c > result1 and c > result2 and c > result3

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3); 

ANY与SOME关键字:

与子查询返回的任何值比较为true,则返回true。

ANY可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于

等于、不等于其中的任何一个数据。

表示指定列中的值要大于子查询中的任意一个值,即必须要大于子查询集中的最小值。同理可以推

出其它的比较运算符的情况。

SOME和ANY的作用一样,SOME可以理解为ANY的别名。

-- 语法:
-- select …from …where c > any(查询语句)
-- 等价于:
-- select ...from ... where c > result1 or c > result2 or c > result3

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

IN关键字:

IN关键字,用于判断某个记录的值,是否在指定的集合中。

在IN关键字前边加上not可以将条件反过来。

-- 语法:
-- select …from …where c in(查询语句)
-- 等价于:
-- select ...from ... where c = result1 or c = result2 or c = result3

-- 查询研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

EXISTS关键字:

该子查询如果“有数据结果”(至少返回一行数据), 则该EXISTS() 的结果为“true”,外层查询执行

子查询如果“没有数据结果”(没有任何数据返回),则该EXISTS()的结果为“false”,外层查询

不执行。

EXISTS后面的子查询不返回任何实际数据,只返回真或假,当返回真时 where条件成立。

EXISTS关键字,比IN关键字的运算效率高,因此,在实际开发中,特别是大数据量时,推

荐使用EXISTS关键字。

-- 语法:
-- select …from …where exists(查询语句)

-- 查询公司是否有大于60岁的员工,有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
 
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

4. 自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当

成多张表来用。

注意自关联时表必须给表起别名。

-- 语法:
-- select 字段列表 from 表1 a , 表1 b where 条件;
-- 或者 
-- select 字段列表 from 表1 a [left] join 表1 b on 条件;

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);

-- 添加数据 
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
 
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息,如:  关羽  刘备 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

(日常美图时间)

MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)

 文章来源地址https://www.toymoban.com/news/detail-444905.html

到了这里,关于MySQL---多表联合查询(下)(内连接查询、外连接查询、子查询(ALL/ANY/SOME/IN/EXISTS关键字)、自关联查询)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 五、多表查询-3.4连接查询-联合查询union

    1、查询薪资低于5000的员工  2、查询年龄大于50岁的员工  3、将薪资低于5000的员工,和 年龄大于50岁的 员工全部查询出来(把上面两部分的结果集直接合并起来)  4、 去重 ,“鹿杖客”既薪资低于5000,年龄也大于50岁,所以有两条数据 union all 改为 union,即可去重    5、

    2024年02月11日
    浏览(59)
  • MySQL-多表联合查询

     🎉欢迎您来到我的MySQL基础复习专栏 ☆* o(≧▽≦)o *☆哈喽~我是小小恶斯法克🍹 ✨博客主页: 小小恶斯法克的博客 🎈该系列文章专栏: 重拾MySQL 🍹文章作者技术和水平很有限,如果文中出现错误,希望大家能指正🙏 📜 感谢大家的关注! ❤️ 目录 🚀联合查询 🚀子

    2024年01月17日
    浏览(47)
  • 连接查询(多表查询)——MySQL

    又称多表查询,当 查询的字段涉及多个表 的时候,就要用到连接查询 分类: 为表起别名: 提高语句的简洁度 区分多个重名字段 注意 :如果为表起了别名,则查询的字段就 不能使用原来的别名去限定 查询A、B 交集部分数据 语法: 隐式内连接 select 字段列表 from 表1,表

    2024年02月04日
    浏览(48)
  • MySQL ——多表连接查询

    内连接 : 假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来。A和B两张表没有主付之分,两张表是平等的。 :inner join on 语句:select * from a_table a inner join b_table b on a.a_id = b.b_id; 说明:组合两个表中的记录,返回关联字段相符的记录,也就

    2024年04月16日
    浏览(55)
  • MySQL每日一练:多表查询——连接查询、子查询

    目录 1、首先创建员工表emp和部门表dept: dept表: emp表:  2、插入数据: dept表: emp表: 3、 按条件查找 dept表: emp表:  dept表: emp表: 1.找出销售部门中年纪最大的员工的姓名  2.求财务部门最低工资的员工姓名  3.列出每个部门收入总和高于8000的部门名称   4.求工资在

    2024年02月15日
    浏览(37)
  • MySQL单表查询与多表连接查询

    单表查询 创建数据表emp 插入数据 -- 1. 查询出部门编号为30的所有员工 -- 2. 所有销售员的姓名、编号和部门编号。 -- 3. 找出奖金高于工资的员工。 -- 4. 找出奖金高于工资60%的员工。 -- 5. 找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料。 -- 6. 找出部门

    2024年02月13日
    浏览(44)
  • ⑧【MySQL】数据库查询:内连接、外连接、自连接、子查询、多表查询

    个人简介:Java领域新星创作者;阿里云技术博主、星级博主、专家博主;正在Java学习的路上摸爬滚打,记录学习的过程~ 个人主页:.29.的博客 学习社区:进去逛一逛~ 多表关系 : 一对一 :在 任意一方 加入外键,关联另一方的主键,并设置外键为唯一(UNIQUE)。 一对多(

    2024年02月05日
    浏览(54)
  • MySQL每日一练:多表查询——连接查询、子查询(1)

    目录 1、首先创建员工表emp和部门表dept: dept表: emp表:  2、插入数据: dept表: emp表: 3、 按条件查找 dept表: emp表:  dept表: emp表: 1.找出销售部门中年纪最大的员工的姓名  2.求财务部门最低工资的员工姓名  3.列出每个部门收入总和高于8000的部门名称   4.求工资在

    2024年02月15日
    浏览(37)
  • 【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自连接 | 子查询 | 合并查询

    「前言」文章内容大致是对MySQL复合查询的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 前面篇章讲解的mysql表的查询都是对一张表进行查询,在实际开发中这远远不够,下面将讲解复合查询,首先回顾一下基本的查询。 使用的数据库是之前篇章的雇

    2024年02月11日
    浏览(50)
  • 【MYSQL】聚合函数和单表/多表查询练习、子查询、内外连接

    目录 1.聚合函数 1.1.group by子句 1.2.having语句 2.单表查询  2.2单表查询 3.多表查询  3.2.子查询 5.内链接 6.外连接 函数 说明 count 返回查询到的数据的数量 sum 返回查询到的数据的总和 avg 返回查询到的数据的平均值 max 返回查询到的数据的最大值 min 返回查询到的数据的最小值

    2024年02月04日
    浏览(48)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包