MySQL中连接查询和子查询的问题

目录
  • 多表连接的基本语法
  • 交叉连接和笛卡尔积现象
    • 交叉连接
    • 笛卡尔积现象
  • 内连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 子查询

多表连接的基本语法

多表连接，就是将几张表拼接为一张表，然后进行查询

select 字段1, 字段2, ...
from 表1 {inner|lift|right} join 表2
on 连接条件;

有如下两张表：部门表和员工表

交叉连接和笛卡尔积现象

交叉连接

交叉连接，又名无条件内连接/笛卡尔连接

第一张表种的每一项会和另一张表的每一项依次组合

select * from employee,department;

上述结果肯定不是我们想晓得的，左表中每一个人都有4个部门，仔细观察这4条记录，正好是左表每一条记录和右表一一匹配后的结果。

笛卡尔积现象

笛卡尔积现象产生的原因：两张表没有有效的连接条件。既然你没有连接条件，本表中的第一行肯定是能和另外表中的所有行进行一一匹配，同理，本表中的第二行肯定是能和另外表中的所有行进行一一匹配，以此类推，本表中的最后一行m也可以和另外表中的所有行进行一一匹配。若另外一张表有n行，那么最后显示的行数，肯定就是m*n行了。

如果不想产生笛卡尔积现象，就需要添加有效的表连接条件。拿上述例子来说，左表dep_id只有和右表id相等时，才代表他们的部门。

内连接

内连接(INNER JOIN)是找几张表的交集，即根据条件筛选出来正确的结果。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp INNER JOIN department as dep
on emp.dep_id=dep.id;

由于部门表中没有id=5的部门，所以员工表dep_id=5的这条记录没有返回；而由于行政部没有员工，所以这条记录也没返回。

外连接

左外连接

左连接(left join)是以左表为准，如果右表中没有合适的记录，用NULL补全；其本质是在内连接的基础上增加左表有结果而右表没有的记录(内连接时，这种情况的记录会忽略)。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id;

右外连接

跟左连接正好相反，右连接(right join)是以右表为准，如果左表中某些字段没有合适的结果，用NULL补全；其本质是在内连接的基础上增加右表有结果而左表没有的记录(内连接时，这种情况的记录会忽略)。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id;

全外连接

全外连接，在内连接的基础上，展示左右表的所有的记录，而左右表中缺省记录以NULL补全。

MySQL中并没有全外连接的FULL JOIN语法，而是借助UNION/UNION ALL语句实现。

UNION和UNION ALL的区别，UNION具有去重功能。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id
union
select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id;

子查询

  • 子查询是将一个查询语句嵌套再另一个查询语句中的查询方式：
  • 子查询的内层查询结果，可以作为外层查询语句提供查询条件。
  • 子查询中可以包含IN、NOT IN、AND、ALL、EXISTS、NOT EXISTS等关键字。

子查询中还可以包含比较运算符，如=、!=、>、<等。

-- 查询平均年龄在20以上的部门名称
select name
from department
where id in (
select dep_id
from employee
group by dep_id
having avg(age) > 20);

-- 查询财务部员工姓名
select name
from employee
where dep_id in (
select id
from department
where name='财务部');

-- 查询所有大于平均年龄的员工的年龄和姓名
select name,age
from employee
where age > (
select avg(age) from employee);

到此这篇关于MySQL中连接查询和子查询的问题的文章就介绍到这了,更多相关MySQL连接查询和子查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL子查询操作实例详解

    本文实例总结了MySQL子查询操作.分享给大家供大家参考,具体如下: 定义两个表tb1和tb2 CREATE table tbl1 ( num1 INT NOT NULL); CREATE table tbl2 ( num2 INT NOT NULL); 向两个表中插入数据: INSERT INTO tbl1 values(1), (5), (13), (27); INSERT INTO tbl2 values(6), (14), (11), (20); any some关键字的子查询 SELE

  • mysql实现多表关联统计(子查询统计)示例

    mysql实现多表关联统计(子查询统计)示例

    本文实例讲述了mysql实现多表关联统计的方法.分享给大家供大家参考,具体如下: 需求: 统计每本书打赏金额,不同时间的充值数据统计,消费统计, 设计四个表,book 书本表,orders 订单表  reward_log打赏表   consume_log 消费表 ,通过book_id与book表关联, 问题: 当关联超过两张表时导致统计时数据重复,只好用子查询查出来,子查询只能查一个字段,这里用CONCAT_WS函数将多个字段其拼接 实现: 查询代码如下 SELECT b.id, b.book_

  • mysql连接查询（左连接，右连接，内连接）

    一.mysql常用连接 INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录. LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录. RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录. mysql> select * from name_address; +----------+------+----+ | address | name | id | +----------+------

  • 详解MySql基本查询、连接查询、子查询、正则表达查询

    详解MySql基本查询、连接查询、子查询、正则表达查询

    查询数据指从数据库中获取所需要的数据.查询数据是数据库操作中最常用,也是最重要的操作.用户可以根据自己对数据的需求,使用不同的查询方式.通过不同的查询方式,可以获得不同的数据.MySQL中是使用SELECT语句来查询数据的.在这一章中将讲解的内容包括. 1.查询语句的基本语法 2.在单表上查询数据 3.使用聚合函数查询数据 4.多表上联合查询 5.子查询 6.合并查询结果 7.为表和字段取别名 8.使用正则表达式查询 什么是查询? 怎么查的? 数据的准备如下: create table STUD

  • MySQL查询优化:用子查询代替非主键连接查询实例介绍

    一对多的两张表,一般是一张表的外键关联到另一个表的主键.但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联. 例如: 复制代码 代码如下: create table t_team ( tid int primary key, tname varchar(100) ); create table t_people ( pid int primary key, pname varchar(100), team_name varchar(100) ); team表和people表是一对多的关

  • mysql连接查询、联合查询、子查询原理与用法实例详解

    本文实例讲述了mysql连接查询.联合查询.子查询原理与用法.分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询: 连接查询就是将多个表联合起来查询,连接查询方式有内连接.外连接.自然连接.交叉连接.连接查询使得可以同时查看多张表中数据. 内连接:有条件连接,多个表之间依据指定条件连接,匹配结果是保留符合匹配结果的记录. 外连接:与内连接不同的是不管匹配符不符合都保留,根据外连接

  • 详解MySQL子查询（嵌套查询）、联结表、组合查询

    一.子查询 MySQL 4.1版本及以上支持子查询 子查询:嵌套在其他查询中的查询. 子查询的作用: 1.进行过滤: 实例1:检索订购物品TNT2的所有客户的ID = + 一般,在WHERE子句中对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询. 注意:列必须匹配 --在WHERE子句中使用子查询(如这里所示),应该保证SELECT语句具有与WHERE子句中相同数目的列.通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列. 示例2:返回订购

  • 详解MySQL中的分组查询与连接查询语句

    分组查询 group by group by 属性名 [having 条件表达式][ with rollup] "属性名 "指按照该字段值进行分组:"having 条件表达式 "用来限制分组后的显示,满足条件的结果将被显示:with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和. 1)单独使用 group by 单独使用,查询结果只显示一个分组的一条记录. 实例: select * from employee group by sex;

  • MySQL中连接查询和子查询的问题

    MySQL中连接查询和子查询的问题

    目录 多表连接的基本语法 交叉连接和笛卡尔积现象 交叉连接 笛卡尔积现象 内连接 外连接 左外连接 右外连接 全外连接 子查询 多表连接的基本语法 多表连接,就是将几张表拼接为一张表,然后进行查询 select 字段1, 字段2, ... from 表1 {inner|lift|right} join 表2 on 连接条件; 有如下两张表:部门表和员工表 交叉连接和笛卡尔积现象 交叉连接 交叉连接,又名无条件内连接/笛卡尔连接 第一张表种的每一项会和另一张表的每一项依次组合 select * f

  • Mysql数据库性能优化之子查询

    Mysql数据库性能优化之子查询

    记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",

  • MySQL嵌套查询实现子查询的方法

    MySQL嵌套查询实现子查询的方法

    目录 一个查询语句嵌套在另一个查询语句内部的查询 1.带ANY(SOME)关键字的子查询 2.带ALL关键字的子查询 3.带EXISTS关键字的子查询 4.带IN关键字的子查询 5.带比较运算符的子查询 一个查询语句嵌套在另一个查询语句内部的查询 常用操作符:ANY(SOME), ALL, IN, EXISTS 比较运算符:>, >=, <, <=, != 等 创建两个表,表明分别是tb1,tb2,并向其各插入了数据. 1.带ANY(SOME)关键字的子查询 这里any和some是

  • mysql中使用instr进行模糊查询方法介绍

    在mysql中使用内部函数instr,可代替传统的like方式查询,并且速度更快. instr 函数,第一个参数是字段,第二个参数是要查询的串,返回串的位置,第一个是1,如果没找到就是0. 例如,查询字段name中带"军"的名字,传统的方法是: select name from 用户表 where name like `%军%'; 用instr的方法: select name from 用户表 where instr('name','军'); 或: select name from 用

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • MySql中流程控制函数/统计函数/分组查询用法解析

    路漫漫其修远兮,吾将上下而求索,又到了周末,我继续带各位看官学习回顾Mysql知识. 上次说到了流程控制函数,那就从流程控制函数来继续学习吧! #五.流程控制函数 #1.if函数:if else的效果 IF(条件表达式,成立返回1,不成立返回2) #与Java三元运算相同 SELECT IF(10>5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金呵呵','有奖金嘻嘻') AS 备注 FROM em

  • oracle基本查询操作子查询用法实例分析

    本文实例讲述了oracle基本查询操作子查询用法.分享给大家供大家参考,具体如下: 一.子查询语法 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 子查询在主查询之前一次执行完成. 子查询的结果被主查询使用. select ename from emp where sal > (select sal from emp where ename='SCOTT'); (*注意:子查询要

  • MySQL优化之使用连接(join)代替子查询

    使用连接(JOIN)来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询.这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中.例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示: DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FR

随机推荐